华南俳烁实业有限公司

子查詢

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);

--再執(zhí)行

--出錯:ORA-01427: 單行子查詢,返回了多于一行記錄.

--改進:

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

--子查詢結(jié)果多于一條記錄,

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';

--再查, 查看結(jié)果

數(shù)據(jù)結(jié)構(gòu) 線性表, 樹, 圖

范式 1NF  2NF  3NF