IT2351-Assignment 3 Solved

1.            Additional practice editing and loading a database script: edit the Guitar database script (located in the assignment link) to include your name.  For example,

DROP DATABASE IF EXISTS YourName_guitar_shop;

CREATE DATABASE YourName _guitar_shop;

USE YourName _guitar_shop;

 

Once edited, open the file in MySQL Workbench, run the script and refresh your schema window.  Take a screenshot of your new database and name the file Assignment3No1.png. Upload to your assignment folder in GitHub.  Here is an example:  

 

  

2.            Write a SELECT statement that returns these columns:   

  

a.      The count of the number of orders in the orders table  

b.      The sum of the “Ship Value” which is the sum of the ship_amount from the orders table.

c.      Submit a screenshot of your result (code and output) named Assignment2No2.  Also submit your script also named Assignment2No2.  

  

3.            Write a SELECT statement that returns one row for each category that has products (inner join) with these columns:   

a)        The Category Name column from the Categories table   

b)        The count of the products in the Products table (join these two tables)

c)         The price of the most expensive product in the Products table   

d)        Sort the result set so the category with the most products appears first.   

e)        Submit as in the previous task and for all remaining tasks until the discussions at the end.  

  

4.            Write a SELECT statement that answers this question: What is the total quantity purchased for each product within each category? Return these columns:   

a)        The CategoryName column from the category table   

b)        The ProductName column from the products table   

c)         The total quantity purchased for each product with orders in the OrderDetails table

d)        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 they’re for summary rows.

 

 

5.           Write a SELECT statement that returns the same result set as this SELECT statement, but don’t use a join. Instead, use a subquery in a WHERE clause that uses the IN keyword. 

SELECT DISTINCT category_name 

FROM categories c JOIN products p 

  ON c.category_id = p.category_id 

ORDER BY category_name 

 

6.            Write a SELECT statement that returns three columns: email_address, order_id, and the order total for each customer. To do this, you can group the result set by the email_address and order_id columns. In addition, you must calculate the order total from the columns in the Order_Items table.  

 

7.           In MySQL Workbench, select the “Administration” tab on the lower left and then select “Status and System Variables” item from the Navigator window.  Next, select “Commands/DDL” (under the Status Variables tab) to view the server variables.    

a.      Explain the following in the text submission box: What variable(s) currently have a value and why?  

b.      Next, select “Commands/General” and explain the values greater than zero that you see.  You may need to do a bit of additional research.

8.           View the system variables named general_log and general_log_file. Make a note of the values of these system variables and explain what they mean in the text submission box.

9.           (What is the difference between status and system variables? This discussion is flexible.  You can compare variables, give some examples etc. Here is an additional resource that my be helpful: https://dev.mysql.com/doc/workbench/en/wb-mysql-connections-navigator-management-statusvariables.html.  

 

10.        Please number your discussions (7, 8, 9) in the assignment link text submission box for clarity and, let me know if you have questions.