Database Manipulation Language Statements Use the Unit 5 Assignment Template, linked in the Resources, to document and submit your assignment. Your completed assignment will be graded using the...

Database Manipulation Language Statements

Use the Unit 5 Assignment Template, linked in the Resources, to document and submit your assignment. Your completed assignment will be graded using the following criteria:

• Designed SQL transactions that meet the organization's needs.


• Designed SQL transactions that effectively uses INSERT statements.


• Designed SQL transactions that effectively uses UPDATE statements.


• Designed SQL transactions that effectively uses DELETE statements.


• Demonstrate an ability to use scripts in SQL statements and generate successful results.

Assignment Overview

For this assignment, you will develop a series of transactions using data manipulation language (DML) in SQL.

Now that the database has been populated, SmartHomes needs DML statements in order to insert, update, and delete content from their site. Although these statements need to be customized to allow input from the website, SmartHomes wants statements based on the dummy data in the database first to ensure that they work correctly. Some of them may require multiple statements in the transaction and need to consider the order in which the statements must occur. These statements will be shared with the application developers to use within their application.

In preparation for this assignment, you must understand the fundamentals of SQL data manipulation language. You should be familiar with INSERT, UPDATE, and DELETE statements to support the manipulation of the database. Use the suggested resource (linked in the Resources) or other resources you find to learn more about these topics.

• Sampson, A. (2017).Filter and modify data in SQL Server 2016[Tutorial]. Skillsoft Ireland.
  
  
  • Modifying Data Using DML (43 minutes).
  
  
  
  

Assignment Instructions

For this assignment, complete the following:

1. There are some situations in which a product requires some changes in terms of price and description. Create an UPDATE statement that will update a specific product's price and description (of your choice). Query the row to validate the changes.


2. A category has to be removed. DELETE the category and UPDATE all of the products in that category to an existing category.


3. A "fake" user has made a few orders. DELETE the user account along with all their orders.


4. Insert a new product with no category included. Then run an update statement to add it to a category.


5. There has been a price increase on some items. UPDATE all product prices that are greater than $10 to be $1 more.


6. The database server had been reset with the wrong date and time without anyone knowing. UPDATE any order date in the past week to add seven days.


7. Too many user accounts do not have any orders and should be removed. DELETE all customer records that do not have any orders.


8. UPDATE the product's description to include on the following string on those products that have not been ordered yet: "Special promotion: 25% off."


9. While the website was down, someone had taken in a manual order. Create the INSERT statements to add a customer and the three products that they have purchased.


10. A customer had called in with their name (no order or customerID) and had purchased the incorrect product. Using an UPDATE statement, update the OrderDetail table using their first and last name (consider it to be unique).

Be sure to provide screenshots of your transactions.

Refer to the Data Manipulation Language Statements scoring guide to ensure that you meet the grading criteria for this assignment.