SQLize Online / PHPize Online  /  SQLtest Online

/* INFO 1620 Midterm SQL Joe Rakoczy GrubHub Database */ /* Naming database, access denied by sqlfiddle */ /*create schema GrubHub;*/ /* creating tables*/ CREATE TABLE Customer ( Customer_ID INTEGER NOT NULL, Customer_FName CHAR(35), Customer_LName CHAR(35), Customer_Phone VARCHAR(10), Customer_PaymentID INTEGER, Customer_Building VARCHAR(35), Customer_Street VARCHAR(35), Customer_Apt VARCHAR(35), Customer_City VARCHAR(35), Customer_State VARCHAR(10), Customer_Zip VARCHAR(35), CONSTRAINT Customer_PK PRIMARY KEY (Customer_ID) ); CREATE TABLE Driver ( Driver_ID INTEGER NOT NULL, Driver_FName CHAR(35), Driver_LName Char(35), Driver_Phone VARCHAR(10), Driver_Plate VARCHAR(8), Driver_License VARCHAR(35), Driver_PaymentID INTEGER, Driver_Status VARCHAR(8), CONSTRAINT Driver_PK PRIMARY KEY (Driver_ID) ); CREATE TABLE Restaurant ( Restaurant_ID INTEGER NOT NULL, Restaurant_Name VARCHAR(50), Restaurant_PaymentID INTEGER, Restaurant_Phone VARCHAR(10), Restaurant_Cuisine VARCHAR(35), Restaurant_Building VARCHAR(35), Restaurant_Street VARCHAR(35), Restaurant_Apt VARCHAR(35), Restaurant_City VARCHAR(35), Restaurant_State VARCHAR(10), Restaurant_Zip VARCHAR(35), CONSTRAINT Restaurant_PK PRIMARY KEY (Restaurant_ID) ); CREATE TABLE Orders ( Order_ID INTEGER, Restaurant_ID INTEGER, Customer_ID INTEGER, Driver_ID INTEGER, Order_Status VARCHAR(35), Order_Time VARCHAR(35), CONSTRAINT Orders_PK PRIMARY KEY (Order_ID), CONSTRAINT Orders_Rest_FK FOREIGN KEY (Restaurant_ID) REFERENCES Restaurant(Restaurant_ID), CONSTRAINT Orders_Cust_FK FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID), CONSTRAINT Orders_Driver_FK FOREIGN KEY (Driver_ID) REFERENCES Driver(Driver_ID) ); /* inserting info */ insert into Customer( Customer_ID, Customer_FName, Customer_LName, Customer_Phone, Customer_PaymentID, Customer_Building, Customer_Street, Customer_Apt, Customer_City, Customer_State, Customer_Zip ) -- ID, first, last, phone, paymentID, building, street, apt, city, state, zip values (1, "April", "Ludgate", "4025534397", 23456, "123", "S 55 Ave", NULL, "Omaha", "NE", "68132"), (2, "Leslie", "Knope", "2344325437", 98754, "4387", "Waffles Drive", NULL, "Pawnee", "IN", "46011"), (3, "Ron", "Swanson", "4569873265", 234789, "987", "Bacon Avenue", NULL, "Pawnee", "IN", "46011"), (4, "Andy", "Dwyer", NULL, 12390, "2468", "The Pit", NULL, "Pawnee", "IN", "46011") ; insert into Driver( Driver_ID, Driver_FName, Driver_LName, Driver_Phone, Driver_Plate, Driver_License, Driver_PaymentID, Driver_Status ) -- ID, first, last, phone, plate, license, paymentID, status values (1, "Henry", "Roth", "2869372250", "IUC989", "C04790049", 444862, "active"), (2, "Charity", "Osborne", "8156050336", "REW222", "D89973937", 959227, "active"), (3, "Fritz", "Macais", "9438936193", "XUA177", "U06119817", 718371, "active"), (4, "Brenden", "Hill", "5132849064", "IOL455", "X22173227", 334452, "active"), (5, "Henry", "Roth", "2869372250", "IUC989", "C04790049", 444862, "active"), (6, "Leah", "Peters", "9094778843", "AJA879", "V44276914", 603268, "inactive") ; insert into Restaurant( Restaurant_ID, Restaurant_Name, Restaurant_PaymentID, Restaurant_Phone, Restaurant_Cuisine, Restaurant_Building, Restaurant_Street, Restaurant_Apt, Restaurant_City, Restaurant_State, Restaurant_Zip ) -- ID, name, paymentID, phone, cuisine, building, street, apt, city, state, zip values (1, "Paunch Burger", 4826, "6788931568", "Fast Food", "6872", "Lard Lane", NULL, "Pawnee", "IN", "46011"), (2, "JJ's Diner", 78623, "9438936193", "Breakfast", "23428", "Main St.", NULL, "Pawnee", "IN", "46011"), (3, "Mulligan's", 48623, "7862354862", "Steakhouse", "6876", "Classy Rd.", NULL, "Indianapolis", "IN", "46077") ; insert into Orders( Order_ID, Restaurant_ID, Customer_ID, Driver_ID, Order_Status, Order_Time ) -- orderID, restID, custID, driverID, status, time values (1, 1, 1, 1, "out", "9/30/2020 6:44"), (2, 1, 1, 2, "placed", "9/30/2020 10:44"), (3, 2, 2, 3, "fulfilled", "9/30/2020 10:54"), (4, 3, 3, 4, "placed", "9/30/2020 10:46"), (5, 2, 4, 1, "cancelled", "9/30/2020 11:44") ; /* Shows list of all orders, with some info from each involved party */ CREATE VIEW Orders_Info AS select Order_ID, Restaurant.Restaurant_Name AS 'Restaurant', CONCAT("(", SUBSTR(Restaurant_Phone,1,3), ") ", SUBSTR(Restaurant_Phone,4,3), "-", SUBSTR(Restaurant_Phone,7,4)) AS 'Restaurant Phone Number', CONCAT(Restaurant_Building, " ", Restaurant_Street, ", ", CASE WHEN Restaurant_Apt IS NULL THEN "" ELSE CONCAT(Restaurant_Apt, ", ") END, Restaurant_City, ", ", Restaurant_State, " ", Restaurant_Zip) AS 'Restaurant Address', CONCAT(Driver_FName, " ", Driver_LName) as 'Driver Name', Driver_Plate AS 'Driver License Plate', CONCAT("(", SUBSTR(Driver_Phone,1,3), ") ", SUBSTR(Driver_Phone,4,3), "-", SUBSTR(Driver_Phone,7,4)) AS 'Driver Phone Number', CONCAT(Customer_FName, " ", Customer_LName) as 'Customer Name', CONCAT("(", SUBSTR(Customer_Phone,1,3), ") ", SUBSTR(Customer_Phone,4,3), "-", SUBSTR(Customer_Phone,7,4)) AS 'Customer Phone Number', CONCAT(Customer_Building, " ", Customer_Street, ", ", CASE WHEN Customer_Apt IS NULL THEN "" ELSE CONCAT(Customer_Apt, ", ") END, Customer_City, ", ", Customer_State, " ", Customer_Zip) AS 'Customer Address' from Orders join Restaurant on Orders.Restaurant_ID = Restaurant.Restaurant_ID join Driver on Orders.Driver_ID = Driver.Driver_ID join Customer on Orders.Customer_ID = Customer.Customer_ID ; /* test queries*/ show tables; select * from Customer; select * from Driver; select * from Restaurant; select * from Orders;