Just answer A , B , C

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;

Answer the three questions in the Multiple Tables Homework Document

NOTE: Yellow bar indicates how many records should be returned

Create the SQL statements to generate the following report (Partial lists displayed).

A)

Employee	CompanyName	ContactName	Customer Phone	OrderID	Orderdate
Steven Buchanan	Vins et alcools Chevalier	Paul Henriot	26.47.15.10	10248	1996-07-04
Michael Suyama	Toms Spezialitaten	Karin Josephs	0251-031259	10249	1996-07-05
Margaret Peacock	Hanari Carnes	Mario Pontes	(21) 555-0091	10250	1996-07-08
Janet Leverling	Victuailles en stock	Mary Saveley	78.32.54.86	10251	1996-07-08

B)

Which Customers used United Package as shippers during the month of March 1998?

CompanyName	OrderID	OrderDate	CompanyName
Hanari Carnes	11022	1998-04-14	United Package
Folk och fa HB	11050	1998-04-27	United Package
HILARIoN-Abastos	11055	1998-04-28	United Package
Eastern Connection	11056	1998-04-28	United Package
Franchi S.p.A.	11060	1998-04-30	United Package
Hungry Owl All-Night Grocers	11063	1998-04-30	United Package
White Clover Markets	11066	1998-05-01	United Package
Drachenblut Delikatessen	11067	1998-05-04	United Package
Tortuga Restaurante	11069	1998-05-04	United Package

C)

Which Orders IDs contain products where the Category Name is Beverages?

(Partial list displayed).

OrderID	ProductID	ProductName
10253	39	Chartreuse verte
10254	24	Guarana Fantastica
10255	2	Chang
10257	39	Chartreuse verte
10258	2	Chang
10260	70	Outback Lager
10261	35	Steeleye Stout
10263	24	Guarana Fantastica

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