|
Custom Search
| |
|
MySQL Northwind Queries - Part 1 The popular Microsoft Access Northwind database has been exported to MySQL here. This is part 1 of this tutorial series - we convert the Northwind Access queries to MySQL SQL queries. These queries are used for Access Northwind Traders application. Some of them are relatively complex aggregated queries. Here is a screenshot of the queries from Access Northwind database.
1. Order Subtotals For each order, calculate a subtotal for each Order (identified by OrderID). This is a simple query using GROUP BY to aggregate data for each order.
-- Get subtotal for each order.
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,
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
Here is the query result. 296 records returned. 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;
Here is the query result. 69 records returned. 5. Current Product List This is another simple query. No aggregation is used for summarizing data.
select ProductID, ProductName
Here is the query result. 69 records returned. Other tutorials in this category 1. MySQL Northwind Queries - Part 2 |
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 |