mysql project and need the explanation and answer to help me learn.
Requirements: as per instruction
Note: Review the output files provided (XXA03*.out). Display the question headers in your output files.
Topic: Data Manipulation Language (Insert, Update, Delete)
Important: After each DML statement, you must show the number of rows affected (i.e., call ROW_COUNT() immediately after the INSERT/UPDATE/DELETE).
Tip: Before you run your A3 solution, run all the create database SQL scripts to get consistent results.
Task 1. My Guitar Shop (MGS) Database
Save your solution to XXA03Task1.sql. Redirect your output to XXA03Task1.out.
Assumption: You have MGS database created from mysql\mgs_ex_starts\create_my_guitar_shop.sql (part of Lab 2).
Q1. MGS Exercise 1
Write an INSERT statement that adds this row to the Categories table:
category_name: Wind
Code the INSERT statement so MySQL automatically generates the category_id column.
Write an UPDATE statement that modifies the row you just added to the Categories table. This statement should change the category_name column to “String”, and it should use the category_id column to identify the row.
Write a DELETE statement that deletes the row you just added to the Categories table. This statement should use the category_id column to identify the row.
Q2. MGS Exercise 2
Write an INSERT statement that adds this row to the Products table:
product_id: The next automatically generated ID
category_id: 4
product_code: dgx_640
product_name: Yamaha DGX 640 Digital Piano
description: This is the Yamaha DGX 640 Digital Piano with 88 keys.
list_price: $845.95
discount_percent: 10%
date_added: Today’s date and time.
Use a column list for this statement.
Write an UPDATE statement that modifies the product you just added. This statement should change the discount_percent column from 10% to 30%.
Q3. MGS Exercise 3
Write an INSERT statement that adds this row to the Customers table:
email_address: yourname@conestogac.on.ca (Use your real name)
password: (empty string)
first_name: your_first_name
last_name: your_last_name Use a column list for this statement.
Write an UPDATE statement that modifies the Customers table. Change the password column to “s3cr3t” for the customer with an email address of yourname@conestogac.on.ca. This will update 1 row.
Task 2. Software Expert (SWE) Database
Save your solution to XXA03Task2.sql. Redirect your output to XXA03Task2.out.
Assumption: You have SWE database created from swexpert.sql (assignment 2).
Q1. SWE Exercise 1
You were hired as a new consultant. Add a new record as follows:
c_id: 106
c_last: (Your last name)
c_first: (Your first name)
c_mi: (Your middle initial)
c_add: (Your fake address)
c_city: (Your fake city)
c_state: (Your fake province code)
c_zip: (Your fake postal code)
c_phone: (Your fake phone)
c_email: (Your fake email)
All column values must not be null.
Q2. SWE Exercise 2
The company got a new client. Add a new record as follows:
client_id: 17
client_name: City of Waterloo
contact_last: Jaworsky
contact_first: Dave
contact_phone: 519 886 1550
Q3. SWE Exercise 3
As a new consultant, you were assigned as the manager of a new project for the client Dave Jaworsky. Add a new project named ‘ION Rapid Transit’ with a project ID of 88. The parent project of ‘ION Rapid Transit’ project is unassigned.
Q4. SWE Exercise 4
Write an UPDATE statement that modifies the parent project of Project table. Assign all projects without a parent project to the newly added project. The parent project of ‘ION Rapid Transit’ project must remain unassigned.
Assignment Submissions
Zip XXA03Task1.sql, XXA03Task1.out, XXA03Task2.sql, and XXA03Task2.out into a single zip file. (where XX is your initials in upper case letters)