Oracle Complex query tutorial

Select comm from emp;
-- Query the employees with the highest salaries
Select ename, job, sal from emp where sal = (select max (sal) from emp );
Select ename, job, sal * 13 + nvl (comm, 0) * 13 s from emp order by s desc;
-- Query average salary and total salary
Select avg (nvl (sal, 0), sum (nvl (sal, 0) from emp;
-- Query prize money
Select avg (comm), sum (comm) from emp;
-- Group by and having
Select avg (sal), sum (sal), deptno from emp group by deptno;
Select count (comm) from emp;
Select count (ename) from emp;
---- Display the average and maximum salaries for each position in each department
Select avg (sal), sum (sal), deptno, job from emp group by deptno, job
---- Show the average salary of a department lower than 2000 and his average salary
-- Idea 1: query the average salary of each department
Select avg (sal), deptno from emp GROUP by deptno;
-- Idea 2: Pick out departments lower than 2000
Select avg (sal), deptno from emp GROUP by deptno having avg (sal) <5000;
---- 1, the grouping function (avg (), sum () ......) can only appear in the selection list, having, order by clause
---- 2. If the select statement contains group by, having, and order by statements, the order is group by, having, and order.
---- 3. If there are columns, expressions, and grouping functions in the selection column, these columns and expressions must have one in the group by clause.