MySQL Assignment Your Portfolio Project consists of two parts, a lesson learned reflection report and a database design and analysis project Part 1 Lessons Learned and Reflection Write a 2 to 3 page summary that outlines the lessons you learned in this database class Reflect on how these lessons can be applied toward more effective database management and reporting Part 2 Queries Using the My Guitar Shop database you installed in Module 1, develop the following queries SUBMIT A SCREENSHOT OF EACH STEP 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 thats calculated from the previous two columns discount price A column thats calculated from the previous three columns Round the discount amount and discount price columns to two 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 five rows Submit a screenshot 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 thats calculated by multiplying the item price by the quantity discount total A column thats calculated by multiplying the discount amount by the quantity item total A column thats 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 Submit a screenshot Write a SELECT statement that returns the product name and list price columns from the Products table Return one row for each product that has the same list price as another product Hint Use a self join to check that the product id columns arent equal but the list price columns are equal Sort the result set by the product name column Submit a screenshot Write a SELECT statement that returns these two columns category name The category name column from the Categories table product id The product id column from the Products table Return one row for each category that has never been used Hint Use an outer join and only return rows where the product id column contains a null value Submit a screenshot Write an INSERT statement that adds this row to the Customers table email address rick raven com password (empty string) first name Rick last name Raven Use a column list for this statement Submit a screenshot Write a SELECT statement that answers this question Which customers have ordered more than one product Return these columns The email address column from the Customers table The count of distinct products from the customers orders Sort the result set in ascending sequence by the email address column Submit a screenshot Write a SELECT statement that answers this question What is the total quantity purchased for each product within each category Return these columns The category name column from the category table The product name column from the products table The total quantity purchased for each product with orders in the Order Items table Use the WITH ROLLUP operator to include rows that give a summary for each category name as well as a row that gives the grand total Use the IF and GROUPING functions to replace null values in the category name and product name columns with literal values if theyre for summary rows Submit a screenshot Write and execute a script that creates a user with a username using your firstname initial and lastname and password of your choosing 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

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 07, 2024

MySQL Assignment Your Portfolio Project consists of two parts, a lesson learned reflection report and a database design and analysis project. Part 1: Lessons Learned

MySQL Assignment

Your Portfolio Project consists of two parts, a lesson learned reflection report and a database design and analysis project.

Part 1: Lessons Learned and Reflection

Write a 2- to 3-page summary that outlines the lessons you learned in this database class. Reflect on how these lessons can be applied toward more effective database management and reporting.

Part 2: Queries

Using the My Guitar Shop database you installed in Module 1, develop the following queries. SUBMIT A SCREENSHOT OF EACH STEP.

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 thats calculated from the previous two columns

discount_price A column thats calculated from the previous three columns

Round the discount_amount and discount_price columns to two 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 five rows. Submit a screenshot.
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 thats calculated by multiplying the item price by the quantity

discount_total A column thats calculated by multiplying the discount amount by the quantity

item_total A column thats 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. Submit a screenshot.
Write a SELECT statement that returns the product_name and list_price columns from the Products table.
Return one row for each product that has the same list price as another product.

Hint: Use a self-join to check that the product_id columns arent equal but the list_price columns are equal.

Sort the result set by the product_name column. Submit a screenshot.
Write a SELECT statement that returns these two columns:
category_name The category_name column from the Categories table

product_id The product_id column from the Products table

Return one row for each category that has never been used. Hint: Use an outer join and only return rows where the product_id column contains a null value. Submit a screenshot.
Write an INSERT statement that adds this row to the Customers table:
email_address: rick@raven.com

password: (empty string)

first_name: Rick

last_name: Raven

Use a column list for this statement. Submit a screenshot.
Write a SELECT statement that answers this question: Which customers have ordered more than one product? Return these columns:
The email_address column from the Customers table

The count of distinct products from the customers orders

Sort the result set in ascending sequence by the email_address column. Submit a screenshot.
Write a SELECT statement that answers this question: What is the total quantity purchased for each product within each category? Return these columns
The category_name column from the category table

The product_name column from the products table

The total quantity purchased for each product with orders in the Order_Items table

Use the WITH ROLLUP operator to include rows that give a summary for each category name as well as a row that gives the grand total.

Use the IF and GROUPING functions to replace null values in the category_name and product_name columns with literal values if theyre for summary rows. Submit a screenshot.
Write and execute a script that creates a user with a username using your firstname initial and lastname and password of your choosing. 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