oracle plsql练习题-考核题
数据库脚本：
– Create table 学生信息
create table HAND_STUDENT
(
STUDENT_NO VARCHAR2(10) not null,
STUDENT_NAME VARCHAR2(20),
STUDENT_AGE NUMBER(2),
STUDENT_GENDER VARCHAR2(5)
);
– Add comments to the table
comment on table HAND_STUDENT
is ‘学生信息表’;
– Add comments to the columns
comment on column HAND_STUDENT.STUDENT_NO
is ‘学号’;
comment on column HAND_STUDENT.STUDENT_NAME
is ‘姓名’;
comment on column HAND_STUDENT.STUDENT_AGE
is ‘年龄’;
comment on column HAND_STUDENT.STUDENT_GENDER
is ‘性别’;
– Create/Recreate primary, unique and foreign key constraints
alter table HAND_STUDENT add primary key (STUDENT_NO);
– Create table 教师信息表
create table HAND_TEACHER
(
TEACHER_NO VARCHAR2(10) not null,
TEACHER_NAME VARCHAR2(20),
MANAGER_NO VARCHAR2(10)
);
– Add comments to the table
comment on table HAND_TEACHER
is ‘教师信息表’;
– Add comments to the columns
comment on column HAND_TEACHER.TEACHER_NO
is ‘教师编号’;
comment on column HAND_TEACHER.TEACHER_NAME
is ‘教师名称’;
comment on column HAND_TEACHER.MANAGER_NO
is ‘上级编号’;
– Create/Recreate primary, unique and foreign key constraints
alter table HAND_TEACHER add primary key (TEACHER_NO);
– Create table 课程信息表
create table HAND_COURSE
(
COURSE_NO VARCHAR2(10) not null,
COURSE_NAME VARCHAR2(20),
TEACHER_NO VARCHAR2(20) not null
);
– Add comments to the table
comment on table HAND_COURSE
is ‘课程信息表’;
– Add comments to the columns
comment on column HAND_COURSE.COURSE_NO
is ‘课程号’;
comment on column HAND_COURSE.COURSE_NAME
is ‘课程名称’;
comment on column HAND_COURSE.TEACHER_NO
is ‘教师编号’;
– Create/Recreate primary, unique and foreign key constraints
alter table HAND_COURSE add constraint PK_COURSE primary key (COURSE_NO, TEACHER_NO);
– Create table 成绩信息表
create table HAND_STUDENT_CORE
(
STUDENT_NO VARCHAR2(10) not null,
COURSE_NO VARCHAR2(10) not null,
CORE NUMBER(4,2)
);
– Add comments to the table
comment on table HAND_STUDENT_CORE
is ‘学生成绩表’;
– Add comments to the columns
comment on column HAND_STUDENT_CORE.STUDENT_NO
is ‘学号’;
comment on column HAND_STUDENT_CORE.COURSE_NO
is ‘课程号’;
comment on column HAND_STUDENT_CORE.CORE
is ‘分数’;
– Create/Recreate primary, unique and foreign key constraints
alter table HAND_STUDENT_CORE add constraint PK_SC primary key (STUDENT_NO, COURSE_NO);
/*初始化学生表的数据**/
insert into HAND_STUDENT values (‘s001’,’张三’,23,’男’);
insert into HAND_STUDENT values (‘s002’,’李四’,23,’男’);
insert into HAND_STUDENT values (‘s003’,’吴鹏’,25,’男’);
insert into HAND_STUDENT values (‘s004’,’琴沁’,20,’女’);
insert into HAND_STUDENT values (‘s005’,’王丽’,20,’女’);
insert into HAND_STUDENT values (‘s006’,’李波’,21,’男’);
insert into HAND_STUDENT values (‘s007’,’刘玉’,21,’男’);
insert into HAND_STUDENT values (‘s008’,’萧蓉’,21,’女’);
insert into HAND_STUDENT values (‘s009’,’陈萧晓’,23,’女’);
insert into HAND_STUDENT values (‘s010’,’陈美’,22,’女’);
commit;
/********初始化教师表*************/
insert into HAND_TEACHER values (‘t001’, ‘刘阳’,”);
insert into HAND_TEACHER values (‘t002’, ‘谌燕’,’t001’);
insert into HAND_TEACHER values (‘t003’, ‘胡明星’,’t002’);
commit;
/*****初始化课程表******************/
insert into HAND_COURSE values (‘c001’,’J2SE’,’t002’);
insert into HAND_COURSE values (‘c002’,’Java Web’,’t002’);
insert into HAND_COURSE values (‘c003’,’SSH’,’t001’);
insert into HAND_COURSE values (‘c004’,’Oracle’,’t001’);
insert into HAND_COURSE values (‘c005’,’SQL SERVER 2005’,’t003’);
insert into HAND_COURSE values (‘c006’,’C#’,’t003’);
insert into HAND_COURSE values (‘c007’,’JavaScript’,’t002’);
insert into HAND_COURSE values (‘c008’,’DIV+CSS’,’t001’);
insert into HAND_COURSE values (‘c009’,’PHP’,’t003’);
insert into HAND_COURSE values (‘c010’,’EJB3.0’,’t002’);
commit;
/*****初始化成绩表*************/
insert into HAND_STUDENT_CORE values (‘s001’,’c001’,58.9);
insert into HAND_STUDENT_CORE values (‘s002’,’c001’,80.9);
insert into HAND_STUDENT_CORE values (‘s003’,’c001’,81.9);
insert into HAND_STUDENT_CORE values (‘s004’,’c001’,60.9);
insert into HAND_STUDENT_CORE values (‘s001’,’c002’,82.9);
insert into HAND_STUDENT_CORE values (‘s002’,’c002’,72.9);
insert into HAND_STUDENT_CORE values (‘s003’,’c002’,81.9);
insert into HAND_STUDENT_CORE values (‘s001’,’c003’,’59’);
commit;
1.查询没学过“谌燕”老师课的同学，显示（学号、姓名）
select out_stu.student_no, out_stu.student_name from hand_student
out_stu where out_stu.student_no not in
(select distinct hs.student_no
from hand_course c,
hand_teacher t,
hand_student hs,
hand_student_core hsc
where c.teacher_no = t.teacher_no
and hs.student_no = hsc.student_no
and hsc.course_no = c.course_no
and t.teacher_name = ‘谌燕’)SELECT hs.student_no, hs.student_name FROM hand_student hs WHERE
NOT EXISTS (SELECT 1
FROM hand_course hc,
hand_teacher ht,
hand_student_core hsc
WHERE hc.teacher_no = ht.teacher_no
AND hc.course_no = hsc.course_no
AND ht.teacher_name = ‘谌燕’
AND hsc.student_no = hs.student_no);
2.查询没有学全所有课的同学，显示（学号、姓名）
select hs.student_no, hs.student_name from hand_student hs,
hand_student_core hsc where hs.student_no = hsc.student_no(+) group
by hs.student_no, hs.student_name having count(hsc.course_no) <
(select count(*) from hand_course c);
3.查询 c001 课程比 c002 课程成绩高的所有学生，显示：学号，姓名
select stu.student_no,stu.student_name from hand_student stu,
(select t1.student_no
from (select *
from hand_student_core sc
where sc.course_no = ‘c001’) t1,
(select *
from hand_student_core sc
where sc.course_no = ‘c002’) t2
where t1.student_no = t2.student_no
and t1.core > t2.core) tt where stu.student_no = tt.student_no
4.按各科平均成绩和及格率的百分数，按及格率高到低的顺序排序，显示（课程号、平均分、及格率）
select hsc.course_no,
avg(hsc.core),
sum(case
when hsc.core > 60 then
1
else
0
end) / count() 100 || ‘%’ pass_rate from hand_student_core hsc group by hsc.course_no order by
to_number(substr(pass_rate, 1, length(pass_rate) - 1)) desc
5.1992年之后出生的学生名单找出年龄最大和最小的同学，显示（学号、姓名、年龄）
SELECT hs.student_no, hs.student_name, hs.student_age FROM
hand_student hs,
(SELECT MAX(hs.student_age) max_age, MIN(hs.student_age) min_age
FROM hand_student hs
WHERE to_number(to_char(SYSDATE, ‘yyyy’)) - hs.student_age > 1992) hh WHERE hs.student_age = hh.max_age
OR hs.student_age = hh.min_age;
6.列出矩阵类型各分数段人数，横轴为分数段[100-85]、[85-70]、[70-60]、[<60]，纵轴为课程号、课程名称（提示使用case when句式）
SELECT hsc.course_no,
hc.course_name,
SUM(CASE
WHEN hsc.core BETWEEN 85 AND 100 THEN
1
ELSE
0
END) AS “[100-85]”,
SUM(CASE
WHEN hsc.core BETWEEN 70 AND 85 THEN
1
ELSE
0
END) AS “[85-70]”,
SUM(CASE
WHEN hsc.core BETWEEN 60 AND 70 THEN
1
ELSE
0
END) AS “[70-60]”,
SUM(CASE
WHEN hsc.core < 60 then
1
ELSE
0
END) AS “[<60]” FROM hand_student_core hsc, hand_course hc WHERE hsc.course_no = hc.course_no GROUP BY hsc.course_no,
hc.course_name;
7.查询各科成绩前三名的记录:(不考虑成绩并列情况)，显示（学号、课程号、分数）
SELECT student_no, course_no, core FROM (SELECT hsc.student_no,
hsc.course_no,
hsc.core,
DENSE_RANK() OVER(PARTITION BY hsc.course_no ORDER BY hsc.core DESC) ranks
FROM hand_student_core hsc) WHERE ranks < 4;
8.查询选修“谌燕”老师所授课程的学生中每科成绩最高的学生，显示（学号、姓名、课程名称、成绩）
SELECT hs.student_no,
hs.student_name,
hc.course_name,
hsc.core FROM hand_student hs,
hand_student_core hsc,
hand_course hc,
hand_teacher ht WHERE hs.student_no = hsc.student_no AND hsc.course_no = hc.course_no AND hc.teacher_no = ht.teacher_no
AND ht.teacher_name = ‘谌燕’ AND hsc.core = (SELECT MAX(sc.core)
FROM hand_student_core sc
WHERE sc.course_no = hc.course_no);
9.查询两门以上不及格课程的同学及平均成绩，显示（学号、姓名、平均成绩（保留两位小数））
SELECT hsc.student_no,
hs.student_name,
ROUND(AVG(hsc.core),2) avg_core FROM hand_student_core hsc,
hand_student hs WHERE EXISTS (SELECT sc.student_no
FROM hand_student_core sc
WHERE sc.core < 60
AND sc.student_no = hsc.student_no
GROUP BY sc.student_no
HAVING COUNT(sc.student_no) > 1) AND hsc.student_no = hs.student_no GROUP BY hsc.student_no,hs.student_name;
10。查询姓氏数量最多的学生名单，显示（学号、姓名、人数）
SELECT hs.student_no, hs.student_name, ht.cnt FROM (SELECT
SUBSTR(hs.student_name, 1, 1) surname,
COUNT(1) cnt,
dense_rank() OVER(ORDER BY COUNT(1) DESC) ranks
FROM hand_student hs
GROUP BY SUBSTR(hs.student_name, 1, 1)) ht,
hand_student hs WHERE SUBSTR(hs.student_name, 1, 1) = ht.surname AND ht.ranks = 1;
11.查询课程名称为“J2SE”的学生成绩信息，90以上为“优秀”、80-90为“良好”、60-80为“及格”、60分以下为“不及格”，显示（学号、姓名、课程名称、成绩、等级）
SELECT hsc.student_no,
hs.student_name,
hc.course_name,
hsc.core,
CASE
WHEN hsc.core >= 90 THEN
‘优秀’
WHEN hsc.core < 90 AND hsc.core >= 80 THEN
‘良好’
WHEN hsc.core < 80 AND hsc.core >= 60 THEN
‘及格’
WHEN hsc.core < 60 THEN
‘不及格’
END core_level FROM hand_student_core hsc,
hand_course hc, hand_student hs WHERE hsc.course_no = hc.course_no AND hsc.student_no = hs.student_no AND
hc.course_name = ‘J2SE’;
12.这是一个树结构，查询教师“胡明星”的所有主管及姓名:（无主管的教师也需要显示），显示（教师编号、教师名称、主管编号、主管名称）
select t.teacher_no,t.teacher_name,t.manager_no ,t2.teacher_name
manager_name from hand_teacher t,hand_teacher t2 where t.manager_no =
t2.teacher_no( start with t.teacher_name=’胡明星’ connect by prior
t.manager_no=t.teacher_no
13.查询分数高于课程“J2SE”中所有学生成绩的学生课程信息，显示（学号，姓名，课程名称、分数）
SELECT hsc.student_no, hs.student_name, hc.course_name, hsc.core
FROM hand_student_core hsc, hand_course hc, hand_student hs WHERE
hsc.course_no = hc.course_no AND hsc.student_no = hs.student_no
AND hsc.core > ALL (SELECT hsc.core
FROM hand_student_core hsc, hand_course hc
WHERE hsc.course_no = hc.course_no
AND hc.course_name = ‘J2SE’) AND hc.course_name != ‘J2SE’;
14.分别根据教师、课程、教师和课程三个条件统计选课的学生数量：（使用rollup),显示（教师名称、课程名称、选课数量）
SELECT ht.teacher_name, hc.course_name, COUNT(1) nums FROM
hand_student_core hsc, hand_teacher ht, hand_course hc WHERE
hsc.course_no = hc.course_no AND hc.teacher_no = ht.teacher_no GROUP
BY ROLLUP(ht.teacher_name,hc.course_name);
15.查询所有课程成绩前三名的按照升序排在最开头，其余数据排序保持默认，显示（学号、成绩）
SELECT hs.student_no, hs.core FROM (SELECT rownum rn,
hsc.student_no,
hsc.core,
row_number() OVER(ORDER BY hsc.core DESC) ranks
FROM hand_student_core hsc) hs ORDER BY CASE
WHEN ranks <= 3 THEN
-ranks
ELSE
null
END,
rn;
这是一次过程考核的题目，考的很糟，继续学习吧。共勉。
