Oracle operation instance

: All departments.
Select dept. deptno, dept. dname
From dept, emp
Where dept. deptno = emp. deptno
2. List all employees with higher salaries than SMITH.
Select * from emp
Where sal> (select sal from emp where ename = 'Smith ')
3. List the names of all employees and their immediate superiors.
Select yg. ename, sj. ename
From emp yg, emp sj
Where yg. mgr = sj. empno

4. list all employees whose employment date is earlier than their direct superiors.
Select yg. ename, sj. ename
From emp yg join emp sj on yg. mgr = sj. empno
Where yg. hiredate
5. List department names and employee information of these departments, and list departments without employees.
Select dept. deptno, dept. dname, emp. empno, emp. ename
From dept left join emp
On dept. deptno = emp. deptno
6. List the names of all "Clerks" (Clerks) and their department names.
Select * from dept, emp
Where dept. deptno = emp. deptno
And job = 'cler'
7. List jobs with a minimum salary of more than 1500.
Select job from emp
Group by job
Having min (sal) & gt; 1500

8. List the names of employees who work in the Department named "SALES" (SALES Department). If you do not know the Department Number of the SALES department.
Method 1:
Select emp. ename
From dept, emp
Where dept. deptno = emp. deptno
And dname = 'sales'
Method 2:
Select * from emp
Where deptno = (select deptno from dept where dname = 'sales ')
9. list all employees whose salaries are higher than the company's average salaries.
Select * from emp
Where sal> (select avg (sal) from emp)
Comparison: list all employees whose salaries are higher than the average salaries of the Department.
Method 1:
Select * from emp
Where a. sal> (select avg (sal) from emp B where B. deptno = a. deptno)
Disadvantage: low efficiency of related subqueries.
Method 2:
Select empno, ename, sal from
Emp a, (select avg (sal) avg_sal from emp B where B. deptno = a. deptno) B
Where a. deptno = B. deptno
And a. sal> B. avg_sal
10. list all employees engaged in the same job as SCOTT.
Select * from emp
Where job = (select job from emp where ename = 'Scott ')
11. List the names and salaries of all employees whose salaries are equal to the salaries of employees in department 30.
Select ename, sal from emp
Where sal = any (select sal from emp where deptno = 30)

12. List the names and salaries of employees whose salaries are higher than the salaries of all employees who work in the Department 30.
Method 1:
Select ename, sal from emp
Where sal> all (select sal from emp where deptno = 30)
Method 2:
Select ename, sal from emp
Where sal> (select max (sal) from emp where deptno = 30)
13. List the number of employees, average salaries, and average service life of each department.
Select deptno, avg (FIG (sysdate-hiredate)/365) as year
From emp
Group by deptno
-Reference: truncation. The entire function is used.
Select trunc (99.9) from dual;
Return
99
14. List the names, department names, and salaries of all employees.
Select d. dname, e. ename, e. sal
From dept d, emp e
Where d. deptno = e. deptno
Note: Each field is prefixed with a table, which is more efficient.
15. list detailed information about all departments and the number of departments.
Select dept. deptno, dept. dname, count (*)
From dept, emp
Where dept. deptno = emp. deptno
Group by dept. deptno, dept. dname

16. List the minimum wage for various jobs.
Select job, min (sal) from emp group by job
17. List the minimum salaries of managers in each department.
Select deptno, min (sal) from emp where job = 'manager' group by deptno

18. List the annual salary of all employees in descending order of annual salary.
Select empno, ename, sal * 12 as annual salary from emp
Order by sal
19. Ask employees with the lowest salaries.
Method 1:
Select * from emp
Where sal = (select min (sal) from emp B where B. job = a. job)
Method 2:
Select emp .*
From emp a, (select job, min (sal) min_sal from emp group by job) B
Where a. job = B. job and a. sal = B. min_sal
20. List the top 3 employees with various salaries
Select * from (
Select empno, ename, sal, job,
Dense_rank () over (partition by job order by sal desc) as ranking
From emp)
Where a. Ranking <= 2
Order by job;
Note: Oracle's powerful "partition ranking technology" is used. "dense_rank ()" is an Oracle parsing function.