# Querying Seussology DB

# 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.

# SELECT Statements

# Task 1

Retrieve all the data (columns) and all the books (rows) from the books table.

The SELECT statement is used to retrieve data from the database and the FROM statement is used to specify the table or tables to use. The columns can be specified as a list of column names following the SELECT statement. The * can be used to retrieve all the columns.

SELECT * FROM books;

# Task 2 DIY

Retrieve the titles of all the books (book_title) from the books table.

# Task 3

Retrieve the book with the book id (book_id) of 5 from the books table.

The WHERE clause is use to filter records or rows retrieved. A condition statement must following the WHERE clause.

SELECT * FROM books WHERE book_id = 5;

# Task 4 DIY

Retrieve the book with the title (book_title) "Hop on Pop" from the books table.

# Task 5 DIY

Retrieve all the books published (book_year) before 1960 from the books table.

# Task 6

Retrieve all the books published (book_year) during the 1970s from the books table.

The BETWEEN... AND operator check whether a value is within a range of values.

SELECT * FROM books WHERE book_year BETWEEN 1970 AND 1979

# Task 7 DIY

Retrieve all the books from the books table with a number of pages (book_pages) greater than 56 and less than 72.

# Task 8

Retrieve all the books with a cover image (book_image) from the books table.

The IS and IS NOT operators are used to test if a value is NULL

SELECT * FROM books WHERE book_image IS NOT NULL

# Task 9 DIY

Retrieve all the books without a number of pages listed (book_pages) from the books table.

# Task 10

Retrieve all the books with the word "Cat" in the title (book_title) from the books table.

The LIKE operator is use to match a string within another string. The % is used as a wildcard that matches any number of characters.

SELECT * FROM books WHERE book_title LIKE '%Cat%';

# Task 11 DIY

Retrieve all the books with the word "Egg" in the title (book_title) from the books table.

# Task 12 DIY

Retrieve all the books with the word "Egg" at the END of the title (book_title) from the books table.

# Task 13

Retrieve all the books titles (book_title) from the books table sorted in reverse alphabetical order by book title column.

The ORDER BY clause is use to sort records (rows) returned from the database. The data will be sorted by the column proceeding the ORDER BY clause. By default, the records will be sorted in ascending order. The DESC keyword is use to sort records in descending order.

SELECT book_title FROM books ORDER BY book_title DESC;

# Task 14 DIY

Retrieve all the books titles (book_title) from the books table sorted in reverse alphabetical order by book title sort (book_title_sort) column.

# Task 15

Retrieve the first 5 book titles (book_title) from the books table when sorted in alphabetical order by book title sort (book_title_sort) column.

The LIMIT clause can be used to limit the number of records (rows) returned from the database. When the LIMIT clause is followed by a single number, that number of rows will be returned (row_count).

SELECT book_title FROM books ORDER BY book_title_sort LIMIT 5;

# Task 16

Retrieve the next 5 book titles (book_title) from the books table when sorted in alphabetical order by book title sort (book_title_sort) column.

The LIMIT can be followed by two numbers separated by a comma. When this occurs, the first number is the row offset and the second number is the row_count.

SELECT book_title FROM books ORDER BY book_title_sort LIMIT 5, 5;

# Task 17 DIY

Retrieve all the book titles (book_title) whose book title sort column (book_title_sort) starts with 'F' and sort the result in alphabetical order by the book title sort column.

# INSERT Statements

# Task 18

Add the book 'I Can Draw It Myself' with the following data to the books table.

The INSERT statement is used to add new data to the database. It starts with the keywords INSERT INTO followed by the name of the table where you will be adding the record followed by a set of parentheses with the columns that you want to fill with information. The VALUES keyword and a second set of parentheses is used to add the values for each column.

INSERT INTO books (
  book_title, 
  book_title_sort, 
  book_year, 
  book_description, 
  book_image
) 
VALUES (
  'I Can Draw it Myself, By Me, Myself', 
  'I Can Draw it Myself, By Me, Myself', 
  2011, 
  'Budding artists and Seuss fans alike will be delighted to get their hands (crayons, markers, and/or pencils) on this expanded hardcover edition of a coloring classic!', 
  'http://www.seussville.com/media/assets/all-book-covers/14.jpg'
);

# Task 19 DIY

Add the book 'In a People House' with the following data to the books table.

# UPDATE Statement

# Task 20

Change the published year (book_year) to 1970 and the number of pages (book_page) to 36 for the book 'I Can Draw it Myself, By Me, Myself' in books table.

The UPDATE statement is use to change one or more records in a table. In the statement, UPDATE is followed by the table name, the keyword SET and a comma separate list of columns and values. The WHERE clause can be used to filter which records will be affected and LIMIT clause can be used to limit the number of records.

UPDATE books 
SET book_year = 1970, book_pages = 36
WHERE book_title = 'I Can Draw it Myself, By Me, Myself'
LIMIT 1;

# Task 21 DIY

Change the number of pages (book_page) to 36 for the book 'In a People House' in books table.

# DELETE Statement

# Task 22

Remove the book 'I Can Draw it Myself, By Me, Myself' from the books table.

The DELETE statement is used to remove one or more records (rows) from a table. The FROM keyword is used specify the table. The WHERE clause can be used to filter which records will be affected and LIMIT clause can be used to limit the number of records.

DELETE FROM books 
WHERE book_title = 'I Can Draw it Myself, By Me, Myself'
LIMIT 1;

# Task 23 DIY

Remove the book 'In a People House' from the books table.