MySQL

/* Query 1. (10 points) Write an INSERT statement with an OUTPUT clause to add a new row to the Categories table in MyGuitarShop database. The OUTPUT clause must display the new inserted values of both columns of Categories. The CategoryName of this new row is 'Brass'. Your INSERT statement should not insert a value into the CategoryID column because this column is an IDENTITY column, meaning its values are generated automatically by the server. Hint: see lecture video about how to add an OUTPUT clause to meet the above requirement. */

/* Query 2. (10 points) Once Query 1 is done, write an UPDATE statement with an OUTPUT clause to modify rows of Categories table. This statement should change the CategoryName value by appending "***" to its end if the CategoryName value begins with a letter 'B'. The OUTPUT clause must display three columns: CategoryID column, 'Old_CategoryName' column for the CategoryName value before update, and 'New_CategoryName' column for the CategoryName value after update. */

/* Query 3. (30 points) Once Query 2 is done, write a DELETE statement to delete rows of Categories table if their CategoryName value ends with '***'.

When you execute this statement, it will produce an error since these categories to be deleted have related rows in the Products table. You may open or create a database diagram to easily see how these tables are related. To fix that, precede the DELETE statement with another DELETE statement that deletes all products in these categories. Hint: use a subquery in the WHERE clause of the DELETE statement.

Again, when you execute this new statement to delete products that are related to the above categories, you get another error since these products have related rows in OrderItems table. Using the same approach, precede the above DELETE statement with another new DELETE statement that deletes all order items related to the products that belong to categories whose CategoryName value ends with '***'. Hint: this DELETE statement requires a subquery nested in another subquery.

Include an OUTPUT clause in all three DELETE statements, each must display all columns of the rows that are deleted.

Correct answer of this query should contain three DELETE statements in the following order. The first one contains two levels of subqueries to delete rows from OrderItems table. The second one contains only one level of subquery to delete rows from Products table. And, the third DELETE statement deletes rows of Categories table with no subquery. */

Object Explorer ConnectY& EE dboAddresses Columns o AddresslD (PK, int, not null) o CustomerlD (FK, int, null) Line! (varchar(60), not null) E Line2 (varchar(60), null) City (varchar(40), not null) State (varchar(2), not null) ZipCode (varchar(10), not null) Phone (varchar(12), not null) Disabled (int, not null) Keys Constraints Triggers Indexes " Statistics E dboAdministrators Columns o AdminlD (PK, int, not null) E EmailAddress (varchar(255), not null) Password (varchar(255), not null) | FirstName (varchar(255), not null) E LastName (varchar(255), not null) Keys Constraints Triggers Indexes " Statistics E dboCategories Columns O CategorylD (PK, int, not null) CategoryName (varchar(255), not null) Keys Constraints Triggers Indexes Statistics dboCustomers Columns CustomerlD (PK, int, not null) EmailAddress (varchar(255), not null) Password (varchar(60), not null) FirstName (varchar(60), not null) | E LastName (varchar(60), not null) ShippingAddressID (int, null) BillingAddressID (int, null) Object Explorer ConnectY& EE dboAddresses Columns o AddresslD (PK, int, not null) o CustomerlD (FK, int, null) Line! (varchar(60), not null) E Line2 (varchar(60), null) City (varchar(40), not null) State (varchar(2), not null) ZipCode (varchar(10), not null) Phone (varchar(12), not null) Disabled (int, not null) Keys Constraints Triggers Indexes " Statistics E dboAdministrators Columns o AdminlD (PK, int, not null) E EmailAddress (varchar(255), not null) Password (varchar(255), not null) | FirstName (varchar(255), not null) E LastName (varchar(255), not null) Keys Constraints Triggers Indexes " Statistics E dboCategories Columns O CategorylD (PK, int, not null) CategoryName (varchar(255), not null) Keys Constraints Triggers Indexes Statistics dboCustomers Columns CustomerlD (PK, int, not null) EmailAddress (varchar(255), not null) Password (varchar(60), not null) FirstName (varchar(60), not null) | E LastName (varchar(60), not null) ShippingAddressID (int, null) BillingAddressID (int, null)