SQL Help13

For the final project, you will be working with a Guitar Shop.  You will need the MySQL server and MySQL workbench installed, just as it has been throughout the class. You will also need the following file:

Project Requirements:

1. Execute the attached create_my_guitar_shop.sql to create the database you will use for this project.

2. Write an SQL script that does the following and save this script as CIS276DA_FinalExercise2_JAM2334209.sql . Save the output of this script as CIS276DA_FinalExercise2_ JAM2334209csv

· Display the product name, list price, discount_percent, discount_amount, and discount_price of all products from the Products Table, ordered by the product name.

· The discount amount can be calculated from the discount percent and the list price.  Round this to 2 decimal places.

· The discount price can be calculated from the discount amount, the discount percent, and the list price.  Round this to 2 decimal place.

· Sort the results from highest to lowest price and only show the 5 highest priced products in the results.

3. Write an SQL script that does the following and save this script as CIS276DA_FinalExercise3_ JAM2334209.sql . Save the output of this script as CIS276DA_FinalExercise3_ JAM2334209.csv

· Display the customers name, from the Customers table, as lastname, firstname separated by a comma and a space.  (Ex.  Doe, John).  Sort the results by last name alphabetically, but only return the names that begin with the letters M through Z.

4. Write an SQL script that does the following and save this script as CIS276DA_FinalExercise4_ JAM2334209.sql . Save the output of this script as CIS276DA_FinalExercise4_ JAM2334209.csv

· Display the lastname, firstname, orderdate, productname, itemprice, discountamount, and quantity as one result set from the Customers, Orders, Order_Items, and Products tables, using aliases for the tables. Sort the results by the last name, order date, and product name, in that order.

5. Write an SQL script that does the following. 

· Add the following product to the Products table:

· product_id: The next automatically generated ID

· category_id: 4

· product_code: dgx_640

· product_name: Yamaha DGX 640 88-Key Digital Piano

· description: Long description to come.

· list_price: 799.99

· discount_percent: 0

· date_added: Today’s date/time.

· Save this script as CIS276DA_FinalExercise5a_MEID.sql

· Save the output of this script as CIS276DA_FinalExercise5a_MEID.csv

· Update the previous record to change the discount percent to 35% instead of 0

· Save this script as CIS276DA_FinalExercise5b_ JAM2334209.sql

· Save the output of this script as CIS276DA_FinalExercise5b_ JAM2334209.csv

6. Write an SQL script that does the following and save this script as CIS276DA_FinalExercise6_ JAM2334209.sql . Save the output of this script as CIS276DA_FinalExercise6_ JAM2334209.csv

· Your results should display one row for each customer that does the following:

· Display the email address from the Customers table, a count of the number of orders for each customer, and the total amount for each order (HINT:  Don’t forget about the discounts when calculating the total amount for the order!)

· Only show results for customers that have more than 1 order.

· Sort the results in descending order by the sum of the line item amounts.

7. Write an SQL script that does the following and save this script as CIS276DA_FinalExercise7_ JAM2334209.sql . Save the output of this script as CIS276DA_FinalExercise7_ JAM2334209.csv

· Use an aggregate window function to display the order id from the Order_Items table along with the total amount for each order 
item in the Order_Items table. (HINT:  Don’t forget the discount!)

· Also display the total amount for each order

· Use an aggregate window function to get the average item amount for each order and display that as a column in the results.

· Use a named window for the two aggregate functions.

· Sort the results in ascending order by the order_id column

8. Write an SQL script that does the following and save this script as CIS276DA_FinalExercise8_ JAM2334209.sql . Save the output of this script as CIS276DA_FinalExercise8_ JAM2334209.csv

· Use a correlated subquery to return one row per customer, representing the customer’s oldest order (the one with the earliest date). Each row should include these three columns:  email_address, order_id, and order_date.

· Sort the results by the order date and order id columns.

9. Write an SQL script that does the following and save this script as CIS276DA_FinalExercise9_ JAM2334209.sql . Save the output of this script as CIS276DA_FinalExercise9_ JAM2334209.csv

· Write a SELECT statement that uses the analytic functions to get the highest and
lowest sales by product within each category. Return these columns:

· The category_name column from the Categories table

· The product_name column from the Products table

· A column named total_sales that shows the sum of the sales for each
product with sales in the Order_Items table

· A column named highest_sales that uses the FIRST_VALUE function to
show the name of the product with the highest sales within each category

· A column named lowest_sales that uses the LAST_VALUE function to
show the name of the product with the lowest sales within each category

10. Write an SQL script that does the following and save this script as CIS276DA_FinalExercise10_ JAM2334209.sql . Save the output of this script as CIS276DA_FinalExercise10_ JAM2334209.csv

· add three new columns to the Products table

· Add one column for the name of the product supplier 

· Add one column for the product markup percentage that provides for three digits to the left of the decimal point and two to the right.  This column should have a default value of 50.00

· Add one column for the date and time that the product was added to the database

· Display the new structure of the table and save that to the csv file.

11. Write an SQL script that does the following and save this script as CIS276DA_FinalExercise11_ JAM2334209.sql . Save the output of this script as CIS276DA_FinalExercise11_ JAM2334209.csv

· Create a view named order_item_products that returns columns from the Orders,
Order_Items, and Products tables.

· This view should return these columns from the Orders table: order_id, order_date,
tax_amount, and ship_date.

· This view should return the product_name column from the Products table.

· This view should return these columns from the Order_Items table: item_price,
discount_amount, final_price (the discount amount subtracted from the item price),
quantity, and item_total (the calculated total for the item).

12. Write an SQL script that does the following and save this script as CIS276DA_FinalExercise12_ JAM2334209.sql . Save the output of this script as CIS276DA_FinalExercise12_ JAM2334209.csv

· Write and execute a script that creates a user with a username and password of yourchoosing. This user should be able to connect to MySQL from any computer. This user should have SELECT, INSERT, UPDATE, and DELETE privileges for the Customers, Addresses, Orders, and Order_Items tables of the My Guitar Shop database. However, this user should only have SELECT privileges for the Products and Categories tables. Also, this user should not have the right to grant privileges to other users.

· Check the privileges for the user by using the SHOW GRANTS statement

Project Submission

1. Place all your script files (12) and CSV files (12) in a folder labeled “Final Project”.

2. Replace “MEID” in the file names with your actual MEID.

3. Zip the entire “Final Project” folder as described in the Syllabus.

4. Submit the zipped assignment folder using the link below.