Seussology DB II

Objective

For this assignment, you will demonstrate your skills and understanding of SQL Queries by creating queries that create, read, update, and delete data from the Seussology Database.

Project Setup

1. Clone the Repository

Clone this repository from GitHub and use the provided file to complete the assignment.

2. Import the Seussology Database

To import the database, using phpMyAdmin, complete the following instructions.

1. Open phpMyAdmin at http://localhost/phpmyadmin(Windows) or http://phpmyadmin.test(macOS)
2. Log in to phpMyAdmin using the root username and password
3. From the phpMyAdmin homepage, click on the "Import" tab found at the top of the page.
4. Under the "File to import" section, click the "Choose File" button. Choose the seussology.sql file found in this repository.
5. Click the "Go" button found at the bottom of the page.
6. Once the importing has been completed, click on the seussology database, which should appear in the list on the left side of the page.
7. With the seussology database selected, click on the "SQL" tab at the top of the page. From this page, you can execute custom SQL commands.

Requirements

Create ONE query for each of the 10 tasks below. The queries should be saved in the queries.sql file.

1. Insert a new quote into the quotes table with the following data:

Columns	Values
quote	"The fools that I saw were none other than you"
book_id	9

2. Using a subquery, insert the quote, "Just stew! Pooh." for the book "Steak for Supper" into the quotes table.

3. Update the book year (book_year) of the book with the title "Tadd and Todd" to 1950 in the books table.

4. Using subqueries, set all of the quotes in the quotes table for the book "The Big Brag" to the book id for the book "Yertle the Turtle"

The quotes table before the query

The quotes table after the query

NOTE

Do NOT use the book ids or quote ids directly. You need to retrieve the ids using the book titles. This should be done in ONE query.

NOTE

You MUST complete query 1 first!

5. Remove the book "The Big Brag" from the books table.

NOTE

You MUST complete query 4 first!

6. Using an INNER JOIN, retrieve the first 5 book titles (book_title) and category name (category_name) when the results are sorted by the book title sort (book_title_sort) column.

7. Retrieve the average number of book pages (book_pages) from the books table.

8. Retrieve the newest book year (book_year) from the books table in the category of "Beginner Books".

Do NOT use the category id directly. You need to retrieve the id using the category_name. This should be done in ONE query.

9. Retrieve the category id (category_id) and the number of books in each category from the books table, and sort the results by the number of books with the highest number.

10. Using INNER JOINs, retrieve the second set of 5 book titles (book_title) and quotes (quotes) for all of the books in the "Beginner's Books" category when the results are sorted by the book title (book_title).

Rubric

Criteria	Pts
Successfully completed task #1 using one query	2
Successfully completed task #2 using one query	2
Successfully completed task #3 using one query	2
Successfully completed task #4 using one query	2
Successfully completed task #5 using one query	2
Successfully completed task #6 using one query	2
Successfully completed task #7 using one query	2
Successfully completed task #8 using one query	2
Successfully completed task #9 using one query	2
Successfully completed task #10 using one query	2
Total	20