oracle--Sub-query--bai

--1 CREATE VIEW Show_max_sal_name_view display the highest wage employee name and his salary create or replace view Show_max_sal_name_view asselect Ename,sal from SC Ott.emp where sal= (select Max (sal) from scott.emp), select * from show_max_sal_name_view;--2 CREATE VIEW Show_min_sal_dname show average work    The department name and average wage of the lowest-funded sector-the average wage by sector select DEPTNO,AVG (SAL) from the Scott.emp group by Deptno;    --Get the lowest average wage select min (avg (SAL)) from the Scott.emp group by Deptno;     --Get the minimum average wage and its department number select * FROM (select Deptno,avg (SAL) Average salary from Scott.emp Group by deptno) T where T. Average salary =    (Select min (avg (SAL)) from Scott.emp Group by Deptno); --Get the minimum average wage and its department name create a view created or replace view show_min_sal_dname as SELECT * FROM (select Deptno,avg (sal) Average salary fro    M scott.emp GROUP by deptno) T where T. Average salary = (select min (sal)) from Scott.emp Group by Deptno); --3 Create a View show_leader_name displays the names of all employees and their superior leader names. (difficult)--using the MGR field for the "self-correlating" select E1.ename as employee name of the EMP table, E2.ename as leader name from Scott.emp e1,scott.emp E2 where e1.mgr    =e2.empno  Union select Ename, ' no leader ' from Scott.emp where Mgr is null;===========================================================  ==============================================--replace empty with 0 update scott.emp Set comm = ' 0 ' where comm is null; --Identification of employee numbers lower than higher salary + bonus, employee name CREATE VIEW Show_higher_empee as SELECT * FROM (select E1.empno,e1.ename as name, E2.ename As leader's name, E1.SAL+NVL (E1.comm, ' 0 ') as my total income, E2.SAL+NVL (E2.comm, ' 0 ') as leader revenue from Scott.emp e1,scott.emp E2 where e1.mgr        =e2.empno) where I own total revenue > lead revenue; --Using Join connection SELECT * FROM (select E1.empno,e1.ename as my name, E2.ename as leader name, E1.SAL+NVL (e1.comm      , ' 0 ') as my total revenue, E2.SAL+NVL (E2.comm, ' 0 ') as leader revenue from Scott.emp E1 INNER join scott.emp E2 on E1.mgr=e2.empno          Where I own total revenue > lead revenue; SELECT * from Show_higher_empee;

　　

oracle--Sub-query--bai