Question

Write a SELECT statement that joins the Categories table to the Products table and returns these columns: category_name, product_name, list_price. Sort the result set by category_name and then by product_name, both in ascending sequence. (...feel free to use table alias names to make your SQL statements easier to code and read...) 10 records should display in your result Write a SELECT statement that joins the Customers, Orders, order_items, and Products tables. This statement should return these columns: last_name, first_name, order_date, product_name, item_price, discount_amount, and quantity. Use aliases for the tables. Sort the final result set by last_name, order_date, and product_name. Write a SELECT statement that returns these two columns: category_name The category_name column from the Categories table product_id The product_id column from the Products table Return one row for each category that has never been used. Use table alias names. Use the UNION operator to generate a result set consisting of three columns from the Orders table: The 3 columns will be: If the order has a value in the ship date column, the ship status column should contain a value of SHIPPED. Otherwise, it should contain a value of NOT SHIPPED.

Answer 1

Answer & Explanation:

1) Query:

SELECT Product_Name, Category_Name, List_Price

FROM Products AS P JOIN Categories AS C

ON C.Category_ID = P.Category_ID

ORDER BY Category_Name, Product_Name ASC;

2) Query:

SELECT C.Last_Name, C.First_Name, Order_Date, P.Product_Name, Item_Price, Discount_Amount, Quantity

FROM Customers AS C JOIN Orders AS O

ON C.Customer_ID = O.Customer_ID

JOIN Order_Items AS OI

ON O.Order_ID = OI.Order_ID

JOIN Products AS P

ON OI.Product_ID = P.Product_ID

ORDER BY Last_Name, Order_Date, Product_Name;

3) Query:

SELECT Category_Name, Product_ID

FROM Categories LEFT JOIN Products

ON Categories.Category_ID = Products.Category_ID

WHERE Product_ID IS NULL;

4) Query:

SELECT 'SHIPPED' AS Ship_Status, Order_Id, Order_Date

FROM Orders

WHERE Ship_Date IS NOT NULL

UNION

SELECT 'NOT SHIPPED' AS Ship_Status, Order_ID, Order_Date

FROM Orders

WHERE Ship_Date IS NULL

ORDER BY Order_Date;