This assignment is ungraded and should not be submitted. The questions review key concepts from the reading for this module.

Expected Duration
45 minutes – 1.5 hours
Deadline
None (no explicit submission is required)
Points
None

Help Policy

Authorized Resources
Any
Notes
You may jointly work on this assignment with classmates

Assignment

Answer the following questions from Fundamentals of Database Management Systems (solutions):

  1. What are the four basic operations that can be performed on stored data? (Question 4.1)

    CRUD: Create, Read, Update, and Delete

  2. How does the SQL SELECT command relate to the relational Select, Project, and Join concepts? (Question 4.5)

    SELECT combines the select (\(\sigma\)), project (\(\pi\)), and join (e.g., Cartesian product (\(\times\)) and natural join (\(\bowtie\))) operations from the relational algebra.

  3. What is the purpose of the DISTINCT operator? (Question 4.14)

    The DISTINCT operator removes duplicate rows from a query’s results.

  4. What is the purpose of the ORDER BY clause? (Question 4.15)

    The ORDER BY clause sorts a query’s results based on the specified expressions (e.g., attributes).

  5. Explain the difference between the SUM and COUNT built-in functions. (Question 4.17)

    SUM calculates the total of numeric values. COUNT counts the total number of non-NULL values.

  6. Describe the purpose of the GROUP BY clause. Why must the attribute in the GROUP BY clause also appear in the SELECT clause? (Question 4.18)

    The GROUP BY clause combines rows with matching values when calculating an aggregate function.

    Generally one includes the attributes listed in the GROUP BY clause in the SELECT clause so that the output is meaningful – i.e., one can identify which group corresponds to which aggregate value. Technically, though, the inverse is true: an attribute listed in the SELECT clause must also appear in the GROUP BY clause, but one can group by additional attributes beyond those that appear in the output.

  7. Describe the purpose of the HAVING clause. How do you decide whether to place a row-limiting predicate in the WHERE clause or in the HAVING clause? (Question 4.19)

    The HAVING clause is analogous to the WHERE clause but filters based on the results of applying an aggregate function. That is, the WHERE clause filters the values that are input to the aggregate function whereas the HAVING clause filters the outputs of the aggregate function.

  8. What SQL command do you use to produce a new table structure? What SQL command do you use to discard a table? (Question 7.18)

    CREATE TABLE and DROP TABLE respectively

  9. 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. (Exercise 4.2)

    Supplier
    suppliernum, suppliername, city, country, president
    Part
    partnum, suppliernum, parttype, cost, appliancenum
    Appliance
    appliancenum, appliancetype, datemanuf, buyernum, price
    Buyer
    buyernum, buyername, city, country, creditrating
    Inspector
    inspectornum, inspectorname, salary, datehire
    Inspection
    appliancenum, inspectornum, dateinspection, score

    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:

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

      SELECT suppliername
FROM Supplier
WHERE city IN ('London', 'Liverpool', 'Manchester')
  AND country = 'UK'
ORDER BY suppliername ASC;

    2. List the names of the suppliers that supply motors (see parttype) costing between $50 and $100.

      SELECT suppliername
FROM Supplier NATURAL INNER JOIN Part
WHERE parttype = 'motor'
  AND (50 <= cost AND cost <= 100);

    3. Find the average cost of the motors (see parttype) supplied by supplier number 3728.

      SELECT AVG(cost)
FROM Supplier NATURAL INNER JOIN Part
WHERE parttype = 'motor'
  AND suppliernum = 3728;

    4. List the names of the inspectors who were inspecting refrigerators (see appliancetype) on April 17, 2011.

      SELECT inspectorname
FROM Inspector NATURAL INNER JOIN Inspection
               NATURAL INNER JOIN Appliance
WHERE appliancetype = 'refrigerator'
  AND dateinspection = '2011-04-17';

    5. What was the highest inspection score achieved by a refrigerator on November 3, 2011?

      SELECT MAX(score)
FROM Inspection NATURAL INNER JOIN Appliance
WHERE appliancetype = 'refrigerator'
  AND dateinspection = '2011-11-03';

    6. Find the total amount of money spent on Quality Appliance products by each buyer from Mexico, Venezuela, and Argentina.

      SELECT buyernum, SUM(price)
FROM Buyer NATURAL INNER JOIN Appliance
WHERE country IN ('Mexico', 'Venezuela', 'Argentina')
GROUP BY buyernum;

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

      SELECT appliancenum, SUM(cost)
FROM Part NATURAL INNER JOIN Appliance
WHERE appliancetype = 'dishwasher'
  AND datemanuf = '2010-02-28'
GROUP BY appliancenum
HAVING SUM(cost) >= 200;

    8. List the highest paid inspectors.

      SELECT inspectornum  -- or inspectorname
FROM Inspector
WHERE salary = (SELECT MAX(salary) FROM Inspector);

    9. List the highest paid inspectors who were hired in 2009.

      SELECT inspectornum  -- or inspectorname
FROM Inspector
WHERE datehire BETWEEN '2009-01-01' AND '2009-12-31'
  AND salary = (SELECT MAX(salary)
                FROM Inspector
                WHERE datehire BETWEEN '2009-01-01' AND '2009-12-31');

    10. Among all of the inspectors, list those who earn more than the highest-paid inspector who was hired in 2009.

      SELECT inspectornum  -- or inspectorname
FROM Inspector
WHERE salary > (SELECT MAX(salary)
                FROM Inspector
                WHERE datehire BETWEEN '2009-01-01' AND '2009-12-31');