Northwind - Queries

Queries - is a subset of one or more tables used to restrict access to certin columns or rows of data within a table


Alphabetical List of Products 
Category Sales for 1997 
Current Product List 
Customer and Suppliers by City 
Employee Sales by Country 
Invoices 
Invoices Filter 
Order Details Extended 
Order Subtotals 
Order Qry 
Product Sales for 1997 
Products Above Average Price 
Products by Category 
Quarterly Orders 
Quarterly Orders By Product 
Sales by Category 
Sales by Year 
Sales Totals by Amount 
Summary of Sale by Quarter 
Summary of Sales by Year 
Ten Most Expensive Products 

Alphabetical List of Products

Access

SELECT         Products.* 
                     ,Categories.CategoryName
FROM Categories
INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID
WHERE Products.Discontinued = No

SQL Server

SELECT         dbo.Products.ProductID 
                     ,dbo.Products.ProductName
                     ,dbo.Products.SupplierID
                     ,dbo.Products.CategoryID
                     ,dbo.Products.QuantityPerUnit
                     ,dbo.Products.UnitPrice
                     ,dbo.Products.UnitsInStock
                     ,dbo.Products.UnitsOnOrder
                     ,dbo.Products.ReorderLevel
                     ,dbo.Products.Discontinued
                     ,dbo.Categories.CategoryName
FROM dbo.Categories
INNER JOIN dbo.Products
ON dbo.Categories.CategoryID = dbo.Products.CategoryID
WHERE dbo.Products.Discontinued = 0

Category Sales for 1997

Access

SELECT DISTINCTROW  [Product Sales for 1997].CategoryName 
      ,SUM([Product Sales for 1997].ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY [Product Sales for 1997].CategoryName

SQL Server

SELECT     CategoryName 
      ,SUM(ProductSales) AS CategorySales
FROM dbo.[Product Sales for 1997]
GROUP BY CategoryName

Current Product List

Access

SELECT     [Product List].ProductID 
      ,[Product List].ProductName
FROM Products AS [Product List]
WHERE [Product List].Discontinued = No
ORDER BY [Product List].ProductName

SQL Server

SELECT     ProductID 
      ,ProductName
FROM dbo.Products AS Product_List
WHERE (Discontinued = 0)

Customer and Suppliers by City

Access

SELECT     City 
      ,CompanyName
      ,ContactName
      ,"Customers" AS [Relationship]
FROM Customers
UNION SELECT City
      ,CompanyName
      ,ContactName
      ,"Suppliers"
FROM Suppliers
ORDER BY City,
      CompanyName;

SQL Server

SELECT     City 
      ,CompanyName
      ,ContactName
      ,'Customers' AS Relationship
FROM dbo.Customers
UNION
SELECT City
      ,CompanyName
      ,ContactName
',Suppliers' AS Expr1
FROM dbo.Suppliers

Employee Sales by Country

Access

PARAMETERS    [Beginning Date] DateTime, [Ending Date] DateTime 
SELECT DISTINCTROW Employees.Country
      ,Employees.LastName
      ,Employees.FirstName
      ,Orders.ShippedDate
      ,Orders.OrderID
      ,[Order Subtotals].Subtotal AS SaleAmount
FROM Employees
INNER JOIN (Orders
INNER JOIN [Order Subtotals]
ON Orders.OrderID = [Order Subtotals].OrderID)
ON Employees.EmployeeID = Orders.EmployeeID
WHERE (Orders.ShippedDate) BETWEEN [Beginning Date] AND [Ending Date])

SQL Server

not provided in SQL Server


Invoices

Access

CCur is not available in SQL Server

SELECT     Orders.ShipName 
      ,Orders.ShipAddress
      ,Orders.ShipCity
      ,Orders.ShipRegion
      ,Orders.ShipPostalCode
      ,Orders.ShipCountry
      ,Orders.CustomerID
      ,Customers.CompanyName
      ,Customers.Address
      ,Customers.City
      ,Customers.Region
      ,Customers.PostalCode
      ,Customers.Country
      ,[FirstName] & " " & [LastName] AS Salesperson
      ,Orders.OrderID
      ,Orders.OrderDate
      ,Orders.RequiredDate
      ,Orders.ShippedDate
      ,Shippers.CompanyName
      ,[Order Details].ProductID
      ,Products.ProductName
      ,[Order Details].UnitPrice
      ,[Order Details].Quantity
      ,[Order Details].Discount
      ,CCur([Order Details].UnitPrice * [Quantity] * (1 - [Discount]) / 100) * 100 AS ExtendedPrice
      ,Orders.Freight
