Oracle multi-Table query instance

Oracle multi-Table query is a common query method. The following lists some oracle multi-Table query examples, we hope that you will have a better understanding of oracle multi-table queries.

1. List all departments with at least one employee.
Select deptno, dname, loc from dept
Where deptno in (select deptno from emp );
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 a. ename, B. ename mgname from emp a, emp B
Where a. mgr = B. empno;
4. list all employees whose employment date is earlier than their direct superiors.
Select * from emp a, emp B
Where a. mgr = B. empno and a. hiredate <B. hiredate;

Select a. hiredate, B. hiredate from emp a, emp B
Where a. mgr = B. empno and a. hiredate <B. hiredate;
5. List department names and employee information of these departments, and list departments without employees.
Select dname, emp. * from emp, dept
Where emp. deptno (+) = dept. deptno;
6. List the names and department names of all "CLERK" clerks.
Select ename, dname from emp, dept
Where emp. deptno = dept. deptno and emp. job = 'cler ';
7. List jobs with a minimum salary of more than 1500.
Select job from emp
Group by job
Having min (sal)> 1500;
8. List the names of employees working in the Department SALES department. If you do not know the Department Number of the SALES department.
Select ename from (select deptno from dept where dname = 'sales') a, emp
Where a. deptno = emp. deptno;
9. list all employees whose salaries are higher than the company's average salaries.
Select emp. * from emp
Where sal> (select avg (sal) from emp );
10. list all employees engaged in the same job as SCOTT.
Select emp. * from emp
Where job in (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 in (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.
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.
14. List the names, department names, and salaries of all employees.
Select ename, dname, (nvl (comm, 0) + sal) from emp, dept
Where emp. deptno = dept. deptno;
15. List a combination of employees engaged in the same job but belonging to different departments.
Select a. * from emp a, emp B
Where a. job = B. job and a. deptno <> B. deptno;
16. list detailed information about all departments and the number of departments.
Select dept. *, (select count (*) from emp where dept. deptno = emp. deptno) as pop from dept;
17. List the minimum wage for various jobs.
Select job, min (nvl (comm, 0) + sal) from emp
Group by job;
18. List the minimum salaries of managers in each department.
Select min (sal) from emp
Where job = 'manager'
Group by deptno;
19. List the annual salary of all employees in descending order of annual salary.
Select ename, (nvl (comm, 0) + sal) * 12 a from emp
Order by a asc;

The preceding section describes oracle multi-Table query instances.

ORACLE Database Encoding

Use the Oracle to_date () function

Oracle memory structure-SGA

Learn about the ORACLE resource role

Oracle data export and import Permissions