SQL Lab Assignment 6: Answer the questions below in SQL queries.

The purpose of this assignment is to demonstrate that you can execute statements that are used to add, change, and delete data in relational database tables. These problems require knowledge of the INSERT, UPDATE, and DELETE statements. SQL Database and Column names listed below for accuracy.

Answer the questions below 1-10 in SQL queries.You can write and run an appropriate SELECT statement.

SQL Database and Column names listed below for accuracy.

1. Write an INSERT statement that adds this row to the Categories table: CategoryName: Brass Code the INSERT statement so SQL Server automatically generates the value for the CategoryID column.

2. Write an UPDATE statement that modifies the row you just added to the Categories table. This statement should change the CategoryName column to Woodwinds, and it should use the CategoryID column to identify the row.

3. Write a DELETE statement that deletes the row you added to the Categories table in exercise 1. This statement should use the CategoryID column to identify the row.

4. Write an INSERT statement that adds this row to the Products table: ProductID: The next automatically generated ID CategoryID: 4 ProductCode: dgx_640 ProductName: Yamaha DGX 640 88-Key Digital Piano Description: Long description to come. ListPrice: 799.99 DiscountPercent: 0 (this is the default) DateAdded: Todays date/time. Use a column list for this statement.

5. Write an UPDATE statement that modifies the product you added in exercise 4. This statement should change the DiscountPercent column from 0% to 35%.

6. Write a DELETE statement that deletes the row in the Categories table that has an ID of 4. When you execute this statement, it will produce an error since the category has related rows in the Products table. To fix that, precede the DELETE statement with another DELETE statement that deletes all products in this category.

7. Write an INSERT statement that adds this row to the Customers table: EmailAddress: rick@raven.com Password: (empty string) FirstName: Rick LastName: Raven Use a column list for this statement.

8. Write an UPDATE statement that modifies the Customers table. Change the password column to secret for the customer with an email address of rick@raven.com.

9. Write an UPDATE statement that modifies the Customers table. Change the password column to reset for every customer in the table.

10. Open the script named CreateMyGuitarShop.sql thats in the MGS Exercise Starts directory. Then, run this script. That should restore the data thats in the database. If an error message is displayed indicating that the database is in use, youll need to close and restart the Management Studio and then run the script again.

dbo.Addresses Columns mo AddressID (PK, int, not null) Qu CustomerlD (FK, int, null) Line1 (varchar(60), not null) Line2 (varchar (60), null) City (varchar(40), not null) State (varchar(2), not null) ZipCode (varchar(10), not n Phone (varchar(12), not null Disabled (int, not null) Keys Constraints Triggers Indexes Statistics dbo.Administrators Columns \%o AdminID (PK, int, not null) EmailAddress (varchar(255), Password (varchar(255), not FirstName (varchar(255), no LastName (varchar(255), no Keys Constraints Triggers Indexes Statistics dbo.Categories Columns mo CategorylD (PK, int, not null CategoryName (varchar(25: Keys Constraints Triggers Indexes Statistics dbo.Customers Columns o CustomerID (PK, int, not nu EmailAddress (varchar(255), Password (varchar (60), not FirstName (varchar (60), not LastName (varchar(60), not ShippingAddressID (int, null BillingAddressID (int, null) dbo.Orderltems Columns o ItemID (PK, int, not null) Q OrderlD (FK, int, null) ProductID (FK, int, null) ItemPrice (money, not null) DiscountAmount (money, r Quantity (int, not null) Keys Constraints Triggers Indexes Statistics dbo.Orders Columns mo OrderID (PK, int, not null) Q C CustomerlD (FK, int, null) OrderDate (datetime, not nu ShipAmount (money, not n TaxAmount (money, not nu ShipDate (datetime, null) ShipAddressID (int, not null] CardType (varchar(50), not i Card CardType (varchar(50), not Carconpmes (crantrinnorma BillingAddressID (int, not nu Keys Constraints Triggers Indexes Statistics dbo.Products Columns o ProductID (PK, int, not null) Q CategorylD (FK, int, null) ProductCode (varchar(10), r ProductName (varchar(255) Description (text, not null) ListPrice (money, not null) DiscountPercent (money, n DateAdded (datetime, null) Keys Constraints Triggers Indexes Statistics