-- Part 1; add drops so that we can keep re-running this file. DROP TABLE Reservation; DROP TABLE TravelAgent; DROP TABLE Cruise; DROP TABLE Company; DROP TABLE Customer; DROP SEQUENCE customerID_seq; DROP SEQUENCE cruiseID_seq; DROP SEQUENCE travelAgentID_seq; DROP SEQUENCE reservationID_seq; -- Part 2; Set up schema and insert data. CREATE TABLE Customer( customerID NUMBER, firstName VARCHAR2(15), lastName VARCHAR2(15), address VARCHAR2(30), phone NUMBER(10), age NUMBER(3), CONSTRAINT Customer_pk PRIMARY KEY(customerID), CONSTRAINT Customer_U1 UNIQUE(firstName, lastName, phone), CONSTRAINT Customer_phoneNumber_val CHECK (phone IS NOT NULL) ); CREATE SEQUENCE customerID_seq; CREATE TABLE Company( companyName VARCHAR2(15), stockSymbol CHAR(4), website VARCHAR2(40), CONSTRAINT Company_pk PRIMARY KEY(companyName), CONSTRAINT Company_U1 UNIQUE(stockSymbol) ); CREATE TABLE Cruise( cruiseID NUMBER, cruiseName VARCHAR2(25), departurePort VARCHAR2(20), days NUMBER(2), companyName VARCHAR2(15), shipName VARCHAR2(30), price NUMBER(7, 2), CONSTRAINT Cruise_pk PRIMARY KEY(cruiseID), CONSTRAINT Cruise_companyName_fk FOREIGN KEY (companyName) REFERENCES Company(companyName) ON DELETE SET NULL ); CREATE SEQUENCE cruiseID_seq; CREATE TABLE TravelAgent( travelAgentID NUMBER, firstName VARCHAR2(15), lastName VARCHAR2(20), title VARCHAR2(15), salary NUMBER(7, 2), CONSTRAINT TravelAgent_pk PRIMARY KEY(travelAgentID), CONSTRAINT TravelAgent_title_val CHECK (title IN ('Assistant', 'Agent', 'Manager')) ); CREATE SEQUENCE travelAgentID_seq; CREATE TABLE Reservation( reservationID NUMBER, customerID NUMBER, cruiseID NUMBER, travelAgentID NUMBER, travelDate DATE, CONSTRAINT Reservation_pk PRIMARY KEY(reservationID), CONSTRAINT Reservation_customerID_fk FOREIGN KEY (customerID) REFERENCES Customer(customerID) ON DELETE CASCADE, CONSTRAINT Reservation_cruiseID_fk FOREIGN KEY (cruiseID) REFERENCES Cruise(cruiseID) ON DELETE CASCADE, CONSTRAINT Reservation_travelAgentID_fk FOREIGN KEY (travelAgentID) REFERENCES TravelAgent(travelAgentID) ON DELETE CASCADE ); CREATE SEQUENCE reservationID_seq; -- Customer values INSERT INTO Customer VALUES (customerID_seq.nextval, 'Dylan', 'Ward', '42 Elm Place', 8915367188, 22); INSERT INTO Customer VALUES (customerID_seq.nextval, 'Austin', 'Ross', '657 Redondo Ave.', 1233753684, 25); INSERT INTO Customer VALUES (customerID_seq.nextval, 'Lisa', 'Powell', '5 Jefferson Ave.', 6428369619, 17); INSERT INTO Customer VALUES (customerID_seq.nextval, 'Brian', 'Martin', '143 Cambridge Ave.', 5082328798, 45); INSERT INTO Customer VALUES (customerID_seq.nextval, 'Nicole', 'White', '77 Massachusetts Ave.', 6174153059, 29); INSERT INTO Customer VALUES (customerID_seq.nextval, 'Tyler', 'Garcia', '175 Forest St.', 9864752346, 57); INSERT INTO Customer VALUES (customerID_seq.nextval, 'Anna', 'Allen', '35 Tremont St.', 8946557732, 73); INSERT INTO Customer VALUES (customerID_seq.nextval, 'Michael', 'Sanchez', '9 Washington Court', 1946825344, 18); INSERT INTO Customer VALUES (customerID_seq.nextval, 'Justin', 'Myers', '98 Lake Hill Drive', 7988641411, 26); INSERT INTO Customer VALUES (customerID_seq.nextval, 'Bruce', 'Clark', '100 Main St.', 2324648888, 68); INSERT INTO Customer VALUES (customerID_seq.nextval, 'Rachel', 'Lee', '42 Oak St.', 2497873464, 19); INSERT INTO Customer VALUES (customerID_seq.nextval, 'Kelly', 'Gray', '1414 Cedar St.', 9865553232, 82); INSERT INTO Customer VALUES (customerID_seq.nextval, 'Madison', 'Young', '8711 Meadow St.', 4546667821, 67); INSERT INTO Customer VALUES (customerID_seq.nextval, 'Ashley', 'Powell', '17 Valley Drive', 2123043923, 20); INSERT INTO Customer VALUES (customerID_seq.nextval, 'Joshua', 'Davis', '1212 8th St.', 7818914567, 18); -- Company values INSERT INTO Company VALUES ('Carnival', 'CRVL', 'http://www.carnival.com'); INSERT INTO Company VALUES ('Celebrity', 'CELB', 'http://www.celebritycruises.com'); INSERT INTO Company VALUES ('NCL', 'NCLC', 'http://www.ncl.com'); INSERT INTO Company VALUES ('Princess', 'PRCS', 'http://www.princess.com'); -- Cruise values INSERT INTO Cruise VALUES (cruiseID_seq.nextval, 'Mexico', 'Miami', 7, 'NCL', 'Norwegian Pearl', 799.00); INSERT INTO Cruise VALUES (cruiseID_seq.nextval, 'New England', 'Boston', 7, 'NCL', 'Norwegian Jewel', 895.75); INSERT INTO Cruise VALUES (cruiseID_seq.nextval, 'ABC Islands', 'Miami', 4, 'Celebrity', 'Equinox', 450.50); INSERT INTO Cruise VALUES (cruiseID_seq.nextval, 'Hawaii', 'San Francisco', 14, 'Princess', 'Crown Princess', 2310.00); INSERT INTO Cruise VALUES (cruiseID_seq.nextval, 'Panama Canal', 'Miami', 10, 'Carnival', 'Carnival Spirit', 1432.99); -- TravelAgent values INSERT INTO TravelAgent VALUES (travelAgentID_seq.nextval, 'Chloe', 'Rodriguez', 'Assistant', 31750.00); INSERT INTO TravelAgent VALUES (travelAgentID_seq.nextval, 'Ben', 'Wilson', 'Agent', 47000.22); INSERT INTO TravelAgent VALUES (travelAgentID_seq.nextval, 'Mia', 'Smith', 'Manager', 75250.00); INSERT INTO TravelAgent VALUES (travelAgentID_seq.nextval, 'Noah', 'Williams', 'Assistant', 32080.90); INSERT INTO TravelAgent VALUES (travelAgentID_seq.nextval, 'Liam', 'Brown', 'Manager', 60500.75); INSERT INTO TravelAgent VALUES (travelAgentID_seq.nextval, 'Mason', 'Jones', 'Manager', 79000.00); INSERT INTO TravelAgent VALUES (travelAgentID_seq.nextval, 'Olivia', 'Miller', 'Agent', 54000.50); INSERT INTO TravelAgent VALUES (travelAgentID_seq.nextval, 'Sofia', 'Davis', 'Agent', 45000.00); INSERT INTO TravelAgent VALUES (travelAgentID_seq.nextval, 'Jason', 'Garcia', 'Manager', 52025.95); INSERT INTO TravelAgent VALUES (travelAgentID_seq.nextval, 'Emily', 'Johnson', 'Assistant', 22000.50); INSERT INTO TravelAgent VALUES (travelAgentID_seq.nextval, 'Ethan', 'Elm', 'Agent', 27044.52); -- Reservations values INSERT INTO Reservation VALUES (reservationID_seq.nextval, 12, 1, 2, '9-Nov-18'); INSERT INTO Reservation VALUES (reservationID_seq.nextval, 14, 4, 5, '21-Jan-19'); INSERT INTO Reservation VALUES (reservationID_seq.nextval, 5, 4, 1, '11-Dec-18'); INSERT INTO Reservation VALUES (reservationID_seq.nextval, 9, 5, 4, '31-Aug-19'); INSERT INTO Reservation VALUES (reservationID_seq.nextval, 13, 1, 2, '10-Apr-19'); INSERT INTO Reservation VALUES (reservationID_seq.nextval, 5, 4, 6, '29-Jul-18'); INSERT INTO Reservation VALUES (reservationID_seq.nextval, 2, 2, 2, '17-May-19'); INSERT INTO Reservation VALUES (reservationID_seq.nextval, 4, 1, 10, '11-Apr-19'); INSERT INTO Reservation VALUES (reservationID_seq.nextval, 10, 5, 3, '3-Jun-18'); INSERT INTO Reservation VALUES (reservationID_seq.nextval, 5, 3, 9, '15-Oct-18'); INSERT INTO Reservation VALUES (reservationID_seq.nextval, 1, 2, 7, '8-Mar-19'); INSERT INTO Reservation VALUES (reservationID_seq.nextval, 5, 4, 7, '24-Nov-18'); INSERT INTO Reservation VALUES (reservationID_seq.nextval, 8, 1, 1, '3-Aug-19'); INSERT INTO Reservation VALUES (reservationID_seq.nextval, 15, 5, 10, '13-Dec-18'); INSERT INTO Reservation VALUES (reservationID_seq.nextval, 4, 3, 7, '6-Feb-19'); INSERT INTO Reservation VALUES (reservationID_seq.nextval, 6, 4, 5, '12-Aug-19'); INSERT INTO Reservation VALUES (reservationID_seq.nextval, 14, 2, 8, '22-Jun-19'); INSERT INTO Reservation VALUES (reservationID_seq.nextval, 11, 5, 9, '1-Feb-19'); INSERT INTO Reservation VALUES (reservationID_seq.nextval, 7, 4, 8, '15-Mar-19'); INSERT INTO Reservation VALUES (reservationID_seq.nextval, 14, 4, 3, '28-Feb-19'); -- Part 3; Queries. -- 3a UPDATE Cruise SET price = ROUND(price * 1.15, 2) WHERE departurePort = 'Miami'; SELECT cruiseName, departurePort, shipName, TO_CHAR(price, '$99,999.99') AS NEWPRICE FROM Cruise; -- 3b SELECT DISTINCT Cruise.CompanyName, Cruise.cruiseName FROM Cruise JOIN Reservation ON Cruise.cruiseID = Reservation.cruiseID JOIN Customer ON Reservation.customerID = Customer.customerID WHERE Customer.Age > 50 AND Cruise.departurePort = 'Miami' ORDER BY Cruise.CompanyName ASC; -- 3c SELECT companyName || ' ' || Company.stockSymbol AS CruiseCompany, Cruise.cruiseName, Reservation.travelDate FROM Reservation NATURAL JOIN Cruise NATURAL JOIN Company WHERE Cruise.Days >= 10 AND Reservation.travelDate >= '1-JAN-2019' ORDER BY Cruise.cruiseName ASC, Reservation.travelDate ASC; -- 3d SELECT travelAgent.firstName, travelAgent.lastName, travelAgent.title FROM travelAgent LEFT JOIN Reservation ON travelAgent.travelAgentID = Reservation.travelAgentID WHERE Reservation.travelAgentID IS NULL;