SQL Database. Desperatly need help finishing this assignment. I need help with t
ID: 3707601 • Letter: S
Question
SQL Database. Desperatly need help finishing this assignment. I need help with the remainder of quesitons 19-25. Below is a link to whats been answered so far. I had to break it it up.
https://www.chegg.com/homework-help/questions-and-answers/desperate-need-help-sql-developement-pay-done-father-passed-away-last-week-got-backt-town--q28378885
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.
Explanation / Answer
=====================================
Formatting is missed while copying from word. But I marked the required queries in BOLD.
Feel free to ask for any doubts
=====================================
19. Query: select Ship_Name, Ship_Size, Ship_ServEntryDate
from SHIP
where Ship_ServEntryDate = (select max(Ship_ServEntryDate) from SHIP);
Output:
SHIP_NAME
SHIP_SIZE
SHIP_SERVENTRYDATE
Ship of Dreams
70000
2004
20.
PASS_ID
CRUISE_ID
RES_TOTALCOST
RES_BALANCEDUE
RES_SPECIALREQUEST
RES_ROOM
23451
6
1200
150
Kosher
A465
48596
5
999
250
Vegetarian
B918
78756
2
1200
200
-
G989
Query: update RESERVATION set
RES_BALANCEDUE= 0
where
PASS_ID = 48596 AND CRUISE_ID = 5
PASS_ID
CRUISE_ID
RES_TOTALCOST
RES_BALANCEDUE
RES_SPECIALREQUEST
RES_ROOM
23451
6
1200
150
Kosher
A465
48596
5
999
0
Vegetarian
B918
78756
2
1200
200
-
G989
21.
Here we are deleting reservations particular to the CRUISE_ID = 1. In question, they allow us to delete the entries. We should not delete the CRUISE from CRUISE table because the CRUISE is temporarily cancelled.
PASS_ID
CRUISE_ID
RES_TOTALCOST
RES_BALANCEDUE
RES_SPECIALREQUEST
RES_ROOM
23451
6
1200
150
Kosher
A465
48596
1
899
0
Vegetarian
A423
48596
5
999
250
Vegetarian
B918
78756
2
1200
200
-
G989
78756
1
799
300
Low salt
U912
5 rows selected.
Query: delete from RESERVATION where CRUISE_ID = 1;
2 row(s) deleted.
PASS_ID
CRUISE_ID
RES_TOTALCOST
RES_BALANCEDUE
RES_SPECIALREQUEST
RES_ROOM
23451
6
1200
150
Kosher
A465
48596
5
999
250
Vegetarian
B918
78756
2
1200
200
-
G989
22.
Query: alter table SHIP
add constraint check_ship_lifestyle
CHECK ( Ship_Lifestyle in ('Contemporary', 'Premium', 'Luxury'))
-------------------- Constraint was added.
23.
We have to join the table of PASSENGER and RESERVATION tables first using common attribute PASS_ID. Then we apply our condition RES_BALANCEDUE > 0
PASSENGER TABLE
PASS_ID
PASS_NAME
PASS_CITY
PASS_TELEPHONE
PASS_NEXTOFKIN
23451
Thomas McCoy
San Francisco
(415) 831-2121
John McCoy
48596
John Perkins
Harrisburg
(717) 876-3457
Carl Perkins
78756
Monica Renata
Clarksville
(913) 789-8798-3457
Johnn Renata
3 rows selected.
RESERVATION TABLE
PASS_ID
CRUISE_ID
RES_TOTALCOST
RES_BALANCEDUE
RES_SPECIALREQUEST
RES_ROOM
23451
6
1200
150
Kosher
A465
48596
5
999
0
Vegetarian
B918
78756
2
1200
200
-
G989
3 rows selected.
Query: select pass_name, pass_telephone, res_balancedue
from PASSENGER P, RESERVATION R
WHERE P.pass_id = R.pass_id AND RES_BALANCEDUE > 0
Result Set 15
PASS_NAME
PASS_TELEPHONE
RES_BALANCEDUE
Thomas McCoy
(415) 831-2121
150
Monica Renata
(913) 789-8798-3457
200
2 rows selected.
24.
First we should get the ship_name and max(cruise_duration) from the table. Second, we will print the details of the cruise with matching ship_name and max(cruise_duration) using nested query.
Query: select ship_name, cruise_deptdate, cruise_deptcity, cruise_duration from CRUISE
where (ship_name,cruise_duration) in
(select ship_name, max(cruise_duration) from cruise group by ship_name);
Note: In Question it was mentioned that --- “There should be one row for each ship that is used for a cruise”. But, in CRUISE table, if we observe there are two entries for rows for ship “Ship of Dreams” having CRUISE_DURATION=7 (which is a maximum for this ship). There is no rule was mentioned, which one to get.
Ideally the solution with 2 entries for “Ship of Dreams” is correct. If duration is having a distinct value, then we will get only one row.
Sample Output: (CRUISE TABLE)
CRUISE_ID
SHIP_NAME
CRUISE_DEPTDATE
CRUISE_DEPTCITY
CRUISE_DURATION
1
Sunshine of the Seas
25-MAY-15
Miami
10
2
Carribean Princess
15-JUN-15
San Juan
7
3
Sunshine of the Seas
30-JUN-15
Ft. Lauderdale
5
4
Ship of Dreams
15-JUL-15
Miami
7
5
Ship of Dreams
30-JUL-15
Ft. Lauderdale
7
6
Carribean Princess
01-JUN-15
Ft. Lauderdale
10
7
Sunshine of the Seas
30-APR-15
San Juan
14
7 rows selected.
INNER QUERY RESULT:
SHIP_NAME
MAX(CRUISE_DURATION)
Ship of Dreams
7
Carribean Princess
10
Sunshine of the Seas
14
3 rows
FINAL QUERY RESULT BASED ON INNER QUERY:
SHIP_NAME
CRUISE_DEPTDATE
CRUISE_DEPTCITY
CRUISE_DURATION
Ship of Dreams
15-JUL-15
Miami
7
Ship of Dreams
30-JUL-15
Ft. Lauderdale
7
Carribean Princess
01-JUN-15
Ft. Lauderdale
10
Sunshine of the Seas
30-APR-15
San Juan
14
4 rows selected.
25.
Query: CREATE INDEX shipname_idx
ON CRUISE (Ship_Name);
SHIP_NAME
SHIP_SIZE
SHIP_SERVENTRYDATE
Ship of Dreams
70000
2004
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.