SQL statementsshould be created to executea set of queries that the Pretty Print
ID: 3871284 • Letter: S
Question
SQL statementsshould be created to executea set of queries that the Pretty PrintsCompany executes each month to better manage their business. Use the database created for PrettyPrints that was created in Pretty Prints I.The SQL statements can be created within the MySQL Workbench.
5. List all item id, title, artist, unit price, and on hand in ascending order by price
6. List all item id, title, artist, unit price, and on hand for all items with a unit price that is more than $100.00
7. List all item id, title, artist, unit price, and on hand for all items where there are more than 300 on hand
8. List all titles along with their unit price and retail price (retail price is unit price doubled)
9. List all customers that have placed an order in 2014 along with their phone numbers
10. List all artists with the number of their prints that have been sold
11. List all titles that have a unit price between $40.00 and $100.00.
12. List all customers, title, artist, quantity ordered
13. List all customers along with the total revenue received from that customer (revenue would be total retail price)
14. List each state and the number of customers from that state
TABLES customers customer id |customer name customer-add customer cit 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000 2100 2200 Cora Blanca Yash Reed John Mills David Cox lina Evans Will Allern James Boyd Will Parsons Walter Kelly Ann Damian Grace Hull Jane Brown Betty Draper 1555 Seminole Ct. Charlotte 878 Madison Ave. Greensboro 4200 Olive Ave 608 Old Post Rd 235 Easton Ave 2508 W. Shaw Rd. Raleigh 200 Pembury Ln 4990 S. Pine St. 1200 Little St. 7822 N. Ridge Rd. Jacksonville 4090Caldweld St. Charlotte 3320 W. Main St. Charlotte 1600 Sardis Rd customer statecustomer zipcustomer phone 28210 27407 29206 30030 32221 27542 29206 27545 29206 32216 28205 28210 32441 NC NC SC GA FL NC SC NC SC FL NC NC FL 704/552.1810 336/316-5434 803/432.6225 404/243.7379 904/992-7234 919/809.2545 803/432-7600 919/355/0034 803/432-1987 904/725-4672 704/365.7655 704/372/9000 918/941-9121 Columbia Decatu Jacksonville Columbia Raleigh Columbia SarasotaExplanation / Answer
Please find below the required SQL queries.
5. SELECT item_id. title, artist, unit_price, on_hand FROM items ORDER BY unit_price;
6. SELECT item_id. title, artist, unit_price, on_hand FROM items WHERE unit_price > 100;
7. SELECT item_id. title, artist, unit_price, on_hand FROM items WHERE on_hand > 300;
8. SELECT title, unit_price, unit_price*2 AS retail_price FROM items;
9. SELECT c.customer_id, c.customer_name, c.customer_phone FROM customers c, orders o WHERE c.customer_id = o.customer_id AND o.order_date like "2014-%";
10. SELECT i.artist, sum(ol.order_qty) FROM items i, orderline ol WHERE i.item_id = ol.item_id GROUP BY i.artist;
11. SELECT title FROM items WHERE unit_price BETWEEN 40 AND 100;
12. SELECT c.customer_name, i.title, i.artist, ol.order_qty FROM cutomers c, items i, orders o, orderline ol WHERE c.customer_id = o.customer_id AND o.order_id = ol.order_id AND i.item_id = ol.item_id;
13. SELECT c.customer_id, c.customer_name, i.unit_price*2 AS total_revenue WHERE customer c, items i, orderd o, orderline ol WHERE c.customer_id = o.customer_id AND o.order_id = ol.order_id AND i.item_id = ol.item_id GROUP BY c.customer_id;
14. SELECT customer_state, COUNT(customer_id) FROM customers GROUB BY customer_state;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.