Section 1.1: Subqueries Explained

Subqueries, often referred to as nested queries or inner queries, are SQL queries embedded within another SQL statement. They can be utilized in SELECT, INSERT, UPDATE, and DELETE commands, proving particularly beneficial for intricate data retrieval tasks.

For instance, if we want to identify employees earning above their department's average salary, we could write:

SELECT employee_name

FROM employees

WHERE salary > (SELECT AVG(salary) FROM employees GROUP BY department);

In this scenario, the inner query (SELECT AVG(salary) FROM employees GROUP BY department) computes the average salary for each department, while the outer query fetches the names of employees whose salaries surpass this average.

Section 1.2: Understanding Common Table Expressions (CTEs)

Common Table Expressions (CTEs) allow you to define temporary result sets that can be referenced in subsequent SQL statements. This approach enhances the readability and maintainability of complex queries by segmenting them into smaller, more digestible components.

For example, to calculate the total sales amount for each customer and rank them according to their sales figures, you might use:

WITH SalesCTE AS (

SELECT customer_id, SUM(amount) AS total_sales

FROM orders

GROUP BY customer_id

)

SELECT customer_id, total_sales,

RANK() OVER (ORDER BY total_sales DESC) AS sales_rank

FROM SalesCTE;

Here, the CTE named SalesCTE sums the sales for each customer, and the outer query retrieves the customer ID, total sales, and assigns a rank based on their sales figures.

This video, titled "The Last Advanced SQL Tutorial You Will Ever Need To Watch As A Data Scientist Or Engineer," provides comprehensive insights into SQL techniques, making it an invaluable resource for data professionals.

Section 1.3: The Power of Window Functions

Window functions enable calculations across a set of rows related to the current row without reducing the result set. They are particularly useful for tasks like computing moving averages, cumulative sums, and ranking rows according to specific criteria.

For example, to find the cumulative salary of each employee based on their hiring date, you could execute:

SELECT employee_id, hire_date, salary,

SUM(salary) OVER (ORDER BY hire_date) AS cumulative_salary

FROM employees;

In this example, the window function SUM(salary) OVER (ORDER BY hire_date) calculates the cumulative total of salaries as the query processes rows sorted by hire date.

Section 1.4: Utilizing Pivot and Unpivot Operations

Pivot and unpivot operations allow for the transformation of data from rows to columns (pivot) and columns to rows (unpivot). These operations are beneficial for crafting summary reports and restructuring data for analysis.

As an illustration, if you have a sales table with columns for year, quarter, and revenue, and you wish to pivot the data to display quarterly revenues for each year, you might use:

SELECT year,

SUM(CASE WHEN quarter = 1 THEN revenue ELSE 0 END) AS Q1,

SUM(CASE WHEN quarter = 2 THEN revenue ELSE 0 END) AS Q2,

SUM(CASE WHEN quarter = 3 THEN revenue ELSE 0 END) AS Q3,

SUM(CASE WHEN quarter = 4 THEN revenue ELSE 0 END) AS Q4

FROM sales

GROUP BY year;

This query transforms the quarterly revenue data into separate columns for each quarter, grouped by year.