SQLize Online / PHPize Online  /  SQLtest Online

/*Creating Tables*/ /*1. Customer Table*/ create table Customer( c_no int Primary Key, name varchar(20), address varchar(50), "phone no" bigint, c_paymentId int Unique ); /*2. Restaurant Table*/ create table Restaurant( r_no int Primary Key, name varchar(20), address varchar(50), "phone no" bigint, r_paymentId int Unique, cuisine varchar(20) ); /*3. Drivers Table*/ create table Drivers( id int Primary Key, name varchar(20), "phone no" bigint, license_no int, d_paymentId int Unique, status varchar(20) ); /*4. Orders Table*/ create table Orders( c_no int Foreign Key REFERENCES Customer(c_no), r_no int Foreign Key REFERENCES Restaurant(r_no), d_no int Foreign Key REFERENCES Drivers(id), o_no bigint Primary Key, "timestamp" timestamp, status varchar(20), ); /*5. GrubHub_Accounting Table*/ create table GrubHub_Accounting( c_payemntId int Foreign Key REFERENCES Customer(c_paymentId), r_paymentId int Foreign Key REFERENCES Restaurant(r_paymentId), d_paymentId int Foreign Key REFERENCES Drivers(d_paymentId), ); /* 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 a new record in the Customers table. Customers CustomerName, Address, Phone number, payment ID, Restaurant name, address, cuisine, Driver, phone number, lisense place, drivers lisense, delivery status, order date, 'April Ludgate', '123 s 55 Ave, Omaha, NE 68132', '402-553-4397', '23456', 'paunch burger' '6872 Lard Lane, Pawnee, IN 46011' '678-893-1568' ; 'Leslie Knope' '4387 Waffles Drive, Pawnee, IN, 4601' '234-432-5437' '98754' 'JJ's Diner' '23428 Main St. Pawnee, IN 46011' '456-987-3185' ; 'Ron Swanson', '987 Bacon Avenue, Pawnee, IN 46011', '456-987-3265', '234789', 'Mulligan's 6876 Classy Rd., Indianapolis, IN 46077', '786-235-4862', ; 'Andy Dwyer', '2468 The Pit, Pawnee, IN 46011', '12390', 'JJ's Diner 23428 Main St. Pawnee, IN 46011', '456-987-3185', ;