就业培训学习记录-day004

课堂任务

视图

• 视图就是封装了一条复杂的语句
• 是一个虚表，不占用空间
• 最大的优点就是简化复杂的查询
• 视图会限制很多操作，比如说不能使用delete、组函数、group by

创建视图

create view empview
as
select e.empno,e.ename,e.sal,e.sal*12 ansal,d.dname
from emp e,dept d
where e.deptno=d.deptno;

查看视图

select * from empview;

删除视图

drop view empview;

序列

在很多数据库中都存在一个自动增长的列，如果现在要想在oracle中完成自动增长的功能，则只能依靠序列完成。所有的自动增长的操作，需要用户手工完成处理，并且oracle将序列值装入内存可以提高访问效率。
序列可以多个用户来产生唯一数值的数据库对象、可以自动提供唯一的数值、共享对象、主要用于提供主键值。
语法：

CREATE SEQUENCE 序列名
　　[INCREMENT BY n]
　　[START WITH n]
　　[{MAXVALUE/ MINVALUE n| NOMAXVALUE}]
　　[{CYCLE|NOCYCLE}]
　　[{CACHE n| NOCACHE}];

其中：

1. INCREMENT BY用于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表Oracle序列的值是按照此步长递减的。
2. START WITH定义序列的初始值(即产生的第一个值)，默认为1。
3. MAXVALUE定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项。
4. MINVALUE定义序列生成器能产生的最小值。
5. CYCLE和NOCYCLE表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值；对于递减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。
6. CACHE（缓冲）定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。
   大量语句发生请求，申请序列时，为了避免序列在运用层实现序列而引起的性能瓶颈。Oracle序列允许将序列提前生成cache x个先存入内存，在发生大量申请序列语句时，可直接到运行最快的内存中去得到序列。但cache个数也不能设置太大，因为在数据库重启时，会清空内存信息，预存在内存中的序列会丢失，当数据库再次启动后，序列从上次内存中最大的序列号+1 开始存入cache x个。这种情况也能会在数据库关闭时也会导致序号不连续。
7. NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用。
8. CURRVAL 获取序列中存放的当前值。首次使用CURRVAL之前，应该先使用一次NEXTVAL。

下面这个序列的最大值是多少？

CREATE SEQUENCE dept_deptid_seq
  INCREMENT BY 10
  START WITH 120
  MAXVALUE 9999
  NOCACHE
  NOCYCLE;

答案：9990。因为该序列是从120开始的，每次递增10，则临近最大值9999的序列为9990。

创建序列

create sequence myseq;

查看序列

select myseq.currval from dual;

如果提示“序列 MYSEQ.CURRVAL 尚未在此会话中定义”，则需要先执行一次下面的语句。

select myseq.nextval from dual;

序列的使用

首先先创建一张表，然后在这张表做示范

create table testseq(tid number,tname varchar2(20));
insert into testseq values(myseq.nextval,'aaaa');
insert into testseq values(myseq.nextval,'aaaa');
insert into testseq values(myseq.nextval,'aaaa');
insert into testseq values(myseq.nextval,'aaaa');
select * from testseq;

序列可能会产生裂缝的原因

1. 回滚
2. 系统异常
3. 多个表共用一个序列

删除序列

drop sequence myseq;

索引

索引是用于加速数据存取的数据对象，合理的使用索引可以大大降低i/o次数，从而提供数据访问性能。一般来说索引可以提高查询的速度，但不是有了索引，查询速度就一定会提高。
什么时候创建索引:

• 列中数据值分布范围很广
• 列经常在where子句或连接条件中出现
• 表经常被访问且数据量很大，访问的数据大概占数据总量的2%到4%

什么时候不要创建索引：

• 表很小的
• 列不经常作为链接条件或出现在where子句中
• 查询的数据大于数据总量的2%到4%
• 表经常更新

创建索引

1. 单列索引
   是基于单个列所建立的索引。

