It
Unit 7 Assignment: Modifying Database Data
Outcomes addressed in this activity:
Unit Outcomes:
· Use DML commands to modify an existing table.
Course Outcome:
IT234-3: Explore Data Manipulation Language (DML) statements to manage data within schema objects.
Purpose
In business, things change, and data in the organization’s database must be modified to reflect those changes. In this week’s Assignment, you will insert new data into the Northwind database, and you will then update a table in the database.
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.
image5
image6
image7
image8
image9
image10
image1
image2
image3
image4
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
