Question
Just answer A , B , C include your answers with screen shots of the results. Create a report containing Company names and Orders. Must
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
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started