SQLize Online / PHPize Online  /  SQLtest Online

/*Creating Tables*/ /*1. Customer Table*/ CREATE TABLE Customer ( CustNumber INTEGER NOT NULL PRIMARY KEY, Name VARCHAR(50), Address VARCHAR(50), PhoneNumber VARCHAR(12), CustPaymentId VARCHAR(20) ); /*2. Restaurant Table*/ CREATE TABLE Restaurant ( RestNumber INTEGER NOT NULL PRIMARY KEY, Name VARCHAR(50), Address VARCHAR(50), PhoneNumber VARCHAR(12), RestPaymentId VARCHAR(20), Cuisine VARCHAR(20) ); /*3. Drivers Table*/ CREATE TABLE Drivers ( DriverID INTEGER NOT NULL PRIMARY KEY, Name VARCHAR(50), PhoneNumber VARCHAR(12), LicenseNumber VARCHAR(20), LicensePlateNo VARCHAR(20), DriverPaymentID VARCHAR(20), Status VARCHAR(20) ); /*4. Orders Table*/ CREATE TABLE Orders ( OrderID INTEGER NOT NULL PRIMARY KEY, OrderTimeStamp DateTime, Status VARCHAR(20), CustNumber INTEGER, RestNumber INTEGER, DriverID INTEGER, FOREIGN KEY (CustNumber) REFERENCES Customer (CustNumber), FOREIGN KEY (RestNumber) REFERENCES Restaurant (RestNumber), FOREIGN KEY (DriverID) REFERENCES Drivers (DriverID) ); /* Adding Check constraint to allow only four values(Placed/Out/Cancelled/Fulfilled) for status cloumn in Orders Table*/ ALTER TABLE Orders ADD CONSTRAINT chK_status CHECK (status = 'Placed' or status = 'Out' or status = 'Cancelled' or status = 'Fulfilled'); /* Adding Check constraint to allow only two values(active/inactive) for status cloumn in Drivers Table*/ ALTER TABLE Drivers ADD CONSTRAINT chK_driver_status CHECK (status = 'Active' or status = 'Inactive'); /* INSERT STATEMENTS */ INSERT INTO Customer (CustNumber, Name, Address, PhoneNumber, CustPaymentID) 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 Restaurant (RestNumber, Name, Address, PhoneNumber, RestPaymentID, Cuisine) VALUES (1, "Paunch Burger", "6872 Lard Lane, Pawnee, IN 46011", "678-893-1568", "4826", "Fast Food"), (2, "JJ's Diner", "23428 Main St, Pawnee, IN 46011", "456-987-3185", "78623", "Breakfast"), (3, "Mulligan's", "6876 Classy Rd, Indianapolis, IN 46077", "786-235-4862", "48623", "Steakhouse"); INSERT INTO Drivers (DriverID, Name, PhoneNumber, LicenseNumber, LicensePlateNo, DriverPaymentID, Status) VALUES (1, "Henry Roth", "286-937-2250", "C04790049", "IUC989", "444862", "Active"), (2, "Charity Osborne", "815-605-0336", "D89973937", "REW222", "959227", "Active"), (3, "Fritz Macias", "943-893-6193", "U06119817", "XUA177", "718371", "Active"), (4, "Brenden Hill", "513-284-9064", "X22173227", "IOL455", "334452", "Active"), (5, "Leah Peters", "909-477-8843", "V44276914", "AJA879", "603268", "Inactive"); INSERT INTO Orders (OrderID, OrderTimeStamp, Status, CustNumber, RestNumber, DriverID) VALUES (1, "2020-09-30 06:44", "Out", 1, 1, 1), (2, "2020-09-30 10:44", "Placed", 1, 1, 2), (3, "2020-09-30 10:54", "Fulfilled", 2, 2, 3), (4, "2020-09-30 10:46", "Placed", 3, 3, 4), (5, "2020-09-30 11:44", "Cancelled", 4, 2, 1);