create index 索引名 on 表名(列名)

2. 复合索引
   是基于两个或多个列的所建立的索引，在同一张表上可以有多个索引，但是要求列的组合必须不同。

create index 索引名 on 表名(列名1,列名2);

删除索引

drop index 索引名;

同义词

同义词就是一个别名，我们给表取一个别名，这个别名就叫同义词。

create synonym 别名 for 对象（表、视图等）;

例如

create synonym hremp for hr.employees;

PL/SQL

PLSQL是oracle对sql语言的过程化扩展，在sql命令中增加了过程处理语句（分支，循环），使sql语言具有过程处理能力，把sql语言的数据操纵能力与过程语言的数据处理能力结合起来，使得plsql面向过程但比过程语言简单、高效和灵活。
plsql结构如下

DECLARE
--声明部分，在此声明pl/sql用到的变量，类型及游标，以及局部的存储过程和函数
BEGIN
--执行部分，过程及sql语句
EXCEPTION
--执行异常
END;

输出'hello plsql'

先执行一次下面的语句，打开输出

set serveroutput on

然后接着开始在命令行使用plsql

declare
begin
dbms_output.put_line('hello plsql');
end;
/

SQL> declare
    2    begin
    3    dbms_output.put_line('hello plsql');
    4    end;
    5    /
hello plsql
PL/SQL 过程已成功完成。
已用时间: 00: 00: 00.00

安装sqldeveloper

官方下载链接如下，当然也可以自己从其他地方下载。
Oracle SQL Developer Downloads
安装教程参考下面的链接，直到可以执行查询语句select * from emp;即可。
sqldeveloper的安装及其使用教程

变量

:= 相当于赋值
定义变量

Varl char(15);
Married boolean:=true;
Psal numbe(7,2);
My_name emp.ename%type; -- %相当于引用类型，这里的意思是变量My_name的类型就是emp.ename的类型 
Emp_rec emp%rowtype; -- 记录型变量，相当于行类型，就是引用这一行的类型

引用类型变量

查询并打印7839的姓名和薪水

declare
  --定义变量保存姓名和薪水
--  pename varchar2(25);
--  psal number;
  pename emp.ename%type;
  psal emp.sal%type;
begin
  --得到7839的姓名和薪水
  select ename,sal into pename,psal from emp where empno=7839;
  --打印
  dbms_output.put_line(pename||'的薪水是'||psal);
end;

结果如图，如果没有显示结果，则先执行一次下列语句，再执行上面的语句。

set serveroutput on

记录类型变量

查询并打印7566的姓名和薪水

declare
  --定义记录型变量：代表一行
  emp_rec emp%rowtype;
begin
  select * into emp_rec from emp where empno=7566;
  dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);
end;

returning字句

returning用于检索insert语句中所影响的数据行，当insert语句使用values子句插入数据的时候
returning子句还可以将插入数据中的某个值返回到输出变量中。

1. 新增一个99号财务部，地点成都，并显示插入的数据

declare
  row_id ROWID;
  info varchar2(40);
BEGIN
  insert into dept values (99,'财务部','成都')
  returning rowid,dname||':'||to_char(deptno)||':'||loc
  into row_id,info;
  dbms_output.put_line('row_id==='||row_id);
  dbms_output.put_line('info==='||info);
  commit;
end;

2. 把财务部改成90号部门

declare
  row_id ROWID;
  info varchar2(40);
begin
  update dept set deptno='90' where dname='财务部'
  Returning rowid,dname||':'||to_char(deptno)||':'||loc into row_id,info;
  DBMS_OUTPUT.PUT_LINE(row_id);
  DBMS_OUTPUT.PUT_LINE(info);
end;

if条件判断

语法：

--写法1
if 条件1 then 语句1
end if;

--写法2
if 条件1 then 语句1;
else 语句2;
end if;

