Skip to content

SQL Advanced Concepts

Advanced SQL concepts are explained below, with examples based on the Hotel Reservation System relational data model introduced in the previous section.

Common Table Expression (CTE)

A Common Table Expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. It can be used to simplify complex queries by breaking them up into smaller, more manageable pieces.

A CTE is defined using the WITH clause, which precedes the SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. It can be used to reference one or more CTEs in a single query.

Here is the basic syntax for defining a CTE:

WITH cte_name (column_name1, column_name2, ...) AS
(
    SELECT column_name1, column_name2, ...
    FROM table_name
    WHERE condition
)

Here is an example of how you might use a CTE to compute the total number of rooms and the total number of occupied rooms in each hotel:

WITH rooms (hotel_id, num_rooms, num_occupied) AS
(
    SELECT R.hotel_id, COUNT(*) AS num_rooms, SUM(CASE WHEN R.room_id IN
        (SELECT room_id FROM RESERVATIONS WHERE check_in <= CURRENT_DATE AND check_out > CURRENT_DATE) THEN 1 ELSE 0 END) AS num_occupied
    FROM ROOMS R
    GROUP BY R.hotel_id
)
SELECT H.name, R.num_rooms, R.num_occupied
FROM HOTELS H
INNER JOIN rooms R ON H.hotel_id = R.hotel_id;

Here is another example that uses a CTE to compute the average length of stay for guests at each hotel:

WITH stays (hotel_id, avg_stay) AS
(
    SELECT H.hotel_id, AVG(DATEDIFF(check_out, check_in)) AS avg_stay
    FROM RESERVATIONS R
    INNER JOIN ROOMS RO ON R.room_id = RO.room_id
    INNER JOIN HOTELS H ON RO.hotel_id = H.hotel_id
    GROUP BY H.hotel_id
)
SELECT H.name, S.avg_stay
FROM HOTELS H
INNER JOIN stays S ON H.hotel_id = S.hotel_id;

Subqueries

A subquery is a SELECT statement that is nested within another SELECT, INSERT, UPDATE, DELETE, or SET statement. Subqueries are used to return data that will be used in the outer query. They can be used in the SELECT, FROM, WHERE, and HAVING clauses.

Here is an example of how you might use a subquery to retrieve the names of all guests who have made a reservation at a specific hotel:

SELECT first_name, last_name
FROM GUESTS
WHERE guest_id IN
    (SELECT guest_id
     FROM RESERVATIONS
     INNER JOIN ROOMS ON RESERVATIONS.room_id = ROOMS.room_id
     INNER JOIN HOTELS ON ROOMS.hotel_id = HOTELS.hotel_id
     WHERE HOTELS.name = 'Hotel Name');

Window functions

A window function performs a calculation across a set of rows that are related to the current row. It allows you to compute values such as running totals, moving averages, and rank.

Here is an example of how you might use a window function to compute the running total of reservations made by each guest:

SELECT first_name, last_name, count(*) OVER (PARTITION BY guest_id ORDER BY reservation_id) as running_total
FROM RESERVATIONS
INNER JOIN GUESTS ON RESERVATIONS.guest_id = GUESTS.guest_id;

Pivot tables

A pivot table is a table that summarizes and rearranges data in a more meaningful and easier to understand format. It allows you to transform rows into columns, and vice versa. Here is an example of how you might use a pivot table to summarize the number of reservations made by each guest at each hotel:

SELECT *
FROM
(
    SELECT G.first_name, G.last_name, H.name as hotel_name, COUNT(*) as num_reservations
    FROM RESERVATIONS R
    INNER JOIN GUESTS G ON R.guest_id = G.guest_id
    INNER JOIN ROOMS RO ON R.room_id = RO.room_id
    INNER JOIN HOTELS H ON RO.hotel_id = H.hotel_id
    GROUP BY G.first_name, G.last_name, H.name
) AS source_table
PIVOT
(
    SUM(num_reservations)
    FOR hotel_name IN ([Hotel 1], [Hotel 2], [Hotel 3])
) AS pivot_table;

Recursive SQL

Recursive SQL refers to the use of recursive common table expressions (CTEs) in SQL. A recursive CTE is a CTE that references itself, allowing you to perform complex queries on hierarchical or recursive data structures, such as trees or graphs.

Here is the basic syntax for defining a recursive CTE:

WITH cte_name (column_name1, column_name2, ...) AS
(
    SELECT column_name1, column_name2, ...
    FROM table_name
    WHERE condition
    UNION ALL
    SELECT column_name1, column_name2, ...
    FROM cte_name
    WHERE condition
)

The UNION ALL operator is used to combine the results of the initial query with the results of the recursive query. The WHERE clause is used to specify the termination condition for the recursion.

Here is an example of how you might use a recursive CTE to build a list of all the ancestors of a specific node in a tree:

WITH ancestors (id, name, parent_id) AS
(
    SELECT id, name, parent_id
    FROM nodes
    WHERE id = @node_id
    UNION ALL
    SELECT n.id, n.name, n.parent_id
    FROM nodes n
    INNER JOIN ancestors a ON n.id = a.parent_id
)
SELECT id, name, parent_id
FROM ancestors;

Additional Topics

There are several more advanced SQL concepts, including:

  • Stored procedures: A stored procedure is a pre-compiled collection of SQL statements that can be stored in a database and executed on demand. Stored procedures can be used to encapsulate complex logic and to improve performance by reducing the amount of data that needs to be transmitted between the database and the client.

  • Triggers: A trigger is a special type of stored procedure that is automatically executed in response to a specific event, such as the insertion, update, or deletion of a row in a table. Triggers can be used to enforce data integrity, to maintain data history, or to perform other tasks automatically.

  • Cursors: A cursor is a database object that allows you to fetch and process rows one at a time from a result set. Cursors can be useful for processing large datasets in a controlled manner, or for iterating through the rows of a result set one at a time.

  • Dynamic SQL: Dynamic SQL is a technique that allows you to generate and execute SQL statements dynamically at runtime. It can be used to build flexible and dynamic queries, or to execute statements that cannot be hard-coded in advance.