In these lab exercises, we continue to work with the Centrepointe church database (see cs342/databases/cpdb/*).

Using Outer Joins

Now, consider inner and outer joins.

Exercise 6.1

If possible, write SQL queries to do the following.

  1. Get the names and mandate statements of all teams along with the ID of their “chair” member. If a chair member does not exist, include NULL for the ID.

  2. [Optional] If you’re looking for a challenge, modify the previous query to return the chair person’s full name instead of just their ID.

Store your results in lab06_1.sql .

Using Grouping and Aggregate Functions

Now, consider aggregation and grouping.

Exercise 6.2

If possible, write SQL queries to do the following.

  1. Compute the average age of all the people in the database. Note you can use the following Oracle features in this query.

    • MONTHS_BETWEEN(laterDate, earlierDate) computes the number of months between the earlier and later dates.
    • SYSDATE gives the current date.
    • TRUNC() rounds a number down to the nearest integer.

    This query uses an agregate function, but is it doing grouping? If so, what is the group? If not, what is it doing instead?

  2. Get the household ID and count of members of all households in Grand Rapids having at least 2 members. Order the results by decreasing size.

  3. Modify the previous query to retrieve the phone number of the household as well.

Store your results in lab06_2.sql .

Checking in

We will grade your work according to the following criteria: