Shulou(Shulou.com)06/01 Report--

The role of subqueries: query things whose conditions are unknown

Questions with known query conditions: for example, query employee information with a salary of 800

Questions with unknown query conditions: for example, query the information of employees whose salary is the average salary of department 20

A problem with unknown conditions can be decomposed into problems with known conditions.

Query the information of employees whose salary is higher than WARD

First: inquire about WARD's salary?

Select sal from emp where ename = 'WARD'

Second: check the information of employees whose salary is higher than 1250?

Select * from emp where sal > 1250

Subquery:

Select * from emp where sal > (select sal from emp where ename = 'WARD')

Query the employee information of 'SALES' department (method 1: sub-query)

First: query the number of the department named 'SALES'?

Select deptno from dept where dname = 'SALES'

Second: inquire about the information of the employee with department number 30?

Select * from emp where deptno = 30

Subquery:

Select * from emp where deptno = (select deptno from dept where dname = 'SALES')

Subquery details:

1) the child query and the parent query can be directed against the same table

2) the child query and the parent query can be based on different tables

3) the number of child queries should be the same as that of parent queries in traditional parameters.

4) the type of subquery should be the same as that of parent query in traditional parameters.

5) the meaning of child query should be the same as that of parent query in traditional parameters.

Query the employee information of the department named 'SALES' (method 2: multi-table query)

Select emp.*from dept,empwhere (dept.deptno=emp.deptno) and (dept.dname='SALES')

Query each employee number, name, department name, salary grade (three tables query, these three tables are not related)

Select e.empnoree.enamered.dnameparams.gradefrom emp eCoherence dept dpencil salgrade swhere (e.deptno=d.deptno) and (e.sal between s.losal and s.hisal)

Query the lowest-paid employee information (single-line subquery, use the = sign)

First: find out what is the lowest wage?

Select min (sal) from emp

Second: inquire about the information of employees with a salary of 800?

Select * from emp where sal = 800,

Subquery:

Select * from emp where sal = (select min (sal) from emp)

Query the employee information of the department named 'ACCOUNTING' or' SALES' (multi-line subquery, using the in keyword)

First: check the department number of the department named 'ACCOUNTING' or' SALES'?

Select deptno from dept where dname in ('ACCOUNTING','SALES')

Second: inquire about the employee information whose department number is 10 or 30?

Select * from emp where deptno in (100.30)

Subquery:

Select * from emp where deptno in (select deptno from dept where dname in ('ACCOUNTING','SALES'))

Inquiry salary is lower than that of department 20 [arbitrary any] an employee's salary [lower]

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.