oracleplsql练习题-考核题

数据库脚本：

– 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 BYhsc.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.co

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

这是⼀次过程考核的题⽬，考的很糟，继续学习吧。共勉。