Always will be ready notify the world about expectations as easy as possible: job change page
Mar 15

20 advanced SQL techniques

20 advanced SQL techniques

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.


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.


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.


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.


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.


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.


    SELECT department_id, job_id, salary
    FROM employees
    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.


SELECT employee_id FROM employees
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.


SELECT employee_id,
           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.


WITH RECURSIVE employee_hierarchy AS (
    SELECT employee_id, first_name, last_name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    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.


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.


INSERT INTO employees (employee_id, first_name, last_name, email, hire_date)
VALUES (1001, 'John', 'Doe', '', '2023-02-15');

12. Temporary Tables

Temporary tables are created and used for the duration of a session or transaction.


SELECT * FROM employees WHERE department_id = 50;

13. Grouping Sets

Grouping sets allow us to define multiple grouping sets within a single SQL query.


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.


CREATE PROCEDURE get_employee_info (IN employee_id INT)
    SELECT * FROM employees WHERE employee_id = employee_id;

15. Indexing

Indexes improve the speed of data retrieval operations by providing quick access to rows in a table.


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.


SELECT employee_id, salary FROM employees;

17. Database Constraints

Constraints enforce rules for data integrity, such as unique keys, foreign keys, and check constraints.


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.


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.


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.


EXECUTE IMMEDIATE 'SELECT * FROM employees WHERE department_id = :dept_id' USING 50;


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.

Dec 7, 2022
Author: Anurag Sharma
Keeping our SQL server in a healthy state is a matter of concern for sure. Here users can learn the top 11 SQL server maintenance plan best practices that experts, DBAs, architects, and developers follow. No doubt that users often...
May 14, 2023
Author: Edwin Klesman
In this article, I’ll show you what the basic steps are for converting a SQL query into LINQ. You’ll learn the basic steps needed while we convert an example query.In this article, it's assumed that you have a basic understanding...
Jul 10, 2021
Author: Sam Walpole
I've recently gotten into using Docker in my development cycle and I'm really enjoying how much of a wonderful tool it is. One thing that always used to be a pain was setting up a development server to run SQL...
Feb 6, 2023
Author: Sajal Soni
In this tutorial, I'll teach you how to build a simple REST API with PHP and MySQL.REST has become the de facto standard when it comes to exposing data via APIs and building web services. In fact, most web applications...
Send message
Your name

© 1999–2024 WebDynamics
1980–... Sergey Drozdov
Area of interests: .NET Framework | .NET Core | C# | ASP.NET | Windows Forms | WPF | HTML5 | CSS3 | jQuery | AJAX | Angular | React | MS SQL Server | Transact-SQL | ADO.NET | Entity Framework | IIS | OOP | OOA | OOD | WCF | WPF | MSMQ | MVC | MVP | MVVM | Design Patterns | Enterprise Architecture | Scrum | Kanban