The Nwind sample database provided on CD with Manifold System is a version of the standard Microsoft Northwind.mdb database that is provided with Microsoft Access. It contains numerous tables and queries that may be used to learn database and query concepts.
This topic lists queries that are part of the Nwind database, with some minor adjustments for Manifold SQL.
AutoLookup
SELECT DISTINCTROW Orders.[Order ID], Orders.[Order Date], Orders.[Customer ID], Customers.[Company Name], Customers.Address, Customers.City, Customers.Region, Customers.[Postal Code], Customers.Country, Customers.Phone
FROM Customers INNER JOIN Orders ON Customers.[Customer ID] = Orders.[Customer ID]
ORDER BY Orders.[Order ID];
Catalog
SELECT DISTINCTROW [Categories].[Category Name],
[Products].[Product Name], [Categories].[Description],
[Products].[Product ID], [Products].[Quantity Per Unit],
[Products].[Unit Price], [Products].[Discontinued]
FROM [Categories] INNER JOIN [Products] ON [Categories].[Category ID] = [Products].[Category ID]
WHERE ([Products].[Discontinued] = No) ORDER BY [Categories].[Category Name], [Products].[Product Name];
Category List
SELECT DISTINCTROW Categories.[Category Name], Categories.[Category ID]
FROM Categories
ORDER BY Categories.[Category Name];
Category Sales for 1993
SELECT DISTINCTROW [Sales for 1993].[Category Name], Sum([Sales for 1993].[Product Sales]) AS [Category Sales]
FROM [Sales for 1993]
GROUP BY [Sales for 1993].[Category Name];
Customer List
SELECT DISTINCTROW Customers.[Company Name], Customers.[Customer ID]
FROM Customers
ORDER BY Customers.[Company Name];
Daily Order Totals
SELECT DISTINCTROW Orders.[Order Date], Count(Orders.[Order ID]) AS [CountOfOrder ID], Sum([Order Subtotals].Subtotal) AS SumOfSubtotal
FROM Orders INNER JOIN [Order Subtotals] ON Orders.[Order ID] = [Order Subtotals].[Order ID]
GROUP BY Orders.[Order Date];
Employee List
SELECT DISTINCTROW [Last Name] & ", " & [First Name] AS [Employee Name], Employees.[Employee ID]
FROM Employees
ORDER BY [Last Name] & ", " & [First Name];
Employee Sales by Country (Parameter)
PARAMETERS [Beginning Date] DateTime, [Ending Date] DateTime;
SELECT Orders.[Order ID], [Last Name] & ", " & [First Name] AS Salesperson, Employees.Country, Orders.[Shipped Date], [Order Subtotals].Subtotal AS [Sale Amount]
FROM Employees INNER JOIN (Orders INNER JOIN [Order Subtotals] ON Orders.[Order ID] = [Order Subtotals].[Order ID]) ON Employees.[Employee ID] = Orders.[Employee ID]
WHERE ((Orders.[Shipped Date] Between [Beginning Date] And [Ending Date]))
ORDER BY [Last Name] & ", " & [First Name], Employees.Country, Orders.[Shipped Date];
Employee Sales for 1993
SELECT DISTINCTROW Sum([Order Subtotals].Subtotal) AS [Employee Sales], [First Name] & " " & [Last Name] AS [Employee Name], Employees.[Last Name], Employees.[First Name]
FROM Employees INNER JOIN ([Order Subtotals] INNER JOIN Orders ON [Order Subtotals].[Order ID] = Orders.[Order ID]) ON Employees.[Employee ID] = Orders.[Employee ID]
WHERE ((Orders.[Order Date] Between #01/1/93# And #12/31/93#))
GROUP BY [First Name] & " " & [Last Name], Employees.[Last Name], Employees.[First Name];
Freight Charges
SELECT DISTINCTROW Shippers.[Company Name], Orders.[Order ID], Orders.Freight, Orders.[Shipped Date]
FROM Shippers INNER JOIN Orders ON Shippers.[Shipper ID] = Orders.[Ship Via]
WHERE ((Orders.[Shipped Date] Between #03/25/93# And #03/31/93#))
ORDER BY Shippers.[Company Name], Orders.[Order ID];
Invoices
SELECT DISTINCTROW Orders.[Shipped Date], Orders.[Ship Name], Orders.[Ship Address], Orders.[Ship City], Orders.[Ship Region], Orders.[Ship Postal Code], Orders.[Ship Country], Orders.[Customer ID], Customers.[Company Name], Customers.Address, Customers.City, Customers.Region, Customers.[Postal Code], Customers.Country, Employees.[Last Name], Employees.[First Name], [Order Details].[Order ID], Orders.[Order Date], Orders.[Required Date], Orders.[Ship Via], [Order Details].[Product ID], Products.[Product Name], [Order Details].Quantity, [Order Details].[Unit Price], [Order Details].Discount, Orders.Freight
FROM Employees INNER JOIN ((Customers INNER JOIN Orders ON Customers.[Customer ID] = Orders.[Customer ID]) INNER JOIN (Products INNER JOIN [Order Details] ON Products.[Product ID] = [Order Details].[Product ID]) ON Orders.[Order ID] = [Order Details].[Order ID]) ON Employees.[Employee ID] = Orders.[Employee ID]
ORDER BY Orders.[Shipped Date];
Order Details Extended
SELECT DISTINCTROW [Order Details].[Order ID], Products.[Product Name], [Order Details].[Product ID], [Order Details].[Unit Price], [Order Details].Quantity, [Order Details].Discount, CLng([Order Details].[Unit Price]*[Quantity]*(1-[Discount])*100)/100 AS [Extended Price]
FROM Products INNER JOIN [Order Details] ON Products.[Product ID] = [Order Details].[Product ID];
Order Information
SELECT DISTINCTROW Orders.[Order ID], Orders.[Customer ID], Customers.[Company Name], Customers.Address, Customers.City, Customers.Region, Customers.[Postal Code], Customers.Country, Orders.[Ship Name], Orders.[Ship Address], Orders.[Ship City], Orders.[Ship Region], Orders.[Ship Postal Code], Orders.[Ship Country], Orders.[Ship Via], Orders.[Employee ID], Orders.[Order Date], Orders.[Required Date], Orders.[Shipped Date], Orders.Freight
FROM Customers INNER JOIN Orders ON Customers.[Customer ID] = Orders.[Customer ID]
ORDER BY Orders.[Order ID];
Order Review
SELECT DISTINCTROW Orders.[Order ID], Orders.[Order Date], Orders.[Required Date], Orders.[Shipped Date], Orders.[Customer ID], Customers.[Company Name], Employees.[Last Name], Shippers.[Company Name] AS [Ship Via], [Order Subtotals].Subtotal, Orders.Freight, [Subtotal]+[Freight] AS Total
FROM Employees INNER JOIN (Shippers INNER JOIN (Customers INNER JOIN ([Order Subtotals] INNER JOIN Orders ON [Order Subtotals].[Order ID] = Orders.[Order ID]) ON Customers.[Customer ID] = Orders.[Customer ID]) ON Shippers.[Shipper ID] = Orders.[Ship Via]) ON Employees.[Employee ID] = Orders.[Employee ID]
ORDER BY Orders.[Order ID] DESC;
Order Subtotals
SELECT DISTINCTROW [Order Details].[Order ID], Sum(CLng([Unit Price]*[Quantity]*(1-[Discount])*100)/100) AS Subtotal
FROM [Order Details]
GROUP BY [Order Details].[Order ID];
Product List
SELECT DISTINCTROW Categories.[Category Name], Products.[Product Name], Products.[Product ID], Products.[Units In Stock], Products.[Reorder Level], Products.[Units On Order], Products.[Quantity Per Unit], Products.[Unit Price], Categories.Description, Products.Discontinued
FROM Categories INNER JOIN Products ON Categories.[Category ID] = Products.[Category ID]
WHERE ((Products.Discontinued=False))
ORDER BY Categories.[Category Name], Products.[Product Name];
Product Names and IDs
SELECT DISTINCTROW Products.[Product Name], Products.[Product ID]
FROM Products
ORDER BY Products.[Product Name];
Products and Suppliers
SELECT DISTINCTROW Products.[Product Name], Products.[Product ID], Products.[English Name], Products.[Category ID], Products.[Supplier ID], Products.[Quantity Per Unit], Products.[Unit Price], Products.[Units In Stock], Products.[Units On Order], Products.[Reorder Level], Products.Discontinued, Suppliers.[Company Name], Suppliers.[Contact Name], Suppliers.City, Suppliers.Country, Suppliers.Phone
FROM Suppliers INNER JOIN Products ON Suppliers.[Supplier ID] = Products.[Supplier ID]
ORDER BY Products.[Product Name];
Products on Order
SELECT DISTINCTROW Categories.[Category Name], Products.[Product Name], Products.[Units In Stock], Products.[Units On Order], Suppliers.[Company Name], Suppliers.Phone
FROM Categories INNER JOIN (Suppliers INNER JOIN Products ON Suppliers.[Supplier ID] = Products.[Supplier ID]) ON Categories.[Category ID] = Products.[Category ID];
Quarterly Orders by Product (Crosstab)
TRANSFORM Sum(CLng([Order Details].[Unit Price]*[Quantity]*
(1-[Discount])*100)/100)
SELECT Products.[Product Name], Orders.[Customer ID]
FROM Orders INNER JOIN
(Products INNER JOIN [Order Details] ON
Products.[Product ID] = [Order Details].[Product ID]) ON
Orders.[Order ID] = [Order Details].[Order ID]
WHERE ((Orders.[Order Date] Between #01/1/93# And #12/31/93#))
GROUP BY Products.[Product Name], Orders.[Customer ID], Orders.[Order Date]
ORDER BY Products.[Product Name]
PIVOT "Qtr " & DatePart("q",[Order Date]) In
("Qtr 1","Qtr 2","Qtr 3","Qtr 4");
Quarterly Orders by Product (Make Table)
SELECT DISTINCTROW [Quarterly Orders by Product (Crosstab)].[Product Name], [Quarterly Orders by Product (Crosstab)].[Customer ID], [Quarterly Orders by Product (Crosstab)].[Qtr 1], [Quarterly Orders by Product (Crosstab)].[Qtr 2], [Quarterly Orders by Product (Crosstab)].[Qtr 3], [Quarterly Orders by Product (Crosstab)].[Qtr 4] INTO [Quarterly Orders]
FROM [Quarterly Orders by Product (Crosstab)];
Sales by Category
SELECT DISTINCTROW Categories.[Category Name], Products.[Product Name], Sum(CLng([Order Details].[Unit Price]*[Quantity]*(1-[Discount])*100)/100) AS [Product Sales]
FROM Orders INNER JOIN ((Categories INNER JOIN Products ON Categories.[Category ID] = Products.[Category ID]) INNER JOIN [Order Details] ON Products.[Product ID] = [Order Details].[Product ID]) ON Orders.[Order ID] = [Order Details].[Order ID]
WHERE ((Orders.[Order Date] Between #01/1/93# And #12/31/93#))
GROUP BY Categories.[Category Name], Products.[Product Name]
ORDER BY Categories.[Category Name], Products.[Product Name];
Sales by Date (Parameter)
PARAMETERS [Beginning Date] DateTime, [Ending Date] DateTime;
SELECT DISTINCTROW Orders.[Order ID], Orders.[Shipped Date], Customers.[Company Name], [Order Subtotals].Subtotal AS [Sale Amount]
FROM Customers INNER JOIN ([Order Subtotals] INNER JOIN Orders ON [Order Subtotals].[Order ID] = Orders.[Order ID]) ON Customers.[Customer ID] = Orders.[Customer ID]
WHERE ((Orders.[Shipped Date] Between [Beginning Date] And [Ending Date]))
ORDER BY Orders.[Order ID];
Sales by Product
SELECT DISTINCTROW Products.[Product ID], Orders.[Order Date], Sum(CLng([Order Details].[Unit Price]*[Quantity]*(1-[Discount])*100)/100) AS [Product Amount]
FROM Orders INNER JOIN (Products INNER JOIN [Order Details] ON Products.[Product ID] = [Order Details].[Product ID]) ON Orders.[Order ID] = [Order Details].[Order ID]
GROUP BY Products.[Product ID], Orders.[Order Date];
Sales by Sale Amount
SELECT DISTINCTROW [Order Subtotals].Subtotal AS [Sale Amount], Orders.[Order ID], Customers.[Company Name], Orders.[Shipped Date]
FROM Customers INNER JOIN (Orders INNER JOIN [Order Subtotals] ON Orders.[Order ID] = [Order Subtotals].[Order ID]) ON Customers.[Customer ID] = Orders.[Customer ID]
WHERE ((Orders.[Shipped Date] Between #10/1/93# And #12/31/93#))
ORDER BY [Order Subtotals].Subtotal;
Sales for 1993
SELECT DISTINCTROW Categories.[Category Name], Products.[Product Name], Sum(CLng([Order Details].[Unit Price]*[Quantity]*(1-[Discount])*100)/100) AS [Product Sales]
FROM Orders INNER JOIN ((Categories INNER JOIN Products ON Categories.[Category ID] = Products.[Category ID]) INNER JOIN [Order Details] ON Products.[Product ID] = [Order Details].[Product ID]) ON Orders.[Order ID] = [Order Details].[Order ID]
WHERE ((Orders.[Order Date] Between #01/1/93# And #12/31/93#))
GROUP BY Categories.[Category Name], Products.[Product Name];
Sales Totals
SELECT DISTINCTROW [Employees].[First Name] & " " & [Last Name] AS Name, Orders.[Employee ID], Count(Orders.[Order ID]) AS [CountOfOrder ID], Sum([Order Subtotals].Subtotal) AS SumOfSubtotal
FROM Employees INNER JOIN ([Order Subtotals] INNER JOIN Orders ON [Order Subtotals].[Order ID] = Orders.[Order ID]) ON Employees.[Employee ID] = Orders.[Employee ID]
GROUP BY [Employees].[First Name] & " " & [Last Name], Orders.[Employee ID];
Subquery
SELECT DISTINCTROW Customers.[Contact Name], Customers.[Company Name], Customers.[Contact Title], Customers.Phone
FROM Customers
WHERE ((Customers.[Customer ID] In (SELECT DISTINCTROW Orders.[Customer ID] FROM Orders
WHERE Orders.[Order Date] BETWEEN #04/1/93# AND #07/1/93#)));
Summary of Sales
SELECT DISTINCTROW Orders.[Shipped Date], Orders.[Order ID], [Order Subtotals].Subtotal AS [Order Amount]
FROM Orders INNER JOIN [Order Subtotals] ON Orders.[Order ID] = [Order Subtotals].[Order ID];
Summary of Sales (Parameter)
PARAMETERS [Beginning Date] DATETIME, [Ending Date] DATETIME; SELECT DISTINCTROW [Orders].[Shipped Date], [Orders].[Order ID], [Order Subtotals].[Subtotal], Year([Shipped Date]) AS [Year] FROM [Orders] INNER JOIN [Order Subtotals] ON [Orders].[Order ID] = [Order Subtotals].[Order ID] WHERE ([Orders].[Shipped Date] BETWEEN [Beginning Date] AND [Ending Date]);
Supplier List
SELECT DISTINCTROW Suppliers.[Company Name], Suppliers.[Supplier ID] FROM Suppliers
ORDER BY Suppliers.[Company Name];
Ten Most Expensive Products
SELECT DISTINCTROW TOP 10 Products.[Product Name] AS [Ten Most Expensive Products], Products.[Unit Price]
FROM Products
ORDER BY Products.[Unit Price] DESC;
Union Query
SELECT [Company Name], [City]
FROM [Suppliers]
WHERE Country = "Brazil"
UNION SELECT [Company Name], [City]
FROM [Customers]
WHERE Country = "Brazil";