SQLize Online / PHPize Online  /  SQLtest Online

/****************** GrubHub Database INFO 1620 Written by Pasarlay Khan Last Updated Fall 2022 ******************/ /**** The below code will create a database for us to work with. Copy and paste this code to the DDL pane of SQLize. --<<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>><<>>-- Creation Code. DO NOT EDIT! ****/ CREATE TABLE Customer ( Customer_ID Integer NOT NULL, Customer_Name VARCHAR(25), Customer_Address VARCHAR(35), Customer_City CHAR(12), Customer_State CHAR(12), Customer_Zip CHAR(12), Customer_PhoneN0 CHAR(12), Customer_PmtN0 CHAR(12), CONSTRAINT Customer_PK PRIMARY KEY (Customer_ID) ); CREATE TABLE Restaurant ( Restaurant_ID integer NOT NULL, Restaurant_Name VARCHAR(25), Restaurant_Address VARCHAR(35), Restaurant_City CHAR(12), Restaurant_State CHAR(12), Restaurant_Zip CHAR(12), Restaurant_PhoneN0 CHAR(12), Cuisine VARCHAR(35), Restaurant_PmtN0 CHAR(12), CONSTRAINT Restaurant_PK PRIMARY KEY (Restaurant_ID) ); CREATE TABLE Driver ( Driver_ID integer NOT NULL, Driver_Name VARCHAR(25), Driver_PhoneN0 CHAR(12), Driver_LicensePlate VARCHAR(25), Driver_License VARCHAR(25), Driver_PmtN0 CHAR(12), Driver_Status CHAR(12), CONSTRAINT Driver_PK PRIMARY KEY (Driver_ID) ); CREATE TABLE OrderReceipt ( Order_ID integer NOT NULL, Customer_ID integer NOT NULL, Restaurant_ID integer NOT NULL, Driver_ID integer NOT NULL, Order_Date CHAR(12), Order_Time CHAR(12), Order_Status CHAR(12), CONSTRAINT OrderReceipt_PK PRIMARY KEY (Order_ID), CONSTRAINT OrderReceipt_Customer_FK FOREIGN KEY(Customer_ID) REFERENCES Customer(Customer_ID), CONSTRAINT OrderReceipt_Restaurant_FK FOREIGN KEY(Restaurant_ID) REFERENCES Restaurant(Restaurant_ID), CONSTRAINT OrderReceipt_Driver_FK FOREIGN KEY(Driver_ID) REFERENCES Driver(Driver_ID) ); /***Insert statements to give us data to work with. DO NOT EDIT! ***/ insert into Customer(Customer_ID, Customer_Name, Customer_Address, Customer_City, Customer_State, Customer_Zip, Customer_PhoneN0, Customer_PmtN0) values(1, "April Ludgate", "123 S 55 Ave", "Omaha", "NE", 681132, "402-553-4397", 23456), (2, "April Ludgate", "123 S 55 Ave", "Omaha", "NE", 681132, "402-553-4397", 23456), (3, "Lesile Knope", "4387 Waffles Drive", "Pawanee", "IN", 46011, "234-432-5437", 98754), (4, "Ron Swanson", "987 Bacon Avenue", "Pawnee", "IN", 46011, "456-987-3265", 234789), (5, "Andy Dwyer", "2468 The Pit", "Pawnee", "IN", 46011, "", 12390); insert into Restaurant(Restaurant_ID, Restaurant_Name, Restaurant_Address, Restaurant_City, Restaurant_State, Restaurant_Zip, Cuisine, Restaurant_PhoneN0, Restaurant_PmtN0) values(1, "Paunch Burger", "6872 Lard Lane", "Pawanee", "IN", 46011, "678-893-1568", "Fast Food", 4826), (2, "Paunch Burger", "6872 Lard Lane", "Pawanee", "IN", 46011, "678-893-1568", "Fast Food", 4826), (3, "JJ's Diner", "23428 Main St", "Pawanee", "IN", 46011, "456-987-3185", "Breakfast", 78623), (4, "Mulligan's", "6876 Classy Rd", "Indianapolis", "IN", 46077, "786-235-4862", "Steakhouse", 48623), (5, "JJ's Diner", "23428 Main St", "Pawanee", "IN", 46011, "456-987-3185", "Breakfast", 78623); insert into Driver(Driver_ID, Driver_Name, Driver_PhoneN0, Driver_LicensePlate, Driver_License, Driver_PmtN0, Driver_Status) values(1, "Henry Roth", "286-937-2250", "IUC989", "CO4790049", 444862, "Active"), (2, "Charity Osborne", "815-605-0336", "REW222", "D89973937", 959227, "Active"), (3, "Fritz Macias", "943-893-6193", "XUA177", "UO6119817", 718371, "Active"), (4, "Brenden Hill", "513-284-9064", "IOL455", "X22173227", 334452, "Active"), (5, "Henry Roth", "286-937-2250", "IUC989", "CO4790049", 444862, "Active"), (6, "Leach Peter", "909-477-8843", "AJA879", "V44276914", 603268, "Inactive"); insert into OrderReceipt(Order_ID, Customer_ID, Restaurant_ID, Driver_ID, Order_Date, Order_Time, Order_Status) values(1, 1, 1, 1, "9/30/2020", "6:44", "Out"), (2, 2, 2, 2, "9/30/2020", "10:44", "Placed"), (3, 3, 3, 3, "9/30/2020", "10:54", "Fullfilled"), (4, 4, 4, 4, "9/30/2020", "10:46", "Placed"), (5, 5, 5, 5, "9/30/2020", "11:44", "Cancelled"); /* Test Code: Execute in query pane */ Select * from Customer; Select * from Restaurant; Select * from Driver; Select * from OrderReceipt;