select *
from employee
where salary > (select avg(salary) from employee);


select e.*
from employee e
join (select avg(salary) sal from employee) avg_sal
on e.salary > avg_sal.sal


/* Find the employees who earn the highest salary in each department */

select *
from employee
where (dept_name, salary) in (
	select dept_name, max(salary)
	from employee
	group by dept_name
)


/* Find the average price of each hotel (name, price). Order by price */

select t2.name, t1.avg_price from

(select hrooms.hid, round(avg(hrooms.price)) as avg_price
from hrooms group by hid order by avg_price desc) AS t1

join

(select hid, name from hotel) AS t2
on t1.hid=t2.hid



union
union all
except
intersect