表结构及数据
-- 学生信息表
SELECT *
FROM hand_student;
--教师信息表
SELECT *
FROM hand_teacher;
--课程信息表
SELECT *
FROM hand_course;
--成绩信息表
SELECT *
FROM hand_student_core;
-- 学生信息表
SELECT *
FROM hand_student;
--教师信息表
SELECT *
FROM hand_teacher;
--课程信息表
SELECT *
FROM hand_course;
--成绩信息表
SELECT *
FROM hand_student_core;
--1. 查询没学过“谌燕”老师课的同学,显示(学号、姓名)
--方法一:
SELECT stu.student_no,
stu.student_name
FROM hand_student stu
WHERE stu.student_no NOT IN (SELECT core.student_no
FROM hand_student_core core
WHERE core.course_no IN (SELECT course.course_no
FROM hand_course course
WHERE course.teacher_no IN (SELECT tea.teacher_no
FROM hand_teacher tea
WHERE tea.teacher_name = '谌燕')
AND course.course_no IS NOT NULL));
--方法二:(优先使用)
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 stu.student_no,stu.student_name
FROM hand_student stu
WHERE stu.student_no IN (SELECT core.student_no
FROM hand_student_core core
GROUP BY core.student_no
HAVING COUNT(*) < (SELECT COUNT(*)
FROM hand_course));
--3. 查询“c001”课程比“c002”课程成绩高的所有学生,显示(学号、姓名)
---误解题意
SELECT stu.student_no,
stu.student_name
FROM hand_student stu
WHERE stu.student_no IN (SELECT c2.student_no
FROM hand_student_core c2
WHERE c2.core > (SELECT MAX(c.core)
FROM hand_student_core c
WHERE c.course_no = 'c002')
AND c2.course_no = 'c001');
--正解
SELECT *
FROM (SELECT *
FROM hand_student_core core1
WHERE core1.course_no = 'c001') t1
JOIN (SELECT *
FROM hand_student_core core1
WHERE core1.course_no = 'c002') t2
ON t1.student_no = t2.student_no
WHERE t1.core > t2.core;
--4. 按各科平均成绩和及格率的百分数,按及格率高到低的顺序排序,显示(课程号、平均分、及格率)
--方法一:
WITH aa AS
(SELECT c.course_no,
AVG(c.core) avg1,
COUNT(*) num1
FROM hand_student_core c
GROUP BY c.course_no),
bb AS
(SELECT c2.course_no,
COUNT(*) count1
FROM hand_student_core c2
WHERE c2.core >= 60
GROUP BY c2.course_no)
SELECT aa.course_no, aa.avg1, nvl2(bb.count1 / aa.num1, bb.count1 / aa.num1, 0)
FROM aa
LEFT JOIN bb
ON aa.course_no = bb.course_no
ORDER BY nvl2(bb.count1 / aa.num1, bb.count1 / aa.num1, 0) DESC;
--方法二:(优先使用)
SELECT core1.course_no,
AVG(core1.core),
COUNT(CASE
WHEN core1.core >= 60 THEN
'X'
END) / COUNT(*) rate
FROM hand_student_core core1
GROUP BY core1.course_no order by rate desc;
--5. 1992年之后出生的学生名单找出年龄最大和最小的同学,显示(学号、姓名、年龄)
--方法一:
WITH maxage AS
(SELECT MAX(stu.student_age)
FROM hand_student stu
WHERE 2018 - stu.student_age > 1992),
minage AS
(SELECT MIN(stu.student_age)
FROM hand_student stu
WHERE 2018 - stu.student_age > 1992)
SELECT s.student_no,s.student_name,s.student_age
FROM hand_student s
WHERE s.student_age = (SELECT *
FROM maxage) OR s.student_age = (SELECT *
FROM minage);
--方法二:(答案)
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 course.course_no,
course.course_name,
COUNT(CASE
WHEN core1.core >= 85
AND core1.core < 100 THEN
'X'
END) "[100-85]",
COUNT(CASE
WHEN core1.core >= 70
AND core1.core < 85 THEN
'X'
END) "[85-70]",
COUNT(CASE
WHEN core1.core >= 60
AND core1.core < 70 THEN
'X'
END) "[70-60]",
COUNT(CASE
WHEN core1.core < 60 THEN
'X'
END) "[<60]"
FROM hand_student_core core1
JOIN hand_course course
ON core1.course_no = course.course_no
GROUP BY course.course_no,
course.course_name;
--7. 查询各科成绩前三名的记录:(不考虑成绩并列情况),显示(学号、课程号、分数)
SELECT *
FROM (SELECT core1.student_no,
core1.course_no,
core1.core,
row_number() over(PARTITION BY core1.course_no ORDER BY core1.core DESC) rn
FROM hand_student_core core1) t
WHERE t.rn <= 3;
--8. 查询选修“谌燕”老师所授课程的学生中每科成绩最高的学生,显示(学号、姓名、课程名称、成绩)
--方法一:
SELECT t.student_no,
t.student_name,
t.course_name,
t.core
FROM (SELECT stu.student_no,
stu.student_name,
course.course_name,
core1.core,
dense_rank() over(PARTITION BY core1.course_no ORDER BY core1.core DESC) rn
FROM hand_student_core core1
JOIN hand_student stu
ON core1.student_no = stu.student_no
JOIN hand_course course
ON core1.course_no = course.course_no
WHERE course.teacher_no IN (SELECT tea2.teacher_no
FROM hand_teacher tea2
WHERE tea2.teacher_name = '谌燕')) t
WHERE t.rn = 1;
--方法二:(答案)
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);
--方法三:(优先使用)
SELECT core1.student_no,
core1.course_no,
core1.core
FROM hand_student_core core1
JOIN hand_course course
ON core1.course_no = course.course_no
JOIN hand_teacher tea
ON course.teacher_no = tea.teacher_no
WHERE core1.core = (SELECT MAX(core2.core)
FROM hand_student_core core2
WHERE core1.course_no = core2.course_no)
AND tea.teacher_name = '谌燕';
--9. 查询两门以上不及格课程的同学及平均成绩,显示(学号、姓名、平均成绩(保留两位小数))
--方法一:
WITH aa AS
(SELECT c.student_no
FROM hand_student_core c
WHERE c.core < 60
GROUP BY c.student_no
HAVING COUNT(*) >= 2)
SELECT c.student_no, stu.student_name, AVG(c.core)
FROM hand_student_core c
JOIN hand_student stu
ON c.student_no = stu.student_no
WHERE c.student_no IN (
SELECT *
FROM aa)
GROUP BY c.student_no,stu.student_name;
--方法二:(答案)
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;
--方法三:(不完整)
SELECT distinct(core1.student_no)
FROM hand_student_core core1
WHERE 2 <= (SELECT COUNT(1)
FROM hand_student_core core2
WHERE core1.student_no = core2.student_no
AND core2.core < 60);
--10. 查询姓氏数量最多的学生名单,显示(学号、姓名、人数)
--方法一:
SELECT *
FROM hand_student s
WHERE substr(s.student_name,
1,
1) = (SELECT t.name1
FROM (SELECT substr(stu.student_name,
1,
1) name1,
COUNT(*)
FROM hand_student stu
GROUP BY substr(stu.student_name,
1,
1)
ORDER BY COUNT(*) DESC) t
WHERE rownum = 1);
--方法二:(优先使用)
SELECT *
FROM hand_student s
WHERE substr(s.student_name,
1,
1) in (SELECT t.name1
FROM (SELECT substr(stu.student_name,
1,
1) name1,
COUNT(*),
dense_rank() OVER(ORDER BY COUNT(*) DESC) ranks
FROM hand_student stu
GROUP BY substr(stu.student_name,
1,
1)
)t where t.ranks=1);
--11. 查询课程名称为“J2SE”的学生成绩信息,90以上为“优秀”、80-90为“良好”、60-80为“及格”、60分以下为“不及格”,显示(学号、姓名、课程名称、成绩、等级)
SELECT stu.student_no,
stu.student_name,
course.course_name,
CASE
WHEN core1.core > 90 THEN
'优秀'
WHEN core1.core > 80 THEN
'良好'
WHEN core1.core > 60 THEN
'及格'
WHEN core1.core < 60 THEN
'不及格'
END
FROM hand_student_core core1
JOIN hand_student stu
ON core1.student_no = stu.student_no
JOIN hand_course course
ON core1.course_no = course.course_no
WHERE course.course_name = 'J2SE';
--12. 这是一个树结构,查询教师“胡明星”的所有主管及姓名:(无主管的教师也需要显示),显示(教师编号、教师名称、主管编号、主管名称)
WITH aa AS
(SELECT *
FROM hand_teacher tea
START WITH tea.teacher_name = '胡明星'
CONNECT BY PRIOR tea.manager_no = tea.teacher_no)
SELECT aa.teacher_no, aa.teacher_name, aa.manager_no, t.teacher_no
FROM aa
LEFT JOIN hand_teacher t
ON aa.manager_no = t.teacher_no;
--13. 查询分数高于课程“J2SE”中所有学生成绩的学生课程信息,显示(学号,姓名,课程名称、分数)
SELECT stu.student_no,
stu.student_name,
course.course_name,
core2.core
FROM hand_student_core core2
JOIN hand_student stu
ON core2.student_no = stu.student_no
JOIN hand_course course
ON core2.course_no = course.course_no
WHERE core2.core > (SELECT MAX(core1.core)
FROM hand_student_core core1
WHERE core1.course_no = (SELECT course.course_no
FROM hand_course course
WHERE course.course_name = 'J2SE'));
--14. 分别根据教师、课程、教师和课程三个条件统计选课的学生数量:(使用rollup),显示(教师名称、课程名称、选课数量)
SELECT tea.teacher_name,
course.course_name,
COUNT(*)
FROM hand_student_core core1
JOIN hand_course course
ON core1.course_no = course.course_no
JOIN hand_teacher tea
ON course.teacher_no = tea.teacher_no
GROUP BY ROLLUP(tea.teacher_name,
course.course_name);
--15. 查询所有课程成绩前三名的按照升序排在最开头,其余数据排序保持默认(7分),显示(学号、成绩)
--测试
SELECT *
FROM (SELECT core1.student_no,
core1.course_no,
core1.core,
row_number() over(PARTITION BY core1.course_no ORDER BY core1.core DESC) rn
FROM hand_student_core core1) t where rn =3;
--正解
SELECT *
FROM (SELECT HSC.STUDENT_NO,
HSC.COURSE_NO,
HSC.CORE,
ROW_NUMBER() OVER(PARTITION BY HSC.COURSE_NO ORDER BY HSC.CORE DESC) RANKS
FROM HAND_STUDENT_CORE HSC) HS
WHERE RANKS <= 3
UNION ALL
SELECT *
FROM (SELECT HSC.STUDENT_NO,
HSC.COURSE_NO,
HSC.CORE,
ROW_NUMBER() OVER(PARTITION BY HSC.COURSE_NO ORDER BY HSC.CORE DESC) RANKS
FROM HAND_STUDENT_CORE HSC) HS
WHERE RANKS > 3