SQL base data retrieval using subqueries (22)

Dolez Query

Where (manager_id, department_id) in

Sub-query

100 90

102 60

124 50

Each row of the main query is compared with multirow and multiple-column subqueries

Comparison of columns

A multi-column comparison that contains subqueries can be:

No pair comparison

Paired comparisons

Paired comparison sub-query
1. Display other employee information with the same manager as the employee named "John"

Select employee_id, manager_id, department_id from Empl_demo

Where (manager_id, department_id) in

(Select manager_id, department_id from Empl_demo

where first_name = ' John ')

and first_name <> ' John ';

No pair comparison

1. Employee number, manager number, department number for the manager ID and department ID of the employee whose name is not "John"

Select employee_id, manager_id, department_id

From Empl_demo

where manager_id in

(Select manager_id

From Empl_demo

where first_name = ' John ')

and department_id in

(Select department_id

From Empl_demo

where first_name = ' John ')

and first_name <> ' John ';

Scalar-scalar query expression

The scalar subquery is a subquery that returns a column from one row

Scalar quantum queries can be used in the following situations:

–decode and case conditions and part of an expression

–select in all clauses except the GROUP by clause

The SET clause and the WHERE clause of the –update statement

A scalar subquery in a CASE expression:

Select employee_id, last_name, department_id,

(case

When department_id =

(Select department_id

From departments

where location_id = 1800)

Then ' Canada ' Else ' USA ' end

From employees;

Scalar subquery in the ORDER by clause:

Select employee_id, last_name,department_id

From Employees E

Order BY (Select Department_name

From Departments D

where e.department_id = d.department_id);

Related sub-query

Correlated subqueries are executed in a row-by-row order, and each row of the main query executes a subquery once

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M02/8F/3F/wKioL1jY0ZPDp46NAABXUTDyPS4665.jpg "title=" Qq20170327140526.jpg "alt=" Wkiol1jy0zpdp46naabxutdyps4665.jpg "/>

Using columns from the main query in a subquery

Select Column1, Column2, ...

From table1 outer_table

where Column1 operator

(SelecT column1, Column2

From table2

where expr1 = OUTER_TABLE.EXPR2);

2. Find all employee information, who pay more than the average salary of their department

Select Last_Name, Salary, department_id

From Employees outer_table

Where Salary >

(SelecT AVG (Salary)

From Employees inner_table

where inner_table.department_id =

OUTER_TABLE.DEPARTMENT_ID);

3. Show which employees have changed their work at least two times

Select e.employee_id, last_name,e.job_id from Employees E

Where 2 <= (select COUNT (*) from Job_history

where employee_id = e.employee_id);

Using the EXISTS operator

The exists operator checks to see if there are rows in the subquery that meet the criteria.

If there are rows in the subquery that meet the criteria:

– Do not continue to find in subqueries

– Condition returns TRUE

If there are no rows in the subquery that meet the criteria:

– Conditional return FALSE

– Continue to find in subqueries

1. Use the EXISTS operator to find the leader

Select employee_id, last_name, job_id, department_id

From Employees outer

where exists (select ' x '

From Employees

where manager_id =

OUTER.EMPLOYEE_ID);

Find a department without any employees

Select department_id, Department_name

From Departments D

Where NOT EXISTS (SELECT ' x '

From Employees

where department_id = d.department_id);

Related update

Update data for another table using related subqueries based on data from one table.

Update table1 alias1 Set column = (select expression from Table2 alias2

where alias1.column = Alias2.column);

Table EMPL6 for violating the Paradigm Add Field Storage Department name (after adding field violates paradigm)

Populating a table with related child updates

ALTER TABLE EMPL6 Add (department_name varchar2 (25));

Update Empl6 E

Set department_name =

(Select Department_name

From Departments D

where e.department_id = d.department_id);

Related Delete

To delete data from another table using related subqueries based on data in one table

Delete from table1 ALIAS1

where column operator

(SELECT expression

From Table2 ALIAS2

where alias1.column = Alias2.column);

1, using the relevant sub-query delete EMPL6 exists at the same time also exists in the Emp_history table data.

Delete from Empl6 E

where employee_id =

(Select employee_id

From Emp_history

where employee_id = e.employee_id);

With clause

Use the WITH clause to avoid repeating the same block of statements in a SELECT statement

The WITH clause executes the statement block in the clause once and stores it in the user's temporary table space

Use the WITH clause to improve query efficiency

1. Use the WITH clause to write a query that displays the department name and the department and payroll of the employees of these departments that are larger than the average wage across the department

With

Dept_costs as (

Select D.department_name, sum (e.salary) as Dept_total

From Employees e join Departments D

On e.department_id = d.department_id

Group by D.department_name),

Avg_cost as (

Select SUM (dept_total)/count (*) as Dept_avg

From Dept_costs)

SELECT *

From Dept_costs

where Dept_total >

(Select Dept_avg

From Avg_cost)

Order BY Department_name;

Recursive with clause

Recursive with clause:

Enables formulation of recursive queries.

Creates query with a name, called the Recursive with element name

Contains types of query blocks Member:anchor and a recursive

Is ansi-compatible

With Reachable_from (source, Destin, Totalflighttime) as

(

Select Source, Destin, Flight_time

From flights

UNION ALL

Select Incoming.source, Outgoing.destin,

Incoming.totalflighttime+outgoing.flight_time

From Reachable_from incoming, flights outgoing

where Incoming.destin = Outgoing.source

)

Select Source, Destin, Totalflighttime

From Reachable_from;

This article is from the "record a bit of learning life" blog, please make sure to keep this source http://ureysky.blog.51cto.com/2893832/1910824

SQL base data retrieval using subqueries (22)