--写法3
if 条件1 then 语句1;
elsif 条件2 then 语句2;
else 语句3;
end if;

判断用户从键盘输入的数字

declare
  pnum number:=&input;
begin
  if pnum=0 then DBMS_OUTPUT.PUT_LINE('输入的是0');
  elsif pnum=1 then DBMS_OUTPUT.PUT_LINE('输入的是1');
  elsif pnum=2 then DBMS_OUTPUT.PUT_LINE('输入的是2');
  else DBMS_OUTPUT.PUT_LINE('输入的是其他数字');
  end if;
end;

循环（for、while）

语法：

--写法1
while 条件
loop
  语句...
end loop;

--写法2
loop
  exit [when 退出条件]; 
  语句...
end loop;

--写法3
for i in 1 .. 3
loop
  语句...
end loop;

例子

--写法1
declare 
  step number:=1;
begin
  while step<=10
  loop
    DBMS_OUTPUT.PUT_LINE(step);
    step:=step+1;
  end loop;
end;

--写法2
declare
  step number:=10;
begin
  loop
    DBMS_OUTPUT.PUT_LINE(step);
    step:=step+1;
    exit when step>=20;
  end loop;
end;

--写法3
declare
  step number;
begin
  for step in 20..30
  loop
    DBMS_OUTPUT.PUT_LINE(step);
  end loop;
end;

6道练习题

这6道题用到的是上述讲过的条件判断和循环。

1. 输入一个雇员的编号，如果其工资高于3500，则显示高工资，工资大于2000，则显示中等工资

declare
  eno emp.empno%type;
  psal emp.sal%type;
begin
  eno:=&seno;
  select sal into psal from emp where empno=eno;
  if psal>3500 then DBMS_OUTPUT.PUT_LINE('高工资');
  elsif psal>2000 and psal<3500 then DBMS_OUTPUT.PUT_LINE('中工资');
  end if;
end;

2. 输入一个雇员编号，根据它所在的部门涨工资
   10部门上涨10%，20部门上涨20%，30部门上涨30%，其他部门不涨。上涨工资的量不能超过5000，若超过，则涨5000。

declare
  eno emp.empno%type;
  dno emp.deptno%type;
  esal emp.sal%type;
begin
  eno:=&eno;
  select deptno,sal into dno,esal from emp where empno=eno;
  if dno=10 then
    esal:=esal*0.1;
  elsif dno=20 then
    esal:=esal*0.2;
  elsif dno=30 then
    esal:=esal*0.3;
  end if;
  if esal>5000 then
    esal:=5000;
  end if;
  update emp set sal=sal+esal where empno=eno;
  commit;
end;

3. 通过循环，为dept表增增加50~80部门

declare
  step number:=50;
begin
  loop
    insert into dept values(step,'财务部','成都');
    step:=step+10;
    exit when step>80;
  end loop;
end;

4. 输入一个雇员的编号，计算某个雇员的年度薪水总额

declare
  pnum number:=#
  psal number;
begin
  select empno,sal*12 into pnum,psal from emp where empno=pnum;
  DBMS_OUTPUT.PUT_LINE(pnum||'年薪:'||psal);
exception
  when no_data_found then
  dbms_output.put_line('没有这个雇员');
end;

5. 用*打印直接三角形，最大的边是9个*

begin
  for i in 1 .. 9 loop
    for j in 1 .. i loop
      dbms_output.put('*');
    end loop;
    dbms_output.put_line('');
  end loop;
end;

6. 打印菱形

declare
  m number:=4;
begin
  for y in -m..m loop
    for x in -m..m loop
      if abs(y)+abs(x) <=m then
        dbms_output.put('*');
      else
        dbms_output.put(' ');
      end if;
    end loop;
    dbms_output.put_line('');
  end loop;
end;

游标

游标可以存储查询的返回的多条数据。
语法：

