Part 1 You will use MySQL Workbench to create the My Guitar Shop database to rev

Part 1
You will use MySQL Workbench to create the My Guitar Shop database to review the tables in this database and to enter SQL statements and run them against this database. All downloads are attached.
Make sure the MySQL server is running
1. Start MySQL Workbench and open a connection for the root user.
2. Check whether the MySQL server is running. If it isn’t, start it.
Use MySQL Workbench to create the My Guitar Shop database.
3. Download and open the script file named my_guitar_shop.sql Download my_guitar_shop.sqlby clicking the Open SQL Script File button in the SQL Editor toolbar. Then, use the resulting dialog box to locate and open the file.
4. Execute the entire script by clicking the Execute SQL Script button in the SQL editor toolbar or by pressing Ctrl+Shift+Enter. When you do, the Output window displays messages that indicate whether the script executed successfully. Take a screenshot.
Use MySQL Workbench to enter and run SQL statements
5. Double-click on the my_guitar_shop database to set it as the default database. When you do that, MySQL Workbench should display the database in bold.
6. Open a SQL editor tab. Then, enter and run this SQL statement:
SELECT product_name FROM products
Take a resulting screenshot.
7. Delete the e at the end of product_name and run the statement again. Note the error number and the description of the error. Take a resulting screenshot.
8. Open another SQL editor tab. Then, enter and run this statement:
SELECT COUNT(*) AS number_of_products
FROM products
Use MySQL Workbench to open and run scripts
9. Download and open the script named product_details.sql Download product_details.sql. Note that this script contains just one SQL statement. Then, run the statement. Take a resulting screenshot.
Link for above file downloads https://www.dropbox.com/scl/fo/65ol4mrgtsdivnik5xv…
Part 2
Using the My Guitar Shop database you installed in Part 1, develop the following queries.
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. Take a screenshot of the query and results.
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 names begin with letters from M to Z. Execute the query and take a screenshot of the query and the results.
NOTE: When comparing strings of characters, ‘M’ comes before any string of characters that begins with ‘M’. For example, ‘M’ comes before ‘Murach’.
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. Execute the query and take a screenshot of the query and the results.
All the screenshots should show current date.
Part 3
Using the My Guitar Shop database you installed in Part 1, develop the following queries.
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. Execute the query and take a screenshot of the query and the results.
Write a SELECT statement that joins the Customers table to the Addresses table and returns these columns: first_name, last_name, line1, city, state, zip_code.Return one row for each address for the customer with an email address of allan.sherwood@yahoo.com. Execute the query and take a screenshot of the query and the results.
Write a SELECT statement that joins the Customers table to the Addresses table and returns these columns: first_name, last_name, line1, city, state, zip_code.Return one row for each customer, but only return addresses that are the shipping address for a customer. Execute the query and take a screenshot of the query and the results.
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. Execute the query and take a screenshot of the query and the results.
All the screenshots should show current date.
Part 4
Using the My Guitar Shop database you installed in Module 1, develop the following queries.
To test whether a table has been modified correctly as you do these exercises, you can write and run an appropriate SELECT statement.
Write an INSERT statement that adds this row to the Categories table:
category_name: Brass
Code the INSERT statement so MySQL automatically generates the category_id column. Execute the query and take a screenshot of the query and the results.
Write an UPDATE statement that modifies the row you just added to the Categories table. This statement should change the category_name column to “Woodwinds,” and it should use the category_id column to identify the row. Execute the query and take a screenshot of the query and the results.
Write a DELETE statement that deletes the row you added to the Categories table in exercise 1. This statement should use the category_id column to identify the row. Execute the query and take a screenshot of the query and the results.
Write an INSERT statement that adds this row to the Products table:
product_id:
category_id:
product_code:
category_name:
description:
list_price:
discount_percent:
date_added:
The next automatically generated ID
4
dgx_640
Yamaha DGX 640 88-Key Digital Piano
Long description to come.
799.99
0
Today’s date/time.
Use a column list for this statement. Execute the query and take a screenshot of the query and the results
All the screenshots should show current date.
Part 5
Using the My Guitar Shop database you installed in Part 1, develop the following queries.
Write a SELECT statement that returns these columns:
The count of the number of orders in the Orders table
The sum of the tax_amount columns in the Orders table
Execute the query and take a screenshot of the query and the results.
Write a SELECT statement that returns one row for each category that has products with these columns:
The category_name column from the Categories table
The count of the products in the Products5table
The list price of the most expensive product in the Products table.
Sort the result set so the category with the most products appears first. Execute the query, and take a screenshot of the query and the results.
Write a SELECT statement that returns one row for each customer that has orders with these columns:
The email_address column from the Customers table
The sum of the item price in the Order_Items table multiplied by the quantity in the Order_Items table
The sum of the discount amount column in the Order_Items table multiplied by the quantity in the Order_Items table
Sort the result set in descending sequence by the item price total for each customer. Execute the query and take a screenshot of the query and the results.
Write a SELECT statement that returns one row for each customer that has orders with these columns:
The email_address column from the Customers table
A count of the number of orders
The total amount for each order (Hint: First, subtract the discount amount from the price. Then, multiply by the quantity.)
Return only those rows where the customer has more than one order. Sort the result set in descending sequence by the sum of the line item amounts. Execute the query and take a screenshot of the query and the results.
All the screenshots should show current date.