考核题整理

第1题
条件：查询成绩90分以上（包含90分）的学生课程成绩明细
显示：学生学号、学生姓名、课程编号、课程名称、成绩、老师编号、老师姓名
排序：学生学号、课程编号、成绩
说明：老师编号和老师姓名是指该课程对应的老师
分值（5分）
select STUDENT_NO,
STUDENT_NAME,
COURSE_NO,
COURSE_NAME,
CORE,
TEACHER_NO,
COURSE_NAME
from HAND_STUDENT hs natural
join HAND_STUDENT_CORE hsc natural
join HAND_COURSE hc
where core > 90
order by STUDENT_NO, COURSE_NO, CORE

运行结果：

第2题
条件：查询老师及其对应的上级老师信息
显示：老师编号、老师姓名、上级老师编号、上级老师姓名
排序：老师编号
说明：如果没有上级老师的，上级老师编号和上级老师姓名放空；上级老师是指直接上级
分值：（5分）
select ht_1.TEACHER_NO,
ht_1.TEACHER_NAME,
ht_1.MANAGER_NO,
ht_2.TEACHER_NAME MANAGER_NAME
from HAND_TEACHER ht_1
LEFT JOIN HAND_TEACHER ht_2
ON ht_1.manager_no = ht_2.TEACHER_NO
ORDER BY ht_1.manager_no

第3题
条件：查询出生日期最早的男同学和出生日期最晚的女同学
显示：学号、姓名、性别、出生日期
排序：学号
说明：如果出生最早的男同学有多个出生日期相同，则一起查出来，如果出生最晚的女同学有多个出生日期相同，也一起查出来
分值：（10分）
select STUDENT_NO, STUDENT_NAME, STUDENT_GENDER, BIRTH_DATE
from HAND_STUDENT hs
where (STUDENT_GENDER, BIRTH_DATE) in
(select STUDENT_GENDER, min(BIRTH_DATE)
from HAND_STUDENT hs
where STUDENT_GENDER = ‘男’
group by STUDENT_GENDER)
union
select STUDENT_NO, STUDENT_NAME, STUDENT_GENDER, BIRTH_DATE
from HAND_STUDENT hs
where (STUDENT_GENDER, BIRTH_DATE) in
(select STUDENT_GENDER, max(BIRTH_DATE)
from HAND_STUDENT hs
where STUDENT_GENDER = ‘女’
group by STUDENT_GENDER)
 order by STUDENT_NO

第4题
条件：查询所有同学课程c001/c002/c003/c004四个课程的成绩
显示：学号、姓名、c001成绩、c002成绩、c003成绩、c004成绩
排序：学号
说明：一个同学只显示一行，一个同学有多个课程成绩，则放在同一行，如果没有该课程则放空，有该课程但是成绩为0则放0
分值：（10分）

with st_core as
(select hs.student_no, hs.student_name, hsc.course_no, hsc.core
from HAND_STUDENT hs LEFT OUTER
join HAND_STUDENT_CORE hsc ON hsc.student_no=hs.student_no ),
st_score_dende as
(select student_no,
student_name,
decode(course_no, ‘c001’, core) “C1”,
decode(course_no, ‘c002’, core) “C2”,
decode(course_no, ‘c003’, core) “C3”,
decode(course_no, ‘c004’, core) “C4”
from st_core)
select student_no,
MAX(C1) “C1_CORE”,
MAX(C2) “C2_CORE”,
MAX(C3) “C3_CORE”,
MAX(C4) “C4_CORE”
from st_score_dende
group by student_no
order by student_no