CURSOR 游标名 [(参数名 数据类型,参数名 数据类型,...)] IS SELECT 语句;
cursor c1 is select ename from emp;

游标的使用步骤:
(1) 打开游标：open c1;（打开游标执行查询）
(2) 取一行游标的值：fetch c1 into pename（取一行到变量中）
(3) 关闭游标：close c1;（关闭游标释放资源）
(4) 游标的结束方式：exite when c1%notfound;

1. 按员工的工种涨工资，总裁涨1000元，经理涨800元，其他涨400元

declare
  cursor cemp is select empno,job from emp;
  pjob emp.job%type;
  pempno emp.empno%type;
begin
  --打开游标
  open cemp;
  loop
    --取一个员工
    fetch cemp into pempno,pjob;
    exit when cemp%notfound;
    --判断工种
    if pjob ='PRESIDENT' then update emp set sal=sal+1000 where empno=pempno;
    elsif pjob ='MANAGER' then update emp set sal=sal+800 where empno=pempno;
    else update emp set sal=sal+400 where empno=pempno;
    end if;
  end loop;
  close cemp;
  dbms_output.put_line('完成');
  commit;
end;

2. 查询并打印员工的姓名和薪水

declare
  cursor cemp is select ename,sal from emp;
  pename emp.ename%type;
  psal emp.sal%type;
begin
  open cemp;
  loop
    fetch cemp into pename,psal;
    exit when cemp%notfound;
    dbms_output.put_line(pename||'的薪水是'||psal);
  end loop;
  close cemp;
end;

3. 带参数的游标，查询某个部门的员工姓名

declare
  cursor cemp(dno number) is select ename from emp where deptno=dno;
  pename emp.ename%type;
begin
  --实参
  open cemp(20);
  loop
    fetch cemp into pename;
    exit when cemp%notfound;
    dbms_output.put_line(pename);
  end loop;
  close cemp;
end;

异常

异常是程序设计语言提供的一种功能，用来增强程序的健壮性和容错性

常见异常

• no_data_found 没有找到数据
• too_many_rows 行数过多
• zero_divide 被零除
• value_error 算术或转换错误

1. 被零除异常

declare
  pnum number;
begin
  pnum:=1/0;
exception 
  when zero_divide then dbms_output.put_line('0不能做分母');
end;

2. 自定义异常，查询0号部门的员工

declare
  cursor cemp is select ename from emp where deptno=0;
  pename emp.ename%type;
  --自定义例外
  no_emp_found exception;
begin
  open cemp;
  --取第一条记录
  fetch cemp into pename;
  if cemp%notfound then
    --抛出例外
    raise no_emp_found;
  end if;
  close cemp;
exception
  when no_emp_found then dbms_output.put_line('没有找到员工');
  --其他异常的处理
  when others then dbms_output.put_line('其他异常');
end;

PL/SQL练习

1. 统计每年入职的员工个数
   结果应为：1980年1人，1981年10人，1982年1人，1987年2人。

declare
  --定义游标
  cursor c1 is select to_char(hiredate,'yyyy') from emp; 
  cyear varchar2(4);
  --每年入职的人数
  count80 number:=0;
  count81 number:=0;
  count82 number:=0;
  count87 number:=0;
  total number:=0;
begin
  --打开游标
  open c1;
  loop
    --取记录
    fetch c1 into cyear;
    exit when c1%notfound;
    --判断年份并做相应处理
    if cyear='1980' then count80:=count80+1;
    elsif cyear='1981' then count81:=count81+1;
    elsif cyear='1982' then count82:=count82+1;
    elsif cyear='1987' then count87:=count87+1;
    end if;
  end loop;
  close c1;
  total:=count80+count81+count82+count87;
  dbms_output.put_line('1980:'||count80);
  dbms_output.put_line('1981:'||count81);  
  dbms_output.put_line('1982:'||count82);
  dbms_output.put_line('1987:'||count87);
  dbms_output.put_line('Total:'||total);
