LBSC 690: Introduction to Information Technology

Assignment 3

This assignment is worth 30 points. It is due before class on November 19.

LBSC 690: Assignment #3 (Fall 2012)

You've already downloaded this SQLite database of records from Project Gutenberg (bad data, warts and all) as part of Assignment 0. So it's already in your Terpconnect account, but if you'd rather use your personal machine, you'll have to download it again.

Take a look at the slides from class for an example on how to connect to Terpconnect using your favorite SSH program, and here's how to copy/paste in Putty.

  1. (10 points) Let's find out which authors were born in a year that ends with the same two digits of the year you were born in (Hint: Use the LIKE or % operators we discussed in class)? Supply SQL commands to:
    1. Give a list of five.
    2. Give a complete list of all born in a year that ends with the same last two digits of your birth year and who died before 1900.
  2. (20 points) Now let's turn our attention to the LOC subjects associated with the books in the database. Supply SQL commands to:
    1. Print how many LOC subjects there are in this database.
    2. Print the top twenty subject headings (by name, not id) sorted by the number of books they're relevant to.
    3. Show all the book titles associated with a subject that interests you (if you're working with others, be sure to choose different subjects).
Grades will be based on: Send your SQL commands (and relevant output) along as an attachement to jbg@umiacs.umd.edu with the subject starting with [LBSC690 Assignment3].