Advanced SQL Tutorial

SQL (Structured Query Language) is a powerful tool for managing and manipulating databases. While basic SQL covers essential operations like querying, inserting, updating, and deleting data, advanced SQL techniques can solve complex problems and optimize database performance. This tutorial aims to cover advanced SQL topics comprehensively, including subqueries, window functions, Common Table Expressions (CTEs), indexing strategies, stored procedures, and more.

1. Advanced SELECT Queries

Subqueries

Subqueries are queries nested within another query. They can be used in various parts of a SQL statement, including the SELECT, FROM, WHERE, and HAVING clauses.

Example: Subquery in SELECT Clause

SELECT 
    employee_id,
    (SELECT AVG(salary) FROM employees) AS average_salary
FROM 
    employees;

Example: Subquery in WHERE Clause

SELECT 
    employee_id,
    name
FROM 
    employees
WHERE 
    department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');

Example: Correlated Subquery

A correlated subquery references columns from the outer query.

SELECT 
    e1.employee_id,
    e1.name
FROM 
    employees e1
WHERE 
    e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e1.department_id);

JOIN Variants

Understanding the different types of JOIN operations is crucial for combining data from multiple tables effectively.

INNER JOIN

SELECT 
    e.employee_id,
    e.name,
    d.department_name
FROM 
    employees e
INNER JOIN 
    departments d ON e.department_id = d.department_id;

LEFT JOIN

SELECT 
    e.employee_id,
    e.name,
    d.department_name
FROM 
    employees e
LEFT JOIN 
    departments d ON e.department_id = d.department_id;

RIGHT JOIN

SELECT 
    e.employee_id,
    e.name,
    d.department_name
FROM 
    employees e
RIGHT JOIN 
    departments d ON e.department_id = d.department_id;

FULL OUTER JOIN

SELECT 
    e.employee_id,
    e.name,
    d.department_name
FROM 
    employees e
FULL OUTER JOIN 
    departments d ON e.department_id = d.department_id;

UNION, INTERSECT, and EXCEPT

These set operations allow combining results from multiple queries.

UNION

Combines results from two queries and removes duplicates.

SELECT 
    name 
FROM 
    customers
UNION
SELECT 
    name 
FROM 
    employees;

UNION ALL

Combines results from two queries without removing duplicates.

SELECT 
    name 
FROM 
    customers
UNION ALL
SELECT 
    name 
FROM 
    employees;

INTERSECT

Returns common records between two queries.

SELECT 
    name 
FROM 
    customers
INTERSECT
SELECT 
    name 
FROM 
    employees;

EXCEPT

Returns records from the first query that are not present in the second query.

SELECT 
    name 
FROM 
    customers
EXCEPT
SELECT 
    name 
FROM 
    employees;

2. Window Functions

Window functions perform calculations across a set of table rows related to the current row. Unlike aggregate functions, window functions do not cause rows to be grouped into a single output row.

OVER Clause

The OVER clause defines the window over which the function operates.

Example: Using ROW_NUMBER()

SELECT 
    employee_id,
    name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
FROM 
    employees;

Example: Using RANK()

SELECT 
    employee_id,
    name,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM 
    employees;

Example: Using DENSE_RANK()

SELECT 
    employee_id,
    name,
    salary,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank
FROM 
    employees;

Aggregate Window Functions

These functions calculate aggregates such as sum, average, minimum, and maximum over a specified window of rows.

Example: Using SUM()

SELECT 
    employee_id,
    name,
    salary,
    SUM(salary) OVER (PARTITION BY department_id) AS total_salary
FROM 
    employees;

Example: Using AVG()

SELECT 
    employee_id,
    name,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) AS average_salary
FROM 
    employees;

3. Common Table Expressions (CTEs)

CTEs are temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

Example: Simple CTE

WITH DepartmentSalary AS (
    SELECT 
        department_id,
        AVG(salary) AS average_salary
    FROM 
        employees
    GROUP BY 
        department_id
)
SELECT 
    e.employee_id,
    e.name,
    ds.average_salary
FROM 
    employees e
JOIN 
    DepartmentSalary ds ON e.department_id = ds.department_id;

Recursive CTEs

Recursive CTEs are used to query hierarchical data.

