WRITE SQL queries to do following from the given cruise database tables:(AND SHOW OUTPUT SCREENSHOT TO CONFIRM CORRECTNESS) mysql)!

 

Please use Subquery in each of the following scenarios:

 

1.1 Which ship weighs the least.

 

 

1.2 Show the name of the passenger who paid the highest fare (use quantified subquery)

 

 

2. Please write 2 different queries for each of the following(do in 2 different ways) using join once and once subquery:

 

2.1:. List the names of passengers who have been on a cruise that visited New York

2.1 a. use a join

 

2.1.b: use subquery

 

2.2 List the names of passengers who have been on a cruise directed by Adams.(same write 2 different queries using:

2.2 a: use join.

 

2.2 b. use subquery

 

     

Transcribed Image Text:

The Cruise Scenario SQL query: SELECT * FROM SHIP; SHIPNUM 005 008 009 012 020 022 025 028 11111 21664 23007 24288 26964 27045 28532 29191 29890 35218 37894 SQL query: SELECT * FROM CRUISE; BUILDER Sea Joy Master The Spirit of Nashville Ajax Ocean TV Ajax Prince Al Ajax Queen Shirley Master Pacific Blue Ace Ace Ace CRUISENUM STARTDATE SHIPNAME Caracas Halifax Princess Oceania La Plata Maracaibo SQL query: SELECT * FROM PORT; Miami Naples LAUNCHDATE WEIGHT 80000 78000 75000 90000 85000 65000 58000 55000 1999-02-01 00:00:00 2003-01-30 00:00:00 2003-05-15 00:00:00 2004-04-20 00:00:00 1999-10-11 00:00:00 2000-05-01 00:00:00 1998-02-01 00:00:00 2001-08-10 00:00:00 PORTNAME COUNTRY NUMDOCKS MANAGER Venezuela 4 Canada 5 Venezuela 2 Venezuela 3 United States 4 Italy 5 ENDDATE 2003-07-05 00:00:00 2003-09-11 00:00:00 Lee 2002-07-05 00:00:00 2002-07-12 00:00:00 Smith 2002-08-14 00:00:00 2002-08-24 00:00:00 Chen 2003-03-28 00:00:00 2003-04-04 00:00:00 Smith 2003-07-01 00:00:00 2003-07-11 00:00:00 Gomez 2003-07-15 00:00:00 2003-07-22 00:00:00 Adams 2003-08-17 00:00:00 2003-08-24 00:00:00 Adams 2003-12-20 00:00:00 2003-12-27 00:00:00 Jones 2004-01-15 00:00:00 2004-01-22 00:00:00 Levin 2004-05-15 00:00:00 2004-05-29 00:00:00 Williams 2004-10-10 00:00:00 | 2004-10-22 00:00:00 Chen DIRECTOR SHIPNUM Lopez Moore Perez Gonzalez Young Angelo 008 009 005 009 020 012 012 009 020 009 020 The Cruise Scenario SQL query: SELECT * FROM SHIP; SHIPNUM 005 008 009 012 020 022 025 028 11111 21664 23007 24288 26964 27045 28532 29191 29890 35218 37894 SQL query: SELECT * FROM CRUISE; BUILDER Sea Joy Master The Spirit of Nashville Ajax Ocean TV Ajax Prince Al Ajax Queen Shirley Master Pacific Blue Ace Ace Ace CRUISENUM STARTDATE SHIPNAME Caracas Halifax Princess Oceania La Plata Maracaibo SQL query: SELECT * FROM PORT; Miami Naples LAUNCHDATE WEIGHT 80000 78000 75000 90000 85000 65000 58000 55000 1999-02-01 00:00:00 2003-01-30 00:00:00 2003-05-15 00:00:00 2004-04-20 00:00:00 1999-10-11 00:00:00 2000-05-01 00:00:00 1998-02-01 00:00:00 2001-08-10 00:00:00 PORTNAME COUNTRY NUMDOCKS MANAGER Venezuela 4 Canada 5 Venezuela 2 Venezuela 3 United States 4 Italy 5 ENDDATE 2003-07-05 00:00:00 2003-09-11 00:00:00 Lee 2002-07-05 00:00:00 2002-07-12 00:00:00 Smith 2002-08-14 00:00:00 2002-08-24 00:00:00 Chen 2003-03-28 00:00:00 2003-04-04 00:00:00 Smith 2003-07-01 00:00:00 2003-07-11 00:00:00 Gomez 2003-07-15 00:00:00 2003-07-22 00:00:00 Adams 2003-08-17 00:00:00 2003-08-24 00:00:00 Adams 2003-12-20 00:00:00 2003-12-27 00:00:00 Jones 2004-01-15 00:00:00 2004-01-22 00:00:00 Levin 2004-05-15 00:00:00 2004-05-29 00:00:00 Williams 2004-10-10 00:00:00 | 2004-10-22 00:00:00 Chen DIRECTOR SHIPNUM Lopez Moore Perez Gonzalez Young Angelo 008 009 005 009 020 012 012 009 020 009 020

Answer rating: 100% (QA)

Based on the information and instructions provided here are the SQL queries for the given scenarios ... View the full answer