|
Custom Search
| |
|
MySQL Northwind Queries - Part 2 This is part 2 of the tutorial series - converting the popular Microsoft Access Northwind database queries to MySQL queries. These queries are originated from Access Northwind Traders application. Some of them are relatively complex aggregated queries with sub-queries. 6. Order Details Extended This query calculates sales price for each order after discount is applied.
select distinct y.OrderID,
Here is the query result. 2,155 records returned. 7. Sales by Category For each category, we get the list of products sold and the total sales amount. Note that, in the second query, the inner query for table c is to get sales for each product on each order. It then joins with outer query on Product_ID. In the outer query, products are grouped for each category.
/*
Here is the query result. 77 records returned. 8. Ten Most Expensive Products The two queries below return the same result. It demonstrates how MySQL limits the number of records returned. The first query uses correlated sub-query to get the top 10 most expensive products. The second query retrieves data from an ordered sub-query table and then the keyword LIMIT is used outside the sub-query to restrict the number of rows returned.
-- Query 1
Here is the query result. 10 records returned. 9. Products by Category This is a simple query just because it's in Access Northwind so we converted it here in MySQL. select distinct a.CategoryName,
Here is the query result. 69 records returned. 10. Customers and Suppliers by City This query shows how to use UNION to merge Customers and Suppliers into one result set by identifying them as having different relationships to Northwind Traders - Customers and Suppliers.
select City, CompanyName, ContactName, 'Customers' as Relationship
Here is the query result. 120 records returned.
Other tutorials in this category 1. MySQL Northwind Queries - Part 1 |
Copyright © 2024 GeeksEngine.com. All Rights Reserved. This website is hosted by HostGator. No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it. |
Home | Feedback | Terms of Use | Privacy Policy |