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)


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