Read Chapter 4 of the textbook, which talks about the basic statement to retriev

Question

Read Chapter 4 of the textbook, which talks about the basic statement to retrieve relational data using SQL (Structure Query Language).

Then, go to page 102, section Minicase, number 1 and write the correct statement (command or instruction) using SQL to retrieve the necessary data requested in a word document.

ONLY NEED G-M ANSWERED

MINI CASES 1. Consider the following relational database for Happy PORT Table Cruise Lines. It keeps track of ships, cruises, ports, and passengers. A "cruise" is a particular sailing of a ship NUMDOCKS PORTNAME COUNTRY MANAGER a particular date. For example, the seven-day journey of the ship Pride of Tampa that leaves on June 13, 2011, is a cruise. Note the following facts about this VISIT Table environment Both ship number and ship name are unique in the CRUISENUM PORTNAME COUNTRY ARRDATE DEPDATE SHIP Table. A ship goes on many cruises over time. A cruise is associated with a single ship. PASSENGER Table A port is identified by the combination of port name PASSENGERNUM PASSENGERNAME SOCSECNUM STATE COUNTRY and country As indicated by the VISIT Table, a cruise includes visits to several ports and a port is typically included VOYAGE Table n several cruises. Both Passenger Number and Social Security Number PASSENGER NUM CRUISENUM ROOMNUMA FARE are unique in the PASSENGER Table. A particular person has a single Passenger Number that is used fo Write SQL SELECT commands to answer the all the cruises she takes following queries The VOYAGE Table indicates that a person can a. Find the start and end dates of cruise number 35218 take many cruises and a cruise, of course, has many b. List the names and ship numbers of the ships built passengers by the Ace Shipbuilding Corp. that weigh more than 60,000 tons SHIP Table c. List the companies that have built ships for Happy Cruise Lines. SHIPNUM SHIPNAME BUILDER LAUNCH DATE WEIGHT d. Find the total number of docks in all the ports in Canada. e. Find the average weight of the ships built by the Ace CRUISE Table Shipbuilding Corp. that have been launched since 2000 CRUISENUM STARTDATE ENDDATE DIRECTOR SHIPNUM many ports in Venezuela have at least three docks?

Explanation / Answer

G)
SELECT SUM(NUMDOCKS), COUNTRY FROM PORT
GROUP BY COUNTRY;

H)
SELECT COUNT(PORTNAME), COUNTRY FROM PORT
GROUP BY COUNTRY;

I)
SELECT SUM(NUMDOCKS), COUNTRY FROM PORT
WHERE SUM(NUMDOCKS) > 11
GROUP BY COUNTRY;

J)
SELECT SHIPNAME FROM SHIP
WHERE SHIPNUM =
(SELECT SHIPNUM FROM CRUISE
WHERE CRUISENUM = 35218);

K)
SELECT PASSENGERNAME, STATE, COUNTRY FROM PASSENGER
WHERE PASSENGERNUM IN
(SELECT PASSENGERNUM FROM VOYAGE
WHERE CRUISENUM =
(SELECT CRUISENUM FROM CRUISE
WHERE STARTDATE LIKE '%JUL-2011'
AND SHIPNUM =
(SELECT SHIPNUM FROM SHIP
WHERE SHIPNAME LIKE 'The Spirit of Nashville')));

L)
SELECT SHIPNAME, BUILDER FROM SHIP
WHERE WEIGHT = MAX(WEIGHT)
GROUP BY BUILDER;

M)
SELECT SHIPNAME, BUILDER FROM SHIP
WHERE WEIGHT = MAX(WEIGHT)
AND LAUNCHDATE BETWEEN #15/07/2011# AND #31/07/2011#
GROUP BY BUILDER;

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

---

---

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