SQL练习

SQL练习

一、查询没学过“谌燕”老师课的同学，显示（学号、姓名）

SELECT
	s2.student_no,
	s2.student_name 
FROM
	hand_student s2 
WHERE
	s2.student_no NOT IN (
	SELECT DISTINCT
		s.student_no 
	FROM
		hand_student s,
		hand_student_core sc 
	WHERE
		s.student_no = sc.student_no 
	AND sc.course_no IN ( SELECT c.course_no FROM hand_teacher t JOIN hand_course c ON c.teacher_no = t.teacher_no AND t.teacher_name = '谌燕' ) 
	)

使用了很傻的distinct，贴出来为了让总结反省

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

二、查询没有学全所有课的同学，显示（学号、姓名）

SELECT
	hs.student_no,
	hs.student_name 
FROM
	hand_student hs
	LEFT JOIN hand_student_core hsc ON hs.student_no = hsc.student_no 
GROUP BY
	hs.student_no,
	hs.student_name 
HAVING
	count( hsc.course_no ) < (SELECT	count( hc.course_no ) FROM hand_course hc)

三、查询“c001”课程比“c002”课程成绩高的所有学生，显示（学号、姓名）

select hs.student_no,hs.student_name from hand_student hs,
(select * from hand_student_core hsc1 where hsc1.course_no='c001') shsc1,
(select * from hand_student_core hsc2 where hsc2.course_no='c002') shsc2
where 
shsc1.student_no = shsc2.student_no
and shsc1.core>shsc2.core
and shsc1.student_no = hs.student_no

四、按各科平均成绩和及格率的百分数，按及格率高到低的顺序排序，显示（课程号、平均分、及格率）

select course_no,avg(core),
sum(case when core>60 then 1 else 0 end)/count(*)*100 || '%'
from hand_student_core
group by course_no  order by sum(case when core>60 then 1 else 0 end) desc

五、1992年之后出生的学生名单找出年龄最大和最小的同学，显示（学号、姓名、年龄)

SELECT
	hs.student_no,
	hs.student_name,
	hs.student_age 
FROM
	hand_student hs,
	(
	SELECT
		max( hs2.student_age ) max_age,
		min( hs2.student_age ) min_age 
	FROM
		hand_student hs2 
	WHERE
		extract( year FROM SYSDATE ) - hs2.student_age > 1992 
	) mm_stu 
WHERE
	hs.student_age = mm_stu.max_age 
	OR hs.student_age = mm_stu.min_age

六、统计列出矩阵类型各分数段人数，横轴为分数段[100-85]、[85-70]、[70-60]、[<60]，纵轴为课程号、课程名称（提示使用case when句式）

 select
 hc.course_no,
 hc.course_name,
	sum(case when hsc.core BETWEEN 85 and 100 then 1 else 0 end) "[100-85]",sum(
		 case when hsc.core BETWEEN 70 and 85 then 1 else 0 end
	) "[85-70]",
	sum(
		 case when hsc.core BETWEEN 60 and 70 then 1 else 0 end
	) "[70-60]",
	sum(
		 case when hsc.core < 60 then 1 else 0 end
	) "[<60]"

 from hand_course hc,
 hand_student_core hsc  
 where hsc.course_no = hc.course_no
 group by hc.course_name,hc.course_no

七、查询各科成绩前三名的记录:(不考虑成绩并列情况)，显示（学号、课程号、分数)

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;

https://www.cnblogs.com/yeshadow937/p/6112202.html

八、查询选修“谌燕”老师所授课程的学生中每科成绩最高的学生，显示（学号、姓名、课程名称、成绩）

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
	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 hs.student_no,hs.student_name,hs_rank.snum 
from 
hand_student hs,
(select substr(student_name,1,1) sname,count(1) snum,dense_rank() over(order by count(1) desc) rank from hand_student
group by substr(student_name,1,1)) hs_rank
where  substr(student_name,1,1) = hs_rank.sname and hs_rank.rank=1 

十一、查询课程名称为“J2SE”的学生成绩信息，90以上为“优秀”、80-90为“良好”、60-80为“及格”、60分以下为“不及格”，显示（学号、姓名、课程名称、成绩、等级)

SELECT hs.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 class
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';

SELECT
	ht1.teacher_no,
	ht1.teacher_name,
	ht1.manager_no,
	ht2.teacher_name manager_name 
FROM
	hand_teacher ht1
	LEFT JOIN hand_teacher ht2 ON ht1.manager_no = ht2.teacher_no START WITH ht1.teacher_name = '胡明星' CONNECT BY PRIOR ht1.manager_no = ht1.teacher_no

十三、查询分数高于课程“J2SE”中所有学生成绩的学生课程信息，显示（学号，姓名，课程名称、分数)

SELECT
	hs.student_no,
	hs.student_name,
	hc.course_name,
	hsc.core 
FROM
	hand_student hs,
	hand_course hc,
	hand_student_core hsc 
WHERE
	hc.course_no = hsc.course_no 
	AND hsc.student_no = hs.student_no 
	AND hsc.core > ALL (
	SELECT
		hsc2.core 
	FROM
		hand_student_core hsc2,
		hand_course hc2 
	WHERE
		hsc2.course_no = hc2.course_no 
	AND hc2.course_name = 'J2SE' 
	)

十四、分别根据教师、课程、教师和课程三个条件统计选课的学生数量：（使用rollup),显示（教师名称、课程名称、选课数量)

SELECT ht.teacher_name,
       hc.course_name,
       count(1) num
  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);

十五、查询所有课程成绩前三名的按照升序排在最开头，其余数据排序保持默认（7分）,显示（学号、成绩）编号

SELECT hss.student_no,hss.core
FROM (SELECT hsc.student_no,hsc.core,row_number() OVER(ORDER BY hsc.core DESC) rank,rownum rnum FROM hand_student_core hsc) hss
ORDER BY CASE WHEN rank<=3 THEN -rank ELSE null END,
rnum