Revised and corrected 2013 Feb 13: Identified primary keys in the database schema, corrected hint on problem #4.
Facility with basic SQL
A database for Bookworm and Co. has the schema shown in Table 1. In the Author table, aborn and adied are the years of the author’s birth and death. In Book, pid is a foreign key reference to Publisher; bdate is the year of the book’s first publication. In City, cname is the name of the city, cstate is its state or province.
Table 1: Schema for the bookworm database. Primary keys are underlined. There are some foreign key references to link the tables together; you can make use of these with natural joins.
Author(aid, alastname, afirstname, acountry, aborn, adied).
Book(bid, btitle, pid, bdate, bpages, bprice).
City(cid, cname, cstate, ccountry).
To access the database, logon to merlin.iue.edu and enter the command:
$ psql bookworm
General instructions: Use one SQL statement for each query. Do not create any additional tables or views—not even temporarily.
These are some ideas about how to save your queries in a file and get your query output into a file.
M-x sql-postgres RET (i.e., Alt-X or Escape-X, sql-postgres, then RETURN or ENTER) starts psql in an Emacs buffer. Answer the prompts as follows, being careful to leave the server blank:
User: Database: bookworm Server:
You may give your username for the “User” prompt or leave it blank. You should not be asked for a password. If you enter “localhost” or “merlin.iue.edu” for the server, you will probably get an error message about “Ident authentication failed.”
C-c C-c(control-c twice) send paragraph
C-c C-rsend region
C-c C-bsend buffer
(These commands are also in the Emacs SQL menu.)
After you have tested your queries, if you have saved them in a file, a nice way of showing that they work correctly is to use the psql
\i command, which reads an SQL file and executes its queries. For example, to run queries from input file “queries.sql”, enter the psql command
You can also run
psql, like any Unix command, with redirection of input and output:
$ psql bookworm < queries.sql > results.sql
Turn in your SQL queries and the result of each query. The queries and results can be combined in one listing, or listed separately (one listing of queries, one listing of results). In either case, be sure to clearly identify each query and query result with its problem number (1 to 10); you may do this with comments in the source file.
Please do not include erroneous versions of the queries or their results. That is, if you had to try more than once to get the correct query, omit the earlier versions of the query and their output.
10 queries, 4 points each, total 40 points.