Assignment 02 – Due on 02/16/22, 11.59pm
ii) SQL Practice
Import the My Guitar Shop database into MySQL workbench using attached create_my_guitar_shop.sql file for answering these questions. The expected output file is also attached (HWK2 – Expected Output.pdf). Write the following queries in a single sql file. You must submit the .sql file named hwk02-part-ii-lastname.sql (Example hwk02-part-ii-kumar.sql) for this section.
All answers must be formatted for easy comprehension. MySQL Workbench has a format command (Edit -> Format -> Beautify Query).
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.
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.
Only submit the final query including the ORDER BY clause.
2. 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 ascending sequence.
Return only the customers whose last name begins with letters from M to Z.
NOTE: When comparing strings of characters, ‘M’ comes before any string of characters that begins with ‘M’. For example, ‘M’ comes before ‘Murach’.
3. 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 in descending sequence by the date_added column.
4. Write a SELECT statement that returns these columns from the Orders table:
order_id The order_id column
order_date The order_date column
ship_date The ship_date column
Return only the rows where the ship_date column contains a null value. (Hint: use IS NULL)
5. 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 category_name and then by product_name in ascending sequence.
6. 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.
7. 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.
Must use aliases for the tables to get full credit. Sort the final result set by last_name, order_date, and product_name.
