SQL Lab #5

Turn In: SQL Statements and the output from those statements.

Due: March 10, 2010

CREATE AND POPULATE TABLES FOR HOME WORK:

CREATE TABLE salesrep
(slsrep_nr INTEGER NOT NULL,
lname VARCHAR(30) NOT NULL,
fname VARCHAR(30) NOT NULL,
street VARCHAR(25),
city VARCHAR(25),
state CHAR(3),
zip_code CHAR(10),
total_commission DECIMAL(7,2),
commission_rate DECIMAL(7,2));

INSERT INTO salesrep
VALUES (2, 'BROWN', 'WILLY', 'CITY HALL', 'SAN FRANCISCO', 'CA', '94123', 0, .08);
INSERT INTO salesrep
VALUES (3, 'JONES', 'MARY', '123 MAIN', 'GRANT', 'MI', '49219', 2150.00, .05);
INSERT INTO salesrep
VALUES (6, 'SMITH', 'WILLIAM', '102 RAYMOND', 'ADA', 'MI', '49224', 4912.50,.07);
INSERT INTO salesrep
VALUES (12,'DIAZ' , 'MIGUEL', '419 HARPER', 'LANSING', 'MI', '49224', 2150.00, .05);

CREATE TABLE part (part_number INTEGER, part_description VARCHAR(50),
units_on_hand INTEGER, item_class ENUM('HW', 'SG', 'AP'),
warehouse_number INTEGER, unit_price DECIMAL(9,2));

INSERT INTO part (part_number, part_description, units_on_hand,
item_class, warehouse_number, unit_price)
VALUES(1, 'IRON',104,'HW', 3, 24.95);

INSERT INTO part (part_number, part_description, units_on_hand,
item_class, warehouse_number, unit_price)
VALUES(2, 'DARTBOARD',20,'SG', 2,12.95);

INSERT INTO part (part_number, part_description, units_on_hand,
item_class, warehouse_number, unit_price)
VALUES(3, 'BASKETBALL',40,'SG', 1, 29.95);

INSERT INTO part (part_number, part_description, units_on_hand,
item_class, warehouse_number, unit_price)
VALUES(4, 'CORNPOPPER',95,'HW', 3,24.95);

INSERT INTO part (part_number, part_description, units_on_hand,
item_class, warehouse_number, unit_price)
VALUES(5, 'GAS GRILL',11,'AP', 2,149.99);

INSERT INTO part (part_number, part_description, units_on_hand,
item_class, warehouse_number, unit_price)
VALUES(6, 'WASHER',52,'AP', 3,399.99);

INSERT INTO part (part_number, part_description, units_on_hand,
item_class, warehouse_number, unit_price)
VALUES(7, 'GRIDDLE',78,'HW', 3,39.99);

INSERT INTO part (part_number, part_description, units_on_hand,
item_class, warehouse_number, unit_price)
VALUES(8, 'BIKE',44,'SG', 1 , 299.99);

INSERT INTO part (part_number, part_description, units_on_hand,
item_class, warehouse_number, unit_price)
VALUES(9, 'BLENDER',112,'HW', 3, 22.95);

INSERT INTO part (part_number, part_description, units_on_hand,
item_class, warehouse_number, unit_price)
VALUES(10, 'TREADMILL',68,'SG', 2,349.95);

create table customer
(
customer_number INTEGER PRIMARY KEY AUTO_INCREMENT,
lname VARCHAR(50),
fname VARCHAR(50),
street VARCHAR(50),
city VARCHAR(50),
state CHAR(3),
zip_code CHAR(10),
balance DECIMAL(10,2),
credit_limit DECIMAL(10,2),
slsrep_number DECIMAL(3)
);

insert into customer (customer_number, lname, fname, street, city, state, zip_code, balance, credit_limit, slsrep_numbeR)
values (1, 'Adams', 'John', '123 Main', 'Alameda', 'CA', '94501', 500, 1000, 3);

insert into customer (customer_number, lname, fname, street, city, state, zip_code, balance, credit_limit, slsrep_numbeR)
values (2, 'Bentson', 'Lloyd', '12 Main', 'Reno', 'NV', '54321', 750, 1500, 6);

insert into customer (customer_number, lname, fname, street, city, state, zip_code, balance, credit_limit, slsrep_numbeR)
values (3, 'Tiger', 'Scott', '1 Oracle Way', 'Redwood Shores', 'CA', '94123', 750, 1500, 12);

insert into customer (customer_number, lname, fname, street, city, state, zip_code, balance, credit_limit, slsrep_numbeR)
values (4, 'Wilde', 'Paul', '3 Market', 'San Francisco', 'CA', '94105', 750, 7000, 3);

insert into customer (customer_number, lname, fname, street, city, state, zip_code, balance, credit_limit, slsrep_numbeR)
values (5, 'Test', 'Bob', '123 Bankrupt', 'Las Vegas', 'NV', '54321', 1750, 1500, 6);

insert into customer (customer_number, lname, fname, street, city, state, zip_code, balance, credit_limit, slsrep_numbeR)
values (6, 'Test', 'John', '123 Bankrupt', 'Las Vegas', 'NV', '54321', 1750, 1500, 12);

CREATE TABLE orders (order_number INTEGER PRIMARY KEY AUTO_INCREMENT, order_date DATE, customer_number INTEGER);

INSERT INTO orders VALUES (1, SYSDATE(), 3);

INSERT INTO orders VALUES (2, SYSDATE(), 2);

INSERT INTO orders VALUES (3, SYSDATE(), 1);

INSERT INTO orders VALUES (4, SYSDATE(), 4);

INSERT INTO orders VALUES (5, SYSDATE(), 3);

INSERT INTO orders VALUES (6, SYSDATE(), 2);

CREATE TABLE order_line (order_number INTEGER, part_number INTEGER, number_ordered INTEGER, quoted_price DECIMAL(8,2));

INSERT INTO order_line (order_number, part_number, number_ordered, quoted_price)
VALUES (1,  1, 2, 100);
INSERT INTO order_line (order_number, part_number, number_ordered, quoted_price)
VALUES (2, 2, 10, 90);
INSERT INTO order_line (order_number, part_number, number_ordered, quoted_price)
VALUES (3, 3, 5, 75);
INSERT INTO order_line (order_number, part_number, number_ordered, quoted_price)
VALUES (4, 2, 6, 89);
INSERT INTO order_line (order_number, part_number, number_ordered, quoted_price)
VALUES (5, 5, 7, 77);
INSERT INTO order_line (order_number, part_number, number_ordered, quoted_price)
VALUES (1, 6, 7, 72);

Lab Questions to turn in:

1.  Use a UNION query to produce a list of last & first names of all salesrep and customers.

2.  List all customers that have not yet made a purchase.  Most Efficient Sub Query.

3.  Use a nested sub query to List all customers that have purchased a item_class of 'AP'.

4.  Delete all customers that have not bought anything from us.

5.  Add the column date_ordered to the part table and set it up with a default date of the current system date.