Unit 7 Assignment_ Modifying Database Data
docx
keyboard_arrow_up
School
Purdue Global University *
*We aren’t endorsed by this school
Course
234
Subject
Information Systems
Date
Dec 6, 2023
Type
docx
Pages
5
Uploaded by Savannah_98
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
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
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.
Also review the university policy on plagiarism. If you have any questions, please contact
your professor.
The assignment is due by the final day of the Unit 7 week.
Directions for Submitting Your Assignment
Name your assignment document according to this convention:
IT234_<YourName>_Unit7.docx. (replace <YourName> with your full name). Submit your
completed assignment to the Unit 7 Assignment Dropbox by the final day of the Unit 7
week.
Review the Unit 7 Assignment Rubric before beginning this activity.