Scribd
Upload
8 views2 pages

Advanced SQL Revision Sheet

This document provides an advanced SQL revision sheet covering various complex queries including ranking functions, window functions, common table expressions (CTEs), complex joins, case expressions, and grouping sets. It includes examples for each category, demonstrating how to perform tasks such as calculating ranks, running totals, and hierarchical queries. The document emphasizes practicing these queries on complex datasets to enhance analytical SQL skills.

Uploaded by

chaudhari.lisha.1442
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
8 views2 pages

Advanced SQL Revision Sheet

This document provides an advanced SQL revision sheet covering various complex queries including ranking functions, window functions, common table expressions (CTEs), complex joins, case expressions, and grouping sets. It includes examples for each category, demonstrating how to perform tasks such as calculating ranks, running totals, and hierarchical queries. The document emphasizes practicing these queries on complex datasets to enhance analytical SQL skills.

Uploaded by

chaudhari.lisha.1442
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

■ Advanced SQL Revision Sheet - Hard Level

Queries

1. Ranking Functions
-- RANK: Gives the same rank for ties, skipping numbers
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

-- DENSE_RANK: Same as RANK but without gaps


SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_dense_rank
FROM employees;

-- ROW_NUMBER: Unique sequence numbers


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

2. Window Functions
-- Running total of salaries partitioned by department
SELECT department_id, name, salary,
SUM(salary) OVER (PARTITION BY department_id ORDER BY salary) AS running_total
FROM employees;

-- Moving average of last 3 salaries


SELECT name, salary,
AVG(salary) OVER (ORDER BY salary ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM employees;

3. Common Table Expressions (CTEs)


-- Simple CTE
WITH high_salary AS (
SELECT * FROM employees WHERE salary > 70000
)
SELECT name, salary FROM high_salary ORDER BY salary DESC;

-- Recursive CTE
WITH RECURSIVE emp_hierarchy AS (
SELECT emp_id, manager_id, name FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, e.manager_id, [Link]
FROM employees e
JOIN emp_hierarchy h ON e.manager_id = h.emp_id
)
SELECT * FROM emp_hierarchy;

4. Complex Joins and Subqueries


-- Self-Join: Find pairs of employees in the same department
SELECT [Link] AS emp1, [Link] AS emp2, e1.department_id
FROM employees e1
JOIN employees e2 ON e1.department_id = e2.department_id
AND e1.emp_id < e2.emp_id;

-- Subquery in FROM clause


SELECT d.department_id, avg_salary
FROM (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
) d
WHERE avg_salary > 60000;

5. CASE Expressions
SELECT name, salary,
CASE
WHEN salary > 80000 THEN 'High'
WHEN salary BETWEEN 50000 AND 80000 THEN 'Medium'
ELSE 'Low'
END AS salary_band
FROM employees;

6. Grouping Sets, Cube, and Rollup


-- Grouping Sets
SELECT department_id, job_title, SUM(salary) AS total_salary
FROM employees
GROUP BY GROUPING SETS ((department_id), (job_title), ());

-- Rollup
SELECT department_id, job_title, SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP(department_id, job_title);

Tip: Practice these advanced queries on complex datasets to master analytical SQL skills.

You might also like