1. Write an SQL command to display the order number, customer number, order date
ID: 3770056 • Letter: 1
Question
1. Write an SQL command to display the order number, customer number, order date, and items ordered for order number 1001.
2. Write an SQL command to display each item ordered for order number 1001, its standard price, and total price for each item ordered.
3. Write an SQL command to total cost of order number 1001.
4. Write an SQL command that will find any customers who have not placed orders.
5. Write an SQL command to produce a list of all the products (i.e., product description) and number of times each product has been ordered.
6. Write an SQL command to display customer number, name, and order number for all customers and their orders.
7. Write an SQL command to list the order number and order quantity for all customer order for which the order quantity is greater than the average order quantity of that product. Hint: This involves a correlated subquery.
You need to print out the SQL command you used for each query and the result (table) you obtained.
Explanation / Answer
1) Write an SQL command to display the order number, customer number, order date, and items ordered for order number 1001 Answer select ol.order_id, o.customer_id, o.order_date, ol.product_id from order_t o, order_line_t ol where ol.order_id = o.order_id and ol.order_id = 1001; order_id | customer_id | order_date | product_id ----------+-------------+------------+------------ 1001 | 1 | 2000-10-21 | 1 1001 | 1 | 2000-10-21 | 2 1001 | 1 | 2000-10-21 | 4 (3 rows) 2) Write an SQL command to display each item ordered for order number 1001, its standard price, and total price for each item ordered. Answer select ol.order_id, ol.product_id, ol.ordered_quantity, p.standard_price, (p.standard_price * ol.ordered_quantity) as totalprice from order_line_t ol, product_t p where ol.product_id = p.product_id and order_id = 1001; order_id | product_id | ordered_quantity | standard_price | totalprice ----------+------------+------------------+----------------+------------ 1001 | 1 | 2 | 175.00 | 350.00 1001 | 2 | 2 | 200.00 | 400.00 1001 | 4 | 1 | 650.00 | 650.00 (3 rows) 3) Write an SQL command to total cost of order number 1001 Answers select sum(p.standard_price * ol.ordered_quantity) as totalprice from order_line_t ol, product_t p where ol.product_id = p.product_id and order_id = 1001; totalprice ------------ 1400.00 (1 row) 4) Write an SQL command that will find any customers who have not placed orders. Answer select c.customer_id from customer_t c where c.customer_id not in (select distinct o.customer_id from order_t o); order_id | totalprice ----------+------------ 1001 | 1400.00 1002 | 1875.00 1003 | 1125.00 1004 | 2000.00 1005 | 1950.00 1006 | 2900.00 1007 | 925.00 1008 | 1875.00 1009 | 3700.00 1010 | 2500.00 (10 rows) 5) Write an SQL command to produce a list of all the products (i.e., product description) and number of times each product has been ordered. select p.product_description from product_t p, order_line_t ol where p.product_id order by p.product_description; select p.product_description, count(ol.product_id) as numorders from order_line_t ol, product_t p where ol.product_id = p.product_id group by p.product_description order by p.product_description; product_description | numorders ----------------------+----------- 8-Drawer Desk | 1 Coffe Table | 2 Computer Desk | 6 Dining Table | 2 End Table | 2 Entertainment Center | 4 Writers Desk | 1 (7 rows)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.