Northwind database queries
2. Sales by Year
This query shows how to get the year part from Shipped_Date column. A subtotal is calculated by a sub-query for each order. The sub-query forms a table and then joined with the Orders table.
select distinct date(a.ShippedDate) as ShippedDate,
a.OrderID,
b.Subtotal,
year(a.ShippedDate) as Year
from Orders a
inner join
(
select distinct OrderID,
format(sum(UnitPrice * Quantity * (1 - Discount)), 2) as Subtotal
from order_details
group by OrderID
) b on a.OrderID = b.OrderID
where a.ShippedDate is not null
and a.ShippedDate between date and date
order by a.ShippedDate;
3. Employee Sales by Country
For each employee, get their sales amount, broken down by country name.
select distinct b.*, a.CategoryName
from Categories a
inner join Products b on a.CategoryID = b.CategoryID
where b.Discontinued = 'N'
order by b.ProductName;
4. Alphabetical List of Products
This is a rather simple query to get an alphabetical list of products.
select distinct b.*, a.Category_Name from Categories a inner join Products b on a.Category_ID = b.Category_ID where b.Discontinued = 'N' order by b.Product_Name;
5. Current Product List
This is another simple query. No aggregation is used for summarizing data.
select ProductID, ProductName
