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:

Learning Outcomes

In this module, you will complete the following exercises:

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

After completing this lab, you will be able to:

  • Manipulate data using INSERT statements
  • Perform update operation on a database table
  • Perform delete operation on a database table

Exam Objectives

The following exam objective is covered in this lab:

  • 3.1 Insert data
  • 3.2 Update data
  • 3.3 Delete data

Lab Duration

It will take approximately 20 minutes to complete this lab.

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

The Insert statement is used to insert data into a table or a view. To insert data into a table, you need to specify the table into which the data is to be inserted and the values that are to be inserted. You can insert data into all the columns in the table or to specific columns. When you insert data into specific columns, the Database Engine inserts a NULL value or the default value, if specified, in the columns for which you have not specified any values.

Learning Outcomes

After completing this lab, you will be able to:

  • Manipulate data using INSERT statements

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.

Learning Outcomes

After completing this lab, you will be able to:

  • Perform update operation on a database table

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.

Learning Outcomes

After completing this lab, you will be able to:

  • Perform delete operation on a database table

Comprehensive Learning

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