Overview

Introduction

The Modify Data module provides you with the instruction and server hardware to develop your hands on skills in the defined topics. This module includes the following exercises:

  1. Modifying Data using INSERT, UPDATE, and DELETE Statements
  2. Performing Update Operations on Tables using T-SQL Statements
  3. Performing Delete Operations on Tables using T-SQL Statements

Exercise 1 – Modifying Data using INSERT, UPDATE, and DELETE Statements

In this exercise, you will learn the following in Microsoft SQL Server 2008 R2:

  • Insert data into tables using Transact SQL
  • Perform update operations on database table
  • Perform delete operations on database table

Exercise 2 – Performing Update Operations on Tables using T-SQL Statements

The Update statement is used to change data in a column or multiple columns in a table or a view. A simple Update statement will contain the Update, Set, and Where clauses. The Update clause specifies the tables that is to be updated. The Set clause specifies the columns to be updated. The Set clause also specifies the value to be updated. You can specify the value directly or use an expression to specify the value. You can also use the Default keyword to assign the default value to a column, if a default value has been specified for that column. You can also use the Null keyword to assign a Null value to a column. The Where clause specifies the condition that identifies the rows to be updated. If you do not specify the Where clause, all the rows in the table are updated.

You can also use joins in the From clause or Where clause of the Update statement. In the From clause, a join is used to specify column values or search conditions based on another table other than the table that is being updated. In the Set clause, a join can be used to retrieve values to be assigned to the columns. A join can be used in the Where clause to specify a search condition.

Exercise 3 – Performing Delete Operations on Tables using T-SQL Statements

The delete statement is used to delete one or two rows from a table or a view. To delete all the rows in a table, use the Delete statement followed by the name of the table. You can also use the From clause, which is optional, before the name of the table. If you want to delete specific rows from the table, you can use the Where clause to specify the condition that the rows should meet to be deleted. You can also use joins in the From or Where clause of the Delete statement. To perform the delete operation based on the data in some other table, use a subquery or join in the From clause. A subquery or join is used in the Where clause to specify the condition used to identify the rows to be deleted.

Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.