Programming Question

You will need: Premiere Products Company. I will add a pdf of its ownPlease submit one .sql file containing all queries.
The Premiere Products Company distributes appliances, house wares, and sporting goods. The company
maintains sales representative, customer, order and inventory data. The Premiere Products ERD model is
shown below. The script to create the schema tables and load the data is in D2L. After creating the
schema in MySQL answer the questions below.
1. List the part number and description of each part not in item class SG.
2. For each order placed on October 21, 2007, list the order number along with the number and
name of the customer that placed the order.
3. For each order, list the order number, order date, part number, part description, and item class for
each part that makes up the order. Order the rows by item class and then by order number.
4. For each part, list the part number, description, units on hand, and number of units ordered. All
parts should be included in the results. For those parts that are currently not on order, the order
number and number of units ordered should be left blank. Order the results by part number.
5. List the part number of any part with an unknown description.
/*SQL script to create the PREMIERE database and associated tables and then load the data*/
/*Create the database and switch to it*/
DROP SCHEMA IF EXISTS PREMIERE;
CREATE SCHEMA PREMIERE;
USE PREMIERE;
/*Create REP table*/
CREATE TABLE rep (
rep_num CHAR(2) PRIMARY KEY,
last_name VARCHAR(15) NOT NULL,
first_name VARCHAR(15),
street VARCHAR(15),
city VARCHAR(15),
state CHAR(2),
zip CHAR(5),
commission DECIMAL(7,2),
rate DECIMAL(3,2)
);
/*Create CUSTOMER table*/
CREATE TABLE customer (
customer_num CHAR(3) PRIMARY KEY,
customer_name VARCHAR(35) NOT NULL,
street VARCHAR(15),
city VARCHAR(15),
state CHAR(2),
zip CHAR(5),
balance DECIMAL(8,2),
credit_limit DECIMAL(8,2),
rep_num CHAR(2),
FOREIGN KEY (rep_num) REFERENCES rep(rep_num)
);
/*Create ORDERS table*/
CREATE TABLE orders (
order_num CHAR(5) PRIMARY KEY,
order_date DATE,
customer_num CHAR(3),
FOREIGN KEY (customer_num) REFERENCES customer(customer_num)
);
CREATE TABLE IF NOT EXISTS warehouse (
id INT(11) PRIMARY KEY,
name VARCHAR(45) NOT NULL,
address VARCHAR(100) NOT NULL);
/*Create PART table*/
CREATE TABLE part (
part_num CHAR(4) PRIMARY KEY,
description VARCHAR(15),
on_hand DECIMAL(4,0),
class CHAR(2),
warehouse_id int,
price DECIMAL(6,2),
FOREIGN KEY (warehouse_id) REFERENCES warehouse(id)
);
/*Create ORDER_LINE table*/
CREATE TABLE order_line (
order_num CHAR(5),
part_num CHAR(4),
num_ordered DECIMAL(3,0),
quoted_price DECIMAL(6,2),
PRIMARY KEY (order_num, part_num),
FOREIGN KEY (order_num) REFERENCES orders(order_num),
FOREIGN KEY (part_num) REFERENCES part(part_num)
);
/*Insert data into tables*/
INSERT INTO rep VALUES (’20’,’Kaiser’,’Valerie’,’624 Randall’,’Grove’,’FL’,’33321′,20542.50,0.05);
INSERT INTO rep VALUES (’35’,’Hull’,’Richard’,’532 Jackson’,’Sheldon’,’FL’,’33553′,39216.00,0.07);
INSERT INTO rep VALUES (’65’,’Perez’,’Juan’,’1626 Taylor’,’Fillmore’,’FL’,’33336′,23487.00,0.05);
INSERT INTO customer VALUES (‘148′,’Al”s Appliance and Sport’,’2837 Greenway’,’Fillmore’,’FL’,’33336′,6550.00,7500.00,’20’);
INSERT INTO customer VALUES (‘282′,’Brookings Direct’,’3827 Devon’,’Grove’,’FL’,’33321′,431.50,10000.00,’35’);
INSERT INTO customer VALUES (‘356′,’Ferguson”s’,’382 Wildwood’,’Northfield’,’FL’,’33146′,5785.00,7500.00,’65’);
INSERT INTO customer VALUES (‘408′,’The Everything Shop’,’1828 Raven’,’Crystal’,’FL’,’33503′,5285.25,5000.00,’35’);
INSERT INTO customer VALUES (‘462′,’Bargains Galore’,’3829 Central’,’Grove’,’FL’,’33321′,3412.00,10000.00,’65’);
INSERT INTO customer VALUES (‘524′,’Kline”s’,’838 Ridgeland’,’Fillmore’,’FL’,’33336′,12762.00,15000.00,’20’);
INSERT INTO customer VALUES (‘608′,’Johnson”s Department Store’,’372 Oxford’,’Sheldon’,’FL’,’33553′,2106.00,10000.00,’65’);
INSERT INTO customer VALUES (‘687′,’Lee”s Sport and Appliance’,’282 Evergreen’,’Altonville’,’FL’,’32543′,2851.00,5000.00,’35’);
INSERT INTO customer VALUES (‘725′,’Deerfield”s Four Seasons’,’282 Columbia’,’Sheldon’,’FL’,’33553′,248.00,7500.00,’35’);
INSERT INTO customer VALUES (‘842′,’All Season’,’28 Lakeview’,’Grove’,’FL’,’33321′,8221.00,7500.00,’20’);
INSERT INTO orders VALUES (‘21608′,’2007-10-20′,’148’);
INSERT INTO orders VALUES (‘21610′,’2007-10-20′,’356’);
INSERT INTO orders VALUES (‘21613′,’2007-10-21′,’408’);
INSERT INTO orders VALUES (‘21614′,’2007-10-21′,’282’);
INSERT INTO orders VALUES (‘21617′,’2007-10-23′,’608’);
INSERT INTO orders VALUES (‘21619′,’2007-10-23′,’148’);
INSERT INTO orders VALUES (‘21623′,’2007-10-23′,’608′);
INSERT INTO warehouse VALUES (1,’Blue House’,’123 Main Street’);
INSERT INTO warehouse VALUES (2,’Big Storage Place’,’876 Baker Street’);
INSERT INTO warehouse VALUES (3,’Little Storage House’,’76 Elm Avenue’);
INSERT INTO part VALUES (‘AT94′,’Iron’,50,’HW’,3,24.95);
INSERT INTO part VALUES (‘BV06′,’Home Gym’,45,’SG’,2,794.95);
INSERT INTO part VALUES (‘CD52′,’Microwave Oven’,32,’AP’,1,165.00);
INSERT INTO part VALUES (‘DL71′,’Cordless Drill’,21,’HW’,3,129.95);
INSERT INTO part VALUES (‘DR93′,’Gas Range’,8,’AP’,2,495.00);
INSERT INTO part VALUES (‘DW11′,’Washer’,12,’AP’,3,399.99);
INSERT INTO part VALUES (‘FD21′,’Stand Mixer’,22,’HW’,3,159.95);
INSERT INTO part VALUES (‘KL62′,’Dryer’,12,’AP’,1,349.95);
INSERT INTO part VALUES (‘KT03′,’Dishwasher’,8,’AP’,3,595.00);
INSERT INTO part VALUES (‘KV29′,’Treadmill’,9,’SG’,2,1390.00);
INSERT INTO order_line VALUES (‘21608′,’AT94’,11,21.95);
INSERT INTO order_line VALUES (‘21610′,’DR93’,1,495.00);
INSERT INTO order_line VALUES (‘21610′,’DW11’,1,399.99);
INSERT INTO order_line VALUES (‘21613′,’KL62’,4,329.95);
INSERT INTO order_line VALUES (‘21614′,’KT03’,2,595.00);
INSERT INTO order_line VALUES (‘21617′,’BV06’,2,794.95);
INSERT INTO order_line VALUES (‘21617′,’CD52’,4,150.00);
INSERT INTO order_line VALUES (‘21619′,’DR93’,1,495.00);
INSERT INTO order_line VALUES (‘21623′,’KV29’,2,1290.00);