sql 문제풀이

1. write a query that returns the name and salary (in this column order) of each professor who is either a top 3 or bottom 3 earner(include ties)

select professor_name, salary from salaries 
where 1=1 
and salary<=(
select salary 
from (select 
salary, 
rank() over(order by salary desc) as rnk)
from salaries) 
where rnk=3) or 
salary<=(select 
salary,
rank() over(order by salary asc) as rnk from salaries) 
where rnk=3);

*offset 지정한 개수만큼 건너뛰기 limit뒤 -순서 
offset 1000rows 1000개수만큼 건너뛰고 fetch next 10 rows only 다음 10개 행만 출력하기로 사용가능 

2. write a query that returns every department and the number of professors in each one (in this column order)

select department count(professor_name) as professors from salaries group by department

3. write a query that returns the department with the most professors and thet amount(in this column order).

select department,
professor from(
select department ,
count(professor_name) as professor from salaries
group by department) sub order by sub.professor desc limit 1;