CPSC340A
Database Systems

Assignment 7

Aggregate Functions

Submit your your queries to the following prompts as a .sql file, with comments, on the course Inquire site.

Entertainment Agency Database

  1. What is the average salary of a booking agent?

    +--------------------+
    | AverageAgentSalary |
    +--------------------+
    |       24850.000000 |
    +--------------------+
    (1 row)
  2. Show me the engagement numbers for all engagements that have a contract price greater than or equal to the overall average contract price. (Hint: You’ll have to use a subquery to answer this request.)

    +------------------+
    | EngagementNumber |
    +------------------+
    |                6 |
    |                8 |
    |                9 |
    |               10 |
    |               12 |
    |               14 |
    |               16 |
    |               21 |
    |               24 |
    |               27 |
    |               28 |
    |               31 |
    |               37 |
    |               38 |
    |               42 |
    |               44 |
    |               46 |
    |               56 |
    |               66 |
    |               68 |
    |               71 |
    |               73 |
    |               75 |
    |               77 |
    |               79 |
    |               88 |
    |               92 |
    |               95 |
    |               98 |
    |               99 |
    |              100 |
    |              101 |
    |              102 |
    |              104 |
    |              105 |
    |              109 |
    |              110 |
    |              114 |
    |              115 |
    |              121 |
    |              124 |
    |              129 |
    |              131 |
    +------------------+
    (43 rows)
  3. How many of our entertainers are based in Bellevue?

    +----------------------+
    | NumberOfEntertainers |
    +----------------------+
    |                    3 |
    +----------------------+
    (1 row)

School Scheduling Database

  1. What is the current average class duration?

    +----------------------+
    | AverageClassDuration |
    +----------------------+
    |              78.9394 |
    +----------------------+
    (1 row)
  2. List the last name and first name of each staff member who has been with us since the earliest hire date. (Hint: You’ll have to use a subquery containing an aggregate function that evalu- ates the DateHired column.)

    +---------------+
    | StaffMember   |
    +---------------+
    | Abolrous, Sam |
    +---------------+
    (1 row)
  3. How many classes are held in room 3346?

    +----------------------+
    | TotalNumberOfClasses |
    +----------------------+
    |                   10 |
    +----------------------+
    (1 row)

Recipes Database

  1. Which recipe requires the most cloves of garlic? (Hint: You’ll need to use INNER JOINs and a subquery to answer this request.)

    +-------------+
    | RecipeTitle |
    +-------------+
    | Roast Beef  |
    +-------------+
    (1 row)
  2. Count the number of main course recipes. (Hint: This requires a JOIN between Recipe_Classes and Recipes.)

    +-----------------+
    | NumberOfRecipes |
    +-----------------+
    |               7 |
    +-----------------+
    (1 row)
  3. Calculate the total number of teaspoons of salt in all recipes.

    +----------------+
    | TotalTeaspoons |
    +----------------+
    |           8.75 |
    +----------------+
    (1 row)