SQL I need to demonstrate an update that includes two tables and uses a subquery

Question

SQL

I need to demonstrate an update that includes two tables and uses a subquery.

I need to develop at least four meaningful reports. One must include a join, one must use a subquery, and one must use an aggregate function. It needs to describe the business requirement of the update and each report. I also will need to make a script file that has each of the queries called yourname_task3.txt.

For the update and each report, describe the update and report requirement; show the query; and show the result of the query.

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;

Explanation / Answer

1.An update that includes two tables and uses a subquery.

Update/change the payment type of all deliveries by the transport_name ‘Kentucky’ to ‘Cash’

Business Scenario: Suppose some unfortunate technical errors occurred, and the ‘Kentucky’ transport can only accept ‘cash’ mode transactions.

Courier table shows the transport Id of Kentucky Transport

Delivery table shows the rows with transport Ids same as that of Kentucky (before update)

Required query: UPDATE DELIVERY SET PAYMENT_TYPE='CASH' WHERE TRANSPORT_ID=(SELECT TRANSPORT_ID FROM COURIER WHERE TRANSPORT_NAME='KENTUCKY');

Delivery table after update

2.A meaningful report that includes a ‘join’

Retrieve firstname, lastname of the customers along with the corresponding attributes they’ve ordered

Business Scenario: To fetch the details of customers who’ve ordered something

Required query: SELECT C.C_FNAME,C.C_LNAME,O.`ATTRIBUTE NAME` FROM CUSTOMER C JOIN ORDERS O ON C.C_ID=O.C_ID;

Result of the query

3.A meaningful report that includes a ‘subquery’

Retrieve the name of the menu item corresponding to the Bill_id 0000000001

Business Scenario: To fetch the details about a product with its bill information

Required query: SELECT MENU_ITEM_NAME FROM MENU WHERE MENU_ITEM_ID=(SELECT MENU_ITEM_ID FROM ORDERS WHERE BILL_ID=0000000001);

Result of the query

4.A meaningful report that includes an ‘aggregate function’

Retrieve the details of the highest refund amount transaction.

Business Scenario: To fetch the details of maximum / minimum refund amount processed, or the details of a product with highest/lowest price,we can use the help of aggregate functions.

Required query: SELECT * FROM REFUND WHERE REFUND_AMOUNT=(SELECT MAX(REFUND_AMOUNT) FROM REFUND);

Result of the query

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.