Complex query of a single Oracle table

Complex query of a single Oracle table

select avg(sal),max(sal),deptnofrom empgroupby deptno;

 

select avg(sal),max(sal),deptnofrom empgroupby deptnohavingavg(sal)>2000orderby deptno;

 

Query employees whose salaries are higher than 500 or whose positions are MANAGER and whose first letter is uppercase J? Select * from empwhere (sal> 500or job = 'manager') and enamelike 'J % '; select * from emporderby deptnoasc, saldesc; select (sal + nvl (comm, 0) * 12 assum, enamefrom emporderbysum; Calculate the highest wage and minimum wage selectmax (sal), min (sal) from emp; query the name of the highest-wage employee. select ename, salfrom empwhere (selectmax (sal) from emp) = sal; select * from empwhere sal> (selectavg (sal) from emp); group by and having clause group by are used for queries. The having clause is used to limit the display results of a group. How do I display the average and maximum salaries of each department selectavg (sal), max (sal), deptnofrom empgroupby deptno; display the average salary and minimum wage of each position in each department? Selectavg (sal), max (sal), min (sal), deptno, jobfrom empgroupby deptno, job; shows that the average salary is less than 2000 of the Department number and its average salary? Selectavg (sal), max (sal), deptnofrom empgroupby deptnohavingavg (sal)> 2000 orderby deptno;

 

Summary of Data grouping: 1 The grouping function can only appear in the selection list, having, order by clause (cannot appear in where) 2. If the select statement contains group, having, order by then their order is group by, having, order by3 in the selection column if there are columns, expressions and grouping functions, these columns and expressions must appear in the group by clause. Otherwise, an error occurs. For example

SELECT deptno, AVG(sal), MAX(sal) FROM emp GROUP by deptno HAVING AVG(sal) < 2000;