Using psql

Laboratory Exercise 2

CMP 420: Database Systems
Lehman College, City University of New York
20 February 2003

More on Unix

The Unix operating system has many, useful built-in commands. The beginning of each lab has several commands that you should try and make sure you understand how they work:


In the first lab, we used the PostgreSQL (pronounced "Post-gres-cue-el") database package, via the graphical interface, pgaccess. To use this interface, you must be on a machine running unix or the windowing system, called X. In addition to the machines in this lab, there are 4 machines in the Student Research Room from which you can directly access the server comet and use pgaccess (they're located along the back wall and say "Unix only" on them).

Today, we will focus on the text-based interface to PostgreSQL, called psql. We will be working with the text-based program for two reasons. First, you can work with this interface anywhere, by telnetting into comet. Second, the commands for the text interface to the database server are very similar to the commands that will be embedded into Java programs later in the course.

Telnet into comet and create a database for today's lab.

One way to enter the information into your database is to type in every line by hand. Another way is to read in the database stored as a file. We will use a sample database from PostgreSQL Notes by Richard Huxton. First, download or copy the following two files into your directory. If you have netscape running, you can save the following files to your directory by right clicking and choosing the "save" option from the menu: lab2tables.txt and lab2data.txt. Since you're logged onto comet, you can also copy the files directly by typing:

cp ~stjohn/public_html/teaching/dbSpr03/lab2tables.txt .
cp ~stjohn/public_html/teaching/dbSpr03/lab2data.txt .

Now that you have the files in your directory, look at what's inside them using more. The first contains the commands to create the tables. The example system involves four main tables:

Note that when the tables are created, each attribute is followed by its type. The data file has 20 companies, 10 products, 100 orders, and 100 diary entries.

Next, create your database:

    createdb MYNAMElab3
where MYNAME is your name.

And read in the file that creates the tables:

    psql -f lab3_tables.txt MYNAMElab3
and the files that has the data:
    psql -f lab3_data.txt MYNAMElab3
As these files load, you will see a text message corresponding to each command as its being executed. So, this could take a while given the size of the data file.

Now, let's interactively view the database using psql. First, type

psql MYNAMElab3
This will run the PostgreSQL interactive sql monitor. A short text menu will be displayed to the screen, as well as the name of the current database to which you are connected. Type
This will list the basic commands and short descriptions of what they do.

Type in the command:

This will display the tables in the database. If you would also like to see the attributes of a given table, type
    \d tablename
where tablename is the name of the table you're interested in.

Now, we're ready to start writing queries. You can either type the queries directly in at the psql prompt. Or, you can type them into a text file (using the text editor of your choice) and then load the file in using the following command at the psql prompt:

    \i filename
where filename is the name that contains your queries.

You must follow every SQL command with a semi-colon (";")-- since you can type your queries on multiple lines that is your signal to the program that you are finished entering the query. If you forget to type the semi-colon, psql will wait until you do.

Try typing the following query:

    select co_name from companies;
As output, you should see a list of the 20 companies in the database.

Submitting Your Lab

For today's lab, you should write queries that do the following:
  1. gives the product codes contained in the database
  2. gives the product codes and how many have been ordered of each.
    (Hint: Group by product code, and sum the quantity ordered.)
  3. gives the product codes and the minimum number order, the average number ordered, and the maximum number ordered.
    (Hint: Group by product code, and use the appropriate aggregate operators.)
  4. gives the companies, by name, and the products that have been ordered from them. Note that the orders table stores the companies by ID numbers, not by names.

Final Comments

Before leaving the lab, you need to log out and shut down your computer. First, you should quit all applications. Then, go to the K menu in the bottom left hand corner and select Log Out. Choose the Shut Down option, and shut off the monitor when you're done.

Remember to pick up any files you sent to the printer and to take any personal belongings. It is difficult to get back into the lab later (since it's locked when not in use), so, it's worth making sure you haven't forgotten anything.