/*
Name: Spencer Schleich-Vaughan
INFO 1620 Midterm, January 2023
*/
/************ Table Creations ************/
CREATE TABLE Customers (
cust_pmt_id INTEGER NOT NULL,
cust_id INTEGER,
cust_name VARCHAR(50),
cust_address VARCHAR(50),
cust_phone VARCHAR(14),
CONSTRAINT Cust_PK PRIMARY KEY (cust_pmt_id)
)
;
CREATE TABLE Restaurants (
rest_pmt_id INTEGER NOT NULL,
rest_id INTEGER,
rest_name VARCHAR(50),
rest_address VARCHAR(50),
rest_cuisine VARCHAR(15),
rest_phone VARCHAR(14),
CONSTRAINT Rest_PK PRIMARY KEY (rest_pmt_id)
)
;
CREATE TABLE Drivers (
drv_pmt_id INTEGER NOT NULL,
drv_id INTEGER,
drv_name VARCHAR(50),
drv_phone VARCHAR(14),
drv_plate CHAR(6),
drv_license CHAR(9),
drv_status VARCHAR(8),
CONSTRAINT Drv_PK PRIMARY KEY (drv_pmt_id)
)
;
CREATE TABLE Orders (
order_id INTEGER NOT NULL,
order_date VARCHAR(20), /*timestamp*/
order_status VARCHAR(10),
cust_pmt_id INTEGER,
rest_pmt_id INTEGER,
drv_pmt_id INTEGER,
CONSTRAINT Order_PK PRIMARY KEY(order_id),
CONSTRAINT Order_cust_FK FOREIGN KEY(cust_pmt_id)
REFERENCES Customers(cust_pmt_id),
CONSTRAINT Order_rest_FK FOREIGN KEY(rest_pmt_id)
REFERENCES Restaurants(rest_pmt_id),
CONSTRAINT Order_drv_FK FOREIGN KEY(drv_pmt_id)
REFERENCES Drivers(drv_pmt_id)
)
;
/************ Insert Statements ************/
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_phone, cust_pmt_id)
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 Restaurants(rest_id, rest_name, rest_address, rest_phone, rest_cuisine,
rest_pmt_id)
VALUES
(1, 'Paunch Burger','6872 Lard Lane, Pawnee, IN 46011','678-893-1568','Fast
Food', 4826),
(2, 'JJs Diner','23428 Main Street, Pawnee, IN 46011','456-987-
3185','Breakfast', 78623),
(3, 'Mulligans','6876 Classy Rd., Indianapolis, IN 46077','786-235-
4862','Steakhouse', 48623)
;
INSERT INTO Drivers(drv_id, drv_name, drv_phone, drv_plate, drv_license,
drv_pmt_id, drv_status)
VALUES
(1, 'Henry Roth', '(286) 937-2250', 'IUC989', 'C04790049', 444862, 'active'),
(2, 'Charity Osborne', '(815) 605-0336', 'REW222', 'D89973937', 959227,
'active'),
(3, 'Fritz Macias', '(942) 893-6193', 'XUA177', 'U06119817', 718371, 'active'),
(4, 'Brenden Hill', '(513) 284-9064', 'IOL455', 'X22173227', 334452, 'active'),
(5, 'Leah Peters', '(909) 477-8843', 'AJA879', 'V44276914', 603268, 'inactive')
;
INSERT INTO Orders(order_id, order_date, order_status, cust_pmt_id, rest_pmt_id,
drv_pmt_id)
VALUES
(1, '9/30/2020 6:44', 'out', 23456, 4826, 444862),
(2, '9/30/2020 10:44', 'placed', 23456, 4826, 959227),
(3, '9/30/2020 10:54', 'fulfilled', 98754, 78623, 718371),
(4, '9/30/2020 10:46', 'placed', 234789, 48623, 334452),
(5, '9/30/2020 11:44', 'cancelled', 12390, 78623, 444862),
(6, '', '', NULL, NULL, 603268)
;
/************ View ************/
CREATE VIEW All_Orders AS
SELECT o.order_ID, c.cust_name, c.cust_address, c.cust_phone, c.cust_pmt_id,
r.rest_name, r.rest_address, r.rest_phone, r.rest_cuisine, r.rest_pmt_id,
d.drv_name, d.drv_phone, d.drv_plate, d.drv_license, d.drv_pmt_id,
d.drv_status, o.order_date, o.order_status
FROM Orders o
JOIN Customers c ON o.cust_pmt_id=c.cust_pmt_id
JOIN Restaurants r ON o.rest_pmt_id=r.rest_pmt_id
JOIN Drivers d ON o.drv_pmt_id=d.drv_pmt_id
;
/************ Displaying the data in the instructions ************/
SELECT *
FROM All_Orders
;
/************ Showing all orders including null ************/
SELECT o.order_id, d.drv_name
FROM Orders o
LEFT OUTER JOIN Drivers d ON o.drv_pmt_id=d.drv_pmt_id
ORDER BY order_id
;
/************ Showing only null ************/
SELECT *
FROM Orders o
WHERE cust_pmt_id IS NULL
;
Telegram: DB developers channel