SQL

Your final task is to use your tables for an update and for reports. There are two requirements.

You will demonstrate an update that includes two tables and uses a subquery. Make sure to prove that your update executed correctly by showing data in the tables before and after the update.

You will also develop at least four meaningful reports. One must include a join, one must use a subquery, and one must use an aggregate function. You need to describe the business requirement of the update and each report. Note that numeric PK values are not often meaningful to a person looking at the report. You will create a script file that has each of the queries called yourname_task3.txt. You will also document the results of this task in a Word document called yourname_reports.docx to show the result of each query. For the update and each report,

describe the update and report requirement;

show the query; and

show the result of the query.

Include a final section for Conclusions and Lessons Learned regarding the project.

MY SCRIPT:

DROP TABLE IF EXISTS Courier; DROP TABLE IF EXISTS `Delivery Checkout`; DROP TABLE IF EXISTS Payment; DROP TABLE IF EXISTS `Shop Information`; DROP TABLE IF EXISTS Staff; DROP TABLE IF EXISTS Customer; DROP TABLE IF EXISTS Menu; DROP TABLE IF EXISTS Orders; DROP TABLE IF EXISTS Refund; DROP TABLE IF EXISTS Delivery;

CREATE TABLE Courier(Transport_id INT(10) ZEROFILL AUTO_INCREMENT, Transport_name CHAR(10) NOT NULL, PRIMARY KEY(Transport_id)); CREATE TABLE `Delivery Checkout`(Delivery_location CHAR(10), total DOUBLE(5,2)); CREATE TABLE Payment(Payment_type CHAR(10) NOT NULL CHECK(Payment_type IN('Cash','Card','Paypal','Check')), PRIMARY KEY(Payment_type)); CREATE TABLE `Shop Information` (S_id INT(10) ZEROFILL AUTO_INCREMENT, S_name VARCHAR(20), Location VARCHAR(20), Hours int, PRIMARY KEY(S_id)); CREATE TABLE Staff(Staff_id INT(10) ZEROFILL AUTO_INCREMENT, Staff_name VARCHAR(20), Staff_occupation VARCHAR(20),Staff_area_of_expertise VARCHAR(20), PRIMARY KEY(Staff_id)); CREATE TABLE Customer(C_id INT(10) ZEROFILL AUTO_INCREMENT, C_fname VARCHAR(20), C_lname VARCHAR(20), Health_choice VARCHAR(20), Flavor_choice VARCHAR(20), PRIMARY KEY(C_id)); CREATE TABLE Menu(Menu_item_id INT(10) ZEROFILL AUTO_INCREMENT, Menu_item_name VARCHAR(20), PRIMARY KEY(Menu_item_id)); CREATE TABLE Orders(`Attribute name` CHAR(10), Bill_id INT(10) ZEROFILL,C_id INT (10) ZEROFILL, Order_id INT(10) ZEROFILL AUTO_INCREMENT, Menu_item_id INT(10) ZEROFILL, PRIMARY KEY(Order_id),FOREIGN KEY(C_id) REFERENCES Customer(C_id), FOREIGN KEY(Menu_item_id) REFERENCES Menu(Menu_item_id)); CREATE TABLE Refund (Refund_id INT(10) ZEROFILL AUTO_INCREMENT, C_id INT (10) ZEROFILL, Order_id INT (10) ZEROFILL, Refund_reason VARCHAR(50), Refund_amount DOUBLE(5,2),Receipt_number CHAR(10) unique, PRIMARY KEY(Refund_id), FOREIGN KEY(C_id) REFERENCES Customer(C_id), FOREIGN KEY(Order_id) REFERENCES Orders(Order_id)); CREATE TABLE Delivery(Delivery_id INT(10) ZEROFILL AUTO_INCREMENT,Transport_id INT(10) ZEROFILL,Order_id INT(10) ZEROFILL,Payment_type CHAR(10), PRIMARY KEY(Delivery_id), FOREIGN KEY(Transport_id) REFERENCES Courier(Transport_id), FOREIGN KEY(Order_id) REFERENCES Orders(Order_id),FOREIGN KEY(Payment_type) REFERENCES payment(payment_type));

INSERT INTO Courier VALUES(1,'Carnes'); INSERT INTO Courier VALUES(2,'Safe Way'); INSERT INTO Courier VALUES(3,'Pegasus'); INSERT INTO Courier VALUES(4,'Marquette'); INSERT INTO Courier VALUES(5,'Kentucky'); INSERT INTO Courier VALUES(6,'Mercer');

