Can you explain what a nested query in SQL is and how it is used?

A subquery in SQL is a query that is nested inside another query. It is used to retrieve data that is then used in the outer query.

For example, if you want to select all employees whose salary is above the average salary in the company, you can use a subquery:

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Here, the inner query (SELECT AVG(salary) FROM employees) calculates the average salary, and the outer query selects employees with a salary higher than this value.

Nested queries help break down complex tasks into parts and make SQL queries more flexible and powerful.