CSCI 340- Introduction to Database Assignment- Department of Computer Science and Information Systems, Texas A&M University.
Topics: Relational Database Retrieval - SQL:
Project:
1. Consider the following relational database that Best Airlines uses to keep track of its mechanics, their skills, and their airport locations. Mechanic number (MECHNUM), airport name (AIRNAME), and skill number are all unique fields. SIZE is an airport's size in acres. SKILLCAT is a skill's category, such as an engine skill, wing skill, tire skill, etc. YEARQUAL is the year that a mechanic first qualified in a
particular skill; PROFRATE is the mechanic's proficiency rating in a particular skill.
|
MECHNUM
|
MECHNAME
|
TELEPHONE
|
SALARY
|
AIRNAME
|
MECHANIC Table
|
AIRNAME
|
CITY
|
STATE
|
SIZE
|
YEAROPENED
|
AIRPORT Table
|
SKILLNUM
|
SKILLNAME
|
SKILLCAT
|
SKILL Table
|
MECHNUM
|
SKILLNUM
|
YEARQUAL
|
PROFRATE
|
QUALIFICATION Table
Write SQL SELECT commands to answer the following queries.
a. List the names and telephone numbers of all of the mechanics.
b. List the airports in California that are at least 20 acres in size and have been open since 1935. Order the results from smallest to largest airport.
c. List the airports in California that are at least 20 acres in size or have been open since 1935.
d. Find the average size of the airports in California that have been open since 1935.
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? Only include in your answer 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.
Homework (not to be turned in):
2. Consider the following relational database for the Quality Appliance Manufacturing Co. The database is designed to track the major appliances (refrigerators, 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 are major department stores, home improvement chains, and wholesalers. Buyer numbers are unique.
• An appliance may be inspected by several inspectors. There is clearly a many-to- many relationship between appliances and inspectors, as indicated by the INSPECTION table.
• 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.)
|
SUPPLIERNUM
|
SUPPLIERNAME
|
CITY
|
COUNTRY
|
TELEPHONE
|
SUPPLIER Table
|
PARTNUM
|
SUPPLIERNUM
|
PARTTYPE
|
COST
|
APPLIANCENUM
|
PART Table
|
APPLIANCENUM
|
APPLIANCETYPE
|
DATEMANUF
|
BUYERNUM
|
PRICE
|
APPLIANCE Table
|
BUYERNUM
|
BUYERNAME
|
CITY
|
COUNTRY
|
CREDITRATING
|
BUYER Table
|
INSPECTORNUM
|
INSPECTORNAME
|
SALARY
|
DATEHIRE
|
INSPECTOR Table
|
APPLIANCENUM
|
INSPECTORNUM
|
DATEINSPECTION
|
SCORE
|
INSPECTION Table
Write SQL SELECT commands to answer the following queries.
a. List the names, in alphabetic order, of the suppliers located in London, Liverpool, or Manchester, UK.
b. List the names of the suppliers that supply motors (see PARTTYPE) costing between $50 and $100.
c. Find the average cost of the motors (see PARTTYPE) supplied by supplier number 3728.
d. List the names of the inspectors who were inspecting refrigerators (see APPLIANCETYPE) on April 17, 2003.
e. What was the highest inspection score achieved by a refrigerator on November 3, 2003?
f. Find the total amount of money spent on Quality Appliance products by each buyer from Mexico, Venezuela, and Argentina.
g. Find the total cost of the parts used in each dishwasher manufactured on February 28, 2004. Only include in the results those dishwashers that used at least $200 in parts.
h. List the highest paid inspectors.
i. List the highest paid inspectors who were hired in 2002.
j. Among all of the inspectors, list those who earn more money than the highest paid inspector who was hired in 2002.