Northwind Database Sql Queries
That is what the subquery does. 'a' refers to the main query that I outlined above. 'b' refers to the new subquery that we created. For each record in 'a' the query will fetch the product name with highest sales from 'b' The joins are used only for linking the data of sales person. So the data of 'a' will be linked to the data of 'b'.
This is part 2 of the tutorial series - converting the popular Microsoft Access Northwind database queries toMySQL 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.
- Northwind and pubs sample databases for Microsoft SQL Server Before you can use the Northwind database, you have to run the downloaded instnwnd.sql script file to recreate the database on an instance of SQL Server by using SQL Server Management Studio or a similar tool. Follow the instructions in the Readme file in the repository.
- The Northwind database was Microsoft’s first attempt at making a unified set of objects available for cross-platform examples. That means Microsoft Access users can start with a database in SQL Server that they are already familiar with, and applications designed to work against this database in Access will work once you point them at SQL Server.
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.
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.
Sql Database Queries Examples
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.
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
Create a procedures
1. To get list of all the orders processed with category name as an input parameter
2. Get the product name , count of orders processed
3. Get the list of the months which doesn’t have any orders like below ( This is a typical question , design the code before you execute carefully)
Product | No_orders_Year and month |
Chai | 03/2007 |
Tofu | 04/2007 |
select distinct P_CROSS_Y.productid,P_CROSS_Y.productname,P_CROSS_Y.y,PY.ORDER_YEAR from |
Result :
4. Get the top 3 products which has more orders
5. Get the list of the months which doesn’t have any orders for product chai
6. Get the list of the products which doesn’t have any orders across all the months and year as
7. Get the list of employees who processed the order “chai”
8. Get the list of the employees and the count of orders they processed in the month of “march “across all the years
9. Get the list of the employees who processed the orders belongs to his own city
10. Get the list of the employees who processed the orders doesn’t belongs to his own city
11. Get the shipper company who processed the order categories “Seafood”
12. Get category name , count of orders processed by the USA employees
Northwind Database Sql Queries Tutorial
Get the supplier name , shipper name for product category “sea food”
Add one more column to the Employees Table as 'Bonus'
and update the bonus field with number of total number orders each employee processed. Update statement must contains Join