Her is the code: create database p; use pp; create table customers (customer_id

Question

Her is the code:

create database p;
use pp;

create table customers
(customer_id char(4) primary key,
customer_name char(20),
customer_add char(20),
customer_city char(20),
customer_state char(20),
customer_zip char(20),
customer_phone char(20));

create table items
(item_id char(4) primary key,
Title char(20),
artist char(20),
untit_price decimal(5,2),
on_hand char(3));

create table orders
(order_id char(2) primary key,
customer_id char(4),
order_date char(10),
ship_date char(10));

create table orderline
(order_id char(2),
item_id char(3),
order_qty char(2));

INSERT INTO customers
values
('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', '4090 Caldwell 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');

INSERT INTO items
values
('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);

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);

PRETTY PRINTS II 2 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 Give the SQL commands necessary to perform the following queries 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

Explanation / Answer

/* List of all customers who have placed order in 2014*/

select c.customer_id, customer_name,customer_phone from customers c

inner join orders o on o.customer_id = c.customer_id

where YEAR(order_date) = 2014;

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

select Artist, SUM(order_qty) as 'Number of Prints' from items i

inner join orderline o on i.item_id = o.item_id

group by artist ;

/* List of all titles that have unit price between $40 and $100*/

select Title, untit_price from items

where untit_price BETWEEN 40 AND 100;

/* List all customers, title, artist,quantity ordered*/

select c.customer_id, customer_name,i.Title,i.artist,SUM(order_qty)

from customers c

left join orders o on o.customer_id = c.customer_id

left join orderline ol on ol.order_id = o.order_id

left join items i on i.item_id = ol.item_id

group by customer_id, i.item_id;

/* List of all customer along with total revenue received from customer */

select c.customer_id, customer_name,(SUM(order_qty)* untit_price) as Revenue

from customers c

left join orders o on o.customer_id = c.customer_id

left join orderline ol on ol.order_id = o.order_id

left join items i on i.item_id = ol.item_id

group by customer_id;

/*List each state and customers from that state*/

select customer_state as state, count(customer_id) as 'No of customers' from customers group by customer_state;

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.