Overview

Introduction

The Working with Subqueries module provides you with the instructions and devices to develop your hands-on skills in the following topics.

  • Using subqueries
  • Working with correlated subqueries
  • Working with self-contained subqueries
  • Managing nested subqueries

Lab time: It will take approximately 60 minutes to complete this lab.

Exam Objectives

The following exam objectives are covered in this lab:

  • Write SELECT statements
  • using a subquery with Aliases
  • using a subquery with comparison operators
  • using a subquery with EXISTS keyword
  • using a subquery with NOT EXISTS keyword
  • using a subquery in place of an expression
  • Use subqueries in UPDATE, DELETE, and INSERT statements
  • Write correlated subqueries that return scalar results
  • Write correlated subqueries that return multi-valued results
  • Write a self-contained subquery that returns a scalar value
  • Write nested subqueries
  • Demonstrate the differences between joins and correlated subqueries using the query plan

Exercise 1 - Using Subqueries

A subquery is a query that exists within another query. The main query that contains the subquery is known as the outer query or outer select. The subquery is also known as the inner query or inner select. Usually, the results of the subquery act as the inputs for the WHERE clause in the outer query.

In this exercise, you will learn to use subqueries in Transact-SQL statements.

Exercise 2 - Working with Correlated Subqueries

A correlated subquery depends on the outer query for its inputs. In other words, the outer query must provide values to the correlated subquery for execution. The correlated subquery is also known as a repeating subquery. The correlated subquery executes repeatedly for every single row selected by the outer query.

In this exercise, you will learn to work with correlated subqueries.

Exercise 3 - Working with Self-Contained Subqueries

The self-contained subqueries are independent queries. These queries do not require inputs from the outer query for execution. The self-contained subqueries are executed only once and the results are used by the outer query for execution.

In this exercise, you will learn to work with self-contained subqueries.

Exercise 4 - Managing Nested Subqueries

A nested subquery is a subquery that exists as an inner query and is a part of the outer query.

In this exercise, you will learn to manage nested subqueries.

Comprehensive Learning

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