Oracle作业及其答案答辩
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
1
3 查询部门内部人数多于3人的部门号。 select deptno,count(*) from emp group by deptno
having count(*)>3; 4 向emp表中插入一条数据，员工号1000，员工名： Zhangsan，工作日期是1985年2月3日。 insert into emp(empno,ename,hiredate) values
11
第7章 作业
1.定义记录表类型存储dept表中每个部门（10、20、30、40）的部门
号和部门名，并输出。
set serveroutput on
declare
type table_dept is table of dept%rowtype index by
binary_integer;
raise ex_update;
end if;
exception
when ex_update then
dbms_output.put_line('没有50号部门');
when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
end;
18
第8章作业
elseif vc.deptno=20 then
update emp set sal=sal+200 where empno=vc.empno;
else
update emp set sal=sal+300 where empno=vc.empno;
edn if;
end loop;
end;
‘M’，且默认值为‘M’。 alter table student add constraint ck_stu1
check(sex in ('F','M')); alter table student modify sex default 'M';
6
5、查询student表的约束信息，并记录查询结果。 select constraint_name,column_name from user_cons_columns where table_name='STUDENT'; select owner,constraint_name,constraint_type,status from user_constraints where table_name=' STUDENT ';
7
第6章作业
已知表: 学生表：student(sno,sname,sbirth,sclass) 班级表：class(cno,cname,cdept) 系表：department(dno,dname) 其中student(sclass)关联class(cno)， class(cdept)关联department(dno)。
(1000,'Zhangsan',date'1985-2-3'); 5 修改Zhangsan的工资为20部门的最高工资。 update emp set sal=(select max(sal) from emp
where deptno=20) where ename=‘Zhangsan’;
2
6 删除员工名中包含一个“A”并且以“W”结尾的 员工信息。 delete from emp where ename like '%A%W'; 7 统计emp表中每个部门的平均工资和最高工资，并 要求参与统计的部门的平均工资多于1000，少于3000。 select avg(sal),max(sal) from emp group by deptno
t_d table_dept;
begin
for t_d in (select deptno,dname from dept) loop
dbms_output.put_line(t_d.deptno||’’||t_d.dname);
end loop;
end;
12
2.利用显式游标修改表emp中各个雇员的工资，若雇员属于10
end;
16
第7章练习
ex：使用游标与异常处理，完成下列功能。 • 查询dept表中的所有部门号与部门名，并输出。 • 将dept表中部门号为50的部门地址loc改为：上 海。 • 如果部门号不合法，则给出错误提示。 • 如果出现其他错误，给出错误代码和错误文本。
17
declare
cursor e1 is select * from dept;
第3章作业
编写SQL语句完成下列功能，并要求写出第1、3、7题 的
查询结果。 1 查询10号部门员工的员工号以及其领导的员工号，
并以别名“领导员工号”显示列名。 select empno,mgr 领导员工号 from emp where
deptno=10; 2 查询emp表中所有的员工信息，并要求按照部门号升 序排序，相同部门按照工资降序排序。 select * from emp order by deptno,sal desc;
9
3.查询‘计算机系’里1988年以前出生的学生的学号和出 生日期。
1） select sno,sbirth from student where sclass in (select cno from class a,department b where a.cdept=b.dno and d.dname=‘计算机系’ ) and sbirth<date’1988-01-01’; 2） select sno,sbirth from student a,class b,department c where a.sclass=o and b.cdept=c.dno and d.dname=‘计算机系’ and a.sbirth<date’1988-01-01’;
号部门，则增加100，若雇员属于20号部门，则增加200，否则 增加300。
declare
cursor c_emp is select empno,sal,deptno from emp;
begin
for vc in c_=10 then
update emp set sal=sal+100 where empno=vc.empno;
13
3.查询并输出emp表中某一部门的员工号、员工名。如果此部
门号不存在，捕获此异常。如果出现其他异常，给出异常信息。
declare
type r_emp is record(
ENO emp.empno%type，
ENAME emp.ename%type);
type emp_rc is ref cursor return r_emp;
8
编写sql语句完成下列功能。 1.查询班级名为‘1班’的所有学生的学号、姓名。 select sno,sname from student a,class b where a.sclass=o and cname=‘1班‘； 2.查询学号为‘20070001’的学生的姓名和所在班的
班名以及所在系的系名。 select sname,cname,dname from student a,class b,department c where a.sclass=o and b.cdept=c.dno and a.sno=‘20070001’;
10
4.查询比’计算机系‘某一个班的年龄小的学生的学号和出生 日期。
select sno,sbirth from student where sbirth <any (select sbirth from student a,class b,department c where a.sclass=o and b.cdept=c.dno and d.dname=‘计算机系’）; 5.查询‘计算机系’各个班的人数，显示人数和班级号。 select count(*),sclass from student where sclass in (select cno from class a,department b where a.cdept=b.dno and b.dname=‘计算机系’);
=v_deptno;
while i=j loop
fetch v_rc into emp_row;
i:=i+1;
if v_rc%found then
dbms_output.putline(‘员工号：’||emp_row.ENO||’’||’员工名
：’||emp_row.ENAME);
end if;
1、编写过程，用于查询某部门的所有员工的员工号和工资。如果部门号不存在，
提示出错。 create or replace procedure searchempno_sal(v_dno number) as
cursor c_emp is select empno,sal from emp where deptno=v_dno; cc c_emp%rowtype; ex exception; begin open c_emp; fetch c_emp into cc; if c_emp%notfound then
having avg(sal) between 1000 and 3000;
3
第5章作业
编写sql语句完成下面功能。 1、按照下列表结构创建表。
class表
列名
数据类型
约束
cno
number(2)
主键
cname varchar2(20)
create table class( cno number(2) constraint pk_cl primary key, cname varchar2(20));
v_rc emp_rc;
emp_row r_emp;
v_deptno emp.deptno%type;
ex exception;
i number:=0;
j number:=0;
begin
14
v_deptno=&x;
open v_rc for select empno,ename from dept where deptno
4
列名
数据类型
约束
sno
number(4)
主键
sname varchar2(20)
student表 sage
number
唯一
sex
char(2)
cno
number(2)
create table student(
sno number(4) constraint pk_stu primary key,
if j=0 then raise ex;
end if;
end loop;
close v_rc;
15
exception
when ex then
dbms_output.put_line(‘此员工不存在’)；
when others then
dbms_output.put_line(‘异常信息:’||SQLCODE||’’||SQLERROM);
ex_update exception;
begin
for ve in e1 loop
dbms_output.put_line(ve.deptno||' '||ve.dname);
end loop;
update dept set loc='上海' where deptno=50;
if sql%notfound then
sname varchar2(20) constraint un_stu unique,
sage number,
sex char(2),
cno number(2));
5
2、为student表添加外键约束，其cno列参照class表cno 列。
alter table student add constraint fk_stu foreign key(cno) references class(cno); 3、为student表sage列添加检查约束，列值在0-100。 alter table student add constraint ck_stu check(sage between 0 and 100); 4、为student表sex列添加约束，确定其值为’F’或
3 查询部门内部人数多于3人的部门号。 select deptno,count(*) from emp group by deptno
having count(*)>3; 4 向emp表中插入一条数据，员工号1000，员工名： Zhangsan，工作日期是1985年2月3日。 insert into emp(empno,ename,hiredate) values
11
第7章 作业
1.定义记录表类型存储dept表中每个部门（10、20、30、40）的部门
号和部门名，并输出。
set serveroutput on
declare
type table_dept is table of dept%rowtype index by
binary_integer;
raise ex_update;
end if;
exception
when ex_update then
dbms_output.put_line('没有50号部门');
when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
end;
18
第8章作业
elseif vc.deptno=20 then
update emp set sal=sal+200 where empno=vc.empno;
else
update emp set sal=sal+300 where empno=vc.empno;
edn if;
end loop;
end;
‘M’，且默认值为‘M’。 alter table student add constraint ck_stu1
check(sex in ('F','M')); alter table student modify sex default 'M';
6
5、查询student表的约束信息，并记录查询结果。 select constraint_name,column_name from user_cons_columns where table_name='STUDENT'; select owner,constraint_name,constraint_type,status from user_constraints where table_name=' STUDENT ';
7
第6章作业
已知表: 学生表：student(sno,sname,sbirth,sclass) 班级表：class(cno,cname,cdept) 系表：department(dno,dname) 其中student(sclass)关联class(cno)， class(cdept)关联department(dno)。
(1000,'Zhangsan',date'1985-2-3'); 5 修改Zhangsan的工资为20部门的最高工资。 update emp set sal=(select max(sal) from emp
where deptno=20) where ename=‘Zhangsan’;
2
6 删除员工名中包含一个“A”并且以“W”结尾的 员工信息。 delete from emp where ename like '%A%W'; 7 统计emp表中每个部门的平均工资和最高工资，并 要求参与统计的部门的平均工资多于1000，少于3000。 select avg(sal),max(sal) from emp group by deptno
t_d table_dept;
begin
for t_d in (select deptno,dname from dept) loop
dbms_output.put_line(t_d.deptno||’’||t_d.dname);
end loop;
end;
12
2.利用显式游标修改表emp中各个雇员的工资，若雇员属于10
end;
16
第7章练习
ex：使用游标与异常处理，完成下列功能。 • 查询dept表中的所有部门号与部门名，并输出。 • 将dept表中部门号为50的部门地址loc改为：上 海。 • 如果部门号不合法，则给出错误提示。 • 如果出现其他错误，给出错误代码和错误文本。
17
declare
cursor e1 is select * from dept;
第3章作业
编写SQL语句完成下列功能，并要求写出第1、3、7题 的
查询结果。 1 查询10号部门员工的员工号以及其领导的员工号，
并以别名“领导员工号”显示列名。 select empno,mgr 领导员工号 from emp where
deptno=10; 2 查询emp表中所有的员工信息，并要求按照部门号升 序排序，相同部门按照工资降序排序。 select * from emp order by deptno,sal desc;
9
3.查询‘计算机系’里1988年以前出生的学生的学号和出 生日期。
1） select sno,sbirth from student where sclass in (select cno from class a,department b where a.cdept=b.dno and d.dname=‘计算机系’ ) and sbirth<date’1988-01-01’; 2） select sno,sbirth from student a,class b,department c where a.sclass=o and b.cdept=c.dno and d.dname=‘计算机系’ and a.sbirth<date’1988-01-01’;
号部门，则增加100，若雇员属于20号部门，则增加200，否则 增加300。
declare
cursor c_emp is select empno,sal,deptno from emp;
begin
for vc in c_=10 then
update emp set sal=sal+100 where empno=vc.empno;
13
3.查询并输出emp表中某一部门的员工号、员工名。如果此部
门号不存在，捕获此异常。如果出现其他异常，给出异常信息。
declare
type r_emp is record(
ENO emp.empno%type，
ENAME emp.ename%type);
type emp_rc is ref cursor return r_emp;
8
编写sql语句完成下列功能。 1.查询班级名为‘1班’的所有学生的学号、姓名。 select sno,sname from student a,class b where a.sclass=o and cname=‘1班‘； 2.查询学号为‘20070001’的学生的姓名和所在班的
班名以及所在系的系名。 select sname,cname,dname from student a,class b,department c where a.sclass=o and b.cdept=c.dno and a.sno=‘20070001’;
10
4.查询比’计算机系‘某一个班的年龄小的学生的学号和出生 日期。
select sno,sbirth from student where sbirth <any (select sbirth from student a,class b,department c where a.sclass=o and b.cdept=c.dno and d.dname=‘计算机系’）; 5.查询‘计算机系’各个班的人数，显示人数和班级号。 select count(*),sclass from student where sclass in (select cno from class a,department b where a.cdept=b.dno and b.dname=‘计算机系’);
=v_deptno;
while i=j loop
fetch v_rc into emp_row;
i:=i+1;
if v_rc%found then
dbms_output.putline(‘员工号：’||emp_row.ENO||’’||’员工名
：’||emp_row.ENAME);
end if;
1、编写过程，用于查询某部门的所有员工的员工号和工资。如果部门号不存在，
提示出错。 create or replace procedure searchempno_sal(v_dno number) as
cursor c_emp is select empno,sal from emp where deptno=v_dno; cc c_emp%rowtype; ex exception; begin open c_emp; fetch c_emp into cc; if c_emp%notfound then
having avg(sal) between 1000 and 3000;
3
第5章作业
编写sql语句完成下面功能。 1、按照下列表结构创建表。
class表
列名
数据类型
约束
cno
number(2)
主键
cname varchar2(20)
create table class( cno number(2) constraint pk_cl primary key, cname varchar2(20));
v_rc emp_rc;
emp_row r_emp;
v_deptno emp.deptno%type;
ex exception;
i number:=0;
j number:=0;
begin
14
v_deptno=&x;
open v_rc for select empno,ename from dept where deptno
4
列名
数据类型
约束
sno
number(4)
主键
sname varchar2(20)
student表 sage
number
唯一
sex
char(2)
cno
number(2)
create table student(
sno number(4) constraint pk_stu primary key,
if j=0 then raise ex;
end if;
end loop;
close v_rc;
15
exception
when ex then
dbms_output.put_line(‘此员工不存在’)；
when others then
dbms_output.put_line(‘异常信息:’||SQLCODE||’’||SQLERROM);
ex_update exception;
begin
for ve in e1 loop
dbms_output.put_line(ve.deptno||' '||ve.dname);
end loop;
update dept set loc='上海' where deptno=50;
if sql%notfound then
sname varchar2(20) constraint un_stu unique,
sage number,
sex char(2),
cno number(2));
5
2、为student表添加外键约束，其cno列参照class表cno 列。
alter table student add constraint fk_stu foreign key(cno) references class(cno); 3、为student表sage列添加检查约束，列值在0-100。 alter table student add constraint ck_stu check(sage between 0 and 100); 4、为student表sex列添加约束，确定其值为’F’或