NYU Database Systems (Spring'2000)
SQL Assignment - 2
This assignment will invove writing queries in SQL, Relational
Algebra, and Tuple Relational Calculus.
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.
author: name, birthplace, citizenship /* birthplace is the
city in which the author was born */
library: libname, city
book: isbn, author, title /* each book has a single author. author
here refers to the name of the author*/
in_stock: isbn, libname, quantity /* quantity can be 0 or
bindex: isbn, subject /* lists the subject(s) relevant to
that book. There can be more than 1 subject per book */
Print the birthplace and citizenship of author "Korth"
Print the names of the San Francisco and New York libraries
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.
Print the name of every author that has a book that, independent of edition,
is carried by at least two different libraries in Berkeley.
Print the title of every autobiography (subject = authorname) carried by
a library in the author's birthplace
Check if some edition of "Why I Went to Stanford" is carried by a San Francisco
Bay Area library.
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
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)
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.
Print the title of every book that, independent of edition is carried
by all libraries in Boston.
What to hand in
ALL of the above queries written in SQL
All the Odd-numbered queries in Relational Algebra
All the Even-numbered queries in Tuple Relational Calculus
Don't worry about efficiency! (much)
(In contrast to what I said in the class) If the query requires, say, the
names of all cities, the answer should NOT have duplicates in it. So, use
DISTINCT where necessary. Unnecessary use of DISTINCT or the omission of
DISTINCT where necessary will be penalized.
If you need to make any assumptions about the data, please list them on
When to hand in
Thursday, Feb 24th (gives you two weeks)
Late assignments will be fined 15% of the marks for every delayed week
Deadline will NOT be extended for anyone
The best way to learn SQL is by writing as many queries as possible.
Vishy Poosala, 2/11//2000
Back to Course Home Page