man lsHow do you get a directory listing, ordered by the last modification time of the files?
ls | wc -lWhat 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.)
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:
Next, create your database:
createdb MYNAMElab3where MYNAME is your name.
And read in the file that creates the tables:
psql -f lab3_tables.txt MYNAMElab3and the files that has the data:
psql -f lab3_data.txt MYNAMElab3As 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 MYNAMElab3This 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:
\dtThis will display the tables in the database. If you would also like to see the attributes of a given table, type
\d tablenamewhere 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 filenamewhere 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.
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.