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 among 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).

Write SQL SELECT commands to answer the following queries.

a. List the names, in alphabetic order, of the suppliers located in London, Liverpool, and Manchester, UK.

b. List the names of the suppliers that supplymotors (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, 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.
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.

Transcribed Image Text:

SUPPLIER Table SUPPLIERNUM SUPPLIERNAME CITY COUNTRY PRESIDENT PART Toble PARTNUM SUPPLIERNUM PARTTYPE COST APPLIANCENUM APPLIANCE Toble APPLIANCENUM APPLIANCETYPE DATEMANUF BUYERNUM PRICE BUYER Table BUYERNUM BUYERNAME CITY COUNTRY CREDITRATING INSPECTOR Table INSPECTORNUM INSPECTORNAME SALARY DATEHIRE