Example: Recursive CTE

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT 
        employee_id,
        name,
        manager_id
    FROM 
        employees
    WHERE 
        manager_id IS NULL
    UNION ALL
    SELECT 
        e.employee_id,
        e.name,
        e.manager_id
    FROM 
        employees e
    JOIN 
        EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT 
    * 
FROM 
    EmployeeHierarchy;

4. Indexing Strategies

Indexes improve query performance by allowing the database to find rows more efficiently.

Types of Indexes

B-Tree Indexes

Default index type, suitable for most queries.

Bitmap Indexes

Used in data warehouses and suitable for columns with a low cardinality.

Hash Indexes

Useful for equality comparisons.

Full-Text Indexes

Used for full-text searches.

Indexing Best Practices

• Choose the Right Columns: Index columns used in WHERE, JOIN, and ORDER BY clauses.
• Avoid Over-Indexing: Too many indexes can slow down INSERT, UPDATE, and DELETE operations.
• Use Composite Indexes: Index multiple columns when queries filter on multiple columns.

5. Stored Procedures and Functions

Stored procedures and functions encapsulate reusable logic.

Creating and Using Stored Procedures

Example: Stored Procedure

CREATE PROCEDURE GetEmployeeDetails (
    IN emp_id INT
)
BEGIN
    SELECT 
        employee_id,
        name,
        department_id,
        salary
    FROM 
        employees
    WHERE 
        employee_id = emp_id;
END;

Executing Stored Procedure

CALL GetEmployeeDetails(101);

User-Defined Functions

Example: Scalar Function

CREATE FUNCTION GetEmployeeName(emp_id INT) 
RETURNS VARCHAR(100)
BEGIN
    DECLARE emp_name VARCHAR(100);
    SELECT name INTO emp_name FROM employees WHERE employee_id = emp_id;
    RETURN emp_name;
END;

Using User-Defined Function

SELECT 
    GetEmployeeName(101) AS employee_name;

6. Transactions and Concurrency

Transactions ensure data integrity and consistency.

ACID Properties

• Atomicity: Ensures that all operations within a transaction are completed; otherwise, the transaction is aborted.
• Consistency: Ensures the database remains in a consistent state before and after the transaction.
• Isolation: Ensures that transactions are isolated from each other.
• Durability: Ensures that the results of a transaction are permanent.

Isolation Levels

• READ UNCOMMITTED: Allows dirty reads.
• READ COMMITTED: Prevents dirty reads.
• REPEATABLE READ: Prevents dirty reads and non-repeatable reads.
• SERIALIZABLE: Prevents dirty reads, non-repeatable reads, and phantom reads.

Locking Mechanisms

• Pessimistic Locking: Locks data until the transaction is complete.
• Optimistic Locking: Assumes data will not conflict and only checks before committing.

7. Performance Tuning

Optimizing SQL queries and database performance is crucial.

Query Optimization Techniques

• Use EXPLAIN: Analyze query execution plans.
• Optimize Joins: Use appropriate join types and indexes.
• Limit Result Set: Retrieve only necessary data.
• **Avoid SELECT * **: Specify only needed columns.

Analyzing Query Execution Plans

Use EXPLAIN to understand how the database executes a query and identify bottlenecks.

EXPLAIN SELECT 
    e.employee_id,
    e.name,
    d.department_name
FROM 
    employees e
JOIN 
    departments d ON e.department_id = d.department_id;

8. Advanced Data Types and JSON

SQL databases support various advanced data types, including JSON.

Working with JSON Data

Example: Storing JSON Data

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_details JSON
);

Example: Querying JSON Data

SELECT 
    product_id,
    product_details->>'$.name' AS product_name
FROM 
    products;

Full-Text Search

Full-text search allows for complex search queries on textual data.

Example: Full-Text Search

CREATE FULLTEXT INDEX idx_product_name ON products(product_name);

SELECT 
    product_id,
    product_name
FROM 
    products
WHERE 
    MATCH(product_name) AGAINST ('laptop');

Conclusion

Advanced SQL techniques enable you to handle complex queries, improve performance, and maintain database integrity and consistency. By mastering subqueries, window functions, CTEs, indexing, stored procedures, transactions, and performance tuning, you can become proficient in managing and optimizing databases.