Oracle's SQL statement training

--Query The department number of the highest average salary department
Select Deptno,avg_sal from
(select Deptno,avg (SAL) avg_sal from EMP Group by Deptno)-as a table, no table can be
where avg_sal =
(select Max (avg_sal) from (select Deptno,avg (SAL) avg_sal from EMP Group by DEPTNO))--as a value;
--Query The department name of the highest average salary department
Select Dname from dept where deptno=
(select Deptno from--as a value
(select Deptno,avg (SAL) avg_sal from EMP Group by DEPTNO)
where avg_sal =
(SELECT MAX (avg (SAL)) from the EMP Group by DEPTNO); --nesting of group functions, up to two layers, this is the two layers (())

--Check the average salary level of the Department
Select Deptno, Avg_sal, Grade from (select Deptno,avg (SAL) avg_sal from EMP Group by DEPTNO) T
Join Salgrade on avg_sal between Losal and Hisal;

--Query the name of the department with the lowest average salary level--use view to simplify program code
Select Dname from dept where deptno=
(Select Deptno from
(select Deptno, Avg_sal, Grade from (select Deptno,avg (SAL) avg_sal from EMP Group by DEPTNO) T
Join Salgrade on avg_sal between Losal and Hisal))
where grade=
(select min (grade) from
(select Deptno, Avg_sal, Grade from (select Deptno,avg (SAL) avg_sal from EMP Group by DEPTNO) T
Join Salgrade on avg_sal between Losal and Hisal));

--Simplify code with views
Create View My_view as (select Deptno, Avg_sal, Grade from (select Deptno,avg (SAL) avg_sal from EMP Group by DEPTNO) T
Join Salgrade on avg_sal between Losal and Hisal);
--Create a view to authorize the Scott user with the SYS user
After the user logs on successfully with SYS,
Grant create table,create view to Scott;
After the authorization is successful, you can create a view using Scott's ordinary users;
--Use a view to simplify the above code
Select Dname from dept where deptno=
(Select Deptno from
(My_view)
where grade=
(select min (grade) from
My_view));
--The name of the manager who asks for a higher salary than the average employee
Ordinary staff: That is not MGR this inside is the ordinary staff
Manager: In Mgr this is the manager.
--the highest salary for the average employee
Select Max (SAL) from the EMP where empno not in (select distinct MGR from EMP where Mgr are NOT null);--For a null value to be treated like this

--The name of the manager who is higher than the highest wage.
Select ename from emp where empno in (select distinct MGR from EMP where MGR is not NULL)
> Sal
(select Max (SAL) from the EMP where empno not in (select distinct MGR from EMP where Mgr are NOT null));

--The department name of the department manager with the lowest average salary
Analysis:
Who are the managers in which departments, and what is the salary of the managers?
Select Dname from Dept D join (
Select Deptno,avg (SAL) avg_sal from (select Ename,sal,deptno from emp where empno on (select Mgr from EMP)) GROUP by DEPTN O) s
On D.deptno =s.deptno
where avg_sal=
(select min (avg_sal) from (select Deptno,avg (SAL) avg_sal from (select Ename,sal,deptno from emp where empno in (select Mgr From EMP)) (Group by Deptno));

-The top 5 employees with the highest pay
Select Ename,sal from (select Ename,sal from emp order BY sal Desc) where RowNum <=5;

MySQL paging is limit a A, and Oracle's paging is special, no limit is only rownum, this is a pseudo-field that is not displayed. This field can only use less than or less than or equal, cannot use greater than or greater than equals.
If pagination can only be queried first rownum this column select RowNum from EMP, and then from here rownum> or < which number.
Select RowNum, ename from (select RowNum No from emp) T where no>3 and no<10;

--The 6th to 10th employee of the highest salary
Select Ename,sal from (select Ename,sal,rownum r from (select Ename,sal from emp order by sal Desc)) where r>=6 and r& lt;=10;

--For the last 5 employees (in descending order of entry time, the first five can be taken out)

Select Ename,hiredate,rownum from (select Ename,hiredate from emp order BY hiredate DESC) where rownum<=5;

--Comparative efficiency
SELECT * from emp where deptno=10 and ename like '%a% '-this is much more efficient, short-circuiting with Java and similar
SELECT * from emp where ename like '%a% and deptno=10

Oracle's SQL statement training