A step-by-step guide to SQL subqueries with examples for solving complex queries effectively.

6.How do you write a subquery to get a single value?
A single value can be returned using a subquery in a place where a scalar result is expected.
for example.
SELECT name FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);
The query above will return the employee's name with the maximum salary because the subquery is determining the maximum salary.

7.How would you return multiple values in a subquery?
You can return more than one value with a subquery if you make use of operators like IN or EXISTS.
Example.
SELECT name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');
This will use a subquery returning a list of department IDs and then return the employees working in those departments using an outer query.

8.How do you handle subqueries that return multiple rows?
If a subquery returns multiple rows, you must use an operator like IN, ANY or EXISTS in the outer query to handle the results.
Example.
SELECT name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = ‘New York’);
This is the query used to find the names of employees who work in departments located in New York.
This would start with a subquery using a check in the departments table to identify all department_id where the location is "New York." Then it would feed those department IDs into the main query, which looks at the employees table and selects names of those employees who have a department_id corresponding to one of those found by the subquery.
This will bring to the result only those employees who are associated with departments based in New York.

9.How do you use correlated subqueries?
A correlated subquery is a subquery that depends on the outer query for its values. It is executed repeatedly, once for each row processed by the outer query. The subquery uses values from the current row of the outer query, thus linking the two.
Example.
SELECT e.name
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);
This query selects all the employees who have higher salary than the average of all the employees of his respective department. First it determines the average salary for every department. It does this by checking the employees table. It then compares the salary of every employee to the average of that employee's department. If an employee's salary is higher than the average, then their name will be included in the results. In other words, the query will give a list of names of employees who are the top earners in their own departments.

10.How do you use a subquery with IN to filter results?
You can use a subquery with IN to filter results based on the column's match with the values returned by the subquery.
Example.
SELECT name FROM employees WHERE department_id
IN
(SELECT department_id FROM departments WHERE location = 'California');
This query seeks to find the names of employees working in departments based in California. First, it looks for departments in California and retrieves the department_id. Then it fetches the names of employees whose department matches any of the department_id values from the first step.

11.How do you use a subquery with EXISTS to test for the existence of rows?
The EXISTS operator checks if a subquery returns at least one row. It returns TRUE if the subquery finds at least one row. Otherwise, it returns FALSE.
Example:
SELECT name FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id);
It calls up the names of those employees who have a valid department. This checks to ensure if the employee's department_id appears within the departments table so if he is a valid department, the list will be added in this result. In short words, it makes sure only those employees who have some real department will appear.

12.How can you use NOT EXISTS with a subquery?
The operator NOT EXISTS is applied in filtering if subquery returns no rows.
For Example:
SELECT e.name FROM employees e WHERE NOT EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id);
This returns names of employees those who have not been assigned with any department. Here, the use of the condition NOT EXISTS for each employee if there is department_id available from the departments table, for those employees also but if it has found the departments for that employees not, it comes their names in the results. That is to say, this search returns all staff who do not have a valid assigned department.

13.How would you use a subquery with aggregate functions such as AVG(), COUNT()?
You can use a subquery with aggregate functions to perform operations such as calculating the average or count of rows based on some criteria.
SELECT department_id FROM employees
WHERE
salary> (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id);
This return shows that department_id of departments has lesser average salaries of employees that earn more than at least one within that department. This means the subquery, having computed the average of every department the outer query makes a comparison about each employee's salary by using their respective department averages. This is, if an employee's salary exceeds the average the department_id of that employee will appear in the results. That is to say, it marks those departments whose employees' salary surpasses their departmental average salary.

14.How do you use a subquery in an INSERT statement?
You can use a subquery within the INSERT statement when you want to insert results produced by a query into the table.
for example.
INSERT INTO high_salary_employees (name, salary)
SELECT name, salary FROM employees
WHERE
salary> (SELECT AVG(salary)FROMemployees);
This statement will make insertion into the high_salary_employees table of names and their salaries of employees whose compensation is greater than the mean compensation of all employees.
This subquery calculates the average salary of all employees in the employees table. The main query then picks up the name and salary of those employees whose salary exceeds this average and pushes this data into the high_salary_employees table.
It really determines which employees earn more than the average the details of such employees are then moved into a separate table of high-salary employees.

15.How do you use a subquery in an UPDATE statement?
You can use a subquery in the SET clause of an UPDATE statement if you want to update the rows based on values fetched from another table.
Consider the following example:
UPDATE employees SET salary =
(SELECT MAX(salary) FROM employees WHERE department_id = employees.department_id)
WHERE department_id = 5;
This statement would update the salaries of all employees in department 5 to the maximum salary within his or her respective department.
The subquery determines the maximum salary, MAX(salary) of every department. Then the outer query takes that value and applies it to update the salaries of all employees working in department 5. The query is updating each employee's salary to equal the maximum salary that exists within his or her department, but it does so only for employees working in department 5.
Simply, it makes sure every employee in department 5 has their salary upgraded to that of the highest employee within that department.

16.How do you use a subquery in a DELETE statement?
You can use a subquery within a 'DELETE' statement in order to delete rows based on conditions determined by the subquery.
Example:
DELETE FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'HR');
Delete all records from the employees table for employee associated with 'HR' department. This will be done starting with an inner query in which there will be a department_id brought back from a departments table, where department_name is set to equal 'HR' and in the outer query will delete all employees having a match in the departments_id which is brought into the field of comparison by an inner SELECT. In simple words, this query removes all employees working in the HR department hence deleting from the system all those employees linked to that department only.