FROM SHIPPERS
INNER JOIN (PRODUCTS
 INNER JOIN (
        (EMPLOYEES
  INNER JOIN (CUSTOMERS
   INNER JOIN ORDERS
   ON Customers.CustomerID = Orders.CustomerID)
  ON Employees.EmployeeID = Orders.EmployeeID)
  INNER JOIN [Order Details]
  ON Orders.OrderID = [Order Details].OrderID)
 ON Products.ProductID = [Order Details].ProductID
)
ON Shippers.ShipperID = Orders.ShipVia

SQL Server

SELECT     dbo.Orders.ShipName 
      ,dbo.Orders.ShipAddress
      ,dbo.Orders.ShipCity
      ,dbo.Orders.ShipRegion
      ,dbo.Orders.ShipPostalCode
      ,dbo.Orders.ShipCountry
      ,dbo.Orders.CustomerID
      ,dbo.Customers.CompanyName AS CustomerName
      ,dbo.Customers.Address
      ,dbo.Customers.City
      ,dbo.Customers.Region
      ,dbo.Customers.PostalCode
      ,dbo.Customers.Country
      ,dbo.Employees.FirstName + ' ' + dbo.Employees.LastName AS Salesperson
      ,dbo.Orders.OrderID
      ,dbo.Orders.OrderDate
      ,dbo.Orders.RequiredDate
      ,dbo.Orders.ShippedDate
      ,dbo.Shippers.CompanyName AS ShipperName
      ,dbo.[Order Details].ProductID
      ,dbo.Products.ProductName
      ,dbo.[Order Details].UnitPrice
      ,dbo.[Order Details].Quantity
      ,dbo.[Order Details].Discount
      ,CONVERT(money, (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount) / 100) * 100 AS ExtendedPrice
      ,dbo.Orders.Freight
FROM dbo.SHIPPERS
INNER JOIN dbo.PRODUCTS
INNER JOIN dbo.EMPLOYEES
INNER JOIN dbo.CUSTOMERS
INNER JOIN dbo.ORDERS
ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
ON dbo.Employees.EmployeeID = dbo.Orders.EmployeeID
INNER JOIN dbo.[Order Details]
ON dbo.Orders.OrderID = dbo.[Order Details].OrderID
ON dbo.Products.ProductID = dbo.[Order Details].ProductID
ON dbo.Shippers.ShipperID = dbo.Orders.ShipVia

Invoices Filter

Access

SELECT     Invoices.* 
FROM INVOICES
WHERE (Invoices.OrderID)=[Forms]![Orders]![OrderID])

SQL Server

not provided in SQL Server


Order Details Extended

Access

CCur is not available in SQL Server

SELECT     [Order Details].OrderID 
      ,[Order Details].ProductID
      ,Products.ProductName
      ,[Order Details].UnitPrice
      ,[Order Details].Quantity
      ,[Order Details].Discount
      ,CCur([Order Details].UnitPrice * [Quantity] * (1 - [Discount]) / 100) * 100 AS ExtendedPrice
FROM Products
INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID
ORDER BY [Order Details].OrderID

SQL Server

SELECT     dbo.[Order Details].OrderID D 
      ,dbo.[Order Details].ProductID
      ,dbo.Products.ProductName
      ,dbo.[Order Details].UnitPrice
      ,dbo.[Order Details].Quantity
      ,dbo.[Order Details].Discount
      ,CONVERT(money, (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount) / 100) * 100 AS ExtendedPrice
FROM dbo.Products
INNER JOIN dbo.[Order Details]
ON dbo.Products.ProductID = dbo.[Order Details].ProductID

Order Subtotals

Access

SELECT     [Order Details].OrderID, 
      SUM(CCur([UnitPrice] * [Quantity] * (1 - [Discount]) / 100) * 100) AS Subtotal
FROM [Order Details]
GROUP BY [Order Details].OrderID;

SQL Server

SELECT     OrderID, 
      SUM(CONVERT(money, (UnitPrice * Quantity) * (1 - Discount) / 100) * 100) AS Subtotal
FROM dbo.[Order Details]
GROUP BY OrderID

Order Qry

Access

SELECT     Orders.OrderID 
      ,Orders.CustomerID
      ,Orders.EmployeeID
      ,Orders.OrderDate
      ,Orders.RequiredDate
      ,Orders.ShippedDate
      ,Orders.ShipVia
      ,Orders.Freight
      ,Orders.ShipName
      ,Orders.ShipAddress
      ,Orders.ShipCity
      ,Orders.ShipRegion
      ,Orders.ShipPostalCode
      ,Orders.ShipCountry
      ,Customers.CompanyName
      ,Customers.Address
      ,Customers.City
      ,Customers.Region
      ,Customers.PostalCode
      ,Customers.Country
FROM CUSTOMERS
INNER JOIN ORDERS
ON Customers.CustomerID = Orders.CustomerID;

SQL Server