INSERT INTO `Delivery Checkout` VALUES('Bardstown',500.20); INSERT INTO `Delivery Checkout` VALUES('Louisville',500.20); INSERT INTO `Delivery Checkout` VALUES('Somerset',500.20); INSERT INTO `Delivery Checkout` VALUES('Kuttawa',500.20); INSERT INTO `Delivery Checkout` VALUES('Paducah',500.20); INSERT INTO `Delivery Checkout` VALUES('Lexington',500.20); INSERT INTO `Delivery Checkout` VALUES('Florence',500.20);

INSERT INTO Payment VALUES('Cash'); INSERT INTO Payment VALUES('Card'); INSERT INTO Payment VALUES('Paypal'); INSERT INTO Payment VALUES('Check');

INSERT INTO `Shop Information` VALUES(1,'Royal', 'Bardstown', 10); INSERT INTO `Shop Information` VALUES(2,'Great', 'Louisville', 12); INSERT INTO `Shop Information` VALUES(3,'MYS', 'Paducah', 11); INSERT INTO `Shop Information` VALUES(4,'DSC', 'Lexington', 8); INSERT INTO `Shop Information` VALUES(5,'Fly100', 'Somerset', 9);

INSERT INTO Staff VALUES(1,'John Smith','Manager', 'Production'); INSERT INTO Staff VALUES(2,'Aden Prince','IT', 'Database Administrator'); INSERT INTO Staff VALUES(3,'Nelly John','Accoutant', 'Cost accounting'); INSERT INTO Staff VALUES(4,'Graham Smith','Manager', 'Sales'); INSERT INTO Staff VALUES(5,'John Henderson','Manager', 'Customer relation');

INSERT INTO Customer VALUES(1,'John', 'Aden','rice', 'Provesta 345'); INSERT INTO Customer VALUES(2,'Smith' ,'Prince','carrots', 'Provesta 347'); INSERT INTO Customer VALUES(3,'Nelly','Smith','crisp broccoli', 'Ohly BOV M'); INSERT INTO Customer VALUES(4,'Graham','John' ,'crisp veggies', 'Ohly BOV Z'); INSERT INTO Customer VALUES(5,'Michael', 'Jordan','oasted potatoes', 'Ohly KMXT');

INSERT INTO Menu VALUES(1,'Beef ball'); INSERT INTO Menu VALUES(2,'Beef bun'); INSERT INTO Menu VALUES(3,'Beef chow fun'); INSERT INTO Menu VALUES(4,'Beef Manhattan'); INSERT INTO Menu VALUES(5,'Beef Wellington'); INSERT INTO Menu VALUES(6,'Beefsteak'); INSERT INTO Menu VALUES(7,'Boiled beef');

INSERT INTO Orders VALUES('ball',1,4,1,2); INSERT INTO Orders VALUES('bun',2,2,2,1); INSERT INTO Orders VALUES('chow fun',3,3,3,4); INSERT INTO Orders VALUES('Manhattan',4,1,4,2); INSERT INTO Orders VALUES('Wellington',5,4,5,3);

INSERT INTO Refund VALUES(1,2,2,'Wrong item',2.00,'WRONG0001'); INSERT INTO Refund VALUES(2,4,1,'Damaged',1.00,'DAMAGED001'); INSERT INTO Refund VALUES(3,3,3,'Wrong size',4.00,'WRONG0002'); INSERT INTO Refund VALUES(4,1,4,'Expired',2.00,'EXPIRED01'); INSERT INTO Refund VALUES(5,4,5,'Damaged',3.89,'DAMAGED002');

INSERT INTO Delivery VALUES(1,4,4,'Cash'); INSERT INTO Delivery VALUES(2,2,2,'Paypal'); INSERT INTO Delivery VALUES(3,5,3,'Card'); INSERT INTO Delivery VALUES(4,1,1,'Cash'); INSERT INTO Delivery VALUES(5,3,4,'Check');

SELECT * FROM Courier; SELECT * FROM `Delivery Checkout`; SELECT * FROM Payment; SELECT * FROM `Shop Information`; SELECT * FROM Staff; SELECT * FROM Customer; SELECT * FROM Menu; SELECT * FROM Orders; SELECT * FROM Refund; SELECT * FROM Delivery;