Search  
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
Source:
Views:
1344

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
Jul 30
Author: Emer Kurbegovic
Azure Cosmos DB is a globally distributed, multi-model database service provided by Microsoft Azure. It supports multiple data models, including document, key-value, graph, and column-family data models. In this article, we’ll explore how to add and retrieve data from Azure...
Sep 10, 2023
Author: Shiva Ganesh
MSSQL and MySQL are two of the most popular relational database management systems in the world Nowadays, even when businesses are selling comparable products, rivalry between them is fairly widespread. In a market where there is competition, a company’s profitability...
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...
Jun 10
Author: Dayanand Thombare
LINQ (Language Integrated Query) has revolutionized the way we interact with data in C#. It offers a consistent, readable, and concise way to manipulate collections, databases, XML, and more. However, the beauty and ease of LINQ can sometimes mask performance...
Send message
Type
Email
Your name
*Message