10. Subqueries, CTEs & Views

What a Subquery Is

A subquery is a complete SELECT statement written inside parentheses and embedded within another SQL statement. The database engine treats it exactly like a value or a table — it executes the inner query first, gets the result, and then uses that result in the outer query.

Think of it like asking two questions in sequence. First: What is the average GPA? Then: Which students scored above that number? A subquery lets you do both in a single command.

A Scalar Subquery — Returning a Single Value

The simplest subquery returns exactly one number — one row, one column. This is called a scalar subquery. You can use it anywhere a single value is expected, including inside a WHERE clause.

First, let us understand what we want. We want the average GPA of all students:

SELECT AVG(GPA) FROM Student;

That gives us a number — say, 3.25. Now we want to use that number to filter students. We could hard-code it, but hard-coded numbers go stale the moment new data arrives. Instead, we nest the calculation directly inside the WHERE clause:

SELECT FirstName, LastName, GPA
FROM Student
WHERE GPA > (SELECT AVG(GPA) FROM Student);

The engine executes the parenthesised query first, gets 3.25, then evaluates the outer query as if you had written WHERE GPA > 3.25. The parentheses are essential — they tell SQL Server exactly where the subquery starts and ends.

A Subquery in the FROM Clause — An Inline View

A subquery can also appear in the FROM clause, acting as a temporary table for the duration of the query. This is called a derived table or inline view. You must give it an alias using AS, because SQL Server needs a name to reference it.

Here we calculate the average GPA per department inside a subquery, then join that result back to the Department table to display department names alongside the averages:

SELECT
  d.DeptName,
  avg_table.AvgGPA
FROM Department AS d
INNER JOIN (
  SELECT DeptID, AVG(GPA) AS AvgGPA
  FROM Student
  GROUP BY DeptID
) AS avg_table ON d.DeptID = avg_table.DeptID;

The inner SELECT runs first, producing a temporary result with two columns: DeptID and AvgGPA. The outer query then joins that temporary result to the Department table using DeptID. The alias avg_table is how the outer query refers to the inner result.

Common Table Expressions — The Readable Alternative

As subqueries grow longer, nesting them inside each other becomes difficult to read and debug. A Common Table Expression — abbreviated CTE — solves this by letting you define a named temporary result set at the top of your query using the WITH keyword, then reference it by name as many times as you need below.

A CTE does not create any permanent object in the database. It exists only for the duration of the single query it belongs to.

Here is the same department average query rewritten using a CTE:

WITH DeptAverages AS (
  SELECT DeptID, AVG(GPA) AS AvgGPA
  FROM Student
  GROUP BY DeptID
)
SELECT d.DeptName, da.AvgGPA
FROM Department AS d
INNER JOIN DeptAverages AS da ON d.DeptID = da.DeptID;

The WITH clause defines a CTE named DeptAverages. Below it, the main SELECT query references DeptAverages as if it were a regular table. The logic is identical to the derived table approach, but far easier to read. You can even define multiple CTEs separated by commas before the final SELECT statement.

Views — Saved Queries That Behave Like Tables

A subquery or CTE lives only for one query execution. A View is a saved query stored permanently in the database that you can query repeatedly as if it were a real table. The view does not store any data itself — every time you query it, the underlying SELECT statement runs and produces the result fresh.

Views are used to simplify complex logic for other users, to restrict which columns a user can access, and to provide a stable interface even when the underlying table structure changes.

Here is how to create a view that shows students alongside their department names — saving users from having to write the join every time:

CREATE VIEW StudentWithDept AS
SELECT
  s.StudentID,
  s.FirstName,
  s.LastName,
  s.GPA,
  d.DeptName
FROM Student AS s
INNER JOIN Department AS d ON s.DeptID = d.DeptID;

Once created, you query the view exactly like a table:

SELECT FirstName, LastName, DeptName
FROM StudentWithDept
WHERE GPA > 3.0;

If you later add a column to the Student table or rename a department, you update the view definition and all queries using it continue to work without modification.

Modifying and Removing a View

To change a view's definition, use CREATE OR ALTER VIEW in SQL Server. To remove a view entirely, use DROP VIEW:

CREATE OR ALTER VIEW StudentWithDept AS
SELECT
  s.StudentID,
  s.FirstName,
  s.LastName,
  s.GPA,
  s.Email,
  d.DeptName
FROM Student AS s
INNER JOIN Department AS d ON s.DeptID = d.DeptID;

DROP VIEW StudentWithDept;

Dropping a view removes only the saved query definition. It never removes the underlying data in the tables the view references.

How to Use the Subquery & View Lab

The lab gives you a SQL editor and asks you to create a view called EliteOperators. On the left panel, a live diagram tracks your progress — showing three nodes that activate one at a time as your code becomes more complete.

Here is an example of the pattern the lab expects, using a familiar scenario. Suppose you wanted to create a view for students with above-average GPA:

CREATE VIEW AboveAvgStudents AS
SELECT StudentID, FirstName, GPA
FROM Student
WHERE GPA > (
  SELECT AVG(GPA) FROM Student
);

Apply the exact same structure to the lab's context: replace the view name with EliteOperators, replace Student with Operators, replace GPA with the XP column, and reference the Missions table in the inner query. The three diagram nodes activate in sequence as the lab detects your inner query, then your outer query with the greater-than comparison, then your CREATE VIEW wrapper.