end;

2. 涨工资
   使用socct用户下的emp表，为员工涨工资。每人涨10%，但是老板规定涨工资的总额只有5万元，也就是说涨工资消耗的总额最多只有5万。请计算并打印最多涨工资的人数和涨工资所消耗的金钱总额。如果涨完某个人之后可涨工资余额不足下一个人，则停止涨工资。多次运行的结果中，涨工资的人数应该越来越少，并且工资总额不应该超过5万。

declare
  cursor c1 is select empno,sal from emp order by sal;
  cempno emp.empno%type;
  csal emp.sal%type;
  countemp number:=0;
  salTotal number:=0;
begin
  open c1;
  loop
    --取员工号和当前工资
    fetch c1 into cempno,csal;
    exit when c1%notfound;
    --如果涨工资后所需的金额不超过5万
    if salTotal+csal*1.1<50000 then
      update emp set sal=sal*1.1 where empno=cempno;
      countemp:=countemp+1;
      salTotal:=salTotal+csal*1.1;
    --如果超过，则退出循环
    else exit;
    end if; 
  end loop;
  dbms_output.put_line('countemp:'||countemp);
  dbms_output.put_line('salTotal:'||salTotal);
end;

3. 按部门和薪水进行分段统计
   按照部门和不同阶段的薪水（6000以上，(6000~3000)，3000以下)，统计各个工资段的职工人数，以及各部门的工资总额。将结果插入到一张新表中，表的结构如下。

--建表语句
CREATE TABLE MSG(DEPTNO NUMBER,NUM1 NUMBER,NUM2 NUMBER,NUM3 NUMBER,SUM_SAL NUMBER);

部门	小于3000	3000~6000	大于6000	工资总额
10
20
30
40
...

declare
  cursor c1 is select deptno from dept;
  cdeptno dept.deptno%type;
  cursor c2(input number) is select sal from emp where deptno=input;
  csal emp.sal%type;
  count3k number:=0;
  count3kto6k number:=0;
  count6k number:=0;
  sumsal number:=0;
begin
  open c1;
  loop
    fetch c1 into cdeptno;
    exit when c1%notfound;
    count3k:=0;
    count3kto6k:=0;
    count6k:=0;
    select sum(sal) into sumsal from emp where deptno=cdeptno;
    open c2(cdeptno);
    loop
      fetch c2 into csal;
      exit when c2%notfound;
      if csal<3000 then count3k:=count3k+1;
      elsif csal between 3000 and 6000 then count3kto6k:=count3kto6k+1;
      else count6k:=count6k+1;
      end if;
    end loop;
    close c2;
    insert into msg values(cdeptno,count3k,count3kto6k,count6k,sumsal);
  end loop;
  close c1;
  commit;
  dbms_output.put_line('完成');
end;

课后任务

使用scott用户完成以下练习

1. 选择30号部门员工的所有信息

select * from emp where deptno=30;

2. 列出职位为MANAGER的员工编号、姓名

select empno,ename from emp where job='MANAGER';

3. 找出奖金高于工资的员工

select * from emp where nvl(comm,0)>sal;

4. 找出每个员工奖金和工资的总和

select empno,ename,sal,comm,sal+nvl(comm,0) as sum from emp;

5. 找出10号部门中既不是经理也不是普通员工，而且工资大于等于2000的员工

select * from emp where deptno=10 and job not in('MANAGER','CLERK') and sal>=2000;

6. 找出有奖金的员工的不同工作

select distinct job from emp where comm is not null;

7. 找出没有奖金或者奖金低于500的员工

select * from emp where nvl(comm,0)<500;

8. 显示雇员姓名，根据其服务年限，将最老的雇员排在最前面

select ename,hiredate from emp order by HIREDATE asc;

9. 找出每个月倒数三天受雇的员工（如：2009-5-29）

select * from emp where hiredate=last_day(hiredate)-2;

10. 找出25年雇的员工

select * from emp where to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy')>25;

11. 所有员工名字前加上Dear，并且名字首字母大写

select 'Dear'||initcap(ename) from emp;

12. 找出姓名为5个字母的员工

select * from emp where ename like '_____';

13. 找出姓名中不带R这个字母的员工

select * from emp where ename not like '%R%';

14. 显示所有员工姓名的第一个字母

select empno,substr(ename,1,1),job,mgr,hiredate,sal,comm,deptno from emp;

15. 分组统计各部门下工资>500的员工的平均工资

select deptno,avg(sal) from emp where sal>500 group by deptno;

16. 统计各部门下平均工资大于500的部门

select deptno,avg(sal) from emp group by deptno having avg(sal)>500;

17. 算出30号部门中得到最多奖金的员工姓名

select ename,comm from emp where comm=(select max(comm) from emp where deptno=30);

18. 算出每个职位的员工数和最低工资

select job,count(*),min(sal) from emp group by job;

19. 算出每个部门、每个职位的平均工资和平均奖金（平均奖金包括没有奖金），如果平均奖金大于300，显示“奖金不错”，如果平均奖金在100到300之间，显示“奖金一般”，如果平均奖金小于100，显示“基本没有奖金”，按部门编号降序，平均工资降序排列

select deptno,job,avg(sal),avg(nvl(comm,0)),
  decode(trunc(avg(nvl(comm,0))/100),
  '0','基本没有奖金',
  '1','奖金一般',
  '2','奖金一般',
  '奖金不错')
from emp
group by deptno,job
order by deptno,avg(sal) desc;

20. 列出员工表中每个部门的员工数和部门号

select deptno,count(*) from emp group by deptno;

21. 查出工资大于自己部门平均工资的员工信息

select a.*,b.avgsal
from emp a,(
  select deptno,avg(sal) as avgsal from emp group by deptno
) b 
where a.sal>b.avgsal and a.deptno=b.deptno;

22. 分组统计每个部门、每种职位的平均奖金（也要算没奖金的人）和总工资（包括奖金）

select deptno,job,avg(nvl(comm,0)),sum(nvl(comm,0))+sum(sal)
from emp
group by deptno,job;

23. 列出员工表中每个部门的员工数（员工数必须大于3）和部门名称

select e.deptno,d.dname,count(*)
from emp e left join dept d on e.deptno=d.deptno
group by e.deptno,d.dname
having count(*)>3;

24. 找出工资比JONES多的员工

select * from emp where sal>(select sal from emp where ename='JONES');

25. 列出所有员工的姓名及其上级的姓名

select a.ename,b.ename from emp a left join emp b on a.mgr=b.empno;

26. 以职位分组，找出平均工资最高的两种职位

select job,avgsal
from (
  select job,avg(sal) as avgsal from emp group by job order by avg(sal) desc
)
where rownum<=2;

27. 查找出不在20号部门，并且比20号部门任何一个人工资都高的员工姓名和部门名称

select e.ename,d.dname
from emp e left join dept d on e.deptno=d.deptno
where e.deptno!=20 and sal>(select max(sal) from emp where deptno=20);

28. 查出平均工资大于2000的职位

select job,avg(sal) from emp group by job having avg(sal)>2000;

29. 分部门得到工资大于2000的员工的平均工资，并且平均工资还要大于2500

select deptno,avg(sal) from emp where sal>2000 group by deptno having avg(sal)>2500;

30. 查出每个月工资总数最少的部门的部门编号、名称和位置

select distinct e.deptno,d.dname,d.loc
from emp e left join dept d on e.deptno=d.deptno
where e.deptno=(
  select deptno from (
    select deptno,sum(sal) as sumsal
    from emp
    group by deptno
    order by sumsal asc
  )
where rownum=1
);

31. 分部门查询平均工资等级为2级（见工资等级表）的部门编号

