Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Question: How many cruises has each ship been on? Instead of ship number, display each ships name in your answer. Here's extra context. The Database

Question: How many cruises has each ship been on? Instead of ship number, display each ships name in your answer.

Here's extra context. The Database I'm using.

DROP DATABASE IF EXISTS HAPPY_CRUISE; CREATE DATABASE IF NOT EXISTS HAPPY_CRUISE; USE HAPPY_CRUISE;

CREATE TABLE CRUISE ( CRUISENUM INTEGER(5) NOT NULL, STARTDATE DATE, ENDDATE DATE, DIRECTOR VARCHAR(25), SHIPNUM INTEGER(2), PRIMARY KEY (CRUISENUM) );

INSERT INTO CRUISE VALUES (21644, '2002-07-05', '2002-07-12', 'Smith', 9), (23007, '2002-08-14', '2002-08-24', 'Chen', 20), (24288, '2003-03-28', '2003-04-04', 'Smith', 9), (26964, '2003-07-01', '2003-07-11', 'Gomez', 20), (27045, '2003-07-15', '2003-07-22', 'Adams', 12), (28532, '2003-08-17', '2003-08-24', 'Adams', 12), (29191, '2003-12-20', '2003-12-27', 'Jones', 9), (29890, '2004-01-15', '2004-01-22', 'Levin', 20), (35218, '2008-02-25', '2008-03-05', 'Carter', 32), (42983, '2011-07-15', '2011-07-22', 'Lucas', 28), (42996, '2011-07-22', '2011-07-29', 'Lucas', 28), (42999, '2011-07-29', '2011-08-05', 'Norton', 28), (43744, '2011-07-25', '2011-08-10', 'Goldberg', 41), (44791, '2011-07-21', '2011-07-28', 'Barton', 20), (45109, '2011-07-02', '2011-07-09', 'Adams', 25), (45231, '2011-07-09', '2011-07-16', 'Davidson', 25), (45270, '2011-07-16', '2011-08-03', 'Cohen', 25), (45382, '2011-08-03', '2011-08-10', 'Stevenson', 25);

CREATE TABLE PASSENGER ( PASSENGERNUM INTEGER(6) NOT NULL, PASSENGERNAME VARCHAR(25), SOCSECNUM INTEGER(9), STATE VARCHAR(25), COUNTRY VARCHAR(15), PRIMARY KEY (PASSENGERNUM) );

INSERT INTO PASSENGER VALUES (481912, 'DuBois', 48294713, 'Paris', 'France'), (842642, 'Smith', 10482746, 'North Carolina', 'USA'), (901734, 'Jones', 58377163, 'Tennessee', 'USA'), (133861, 'Smythe', 83776427, 'London', 'UK'), (254381, 'Lopez', 59244163, 'Madrid', 'Spain'), (310656, 'Simmons', 49271183, 'California', 'USA'), (524891, 'Carter', 72716328, 'Ohio', 'USA'), (759285, 'Jackson', 49218549, 'Tennessee', 'USA'), (628902, 'Smith', 31498577, 'Tennessee', 'USA'), (259028, 'Gonzalez', 13287482, 'Texas', 'USA'), (945198, 'Billings', 82711611, 'Oregon', 'USA'), (234149, 'Kaplan', 62945822, 'Oregon', 'USA'), (428247, 'Jennings', 48375913, 'Texas', 'USA'), (739716, 'Lopez', 32572856, 'Arkansas', 'USA'), (142174, 'Carter', 42916465, 'Georgia', 'USA'), (225729, 'Johnson', 93015012, 'Georgia', 'USA'), (425911, 'Dodd', 52478221, 'New York', 'USA'), (394587, 'Singh', 34575207, 'Illinois', 'USA');

CREATE TABLE PORT ( PORTNAME VARCHAR(25) NOT NULL, COUNTRY VARCHAR(15) NOT NULL, NUMDOCKS INTEGER(2), MANAGER VARCHAR(25), PRIMARY KEY (PORTNAME, COUNTRY) );

INSERT INTO PORT VALUES ('Vancouver', 'Canada', 23, 'Smythe'), ('Halifax', 'Canada', 18, 'Jones'), ('Montreal', 'Canada', 12, 'Williams'), ('Caracas', 'Venezuela', 18, 'Gomez'), ('Maracaibo', 'Venezuela', 12, 'Perez'), ('Puerto Cabello', 'Venezuela', 2, 'Gonzalez'), ('New York', 'USA', 32, 'Carter'), ('New Orleans', 'USA', 19, 'Adams'), ('Long Beach', 'USA', 41, 'Baker'), ('Boston', 'USA', 15, 'Martin'), ('Oakland', 'USA', 35, 'Fields'), ('Miami', 'USA', 14, 'Vincent'), ('Shanghai', 'China', 45, 'Chen'), ('Hangzhou', 'China', 37, 'Zhang'), ('Fuzhou', 'China', 26, 'Yang'), ('Mombasa', 'Kenya', 10, 'Smith'), ('Gdansk', 'Poland', 9, 'Kowalski');

