Just answer A , B , C include your answers with screen shots of the results. Cre
ID: 3808103 • Letter: J
Question
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 CustomerID’s 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 ID’s 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
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
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 ShipCountryExplanation / Answer
A. we use || for concatenating 2 columns of a table and use as keyword to rename table column
select e.firstName||' '||e.lastName as Employee,c.companyName ,c.contactName,c.phone as customerPhone,o.OrderID,o.OrderDate
from Employees e,Customers c,Orders o
where e.employeeID=o.employeeID and
c.customerID=o.customerID
B.
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 AND
S.CompanyName ="United Package" AND
O.OrderDate BETWEEN '1998-04-01' AND '1998-04-31'
ORDER BY C.CompanyName;
c.
no product information availiable in the given Class diagram.Please provide the table definition of related tables for productID,category
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.