CSE3330 Project #2

Query formulation
Monday October 25, before midnight
Worth 8% of the final grade


Express in SQL and evaluate in MySql the following queries:

  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 total value of all past shipping orders to all warehouses purchased by the customer named "John Smith".
  5. print the total number of available copies of a book with ISBN 123456789 stocked in all warehouses.
  6. print the ISBN and title of all books that are out of stock (ie, there is no warehouse that has copies of this book).
  7. print the average price of all current shopping baskets.
  8. for each different author, print the author's ssn and name, and the total number of books she authored.
  9. for each different warehouse, print the total number of books stocked in the warehouse.
  10. for each different customer, print the customer name and the average price of all shipping orders to this customer.
You may use different values from Ramez Elmasri, John Smith, ISBN 123456789, and W5, if you like. Write your queries in a file queries.sql and evaluate them using mysql on Omega. You should add more data into your database so that each one of the above queries prints at least two rows. You need to submit your queries.sql file, the exported schema and data (as the one you submitted for project #1, but updated/corrected), and a printout of the results when you evaluate your queries against your data. To get a printout of the results in mysql, use the command tee output.txt and then do source queries.sql to execute the queries.

You must use the following form to submit the three files (queries.sql, your-netid.sql, and output.txt) electronically (one file at a time). You may submit each file as many times as you like, but only the most recently submitted file will be retained and evaluated.

Submit Project #2:

Last modified: 10/11/10 by Leonidas Fegaras