Applied Database Systems - Tutorial 1

This tutorial is a revised version of Lena Hansson and Mark Longair from previous years of this course. The latter part is essentially the same as part of the PostgreSQL tutorial. Please let me know of any errors you find in this tutorial.

This tutorial consists of 2 sections:

 Getting Started
It is strongly suggested that you do the steps in the following section ("Using PostgreSQL on DICE") before arriving at the lab session, since it may take up to an hour for your access to the PostgreSQL database to be activated after your initial request. This section of the tutorial consists of 6 parts:
Using PostgreSQL on DICE

Access to the PostgreSQL database server on DICE for teaching purposes is restricted to those people doing relevant courses, and you must register in order to use the system.

Your first step should be to read and follow the instructions on the PostgreSQL User Self-Service Guide page, which will explain you how to register for a PostgreSQL account. The computer support people assured us that PostgreSQL accounts should be available within 24 hours of registering for the AD course. If you cannot get a PostrgeSQL account after 24 hours, please fill out computing support request.

If everything has gone well, you should now be able to interact with PostgreSQL by running the command:

psql -h pgteach

Try this now; it should bring up a prompt that looks like this:

Welcome to psql 7.4.11, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)


If that doesn't happen, and you get an error like "psql: krb5_sendauth: Ticket expired" then your Kerberos ticket has expired. You can renew it by typing:


... and entering your password when prompted. If you have other problems at this stage, something may have gone wrong in your initial registration, and you should go back to the PostgreSQL User Self-Service Guide page and check to see if your problem is discussed there.

The following sections, up to Basic SQL below, describe the basics of interacting with the PostgreSQL terminal and contain a variety of tips which are hopefully useful for anyone who is unfamiliar with command-line programs of this type.
PostgreSQL Basics

Now that you are properly registered, you shouldn't have to worry about any of the above again. If you ran psql -h pgteach as above, then you will be faced with the PostgreSQL prompt. In order to exit from the interpreter you can type \q or press ctrl-D at an empty prompt.

Some other useful commands you may need include:

  • \d to show all ("describe") tables in the database
  • \d <tablename> to describe a particular table
  • \h to see all SQL commands
  • \? to see all PostgreSQL commands

A common source of confusion when using PostgreSQL interactively is that if you make a mistake, such as not finishing your SQL statement with a semi-colon, then the command prompt may change to -> instead of =>, indicating that the interpreter thinks that you are still typing the previous command. To return to the top level, use ctrl-C. To give an example, the normal top level interpreter prompt looks like:


... and the continuation prompt looks like this:

Using External Files with PostgreSQL

Using the text editor of your choice (e.g. Emacs, XEmacs or one from the Start Menu), enter the SQL commands just as you would at the PostgreSQL prompt, making sure to complete all the commands with a semi-colon (';'). Save that file as, for example, hello.sql.

If you saved that file in your home directory then you can just open a terminal window, start the interactive terminal with psql -h pgteach and perform the commands in the file by typing:

\i hello.sql

(replacing 'hello.sql' with whatever you called your file.) If you saved the file elsewhere, change to that directory before running the interpreter, or enter the full file name after \i instead of just 'hello.sql'.

Retrieving and Editing Old Commands

Use the up-arrow to return to previous typed commands. This can be used multiple times to access even older commands.

Copying and Pasting in X Windows

Mark the text you wish to copy by clicking the left mouse button and dragging until the "darker region" covers the text you wish to copy. Then go to the application (and place) where you want to paste the text. There, click the wheel on the mouse to paste the text. If the wheel does not work, then try both mouse-buttons at once.


If you are typing a command or the name of a file in a terminal window, you may be able to complete the word automatically by hitting the Tab key. In the PostgreSQL terminal you can also use the Tab key to complete the names of tables and so on.

Example 1: I have two tables in my database, one is called "cities" and the other "weather". If I wanted to run the command "SELECT * FROM WEATHER;" I could type "SELECT * FROM W", hit Tab and the W would be completed to WEATHER.

Example 2: I have three tables, the two above and one called WINTER. Should I follow the same steps as above, PostgreSQL will display all the possible completions, in this case WEATHER and WINTER. This means that I have to add an E to my command and then hit Tab again to uniquely complete the word.

 Basic SQL

This tutorial is adapted in minor ways from the PostgreSQL tutorial. This section of the tutorial consists of 4 parts:

Basic Concepts and Introduction

PostgreSQL is a relational database management system (RDBMS). That means it is a system for managing data stored in relations. Relation is essentially a mathematical term for table. The notion of storing data in tables is so commonplace today that it might seem inherently obvious, but there are a number of other ways of organizing databases. Files and directories on Unix-like operating systems form an example of a hierarchical database. A more modern development is the object-oriented database.

