Task 1. Create the following tables using the SQL CREATE commands. Be sure to in

Question

Task

1. Create the following tables using the SQL CREATE commands. Be sure to include in the CREATE statement not only the definitions of the columns (giving each a name, and specifying the data type for each), but also the definition of the primary keys and foreign keys as well. Create CHECK constraints to enforce the domain constraints indicated. The columns have the following data types:

SHIP. Primary key: {Ship_Name}

CRUISE. Primary key: {Cruise_ID}

RESERVATION. Primary key: {Cruise_ID, Pass_ID} <-- NOTE!!!!

PASSENGER. Primary key: {Pass_ID}

2. Insert the data shown into the tables. You should use one INSERT command for each row of data. (In Oracle, when inserting currency values, don't try to insert the '$' or ',' marks.)

SHIP

CRUISE   Note: Use an Oracle sequence to insert the Cruise_ID. Call the sequence cruise_id_sq .

RESERVATION

PASSENGER

3. List the name, size, passenger capacity, and crew capacity of each ship.

4. List the names and registry of all ships with a “Contemporary” lifestyle.

5. List the Cruise ID, the departure date, and ship name for all cruises departing from Miami.

6. List the Cruise ID, departure date, and departure city of all cruises longer than 5 days in duration.

7. List the name and registry of all ships used in a cruise that departs from Miami. (Use the IN + subquery construct.)

8. List the name of all passengers with a reservation on a cruise that departs from Miami. (Use the IN + subquery construct.)

9. List the name of all passengers who have requested a Vegetarian diet on a cruise. Include the name of the ship in query output. (Do a join.)

10. List the names of all passengers with a reservation on a cruise that departs from Miami on 25-May-15. Include the name of the ship in the query output. (Do a join.)

11. List the names of all passengers with a reservation on a cruise on a ship registered in Liberia.

12. List all ship names. For those ships used on a cruise, include the cruise departure city, departure date, and duration.

13. List the Cruise ID, ship name, departure date, and departure city for all cruises departing in June, 2015. (use BETWEEN)

14. List the total number of ships registered in Liberia.

15. List, for each ship, the total number of cruises using that ship.

16. List the total balance due for all reservations. (Result should have a single row).

17. For each cruise, list the cruise ID, the ship name, the departure date, the departure city, and the total number of passengers on that cruise.

18. List the same information as in #17, but show only those cruises with fewer than 3 reservations.

19. List the ship name, size, and service entry date of the largest ship (greatest size). The query should return one row of data. You may not provide any specific values in the WHERE clause (e.g., 'WHERE ship_size = 142000' is not permitted.)

20. Passenger 48596 has paid the remaining balance on his reservation on cruise #5. Make the appropriate change to the database.

21. Cruise #1 has been cancelled, due to hurricane danger. Do what is necessary to delete this cruise and the associated reservations from the database. (In a production database, such an event would not cause data to be deleted; rather, the cruise would be marked as cancelled in some way. But this isn’t a production database, and we need to practice deleting data, so…. )

22. Add a constraint to the Ship table to limit the Ship_Lifestyle values to: Contemporary, Premium, Luxury.

23. Create a view that displays the passenger name, telephone number, and balance due for those passengers who have a positive balance due.

24. For each ship, list the departure date and departure city of that ship’s cruise(s) with the longest duration. (There should be one row for each ship that is used for a cruise.)

25. Create an index on the Ship_Name column in the Cruise table.

Column Name Data Type Domain Key information Ship_Name VARCHAR(100) Primary key Ship_Size INTEGER > 0 Ship_Registry VARCHAR(50) possible values are: Norway, Liberia, The Netherlands, Jamaica, Bahamas Ship_ServEntryDate INTEGER Ship_PassCapacity INTEGER Ship_CrewCapacity INTEGER Ship_Lifestyle VARCHAR(40)

Explanation / Answer

CREATE TABLE SHIP
( Ship_Name varchar2(100) NOT NULL PRIMARY KEY,
Ship_Size numeric,
Ship_Registry varchar2(50),
Ship_ServEntryDate numeric,
Ship_PassCapacity numeric,
Ship_CrewCapacity numeric,
Ship_Lifestyle varchar2(40),
CONSTRAINT Check_Ship,
CHECK ( Ship_Size > 0 AND Ship_Registry IN ('Norway', 'Liberia', 'The Netherlands', 'Jamaica', 'Bahamas'))
);

CREATE TABLE CRUISE
( Cruise_ID numeric NOT NULL PRIMARY KEY,
Ship_Name varchar(100),
Cruise_DeptDate date NOT NULL,
Cruise_DeptCity varchar(80) NOT NULL,
Cruise_Duration numeric,
FOREIGN KEY (Ship_Name),
);

CREATE TABLE RESERVATION
( Pass_ID numeric NOT NULL PRIMARY KEY,
Cruise_ID numeric NOT NULL PRIMARY KEY,
Res_TotalCost numeric(9,2),
Res_BalanceDue numeric(9,2),
Res_SpecialRequest varchar(30),
Res_Room varchar(10),
CONSTRAINT Check_Reservation,
CHECK (Res_TotalCost >= 0 AND Res_BalanceDue >= 0),
FOREIGNN KEY (Pass_ID, Cruise_ID)
);

CREATE TABLE PASSENGER
( Pass_ID numeric NOT NULL PRIMARY KEY,
Pass_Name varchar(100) NOT NULL,
Pass_City varchar(80),
Pass_Telephone varchar(15),
Pass_NextOfKin varchar(100)
);

-------------------------------------------------------------------------------------------------

2.

INSERT INTO SHIP ( Ship_Name, Ship_Size, Ship_Registry, Ship_ServEntryDate, Ship_PassCapacity ,Ship_CrewCapacity, Ship_Lifestyle ) VALUES ('Carribean Pricess', 142000, 'Liberia', 1000, 3100, 1181, 'Contemporary');
INSERT INTO SHIP ( Ship_Name, Ship_Size, Ship_Registry, Ship_ServEntryDate, Ship_PassCapacity ,Ship_CrewCapacity, Ship_Lifestyle ) VALUES ('Carribean Sunshine', 74000, 'Norway', 1992, 1950, 760, 'Premium');
INSERT INTO SHIP ( Ship_Name, Ship_Size, Ship_Registry, Ship_ServEntryDate, Ship_PassCapacity ,Ship_CrewCapacity, Ship_Lifestyle ) VALUES ('Carribean Pricess', 70000, 'Liberia', 2004, 1804, 735, 'Contemporary');
INSERT INTO SHIP ( Ship_Name, Ship_Size, Ship_Registry, Ship_ServEntryDate, Ship_PassCapacity ,Ship_CrewCapacity, Ship_Lifestyle ) VALUES ('Carribean Pricess', 74000, 'The Netherlands', 1990, 2354, 822, 'Luxury');

INSERT INTO CRUISE (Cruise_ID ,Ship_Name ,Cruise_DeptDate ,Cruise_DeptCity ,Cruise_Duration ) VALUES ('Sunshine of the Seas','25-MAY-15','Miami',10);
INSERT INTO CRUISE (Cruise_ID ,Ship_Name ,Cruise_DeptDate ,Cruise_DeptCity ,Cruise_Duration ) VALUES ('Carribean Princess','15-JUN-15','San Juan',7);
INSERT INTO CRUISE (Cruise_ID ,Ship_Name ,Cruise_DeptDate ,Cruise_DeptCity ,Cruise_Duration ) VALUES ('Ship of Dreams','30-JUN-15','Ft. Lauderdale',5);
INSERT INTO CRUISE (Cruise_ID ,Ship_Name ,Cruise_DeptDate ,Cruise_DeptCity ,Cruise_Duration ) VALUES ('Ship of Dreams','15-JUL-15','Miami',7);
INSERT INTO CRUISE (Cruise_ID ,Ship_Name ,Cruise_DeptDate ,Cruise_DeptCity ,Cruise_Duration ) VALUES ('Sunshine of the Seas','30-JUL-15','Ft. Lauderdale',7);
INSERT INTO CRUISE (Cruise_ID ,Ship_Name ,Cruise_DeptDate ,Cruise_DeptCity ,Cruise_Duration ) VALUES ('Carribean Princess','01-JUN-15','Ft. Lauderdale',10);
INSERT INTO CRUISE (Cruise_ID ,Ship_Name ,Cruise_DeptDate ,Cruise_DeptCity ,Cruise_Duration ) VALUES ('Sunshine of the Seas','30-APR-15','San Juan',15);

INSERT INTO RESERVATION ( Pass_ID ,Cruise_ID ,Res_TotalCost ,Res_BalanceDue ,Res_SpecialRequest ,Res_Room) VALUES(23451, 6, 1200, 150, 'Kosher', 'A465);
INSERT INTO RESERVATION ( Pass_ID ,Cruise_ID ,Res_TotalCost ,Res_BalanceDue ,Res_SpecialRequest ,Res_Room) VALUES(48596, 1, 899, 0, 'Vegetarian', 'A423);
INSERT INTO RESERVATION ( Pass_ID ,Cruise_ID ,Res_TotalCost ,Res_BalanceDue ,Res_SpecialRequest ,Res_Room) VALUES(48596, 5, 999, 250, 'Vegetarian' , 'B918');
INSERT INTO RESERVATION ( Pass_ID ,Cruise_ID ,Res_TotalCost ,Res_BalanceDue ,Res_SpecialRequest ,Res_Room) VALUES(78756, 1,799,300, 'Low salt', 'U912');
INSERT INTO RESERVATION ( Pass_ID ,Cruise_ID ,Res_TotalCost ,Res_BalanceDue ,Res_SpecialRequest ,Res_Room) VALUES(78756, 2, 1200, 200, ' ', 'G989');
INSERT INTO RESERVATION ( Pass_ID ,Cruise_ID ,Res_TotalCost ,Res_BalanceDue ,Res_SpecialRequest ,Res_Room) VALUES(78756,6,1100, 250, ' ' , 'C8476');
INSERT INTO RESERVATION ( Pass_ID ,Cruise_ID ,Res_TotalCost ,Res_BalanceDue ,Res_SpecialRequest ,Res_Room) VALUES(84723,6,1200,150, 'Kosher', 'A465');
INSERT INTO RESERVATION ( Pass_ID ,Cruise_ID ,Res_TotalCost ,Res_BalanceDue ,Res_SpecialRequest ,Res_Room) VALUES(87596, 1, 699, 0, 'Vegetarian', 'J923');
INSERT INTO RESERVATION ( Pass_ID ,Cruise_ID ,Res_TotalCost ,Res_BalanceDue ,Res_SpecialRequest ,Res_Room) VALUES(98745, 2, 554, 0, 'Low salt' 'J121');
INSERT INTO RESERVATION ( Pass_ID ,Cruise_ID ,Res_TotalCost ,Res_BalanceDue ,Res_SpecialRequest ,Res_Room) VALUES(98745, 3, 599, 500, ' ' , 'A812');
INSERT INTO RESERVATION ( Pass_ID ,Cruise_ID ,Res_TotalCost ,Res_BalanceDue ,Res_SpecialRequest ,Res_Room) VALUES(98745, 4, 599, 500, ' ' , 'A409');
INSERT INTO RESERVATION ( Pass_ID ,Cruise_ID ,Res_TotalCost ,Res_BalanceDue ,Res_SpecialRequest ,Res_Room) VALUES(98745, 5, 899, 500, ' ' , 'A892');
INSERT INTO RESERVATION ( Pass_ID ,Cruise_ID ,Res_TotalCost ,Res_BalanceDue ,Res_SpecialRequest ,Res_Room) VALUES(98745, 6, 900, 100, 'Vegetarian' , 'DD876');

INSERT INTO PASSENGER (Pass_ID ,Pass_Name ,Pass_City ,Pass_Telephone ,Pass_NextOfKin ) VALUES(23451, 'Thomas McCoy, 'San Francisco' , '(415) 831-2121', 'John McCoy');
INSERT INTO PASSENGER (Pass_ID ,Pass_Name ,Pass_City ,Pass_Telephone ,Pass_NextOfKin ) VALUES(48596, 'John Perkins', 'Harrisburg', '(717) 876-3457', 'Carl Perkins');
INSERT INTO PASSENGER (Pass_ID ,Pass_Name ,Pass_City ,Pass_Telephone ,Pass_NextOfKin ) VALUES(78756, 'Monica Renata', 'Clarksville', '(913) 789-8798', 'John Renata');
INSERT INTO PASSENGER (Pass_ID ,Pass_Name ,Pass_City ,Pass_Telephone ,Pass_NextOfKin ) VALUES(84723, 'Jennifer McCoy', 'San Francisco' , '(415) 831-2121', ' John McCoy');
INSERT INTO PASSENGER (Pass_ID ,Pass_Name ,Pass_City ,Pass_Telephone ,Pass_NextOfKin ) VALUES(87596, 'Sally Perkins', 'Harrisburg', '(717) 876-3457', 'Frank Holder');
INSERT INTO PASSENGER (Pass_ID ,Pass_Name ,Pass_City ,Pass_Telephone ,Pass_NextOfKin ) VALUES(98745, 'Fred Smith', 'San Diego', '(619) 898-4578', 'Denise Hillith');

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

3.
SELECT Ship_Name, Ship_Size, Ship_PassCapacity, Ship_CrewCapacity FROM SHIP;

4.
SELECT Ship_Name, Ship_Registry FROM SHIP WHERE Ship_Lifestyle = 'Conyemporary';

5.
SELECT Cruise_ID, Cruise_DeptDate, Ship_Name FROM CRUSIE WHERE Cruise_DeptCity = 'Miami';

6.
SELECT Cruise_ID, Cruise_DeptDate, Cruise_DeptCity FROM CRUISE WHERE Cruise_Duration > 5;

7.
SELECT S.Ship_Name , S.Ship_Registry
FROM SHIP S
WHERE S.Ship_Name IN ( Select C.Ship_Name FROM CRUISE C WHERE C.Cruise_DeptCity = 'Miami');

8.
SELECT P.Pass_Name
FROM PASSENGER P
WHERE P.Pass_ID IN
( SELECT R.Pass_ID FROM RESERVATION R
WHERE R.Cruise_ID IN
( SELECT C.Cruise_ID FROM CRUISE C
WHERE C.Cruise_DeptCity = 'Miami'));

9.
SELECT P.Pass_Name , C.Ship_Name
FROM PASSENGER P
INNER JOIN RESERVATION R ON R.Pass_ID = P.Pass_ID
INNER JOIN CRUISE C ON C.Cruise_ID = R.Cruise_ID
WHERE R.Res_SpecialRequest = 'Vegetarian' ;

10.
SELECT P.Pass_Name, C.Ship_Name
FROM PASSENGER P
INNER JOIN RESERVATION R ON R.Pass_ID = P.Pass_ID
INNER JOIN CRUISE C ON C.Cruise_ID = R.Cruise_ID
WHERE C.Cruise_DeptDate = '25-MAY-15' AND C.Cruise_DeptCity = 'Miami';

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.