子查询

select ename from emp_ning where salary = (select max(salary) from emp_ning);

--谁的薪水比tom高?

select ename from emp_ning

where salary > (select salary from emp_ning where ename = 'tom');

insert into emp_ning(empno, ename, salary) values(1020, 'tom', 5000);

--再执行

--出错:ORA-01427: 单行子查询,返回了多于一行记录.

--改进:

select ename from emp_ning where salary > (select salary from emp_ning where ename = 'tom' and deptno = 10);

--子查询结果多于一条记录,

3000

5000

--比两条记录都大: all

--比任何一条大都行: any

select ename ,salary from emp_ning where salary > all (select salary  from emp_ning where ename = 'tom');

--谁和tom同部门?

select ename, deptno from emp_ning where deptno in (select deptno from emp_ning where ename = 'tom')

and ename <> 'tom';

--谁的薪水最高?

select ename from emp_ning where salary = (select max(salary) from emp_ning);

--每个部门薪水最高的是谁?

select ename, salary, deptno from emp_ning

where (deptno, salary)  in( select deptno, max(salary) from emp_ning group by deptno);

--把部门10和20的两个tom薪水都改成10000

update emp_ning

set salary = 10000

where ename = 'tom';

--再查, 查看结果

数据结构 线性表, 树, 图

范式 1NF  2NF  3NF