How to insert, update, and delete data

To test whether a table has been modified correctly as you do these exercises, you can write and run an appropriate SELECT statement.

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.

1. Write an UPDATE statement that modifies the row you just added to the Categories table. This statement should change the ProductName column to “Woodwinds”, and it should use the CategoryID column to identify the row.
2. 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.
3. 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
DateAdded: Today’s date/time.

Use a column list for this statement.

1. Write an UPDATE statement that modifies the product you added in exercise 4. This statement should change the DiscountPercent column from 0% to 35%.
2. 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.
3. Write an INSERT statement that adds this row to the Customers table:

EmailAddress: r..k@raven.com
Password: (empty string)
FirstName: Rick
LastName: Raven

Use a column list for this statement.

1. Write an UPDATE statement that modifies the Customers table. Change the password column to “secret” for the customer with an email address of r..k@raven.com.
2. Write an UPDATE statement that modifies the Customers table. Change the password column to “reset” for every customer in the table.
3. <<You don’t need to show this in your homework, but you should do it.>> Open the script named CreateMyGuitarShop.sql that’s in the Exercise Starts directory. Then, run this script. That should restore the data that’s in the database.
4. Chapter 7 qattachments_395fdc0aa35d173aabd7152e53e67341b46fa76a.docx Murach’s SQL Server 2012 How to insert, update, and delete data
   To test whether a table has been modified correctly as you do these exercises, you can write and run an
   appropriate SELECT statement.
   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.
   1. Write an UPDATE statement that modifies the row you just added to the Categories table. This statement
   should change the ProductName column to “Woodwinds”, and it should use the CategoryID column to
   identify the row.
   2. 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.
   3. Write an INSERT statement that adds this row to the Products table:
   ProductID:
   CategoryID:
   ProductCode:
   ProductName:
   Description:
   ListPrice:
   DiscountPercent:
   DateAdded: The next automatically generated ID
   4
   dgx_640
   Yamaha DGX 640 88-Key Digital Piano
   Long description to come.
   799.99
   0
   Today’s date/time. Use a column list for this statement.
   4. Write an UPDATE statement that modifies the product you added in exercise 4. This statement should change
   the DiscountPercent column from 0% to 35%.
   5. 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.
   6. Write an INSERT statement that adds this row to the Customers table:
   EmailAddress:
   Password:
   FirstName:
   LastName: rick@raven.com
   (empty string)
   Rick
   Raven Use a column list for this statement.
   7. 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.
   8. Write an UPDATE statement that modifies the Customers table. Change the password column to “reset” for
   every customer in the table.
   9. &lt;&lt;You don’t need to show this in your homework, but you should do it.&gt;&gt; Open the script named
   CreateMyGuitarShop.sql that’s in the Exercise Starts directory. Then, run this script. That should restore the
   data that’s in the database.

• 1495423680-Exercise starts.zip
• 1495423681-My Guitar Shop.HW7.docx

ComputerScienceExpert

(11)

Status NEW Posted 22 May 2017 03:05 AM My Price 11.00

-----------

Attachments

file 1495423687-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer