Databases Reference
In-Depth Information
c. Find the average cost of the motors (see
PARTTYPE) supplied by supplier number 3728.
d. List
g. Find the total cost of the parts used in each
dishwasher manufactured on February 28, 2010.
Only include in the results those dishwashers that
used at least $200 in parts.
h. List the highest0paid inspectors.
i. List the highest0paid inspectors who were hired
in 2009.
j. Among all of the inspectors, list those who earn
more than the highest-paid inspector who was
hired in 2009.
the
names
of
the
inspectors
who
were
inspecting
refrigerators
(see
APPLIANCE-
TYPE) on April 17, 2011.
e. What was the highest inspection score achieved
by a refrigerator on November 3, 2011?
f. Find the total amount of money spent on Quality
Appliance products by each buyer from Mexico,
Venezuela, and Argentina.
MINICASES
1.
Consider the following relational database for Happy
Cruise Lines. It keeps track of ships, cruises, ports, and
passengers. A ''cruise'' is a particular sailing of a ship
on 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
environment.
• Both ship number and ship name are unique in the
SHIP Table.
• A ship goes on many cruises over time. A cruise is
associated with a single ship.
• A port is identified by the combination of port name
and country.
• As indicated by the VISIT Table, a cruise includes
visits to several ports and a port is typically included
in several cruises.
• Both Passenger Number and Social Security Number
are unique in the PASSENGER Table. A particular
person has a single Passenger Number that is used for
all the cruises she takes.
• The VOYAGE Table indicates that a person can
take many cruises and a cruise, of course, has many
passengers.
PORT Table
PORTNAME
COUNTRY
NUMDOCKS
MANAGER
VISIT Table
CRUISENUM
PORTNAME
COUNTRY
ARRDATE
DEPDATE
−−−−−−−
−−−−−−
PASSENGER Table
PASSENGERNUM
PASSENGERNAME
SOCSECNUM
STATE
COUNTRY
VOYAGE Table
PASSENGERNUM
CRUISENUM
ROOMNUM
FARE
−−−−−−−−−−−
−−−−−−−−
Write
SQL
SELECT
commands
to
answer
the
following queries.
a. Find the start and end dates of cruise number 35218.
b. List the names and ship numbers of the ships built
by the Ace Shipbuilding Corp. that weigh more than
60,000 tons.
c. List the companies that have built ships for Happy
Cruise Lines.
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
Shipbuilding Corp. that have been launched since
2000.
f. How many ports in Venezuela have at least three
docks?
SHIP Table
SHIPNUM
SHIPNAME
BUILDER
LAUNCHDATE
WEIGHT
CRUISE Table
CRUISENUM
STARTDATE
ENDDATE
DIRECTOR
SHIPNUM
−−−−−−
Search WWH ::
Custom Search