SELECT     dbo.Orders.OrderID 
      ,dbo.Orders.CustomerID
      ,dbo.Orders.EmployeeID
      ,dbo.Orders.OrderDate
      ,dbo.Orders.RequiredDate
      ,dbo.Orders.ShippedDate
      ,dbo.Orders.ShipVia
      ,dbo.Orders.Freight
      ,dbo.Orders.ShipName
      ,dbo.Orders.ShipAddress
      ,dbo.Orders.ShipCity
      ,dbo.Orders.ShipRegion
      ,dbo.Orders.ShipPostalCode
      ,dbo.Orders.ShipCountry
      ,dbo.Customers.CompanyName
      ,dbo.Customers.Address
      ,dbo.Customers.City
      ,dbo.Customers.Region
      ,dbo.Customers.PostalCode
      ,dbo.Customers.Country
FROM dbo.Customers
INNER JOIN dbo.Orders
ON dbo.Customers.CustomerID = dbo.Orders.CustomerID

Product Sales for 1997

Access

SELECT     Categories.CategoryName 
      ,Products.ProductName
      ,SUM(CCur([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100) AS ProductSales, "Qtr " & DatePart("q",[ShippedDate]) AS ShippedQuarter
FROM (Categories
INNER JOIN Products
ON Categories.CategoryID=Products.CategoryID)
INNER JOIN (Orders
INNER JOIN [Order Details]
ON Orders.OrderID=[Order Details].OrderID)
ON Products.ProductID=[Order Details].ProductID
WHERE Orders.ShippedDate BETWEEN #1/1/1997# AND #12/31/1997#
GROUP BY Categories.CategoryName,
      Products.ProductName, "Qtr " & DATEPART("q",[ShippedDate]);

SQL Server

SELECT     dbo.Categories.CategoryName 
      ,dbo.Products.ProductName
      ,SUM(CONVERT(money, (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount) / 100) * 100) AS ProductSales
FROM dbo.Categories
INNER JOIN dbo.Products
ON dbo.Categories.CategoryID = dbo.Products.CategoryID
INNER JOIN dbo.Orders
INNER JOIN dbo.[Order Details]
ON dbo.Orders.OrderID = dbo.[Order Details].OrderID
ON dbo.Products.ProductID = dbo.[Order Details].ProductID
WHERE dbo.Orders.ShippedDate BETWEEN '19970101' AND '19971231'
GROUP BY dbo.Categories.CategoryName
      ,dbo.Products.ProductName

Products Above Average Price

Access

SELECT     Products.ProductName 
      ,Products.UnitPrice
FROM Products
WHERE (Products.UnitPrice) >
      ( SELECT AVG([UnitPrice])
       From Products
      )
ORDER BY Products.UnitPrice DESC

SQL Server

SELECT     ProductName 
      ,UnitPrice
FROM dbo.Products
WHERE (UnitPrice >
      (
       SELECT AVG(UnitPrice) AS Expr1
       FROM dbo.Products)
      )

Products by Category

Access

SELECT     Categories.CategoryName 
      ,Products.ProductName
      ,Products.QuantityPerUnit
      ,Products.UnitsInStock
      ,Products.Discontinued
FROM Categories
INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID
WHERE ((Products.Discontinued) <> Yes)
ORDER BY Categories.CategoryName,
      Products.ProductName

SQL Server

SELECT     dbo.Categories.CategoryName 
      ,dbo.Products.ProductName
      ,dbo.Products.QuantityPerUnit
      ,dbo.Products.UnitsInStock
      ,dbo.Products.Discontinued
FROM dbo.Categories
INNER JOIN dbo.Products
ON dbo.Categories.CategoryID = dbo.Products.CategoryID
WHERE (dbo.Products.Discontinued <> 1)

Quarterly Orders

Access

SELECT DISTINCT   Customers.CustomerID 
      ,Customers.CompanyName
      ,Customers.City
      ,Customers.Country
FROM [Customers]
RIGHT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderDate Between #1/1/1997# And #12/31/1997#

SQL Server

SELECT DISTINCT   dbo.[Customers].CustomerID 
      ,dbo.[Customers].CompanyName
      ,dbo.[Customers].City
      ,dbo.[Customers].Country
FROM dbo.[Customers]
RIGHT OUTER JOIN dbo.[Orders]
ON dbo.[Customers].CustomerID = dbo.[Orders].CustomerID
WHERE dbo.[Orders].OrderDate BETWEEN '19970101' AND '19971231'

Quarterly Orders By Product

Access

TRANSFORM    SUM(CCur([Order Details].UnitPrice * [Quantity] * (1 - [Discount]) / 100) * 100) AS ProductAmount 
SELECT Products.ProductName
      ,Orders.CustomerID
      ,YEAR([OrderDate]) AS OrderYear
FROM [Products]
INNER JOIN (Orders
INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID)
ON Products.ProductID = [Order Details].ProductID
WHERE Orders.OrderDate BETWEEN #1/1/1997# AND #12/31/1997#
GROUP BY Products.ProductName,
      Orders.CustomerID,
      YEAR([OrderDate])
      PIVOT "Qtr " & DATEPART("q",[OrderDate],1,0) In ("Qtr 1","Qtr 2","Qtr 3","Qtr 4")

SQL Server

not provided in SQL Server


Sales by Category

Access

SELECT     Categories.CategoryID 
      ,Categories.CategoryName
      ,Products.ProductName
      ,SUM([Order Details Extended].ExtendedPrice) AS ProductSales
FROM Categories
INNER JOIN (Products
INNER JOIN (Orders
INNER JOIN [Order Details Extended]
ON Orders.OrderID = [Order Details Extended].OrderID)
ON Products.ProductID = [Order Details Extended].ProductID)
ON Categories.CategoryID = Products.CategoryID
WHERE Orders.OrderDate BETWEEN '1/1/1997' AND '12/31/1997'
--WHERE Orders.OrderDate BETWEEN #1/1/1997# AND #12/31/1997# - hashes don't work in SQL Server
GROUP BY Categories.CategoryID,
      Categories.CategoryName,
      Products.ProductName
ORDER BY Categories.CategoryName

SQL Server

SELECT     dbo.Categories.CategoryID 
      ,dbo.Categories.CategoryName
      ,dbo.Products.ProductName
      ,SUM(dbo.[Order Details Extended].ExtendedPrice) AS ProductSales
FROM dbo.Categories
INNER JOIN dbo.Products
INNER JOIN dbo.Orders
INNER JOIN dbo.[Order Details Extended]
ON dbo.Orders.OrderID = dbo.[Order Details Extended].OrderID
ON dbo.Products.ProductID = dbo.[Order Details Extended].ProductID
ON dbo.Categories.CategoryID = dbo.Products.CategoryID
WHERE dbo.Orders.OrderDate BETWEEN '19970101' AND '19971231'
GROUP BY dbo.Categories.CategoryID, dbo.Categories.CategoryName, dbo.Products.ProductName

Sales by Year

Access

PARAMETERS    [Forms]![Sales by Year Dialog]![BeginningDate] DATETIME, 
      [Forms]![Sales by Year Dialog]![EndingDate] DATETIME;
SELECT Orders.ShippedDate,
      ,Orders.OrderID
      ,[Order Subtotals].Subtotal
      ,FORMAT([ShippedDate],"yyyy") AS [Year]
FROM ORDERS
INNER JOIN [ORDER SUBTOTALS]
ON Orders.OrderID = [Order Subtotals].OrderID
WHERE (
       (
        (Orders.ShippedDate) IS NOT NULL
       AND (Orders.ShippedDate) BETWEEN [Forms]![Sales by Year Dialog]![BeginningDate] AND [Forms]![Sales by Year Dialog]![EndingDate]
       )
      )

SQL Server

not provided in SQL Server


Sales Totals by Amount

Access

not provided in Access

SQL Server

SELECT     dbo.[Order Subtotals].Subtotal AS SaleAmount 
      ,dbo.Orders.OrderID
      ,dbo.Customers.CompanyName
      ,dbo.Orders.ShippedDate
FROM dbo.Customers
INNER JOIN dbo.Orders
INNER JOIN dbo.[Order Subtotals]
ON dbo.Orders.OrderID = dbo.[Order Subtotals].OrderID
ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
WHERE dbo.[Order Subtotals].Subtotal > 2500
AND dbo.Orders.ShippedDate BETWEEN '19970101' AND '19971231'

Summary of Sale by Quarter

Access

not provided in Access

SQL Server

SELECT     dbo.Orders.ShippedDate 
      ,dbo.Orders.OrderID
      ,dbo.[Order Subtotals].Subtotal
FROM dbo.Orders
INNER JOIN dbo.[Order Subtotals]
ON dbo.Orders.OrderID = dbo.[Order Subtotals].OrderID
WHERE dbo.Orders.ShippedDate IS NOT NULL

Summary of Sales by Year

Access

not provided in Access

SQL Server

SELECT     dbo.[ORDERS].ShippedDate 
      ,dbo.[ORDERS].OrderID
      ,dbo.[Order Subtotals].Subtotal
FROM dbo.[ORDERS]
INNER JOIN dbo.[ORDER SUBTOTALS]
ON dbo.[ORDERS].OrderID = dbo.[ORDER SUBTOTALS].OrderID
WHERE dbo.[ORDERS].ShippedDate IS NOT NULL

Ten Most Expensive Products

Access

SELECT TOP 10   Products.ProductName AS TenMostExpensiveProducts, 
      Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC

SQL Server

not provided in SQL Server



© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext