Homework3 solution

Coding and Database Managment

SQL - Homework 3 - solution

The following queries use the classicmodels schema.

Source: https://www.richardtwatson.com/

1. Report product number, product name of products sold by order date. Order results by date (descending) and product code (ascending). Use both the USING and ON syntax for the join. Observe their different behavior.

SELECT productCode, productName, orderDate
 FROM (orders JOIN orderdetails USING(orderNumber)) JOIN products USING (productCode) 
 ORDER BY orderDate DESC, productCode; 

SELECT products.productCode, productName, orderDate
 FROM orders JOIN orderdetails JOIN products 
  ON orders.orderNumber = orderdetails.orderNumber
  AND orderdetails.productCode = products.productCode
 ORDER BY orderDate DESC, productCode;

2. List all the order number and status for all the orders containing the 1940 Ford Pickup Truck.

SELECT orderNumber, status 
FROM orders JOIN orderdetails USING(orderNumber) JOIN products USING(productCode)
 WHERE productName = '1940 Ford Pickup Truck';  

3. List the names of customers and the corresponding order numbers for those orders from that customer that have a total value greater than $25,000? Total value is obtained by adding the price times quantity sold of all products in the order.

SELECT customerName, orderNumber
FROM customers JOIN orders USING(customerNumber) JOIN orderdetails USING(orderNumber) 
GROUP BY customerName, orderNumber
HAVING SUM(priceEach*quantityOrdered) > 25000
ORDER BY SUM(priceEach*quantityOrdered);
    

SELECT customerName, orderNumber
 FROM customers JOIN orders USING(customerNumber)
 WHERE orderNumber IN (SELECT orderNumber 
                        FROM orders JOIN orderdetails USING(orderNumber)
                        GROUP BY orderNumber
                        HAVING SUM(priceEach * quantityOrdered) > 25000);
    

4. Are there any products that appear on all orders? If so, list their product code and the total number of orders they appear in. Rename the columns as ‘Total Orders’.

SELECT products.productCode, COUNT(*) as 'Total Orders'
 FROM products JOIN orderdetails ON products.productCode = orderdetails.productCode
 GROUP BY products.productCode
 HAVING COUNT(DISTINCT orderNumber) = (SELECT COUNT(DISTINCT orderNumber) FROM orders);

5. Report those orders containing products sold at a discount (i.e., sold at a price less than the product’s MSRP). For these orders report the order number, the product code of the items sold at discount and both the price and the MSRP.

SELECT orderNumber, productCode, priceEach, MSRP
 FROM orderdetails JOIN products USING(productCode)
 WHERE priceEach < MSRP;    

6. Repeat the above query but only return the order number for the orders containing products sold at a discount. Use a subquery.

SELECT DISTINCT orderNumber
 FROM orderdetails
 WHERE priceEach < (SELECT MSRP FROM products
                    WHERE orderdetails.productCode = products.productCode); 

7. Report those products that have been sold with a markup of 100% or more (i.e., the priceEach is at least twice the buyPrice) on at least one sale. Provide the product code, the price at which it sold, the buy price and the MSRP. Be sure not to duplicate output unnecessarily.

SELECT DISTINCT productCode, priceEach, buyPrice, MSRP
 FROM orderdetails JOIN products USING(productCode)
 WHERE priceEach > 2 * buyPrice;    

8. What is the total quantity on hand for products listed in orders with staus: ‘On Hold’? Rename the quantity ordered as ‘Total Ordered’ and the quantity in stock as ‘Total on Hand’. Use the ON syntax for the JOIN.

SELECT products.productCode, SUM(quantityOrdered) AS 'Total Ordered', quantityInStock AS 'On Hand'
 FROM orders JOIN orderdetails JOIN products
  ON orders.orderNumber = orderdetails.orderNumber
  AND orderdetails.productCode = products.productCode
 WHERE status = 'On Hold'
 GROUP BY products.productCode;
    

9. Report product code, name and MSRP for all products that have not sold any units.

SELECT productCode, productName, MSRP
 FROM products
 WHERE productCode NOT IN (SELECT DISTINCT productCode FROM orderdetails);  

© 2025. All Rights Reserved.

Enabled by Dr. Marcin Bartosiak