Open MySQL Workbench and access your local database server.Run the below provided mailorder.sql script to define the mailorder database. After verifying that you have created all the tables successfully,construct the mailorder database by populating the tables with the data below. (Hint: To avoid retyping the data, you should be able to copy-and-paste the values.) Save your DML statements into the script

-- ------------------------ -- mailorder.sql -- ------------------------

-- The DROP statement below will delete your existing mailorder schema (if you have one) DROP SCHEMA IF EXISTS mailorder;

-- Define the mailorder schema CREATE SCHEMA mailorder; USE mailorder;

-- Define the tables for the mailorder schema CREATE TABLE zipcodes ( zip INT(5), city VARCHAR(30) NOT NULL, PRIMARY KEY (zip) ); CREATE TABLE employees ( eno SMALLINT UNSIGNED NOT NULL, ename VARCHAR(30), zip INT(5), hdate DATE, PRIMARY KEY (eno), CONSTRAINT empzipFK FOREIGN KEY (zip) REFERENCES zipcodes(zip) ); CREATE TABLE parts ( pno SMALLINT UNSIGNED NOT NULL, pname VARCHAR(30), qoh SMALLINT UNSIGNED CHECK (qoh >= 0), price FLOAT(6,2) CHECK (price >= 0.0), olevel SMALLINT UNSIGNED, PRIMARY KEY (pno) ); CREATE TABLE customers ( cno SMALLINT UNSIGNED NOT NULL, cname VARCHAR(30), street VARCHAR(30), zip INT(5), phone CHAR(12), PRIMARY KEY (cno), CONSTRAINT custzipFK FOREIGN KEY (zip) REFERENCES zipcodes (zip) ON DELETE RESTRICT ON UPDATE CASCADE ); CREATE TABLE orders ( ono SMALLINT UNSIGNED NOT NULL, cno SMALLINT UNSIGNED NOT NULL, eno SMALLINT UNSIGNED, received DATE, shipped DATE, PRIMARY KEY (ono), CONSTRAINT cnoFK FOREIGN KEY (cno) REFERENCES customers (cno) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT enoFK FOREIGN KEY (eno) REFERENCES employees (eno) ON DELETE RESTRICT ON UPDATE CASCADE ); CREATE TABLE odetails ( ono SMALLINT UNSIGNED NOT NULL, pno SMALLINT UNSIGNED NOT NULL, qty SMALLINT UNSIGNED NOT NULL CHECK (qty > 0), PRIMARY KEY (ono, pno), CONSTRAINT onoFK FOREIGN KEY (ono) REFERENCES orders (ono) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT pnoFK FOREIGN KEY (pno) REFERENCES parts (pno) ON DELETE RESTRICT ON UPDATE CASCADE );

Populate table with below data:

Employees Eno Ename ZIP Hdate 1000 Jones 67226 12-DEC-95 1001 Smith 60606 01-JAN-92 1002 Brown 50302 01-SEP-94

Customers Cno Cname Street ZIP Phone 1111 Charles 123 Main St. 67226 316-636-5555 2222 Bertram 237 Ash Ave. 67226 316-689-5555 3333 Barbara 111 Inwood St. 60606 316-111-1234

Parts Pno Pname QOH Price Level 10506 Land Before Time I 200 19.99 20 10507 Land Before Time II 156 19.99 20 10508 Land Before Time III 190 19.99 20 10509 Land Before Time VI 200 19.99 20 10601 Sleeping Beauty 300 24.99 20 10701 When Harry Met Sally 120 19.99 30 10800 Dirty Harry 140 14.99 30 10900 Dr. Zhivago 100 24.99 30

Odetails Ono Pno Qty 1020 10506 1 1020 10507 1 1020 10508 2 1020 10509 3 1021 10601 4 1022 10601 1 1022 10701 1 1023 10800 1 1023 10900 1

Orders Ono Cno Eno Received Shipped 1020 1111 1000 10-DEC-94 12-DEC-94 1021 1111 1000 12-JAN-95 15-JAN-95 1022 2222 1001 13-FEB-95 20-FEB-95 1023 3333 1000 20-JUN-97 null

Zipcodes ZIP City 67226 Wichita 60606 Fort Dodge 50302 Kansas City 54444 Columbia 66002 Liberal 61111 Fort Hays

For the Mail Order database created above write SQL statements to accomplish the following. You may have to consult the MySQL documentation to complete some of the tasks. The statements must be included in the same order as the order as above task:

a. Find names and cities of employees who have taken orders for parts costing more than $20.00. b. Find the names of customers who have ordered parts from employees living in Wichita. c. Find the names of customers who have placed the highest number of orders. d. Find the names of parts that have been ordered the most (in terms of quantity ordered, not number of orders). e. Find the names of parts along with the number of orders they appear in, sorted in decreasing order of the number of orders. f. Find the name(s) of customer(s) who have spent the most money. g. Decrease by 15 percent the prices of all parts that cost less than $20.00. h. Change the street address of the customer with the customer number 3333 to 321 Fir Rd. i. Delete all the orders for customers living in Wichita. j. Add a customer, Mary, with no address or phone number to the customers table. k. Modify the field holding employee name so that it is called emp name, can have up to 40 characters, and cannot be empty for an employee. l. Add an attribute email to the customers table. m. Remove the foreign key constraint on zip from the customers table. n. Remove the zipcodes table.