1.1) select max(e.sal), min(e.sal) from emp e group by e.deptno;
1.2) select max(e.sal), min(e.sal) from emp e
where e.job = 'CLERK' group by e.deptno;
1.3) select e.ename, e,deptno, e.sal from emp
order by e.deptno desc , e.sal asc
2.1) select s.stuid, s.name, sum(s.core) total_score
from stuscore s group by s.stuid ,s.name order by total_score desc
2.2) select s1.subject, s2.* from stuscore s1
join (select s.stuid, s.name, max(s.score) max_score
from stuscore s group by s.stuid, s.name) s2
on s1.score = s2.max_score and s1.stuid = s2.stuid
2.3) select avg(s.score) avgscore ,s.stuid,s.name
from stuscore s group by s.stuid,s.name
2.4) select *from (SELECT s.name,s.stuid,s.subject,s.score,
row_number() OVER(PARTITION BY s.subject ORDER BY s.score desc) e FROM stuscore s)
t where e <= 2
