Simple query of multiple Oracle tables

1. Oracle multi-Table query

Multi-Table query refers to queries based on two or more tables or views.

Question: Display employee name, employee salary, and department name [Cartesian set ]?

Select t. ename, t. sal, t1.dnamefrom emp t, dept t1where t. deptno = t1.deptno;

Q: What is the name, employee name, and salary of the Department with the department Number 10 displayed?
SELECT d. dname, e. ename, e. sal FROM emp e, dept d WHERE e. deptno = d. deptno and e. deptno = 10;
Question: display the employee name, employee salary, and department name, and sort by department?
SELECT e. ename, e. sal, d. dname FROM emp e, dept d WHERE e. deptno = d. deptno ORDER by e. deptno;

2. Self-connection
Auto-join refers to the connection query in the same table.
Q: What is the name of an employee's superior leadership?
For example, the employee's supervisor is displayed
SELECT worker. ename, boss. ename FROM emp worker, emp boss WHERE worker. mgr = boss. empno AND worker. ename = 'Ford ';

3. subquery
A subquery is a select statement embedded in other SQL statements. It is also called a nested query.
3.1 single row subquery
A single row subquery is a subquery statement that returns only one row of data.
Think about it: show all employees in the same department as SMITH?
Ideas:
1. The SMITH Department number is queried.
Select deptno from emp WHERE ename = 'Smith ';
2 display
SELECT * FROM emp WHERE deptno = (select deptno from emp WHERE ename = 'Smith ');
The SQL statements executed by the database are scanned from left to right. If brackets exist, the statements in the brackets are first executed.
3.2 multi-row subqueries
Multi-row subquery refers to a subquery that returns multi-row data.
How can I query the names, positions, salaries, and department numbers of employees with the same job as department 10?
Select distinct job FROM emp WHERE deptno = 10;
SELECT * FROM emp WHERE job IN (select distinct job FROM emp WHERE deptno = 10 );
(Note: you cannot use job =... because equal sign = is one-to-one)
Use the all operator in multi-row subqueries
Q: How do I display the names, salaries, and department numbers of employees with higher salaries than all employees in department 30?
SELECT ename, sal, deptno FROM emp WHERE sal> all (SELECT sal FROM emp WHERE deptno = 30 );
Use the any operator in multi-row subqueries
Q: How can I display the name, salary, and department number of an employee whose salary is higher than that of any employee of Department 30?
SELECT ename, sal, deptno FROM emp WHERE sal> ANY (SELECT sal FROM emp WHERE deptno = 30 );

Equivalent to SELECT ename, sal, deptno FROM emp WHERE sal> (SELECT min (sal) FROM emp WHERE deptno = 30)

3.3 Multi-column subqueries

A single row subquery refers to a subquery that only returns data in a single column and a single row. A multi-row subquery refers to a single column and multiple rows of data, multi-column subquery refers to the subquery statement that queries and returns data of multiple columns.

Think about how to query all employees who have the same department and position as SMITH

Select * from emp where (deptno, job) = (select deptno, job from emp where ename = 'Smith ')

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.
Note: aliases cannot use as, for example, SELECT e. ename, e. deptno, e. sal, ds. mysal FROM emp e, (SELECT deptno, AVG (sal) mysal FROM emp GROUP by deptno) as ds WHERE e. deptno = ds. deptno AND e. sal> ds. mysal;
You cannot add as before ds; otherwise, an error will be reported (as cannot be added when the table is given an alias; but as can be added when the column is given an alias)

Oracle Parallel Query

Oracle user information query operation statement

Performance problems of querying the maximum and minimum values of a column in a single Oracle table

The recycle bin causes slow usage of Oracle query table space