Unit 4 Advanced Data Retrieval and Manipulation

SUBQUERIES
Nested SELECT Statements

 Definition: A Nested SELECT statement, also known as a subquery, involves embedding

one SELECT statement within another. This nesting allows for the retrieval of data in a
hierarchical manner, with the inner SELECT statement executed first, and its results used
as a condition or value in the outer SELECT statement.

Syntax of Nested SELECT Statements:

SELECT column1, column2, ...FROM table_name WHERE column_name OPERATOR (SELECT column_name
FROM another_table WHERE condition);

Example:
SELECT employeeid, name FROM employees WHERE departmentid = (SELECT departmentid FROM
departments WHERE deptname = 'Sales');

Correlated Subqueries

 Definition: SQL Correlated Subqueries are an advanced query technique used to handle
complex data retrieval tasks by displaying columns from an external query in a
subquery. Unlike conventional subqueries, correlated subqueries execute row by row,
making them dynamic and powerful for solving SQL problems.

 Example:
SELECT e1.employeeid, e1.salary
FROM employees e1
WHERE e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e1.departmentid =
e2.departmentid);

Common Table Expressions (CTEs)

What is CTE in SQL Server?

A CTE (Common Table Expression) is a one-time result set that only exists for the duration of
the query. It allows us to refer to data within a single SELECT, INSERT, UPDATE, DELETE, CREATE
VIEW, or MERGE statement's execution scope. It is temporary because its result cannot be
stored anywhere and will be lost as soon as a query's execution is completed. It first came
with SQL Server 2005 version. A DBA always preferred CTE to use as an alternative to a
Subquery/View. They follow the ANSI SQL 99 standard and are SQL-compliant.

Prep by: Akansha Srivastav Page 1

Unit 4 Advanced Data Retrieval and Manipulation

CTE Syntax in SQL Server

The CTE syntax includes a CTE name, an optional column list, and a statement/query that
defines the common table expression (CTE). After defining the CTE, we can use it as a view in a
SELECT, INSERT, UPDATE, DELETE, and MERGE query.

The following is the basic syntax of CTE in SQL Server:

WITH cte_name (column_names)

AS (query)
SELECT * FROM cte_name;

o We have first specified the CTE name that will be referred to later in a query.
o The next step is to create a list of comma-separated columns. It ensures that the
number of columns in the CTE definition arguments and the number of columns in the
query must be the same. If we have not defined the CTE arguments' columns, it will use
the query columns that define the CTE.
o After that, we'll use the AS keyword after the expression name and then define a SELECT
statement whose result set populates the CTE.
o Finally, we will use the CTE name in a query such as SELECT, INSERT, UPDATE, DELETE,
and MERGE statement.

It should keep in mind while writing the CTE query definition; we cannot use the following
clauses:

1. ORDER BY unless you also use as TOP clause

2. INTO
3. OPTION clause with query hints
4. FOR BROWSE

The below image is the representation of the CTE query definition.

Here, the first part is a CTE expression that contains a SQL query that can be run independently
in SQL. And the second part is the query that uses the CTE to display the result.

Prep by: Akansha Srivastav Page 2

 Unit 4 Advanced Data Retrieval and Manipulation

Example

Let us understand how CTE works in SQL Server using various examples. Here, we are going to
use a table "employee" for a demonstration.

In this example, the CTE name is employees_in_101, the subquery that defines the CTE returns
the three columns customer name, email, and state. As a result, it will return all employees
whose department is sales.

with ein101
as (select * from employees where departmentid=101)
select name from ein101;

After executing the above statement, it will give the following output.

Types of CTE in SQL Server

SQL Server divides the CTE (Common Table Expressions) into two broad categories:

1. Recursive CTE
2. Non-Recursive CTE

Recursive CTE

A common table expression is known as recursive CTE that references itself. Its concept is based
on recursion, which is defined as "the application of a recursive process or definition
repeatedly." When we execute a recursive query, it repeatedly iterates over a subset of the
data. It is simply defined as a query that calls itself. There is an end condition at some point, so
it does not call itself infinitely.

A recursive CTE must have a UNION ALL statement and a second query definition that
references the CTE itself in order to be recursive.

Example

