Homework3
Coding and Database Managment
SQL - Homework 3
The following queries use the classicmodels schema.
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.
List all the order number and status for all the orders containing the 1940 Ford Pickup Truck.
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.
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’.
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.
Repeat the above query but only return the order number for the orders containing products sold at a discount.
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.
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.
Report product code, name and MSRP for all products that have not sold any units.