Create a report containing Company names and Orders. Must use 2 tables Customers
ID: 3806621 • Letter: C
Question
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;
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
All the questions asked seems to be already answered. If you were looking to get a second view or review on your answers, I have gone through your answers. All of the answers seems right to me. I did not find any solution which won't work in your case. Please let me know if that's not what you needed.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.