The PostgreSQL system with the employee database for the Chapter 4 homework is installed on host erdos. The PostgreSQL interactive SQL parser is named psql.
Documentation: A man page for psql is available, which describes the command-line options and gives some examples of how to use it. Use ``man psql'' on erdos to read it.
There is also very complete documentation about the query processing engine and the SQL syntax it accepts. Using any web browser, visit http://www.postgresql.org/idocs/
You can run the PostgreSQL interpreter in interactive mode or batch mode.
Interactive mode: To run SQL in interactive,
line-oriented mode, give the command:
where dbname is the name of the database to which you wish to connect. In this mode, commands are entered line by line, in response to a prompt in the form of the database name followed by => for the first line, and -> for subsequent lines.
For the homework Exercise 4.2, the name of the database is
employee so you would say
The query processor maintains a command history, so you can recall previously typed lines using the up-arrow key, and you can edit a line by moving around with the left and right arrow keys and deleting or inserting text. In PostgreSQL, a SQL command can span many lines, and is not sent to be interpreted until it is terminated either by a semicolon or the special command g, and you press the Enter key.
If you discover an error in a query after several lines have already been typed, you have two basic options for fixing it:
When you are finished using psql, give the command to quit:
Batch mode: After you have become familiar with SQL in interactive mode, the best way to produce output to hand in is to run it in batch mode.
To run the SQL interpreter in batch mode, give the command
psql -e -f filename employee
where filename is the name of a previously prepared file of SQL commands. The -e option specifies echoing of the commands as they are processed; otherwise only the output of the queries will appear. You can redirect this output to a file or to the printer, e.g.
psql -e -f filename employee | lpr
The employee database: In order to make the attribute names legal SQL, the schemas for the tables in the employee database have been modified from the ones given in the text, by replacing hyphens with underscores. The table schemas in the PostgreSQL employee database are as follows:
|employee ( employee_name, street, city )|
|works ( employee_name, company_name, salary )|
|company ( company_name, city )|
|manages ( employee_name, manager_name )|
Practice: For practice, you can try out the
example queries in Chapter 4 using the banking enterprise
database, which has also been installed on erdos, containing the data shown in Figures
3.1-3.7. The attribute names are the same as those given in the
text, except that hyphens have been replaced by underscores. To
access this database, on erdos use the command
You can also practice creating tables and performing insertion,
deletion, and update operations. Your account on erdos has been given
permission to create a database, in which you can define any tables
you like. The database can be named anything you like, but to avoid
collisions with other users on the system, it is best to give it the
same name as your login name. To create your
database, at the shell prompt say
This creates your personal database. Since no database name is specified in this command, the default is to name it with your login name. This command only needs to be done once; the database is permanent. Then connect to the database to create tables and to perform queries and updates by saying
Again, the default in the absence of a database name is to use the database with your login name.
Both the employee and the banking databases can be installed on a computer where you have a SQL processor installed. Scripts that create the databases and populate them with data are on the class web page, http://www.dsm.fordham.edu/~moniot/Classes/Database/. These use insert statements in standard SQL to populate the tables, so the scripts should work properly with little or no modification on any reasonable SQL system.