Subqueries in WHERE Clause:

You can use a subquery within a WHERE clause to filter rows based on the result of the subquery.

                                  
SELECT column1, column2
FROM table1
WHERE column1 IN (SELECT column1 FROM table2 WHERE condition);

                                  
                                

Subqueries in HAVING Clause:

Subqueries in the HAVING clause are similar to those in the WHERE clause but are applied after the GROUP BY operation.

                                  
SELECT column1, COUNT(*)
FROM table1
GROUP BY column1
HAVING COUNT(*) > (SELECT AVG(column2) FROM table2);

                                  
                                

Subqueries in SELECT Clause:

You can use subqueries to calculate a value to be returned in the SELECT statement.

                                  
SELECT column1, (SELECT MAX(column2) FROM table2) AS max_value
FROM table1;

                                  
                                

Writing Subqueries:

Subqueries can either be simple, like in the case of simple queries, or complex, depending on your unique query requirements. It can include multiple ANDs and ORs while performing different tasks through operational and functional parts.

For example, you might use a subquery to find all employees whose salary is above the average salary of their department:For example, you might use a subquery to find all employees whose salary is above the average salary of their department:

                                
SELECT employee_id, name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id);
                                   
                                
                              

This query this employee ID, name as well as salary from the employees table, restricting people to those employees whose salary is bigger, than the average salary of the department they are in.