WITH EmployeeHierarchy AS (
SELECT employee_id, manager_id, 1 AS level

Prep by: Akansha Srivastav Page 3

Unit 4 Advanced Data Retrieval and Manipulation

FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, eh.level + 1
FROM employees e
INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;

This SQL query uses a Common Table Expression (CTE) named EmployeeHierarchy to
recursively traverse and represent an organizational hierarchy of employees. The WITH clause
defines the recursive CTE to retrieve employees and their hierarchical levels starting from the
top-level manager (the root of the hierarchy).

Breakdown of the Query

1. Base Query:

SELECT employee_id, manager_id, 1 AS level FROM employees WHERE manager_id IS

NULL

 This identifies the top-level managers (employees without a manager).

 level is set to 1 for these employees, as they are at the top of the hierarchy.

2. Recursive Query:

UNION ALL SELECT e.employee_id, e.manager_id, eh.level + 1 FROM employees e INNER

JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id

 This part fetches all employees who report to the managers identified in the previous step.
 The recursive join matches manager_id of employees (e) to employee_id of the higher-
level employee (eh).

 level is incremented by 1 for each subsequent level in the hierarchy.

3. Final Query:
o SELECT * FROM EmployeeHierarchy;

Example

Table: employees

Prep by: Akansha Srivastav Page 4

Unit 4 Advanced Data Retrieval and Manipulation

employee_id employee_name manager_id

1 Alice NULL
2 Bob 1
3 Charlie 1
4 David 2
5 Eve 2
6 Frank 3

Execution Steps

1. Base Query:

SELECT employee_id, manager_id, 1 AS level

FROM employees
WHERE manager_id IS NULL

o This identifies the top-level managers (employees without a manager).

o level is set to 1 for these employees, as they are at the top of the hierarchy.

o Find the top-level manager:

employee_id | manager_id | level

------------|------------|------
1 | NULL |1

2. First Iteration of Recursive Query:

UNION ALL
SELECT e.employee_id, e.manager_id, eh.level + 1
FROM employees e
INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id

o This part fetches all employees who report to the managers identified in the
previous step.
o The recursive join matches manager_id of employees (e) to employee_id of the
higher-level employee (eh).
o level is incremented by 1 for each subsequent level in the hierarchy.

o Employees reporting to employee_id = 1 (Alice):

Prep by: Akansha Srivastav Page 5

Unit 4 Advanced Data Retrieval and Manipulation

employee_id | manager_id | level

------------|------------|------
2 |1 |2
3 |1 |2

3. Second Iteration of Recursive Query:

o Employees reporting to employee_id = 2 (Bob):

employee_id | manager_id | level

------------|------------|------
4 |2 |3
5 |2 |3

o Employees reporting to employee_id = 3 (Charlie):

employee_id | manager_id | level

------------|------------|------
6 |3 |3

4. Final Result: Combining all iterations, the query produces:

SELECT * FROM EmployeeHierarchy;

Retrieves all the hierarchical data generated by the CTE.

employee_id | manager_id | level

------------|------------|------
1 | NULL |1
2 |1 |2
3 |1 |2
4 |2 |3
5 |2 |3
6 |3 |3

This type of query is helpful for:

 Representing organizational structures.

 Finding all subordinates of a manager at different levels.
 Calculating hierarchical relationships like depth or levels in a tree structure.

Prep by: Akansha Srivastav Page 6

Unit 4 Advanced Data Retrieval and Manipulation

Non-Recursive CTE

A common table expression that doesn't reference itself is known as a non-recursive CTE. A
non-recursive CTE is simple and easier to understand because it does not use the concept of
recursion. According to the CTE Syntax, each CTE query will begin with a "With" clause followed
by the CTE name and column list, then AS with parenthesis.

WITH DepartmentSalary AS (
SELECT department_id, AVG(salary) AS AvgSalary
FROM employees
GROUP BY department_id
)
SELECT * FROM DepartmentSalary WHERE AvgSalary > 50000;

Disadvantages of CTE
The following are the limitations of using CTE in SQL Server:

o CTE members are unable to use the keyword clauses like Distinct, Group By, Having,
Top, Joins, etc.
o The CTE can only be referenced once by the Recursive member.
o We cannot use the table variables and CTEs as parameters in stored procedures.
o We already know that the CTE could be used in place of a view, but a CTE cannot be
nested, while Views can.
o Since it's just a shortcut for a query or subquery, it can't be reused in another query.
o The number of columns in the CTE arguments and the number of columns in the query
must be the same.

Simplifying Complex Queries with CTEs

 CTEs improve query readability and maintainability by:

o Breaking down long queries into manageable parts.
o Reusing the result set within the same query.

Example:
Using a CTE to calculate average salary and filter results:

WITH AvgSalaries AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
)
SELECT department_id, employee_id, salary

Prep by: Akansha Srivastav Page 7

Unit 4 Advanced Data Retrieval and Manipulation

FROM employees e
INNER JOIN AvgSalaries a ON e.department_id = a.department_id
WHERE e.salary > a.avg_salary;

Cumulative Sums
 Purpose: Used to calculate running totals or cumulative values within a partition.
 Achieved Using: SUM() window function.
 Example:

The SQL query uses the SUM() function as a window function with the OVER() clause. Here's
what it does step-by-step:

Explanation of the Query

1. employee_id and salary: These columns are directly selected from the employees table
for each employee.
2. SUM(salary) OVER (...) AS cumulative_salary:
o SUM(salary): This calculates the cumulative sum of salaries.
o OVER (PARTITION BY department_id ORDER BY employee_id):
 PARTITION BY department_id: Groups the rows based on the
department_id. Each department is treated as an independent group.
 ORDER BY employee_id: Orders the rows within each department_id
group based on the employee_id. The cumulative sum will be calculated
in this order.
o AS cumulative_salary: Renames the calculated column to cumulative_salary.

What the Query Outputs

For each employee in the employees table:

 It retrieves the employee_id, salary, and the cumulative sum of salaries for employees in
the same department (department_id), ordered by their employee_id.

Example

Suppose the employees table looks like this:

employee_id department_id salary

1 10 5000
2 10 7000
3 20 6000

Prep by: Akansha Srivastav Page 8

Unit 4 Advanced Data Retrieval and Manipulation

employee_id department_id salary

4 10 8000
5 20 4000

The query output will be:

employee_id salary cumulative_salary

1 5000 5000
2 7000 12000
4 8000 20000
3 6000 6000
5 4000 10000

 The cumulative salary resets when moving to a new department (department_id).

 Within each department, the cumulative salary increases progressively based on the
ordered employee_id.

Prep by: Akansha Srivastav Page 9