for answer part Q SQL question, you might need the part a and b.

a.The schemas of the generated tables, with the primary keys underlined, are as follows:

Ship(shipNo, shipName, tonnage, buildDate, speed)

Event(eventId, eventName)

Port(portId, portCountry, portName)

ShipPort(shipNo, portId)

PortEvent(portId, eventId?

b.database:

DROP TABLE Ship;

DROP TABLE Event;

DROP TABLE Port;

DROP TABLE ShipPort;

DROP TABLE PortEvent;

CREATE TABLE Ship (shipNo INTEGER NOT NULL PRIMARY KEY, shipName CHAR(15), Tonnage INTEGER NOT NULL, buildDate DATE NOT NULL, Speed VARCHAR(6));

CREATE TABLE Event (eventId VARCHAR(7) NOT NULL PRIMARY KEY, eventName CHAR(13));

CREATE TABLE Port (portId VARCHAR(3) NOT NULL PRIMARY KEY, portCountry CHAR(14), portName CHAR(15));

CREATE TABLE ShipPort (shipNo INTEGER NOT NULL, portId VARCHAR(3) NOT NULL, primary key(shipNo, portId));

CREATE TABLE PortEvent (portId VARCHAR(3) NOT NULL, eventId VARCHAR(7) NOT NULL, primary key(PortId, eventId));

INSERT INTO Ship VALUES (9104005, 'Adonia', 98000, '08-FEB-2004', '21');

INSERT INTO Ship VALUES (0019238, 'Amazing Grace', 1585, '10-JAN-2012', 'Sails');

INSERT INTO Ship VALUES (1893233, 'Amsterdam' , 61000, '05-SEP-2014'. '22.5');

INSERT INTO Ship VALUES (1029833, 'Aurora', 76000, '16-JUL-2006', '24');

INSERT INTO Ship VALUES (1938821, 'Black Prince', 11209, '25-JUN-1976', '24.5');

INSERT INTO Ship VALUES (2832911, 'Black Watch', 28668, '31-DEC-1981', '21');

INSERT INTO Ship VALUES (9283321, 'Dawn Princess', 98000, '30-MAR-1984', '21');

INSERT INTO Ship VALUES (3903211, 'Galaxy', 77713, '21-SEP-1979', '21.5');

INSERT INTO Ship VALUES (1120322, 'Genesis', 220000, '11-NOV-1978', '22.3');

INSERT INTO Ship VALUES (3829111, 'Horizon', 46811, '17-MAR-2012', '21.4');

INSERT INTO Ship VALUES (3822211, 'Jewel of the Seas', 90090, '20-AUG-2003', '25');

INSERT INTO Ship VALUES (4833928, 'Norwegian Crown', 34242, '01-JUN-1981', '22');

INSERT INTO Ship VALUES (3928111, 'Norwegian Dawn', 92250, '09-FEB-1978', '25');

INSERT INTO Ship VALUES (3920000, 'Oceana' , 77000, '28-JUL-2003' ,'21');

INSERT INTO Ship VALUES (3029992, 'Polynesia', 430, '09-MAY-1972', 'Sails');

INSERT INTO Ship VALUES (3400049, 'Rhapsody', 78491, '23-FEB-2012', '22');

INSERT INTO Ship VALUES (2939222, 'Sensation', 70367, '31-DEC-2012', '21');

INSERT INTO Ship VALUES (3992019, 'Serenata', 133500, '07-NOV-1974', '20.5');

INSERT INTO Event VALUES ('Event1', 'BBQ');

INSERT INTO Event VALUES ('Event2', 'Shooting');

INSERT INTO Event VALUES ('Event3', 'Jet Ski');

INSERT INTO Event VALUES ('Event4', 'Gold');

INSERT INTO Event VALUES ('Event5', 'Whales');

INSERT INTO Event VALUES ('Event6', 'Scuba');

INSERT INTO Event VALUES ('Event7', 'Casino');

INSERT INTO Event VALUES ('Event8', 'Helicopter');

INSERT INTO Event VALUES ('Event9', 'Fishing');

INSERT INTO Event VALUES ('Event10', 'Shuffleboard');

INSERT INTO Event VALUES ('Event11', 'Ice Hotel');

INSERT INTO Port VALUES ('N/A', 'N/A');

INSERT INTO Port VALUES ('P1', 'Cayman Islands', 'George Town');

INSERT INTO Port VALUES ('P2', 'Barbados', 'Bridgetown');

INSERT INTO Port VALUES ('P3', 'Belize', 'Belize City');

INSERT INTO Port VALUES ('P4', 'Bermuda', 'Hamilton');

INSERT INTO Port VALUES ('P5', 'England', 'Southampton')

INSERT INTO Port VALUES ('P6', 'Scotland', 'Invergordon')

INSERT INTO Port VALUES ('P7', 'Puerto Rico', 'San Juan');

INSERT INTO Port VALUES ('P8', 'Venezuela', 'Isla Margarita');

INSERT INTO Port VALUES ('P9', 'Anguilla', 'Blowing Point');

INSERT INTO Port VALUES ('P10', 'St. Vincent', 'Kingstown');

INSERT INTO Port VALUES ('P11', 'New Zealand', 'Auckland');

INSERT INTO Port VALUES ('P12', 'Columbia', 'Cartegena');

INSERT INTO Port VALUES ('P13', 'St. Barts', 'Gustavia');

INSERT INTO Port VALUES ('P14', 'Antigua', 'St. John');

INSERT INTO Port VALUES ('P15', 'Russia', 'St. Petersburg');

INSERT INTO Port VALUES ('P16', 'Denmark', 'Copenhagen');

INSERT INTO Port VALUES ('P17', 'Costa Rica', 'Puerto Limon');

INSERT INTO Port VALUES ('P18', 'Martinique', 'Fort de France');

INSERT INTO Port VALUES ('P19', 'Italy', 'Messina');

INSERT INTO Port VALUES ('P20', 'Spain', 'Barcelona');

INSERT INTO Port VALUES ('P21', 'Spain', 'Ibiza');

INSERT INTO Port VALUES ('P23', 'Monaco', 'Monte Carlo');

INSERT INTO Port VALUES ('P24', 'Italy', 'Santa Margherita');

INSERT INTO Port VALUES ('P25', 'Italy', 'Civitavecchia');

INSERT INTO Port VALUES ('P26', 'Belgium', 'Zeebrugge');

INSERT INTO Port VALUES ('P27', 'Corfu', 'Kerkira');

INSERT INTO Port VALUES ('P28', 'Norway', 'Kristiansand');

INSERT INTO Port VALUES ('P29', 'Finland', 'Helsinki');

INSERT INTO Port VALUES ('P30', 'Sweden', 'Stockholm');

INSERT INTO ShipPort VALUES (9104005, 'P1');

INSERT INTO ShipPort VALUES (9104005, 'P2');

INSERT INTO ShipPort VALUES (19238, 'P2');

INSERT INTO ShipPort VALUES (19238, 'P3');

INSERT INTO ShipPort VALUES (19238, 'P4');

INSERT INTO ShipPort VALUES (19238, 'N/A');

INSERT INTO ShipPort VALUES (1893233, 'P3');

INSERT INTO ShipPort VALUES (1893233, 'P4');

INSERT INTO ShipPort VALUES (1029833, 'P14');

INSERT INTO ShipPort VALUES (1029833, 'P4');

INSERT INTO ShipPort VALUES (1938821, 'P7');

INSERT INTO ShipPort VALUES (1938821, 'N/A');

INSERT INTO ShipPort VALUES (1938821, 'P13');

INSERT INTO ShipPort VALUES (2832911, 'P8');

INSERT INTO ShipPort VALUES (2832911, 'P9');

INSERT INTO ShipPort VALUES (2832911, 'P10');

INSERT INTO ShipPort VALUES (9283321, 'P11');

INSERT INTO ShipPort VALUES (9283321, 'P12');

INSERT INTO ShipPort VALUES (3903211, 'P13');

INSERT INTO ShipPort VALUES (1120322, 'P14');

INSERT INTO ShipPort VALUES (1120322, 'N/A');

INSERT INTO ShipPort VALUES (1120322, 'P4');

INSERT INTO ShipPort VALUES (3829111, 'P17');

INSERT INTO ShipPort VALUES (3829111, 'P18');

INSERT INTO ShipPort VALUES (3829111, 'N/A');

INSERT INTO ShipPort VALUES (3822211, 'P20');

INSERT INTO ShipPort VALUES (3822211, 'P21');

INSERT INTO ShipPort VALUES (4833928, 'N/A');

INSERT INTO ShipPort VALUES (4833928, 'P23');

INSERT INTO ShipPort VALUES (3928111, 'P24');

INSERT INTO ShipPort VALUES (3928111, 'P25');

INSERT INTO ShipPort VALUES (3928111, 'P22');

INSERT INTO ShipPort VALUES (3920000, 'P27');

INSERT INTO ShipPort VALUES (3920000, 'N/A');

INSERT INTO ShipPort VALUES (3029992, 'P19');

INSERT INTO ShipPort VALUES (3029992, 'N/A');

INSERT INTO ShipPort VALUES (3400049, 'P28');

INSERT INTO ShipPort VALUES (3400049, 'P29');

INSERT INTO ShipPort VALUES (2939222, 'P30');

INSERT INTO ShipPort VALUES (2939222, 'P15');

INSERT INTO ShipPort VALUES (3992019, 'P16');

INSERT INTO ShipPort VALUES (3992019, 'P6');

INSERT INTO ShipPort VALUES (3992019, 'P26');

INSERT INTO ShipPort VALUES (3992019, 'P5');

INSERT INTO PortEvent VALUES ('P1', 'Event1');

INSERT INTO PortEvent VALUES ('P2', 'Event2');

INSERT INTO PortEvent VALUES ('P2', 'Event9');

INSERT INTO PortEvent VALUES ('P3', 'Event2');

INSERT INTO PortEvent VALUES ('P4', 'Event3');

INSERT INTO PortEvent VALUES ('N/A', 'Event4');

INSERT INTO PortEvent VALUES ('P3', 'Event9');

INSERT INTO PortEvent VALUES ('P4', 'Event5');

INSERT INTO PortEvent VALUES ('P14', 'Event2');

INSERT INTO PortEvent VALUES ('P4', 'Event8');

INSERT INTO PortEvent VALUES ('P7', 'Event2');

INSERT INTO PortEvent VALUES ('N/A', 'Event4');

INSERT INTO PortEvent VALUES ('P13', 'Event6');

INSERT INTO PortEvent VALUES ('P8', 'Event5');

INSERT INTO PortEvent VALUES ('P9', 'Event6');

INSERT INTO PortEvent VALUES ('P10', 'Event9');

INSERT INTO PortEvent VALUES ('P11', 'Event5');

INSERT INTO PortEvent VALUES ('P12', 'Event2');

INSERT INTO PortEvent VALUES ('P13', 'Event8');

INSERT INTO PortEvent VALUES ('P14', 'Event3');

INSERT INTO PortEvent VALUES ('N/A', 'Event10');

INSERT INTO PortEvent VALUES ('P4', 'Event3');

INSERT INTO PortEvent VALUES ('P17', 'Event9');

INSERT INTO PortEvent VALUES ('P18', 'Event8');

INSERT INTO PortEvent VALUES ('N/A', 'Event4');

INSERT INTO PortEvent VALUES ('P20', 'Event7');

INSERT INTO PortEvent VALUES ('P21', 'Event6');

INSERT INTO PortEvent VALUES ('N/A', 'Event10');

INSERT INTO PortEvent VALUES ('P23', 'Event7');

INSERT INTO PortEvent VALUES ('P24', 'Event1');

INSERT INTO PortEvent VALUES ('P25', 'Event1');

INSERT INTO PortEvent VALUES ('P22', 'Event2');

INSERT INTO PortEvent VALUES ('P27', 'Event1');

INSERT INTO PortEvent VALUES ('N/A', 'Event4');

INSERT INTO PortEvent VALUES ('P19', 'Event1');

INSERT INTO PortEvent VALUES ('N/A', 'Event10');

INSERT INTO PortEvent VALUES ('P28', 'Event8');

INSERT INTO PortEvent VALUES ('P29', 'Event11');

INSERT INTO PortEvent VALUES ('P30', 'Event11');

INSERT INTO PortEvent VALUES ('P15', 'Event9');

INSERT INTO PortEvent VALUES ('P16', 'Event3');

INSERT INTO PortEvent VALUES ('P6', 'Event9');

INSERT INTO PortEvent VALUES ('P26', 'Event8');

INSERT INTO PortEvent VALUES ('P5', 'Event7');

Q:Use SQL commands for the following queries. Produce the queries as well as the query results.

1.List, in Ascending order of totals, the total number of ships by speed.

2.List, in alphabetical order, both the names of the ports and the ships that dock at each port.

3.Using a nested subquery, list the total number of ships which dock at Barbados.

4.List, in alphabetical order, the names of the events which are only organised on-board ship (In other words, where port id, port country and port name are all N/A).