INFO I308 Lab 4: SQL Queries

40 points

Revised and corrected 2013 Feb 13: Identified primary keys in the database schema, corrected hint on problem #4.

Learning Objective

Facility with basic SQL select queries.

Overview

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).
Publisher(pid, pname).
Author_Book(aid, bid).
Publisher_City(pid, cid).

To access the database, logon to merlin.iue.edu and enter the command:

$ psql bookworm

Queries

General instructions: Use one SQL statement for each query. Do not create any additional tables or views—not even temporarily.

1. List the first and last names and the dates of birth and death of Russian authors.
   • Hint: The output of a correct query is a table with two rows.
2. List the titles of books having at least 1,000 pages.
   • Hint: 4 rows.
3. List the titles, publishers, and pages of books having at least 1,000 pages, sorted by number of pages in ascending order.
   • Hints: 4 rows again. Natural join will be helpful.
4. List the titles and publishers of books written by Thomas Hardy.
   • Hints: 4 rows. Consider using a natural join of four three tables.
5. What is author Gaston Leroux’s country?
   • Hint: 1 row. Yes, you can probably guess, but ask the database anyway.
6. What titles are published by publishers with an office in Massachusetts?
   • Hint: 4 rows. Is this getting monotonous?
7. What is the average number of pages in a book?
   • Hint: The sum of the digits before the decimal point is 7.
8. What is the least number of pages in a book?
   • Hints: The sum of the digits is, again, 7. A nested query is a good idea here. You solved half of this in the previous problem.
9. What is the book title with the least number of pages? (If there are ties, list all such titles.)
   • Hint: The second word does not begin with “C”.
10. In what countries does Oxford University Press have offices? List each such country only once.
    • Hint: 8 rows.

Additional Hints

These are some ideas about how to save your queries in a file and get your query output into a file.

1. Consider using Emacs for SQL support:

   1. 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.”

   2. File names ending in “.sql” are edited in SQL mode.
   3. Tell Emacs which SQL process to use (it’s silly to have to do this, because there’s only one such process—but remember, computers are dumb):
      • In the SQL menu of the buffer for your SQL file, choose Set SQLi buffer.
      • Accept the default *SQL* by pressing ENTER.
   4. SQL mode provides commands to send queries from an SQL file to the psql process. You must first have set the SQLi buffer as in the previous step.
      • C-c C-c (control-c twice) send paragraph
      • C-c C-r send region
      • C-c C-b send buffer

      (These commands are also in the Emacs SQL menu.)

2. 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 \i queries.sql

3. You can also run psql, like any Unix command, with redirection of input and output:

   $ psql bookworm < queries.sql > results.sql

What to Turn in

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.

Grading

10 queries, 4 points each, total 40 points.