Complete all of the exercises below using the my_guitar_shop database. Once you have a working query, paste your code into Notepad. Place all of your work in one Notepad file. Be sure to properly number each exercise SQL code with chapter and exercise number.

There are four (4) exercises for chapter 3, and four (4) exercises for chapter 4. All of these exercises use create_my_guitar_shop.sql.

Your first task is to create this database in MySQL using the techniques learned in chapters 1 and 2.

Retrieve data from a single table. In these exercises, youll enter and run your own SELECT statements.

1.) 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.

2.) Add an ORDER BY clause to this statement that sorts the result set by list price in ascending 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.

Answer to 3.2 (Just to get you started)

SELECT product_code, product_name, list_price, discount_percent

FROM products

ORDER BY list_price DESC;

3.) 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 last name in descending sequence. Return only the customers whose last name begins with letters from B to Z. NOTE: When comparing strings of characters, B comes before any string of characters that begins with B. For example, B comes before Bumstead.

4.) 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 thats greater than 500 and less than 2000. Sort the result set in descending sequence by the date_added column.