For this lab, you need not write a true lab report. Instead, just turn in your SQL queries and the results they produced. (Except for one problem, where I specifically ask you to also include an English version of your query.) If a query produces a very large result, you can replace a large chunk of rows from the middle with "...", leaving only the first few and last few rows. However, you should in all cases include PostgreSQL's report of how many rows there were, which it prints in parentheses at the end. If in any case you have reason to doubt that the result is correct, but haven't been able to track down what is wrong with your SQL query or fix it, you should indicate what is wrong with the result.
Be sure to answer each question using a single SQL query that directly produces the desired result. It is not acceptable to issue one query, manually extract some information from the result, and use that to type in a second query, which produces the desired result.
Your queries should not produce results with duplicate rows. In some
cases this may require that you use the keyword
"distinct" after "select". Also, in the
book's problem 3.1(g), which asks for the ID numbers of pairs of
agents who live in the same city, you should observe the following:
Try not to make your queries unnecessarily complex. In particular, don't mention a table if it isn't needed to find the result.
Note that some queries are easier to formulate than others, and I haven't necessarily listed them from easiest to hardest. So if you are stuck on one, you may want to go on and then come back when you have more experience. (Asking for help is also a good idea when you are stuck.)
First you should configure your environment for using PostgreSQL, by giving the following commands to the shell. (This assumes you are using csh or tcsh as your shell.)
set path=(/usr/local/pgsql/bin $path) setenv MANPATH :/usr/local/pgsql/man(To have these commands done each time you start a csh or tcsh, you can put them in the .cshrc file.) Now you should be able to use the man command to get information about PostgreSQL commands, for example
man createdbfor information on creating a database. (There is also documentation for PostgreSQL on the web, linked from the MCS-243 page.) And, you should be able to run PostgreSQL commands.
Start by replicating the example I gave in class. Create a database (named with your username, which is the default) on kilpinen, and connect to it using psql, the interactive SQL interface:
createdb -h kilpinen.mcs psql -h kilpinen.mcs(If you want to specify a database name other than your user name, so as to keep databases separate, be sure to prefix the database with your username, so as to not run into conflict with other users. For example, I might have two separate databases called max_movies and max_cap.) Now load in a file of SQL commands to create the movie_info and movie_actors tables and put the data into the tables:
\i /Net/solen/home/m/a/max/jterm/j2000/movies/dbload.sql(Note that there will be error messages at the beginning about trying to drop tables that don't exists. That is OK. The "drop table" commands are in the file in case you want to reload the database after having modified it.) Now use select queries like I showed in class to answer the following questions:
After getting comfortable with PostgreSQL by using the movies database (which I demonstrated in class), switch to the book's CAP database. It can be loaded in the same way, but with "cap" in place of "movies" in the pathname. Do exercise 3.1 parts (a)-(g), (l), (n), (q), (s), and (t). This exercise is on page 192, but you'll also need to refer to exercise 2.5 on pages 84-86.
Instructor: Max Hailperin