Each table is a named collection of rows. Each row of a given table has the same set of named columns, and each column is of a specific data type. Whereas columns have a fixed order in each row, it is important to remember that SQL does not guarantee the order of the rows within the table in any way (although they can be explicitly sorted for display). Tables are grouped into databases, and a collection of databases managed by a single PostgreSQL server instance constitutes a database cluster.

Create the Example Tables

In order to create and examine the example tables:

  • Download this file (right click and "Save as...")
  • Start the PostgreSQL interactive terminal
  • Run the script with \i db1.sql (remembering that if you do not start pgsql from the folder in which you saved the file, then you need to qualify the name with the directory as well.)
  • \d will "describe" all the tables that were created
  • \d weather will describe that particular table
  • \d cities will describe the other table
Querying a Table

To retrieve data from a table, the table is queried. A SELECT statement is used to do this. The statement is divided into a select list (the part that lists the columns to be returned), a table list (the part that lists the tables from which to retrieve the data), and an optional qualification (the part that specifies any restrictions). For example, to retrieve all the rows of table weather, type:

 SELECT * FROM weather; 

(here * means "all columns") and the output should be:

     city      | temp_lo | temp_hi | prcp |    date
 Hayward       |      37 |      54 |      | 1994-11-29
 London        |      -5 |      25 |  0.3 | 2004-01-05
 Chicago       |      15 |      35 |  0.1 | 2003-05-06
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      43 |      57 |    0 | 1994-11-29
 Hayward       |      37 |      54 |      | 1994-11-29
(6 rows)

You may specify any arbitrary expressions in the target list. For example, you can do:

SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

This should give:

     city      | temp_avg |    date
 Hayward       |       45 | 1994-11-29
 London        |       10 | 2004-01-05
 Chicago       |       25 | 2003-05-06
 San Francisco |       48 | 1994-11-27
 San Francisco |       50 | 1994-11-29
 Hayward       |       45 | 1994-11-29
(6 rows)

Notice how the AS clause is used to relabel the output column. (It is optional.)

Arbitrary Boolean operators (AND, OR, and NOT) are allowed in the qualification of a query. For example, the following retrieves the weather of San Francisco on rainy days:

SELECT * FROM weather
    WHERE city = 'San Francisco'
    AND prcp > 0.0;

     city      | temp_lo | temp_hi | prcp |    date
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
(1 row)

As a final note, you can request that the results of a select can be returned in sorted order or with duplicate rows removed:

    FROM weather
    ORDER BY city;
 San Francisco
(4 rows)

DISTINCT and ORDER BY can be used separately, of course.

Joining Tables

Thus far, our queries have only accessed one table at a time. Queries can access multiple tables at once, or access the same table in such a way that multiple rows of the table are being processed at the same time. A query that accesses multiple rows of the same or different tables at one time is called a join query. As an example, say you wish to list all the weather records together with the location of the associated city. To do that, we need to compare the city column of each row of the weather table with the name column of all rows in the cities table, and select the pairs of rows where these values match.

Note:This is only a conceptual model. The actual join may be performed in a more efficient manner, but this is invisible to the user.

This would be accomplished by the following query:

    FROM weather, cities
    WHERE city = name;

     city      | temp_lo | temp_hi | prcp |    date    |     name      |  location
 London        |      -5 |      25 |  0.3 | 2004-01-05 | London        | (153,65.3)
 San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)
(3 rows)

Observe two things about the result set:

  • There is no result row for the city of Hayward. This is because there is no matching entry in the cities table for Hayward, so the join ignores the unmatched rows in the weather table. We will see shortly how this can be fixed.
  • There are two columns containing the city name. This is correct because the lists of columns of the weather and the cities table are concatenated. In practice this is undesirable, though, so you will probably want to list the output columns explicitly rather than using *:
SELECT city, temp_lo, temp_hi, prcp, date, location
    FROM weather, cities
    WHERE city = name;

Exercise: Attempt to find out the semantics of this query when the WHERE clause is omitted.

Since the columns all had different names, the parser automatically found out which table they belong to, but it is good style to fully qualify column names in join queries:

SELECT, weather.temp_lo, weather.temp_hi,
       weather.prcp,, cities.location
    FROM weather, cities
    WHERE =;

