Oracle table common query Experiment (2)

Oracle table common query Experiment (2) Common query experiment of Oracle table (1) http://www.bkjia.com/database/201309/242198.html

1. query the total names of all employees, SAL and COMM. Select ename, sal + nvl (comm, 0) "sal-and-comm" from emp; 2. query the names, salaries, and department names of all employees before January 1, July 1, select ename, sal, dname from emp, dept where emp. deptno = dept. deptno and hiredate> = to_date ('2017-07-01 ', 'yyyy-mm-dd'); 3. query the number of employees in each department after January 1, 1981 select deptno, count (*) from emp where hiredate> = to_date ('2017-01-01 ', 'yyyy-mm-dd ') group by deptno; 4. query the names, salaries, select ename, sal from emp where (job = 'Manager' or job = 'sales') and deptno in (select deptno from dept where loc = 'Chicago '); 5. select ename from emp where hiredate <= add_months (sysdate,-288); 6. query the total revenue of all employees of the Company in 81 years (SAL and COMM) select sum (sal + nvl (comm, 0) from emp where to_char (hiredate, 'yyyy ') = '000000'; 7. the query shows the exact time for each employee to join the company, which is displayed by ×××× years × months × days, hours, minutes, and seconds. Select ename, to_char (hiredate, 'yyyy-mm-dd hh24: mi: ss') from emp; 8. query the number of hired employees in various regions by year and month in the company. select to_char (hiredate, 'yyyy-mm'), loc, count (*) from emp, dept where emp. deptno = dept. deptno group by to_char (hiredate, 'yyyy-mm'), loc; 9. query and list the department names and department managers of each department. select dname, ename from emp, dept where emp. deptno = dept. deptno and job = 'manager'; 10. query the name of the Department with the highest average salary and the lowest Department name select dname from dept where deptno = (select deptno from emp group by deptno order by avg (sal )) where rownum <= 1) union all select dname from dept where deptno = (select deptno from emp group by deptno order by avg (sal) desc) where rownum <= 1); 11. query the name of the employee who enters the company and the name of the department where the employee number is 7521. select ename, dname from (select ename, deptno from (select ename, deptno from emp where hiredate> (select hiredate from emp where empno = 7521) order by hiredate) where rownum <= 1) e, dept where e. deptno = dept. deptno