Northwind database queries

MySQL Northwind Queries
April 3, 2017 – 08:01 am
Newest northwind Questions - Stack Overflow

Here is the query result. 830 records returned.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;

Here is the query result. 296 records returned. 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

Here is the query result. 296 records returned.
Source: www.geeksengine.com
Related Posts