Details of this Paper

ASSIGNMENT 5 Pretty Prints

Description

solution


Question

ASSIGNMENT DESCRIPTION: In assignment 5, a script file should be created to contain a set of queries that the Pretty Prints Company executes each month to better manage their business. Use the database created for Print Prints that was created in Assignment 4. The script file can be created within the MySQL Workbench. Any SQL commands used to prepare the database for queries must be included in the script.;The following queries should be included in the script;1. List all customer along with their address, city, state and zip;2. List all customers and their phone numbers that live in GA;3. List all customers along with their zip codes that live in NC or SC;4. List all titles that have been sold along with the artist, order date and ship date;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;REQUIREMENTS;? Each student must work independently on this assignment. No group work is allowed.;? Submit completed work into Moodle2 under the Final Exam icon.;? Take a screenshot of the submission verification in the event you need proof of submission.;ASSIGNMENT DESCRIPTION: A company named Pretty Prints is in the business of selling lithograph;prints created by local artists.;When an artist creates a lithograph, s(he) creates several hundred prints from it The company needs a;database to manage orders. You are to create a database for this purpose and enter the existing data. The;database should be named prints and is composed of the tables below.;You are asked to do the following;Download the mySQL database management system along with the mySQL workbench. Also download connector/J.;After the download you are to create a database named prints which is composed of these tables.;TABLES;customers;customer_id customer_name customer-add customer_city customer_state customer_zip customer_phone;1000 Cora Blanca 1555 Seminole Ct. Charlotte NC 28210 704/552.1810;1100 Yash Reed 878 Madison Ave. Greensboro NC 27407 336/316-5434;1200 John Mills 4200 Olive Ave. Columbia SC 29206 803/432.6225;1300 David Cox 608 Old Post Rd. Decatur GA 30030 404/243.7379;1400 Tina Evans 235 Easton Ave. Jacksonville FL 32221 904/992-7234;1500 Will Allen 2508 W. Shaw Rd. Raleigh NC 27542 919/809.2545;1600 James Boyd 200 Pembury Ln. Columbia SC 29206 803/432-7600;1700 Will Parsons 4990 S. Pine St. Raleigh NC 27545 919/355/0034;1800 Walter Kelly 1200 Little St. Columbia SC 29206 803/432-1987;1900 Ann Damian 7822 N. Ridge Rd. Jacksonville FL 32216 904/725-4672;2000 Grace Hull 4090Caldweld St. Charlotte NC 28205 704/365.7655;2100 Jane Brown 3320 W. Main St. Charlotte NC 28210 704/372/9000;2200 Betty Draper 1600 Sardis Rd. Sarasota FL 32441 918/941-9121;items;item_id title artist unit_price on_hand;100 Under the Sun Donald Arley 46.80 340;200 Dark Lady Keith Morris 120.99 250;300 Happy Days Andrea Reid 78.00 210;350 Top of the Mountain Janice Jones 110.00 290;400 Streets from Old Sharon Brune 123.00 320;450 The Hunt Walter Alford 39.99 390;600 Rainbow Row Judy Ford 46.00 350;700 Skies Above Alexander Wilson 98.00 275;800 The Seas and Moon Susan Beeler 67.81 235;850 Greek Isles Benjamin Caudle 76.00 300;orders;order_id customer_id order_date ship_date;1 1200 2013-10-23 2013-10-28;2 1500 2013-10-30 2013-11-03;3 1500 2013-11-09 2013-11-14;4 2100 2013-11-15 2013-11-20;5 1600 2013-11-15 2013-11-20;6 1900 2013-12-15 2013-12-19;7 2200 2013-12-18 2013-12-22;8 1600 2013-12-20 2013-12-22;9 1000 2014-01-18 2014-01-23;10 2200 2014-01-31 2014-02-04;11 1500 2014-02-01 2014-02-06;12 1400 2014-02-27 2014-03-02;13 1100 2014-03-10 2014-03-15;14 1400 2014-03-14 2014-03-19;orderline;order_id item_id order_qty;1 800 2;1 600 1;2 700 3;2 399 2;3 850 1;4 200 4;4 100 1;4 850 1;5 450 1;6 800 2;7 300 2;7 600 2;8 100 1;9 100 3;10 450 6;10 600 8;10 200 4;11 700 2;12 300 3;12 700 4;13 200 2;13 600 10;13 450 4;14 700 8;14 200 6;14 800 4;14 450 2;REQUIREMENTS;Each student must work independently on this assignment. No group work is allowed. ?;Submit a screenshot of the SQL commands that you used to create the database and also the database created. ?;Submit completed work into Moodle2. ?;Take a screenshot of the submission verification in the event you need proof of submission.

 

Paper#68313 | Written in 18-Jul-2015

Price : $32
SiteLock