Download the document and include your answers with screen shots of the results.

Create a report containing Company names and Orders.

Must use 2 tables Customers and Orders

Common field is the CustomerID.

SELECT Customers.CompanyName, Orders.OrderID

FROM Customers, Orders

WHERE Customers.CustomerID = Orders.CustomerID;

Use an alias to reduce the size of the SELECT Clause

SELECT C.CompanyName, O.OrderID

FROM Customers C, Orders O

WHERE C.CustomerID = O.CustomerID;

Sort on Company Name

SELECT CompanyName, OrderID

FROM Customers, Orders

WHERE Customers.CustomerID = Orders.CustomerID

ORDER BY CompanyName;

Add to the WHERE clause to narrow down the selection

SELECT C.CompanyName, O.OrderID

FROM Customers C, Orders O

WHERE C.CustomerID = O.CustomerID

AND C.CustomerID like 'A%';

Sort on Company Name

SELECT C.CompanyName, O.OrderID

FROM Customers C, Orders O

WHERE C.CustomerID = O.CustomerID

AND C.CustomerID like 'A%'

ORDER BY C.CompanyName;

Add OrderDate to the SELECT clause

SELECT C.CompanyName, O.OrderID, O.OrderDate

FROM Customers C, Orders O

WHERE C.CustomerID = O.CustomerID

AND C.CustomerID like 'A%'

ORDER BY C.CompanyName;

Why does the OrderDate not require an Alias?

If an element referenced is in only one table, the DBMS can determine which element to display.

So, we can rewrite the original select clause leaving out the reference to the table and the DBMS will not be confused.

SELECT CompanyName, O.OrderID, OrderDate

FROM Customers C, Orders O

WHERE C.CustomerID = O.CustomerID

AND C.CustomerID like 'A%'

ORDER BY CompanyName;

Modify the WHERE clause to select the CustomerIDs first and then perform the rest of the where clause

SELECT CompanyName, O.OrderID, OrderDate

FROM Customers C, Orders O

WHERE C.CustomerID like 'A%'

AND C.CustomerID = O.CustomerID

ORDER BY CompanyName;

Retrieval using 3 tables

Which companies use Shipper number 3?

Using the Orders table as a link between the Customer and the Shipper

SELECT C.CompanyName, O.OrderID, S.CompanyName

FROM Customers C, Orders O, Shippers S

WHERE C.CustomerID = O.CustomerID

AND O.Shipvia = S.ShipperID

ORDER BY C.CompanyName;

Add the Order Date to the report

SELECT C.CompanyName, O.OrderID, O.OrderDate, S.CompanyName

FROM Customers C, Orders O, Shippers S

WHERE C.CustomerID = O.CustomerID

AND O.Shipvia = S.ShipperID

ORDER BY S.ShipperID;

Sort on Shipper and Order

SELECT C.CompanyName, O.OrderID, O.OrderDate, S.CompanyName

FROM Customers C, Orders O, Shippers S

WHERE C.CustomerID = O.CustomerID

AND O.Shipvia = S.ShipperID

ORDER BY S.ShipperID, O.OrderDate;

Add Employee First and Last Name, Company Name

SELECT FirstName,LastName, CompanyName, O.OrderID, O.OrderDate

FROM Customers C, Employees E, Orders O

WHERE E.EmployeeID = O.EmployeeID

AND C.CustomerID = O.CustomerID

ORDER BY E.EmployeeID;

Which Orders used Leka Trading as their supplier

SELECT O.OrderID, P.ProductID, S.SupplierID

FROM OrderDetails O, Products P, Suppliers S

WHERE O.ProductID = P.ProductID

AND P.SupplierID = S.SupplierID

AND S.CompanyName = 'Leka Trading';

SELECT O.OrderID, P.ProductID, ProductName, S.SupplierID

FROM OrderDetails O, Products P, Suppliers S

WHERE O.ProductID = P.ProductID

AND P.SupplierID = S.SupplierID

AND S.CompanyName = 'Leka Trading';

SELECT O.OrderID, P.ProductID, ProductName, S.SupplierID, S.CompanyName

FROM OrderDetails O, Products P, Suppliers S

WHERE O.ProductID = P.ProductID

AND P.SupplierID = S.SupplierID

AND S.CompanyName = 'Leka Trading';

SELECT O.OrderID, P.ProductID, ProductName, S.SupplierID, S.CompanyName

FROM OrderDetails O, Products P, Suppliers S

WHERE O.ProductID = P.ProductID

AND P.SupplierID = S.SupplierID

AND S.CompanyName = 'Leka Trading'

OR S.CompanyName = 'Exotic Liquids';

SELECT CONCAT(Firstname,' ', LastName), CompanyName, OrderID, OrderDate

FROM Employees e, Customers c, Orders o

WHERE e.EmployeeID = o.EmployeeID

AND c.CustomerID = o.CustomerID;

SELECT CONCAT(Firstname,' ', LastName), CompanyName, OrderID, OrderDate

FROM Employees e, Customers c, Orders o

WHERE e.EmployeeID = o.EmployeeID

AND c.CustomerID = o.CustomerID

ORDER BY e.EmployeeID;

SELECT CONCAT(Firstname,' ', LastName), CompanyName, OrderID, OrderDate

FROM Employees e, Customers c, Orders o

WHERE e.EmployeeID = o.EmployeeID

AND c.CustomerID = o.CustomerID

AND (o.OrderDate BETWEEN '1996-07-04' AND '1996-07-31')

ORDER BY e.EmployeeID;

SELECT CONCAT(Firstname,' ', LastName), CompanyName, OrderID, OrderDate

FROM Employees e, Customers c, Orders o

WHERE e.EmployeeID = o.EmployeeID

AND c.CustomerID = o.CustomerID

AND (o.OrderDate BETWEEN '1996-07-04' AND '1996-07-31')

ORDER BY e.EmployeeID, OrderDate;

How do we display what the product is for a given ProductID in the OrderDetails table?

SELECT OrderID, ProductName, Unit Price, QuantityPerUnit

FROM OrderDetails O, Products P

WHERE O.ProductID = P.ProductID

AND OrderID = 11077;

Calculate the total price for each item and include the discount

SELECT OrderID, ProductName, O.UnitPrice, Quantity, Discount, (O.UnitPrice*Quantity-Discount) AS Total

FROM OrderDetails O, Products P

WHERE O.ProductID = P.ProductID

AND Discount > 0;

SELECT OrderID, ProductName, O.UnitPrice, Quantity, Discount,

CONVERT(O.UnitPrice*Quantity-Discount, DECIMAL(6,2)) AS Total

FROM OrderDetails O, Products P

WHERE O.ProductID = P.ProductID

AND Discount > 0;

Customers Y customerID Company Name ContactName ContactTitle Address City Region Postal Code Country Phone Fa Orders order ID CustomerID Employee ID OrderDate Required Date ShippedDate ShipVia Freight ShipName ShipA ddress ShipCity ShipRegion ShipPostal Code ShipCountry