Поиск  
Always will be ready notify the world about expectations as easy as possible: job change page
May 27

20 advanced SQL techniques

20 advanced SQL techniques
Автор:
Источник:
Просмотров:
1382

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.

Похожее
Sep 23, 2022
Author: Jaydeep Patil
In this article, we will discuss gRPC and perform CRUD Operation using that and step-by-step implementation of gRPC. We take Product Application here to understand how things are going to work with gRPC and, in that first, we create ProductOfferGrpcService...
19 июля 2019 г.
Автор: Роберт Шелдон
Индексы — это первое, что необходимо хорошо понимать в работе SQL Server, но странным образом базовые вопросы не слишком часто задаются на форумах и получают не так уж много ответов. Роб Шелдон отвечает на эти, вызывающие смущение в профессиональных кругах,...
5 июня
Как устроены функции и хранимые процедуры и как их применять для повторного использования запросов. SQL, как и любой другой язык программирования, предоставляет функции и хранимые процедуры. В этой статье мы рассмотрим функции и хранимые процедуры в PostgreSQL, а также будут...
Mar 24
Author: Mohammad Hussain
Introduction A common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement in SQL Server. CTEs were introduced in SQL Server 2005 and are similar to derived tables...
Написать сообщение
Тип
Почта
Имя
*Сообщение