Oracle subquery (nested query)

Concept:

The so-called subquery, that is, a select statement nested another or more Select statements

requirements: Find and ID and last_name of all employees of Smith's Department

Target: Staff Id,last_name

From:s_emp

condition: s_emp.dept_id = ID of the department where Smith is located ?

Select Id,last_name

From S_emp

where dept_id =?

Phase objectives: ID of the department where Smith is located

Target: dept_id

From:s_emp

condition: last_name = ' Smith ';

Select dept_id

From S_emp

where last_name = ' Smith ';

Combination:

Select Id,last_name

From S_emp

where dept_id = (

Select dept_id

From S_emp

where last_name = ' Smith '

)

Application Scenarios:

1. the query condition of a query statement relies on the query result of another query statement.

2. the query result of a query statement is a query table ( query Basis )as another query statement.

3. Using subqueries in DML operations ( post-Presentation )

Basic principles of sub-query:

1. you can have single-row subqueries and multiline subqueries in queries

2. subqueries can appear to the left or right of the operator

3. subqueries can be used in many SQL commands

4. The nested query executes first and then passes the result to the main query.

One, the comparison value is not determined, need another select statement to be executed before it can be obtained, using a subquery

Grammar:

Select ...

From ...

Where col_name comparison operator (

Select ...

From ...

where ...

GROUP BY ...

Having ...

)

GROUP BY ...

Having ...

ORDER BY ...

single-valued subquery: The result of a subquery is 1

Demand:

1. Query and simith the last_name and title of all employees with the same title

Analysis steps:

1. determine the final query result ( target / main query ): Query the employee's last_name and title

From:s_emp

conditions : title = Smith 's title

Select Last_name,title

From S_emp

where title =?

2. Determining Conditions ( subquery ):Smith 's title

From:s_emp

conditions :last_name = ' Smith ';

Select title

From S_emp

where last_name = ' Smith ';

3. Combination

Select Last_name,title

From S_emp

where title = (SELECT title

From S_emp

where last_name = ' Smith ');

2. View the ID and name of all employees whose salary is greater than the Chang employee's salary.

Final goal: Id,last_name of employees

From:s_emp

conditions: salary of salary > Chang Employees

Select Id,last_name

From S_emp

Where salary >?

Phase objectives: salary of Chang employees

From : s_emp

condition: last_name = ' Chang ';

Select Salary

From S_emp

where last_name = ' Chang ';

Combination:

3. Check the ID and name of all employees whose employees pay less than the average wage

For example: Find and The ID and last_name of the employee of Smith's same department

Multi-valued subquery: The result of a subquery is multiple

Demand:

1. query the ID and last_name of employees in all departments in area 2

1. determine the final query result: Employee ID, last_name

From:s_emp

conditions :s_emp.dept_id in (?);

Select Id,last_name

From S_emp

Where dept_id in?

2. Determine the condition: The area is 2 Department ID

Sub-query: Department ID

From:s_dept

condition : region_id = 2;

Select ID

From S_dept

where region_id = 2;

3. Combination:

Select Id,last_name

From S_emp

where dept_id in (

Select ID

From S_dept

where region_id = 2

)

Sub-query appears in two cases:

Find the contents of an indeterminate, need to build a table from a query

Grammar:

Select ....

From (select ....) b

where ...

Exercise: Query the ID of eachdepartment,name and average salary of department employees

1. Query target:

The ID of the department is required , the name of the department------ from the s_dept table

The average wage of the departmental staff avg (Salary)---------salary only The s_emp table has

conditions : Department ID,name , and departmental employee, so the ID of the department is required with the ID of the employee's department equal before connecting

Select Id,name, average wage

From S_dept,?

where S_dept.id =?. dept_id;

2. Query Criteria

Select (Dept_id,avg (Salary) Sal)

From S_emp

Group BY DEPT_ID;

3. Combination:

Select Id,name,b.sal

From S_dept dept, (select Dept_id,avg (Salary) Sal

From S_emp

GROUP BY dept_id

) b

where dept.id = b.dept_id;

Oracle subquery (nested query)