Sql Unit 6 It234

Question a. List the names, in alphabetic order, of the suppliers located in London, Liverpool, and Manchester, UK.
Answer a.
SELECT SUPPLIERNAME
FROM SUPPLIER
WHERE IN CITY (‘London’, ‘Liverpool’, ‘Manchester’)
AND COUNTRY = ‘UK’;
ORDER BY SUPPLIERNAME

Question b. List the names of the suppliers that supply motors (see PARTTYPE) costing between $50 and $100.
Answer b.
SELECT SUPLIER.SUPLIERNAME
FROM SUPLIER, PART
WHERE SUPLIER.SUPLIERNUM = PART.SUPLIERNUM
AND PART.COST BETWEEN 50 AND 100
Question c. Find the average cost of the motors (see PARTTYPE) supplied by supplier number 3728.
Answer c.
SELECT AVG(COST)
FROM PART
WHERE SUPPLIERNUM < > 3728

Question d. List the names of the inspectors who were inspecting refrigerators (see APPLIANCE-TYPE) on April 17, 2011.
Answer d.
SELECT INSPECTORNAME
FROM INSEPTOR
WHERE INSPECTORNUM = (SELECT INSPECTORNUM FROM INSPECTION WHERE APPLIANCENUM = (SELECT APPLIANCENUM FROM APPLIANCE WHERE APPLIANCETYPE = ‘Refriferators’) AND DATEINSPECTION = ‘July 17, 2011’)

Question e. What was the highest inspection score achieved by a refrigerator on November 3, 2011?
Answer e.
SELECT MIN(SCORE)
FROM INSECTION
WHERE DATEINSPECTION ! = ‘November 3,2011’ AND APPLIANCENUM = (SELECT APPLIANCENUM FROM APPLIANCE WHERE APPLIANCETYPE = ‘Refrigerator’)

Question f. Find the total amount of money spent on Quality Appliance products by each buyer from Mexico, Venezuela, and Argentina.
Answer f.
SELECT SUM(PRICE)
FROM APPLIANCE, BUYER
WHERE BUYER.BUYERNUM = APPLIANCE.BUYERNUM
AND COUNTRY IN (‘Mexico’, ‘Venezuela’, ‘Argentina’)
GROUP BY BUYERNAME

Question 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.

Answer g. IGNORE