1. Create the following tables using the SQL CREATE commands. Be sure to include
ID: 3706968 • Letter: 1
Question
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
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) SHIP NAME Carribean Princess Carribean Sunshine Ship of Dreams Sunshine of the Seas SHIP-SERVENTRYDATE |t SHIP-PASSCAPACITY 3100 1950 1804 2354 SHIP_SIZESHIP REGISTRY SHIP-CREWCAPACITY SHIP-LIFESTYLE 1181 Contemporary 1000 1992 2004 1990 142000 Liberia 74000 Norway 70000 Liberia 74000 The Netherlands 760 Premium 735 Contemporary 822 LuxuryExplanation / Answer
--------------------------------------------------------------------------------------------------------------------------------------------------------
11. List the names of all passengers with a reservation on a cruise on a ship registered in Liberia.
SQL:
Select P.Pass_name from Passenger P inner join Reservation R on P.Pass_ID = R.Pass_ID inner join Cruise C on R.Cruise_ID in (Select Cr.Cruise_ID from Cruise Cr inner join Ship S where Cr.Ship_name=Cr.Ship_name and S.Ship_Registry = 'Liberia');
---------------------------------------------------------------------------------------------------------------------------------------
12. List all ship names. For those ships used on a cruise, include the cruise departure city, departure date, and duration.
SQL:
Select Ship_Name, Cruise_DeptCity, Cruise_DeptDate, Cruise_Duration from Cruise;
-------------------------------------------------------------------------------------------------------------------------------------------------------
13. List the Cruise ID, ship name, departure date, and departure city for all cruises departing in June, 2015. (use BETWEEN)
SQL:
Select Cruise_ID,Ship_Name, Cruise_DeptCity, Cruise_DeptDate from Cruise where Cruise_DeptDate BETWEEN '01-JUN-2015' AND '30-JUN-2015';
---------------------------------------------------------------------------------------------------------------------------------------------------------
14. List the total number of ships registered in Liberia.
SQL:
select count(Ship_Name) as No_of_ships_liberia from Ship where Ship_Registry='Liberia';
-------------------------------------------------------------------------------------------------------------------------------------------------------
15. List, for each ship, the total number of cruises using that ship.
SQL:
Select Ship_name,Count(Cruise_ID) as No_of_Cruises from Cruise GROUP BY Cruise_ID;
---------------------------------------------------------------------------------------------------------------------------------------------------------
16. List the total balance due for all reservations. (Result should have a single row).
SQL:
Select Sum(Res_BalanceDue) as Total_Balance_Due from Reservation;
---------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------
18. List the same information as in #17, but show only those cruises with fewer than 3 reservations.
SQL:
-------------------------------------------------------------------------------------------------------------------------------------------------
Please repost remaining questions as another question.
As per Chegg policy, I am supposed to answer 4 sub questions. But, have answered lot of things.
Even, I am out of time.
Please do not downvote because of this reason. Please upvote if you are happy with the answers.
------------------------------------------------------------------------------------------------------------------------------------------------
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.