CSE3330 Project #2

Query formulation
Tuesday October 23, before midnight
Worth 8% of the final grade


Implement in SQL and evaluate in phpMyAdmin the following queries:

  1. print the titles of all books authored by John Smith.
  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". (You may assume that the number of copies in a shopping basket (Contains.number) is equal to 1.)
  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. (You may assume that the number of copies in a shopping basket (Contains.number) is equal to 1.)
  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 warehouse code, and 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 John Smith, ISBN 123456789, and W5, if you like. You may use phpMyAdmin to run your queries one-by-one before you write them in the file queries.sql and evaluate them using mysql on Omega. Queries must end with a semicolon. You should add more data into your database so that each one of the above queries prints at least two rows (except queries 4, 5, and 7, which must print only one row).

To login on omega, you need SSH (secure shell). If you don't have secure shell, install SSH from UTA. You can use this program to login to Omega, and to download/upload files between your PC and Omega.

After you login on omega using SSH, to run MySQL, type:

mysql -p
which will ask you for a password. Use your MySQL password. Then, in MySQL, type connect xyz1234;, where xyz1234 is your login name (which is also your schema name). 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.

What to Submit

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 output.txt (the printout of the results when you evaluate your queries against your data).

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/12 by Leonidas Fegaras