Increased facility with SQL, including:
selectqueries, with ordering, aggregation, grouping, and subselect.
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
nested queries (“subselect”) or aggregation with grouping. The second part requires you to modify a copy of the database, using
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.
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.
For each publisher, show the publisher’s name and the average price of books published by the publisher.
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.
Find the number of authors of each country.
Find the names of authors who have written three or more books.
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_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
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
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.
selectqueries) to find the primary keys of the tuples that you need to update or delete.
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:
Otherwise, make the results durable:
→ 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.
Increase the price of each book published after 1960 by ten percent, using only one
Reduce the prices of all of Charles Dickens’s books by 20 percent, using only one
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;
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.
10 problems, 4 points each, total 40 points.