Search  
Always will be ready notify the world about expectations as easy as possible: job change page
3 days ago

20 advanced SQL techniques

20 advanced SQL techniques
Source:
Views:
539

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.

Similar
Dec 19, 2020
Author: Asaf Yigal
Relational Database Management Systems (RDBMS) are one of the most widely used database management systems in the world. Based on the relational model invented by Edgar F. Codd, these databases store data in the form of tables, and allow the...
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...
12 мая 2023 г.
NULL - это специальное значение, которое используется в SQL для обозначения отсутствия данных. Оно отличается от пустой строки или нулевого значения, так как NULL означает отсутствие какого-либо значения в ячейке таблицы.История появления NULL в SQL довольно интересна и длинна. В...
Mar 29
Author: Josh Fruhlinger
Generative AI has seized the popular imagination and started a new tech gold rush. While much attention has been focused on AI tools that produce natural language prose and visual art, in tech circles AI is gaining increased interest for...
Send message
Email
Your name
*Message


© 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