Formatting SQL*Plus Output

It can be helpful to format the table output column widths and names for the SQLplus command-line. For an example script, see code/format.sql; for details, see Formatting SQL*Plus Reports.

Using Advanced SQL Query Features

In these exercises, you query the Centrepointe church database, see code/databases/cpdb/*. You may need to add a few carefully chosen sample data items to ensure that your queries actually return something.

Exercise 5.1

If possible, write SQL queries that:

  1. Retrieve the cross-product of all person and all household records. How many records to you get and why? As an optional challenge, can you use SQL to compute this number?

  2. Retrieve the people who have birthdays specified in the database, ordered by day-of-year (i.e., Jan 1 birthdays go before Jan 2 birthdays, regardless of the year). Note that you can compute the day of year value using the Oracle function: TO_CHAR(birthdate, 'DDD').

Store these queries and explanations in lab05_1.sql .

Here are some further query exercises that use nested queries, both correlated and non-correlated, tuple variables and set operations. Oracle syntax is not always identical to the ANSI standard used in the text; you can find the Oracle SQL reference linked to the course policies.

Exercise 5.2

Write queries that:

  1. Get the youngest person in the database. Write this both as a sub-select and as a ROWNUM query (see the notes below). Consider implementing your sub-select without using aggregate functions (e.g., MAX()).

  2. Get the IDs and full names of people who share the same first name. What happens when there are three or more people who share the same name?

  3. Get the names of all people who are on the music team but not in Byl’s home group. Write this both as a sub-select and as a set operations query.

Go back and indicate which of your nested selects are correlated and which are not correlated.

Store these queries and explanations in lab05_2.sql .

We can use Oracle’s “magic” column named ROWNUM to limit our query results (cf. TOP() in mySql). Use ROWNUM to retrieve the first record of a SELECT result as follows. 

SELECT *
FROM (SELECT * FROM table(s) WHERE condition(s) ORDER BY specification(s))
WHERE ROWNUM = 1;

Because Oracle assigns the row numbers for the resulting table before applying the ORDER BY clause (and the SELECT, GROUP BY and HAVING clauses as well), you need to encapsulate the selection and ordering inside an inline view, i.e., a sub-select in the FROM clause, and then select the first row number of the resulting table. For more details, see On ROWNUM and Limiting Results.

Exercise 5.3 [Homework]

Write an SQL query that creates phonebook entries as follows.

  1. Produce an appropriate phone-book entry for “traditional” family entries, e.g.:

    VanderLinden, Keith and Brenda - 111-222-3333 - 2347 Oxford St.
  2. Extend your solution to handle families in which both spouses keep their own names, e.g.: 
    VanderLinden, Keith and Brenda Roorda - 111-222-3333 - 2347 Oxford St.
  3. Finally, extend your solution to include single-adult families, e.g.: 
    Doe, Jane - 111-222-3333 - 2347 Main St.

List only the parents and the singles, not the children. Store your results in lab05_3.sql.

Checking in

We will grade your work according to the following criteria: