Advanced SQL Concepts

Objective: Explore advanced SQL concepts to write more complex queries.

1. Nested Queries and Subqueries:

In this example, the subquery (SELECT AVG(salary) FROM employees) calculates the average salary, and the main query retrieves employees earning more than this average. This technique is useful for filtering data based on a specific condition derived from the same or another table.

• Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name OPERATOR (SELECT column_name FROM table_name WHERE condition);

• Example:

SELECT employee_name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

2. Common Table Expressions (CTEs):

Here, SalesCTE calculates total sales for each salesperson, and the main query retrieves those with sales over 100,000. CTEs help in organizing and simplifying complex queries, making them easier to understand and maintain.

• Syntax:

WITH cte_name AS (
     SELECT column_name(s)
     FROM table_name
     WHERE condition
)
SELECT column_name(s)
FROM cte_name
WHERE condition;

• Example:

WITH SalesCTE AS (
     SELECT salesperson_id, SUM(sales_amount) AS total_sales
     FROM sales
     GROUP BY salesperson_id
)
SELECT *
FROM SalesCTE
WHERE total_sales > 100000;

3. Window Functions:

This query ranks employees within their department by salary, providing a salary_rank for each employee. Window functions allow you to perform calculations across a set of rows related to the current row, giving you more control over data analysis.

• Syntax:

SELECT column_name(s),
        window_function() OVER (PARTITION BY column_name ORDER BY column_name) AS alias_name
FROM table_name;

• Example:

SELECT employee_name, department_id, salary,
        RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;

4. Case Statements and Conditional Logic:

This query uses a case statement to classify employees into different salary levels based on their earnings. Case statements allow you to apply conditional logic directly within your SQL queries, enabling dynamic data categorization.

• Syntax:

SELECT column_name,
        CASE
            WHEN condition1 THEN result1
            WHEN condition2 THEN result2
            ELSE result3
        END AS alias_name
FROM table_name;

• Example:

SELECT employee_name, salary,
      CASE 
            WHEN salary > 50000 THEN 'High'
            WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium'
            ELSE 'Low'
      END AS salary_level
FROM employees;

5. Recursive Queries:

In this query, OrgChart recursively builds an organizational chart, starting from the top manager and working down through the hierarchy. Recursive queries are essential for handling hierarchical or tree-structured data in SQL.

• Syntax:

WITH RECURSIVE cte_name AS (
     SELECT column_name(s)
     FROM table_name
     WHERE condition
     UNION ALL
     SELECT column_name(s)
     FROM table_name
     INNER JOIN cte_name ON condition
)
SELECT column_name(s)
FROM cte_name;

• Example:

WITH RECURSIVE OrgChart AS (
     SELECT employee_id, manager_id, employee_name
     FROM employees
     WHERE manager_id IS NULL
     UNION ALL
     SELECT e.employee_id, e.manager_id, e.employee_name
     FROM employees e
     INNER JOIN OrgChart o ON e.manager_id = o.employee_id
)
SELECT * FROM OrgChart;

Conclusion

By mastering advanced SQL concepts such as nested queries, Common Table Expressions (CTEs), window functions, case statements, and recursive queries, you’ll be equipped to write more sophisticated and efficient SQL queries. These techniques will empower you to handle complex database operations with ease.

In the next lesson, we’ll explore data manipulation and transactions, covering how to efficiently insert, update, and delete data, and how to manage database transactions in SQL.