Nested Queries

Question 1

What is a subquery in SQL?

Answer 1

A subquery, also known as a nested query, is a SQL query embedded within another SQL query. It is used to retrieve data from one or more tables and is enclosed within parentheses.

Question 2

What are the advantages of using subqueries?

Answer 2

The advantages include:
- Complexity Management: Breaking down complex problems into simpler parts.
- Reusability: Subqueries can be reused in different parts of the outer query.
- Logical Grouping: Allows logical grouping of data retrieval in a single query.

Question 3

What are the disadvantages of using subqueries?

Answer 3

The disadvantages include:
- Performance Issues: Can lead to performance degradation if not optimized.
- Readability: Can become difficult to read and maintain.
- Limited Flexibility: Less flexible than JOIN operations in some cases.

Question 4

What is the difference between a subquery and a JOIN?

Answer 4

A subquery is a query nested inside another query, ideal for single-value returns. A JOIN clause combines rows from two or more tables based on a related column and is generally faster for straightforward data retrieval.

Question 5

When is it better to use a subquery instead of a JOIN?

Answer 5

Use a subquery for single-value calculations or conditions dependent on the result of another query. Use JOINs for direct data correlations and operations involving multiple tables.

Question 6

Provide an example of a simple subquery.

Answer 6

SELECT col_a, col_b
FROM table_1
WHERE col_b > (
SELECT AVG(col_c) FROM table_2
);

Question 7

What is the execution order of a subquery?

Answer 7

1. Execute the subquery to get the result.
2. Use the subquery’s result in the outer query condition.
3. Execute the outer query with the condition applied.
4. Return the final result set.

Question 8

Write a query to find staff members who earn more than the average salary.

Answer 8

SELECT staff_name, staff_salary
FROM staff
WHERE staff_salary > (
SELECT AVG(staff_salary) FROM staff
);

Question 9

Write a query to find projects with a budget higher than the IT department’s average projects budget.

Answer 9

SELECT project_name, budget
FROM project
WHERE dept_id = (SELECT dept_id FROM department WHERE dept_name = ‘IT’)
AND budget > (
SELECT AVG(budget) FROM project
WHERE dept_id = (SELECT dept_id FROM department WHERE dept_name = ‘IT’)
);

Question 10

When should you test both subqueries and JOINs for performance?

Answer 10

Test both methods if performance is a concern, as SQL engines optimize differently based on the query structure and database schema.

Question 11

What should you consider when choosing between subqueries and JOINs for readability and maintenance?

Answer 11

Use the approach that makes the SQL easier to understand and maintain, especially in the context of practical sessions or company policies.