Databases Reference
In-Depth Information
e. How many airports have been open in California
since 1935?
f. How many airports have been open in each state
since 1935?
g. How many airports have been open in each state
since 1935? Include in your answer only those
states that have at least five such airports.
h. List the names of the mechanics who work in
California.
i. Fan blade replacement is the name of a skill.
List the names of the mechanics who have a
proficiency rating of 4 in fan blade replacement.
j. Fan blade replacement is the name of a skill.
List the names of the mechanics who work in
California who have a proficiency rating of 4 in
fan blade replacement.
k. List the total, combined salaries of all of the
mechanics who work in each city in California.
l. Find the largest of all of the airports.
m. Find the largest airport in California.
2. Consider the following relational database for the
Quality Appliance Manufacturing Co. The database
is designed to track the major appliances (refrig-
erators, washing machines, dishwashers, etc.) that
Quality manufactures. It also records information
about Quality's suppliers, the parts they supply, the
buyers of the finished appliances, and the finished
goods inspectors. Note the following facts about this
environment:
• Suppliers are the companies that supply Quality
with its major components, such as electric
motors, for the appliances. Supplier number is
a unique identifier.
• Parts are the major components that the suppliers
supply to Quality. Each part comes with a part
number but that part number is only unique within
a supplier. Thus, from Quality's point of view, the
unique identifier of a part is the combination of
part number and supplier number.
• Each appliance that Quality manufactures is given
an appliance number that is unique across all of
the types of appliances that Quality makes.
• Buyers
• There are one-to-many relationships between
suppliers and parts (Supplier Number is a foreign
key in the PART table), parts and appliances
(Appliance Number is a foreign key in the PART
table), and appliances and buyers (Buyer Number
is a foreign key in the APPLIANCE table).
SUPPLIER Table
SUPPLIERNUM
SUPPLIERNAME
CITY
COUNTRY
PRESIDENT
PART Table
PARTNUM
SUPPLIERNUM
PARTTYPE
COST
APPLIANCENUM
−−−−−−−−−
−−−−−−−−−−
APPLIANCE Table
APPLIANCENUM
APPLIANCETYPE
DATEMANUF
BUYERNUM
PRICE
−−−−−−−
BUYER Table
BUYERNUM
BUYERNAME
CITY
COUNTRY
CREDITRATING
INSPECTOR Table
INSPECTORNUM
INSPECTORNAME
SALARY
DATEHIRE
INSPECTION Table
APPLIANCENUM
INSPECTORNUM
DATEINSPECTION
SCORE
−−−−−−−−−−
−−−−−−−−−−
Write SQL SELECT commands to answer the
following queries.
a. List
are
major
department
stores,
home
improvement
chains,
and
wholesalers.
Buyer
the
names,
in
alphabetic
order,
of
the
numbers are unique.
• An appliance may be inspected by several
inspectors. There is clearly a many-to-many
relationship among appliances and inspectors, as
indicated by the INSPECTION table.
suppliers
located
in
London,
Liverpool,
and
Manchester, UK.
b. List the names of the suppliers that supply motors
(see PARTTYPE) costing between $50 and $100.
Search WWH ::
Custom Search