Question

1 Approved Answer

Posted on Oct 23, 2024

Use the ' Premiere Products Company ' SQL script file found in the D2L content folder called 'SQL Scripts'. Paste the contents of this script

Use the 'Premiere Products Company' SQL script file found in the D2L content folder called 'SQL Scripts'.

Paste the contents of this script into a query tab of MySQL Workbench and execute it. This will create the schema named premiere and populate it for you. After running the script, you will no longer need it.

Using MySQL SELECT commands, answer the questions listed below using the premiereschema.

Copy/paste your SQL query and output into your submission for each question. Label each question with the appropriate question number.

Read each question carefully so you don't miss any steps; be sure to answer any accompanying questions for full credit.

Submit your answers via the D2L drop box.

Assignment Details:

1.Using MySQL Workbench, reverse engineer the premiere schema to create the ERD. Add a text box to display your name within the ERD. Include a screenshot of your results here. This ERD is useful to help you determine which tables to use for the remainder of the assignment. (1 point)

2. Write a query to produce the output listed below. Only include customers that have not placed an order. Why do you think these customers have a balance and assigned rep? (4 points)

Premiere Products Company /*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) ,*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) ,Apps 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 ('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 (' KTO3' , '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 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 (' KTO3' , '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) ;customer_num customer_name balance rep_num 842 All Season 8221.00 20 725 Deerfield's Four Seasons 248.00 35 687 Lee's Sport and Appliance 2851.00 35 524 Kline's 12762.00 20 462 Bargains Galore 3412.00 65\fWarehouse units AssetPrice Blue House 9479.40 Big Storage Place 62 52242.75 Little Storage House 113 17055.23rep_num first name last name customer_name Orders 35 Richard Hull Brookings Direct 35 Richard Hull Deerfield's Four Seasons 35 Richard Hull Lee's Sport and Appliance 35 Richard Hull The Everything Shop 20 Valerie Kaiser Al's Appliance and Sport NHOOONHOOP 20 Valerie Kaiser All Season 20 Valerie Kaiser Kline's 65 Juan Perez Bargains Galore 65 Juan Perez Ferguson's 65 Juan Perez Johnson's Department Storerep_num first_name last name customer_name Orders TotalSales 35 Richard Hull Brookings Direct 1190.00 35 Richard Hull The Everything Shop 1319.80 20 Valerie Kaiser Al's Appliance and Sport 736.45 65 Juan Perez Ferguson's 1 894.99 65 Juan Perez Johnson's Department Store 2 4769.90