Mastering SQL with practical examples
SQL is a powerful tool for managing and manipulating data in relational databases. While basic SQL queries are essential, mastering advanced SQL queries empowers data professionals to extract complex insights and perform sophisticated data transformations. In this article, we’ll explore 20 advanced SQL queries that cover a wide range of scenarios, from subqueries to window functions, with practical code examples.
1. Subqueries
Subqueries allow us to nest one query inside another, enabling more complex data retrieval and filtering.
Example:
SELECT *
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);
2. Joins
SQL joins combine rows from two or more tables based on a related column between them.
Example:
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
3. Aggregate Functions
Aggregate functions perform calculations on a set of values and return a single value.
Example:
SELECT AVG(salary) AS avg_salary
FROM employees;
4. Window Functions
Window functions operate on a set of rows related to the current row within a query result.
Example:
SELECT employee_id, salary,
AVG(salary) OVER (PARTITION BY department_id) AS avg_salary_by_dept
FROM employees;
5. Common Table Expressions (CTEs)
CTEs are temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.
Example:
WITH high_salary_employees AS (
SELECT *
FROM employees
WHERE salary > 100000
)
SELECT * FROM high_salary_employees;
6. Pivot Tables
Pivot tables reorganize data from rows into columns, summarizing it in the process.
Example:
SELECT *
FROM (
SELECT department_id, job_id, salary
FROM employees
)
PIVOT (
AVG(salary)
FOR job_id IN ('ST_CLERK', 'IT_PROG', 'SA_REP', 'SA_MAN')
);
7. Unions and Intersections
UNION combines the result sets of two or more SELECT statements, while INTERSECT returns the common rows between them.
Example:
SELECT employee_id FROM employees
UNION
SELECT employee_id FROM job_history;
8. Case Statements
CASE statements allow us to perform conditional logic within SQL queries, similar to if-else statements in programming languages.
Example:
SELECT employee_id,
CASE
WHEN salary > 100000 THEN 'High'
WHEN salary > 50000 THEN 'Medium'
ELSE 'Low'
END AS salary_category
FROM employees;
9. Recursive Queries
Recursive queries enable hierarchical data retrieval, such as organizational structures or network graphs.
Example:
WITH RECURSIVE employee_hierarchy AS (
SELECT employee_id, first_name, last_name, manager_id
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.first_name, e.last_name, e.manager_id
FROM employees e
INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;
10. Ranking Functions
Ranking functions assign a rank to each row within a result set based on specified criteria.
Example:
SELECT employee_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;
11. Data Modification Statements
SQL not only retrieves data but also modifies it. INSERT, UPDATE, DELETE statements are used for data manipulation.
Example:
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date)
VALUES (1001, 'John', 'Doe', 'john.doe@example.com', '2023-02-15');
12. Temporary Tables
Temporary tables are created and used for the duration of a session or transaction.
Example:
CREATE TEMPORARY TABLE temp_employees AS
SELECT * FROM employees WHERE department_id = 50;
13. Grouping Sets
Grouping sets allow us to define multiple grouping sets within a single SQL query.
Example:
SELECT department_id, job_id, SUM(salary) AS total_salary
FROM employees
GROUP BY GROUPING SETS ((department_id), (department_id, job_id));
14. Stored Procedures
Stored procedures are precompiled SQL statements stored in the database for reuse.
Example:
CREATE PROCEDURE get_employee_info (IN employee_id INT)
BEGIN
SELECT * FROM employees WHERE employee_id = employee_id;
END;
15. Indexing
Indexes improve the speed of data retrieval operations by providing quick access to rows in a table.
Example:
CREATE INDEX idx_last_name ON employees (last_name);
16. Materialized Views
Materialized views store the results of a query physically, allowing for faster access to data.
Example:
CREATE MATERIALIZED VIEW mv_employee_salary AS
SELECT employee_id, salary FROM employees;
17. Database Constraints
Constraints enforce rules for data integrity, such as unique keys, foreign keys, and check constraints.
Example:
ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY (department_id) REFERENCES departments(department_id);
18. Conditional Aggregation
Conditional aggregation performs aggregate functions based on specified conditions.
Example:
SELECT department_id,
COUNT(CASE WHEN salary > 50000 THEN 1 END) AS high_salary_count,
COUNT(CASE WHEN salary <= 50000 THEN 1 END) AS low_salary_count
FROM employees
GROUP BY department_id;
19. Window Frame Clauses
Window frame clauses specify the window of rows used for calculations in window functions.
Example:
SELECT employee_id, salary,
SUM(salary) OVER (ORDER BY employee_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS salary_sum
FROM employees;
20. Dynamic SQL
Dynamic SQL allows for the creation and execution of SQL statements at runtime.
Example:
EXECUTE IMMEDIATE 'SELECT * FROM employees WHERE department_id = :dept_id' USING 50;
Conclusion
Mastering advanced SQL queries is crucial for extracting valuable insights and performing complex data operations. In this article, we’ve explored 20 advanced SQL queries with practical examples, covering subqueries, joins, aggregate functions, window functions, common table expressions, pivot tables, unions, and intersections. By understanding and applying these techniques, data professionals can leverage SQL to its fullest potential and unlock the full power of their relational databases.