select distinct deptno
from emp e,salgrade s
where grade=2 and e.sal between s.losal and s.hisal;

32. 查出收入（工资加奖金），下级比自己上级还高的员工编号、姓名和收入

select a.empno,a.ename,a.sal+nvl(a.comm,0)
from emp a left join emp b on a.mgr=b.empno
where a.sal+nvl(a.comm,0)>b.sal+nvl(b.comm,0);

33. 查出工资等级不为4级的员工的姓名、部门名字和部门位置

select e.ename,d.dname,d.loc,e.sal
from emp e,salgrade s,dept d
where e.deptno=d.deptno and grade!=4 and e.sal between s.losal and s.hisal;

34. 查出职位和 MARTIN 或者 SMITH 一样的员工的平均工资

select avg(sal)
from emp
where job in (select job from emp where ename in ('MARTIN','SMITH'));

35. 查找出不属于任何部门的员工

select * from emp where deptno is null;

36. 按部门统计员工数，查出员工数最多的部门的第二名到第五名（列出部门名字和位置）

select dname,loc
from (
  select rownum row_num,deptno
  from (
    select count(*) count_emp,deptno
    from emp
    group by deptno
    order by count_emp desc
  )
) e,dept d 
where e.row_num between 2 and 5 and e.deptno=d.deptno;

37. 查询出 KING 所在的部门号、部门名称和部门人数

select e.count_num,d.dname,d.loc
from dept d,(
  select count(*) count_num,deptno
  from emp
  where deptno=(
    select deptno
    from emp
    where ename='KING'
  )
  group by deptno
)e
where d.deptno=e.deptno;

38. 查询出工资成本最高的部门的部门号和名称

select d.deptno,d.dname,t.sumsal
from dept d,(
  select deptno,sum(sal) sumsal
  from emp
  group by deptno
  having sum(sal)=(
    select max(sum(sal))
    from emp
    group by deptno
  )
) t
where d.deptno=t.deptno;

建表完成以下练习

DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS teacher;
DROP TABLE IF EXISTS course;
DROP TABLE IF EXISTS choose_course;
create table student(sid number,sname varchar2(20));
create table teacher(tid number,tname varchar2(20));
create table course(cid number,cname varchar2(20),ctype varchar2(20));
create table choose_course(ccid number,sid number,tid number,cid number);
insert into student values(1,'小明');
insert into student values(2,'小花');
insert into teacher values(1,'陈红');
insert into teacher values(2,'陈白');
insert into course values(1,'语文','文科');
insert into course values(1,'数学','理科');
-- 小明选了陈红老师的语文
insert into choose_course values(1,1,1,1);
-- 小明选了陈红老师的数学
insert into choose_course values(2,1,1,2);
-- 小花选了陈红老师的数学
insert into choose_course values(3,2,1,2);
-- 小明选了陈白老师的语文
insert into choose_course values(1,1,2,1);
-- 小花选了陈红老师的语文
insert into choose_course values(4,2,1,1);

1. 查找陈红老师教的学生有哪些

select distinct s.sname from student s,teacher t,choose_course cc
where s.sid=cc.sid and t.tid=cc.tid and t.tname='陈红';

2. 找出学生小明所有的文科老师

select tname
from student s,choose_course cc,teacher t,course c
where s.sid=cc.sid and cc.tid=t.tid and cc.cid=c.cid and s.sname='小明' and c.ctype='文科';
select s.sname

3. 查出没有选陈红老师的课的学生

select s.sname
from student s
minus
select s.sname
from student s,teacher t,choose_course cc
where s.sid=cc.sid and t.tid=cc.tid and t.tname='陈红';

4. 查出教的学生最少的老师

select tname
from teacher
where tid=(
  select tid from (
    select count(*) as count_num,tid from (
      select distinct sid,tid from choose_course
    )
    group by tid
    order by count_num asc
  )
  where rownum=1
);