Oracle multi-Table query, oracle

Oracle multi-Table query, oracle

To query multiple tables, we must first avoid Cartesian sets. To avoid Cartesian sets, the query conditions should not be less than the number of tables-1.
1. Display employee name, employee salary, and employee's department:

2. display the Department name, employee name, and salary with the department Number 10:

3. display the name, salary, and salary level of each employee:

4. display the employee name, employee salary, and department name, sorted by department

**

Self-connection

**

5. The name of FORD's superior leadership is displayed:

6. display the names, salaries, and department numbers of employees whose salaries are higher than those of all employees of Department 30:

7. display the name, salary, and department number of an employee whose salary is higher than that of any employee of Department 30:

8. query all employees who have the same department and position as SMITH:

9. Information showing all employees whose salaries are higher than the average salaries of their respective departments:

Idea: first identify the average salary and department number of each department, and then regard the query result as a table.

Select * from emp e, (select deptno, avg (sal) as avgsal from emp group by deptno) avg_sal where e. deptno = avg_sal.deptno and e. sal> avg_sal.avgsal;

**

Summary

**
When a subquery is used in the from clause, the subquery is treated as a view. Therefore, it is called an embedded view. When a subquery is used in the from clause, you must specify an alias for the subquery. the alias for the subquery does not use as. the alias for the column must use.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.