Oracle Query Basics Exercise ____oracle

1 Select all information about the employee in Department 30
2 List the number of employees whose position is (MANAGER), name
3 Find bonuses above the salary of employees
4 Find the sum of each employee's bonus and salary
5 Find the manager in department 10 and the General Staff in department 20 (clerk)
6 Find out department 10 is neither manager nor ordinary staff, and salary is greater than 2000 of employees
7 Find out the different jobs of employees with bonuses
8 find no bonuses or bonuses less than 500 of employees
9 Display the name of the employee, according to the length of service, the oldest employees in the front

10 The average wage of >500 employees under each department,
11. Departments with average wages above 500 in each department
12) to calculate the department 30 of the most bonuses in the employee bonuses
13 The name of the employee who gets the most bonuses in department 30
14 figure out the number of employees and the minimum wage for each position
15) List the number of employees in each department in the employee table and Department No
16. Information on employees who are paid more than the average wage in their department
17 group statistics The average bonus for each position (also counted as a bonus) and total salary (including bonuses) under each department

Code:

1, select*from dept where deptno = 30;
2, select Empno, ename from emp where job= ' MANAGER ';
3. Select ename from emp where comm>sal;
4, select ENAME,SAL*13+NVL (comm,0) *13 "annual Bonus and Payroll sum" from EMP;
5. Select ename from emp where (deptno=10 and job= ' MANAGER ') or (deptno=20 and job= ' clerk ');
6. Select ename from the EMP where (deptno=10 and job not in (' MANAGER ', ' Clerk ') and sal>=2000);
7. Select DISTINCT job from EMP where deptno>0;
8. Select ename from emp where comm<500 or comm=0;
9. Select Ename,hiredate from EMP (hiredate);
10, Select avg (SAL) from the EMP where sal>500 group by DEPTNO;
11, select Deptno,avg (SAL) from the EMP GROUP by DEPTNO have avg (SAL) >500;
12, select MAX (comm) from emp where deptno = 30;
13. Select ename from emp where comm = (select Max (comm) from emp where deptno = 30);
14, select Job,count (*), Min (sal) from the EMP group by job;
15, select COUNT (*), DEPTNO from EMP Group by DEPTNO;
16, select * FROM EMP E1, (select Deptno,avg (SAL) as avgsal to EMP Group by DEPTNO) E2
where E1.deptno=e2.deptno and e1.sal > e2.avgsal;

17, select Deptno,job,avg (NVL (comm,0)), Sum (SAL+NVL (comm,0)) from the EMP group by Deptno,job;