Overview

Introduction

The Implementing Sub Queries 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:

  • Creating Subqueries
  • Interpreting the Query Execution Plan
  • Use Common Table Expression

Lab time: It will take approximately 1 hour to complete this lab.

Exercise 1 - Creating Subqueries

In this exercise, you will learn the following using SQL Server 2012:

  • Creating subqueries
  • Interpreting the query execution plan
  • Working with Pivot and Unpivot data
  • Using the Apply operator
  • Using the Common Table Expression (CTE)

Exercise 2 - Interpreting the Query Execution Plan

When we write a query, we specify what we want. However, we do not know how the server is going to execute the query to give us what we want. This is ok as long as we write simple queries. But, when we write complex queries, there is a huge possibility that it may affect the efficiency of the database engine and that may, in turn, lead to slowing down the performance of the entire application. As we write large number of code and queries for applications, it might be difficult to avoid these inefficient queries. However, we can optimize the queries to a large extent if we understand how the database engine is going to execute the query. This is what a query execution plan does - it provides information on how the database engine plans to execute query. It gives you information about the total cost involved in executing each part of the query. Looking at this information, you will be able to decide which part of the query needs to be optimized for the query to run faster.

Exercise 3 - Use Common Table Expression

A common table expression is a temporary named result that can be referenced in SELECT, INSERT, UPDATE, or DELETE statement. Additionally, you can reference a CTE in the SELECT query part of a CREATE VIEW statement. When a CTE expression includes references to itself, it is termed as a recursive common table expression. You need to define a CTE by using a WITH clause. In this example, you will perform two tasks using CTE.

Comprehensive Learning

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