Section 1.1: Subqueries

The subquery is one of the most prevalent methods for retaining a result set. A subquery is essentially a query embedded within another query, serving as a means to store transformation logic for use by an outer query. Subqueries can be incorporated into SELECT, FROM, and WHERE clauses.

For instance:

SELECT

employee_name

FROM

employees

WHERE

department_id IN (

SELECT

department_id

FROM

departments

WHERE

location = 'New York'

);

Here, the subquery identifies departments located in New York, while the outer query retrieves the names of employees associated with those departments.

Another example:

SELECT

o.order_id,

o.quantity,

(

SELECT

p.price

FROM

products p

WHERE

p.product_id = o.product_id

) * o.quantity AS total_price

FROM

orders o;

In this case, the subquery fetches the price of each product from the "orders" table, allowing the main query to calculate the total price for each order.

Section 1.2: Common Table Expressions (CTEs)

Common Table Expressions (CTEs) represent a temporary result set that can be referenced throughout a SELECT, INSERT, UPDATE, or DELETE statement. CTEs begin with the WITH keyword and are often preferred over subqueries due to their enhanced readability, especially during intricate data manipulation tasks.

For example:

WITH regional_sales AS (

SELECT

region,

SUM(sales) AS total_sales

FROM

orders

GROUP BY

region

)

SELECT

region

FROM

regional_sales

WHERE

total_sales > 1000000;

In this example, the CTE "regional_sales" aggregates total sales by region, and the main query retrieves regions with sales exceeding one million.

Another CTE example:

WITH department_average_salary AS (

SELECT

department_id,

AVG(salary) AS average_salary

FROM

employees

GROUP BY

department_id

HAVING

AVG(salary) > 50000

)

SELECT

e.name,

e.salary,

e.department_id

FROM

employees e

INNER JOIN

department_average_salary das

ON

e.department_id = das.department_id;

Here, the CTE calculates the average salary for each department, filtering out those with an average salary above 50,000. The main query then joins this CTE with the "employees" table to present a list of employees along with their respective details.

Section 2.1: Views

A view functions as a dynamic representation of a table, encapsulating the results of a transformation. For example:

CREATE VIEW active_employees AS

(

SELECT

employee_id,

employee_name

FROM

employees

WHERE

status = 'Active'

);

SELECT * FROM active_employees;

In this scenario, the view "active_employees" collects all active employee records, which can be queried like a standard table.

Section 2.2: Materialized Views

Materialized views allow for the storage of result sets, which can be refreshed periodically, thus enhancing query performance—particularly for complex aggregations. Unlike standard views, which compute results dynamically, materialized views store the result on disk.

Consider the following example for creating a materialized view:

CREATE MATERIALIZED VIEW monthly_sales AS

SELECT

store_id,

YEAR(sale_date) AS sale_year,

MONTH(sale_date) AS sale_month,

SUM(amount) AS total_sales

FROM

Sales

GROUP BY

store_id,

sale_year,

sale_month;

This materialized view summarizes monthly sales data for each store.

Section 2.3: Temporary Tables

Temporary tables in SQL are utilized to hold data within a session, automatically deleting themselves once the session concludes. They differ from subqueries, CTEs, and views by physically storing data, which can significantly enhance the efficiency of complex queries.

The syntax varies between SQL Server and MySQL:

-- MySQL Example

CREATE TEMPORARY TABLE Temp_Table AS

(

SELECT

col1, col2

FROM

table_name

WHERE

col2 > 500000

);

In SQL Server, temporary tables can be either local (visible only to the creating connection) or global (visible to all sessions).

To illustrate their application:

CREATE TEMPORARY TABLE selling_products AS

SELECT

s.year,

p.product_name,

SUM(s.amount) AS total_sales

FROM

Sales s

JOIN

Products p

ON s.product_id = p.product_id

GROUP BY

s.year, p.product_name

ORDER BY

s.year, total_sales DESC;

This example creates a temporary table to store sales data for each product across years.