SQLize Online / PHPize Online  /  SQLtest Online

/* Name: Spencer Schleich-Vaughan INFO 1620 Midterm, January 2023 */ /************ Table Creations ************/ CREATE TABLE Customers ( cust_pmt_id INTEGER NOT NULL, cust_id INTEGER, cust_name VARCHAR(50), cust_address VARCHAR(50), cust_phone VARCHAR(14), CONSTRAINT Cust_PK PRIMARY KEY (cust_pmt_id) ) ; CREATE TABLE Restaurants ( rest_pmt_id INTEGER NOT NULL, rest_id INTEGER, rest_name VARCHAR(50), rest_address VARCHAR(50), rest_cuisine VARCHAR(15), rest_phone VARCHAR(14), CONSTRAINT Rest_PK PRIMARY KEY (rest_pmt_id) ) ; CREATE TABLE Drivers ( drv_pmt_id INTEGER NOT NULL, drv_id INTEGER, drv_name VARCHAR(50), drv_phone VARCHAR(14), drv_plate CHAR(6), drv_license CHAR(9), drv_status VARCHAR(8), CONSTRAINT Drv_PK PRIMARY KEY (drv_pmt_id) ) ; CREATE TABLE Orders ( order_id INTEGER NOT NULL, order_date VARCHAR(20), /*timestamp*/ order_status VARCHAR(10), cust_pmt_id INTEGER, rest_pmt_id INTEGER, drv_pmt_id INTEGER, CONSTRAINT Order_PK PRIMARY KEY(order_id), CONSTRAINT Order_cust_FK FOREIGN KEY(cust_pmt_id) REFERENCES Customers(cust_pmt_id), CONSTRAINT Order_rest_FK FOREIGN KEY(rest_pmt_id) REFERENCES Restaurants(rest_pmt_id), CONSTRAINT Order_drv_FK FOREIGN KEY(drv_pmt_id) REFERENCES Drivers(drv_pmt_id) ) ; /************ Insert Statements ************/ INSERT INTO Customers(cust_id, cust_name, cust_address, cust_phone, cust_pmt_id) VALUES (1,'April Ludgate','123 S 55 Ave, Omaha, NE 68132','402-553-4397', 23456), (2,'Leslie Knope','4387 Waffles Drive, Pawnee, IN 46011','234-432-5437', 98754), (3,'Ron Swanson','987 Bacon Avenue, Pawnee, IN 46011', '456-987-3265', 234789), (4,'Andy Dwyer','2468 The Pit, Pawnee, IN 46011','', 12390) ; INSERT INTO Restaurants(rest_id, rest_name, rest_address, rest_phone, rest_cuisine, rest_pmt_id) VALUES (1, 'Paunch Burger','6872 Lard Lane, Pawnee, IN 46011','678-893-1568','Fast Food', 4826), (2, 'JJs Diner','23428 Main Street, Pawnee, IN 46011','456-987- 3185','Breakfast', 78623), (3, 'Mulligans','6876 Classy Rd., Indianapolis, IN 46077','786-235- 4862','Steakhouse', 48623) ; INSERT INTO Drivers(drv_id, drv_name, drv_phone, drv_plate, drv_license, drv_pmt_id, drv_status) VALUES (1, 'Henry Roth', '(286) 937-2250', 'IUC989', 'C04790049', 444862, 'active'), (2, 'Charity Osborne', '(815) 605-0336', 'REW222', 'D89973937', 959227, 'active'), (3, 'Fritz Macias', '(942) 893-6193', 'XUA177', 'U06119817', 718371, 'active'), (4, 'Brenden Hill', '(513) 284-9064', 'IOL455', 'X22173227', 334452, 'active'), (5, 'Leah Peters', '(909) 477-8843', 'AJA879', 'V44276914', 603268, 'inactive') ; INSERT INTO Orders(order_id, order_date, order_status, cust_pmt_id, rest_pmt_id, drv_pmt_id) VALUES (1, '9/30/2020 6:44', 'out', 23456, 4826, 444862), (2, '9/30/2020 10:44', 'placed', 23456, 4826, 959227), (3, '9/30/2020 10:54', 'fulfilled', 98754, 78623, 718371), (4, '9/30/2020 10:46', 'placed', 234789, 48623, 334452), (5, '9/30/2020 11:44', 'cancelled', 12390, 78623, 444862), (6, '', '', NULL, NULL, 603268) ; /************ View ************/ CREATE VIEW All_Orders AS SELECT o.order_ID, c.cust_name, c.cust_address, c.cust_phone, c.cust_pmt_id, r.rest_name, r.rest_address, r.rest_phone, r.rest_cuisine, r.rest_pmt_id, d.drv_name, d.drv_phone, d.drv_plate, d.drv_license, d.drv_pmt_id, d.drv_status, o.order_date, o.order_status FROM Orders o JOIN Customers c ON o.cust_pmt_id=c.cust_pmt_id JOIN Restaurants r ON o.rest_pmt_id=r.rest_pmt_id JOIN Drivers d ON o.drv_pmt_id=d.drv_pmt_id ; /************ Displaying the data in the instructions ************/ SELECT * FROM All_Orders ; /************ Showing all orders including null ************/ SELECT o.order_id, d.drv_name FROM Orders o LEFT OUTER JOIN Drivers d ON o.drv_pmt_id=d.drv_pmt_id ORDER BY order_id ; /************ Showing only null ************/ SELECT * FROM Orders o WHERE cust_pmt_id IS NULL ;