INFO I308 Lab 5: Advanced SQL Queries

40 points

Learning Objectives

Increased facility with SQL, including:

• Advanced select queries, with ordering, aggregation, grouping, and subselect.
• Modifying the database with insert, update, and delete statements.

Overview

This assignment uses the same database schema as in Lab 4 for the bookworm database. It is divided into two parts. The first part requires select queries that are more complex than those in the previous lab. For example, a problem may require the group by, having, and/or order by clauses nested queries (“subselect”) or aggregation with grouping. The second part requires you to modify a copy of the database, using insert, update, and delete statements.

Advanced SELECT Queries

Solve the problems by connecting to the bookworm database on merlin.iue.edu. Use one SQL statement for each query. Nested queries count as a single SQL statement. Do not create any additional tables or views.

Problems

1. For each author, find the author’s first and last names and the total number of pages of books written by the author. Present the results sorted by total pages in descending order.

2. For each publisher, show the publisher’s name and the average price of books published by the publisher.

3. For each publisher, show the publisher’s name and the average price per page of books published by the publisher. Average price per page here means the total price divided by the total number of pages for the set of books; it is not the average of (price/number of pages). Present the results sorted by average price per page in ascending order.

4. Find the number of authors of each country.

5. Find the names of authors who have written three or more books.

Modifying the Database

For the problems in this section, each student is to create and use his or her individual database on merlin.iue.edu. Each individual database is (initially) a copy of the bookworm database. The name of each individual database is formed by substituting the individual’s user name for USER in USER_bookworm. For example, user janedoe has database janedoe_bookworm. Do not attempt to modify the bookworm database used in earlier exercises, because your account does not have the privileges needed to do so.

Instructions for creating and using your database (substitute your own user name for USER):

• Log on to merlin.

• Enter these commands in a terminal ($ represents the shell prompt; don’t type it):

  $ cd /home/info/share/i308/bookworm-db
  $ ./create-my-bookworm

• Use psql to access your database:

  $ psql USER_bookworm

• If you enter the wrong SQL commands and damage your database too badly to repair, you can recreate it. First, delete the database:

  $ dropdb USER_bookworm

  Then repeat the procedure above to create it anew.

General Hints

• For each problem, you may need to use one or more SQL statements. If you need more than one, they may need to come in a certain order to satisfy integrity constraints.
• In #7 and 8, use subselects (nested select queries) to find the primary keys of the tuples that you need to update or delete.
• Plan the statements carefully before executing them. There is no “undo” command, so if your statements achieve the wrong result, you must use additional SQL statements to correct the error.
  • Or is there an “undo” command? Remember transactions? It could be a good idea to try out your statements in the safety of a transaction.

    • Start a transaction:

      USER_bookworm-> start transaction;

    • Enter your SQL statement(s) to make changes to the database. Check the results.

    • If anything has gone wrong, undo it:

      USER_bookworm-> rollback;

    • Otherwise, make the results durable:

      USER_bookworm-> commit;

    → There is an undo command, and its name is rollback; but in order to use it, you must have started a transaction.

  • Again, remember that if the database is hopelessly damaged, you can create a fresh copy with the procedure above.

Problems

6. Increase the price of each book published after 1960 by ten percent, using only one update statement.

7. Reduce the prices of all of Charles Dickens’s books by 20 percent, using only one update statement.

8. Delete all books published after 1985 (deleting tuples from both the author_book and book tables, using only two delete statements.)
   • There are foreign key references from author_book to author and book. From which table should you delete first?
9. Insert tuples to represent the facts that we now have the book Induction by John H. Holland, Keith J. Holyoak, Richard E. Nisbett, and Paul R. Thagard; Thagard is Canadian, and the other authors are US citizens, of unknown dates; the book has 416 pages and was published by MIT Press in 1989; and its price is $38.00.
   • In view of foreign key references mentioned in #8, into which tables should you insert first?

10. After solving all the preceding problems, verify all changes made by executing the following queries exactly as shown:

    USER_bookworm-> select * from author 
                    order by alastname asc, afirstname asc;
    
    USER_bookworm-> select * from book
                    order by btitle asc;
    
    USER_bookworm-> select * from author_book
                    order by aid, bid;

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).

As in Lab 4, identify each query and query result with its problem number (1 to 10). Do not include erroneous versions of your queries or commands. Just turn in the versions that worked.

Grading

10 problems, 4 points each, total 40 points.

Revisions

• 2013 Feb 19: Revised hint in the Overview section.
• 2013 Jan 25: Using PostgreSQL instead of MySQL. New procedures enabling students to create their own databases. Additional hints.
• 2012 Feb 16: In Hints, changed “#6, 7, and 8” to “#7 and 8”.