Join queries of the kind seen thus far can also be written in this alternative form:

    FROM weather INNER JOIN cities ON ( =;

This syntax is not as commonly used as the one above, but we show it here to help you understand the following topics.

Now we will figure out how we can get the Hayward records back in. What we want the query to do is to scan the weather table and for each row to find the matching cities row. If no matching row is found we want some "empty values" to be substituted for the cities table's columns. This kind of query is called an outer join. (The joins we have seen so far are inner joins.) The command looks like this:

SELECT * FROM weather LEFT OUTER JOIN cities ON ( =;
     city      | temp_lo | temp_hi | prcp |    date    |     name      |  location
 Chicago       |      15 |      35 |  0.1 | 2003-05-06 |               |
 Hayward       |      37 |      54 |      | 1994-11-29 |               |
 Hayward       |      37 |      54 |      | 1994-11-29 |               |
 London        |      -5 |      25 |  0.3 | 2004-01-05 | London        | (153,65.3)
 San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)
(6 rows)

This query is called a left outer join because the table mentioned on the left of the join operator will have each of its rows in the output at least once, whereas the table on the right will only have those rows output that match some row of the left table. When outputting a left-table row for which there is no right-table match, empty (null) values are substituted for the right-table columns.

Exercise: There are also right outer joins and full outer joins. Try to find out what those do.

We can also join a table against itself. This is called a self join. As an example, suppose we wish to find all the weather records that are in the temperature range of other weather records. So we need to compare the temp_lo and temp_hi columns of each weather row to the temp_lo and temp_hi columns of all other weather rows. We can do this with the following query:

SELECT, W1.temp_lo AS low, W1.temp_hi AS high,, W2.temp_lo AS low, W2.temp_hi AS high
     FROM weather W1, weather W2
     WHERE W1.temp_lo < W2.temp_lo
     AND W1.temp_hi > W2.temp_hi;
     city      | low | high |     city      | low | high
 Hayward       |  37 |   54 | San Francisco |  46 |   50
 San Francisco |  43 |   57 | San Francisco |  46 |   50
 Hayward       |  37 |   54 | San Francisco |  46 |   50
(3 rows)

Here we have relabeled the weather table as W1 and W2 to be able to distinguish the left and right side of the join. You can also use these kinds of aliases in other queries to save some typing, e.g.:

     FROM weather w, cities c
     WHERE =;

You will encounter this style of abbreviating quite frequently.

Aggregate Functions

Like most other relational database products, PostgreSQL supports aggregate functions. An aggregate function computes a single result from multiple input rows. For example, there are aggregates to compute the count, sum, avg (average), max (maximum) and min (minimum) over a set of rows.

As an example, we can find the highest low-temperature reading anywhere with:

SELECT max(temp_lo) FROM weather;
 (1 row)

If we wanted to know what city (or cities) that reading occurred in, we might try:

  SELECT city FROM weather WHERE temp_lo = max(temp_lo);     WRONG

but this will not work since the aggregate max cannot be used in the WHERE clause. (This restriction exists because the WHERE clause determines the rows that will go into the aggregation stage; so it has to be evaluated before aggregate functions are computed.) However, as is often the case the query can be restated to accomplish the intended result, here by using a subquery:

SELECT city FROM weather
     WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
  San Francisco
 (1 row)

This is OK because the subquery is an independent computation that computes its own aggregate separately from what is happening in the outer query.

Aggregates are also very useful in combination with GROUP BY clauses. For example, we can get the maximum low temperature observed in each city with:

SELECT city, max(temp_lo)
     FROM weather
     GROUP BY city;

     city      | max
 Chicago       |  15
 Hayward       |  37
 London        |  -5
 San Francisco |  46
(4 rows)

which gives us one output row per city. Each aggregate result is computed over the table rows matching that city. We can filter these grouped rows using HAVING:

SELECT city, max(temp_lo)
     FROM weather
     GROUP BY city
     HAVING max(temp_lo) < 40;
  city   | max
 Chicago |  15
 Hayward |  37
 London  |  -5
(3 rows)

which gives us the same results for only the cities that have all temp_lo values below 40. Finally, if we only care about cities whose names begin with "S", we might do

SELECT city, max(temp_lo)
     FROM weather
     WHERE city LIKE 'S%'
     GROUP BY city
     HAVING max(temp_lo) < 40;

The LIKE operator does pattern matching and is explained in the PostgreSQL User's Guide.

It is important to understand the interaction between aggregates and SQL's WHERE and HAVING clauses. The fundamental difference between WHERE and HAVING is this: WHERE selects input rows before groups and aggregates are computed (thus, it controls which rows go into the aggregate computation), whereas HAVING selects group rows after groups and aggregates are computed. Thus, the WHERE clause must not contain aggregate functions; it makes no sense to try to use an aggregate to determine which rows will be inputs to the aggregates. On the other hand, HAVING clauses always contain aggregate functions. (Strictly speaking, you are allowed to write a HAVING clause that doesn't use aggregates, but it's wasteful: The same condition could be used more efficiently at the WHERE stage.)

Observe that we can apply the city name restriction in WHERE, since it needs no aggregate. This is more efficient than adding the restriction to HAVING, because we avoid doing the grouping and aggregate calculations for all rows that fail the WHERE check.

End of Tutorial 1. Please send any corrections or suggestions to Heiko Müller.