I have the following tables Table: Customers Columns: CustomerID, CustomerName,
ID: 3853294 • Letter: I
Question
I have the following tables
Table: Customers
Columns: CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country
Table: Categories
Columns: CategoryID, CategoryName, Description
Table: Employees
Columns: EmployeeID, LastName, FirstName, BirthDate, Photo, Notesolumns:
Table: OrderDetails
Columns: OrderDetailID, OrderID, ProductID, Quantity
Table: Orders
Columns: OrderID, CustomerID, EmployeeID, OrderDate, ShipperID
Table: Products
Columns: ProductID, ProductName, SupplierID, CategoryID, Unit , Price
Table: Shippers
Columns: ShipperID, ShipperName, Phone
Table: Suppliers
Columns: SupplierID, SupplierName, ContactName, Address, City, PostalCode, Country, Phone
Write queries using the above tables
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
a. Show tables that cross join tables Products and Categories
B. Find all the customers whose address contains “Po”
C. Make a single SQL statement that shows Product Name, Price, Supplier Name, Category Name and Description (hint need to join three tables)
D. say for For ProductId 14, find the total quantity ordered for such product. Display the title of this number as “Sum of Quantity”. You should use SQL aggregation on OrderDetails.
E. Find the maximum and minimum price across all Categories in Products
F. Find the productname and price whose category name is Seafood using subquery
G. Use an inner join to find orderID. shipperID and Shipper name for orders whose ID is less than 10255. Order the result by orderID
H. Display employees ID 9 and 10, and any orders they might have made. Order your result by employees ID in decreasing order. (hint: this need left join)
I. Change all Country UK to United Kingdom, take screen shot to show all the changes. Show the commands used
J. Find ProductID from OrderDetails when theses order are via ShipperID 3. Use subquery. ProductID should not have duplicates. Order your result in increasing order.
Explanation / Answer
b.
SELECT * FROM Customers
WHERE Address LIKE "%Po%";
c.
SELECT P.ProductName, P.Price,S.SupplierName,C.CategoryName,C.Description
FROM Products P,Suppliers S,Categories C
WHERE P.SupplierID=S.SupplierID AND P.CategoryID=C.CategoryID;
d.
SELECT ProductID,sum(Quantity) as Sum of Quantity
FROM Products
WHERE ProductID=14;
e.
SELECT max(Price),min(Price)
FROM Products;
f.
SELECT ProductName,Price
FROM Products
WHERE CategoryID IN(SELECT CategoryID
FROM Categories
WHERE CategoryName="Seafood");
g.
SELECT O.OrderID,S.ShipperID,S.ShipperName
FROM Orders O
INNER JOIN Shippers S ON O.ShipperID=S.ShipperID
WHERE O.OrderID<10255;
h.
SELECT C.CustomerID O.OrderID
FROM Customers C
LEFT JOIN Orders O ON C.CustomerID=O.CustomerID
WHERE C.CustomerID IN(9,10)
ORDER BY C.CustomerID DESC;
i.
UPDATE Customers
SET Country="United Kingdom"
WHERE Country="UK";
UPDATE Suplliers
SET Country="United Kingdom"
WHERE Country="UK";
j.
SELECT DISTINCT ProductID
FROM OrderDetails
WHERE OrderID IN(SELECT OrderID
FROM Orders
WHERE ShipperID=3)
ORDER BY ProductID;