Cross-database queries and queries using linked SQL Servers. These examples use SQL Server 2000 on which the Northwind and pubs example databases have been installed. The first example begins using the pubs database, and then accesses a table in the Northwind database. The following code example queries the Northwind database for the product with the ProductID value of 27. The value returned is an instance of the Product class, and the Console.WriteLine statement prints the name of the product.
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.
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.
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
Northwind Database Sql Queries Example
Queries In Sql
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
Comments are closed.