SQL试题总结

SQL 练习题及答案

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

				
SELECT
	hs.STUDENT_NO,hs.STUDENT_NAME
FROM
	test_student hs
WHERE
	 NOT EXISTS(
		SELECT
			'A'
		FROM
			test_course hc LEFT JOIN test_teacher ht
		ON	
			hc.teacher_no = ht.teacher_no
		LEFT JOIN test_student_core hsc
		ON
			hc.COURSE_NO = hsc.COURSE_NO
		WHERE
			hs.STUDENT_NO = hsc.STUDENT_NO AND ht.TEACHER_NAME = '谌燕');

2.查询没有学全所有课的同学，显示（学号、姓名）（7分）

SELECT
	hs.STUDENT_NO,hs.STUDENT_NAME
FROM
	test_student hs LEFT JOIN test_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 test_course hc);

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

SELECT
	hs.STUDENT_NO,hs.STUDENT_NAME
FROM
	test_student hs 
WHERE (
		SELECT
			hsc.CORE
		FROM
			test_student_core hsc
		WHERE
			hsc.STUDENT_NO =hs.STUDENT_NO AND hsc.COURSE_NO = 'c001') > 
			(
			SELECT
			hsc.CORE
		FROM
			test_student_core hsc
		WHERE
			hsc.STUDENT_NO =hs.STUDENT_NO AND hsc.COURSE_NO = 'c002')
			

​

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

count只有在遇到NULL时候才不统计数字，COUNT（false) = 1；而 COUNT（NULL) = 0。

SELECT
	hsc.COURSE_NO,
	ROUND( AVG( hsc.core ), 2 ),
	CONCAT( ROUND( SUM( CASE WHEN hsc.core >= 60 THEN 1 ELSE 0 END ) / COUNT(*) * 100, 2 ), '%' ) 
FROM
	test_student_core hsc 
GROUP BY
	hsc.COURSE_NO 
ORDER BY
	SUM( CASE WHEN hsc.CORE >= 60 THEN 1 ELSE 0 END ) / COUNT( hsc.CORE ) DESC

5.1992年之后出生的学生名单找出年龄最大和最小的同学，显示（学号、姓名、年龄）（7分）

having和group by 必须成对出现

SELECT
	hs.STUDENT_NO,
	hs.STUDENT_NAME,
	hs.STUDENT_AGE 
FROM
	test_student hs 
WHERE
	hs.STUDENT_AGE < TIMESTAMPDIFF(
		YEAR,
		DATE_FORMAT( '1992-01-01 00:00:00', '%Y-%m-%d' ),
	DATE_FORMAT( NOW(), '%Y-%m-%d' )) 
	AND (
		hs.STUDENT_AGE = ( SELECT MAX( hs.STUDENT_AGE ) FROM test_student hs ) 
	OR hs.STUDENT_AGE = ( SELECT MIN( hs.STUDENT_AGE ) FROM test_student hs ))

6.统计列出矩阵类型各分数段人数，横轴为分数段[100-85]、[85-70]、[70-60]、[<60]，纵轴为课程号、课程名称（7分）

SELECT
	test_course.course_no,
	test_course.course_name,
	sum( CASE WHEN test_student_core.core BETWEEN 85 AND 100 THEN 1 ELSE 0 END ) '[100-85]',
	sum( CASE WHEN test_student_core.core BETWEEN 70 AND 85 THEN 1 ELSE 0 END ) '[85-70]',
	sum( CASE WHEN test_student_core.core BETWEEN 60 AND 70 THEN 1 ELSE 0 END ) '[70-60]',
	sum( CASE WHEN test_student_core.core < 60 THEN 1 ELSE 0 END ) '[<60]' 
FROM
	test_course,
	test_student_core 
WHERE
	test_student_core.course_no = test_course.course_no 
GROUP BY
	test_course.course_name,
	test_course.course_no

7.查