CREATE TABLE SHIP ( SHIPNUM INTEGER(2) NOT NULL, SHIPNAME VARCHAR(35), BUILDER VARCHAR(25), LAUNCHDATE DATE, WEIGHT INTEGER(6), PRIMARY KEY (SHIPNUM) );

INSERT INTO SHIP VALUES (5, 'Sea Joy', 'Jones', '1999-05-12', 80000), (9, 'Ocean IV', 'Ajax', '2003-12-10', 75000), (12, 'Prince Al', 'Ajax', '2004-07-21', 90000), (20, 'Queen Shirley', 'Master', '1999-09-01', 80000), (7, 'The Spirit of Memphis', 'Ace Shipbuilding Corp.', '1998-10-20', 65000), (32, 'King of the Sea', 'Ace Shipbuilding Corp.', '2006-06-24', 55000), (28, 'Sea Peace', 'Ace Shipbuilding Corp.', '2010-09-22', 95000), (16, 'Ocean V', 'Ace Shipbuilding Corp.', '2010-01-27', 95000), (25, 'The Spirit of Nashville', 'Master', '2006-04-30', 48000), (41, 'Princess of Florida', 'Jones', '2011-02-11', 95000);

CREATE TABLE VISIT ( CRUISENUM INTEGER(5) NOT NULL, PORTNAME VARCHAR(25) NOT NULL, COUNTRY VARCHAR(15) NOT NULL, ARRDATE DATE, DEPDATE DATE, PRIMARY KEY (CRUISENUM, PORTNAME, COUNTRY) );

INSERT INTO VISIT VALUES (27045, 'Caracas', 'Venezuela', '2003-07-16', '2003-07-17'), (27045, 'Maracaibo', 'Venezuela', '2003-07-17', '2003-07-17'), (27045, 'Miami', 'USA', '2003-07-19', '2003-07-20'), (42983, 'Oakland', 'USA', '2011-07-16', '2011-07-17'), (42983, 'Vancouver', 'Canada', '2011-07-18', '2011-07-19'), (21644, 'New York', 'USA', '2002-07-06', '2002-07-07'), (21644, 'Boston', 'USA', '2002-07-07', '2002-07-08'), (21644, 'Halifax', 'Canada', '2002-07-09', '2002-07-10'), (23007, 'Fuzhou', 'China', '2002-08-15', '2002-08-16'), (23007, 'Shanghai', 'China', '2002-08-17', '2002-08-19'), (23007, 'Hangzhou', 'China', '2002-08-20', '2002-08-21'), (45270, 'Miami', 'USA', '2011-07-17', '2011-07-19'), (45270, 'New York', 'USA', '2011-07-20', '2011-07-23'), (45270, 'Halifax', 'Canada', '2011-07-28', '2011-07-31'), (44791, 'New Orleans', 'USA', '2011-07-21', '2011-07-22'), (44791, 'Miami', 'USA', '2011-07-24', '2011-07-25'), (35218, 'Long Beach', 'USA', '2008-02-25', '2008-02-26'), (35218, 'Oakland', 'USA', '2008-02-27', '2008-02-29'), (35218, 'Vancouver', 'Canada', '2008-03-01', '2008-03-04');

CREATE TABLE VOYAGE ( PASSENGERNUM INTEGER(6) NOT NULL, CRUISENUM INTEGER(5) NOT NULL, ROOMNUM INTEGER(5), FARE DECIMAL(7,2), PRIMARY KEY (PASSENGERNUM, CRUISENUM) );

INSERT INTO VOYAGE VALUES (481912, 45231, 4252, 1000), (901734, 45109, 3182, 950), (254381, 45270, 11383, 1550), (524891, 45382, 7245, 1290), (628902, 42983, 4564, 799), (945198, 45109, 8272, 800), (428247, 45231, 10754, 1256), (142174, 45270, 5346, 999), (425911, 45382, 7434, 875), (394587, 42983, 2562, 690), (842642, 24288, 4215, 875), (133861, 29191, 1155, 680), (310656, 35218, 4233, 950), (759285, 21644, 6435, 1099), (259028, 45382, 12414, 1499), (234149, 27045, 6236, 845), (739716, 27045, 5234, 725), (225729, 26964, 3662, 890);

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Filing And Computer Database Projects

Authors: Jeffrey Stewart

2nd Edition

007822781X, 9780078227813

More Books

Students also viewed these Databases questions

Question

Prepare an electronic rsum.

Answered: 1 week ago

Question

Strengthen your personal presence.

Answered: 1 week ago

Question

Identify the steps to follow in preparing an oral presentation.

Answered: 1 week ago