Northwind
C:\Documents and Settings\"user name"\ Northwind 2007.accdb
ACCDB is the new default file format in Access 2007.
This contains a lot more tables than the 2003 version.
The database schema has also changed quite a lot.
There have even been changes to column names (for example: Customers.CompanyName became Customers.Company)
Northwind Tables
| Name | 2007 | 2003 |
| Categories | Y | Y |
| Customers | Y | Y |
| Employee Privileges | Y | |
| Employees | Y | Y |
| Inventory Transaction Types | Y | |
| Inventory Transactions | Y | |
| Invoices | Y | Y |
| Order Detail Status | Y | |
| Order Details | Y | Y |
| Order Status | Y | |
| Order Tax Status | Y | |
| Orders | Y | Y |
| Privileges | Y | |
| Products | Y | Y |
| Purchase Order Details | Y | |
| Purchase Order Status | Y | |
| Purchase Orders | Y | |
| Sales Reports | Y | |
| Shippers | Y | Y |
| String | Y | |
| Suppliers | Y | Y |
Categories
SELECT *
FROM CATEGORIES
SELECT CategoryID
,CategoryName
,Description
FROM CATEGORIES
ORDER BY CategoryName
--cannot order by Description because it is an 'ntext' field
--The text, ntext, and image data types cannot be used in an ORDER BY clause.
SELECT *
FROM CATEGORIES
WHERE CategoryName = 'Produce'
this is an example of a column alias / column name
SELECT CategoryName As Category_Name
FROM CATEGORIES
WHERE CategoryName = 'Produce'
OR CategoryName = 'Seafood'
Customers
SELECT *
FROM CUSTOMERS
SELECT CompanyName
,ContactName
FROM CUSTOMERS
List of customers and the corresponding number of orders they have placed.
SELECT Customers.CompanyName, COUNT(Orders.CustomerID)
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CompanyName
List of customers that have placed over 10 orders
SELECT Customers.CompanyName, COUNT(Orders.CustomerID)
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CompanyName
HAVING COUNT(Orders.CustomerID) > 10
Employees
List all the different job titles and the number of employees with each title.
SELECT Title, COUNT([Title])
FROM Employees
GROUP BY Title
List all the different job titles where more than one employee has been assigned.
SELECT Title, COUNT(Title)
FROM Employees
GROUP BY Title
HAVING COUNT(Title) > 1
List all the employees that were born after 1 Jan 1950
SELECT *
FROM Employees
WHERE BirthDate > #1/1/1950#
List all the employees that have the letter "c" in their firstname
SELECT *
FROM Employees
WHERE FirstName Like "*c*"
The following all work in SQL Server
SELECT Employees.*
FROM EMPLOYEES
WHERE ReportsTo IS NULL
SELECT *
FROM EMPLOYEES
WHERE ReportsTo IS NOT NULL
SELECT *
FROM [EMPLOYEES]
SELECT "Address"
FROM EMPLOYEES
SELECT [EMPLOYEES].[Address]
FROM EMPLOYEES
SELECT [Address]
FROM EMPLOYEES
Invoices
SELECT *
FROM INVOICES
This only works in SQL Server as there is no (nolock) feature in Access
SELECT *
FROM INVOICES (nolock)
This works in both SQL Server and Access becuase Quantity is a numerical field
SELECT *
FROM INVOICES
WHERE Quantity BETWEEN 50 AND 60
This works in both SQL Server and Access
SELECT Country
,COUNT(Country)
FROM INVOICES
GROUP BY Country
HAVING Country = 'Denmark'
ORDER BY Country ASC
This works in both SQL Server and Access
SELECT *
FROM INVOICES I
INNER JOIN CUSTOMERS
ON Customers.CustomerID = I.CustomerID
WHERE I.City = 'Lyon'
Orders
--This is how you select from a table name with spaces in the name
--This works in both Access and SQL Server
SELECT *
FROM [ORDER DETAILS]
ORDERS
SELECT *
FROM ORDERS
SELECT COUNT(*)
FROM ORDERS
SELECT COUNT(*)
FROM ORDERS
WHERE ShipName = 'Hanari Carnes'
--Examples of Table Alias's
SELECT *
FROM ORDERS AS O
INNER JOIN CUSTOMERS AS C
ON O.CustomerID = C.CustomerID
WHERE C.CompanyName = 'Island Trading'
List all the orders and the total amount of each product ordered
SELECT Orders.OrderID, [Order Details].ProductID, [Order Details].Quantity , [Order Details].UnitPrice , [Order Details].UnitPrice * [Order Details].Quantity
FROM [Order Details]
INNER JOIN Orders ON Orders.OrderID = [Order Details].OrderID
List all the customers and their orders
SELECT Customers.CompanyName, Orders.OrderID, [Order Details].ProductID, [Order Details].Quantity , [Order Details].UnitPrice, [Order Details].UnitPrice*[Order Details].Quantity
FROM Orders, Customers, [Order Details]
WHERE Orders.OrderID = [Order Details].OrderID
AND Customers.CustomerID = Orders.CustomerID
This displays a list of customers and the total amount they have ordered
SELECT Customers.CompanyName, SUM([Order Details].UnitPrice*[Order Details].Quantity)
FROM Orders, Customers, [Order Details]
WHERE Orders.OrderID = [Order Details].OrderID
AND Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CompanyName
List all the customers and the size of their average order
SELECT Customers.CompanyName, AVG([Order Details].UnitPrice*[Order Details].Quantity)
FROM Customers
INNER JOIN (
Orders
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
) ON
Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CompanyName
This is the same as the following
SELECT Customers.CompanyName, AVG([Order Details].UnitPrice*[Order Details].Quantity)
FROM Orders, Customers, [Order Details]
WHERE Orders.OrderID = [Order Details].OrderID
AND Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CompanyName
List all the employees and their total sales
SELECT DISTINCTROW Sum(UnitPrice * Quantity) AS Sales, (FirstName & Chr(32) & LastName) AS Name
FROM Employees
INNER JOIN (
Orders
INNER JOIN [Order Details] ON [Order Details].OrderID = Orders.OrderID
) ON
Orders.EmployeeID = Employees.EmployeeID
GROUP BY (FirstName & Chr(32) & LastName)
PRODUCTS
SELECT *
FROM PRODUCTS
--This works in both SQL Server and Access
SELECT *
FROM PRODUCTS
WHERE ProductName IN ('Chocolade','Outback Lager')
--This works in both SQL Server and Access
SELECT *
FROM PRODUCTS
WHERE UnitPrice =
( SELECT MAX(UnitPrice)
FROM PRODUCTS
)
--This works in both SQL Server and Access
SELECT *
FROM PRODUCTS
WHERE LEFT(ProductName,1) IN ('A','B')
--This works in both SQL Server and Access
SELECT *
FROM PRODUCTS
WHERE RIGHT(ProductName,1) IN ('r')
--This only works in SQL Server becuase there is no SUBSTRING function in Access
SELECT *
FROM PRODUCTS
WHERE SUBSTRING(ProductName,1,2) IN ('ch')
--WHERE SUBSTRING(ProductName,1,2) IN ('CH')
-- not case sensitive
This works in both SQL Server and Access (although dates are not formatted very well)
SELECT OrderDate
,Country
,City
,ProductName
,Quantity
,UnitPrice
FROM INVOICES
WHERE Quantity IN (1,3,5)
AND Country = 'Canada'
SELECT *
FROM PRODUCTS
REGION
--This table does not exist in Access
SELECT *
FROM REGION
SHIPPERS
SELECT *
FROM SHIPPERS
SUPPLIERS
SELECT *
FROM SUPPLIERS
WHERE Region IS NOT NULL
TERRITORIES
--This table does not exist in Access
SELECT *
FROM TERRITORIES
WHERE ABS(RegionID) = 4
--RAND() returns a random number between 0 and 1
SELECT RAND(), *
FROM TERRITORIES
--WHERE RegionID = RAND(4)
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
© 2026 Better Solutions Limited. All Rights Reserved. © 2026 Better Solutions Limited TopPrev