Question
Database Mangagement (SQL) Please help with this. I am out of town due to a family emergency. I need to turn in by Thursday. Thank.
Database Mangagement (SQL)
Please help with this. I am out of town due to a family emergency. I need to turn in by Thursday. Thank. Please message me with any questions you have so we can coordinate it. Need my email let me know if it's allowed. I also repeated the questions as I copied and pasted the contents of the file mentioned in the top half they're in bold.
Task
Formulate SQL queries for each of the following operations. Before you begin writing queries, download the .sql file associated with this assignment. Paste your SQL queries below each question. You will be submitting the completed template to me, and I will be running your queries through Oracle AS WRITTEN. If a query doesn't run, you don't get full credit for it.
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}
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) |
CRUISE. Primary key: {Cruise_ID}
Column Name | Data Type | Domain | Key information |
Cruise_ID | INTEGER | Primary key | |
Ship_Name | VARCHAR(100) | Foreign key | |
Cruise_DeptDate | DATE | NOT NULL | |
Cruise_DeptCity | VARCHAR(80) | NOT NULL | |
Cruise_Duration | INTEGER |
RESERVATION. Primary key: {Cruise_ID, Pass_ID}
Column Name | Data Type | Domain | Key information |
Pass_ID | INTEGER | Foreign key, Primary key | |
Cruise_ID | INTEGER | Foreign key, Primary key | |
Res_TotalCost | NUMERIC(9,2) | >= 0 | |
Res_BalanceDue | NUMERIC(9,2) | >= 0 | |
Res_SpecialRequest | VARCHAR(30) | permitted values: Vegetarian, Vegan, Low salt, Gluten free, Kosher, Other | |
Res_Room | VARCHAR(10) |
PASSENGER. Primary key: {Pass_ID}
Column Name | Data Type | Domain | Key information |
Pass_ID | INTEGER | Primary key | |
Pass_Name | VARCHAR(100) | NOT NULL | |
Pass_City | VARCHAR(80) | ||
Pass_Telephone | VARCHAR(15) | ||
Pass_NextOfKin | VARCHAR(100) |
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 isnt 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 ships 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.
-- 1. Create the tables shown in the assignment using SQL CREATE
commands.
-- 2. Insert the data shown into the tables.
-- You should use one INSERT command for each row of data.
-- 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.
-- 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
ships 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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started