CSE3330 Homework #2
Due on Thursday October 13, before midnight
Worth 5% of the final grade


Consider the following relational schema:
Author ( ssn, name, address, phone )
Book ( ISBN, title, year, price, publisher )
WrittenBy ( ssn, ISBN )
Warehouse ( warehouseCode, name, address, phone )
Stocks ( ISBN, warehouseCode, copies )
Customer ( customerName, address, email, phone )
ShoppingBasket ( basketID, customerName )
Contains ( ISBN, basketID )
  1. (64 points total) Express the following queries in SQL (8 points each query):
    1. Print the titles of all books published by Addison-Wesley in 2009 that cost less than $100.
    2. Print the number of warehouses that have at least 100 copies of the book titled "SQL for Dummies".
    3. Print the name of the warehouse that stocks the largest number of different books.
    4. Print the total number of different books authored by Ramez Elmasri.
    5. Print the titles of all books with at least two authors.
    6. For each author, print the author name and the average price of the books he/she authored.
    7. For each author and for each book written by this author, print the book title, the book price, and the number of warehouses that stock this book.
    8. For each customer, print the customer name and the total price of all books contained in all shopping baskets that belong to this customer.

  2. (36 points total) Express the following queries in relational algebra (9 points each query):
    1. print the titles of all books authored by Ramez Elmasri.
    2. print the all the author names and the year of issue of the book with ISBN 123456789.
    3. print the customer names of all shipping orders submitted to the warehouse with name "W5" related to the book with ISBN 123456789.
    4. print the ISBN and title of all the books that are not contained in any shopping basket.


How to Submit the Homework

You need to submit your homework solution online using the following web form. We do not accept email or hardcopy submissions. Acceptable document formats are: Microsoft word/powerpoint document, PDF, postscript, and plain text. You may submit your files as many times as you like, but only the most recently submitted files will be retained and evaluated.

Submit Homework #2:

Last modified: 10/4/11 by Leonidas Fegaras