Complete all of the exercises below. There are five $(5)$ exercises for each chapter from Week $2\text{'}$s readings. All of these exercises use the my$\_$guitar$\_$shop database Download my$\_$guitar$\_$shop database.

Your first task is to create this my$\_$guitar$\_$shop in MySQL using the techniques learned in Week $1\text{'}$s readings. The Create db script is on the eLumin Windows desktop in the class files folder.

Chapter $3$: How to Retrieve Data from a Single Table

Enter and run your own SELECT statements $($submit all in Notepad saved in $.$sql file format. Number each answer using commenting marks.$)$

In these exercises, you$$ll enter and run your own SELECT statements.

Write a SELECT statement that returns four columns from the Products table: product$\_$code, product$\_$name, list$\_$price, and discount$\_$percent. Then, run this statement to make sure it works correctly.

Add an ORDER BY clause to this statement that sorts the result set by list price in descending sequence. Then, run this statement again to make sure it works correctly. This is a good way to build and test a statement, one clause at a time.

Write a SELECT statement that returns one column from the Customers table named full$\_$name that joins the last$\_$name and first$\_$name columns.

Format this column with the last name, a comma, a space, and the first name like this:

Doe, John

Sort the result set by the last$\_$name column in ascending sequence.

Return only the customers whose last name begins with letters from B to J$.$

NOTE: When comparing strings of characters, $$B$$ comes before any string of characters that begins with $$B$.$ For example, $$B$$ comes before $$Book$.$

Write a SELECT statement that returns these columns from the Products table:

product$\_$name The product$\_$name column

list$\_$price The list$\_$price column

date$\_$added The date$\_$added column

Return only the rows with a list price that$$s greater than $500$ and less than $2000.$

Sort the result set by the date$\_$added column in descending sequence.

Write a SELECT statement that returns these column names and data from the Products table:

product$\_$name The product$\_$name column

list$\_$price The list$\_$price column

discount$\_$percent The discount$\_$percent column

discount$\_$amount A column that$$s calculated from the previous two columns

discount$\_$price A column that$$s calculated from the previous three columns

Round the discount$\_$amount and discount$\_$price columns to $2$ decimal places.

Sort the result set by the discount$\_$price column in descending sequence.

Use the LIMIT clause so the result set contains only the first $5$ rows.

Write a SELECT statement that returns these column names and data from the Order$\_$Items table:

item$\_$id The item$\_$id column

item$\_$price The item$\_$price column

discount$\_$amount The discount$\_$amount column

quantity The quantity column

price$\_$total A column that$$s calculated by multiplying the item price by the quantity

discount$\_$total A column that$$s calculated by multiplying the discount amount by the quantity

item$\_$total A column that$$s calculated by subtracting the discount amount from the item price and then multiplying by the quantity

Only return rows where the item$\_$total is greater than $500.$

Sort the result set by the item$\_$total column in descending sequence.

Chapter $4$: How to Retrieve Data from Two or More Tables

Write a SELECT statement that joins the Categories table to the Products table and returns these columns: category$\_$name, product$\_$name, list$\_$price.

Sort the result set by the category$\_$name column and then by the product$\_$name column in ascending sequence.

Write a SELECT statement that joins the Customers table to the Addresses table and returns these columns: first$\_$name, last$\_$name, line$1,$ city, state, zip$\_$code.

Return one row for each address for the customer with an email address of allan.sherwood@yahoo.com.

Write a SELECT statement that joins the Customers table to the Addresses table and returns these columns: first$\_$name, last$\_$name, line$1,$ city, state, zip$\_$code.

Return one row for each customer, but only return addresses that are the shipping address for a customer.

Write a SELECT statement that joins the Customers, Orders, Order$\_$Items, and Products tables. This statement should return these columns: last$\_$name, first$\_$name, order$\_$date, product$\_$name, item$\_$price, discount$\_$amount, and quantity.

Use aliases for the tables.

Sort the final result set by the last$\_$name, order$\_$date, and product$\_$name columns.

Use the UNION operator to generate a result set consisting of three columns from the Orders table:

ship$\_$status A calculated column that contains a value of SHIPPED or NOT SHIPPED

order$\_$id The order$\_$id column

order$\_$date The order$\_$date column

If the order has a value in the ship$\_$date column, the ship$\_$status column should