Use the above schema to create your SQL queries for the following questions 1. Y
ID: 3770553 • Letter: U
Question
Use the above schema to create your SQL queries for the following questions
1. Your manager needs a list of order IDs that contain the most expensive beverage and the revenue the most expensive beverage brought in on each order (i.e., do NOT include other products on the order in your revenue total). He only wants orders in the list where the most expensive beverage generated more than $10,000. HINT: ‘most expensive’ should tip you off that you need a nested query. Find the price of the most expensive beverage first, then translate it to a product ID. From there calculate the total revenue that the most expensive product generated on each order. For this problem, you’ll definitely want to be a groupby.
QUERY:
RESULTS:
2. Modify your query in #4 to create a list of order IDs and the total revenue for the entire order (i.e., use all products on that order). All other conditions in #4 hold.
QUERY:
RESULTS:
Explanation / Answer
Table creation
create table OrderDetails(
OrderId varchar(50),
ProductId varchar(50),
UnitPrice int,
Quantity int,
Discount decimal(2,3));
create table products(
productid varchar(50),
productname varchar(50),
supplierid varchar(50),
categoryid varchar(50),
Quantityperunit varchar(50),
untiprice int,
unitsinstock int,
unitsonorder varchar(50),
reorderlevel varchar(50),
discounted varchar(50));
Query1
select od.OrderId,p.productid,p.productname,((UnitPrice*Discount)*Quantity) as revenue from OrderDetails od,products p where
od.productid=p.productid and
revenue>$10,000.
Query 2:
select OrderId||'#'||((UnitPrice*Discount)*Quantity) as totalrevenue from OrderDetails;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.