Oracle (day 5)

Time:2021-4-28
Subquery
1. Single line subquery:
For example: select * from EMP where Sal > (select Sal from EMP where ename =’allen ‘);
2. Multi row subquery: any all in
Greater than any is greater than the minimum
  select * from emp where sal>any(select sal from emp where deptno=10);
  select * from emp where sal>(select min(sal) from emp where deptno=10);
Greater than all is greater than the maximum
  select * from emp where sal>all(select sal from emp where deptno=10);
  select * from emp where sal>(select max(sal) from emp where deptno=10);
Less than any is less than the maximum
  select * from emp where sal
  select * from emp where sal
Less than all is less than the minimum
  select * from emp where sal
  select * from emp where sal
In (examples of usage are as follows)
这三种语法输出结果一样。
3. Multi column subquery: (understanding)
  select deptno , job from emp where deptno != 10;
  select * from emp where (deptno,job) in (select deptno , job from emp where deptno != 10);
4. Related subqueries:
Every time a primary query is executed, a subquery is executed.
Query the information of people whose salary is higher than the average salary in each department
select * from emp where deptno = 10 and sal > (select avg(sal) from emp where deptno=10);
select * from emp where deptno = 20 and sal > (select avg(sal) from emp where deptno=20);
select * from emp where deptno = 30 and sal > (select avg(sal) from emp where deptno=30);
select * from emp e1 where sal > (select avg(sal) from emp e2 where e1.deptno = e2.deptno);
 
5. Exercise screenshot:
 
 
 
用的表也是第四天的表。
 

Recommended Today

Java review day01

Variables and operators 1、 Variable 1. Define variables Variable is the amount allocated and assigned through memory, which is divided into immutable variable and variable variable //Variable type variable name (= initial value); int x; x = 1; //Same effect as the following int x = 1; The variable type determines the data type, range, […]