Database Assignment

Instructions:

1. Login to our database server through ssh from you terminal:
   1. Type following command: mysql -u student -h tim50.soe.ucsc.edu tim50

2. Use the database Assg1 for assignment. To access the database type the following command in terminal: use Assg1;

3. Students can also install mysql work bench and do their assignments. Link is given below. Mysql workbench is also installed in the BE109 lab.

https://dev.mysql.com/downloads/workbench/. (Links to an external site.)Links to an external site.

4. In a WORD compatible document and for each question:
   1. State the question
   2. Write the SQL query to answer the question
   3. Paste a section of the results from your query (copy from display)
   4. Save your .sql files corresponding to each query. For example save query1.sql containing the query for first question. You have to create such six .sql files.

5. Upload your completed assignment with all the above files in a zip file

Questions to Answer

• Find the most five expensive books. Display in decreasing order and show their Bookid and BookName for each one of them .
• Find the names of the books with authors whose name consist of charachter 'S'. Also print their corresponding Author name.
• Find the number of books published under a Publisher. Display the publisher name and count of books published, in decreasing order.
• Find all books with category 'F' such that their price is less than 501.50. Also show author name and publisher name. No dupicates are allowed.
• Display the name of the books and their corresponding reviewers satisfying the following conditions:

1.The review stars should be more than 3.

2.The review date should be after 2005;