NYU Database Systems (Spring'2000)

SQL Assignment - 2


Description

This assignment will invove writing queries in SQL, Relational Algebra, and Tuple Relational Calculus.

Database

The queries will be over a simplified library database. The schema of the tables are as follows: (primary key attributes are underlined). Data types: Everything is a string, except "quantity", which is an integer.

Queries

  1. Print the birthplace and citizenship of author "Korth"
  2. Print the names of the San Francisco and New York libraries
  3. Print the title of every book some edition of which has between 2 and 5 (2 <= quantity <= 5) copies in stock in  a New York library.
  4. Print the name of every author that has a book that, independent of edition, is carried by at least two different libraries in Berkeley.
  5. Print the title of every autobiography (subject = authorname) carried by a library in the author's birthplace
  6. Check if some edition of "Why I Went to Stanford" is carried by a San Francisco Bay Area library.
  7. Print the titles of every pair of books by the same author, if the two books have at least two subjects in common. Make sure that every pair of titles is printed exactly once, and that a title is not paired up with itself.
  8. For each French author, print their name and the number of books they have written (different editions of the   same book do not count separately)
  9. For every country that has exactly two authors as citizens, print the total number of subjects of all the books written by both authors of that country.
  10.  Print the title of every book that, independent of edition is carried by all libraries in Boston.

What to hand in

When to hand in

Why?

  1. The best way to learn SQL is by writing as many queries as possible.

Vishy Poosala, 2/11//2000

Back to Course Home Page