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:

man commandname
This is one way to get information about a command. For example, if you would like to know more the command ls and the possible options, you type:
```
    man ls
```
How do you get a directory listing, ordered by the last modification time of the files?
more filename
This command displays the filename one screenful at a time. It's very useful for looking at a file that's longer than 26 lines.
wc filename
wc counts the number of characters, words, and lines in a file.
command1 | command2
| (pronounced: "pipe") lets you take the output from the first command and send it to input of the next command. For example, if you would like to count the number of items in your directory, you could first list your directory and pipe the output into word count:
```
    ls | wc -l
```
What does the "-l" option do?
How many student accounts are there on the system?
(Hint: if you go up one directory cd ../, you will be in the student accounts directory. Count how many accounts are there. After you're done, you can return to your directory by typing: cd.)

PostgreSQL

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:

companies - which tracks company name and address
products - one entry per product with a code and a description
orders - with company, product, quantity and order, delivery and payment dates
diary - which records changes to the other tables in addition to users' notes.

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:

\dt

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:

gives the product codes contained in the database
gives the product codes and how many have been ordered of each.
(Hint: Group by product code, and sum the quantity ordered.)
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.)
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.