create database pp; use pp; create table customers (customer_id char(4) primary

Question

create database pp;
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

Please Note: As the post contain more than One Question, according to the Chegg Answering Guidelines, I have answered the first question and four sub-parts here. For receiving the answers to the rest of the Questions, please Re-post for the other Questions

Answers)

1. List customers along with address, city, state, zip

select customer_add, customer_city, customer_state, customer_zip from customers;

Output:

1555 Seminole Ct.|Charlotte|NC|28210

878 Madison Ave.|Greensboro|NC|27407

4200 Olive Ave.|Columbia|SC|29206

608 Old Post Rd.|Decatur|GA|30030

235 Easton Ave.|Jacksonville|FL|32221

2508 W. Shaw Rd.|Raleigh|NC|27542

200 Pembury Ln.|Columbia|SC|29206

4990 S. Pine St.|Raleigh|NC|27545

1200 Little St.|Columbia|SC|29206

7822 N. Ridge Rd.|Jacksonville|FL|32216

4090 Caldwell St.|Charlotte|NC|28205

3320 W. Main St.|Charlotte|NC|28210

1600 Sardis Rd.|Sarasota|FL|32441

2. List customers, phone numbers that live in GA.

select customer_name, customer_phone from customers where customer_state='GA';

Output:

David Cox|404/243.7379

3. List customers, zip codes that live in NC or SC.

select customer_name, customer_zip from customers where customer_state in ('NC','SC');

Output:

Cora Blanca|28210

Yash Reed|27407

John Mills|29206

Will Allen|27542

James Boyd|29206

Will Parsons|27545

Walter Kelly|29206

Grace Hull|28205

Jane Brown|28210

4. List all titles sold, artist, order date. ship date

SELECT items.Title, items.artist, orders.order_date, orders.ship_date

FROM ((items

INNER JOIN orderline ON items.item_id = orderline.item_id)

INNER JOIN orders ON orderline.order_id = orders.order_id);

Output:

The Seas and Moon|Susan Beeler|2013-10-23|2013-10-28

Rainbow Row|Judy Ford|2013-10-23|2013-10-28

Skies Above|Alexander Wilson|2013-10-30|2013-11-03

Happy Days|Andrea Reid|2013-10-30|2013-11-03

Greek Isles|Benjamin Caudle|2013-11-09|2013-11-14

Dark Lady|Keith Morris|2013-11-15|2013-11-20

Under the Sun|Donald Arley|2013-11-15|2013-11-20

Greek Isles|Benjamin Caudle|2013-11-15|2013-11-20

The Hunt|Walter Alford|2013-11-15|2013-11-20

The Seas and Moon|Susan Beeler|2013-12-15|2013-12-19

Happy Days|Andrea Reid|2013-12-18|2013-12-22

Rainbow Row|Judy Ford|2013-12-18|2013-12-22

Under the Sun|Donald Arley|2013-12-20|2013-12-22

Under the Sun|Donald Arley|2014-01-18|2014-01-23

The Hunt|Walter Alford|2014-01-31|2014-02-04

Rainbow Row|Judy Ford|2014-01-31|2014-02-04

Dark Lady|Keith Morris|2014-01-31|2014-02-04

Skies Above|Alexander Wilson|2014-02-01|2014-02-06

Happy Days|Andrea Reid|2014-02-27|2014-03-02

Skies Above|Alexander Wilson|2014-02-27|2014-03-02

Dark Lady|Keith Morris|2014-03-10|2014-03-15

Rainbow Row|Judy Ford|2014-03-10|2014-03-15

The Hunt|Walter Alford|2014-03-10|2014-03-15

Skies Above|Alexander Wilson|2014-03-14|2014-03-19

Dark Lady|Keith Morris|2014-03-14|2014-03-19

The Seas and Moon|Susan Beeler|2014-03-14|2014-03-19

The Hunt|Walter Alford|2014-03-14|2014-03-19

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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