CREATE TABLE `Customer` (
`CustomerID` INT(11),
`fName` VARCHAR(50),
`lName` VARCHAR(50),
`street1` VARCHAR(100),
`street2` VARCHAR(100),
`city` VARCHAR(50),
`zip` INT(11),
`state` VARCHAR(50),
`phoneNumber` BIGINT(20),
`paymentID` INT(11),
PRIMARY KEY (`CustomerID`)
);
CREATE TABLE `Restaurant` (
`RestaurantID` INT(11),
`RestaurantName` VARCHAR(50),
`street1` VARCHAR(100),
`street2` VARCHAR(100),
`city` VARCHAR(50),
`zip` INT(11),
`state` VARCHAR(50),
`phoneNumber` BIGINT(20),
`cuisine` VARCHAR(50),
`restPmtID` INT(11),
`OrderID` INT(11),
PRIMARY KEY (`RestaurantID`)
);
CREATE TABLE `Drivers` (
`DriverID` INT(11),
`LicensePlate` VARCHAR(50),
`DriversFName` VARCHAR(50),
`PhoneNumber` INT(11),
`DriversLicense` VARCHAR(50),
`DriverPmtID` INT(11),
`LicenseStatus` VARCHAR(50),
`DriversLName` VARCHAR(50),
`OrderID` INT(11),
PRIMARY KEY (`DriverID`)
);
CREATE TABLE `Orders` (
`OrderID` INT(11),
`DriversID` INT(11),
`RestaurantID` INT(11),
`CustomerID` INT(11),
`dateTime` date,
`status` VARCHAR(50),
PRIMARY KEY (`OrderID`),
FOREIGN KEY (`DriversID`) REFERENCES `Drivers`(`DriverID`),
FOREIGN KEY (`RestaurantID`) REFERENCES `Restaurant`(`RestaurantID`),
FOREIGN KEY (`CustomerID`) REFERENCES `Customer`(`CustomerID`)
);
insert
into Customer (CustomerID, fName, lName, street1, street2,
city, zip, state, phoneNumber, paymentID)
values
(1, "April", "Ludgate", "123 S 55 Ave", NULL, "Omaha", "68132", "NE", 4025534397, 23456),
(2, "Leslie", "Knope", "4387 Waffles Drive", NULL, "Pawnee", "46011", "IN", 2344325437, 98754),
(3, "Ron", "Swanson", "987 Bacon Avenue", NULL, "Pawnee", "46011", "IN", 4569873265, 234789),
(4, "Andy", "Dwyer", "2468 The Pit", NULL, "Pawnee", "46011", "IN", NULL, 12390)
;
insert
into Restaurant (RestaurantID, RestaurantName, street1, street2,
city, zip, state, phoneNumber, cuisine, restPmtID)
values
(1, "Paunch Burger", "6872 Lard Lane", NULL, "Pawnee", "46011", "IN", 6788931568, "Fast Food", 4826),
(2, "JJ's Diner", "23428 Main St.", NULL, "Pawnee", "46011", "IN", 6788931568, "Breakfast", 78623),
(3, "Mulligan's", "6876 Classy Rd.", NULL, "Indianapolis", "46077", "IN", 6788931568, "Steakhouse", 48623)
;
insert
into Drivers (DriverID, LicensePlate, DriversFName, PhoneNumber,
DriversLicense, DriverPmtID, LicenseStatus, DriversLName)
values
(1, "IUC989", "Henry", 2869372250, "C04790049", "444862", "active", "Roth"),
(2, "REW222", "Charity", 8156050336, "D89973937", "959227", "active", "Osborne"),
(3, "XUA177", "Fritz", 9438936193, "U06119817", "718371", "active", "Macias"),
(4, "IOL455", "Brenden", 5132849064, "X22173227", "334452", "active", "Hill"),
(5, "AJA879", "Leah", 9094478843, "V44276914", "603268", "inactive", "Peters")
;
insert
into Orders (OrderID, DriversID, RestaurantID, CustomerID, dateTime, status)
values
(1, 1, 1, 1, '2020-9-30-06:44', 'out')
(2, 2, 1, 1, '2020-9-30-10:44', 'placed')
(3, 3, 2, 2, '2020-9-30-10:54', 'fulfilled')
(4, 4, 3, 3, '2020-9-30-10:46', 'placed')
(5, 1, 2, 4, '2020-9-30-11:44', 'cancelled')
(6, 5, NULL, NULL, NULL, NULL)
;
select * from Orders;
select * from Customer;
select * from Restaurant;
select * from Drivers;
Telegram: DB developers channel