IT 234 Databases Concepts

Assignment Instructions

Before completing the Assignment, please watch the Unit 7 videos covering facets associated with data modification.

You will use the Northwind database for this assignment. Leverage the following database design diagram as a guide in forming your DML statements for the problems specified below:

• Northwind Database Design Diagram

Your assignment submittal needs to show both the generated SQL statements and confirmatory screenshots verifying task completion.

Problem 1: Use an SQL INSERT statement to populate the Categories table with a new category called “Fresh Produce” and a description of “Fresh Fruits and Vegetables.” There is no picture for this record insert. Remember, the CategoryID field auto populates so you do not include this in your insert statement. After you have added the category, select all records in the Categories table to see if the record was added.

Expected Output

Problem 2: Use the SQL INSERT command to add a new region to handle customers in the Midwest. In this case, the RegionID field does not auto populate so you should first find how many regions are in the table before adding the new region and RegionID. Use a query after completing this to verify that the new region has been added.

Expected Output

Problem 3: Northwind has decided to change the employee title that is now called “Sales Representative” to “Sales Specialist.” Use the SQL UPDATE statement to reflect the change in the Employees table. After you have updated the Employees table, select all records in the Employees table to see if the update was performed correctly.

Expected Output

Problem 4: The Sales department has realigned and before they implement the new “Midwest” region you added, they noted that it really should be called the “Southeast” region. Change the “Midwest” region name to “Southeast” and show the regions again to confirm this change.

Expected Output

Problem 5: An order needs to be deleted. The order number is 11071. However, before you can delete the order, you must delete the order details. Delete all records in the OrderDetails table that references order number 11071. Then execute a query against the OrderDetails table to show that the records associated with order number 11071 have been removed.

Expected Output

Problem 6: Delete order number 11071 from the Orders table. Then execute a query against the Orders table to show that the record was removed.

Expected Output

Problem 7: An employee named Margaret Peacock just received a 5% raise from Northwind. Update Ms. Peacock’s employee record to reflect this change. In order to do this, her current salary needs to be multiplied by 1.05 in the update statement.

Expected Output

Problem 8: A new supplier needs to be added to the Northwind database. Generate an SQL statement to insert the following details:

Company Name: Acme Enterprises

Contact Name: Wylie Coyote

Contact Title: Executive Manager

Address: 123 Main Street

City: Las Vegas

Region: NV

Postal Code: 89108

Country: USA

Phone: (702) 555-9876

Note: You do not need to provide a SupplierID for this record.

Expected Output

Problem 9: A customer just purchased 40 units of a product called “Boston Crab Meat” from Northwind. Reduce the current UnitsInStock value in the Products table by 40 for this product.

Expected Output

Problem 10: A final call has come in from the Sales Department. After presenting the new regional alignment to the board, the board has rejected this part of the reorganization plan. Please DELETE that new region that was added earlier in Problem 2 and then changed in Problem 4 of this assignment.

Expected Output

Assignment Requirements

Microsoft SQL Server Express and SQL Server Management Studio (SSMS) MUST be installed to complete this Assignment.

Compose your Assignment in a Word document and be sure to identify yourself, your class, and unit Assignment at the top of your paper. Embed the screenshots of your SQL statements and confirmatory output (e.g., table structure definitions) into the Word document.