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

1.Select customer_id,customer_name,customer_add,customer_city,customer_state,customer_zip from customers;

2.Select customer_id,customer_name,customer_phone from customers where customer_state = 'GA';

3.Select customer_id,customer_name,customer_zip from customers where customer_state IN ('NC', 'SC');

4. Select items.Title,items.artist,orders.order_date,orders.ship_date from items, orders, orderline where orders.order_id = orderline.order_id AND orderline.item_id = items.item_id ;

5. Select * from items order by untit_price;

6. Select * from items where untit_price>100;

7.Select * from items where on_hand>300;

8. SELECT Title, untit_price, untit_price * 2 AS retail_price
FROM items;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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