CPSC340A
Database Systems

Sales Orders Database

1. What is the average retail price of a mountain bike?

   +-------------+
   | AverageCost |
   +-------------+
   | 1321.250000 |
   +-------------+
   (1 row)

2. What was the date of our most recent order?

   +---------------------+
   | MostRecentOrderDate |
   +---------------------+
   | 2013-02-28          |
   +---------------------+
   (1 row)

3. What was the total amount for order number 8?

   +------------------+
   | TotalOrderAmount |
   +------------------+
   |          1492.60 |
   +------------------+
   (1 row)

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)

Bowling League Database

1. What is the largest handicap held by any bowler at the current time?

   +--------------+
   | HighHandicap |
   +--------------+
   |           52 |
   +--------------+
   (1 row)

2. Which locations hosted tournaments on the earliest tournament date?

   +-------------------+
   | TourneyLocation   |
   +-------------------+
   | Red Rooster Lanes |
   +-------------------+
   (1 row)

3. What is the last tournament date we have in our schedule?

   +----------------+
   | MostRecentDate |
   +----------------+
   | 2013-08-16     |
   +----------------+
   (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)