Assignment 3
pdf
keyboard_arrow_up
School
Albany State University *
*We aren’t endorsed by this school
Course
1121
Subject
Information Systems
Date
Dec 6, 2023
Type
Pages
2
Uploaded by KidOkapiMaster979
For this assignment you need to complete Minicases #1 on p. 102-103 of
Chapter 4.
1.
Write SQL SELECT commands to answer the following queries.
a.
Find the start and end dates of cruise number 35218.
SELECT StartDate, EndDate
FROM Cruise
WHERE CruiseNumber= 35218
b.
List the names and ship numbers of the ships built by the Ace Shipbuilding Corp.
that weigh more than 60,000 tons.
SELECT ShipName, ShipNumber
FROM Ship
WHERE Builder= ‘Ace Shipping Corp’
AND Weight > 60,000
c.
List the companies that have built ships for Happy Cruise Lines.
SELECT Distinct CruiseLine
FROM Cruise
d.
Find the total number of docks in all the ports in Canada.
SELECT SUM(Docks) AS TotalDocks
FROM Port
WHERE Country = ‘Canada’
e.
Find the average weight of the ships built by the Ace Shipbuilding Corp. that have
been launched since 2000.
SELECT AVG(Weight) AS AverageWeight
FROM Ship
WHERE Builder = ‘Ace Shipbuilding Corp’
AND LaunchYear >= 2000
f.
How many ports in Venezuela have at least three docks?
SELECT COUNT(*) AS PortCount
FROM Port
WHERE Country = ‘Venezuela’
AND NumDocks >= 3
g.
Find the total number of docks in each country. List the results in order from most
to least.
SELECT Country, SUM(NumDocks)
FROM Port
GROUP BY Country
ODER BY NumDocks Desc
h.
Find the total number of ports in each country.
SELECT Country, COUNT(*) AS PortCount
FROM Port
WHERE Country
AND NumDocks >= 3
ODER BY COUNT(*) Desc
i.
Find the total number of docks in each country but include only those countries
that have at least twelve docks in your answer.
SELECT Country, SUM(NumDocks)
FROM Port
GROUP BY Country
HAVING SUM (NumDocks) >= 12
ODER BY NumDocks Desc
j.
Find the name of the ship that operated on (was used on) cruise number 35218.
SELECT ShipName
FROM Ship inner join Cruise
ON Ship ShipNum= Cruise.ShipNum
WHERE CruiseNum= 35218
k.
List the names, states and countries of the passengers who sailed on The Spirit
of Nashville on cruises that began during July, 2011.
SELECT PassengerName, State, Country
FROM Passenger inner join Voyage
ON Passenger.PassengerNum = Voyage.PassengerNum
inner join Cruise
ON Voyage.CruiseNum = Cruise.CruiseNum
inner join Ship
ON Cruise.ShipNum = Ship.ShipNum
GROUP BY Country
WHERE ShipName = ‘The Spirit of Nashville’
AND StartDate between ‘2011-07’
l.
Find the names of the company’s heaviest ships.
SELECT ShipName
FROM Ship
WHERE Weight = (SELETE MAX(Weight) FROM Ship)
m. Find the names of the company’s heaviest ships that began a cruise between
July 15, 2011 and July 31, 2011.
SELECT ShipName
FROM Ship
AND Weight = (SELETE MAX(Weight) FROM Ship
INNER JOIN Cruise
ON Ship.ShipNum = Cruise.ShipNum
WHERE StartDate between ‘2011-07-17 and 2011-07-31’)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help