相关子查询和非相关子查询

1.非相关子查询（嵌套子查询）

一个select...From...Where查询语句块可以嵌套在另一个select...From...Where查询块的Where子句中，称为嵌套查询。外层查询称为父查询，主查询。内层查询称为子查询，从查询。
子查询可以嵌套多层，子查询查询到的结果又成为父查询的条件。

---------------------------------------

建表
create table xscj /*学生成绩表*/
(
cjid   NUMBER(4) not null,
xsbh   VARCHAR2(10) not null, /*学生编号关联学生表*/
fs     number(3) default 0 /*分数*/
)

alter table xscj add constraint PK_xscj primary key (cjid);

create table student /*学生表*/
(
xsid NUMBER not null,

xsbh VARCHAR2(10) not null, /*学生编号*/
xsname VARCHAR2(10) not null /*学生姓名*/
)

alter table student add constraint PK_student primary key (xsid);
---------------------------------------------------------------------------

添加数据
select * from student;
insert into student values (1,'001','小张三');
insert into student values (2,'002','小李四');
insert into student values (3,'003','小王五');
insert into student values (4,'004','小赵六');
--------------------------------
select * from xscj;

insert into xscj values(1,'001',95);

insert into xscj values(2,'002',90);
insert into xscj(cjid,xsbh) values(3,'003');
　　update xscj set fs='85' where cjid=3;
insert into xscj values(4,'004',80);

-------------------------------------------------------------------------

我要查询分数大于等于学生编号是'003' 的分数的所有学生成绩信息
select * from xscj where fs>=(select fs from xscj where xsbh='003');

2. 查询员工表中每一个部门的最高基本工资的员工资料

select * from emp ;
select deptno from emp group by deptNo; --3个部门
select max(sal),deptNo from emp group by deptNo; 2850--30, 3000--20, 5000--10;

用到相关子查询

select * from emp a where sal =(select max(sal) from emp b where b.deptno = a.deptno) order by deptno ;

-----------------------------------------------