ASSIGNMENT DESCRIPTION: SQL statements should be created to execute a set of queries that the Pretty Prints Company executes each month to better manage their business. Use the database created for Pretty Prints that was created in Pretty Prints I. The SQL statements can be created within the MySQL Workbench.

Questions

List all customers along with the total revenue received from that customer (revenue would be total retail price)

List all artists with the number of their prints that have been sold

List all customers, title, artist, quantity ordered

Pretty Prints I Database

create DATABASE prints; use prints; Create table customers(customer_id integer,customer_name varchar(25),customer_add varchar(75),customer_city varchar(20),customer_state varchar(20),customer_zip integer,customer_phone varchar(20)); Create table items(item_id integer,Title varchar(50),artist varchar(50),unit_price float,on_hand integer); Create table orders(order_id integer,customer_id integer,order_date date,ship_date date); Create table orderline(order_id integer,item_id integer,order_qty integer);

INSERT INTO customers VALUES (1000,'Cora Blanca','155 Seminole Ct.','Charlotte','NC',28210,'704/552.1810'), (1100,'Yash Reed','878 Madison Ave.','Greensboro','NC',27407,'704/552.1810'), (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.','Ralleigh','NC',27542,'919/809-2545'), (1600,'James Boyd','200 Pembury Ln.','Columbia','SC',29206,'803/432.1987'), (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','4090 Caldweld 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/9419121');

INSERT INTO items VALUES (100,'Under the sun','Donald Artey',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);

INSERT INTO orders VALUES (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');

INSERT INTO orderline VALUES (1,800,2), (1,600,1), (2,700,3), (2,300,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);

SELECT * from customers; SELECT * from items; SELECT * from orders; SELECT * from orderline;