select emp_name,salary from employee where salary >(select avg(salary) from employee);

here we are using 2 query to get a query

1. scalar subquery

A scalar subquery is a subquery used in a context where a single value is expected, like in the SELECT, WHERE, or SET clause.

select avg(salary) from employee

or

SELECT MAX(salary) FROM employees AS highest_salary

2. Multiple row subquery

--subquery which returns multiple column and multiple rows
--subquery which returns one 1 row multiple column or 1 column multiple row

mysql> select * from employee;
+--------+----------+----------+---------+------------+
| emp_id | emp_name | salary   | dept_id | manager_id |
+--------+----------+----------+---------+------------+
|    101 | Alice    | 60000.00 |       1 |       NULL |
|    102 | Bob      | 55000.00 |       1 |        101 |
|    103 | Charlie  | 90000.00 |       2 |       NULL |
|    104 | David    | 75000.00 |       2 |        103 |
|    105 | Eve      | 72000.00 |       2 |        103 |
|    106 | Frank    | 50000.00 |       3 |       NULL |
|    107 | Grace    | 48000.00 |       3 |        106 |
|    108 | Heidi    | 67000.00 |       4 |       NULL |
|    109 | Ivan     | 52000.00 |       4 |        108 |
+--------+----------+----------+---------+------------+
9 rows in set (0.01 sec)

mysql> select * from department;
+---------+-------------+
| dept_id | dept_name   |
+---------+-------------+
|       1 | HR          |
|       2 | Engineering |
|       3 | Marketing   |
|       4 | Sales       |
|       5 | cs          |
+---------+-------------+
5 rows in set (0.00 sec)

Find max salary and name of the employee from each dept

select e.emp_name,e.salary,e.dept_id from employee e
join (select max(salary) as sal ,dept_id from employee group by dept_id) as maxs
on e.dept_id=maxs.dept_id and e.salary=maxs.sal
limit 4;

Find the department who dosent have any employees;

here we know its cs department
so
always go one query at a time so first find the the depart who has employees from emp table after then find distinct of them and then in the main query do not in

select * from department where dept_id not in(select distinct(dept_id) from employee);

3. correlated subquery

its a subquery which is related to outerquery and cannot be runned standalone