Oracle Chapter II exercises

oracle chapter ii exercises

Oracle The first 2 Chapter exercise questions and answers

1. Please read from the table EMP The type of work found in is employee CLERK Or the manager MANAGER Of employees 、 Wages .
select ename,sal from emp where job=‘CLERK’ or job=‘MANAGER’;

2. Please be there. EMP Find the department number in the table 10-30 The name of the employee between 、 Department number 、 Wages 、 Work .
select ename,deptno,sal,job from emp where deptno between 10 and 30;

3. Please read from the table EMP Find a name in to J The names of all employees at the beginning 、 Wages 、 Position .
select ename,sal,job from emp where ename like ‘J%’;

4. Please read from the table EMP Find salary lower than 2000 Name of employee 、 Work 、 Wages , And in descending order of wages .
select ename,job,sal from emp where sal<=2000 order by sal desc;

5. Please check from the table that the work is CLERK The names of the owners of the 、 Wages 、 Department number 、 Information about department name and department address .
select ename,sal,emp.deptno,dname,loc from emp,dept
where emp.deptno=dept.deptno and job=’CLERK’;

6. In the table EMP Query all wages higher than JONES Name of all employees 、 Work and wages .
select ename,job,sal from emp where sal>(select sal from emp where ename=’JONES’);

7. List the names of all employees without corresponding department table information 、 Job and department number .
select ename,job,deptno from emp where deptno not in (select deptno from dept);

8. Find salary in 1000~3000 All personnel information of the Department where the employees are in between
select * from emp where deptno in (select distinct deptno from emp where sal between 1000 and 3000);

9. Who is the highest paid employee .
select ename from emp where sal=(select max(sal) from emp);
select ename from (select * from emp order by sal desc) where rownum<=1;

10. Check the names of all employees 、SAL And COMM The sum of the .
select ename,sal+nvl(comm,0) “sal-and-comm” from emp;

11. Query all 81 year 7 month 1 The name of the employee who came before the day 、 Wages 、 The name of the Department
select ename,sal,dname from emp,dept where emp.deptno=dept.deptno and hiredate<=to_date(‘1981-07-01’,’yyyy-mm-dd’);

12. Inquire about... In each department 81 year 1 month 1 Number of employees since the day
select deptno,count(*) from emp where hiredate>=to_date(‘1981-01-01’,’yyyy-mm-dd’) group by deptno;

13. Query all in CHICAGO The manager of the job MANAGER And the salesperson SALESMAN The name of 、 Wages
select ename,sal from emp where (job=’MANAGER’ or job=’SALES’) and deptno in (select deptno from dept where loc=’CHICAGO’);

14. The company listed in the query has worked for more than 24 The list of employees in 2007
select ename from emp where hiredate<=add_months(sysdate,-288);

15. Query on 81 The total income of all employees in the company over the years (SAL and COMM)
select sum(sal+nvl(comm,0)) from emp where to_char(hiredate,’yyyy’)=’1981’;

16. The query shows the exact time when each employee joined the company , Press ×××× year ×× month ×× Japan Hour, minute and second display .
select ename,to_char(hiredate,‘yyyy-mm-dd hh24:mi:ss’) from emp;

17. Query the number of employees employed 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;

18. Query and list the Department name and department manager name of each department
select dname,ename from emp,dept where emp.deptno=dept.deptno and job=’MANAGER’;

19. Query the Department name with the highest average salary and the Department name with the lowest average salary
select dname from dept where deptno=(select deptno from (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 (select deptno from emp group by deptno order by avg(sal) desc ) where rownum<=1);

20. The query and employee number are 7521 The employee's closest name is the name of the employee who subsequently enters the company and the name of his / her department
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

copyright:author[qq_ forty-five million eight hundred and forty-nine thousand tw],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130509223143.html