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
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
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;
A subquery is a SELECT statement that is nested within another
SET statement. Subqueries are used to return data that will be used in the outer query. They can be used in the
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');
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;
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 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;
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.