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)