Play Store Application link – SQL in 18 steps – App on Google Play

SQL is not just about simple queries; it also includes powerful features that help in solving complex problems efficiently. In this post, we will cover some advanced SQL concepts that are widely used in real-world applications.

1. Common Table Expressions (CTEs) (WITH)

What is a CTE?

A Common Table Expression (CTE) is a temporary result set that helps make queries more readable and maintainable. It is often used for recursive queries, simplifying complex joins, and breaking down complicated SQL logic.

Example: Using CTE to Find Employees in a Department

WITH EmployeeCTE AS (
    SELECT employee_id, name, department_id
    FROM employees
    WHERE department_id = 101
)
SELECT * FROM EmployeeCTE;

Benefits of CTEs:

• Makes queries easier to understand
• Can be referenced multiple times within the same query
• Improves readability over subqueries

2. Recursive Queries

What is a Recursive Query?

Recursive queries allow you to retrieve hierarchical data such as an organization’s reporting structure or category hierarchies in e-commerce websites.

Example: Finding Employee Hierarchy

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
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;

Use Cases:

• Employee-Manager relationships
• Organizational hierarchy
• Parent-child relationships in database structures

3. PIVOT and UNPIVOT (SQL Server, Oracle)

What is Pivoting?

Pivoting is used to convert row data into column format, making it useful for generating reports.

Example: Converting Rows to Columns with PIVOT (SQL Server, Oracle)

SELECT * FROM (
    SELECT department, employee_name, salary FROM employees
)
PIVOT (
    SUM(salary) FOR department IN ('HR', 'IT', 'Sales')
) AS PivotTable;

What is Unpivoting?

Unpivoting is the reverse process, converting columns back into rows.

Example: Converting Columns to Rows with UNPIVOT

SELECT * FROM (
    SELECT employee_id, HR, IT, Sales FROM salaries_table
)
UNPIVOT (
    salary FOR department IN (HR, IT, Sales)
) AS UnpivotTable;

Use Cases:

• Transforming data for reporting purposes
• Data analysis where column-based structure is required

4. Window Functions (RANK(), DENSE_RANK(), ROW_NUMBER())

Window functions perform calculations across a set of table rows that are related to the current row.

Example: Ranking Employees by Salary

SELECT name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS Rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS DenseRank,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS RowNumber
FROM employees;

Explanation:

• RANK(): Skips rankings if there is a tie
• DENSE_RANK(): Does not skip rankings when there is a tie
• ROW_NUMBER(): Assigns a unique row number to each record

Use Cases:

• Finding top N results within groups
• Ranking products based on sales
• Assigning unique row numbers for pagination

Conclusion

These advanced SQL concepts allow developers to write more efficient and readable queries. Whether you are working with hierarchical data, transforming data formats, or ranking records, understanding these features will improve your SQL skills significantly.