数据分析学习课程1-----SQL------Day4例题详解

11 查询和‘002’号的同学学习的课程完全相同的其他同学学号姓名

SELECT
	score.student_id,
	student.sname 
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid 
WHERE
	student_id != 1 
	AND course_id IN ( SELECT course_id FROM score WHERE score.student_id = 1 ) 
GROUP BY
	student_id

12 查询学习张三老师课的SC表记录

SELECT
	* 
FROM
	score 
WHERE
	score.course_id IN (
	SELECT
		cid 
	FROM
		teacher
		LEFT JOIN course ON teacher.tid = course.teacher_id 
	WHERE
		teacher.tname = "李平老师" 
	)

13向sc表中插入一些记录，这些记录要求符合以下条件 没有上过编号002课程的同学的学号，插入003号课程的平均成绩

INSERT INTO score (student_id, course_id, num)  
SELECT   
    s.student_id,  
    3 AS course_id,  
    c.avg_num  
FROM   
    (SELECT DISTINCT student_id  
     FROM score  
     WHERE student_id NOT IN (  
         SELECT student_id  
         FROM score  
         WHERE course_id = 3  
     )) s  
CROSS JOIN  
    (SELECT AVG(num) AS avg_num  
     FROM score  
     WHERE course_id = 3) c;

14按平均成绩从低到高显示所有学生的语文数学英语 三门课的成绩，按如下形式显示：学生id，语文数学，英语，有效课程数，有效平均分

SELECT    
    student_id,  
    COUNT(DISTINCT course_id) AS '有效课程数', 
    MAX(CASE WHEN course_id = 1 THEN num ELSE 0 END) AS '语文',  
    MAX(CASE WHEN course_id = 2 THEN num ELSE 0 END) AS '数学',  
    MAX(CASE WHEN course_id = 3 THEN num ELSE 0 END) AS '英语',  
    (  
        (MAX(CASE WHEN course_id = 1 THEN num ELSE 0 END) +  
         MAX(CASE WHEN course_id = 2 THEN num ELSE 0 END) +  
         MAX(CASE WHEN course_id = 3 THEN num ELSE 0 END)) /  
        COUNT(DISTINCT course_id)  
    ) AS '平均分'  
FROM   
    score  
GROUP BY   
    student_id  

Case when 条件 then 取值else取值

15 查询各科成绩最高和最低分：以如下形式显示：课程ID，最高分，最低分，如果最低分小于10分则显示0

SELECT
	course_id,
	max( num ),
	min( num ),
CASE
		
		WHEN MIN( num )< 10 THEN
		0 ELSE MIN( num ) 
	END AS 'min' 
FROM
	score 
GROUP BY
	course_id

16 按各科平均成绩从低到高和及格率的百分数从高到低顺序

SELECT
	course_id,
	avg( num ),
	SUM( CASE WHEN num < 60 THEN 0 ELSE 1 END )/ SUM( 1 ) AS '及格率' 
FROM
	score 
GROUP BY
	score.course_id 
ORDER BY
	AVG(
	num)

17 课程平均分从高到低（显示课程任教老师）

SELECT
	course_id,
	SUM( CASE WHEN num < 60 THEN 0 ELSE 1 END )/ SUM( 1 ) AS '及格率',
	teacher.tname 
FROM
	score
	LEFT JOIN course ON score.course_id = course.cid
	LEFT JOIN teacher ON course.teacher_id = teacher.tid 
GROUP BY
	course_id 
ORDER BY
	SUM( CASE WHEN num < 60 THEN 0 ELSE 1 END )/ SUM(
	1 
	)

18 计算各科成绩的前两名

注：找到第一名和第三名，分数比第三名高的就是前两名（避免重分）

SELECT
	* 
FROM
	(
	SELECT
		student_id,
		course_id,
		num,
		1,(
		SELECT
			num 
		FROM
			score AS s2 
		WHERE
			s2.course_id = s1.course_id 
		GROUP BY
			s2.num 
		ORDER BY
			s2.num DESC 
			LIMIT 0,
			1 
		) AS '第一名',
		(
		SELECT
			num 
		FROM
			score AS s2 
		WHERE
			s2.course_id = s1.course_id 
		GROUP BY
			s2.num 
		ORDER BY
			s2.num DESC 
			LIMIT 1,
			1 
		) AS '第三名' 
	FROM
		score AS s1 
	) AS B 
WHERE
	B.num > B.第三名

19 查询每门课程被选修的学生数

select course_id,count(1)as '选修人数' from  score  GROUP BY course_id

20 查询只选修了一门课程的的全部学生的学号和姓名

select student_id from score GROUP BY student_id HAVING count(1)=1