17.How would you use a subquery to find records that don't exist in another table?
You can use a subquery with NOT IN or NOT EXISTS to find records in one table that don't have corresponding entries in another table.
SELECT name FROM employees WHERE department_id NOT IN (SELECT department_id FROM departments);

This query brings names of employees whose department_id in the employees table doesn't exist in the departments table.
This subquery returns all the departments table's values for the field department_id. Then it checks these values within the outer query against the column of department_id in the employees table. This outer query then retrieves his name when no such department_id of the employees is present in the subquery's list.
Simpler words, it is finding employees who don't have a department_id found in the departments table.

18.How do you use ANY and ALL with subqueries?
ANY and ALL are used with subqueries to compare values in the outer query with a list of values returned by the subquery.
Example with ANY:
SELECT name FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 2);
This query fetches the names of employees whose salaries exceed that of any of the employees in department 2.
The subquery retrieves all the salaries of the employees who work in department 2. Then the outer query compares each employee's salary with the list of salaries returned by the subquery. If the salary of an employee is higher than at least one of them, his name will appear in the result.
In other words, the query will retrieve all those employees whose salaries are more than that of some employees working in department 2.

Example with ALL:
SELECT name FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 2);
It brings back the names of all employees whose salaries exceed those of all the employees who work in department 2.
A subquery gets a list of the salaries of the employees of department 2 and then the outer query runs a loop through every employee compared with this list. It outputs only those employees whose salary was greater than the maximum in the list of salaries in the department 2.
More explicitly, it is asking for workers who earn more than all the people in department 2 earn. It selects only the workers who earn more than every worker in department 2.

19.How do you optimize queries that contain subqueries?

Optimizing queries with subqueries is strategies to make their execution time and efficiency better.
Here are some key approaches:
Convert Subqueries to Joins: Wherever possible, replace correlated subqueries with JOINs. JOINs are usually faster because they allow the database to process data in one pass rather than repeatedly executing a subquery.
Use EXISTS or IN Wisely: For correlated subqueries, use EXISTS or IN depending on the size of the data set. EXISTS usually performs better on a larger data set while IN is much better on smaller ones.
Avoid Repeated Calculations: Move repeated subquery results into a common table expression, CTE or a temporary table to avoid repeated calculation
Optimize Subquery Filters: Place efficient filters and indexes on subqueries in such a manner that a minimum number of data should be passed through for processing.
Analyzing execution plans. Execution plans will indicate areas that you will have to examine and adjust so that you can minimize

20.How would you use subqueries for data validation or checks before updates?
You could use subqueries to enforce data validation within your queries to ensure certain conditions must be met before an update can occur.
For example, you could validate that a new salary is greater than the average salary in a department before updating an employee's salary:

UPDATE Employees
SET salary = 60000
WHERE employee_id = 101
AND salary < (SELECT AVG(salary) FROM employees WHERE department_id = 5);
This is a query to update the salary of an employee under certain conditions. This compares the salary of employee_id = 101 against the average salaries of his or her department 5 colleagues to determine if it is less and if it is, it will update their salary to 60000. Consider if their actual salary is 55,000 and the average of this employee's fellow department 5 colleagues was 58,000. Because his or her salary was below average, the query bumped his or her salary to be equal to the amount being proposed. If his or her salary is already over the average, nothing happens. Such adjustments are made in an intelligent and fair manner based on how the employee's pay is in comparison to others in their department.

21.How do you combine two or more subqueries in a single query?
You can join two or more subqueries in one query by using logical operators such as AND, OR in the WHERE clause. Or you can place a different subquery in any other part of the whole query. for example: SELECT, FROM, HAVING.
Example:
SELECT name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales')
AND salary > (SELECT AVG(salary) FROM employees WHERE department_id = 5);

This query selects the names of employees whose work department is 'Sales' and who have a salary greater than the average salary of those employees in department 5. The query first determines which is the department labeled as 'Sales' by looking up in the departments table to get the ID of that department and then it looks into the employees table to find which are the workers in that department. Finally, it calculates the average salary in department 5 and compares them to those in the employee group of 'Sales'.

22.In what way do you use subqueries to resolve NULL related problems in queries?
Subqueries may also handle NULL values. You may eliminate rows containing NULL or you may replace the NULL with a default value. For instance, you would make use of a subquery to determine whether it is NULL and then replace that value with a default:
SELECT name,
(SELECT COALESCE(salary, 0) FROM employees WHERE employee_id = e.employee_id) AS salary
FROM employees e
This query fetches names of all employees and salaries for the same, with provision to take care of any missing salary. It checks for every employee on his salary and in the event of no recorded salary that is when the salary is NULL it substitutes by using COALESCE a value of 0 to the NULL. This means that all employees will have a salary value be it only a placeholder of 0. It is a good way of handling incomplete salary data while still producing an evident and complete list of names of employees along with their corresponding salaries.

23.How a subquery is used to compare data in two or more tables?
A subquery may be used for comparing information of two or more tables on a condition basis either on keys or related information. In fact, let us go find some employees who have the highest income and yet this income is lower than his department average:
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id);
This statement will fetch the names and salaries of the employees whose salary is greater than the average salary of the respective department. It does so by comparing the salary of every employee with the average salary of all employees belonging to the same department that has been computed through a subquery. If an employee's salary is greater than this departmental average then their name and salary appear in the results. This query is particularly useful in identifying top earners in each department which is helpful in pointing out people who stand out in relation to earnings compared to their peer group.