# Querying Seussology DB - Part 2

# Importing the Database

To import the database, we will be using Adminer. Adminer is a GUI database manager for MySQL databases. It is also installed by default with Local by Flywheel.

# Step 1: Download SQL File

1. Download the Seussology SQL File

# Step 2: Open Adminer

1. Open Local by FlyWheel
2. Start the MTM6331 site
3. Click the "Database" tab
4. Click the "Adminer" button

# Step 3: Import Database

1. From the Adminer homepage, click on the "Import" link found in left side of the page.
2. Choose the seussology.sql file and clicked the "Execute" button

# Step 4: Select Database

1. Use the select box in the top left corner to select seussology
2. You should now see three tables, books, categories, and quotes. You will be able to complete the in-class exercise from this database.
3. Clicking the "select" next to each table name will display the table data
4. Click the "SQL command" link will allow you to execute custom SQL commands

# SQL Statements

Complete the following tasks using the SQL command screen in Adminer with the Seussology database selected.

# Aggregate Functions

# Task 1

Retrieve the number of books in the books table.

Aggregate or GROUP BY functions are used to perform calculation on a multiples rows of a single column.

The COUNT() function is use to count the number of non-NULL rows for a specify column. If the * is used in place of the column it will count every row returned.

SELECT COUNT(*) FROM books;

# Task 2 DIY

Retrieve the number of book cover images (book_image) in the books table.

# Task 3

Retrieve the category (category_id) and number of books per category (category_id) from the books table.

The GROUP BY clause is use with Aggregate Functions to group the result by one or more columns. The effect of the using the GROUP BY clause on a results of a Aggregate Functions will include a separate row for each group with the aggregate calculation occurring only for the data related to that group.

SELECT category_id, COUNT(*) FROM books GROUP BY category_id;

# Task 4 DIY

Retrieve the number of page (book_pages) and number of books per number of pages (book_pages) from the books table.

# Task 5

Retrieve the earliest published year (book_year) from the books table.

The MIN() and MAX() functions are use to minimum and maximum values of the provide column.

SELECT MIN(book_year) FROM books;

# Task 6 DIY

Retrieve the most number of pages (book_pages) from the books table.

# Task 7

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

The AVG() function will return the mean average of the values of the given column.

SELECT AVG(book_pages) FROM books;

# Task 8

Retrieve the average number of pages (book_pages) of all the books from the books table with a header of "Average Number of Pages".

Aliases are use to give a table or column a temporary name. They can also make columns and table names more readable. The AS keyword is used to set the alias, comes between the table or columns name and the alias name.

SELECT AVG(book_pages) AS `Average Number of Pages` FROM books;

# Task 9 DIY

Retrieve the average number of pages (book_pages) of all the books from the books table using the SUM() and COUNT() function. Give the column a custom header.

# JOIN Clauses

# Task 10

Retrieve the book titles (book_title) from the books table that has a quote and the corresponding quotes (quote) from the quotes table.

The JOIN clauses are used to combine the columns of tables together when returning the results. No changes are made to the actual tables. SQL has several different types of joins, but the three most common for MySQL is INNER JOIN, LEFT JOIN, and RIGHT JOIN. Each JOIN clause is followed by the name of the second table the ON keyword a join condition.

The INNER JOIN will return only the rows from the two table where the join condition is met.

SELECT books.book_title, quotes.quote, 
FROM books
INNER JOIN quotes
ON books.book_id = quotes.book_id;

# Task 11

Retrieve ALL the book titles (book_title) from the books table and any corresponding quotes (quote) from the quotes table.

The LEFT JOIN will return ALL of the rows of the first table and any rows of the second table where the join condition is met. The RIGHT JOIN will return ALL of the rows of the second table and any rows of the first table where the join condition is met.

SELECT b.book_title, q.quote
FROM books AS b
LEFT JOIN quotes AS q
ON b.book_id = q.book_id;

# Task 12 DIY

Retrieve the book titles (book_title) from the books table and their corresponding category names (category_name) from the category table.

# HAVING Clause

# Task 13

Retrieve the book titles (book_title) from the books table and number of quotes for each book for the books with 3 or more quotes.

The WHERE clause is used to filter results BEFORE any grouping occurs. As such, the WHERE clause cannot be use with aggregate functions. Instead, the HAVING clause should be used. The HAVING clause also filter results, but does AFTER grouping occurs and can be use with aggregate functions.

SELECT b.book_title AS `Title`, COUNT(q.book_id) AS `Quotes`
FROM books AS b
INNER JOIN quotes AS q
ON b.book_id = q.book_id
GROUP BY q.book_id
HAVING `Quotes` >= 3;

# Task 14 DIY

Retrieve the book titles (book_title) from the books table and number of quotes for each book for the books with 3 or more quotes and that were published before 1960.

# Subqueries

# Task 15

Retrieve the all book titles (book_title) from the books table with a lower than the average number of pages.

A subquery is a SELECT statement within another statement. In most cases, subqueries can be used in place of a join, and have the advantage of being able to query the same table twice, which is not possible with a join.

SELECT book_title, book_pages
FROM books
WHERE book_pages <
  (SELECT avg(book_pages) FROM books);

# Task 16 DIY

Change the book "Horton Hears a Who!" to be in the "Beginner Books" category.