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, 
y.ProductID,
x.ProductName,
y.UnitPrice,
y.Quantity,
y.Discount,
round(y.UnitPrice * y.Quantity * (1 - y.Discount), 2) as ExtendedPrice
from Products x
inner join Order_Details y on x.ProductID = y.ProductID
order by 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.

/*
Query 1: normal joins
*/
select distinct a.CategoryID,
a.CategoryName,
b.ProductName,
sum(round(y.UnitPrice * y.Quantity * (1 - y.Discount), 2)) as ProductSales
from Order_Details y
inner join Orders d on d.OrderID = y.OrderID
inner join Products b on b.ProductID = y.ProductID
inner join Categories a on a.CategoryID = b.CategoryID
where d.OrderDate between date('1997/1/1') and date('1997/12/31')
group by a.CategoryID, a.CategoryName, b.ProductName
order by a.CategoryName, b.ProductName, ProductSales;

/*
Query 2: join with a sub query

This query returns identical result as above, but here
sub query is used to calculate extended price which
then used in the main query to get ProductSales
*/
select distinct a.CategoryID,
a.CategoryName,
b.ProductName,
sum(c.ExtendedPrice) as ProductSales
from Categories a
inner join Products b on a.CategoryID = b.CategoryID
inner join
(
select distinct y.OrderID,
y.ProductID,
x.ProductName,
y.UnitPrice,
y.Quantity,
y.Discount,
round(y.UnitPrice * y.Quantity * (1 - y.Discount), 2) as ExtendedPrice
from Products x
inner join Order_Details y on x.ProductID = y.ProductID
order by y.OrderID
) c on c.ProductID = b.ProductID
inner join Orders d on d.OrderID = c.OrderID
where d.OrderDate between date('1997/1/1') and date('1997/12/31')
group by a.CategoryID, a.CategoryName, b.ProductName
order by a.CategoryName, b.ProductName, ProductSales;

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
select distinct ProductName as Ten_Most_Expensive_Products,
UnitPrice
from Products as a
where 10 >= (select count(distinct UnitPrice)
from Products as b
where b.UnitPrice >= a.UnitPrice)
order by UnitPrice desc;

-- Query 2
select * from
(
select distinct ProductName as Ten_Most_Expensive_Products,
UnitPrice
from Products
order by UnitPrice desc
) as a
limit 10;

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, 
b.ProductName,
b.QuantityPerUnit,
b.UnitsInStock,
b.Discontinued
from Categories a
inner join Products b on a.CategoryID = b.CategoryID
where b.Discontinued = 'N'
order by a.CategoryName, b.ProductName;

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 
from Customers
union
select City, CompanyName, ContactName, 'Suppliers'
from Suppliers
order by City, CompanyName;

Here is the query result. 120 records returned.





Other tutorials in this category

1. MySQL Northwind Queries - Part 1

2. MySQL Northwind Queries - Part 3

3. How to Work with Two Unrelated Values

4. How to Fill Gaps in Sales Data

5. How to Calculate Totals, Subtotals and Grand Total

6. How to Work with NULL Values

7. How to fill down empty cells with values from a previous non-empty row

8. Use RANK function to update a previous record within a group or partition

9. Two ways to add a unique number or ID to each row

10. 3 ways to get Top N rows from MySQL

11. How to generate Cumulative Sum (running total) by MySQL - Part 1

12. How to generate Cumulative Sum (running total) by MySQL - Part 2

Back to Tutorial Index Page


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