Codingassignmenthelper

Assignment :

NOTE : Due only 8 questions, watch out for dependencies! USE guitar;

Question 1

Create a view named customer_addresses that shows the shipping and billing addresses for each customer.

This view should return these columns from the Customers table: customer_id,email_address, last_name and first_name.

This view should return these columns from the Addresses table: bill_line1, bill_line2, bill_city, bill_state, bill_zip, ship_line1, ship_line2, ship_city, ship_state, and ship_zip.

Now write a SELECT statement that returns these columns from the customer_addresses view: customer_id, last_name, first_name, bill_line1.

The rows in the result should be sorted by the last_name and then first_name columns.

Your code for the view should go after this comment! Your code for using the view should go after this statement! NOTE: The rows in the result should be sorted by the last_name and then first_name columns.

Question 2

Write a script that creates and calls a stored procedure named "question_2". This stored procedure should use two variables to store (1) the count of all of the products in the Products table and (2) the average list price for those products. If the product count is greater than or equal to 7, the stored procedure should display a result set that displays the values of both variables. Otherwise, the procedure should display a result set that displays a message that says, “The number of products is less than 7”.

Your code for the script / query should go after this comment! Test question_2 procedure
CALL question_2();

Question 3

Write a script that creates and calls a stored procedure named "question_3". This procedure should include two SQL statements coded as a transaction to delete the row with a customer ID of 8 from the "Customers" table. To do this, you must first delete all addresses for that customer from the "Addresses" table.

If these statements execute successfully, "commit" the changes. Otherwise, "roll back" the changes.

Your code for the script / query should go after this comment! Test question_3 procedure
CALL question_3();

Question 4

Write a script that creates and calls a stored function named "discount_price" that calculates the discount price of an item in the "Order_Items" table (discount amount subtracted from item price). To do that, this function should accept one parameter for the item ID, and it should return the value of the discount price for that item.

Your code for the function "discount_price" should go after this comment! Your code for a SELECT query using the "discount_price" should go after this comment!

Question 5

Create a table named "Products_Audit". This table should have all columns of the "Products" table, except the "description" column. Also, it should have an "audit_id" column for its primary key, and the "date_added" column should be changed to "date_updated".

Create a trigger named "products_after_update". This trigger should insert the old data about the product into the "Products_Audit" table after the row is updated. Then, test this trigger with an appropriate UPDATE statement.

Your code for creation of the "Products_Audit" should go after this comment! Your code for the trigger "products_after_update" should go after this comment! Your code to update the products table and have the trigger executed! Your UPDATE SQL code should change the discount percentage to 25 percentage on record "1" of the listing for Question 5. The code to display the record in the "Product_Audit" table

            
              SELECT product_id, product_name, discout_percent

              FROM Products_Audit

              WHERE product_id = 1;

Question 6

Use a SET statement to temporarily disable the general log. Then, to make sure this variable was set, use a SELECT statement to view the variable. If you get an error indicating that access is denied, you may need to stop Workbench and run it as an administrator

Your code for setting the "general log" should go after this comment! Your code for the SELECT statement to display the "general log" should go after this comment!

Question 7

Write and execute a script that creates a user with a username "housatonic" and password as "password" 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 Guitar 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.

Your code to add your user and privileges for the database tables should go after this comment! Place your code to show privileges for the user "housatonic" after this comment

Question 8

Write and execute a script that creates a role named "clerk". The role should have the SELECT privilege for the "Customers", "Addresses", "Orders", and "Line_Items" tables of the Guitar database.
Assign the "user" created in Question 7 to the new role.
Set the default role for the user to "clerk".
Check the privileges for the role by using the SHOW GRANTS statement.

Your code to create the role should go after this comment! Your code to assign "housatonic" to "clerk" role should go after this comment! Set the default role for the user to "clerk". Show privileges for "clerk"

Question 9

Show the SQL code you would enter at the command prompt using the "mysqldump" program to create a full backup of the Guitar database.

Your example code for the command prompt should go after this comment!
To Download Click Here > SQL Questions.pdf