Data Manipulation 6 - Complex Queries -

Question 1

What is a complex query in SQL?

Answer 1

A complex query involves multiple steps, conditions, or logical operations to manipulate or retrieve data, handling intricate data relationships, performing conditional logic, and aggregating data across multiple tables.

Question 2

What are some keywords and concepts used in constructing complex queries?

Answer 2

1. CASE
2. EXISTS
3. WINDOW FUNCTION
4. WITH (CTE)
5. RECURSIVE QUERY

Question 3

What is the syntax for a CASE statement in SQL?

Answer 3

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE resultN
END

Question 4

How does the EXISTS operator work in SQL?

Answer 4

EXISTS checks for the existence of any record in a subquery and returns true if the subquery finds one or more matching rows.

Question 5

What is a Common Table Expression (CTE) in SQL?

Answer 5

A CTE allows the creation of temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement, making complex queries more readable and modular.

Question 6

What is a window function in SQL?

Answer 6

A window function performs calculations across a set of table rows that are related to the current row, useful for running totals, moving averages, and other complex statistical calculations.

Question 7

How does a recursive query work in SQL?

Answer 7

Recursive queries handle hierarchical or tree-structured data by calling themselves repeatedly, processing rows in a loop until a certain condition is met, using the WITH RECURSIVE syntax.

Question 8

Provide an example of using the CASE statement to assign a salary category.

Answer 8

SELECT staff_name, staff_last_name, staff_salary,
CASE
WHEN staff_salary > 50000 THEN ‘High’
WHEN staff_salary BETWEEN 40000 AND 50000 THEN ‘Medium’
ELSE ‘Low’
END AS Salary_Category
FROM staff;

Question 9

How can you use EXISTS and CASE together in a query?

Answer 9

SELECT s.staff_name, d.dept_name,
CASE
WHEN EXISTS (
SELECT 1
FROM project p
WHERE p.dept_id = s.dept_id AND p.budget > 75000
) THEN ‘Involved in High-Budget Project’
ELSE ‘Not Involved in High-Budget Project’
END AS Project_involvement
FROM staff s
JOIN department d ON s.dept_id = d.dept_id;

Question 10

What is the advantage of using CTEs in SQL?

Answer 10

CTEs improve query readability and modularity by breaking down complex queries into simpler parts, which can make maintenance and debugging easier.

Question 11

Provide an example of a recursive query in SQL.

Answer 11

WITH RECURSIVE ManagerHierarchy AS (
SELECT s.staff_id, s.staff_name, s.manager_id, 1 AS level
FROM staff s
WHERE s.manager_id IS NULL
UNION ALL
SELECT s.staff_id, s.staff_name, s.manager_id, mh.level + 1
FROM staff s
JOIN ManagerHierarchy mh ON s.manager_id = mh.staff_id
)
SELECT mh.staff_id, mh.staff_name, m.staff_name AS manager_name, mh.level
FROM ManagerHierarchy mh
LEFT JOIN staff m ON mh.manager_id = m.staff_id
ORDER BY mh.level, mh.staff_id;

Question 12

What is a window function’s OVER clause used for?

Answer 12

The OVER clause specifies how to partition the data, allowing calculations across sets of rows related to the current query row without collapsing the result set.

Question 13

How does PostgreSQL handle CTEs in terms of optimization?

Answer 13

PostgreSQL historically treated CTEs as optimization fences, executing them separately from the main query. From version 12 onwards, the planner can inline CTEs when possible, reducing the performance difference compared to subqueries.

Question 14

What are the performance considerations when choosing between CTEs and subqueries?

Answer 14

1. Readability vs. Execution Plan
2. Optimization capabilities
3. Materialization overhead
4. Specific use case and PostgreSQL version