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

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