mysql基础练习题19题

###题目
练习sql下载：
链接：https://pan.baidu.com/s/1tCA7s3nN9r1ia5qTni0trQ
提取码：yyds
1、查询每门课程被选修的学生数；
2、查询出只选修了一门课程的全部学生的学号和姓名；
3、查询男生、女生的人数；
4、查询姓“张”的学生名单；
5、查询同名同姓学生名单，并统计同名人数；
6、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
7、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；
8、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
9、查询课程编号为3且课程成绩在80分以上的学生的学号和姓名；
10、求选了课程的学生人数
11、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；
12、查询各个课程及相应的选修人数；
13、查询每门课程成绩最好的前两名；
14、检索至少选修两门课程的学生学号；
15、查询全部学生都选修的课程的课程号和课程名；
16、查询没学过“叶平”老师讲授的任一门课程的学生姓名；
17、查询两门以上不及格课程的同学的学号及其平均成绩；
18、检索编号为“4”的课程分数小于60，按分数降序排列的同学学号；
19、删除学号为“2”的同学的课程编号为“1”的成绩；

###答案

– 1、查询每门课程被选修的学生数；

SELECT course_id,COUNT(*) FROM score GROUP BY course_id;

– 2、查询出只选修了一门课程的全部学生的学号和姓名；

SELECT student_id,sname FROM student 
LEFT JOIN score ON student_id = student.sid
GROUP BY student_id HAVING COUNT(*) = 1;

– 3、查询男生、女生的人数；

SELECT gender,COUNT(*) FROM student GROUP BY gender;

– 4、查询姓“张”的学生名单；

SELECT * FROM student WHERE sname LIKE '张%';

– 5、查询同名同姓学生名单，并统计同名人数；

SELECT sname,COUNT(*) FROM student GROUP BY sname HAVING COUNT(*)>1;

– 6、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；

SELECT course_id,cname,AVG(result)FROM course 
LEFT JOIN score ON course_id = cid
GROUP BY course_id ORDER BY AVG(result),course_id DESC;

–7、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；

SELECT student_id,sname,AVG(result) FROM student 
LEFT JOIN score ON student_id = student.sid 
GROUP BY student_id HAVING AVG(result) > 85;

– 8、查询课程名称为“数学”，且分数低于60的学生姓名和分数；

SELECT sname,result FROM score 
LEFT JOIN student ON student_id = student.sid
LEFT JOIN course ON course_id = cid
WHERE cname = '数学' AND result < 60;

– 9、查询课程编号为3且课程成绩在80分以上的学生的学号和姓名；

SELECT student_id,sname,course_id,cname,result FROM score 
LEFT JOIN student ON student_id = student.sid
LEFT JOIN course ON course_id = cid
WHERE course_id = 3;

– 10、求选了课程的学生人数

SELECT COUNT(*) FROM (
	SELECT student_id FROM score GROUP BY student_id HAVING COUNT(*) > 0
) tmp;

– 11、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；

SELECT sname,result FROM course 
LEFT JOIN teacher ON teacher_id = tid
LEFT JOIN(
	SELECT sname,result,course_id FROM student 
	LEFT JOIN score ON student_id = student.sid
)tmp ON tmp.course_id = cid 
WHERE tname = '杨艳' ORDER BY result DESC LIMIT 1;

– 12、查询各个课程及相应的选修人数；

SELECT course_id,COUNT(*) FROM score GROUP BY course_id;

– 13、查询每门课程成绩最好的前两名；

SELECT course_id,student_id,result FROM score tmp
WHERE (
	SELECT COUNT(*) FROM score WHERE tmp.course_id = score.course_id
	AND tmp.result < score.result
) < 2 ORDER BY tmp.course_id, tmp.result DESC;

– 14、检索至少选修两门课程的学生学号；

SELECT student_id FROM score GROUP BY student_id HAVING COUNT(*) >= 2;

– 15、查询全部学生都选修的课程的课程号和课程名；

SELECT course_id,cname FROM score 
LEFT JOIN course ON course_id = cid 
GROUP BY course_id 
HAVING COUNT(*) = (
	SELECT COUNT(*) FROM student
)

– 16、查询没学过“叶平”老师讲授的任一门课程的学生姓名；

SELECT sname FROM student 
LEFT JOIN score ON student.sid = student_id
WHERE student_id NOT IN (
	SELECT student_id FROM score WHERE course_id IN(
		SELECT cid FROM course 
		LEFT JOIN teacher ON teacher_id = tid
		WHERE tname = '叶平'
)
)

– 17、查询两门以上不及格课程的同学的学号及其平均成绩；

SELECT student_id,AVG(result) FROM score 
WHERE result < 60 
GROUP BY student_id 
HAVING COUNT(*) >= 2

– 18、检索编号为“4”的课程分数小于60，按分数降序排列的同学学号；

SELECT student_id,course_id,result FROM score 
WHERE course_id = 4 ORDER BY result DESC;

– 19、删除学号为“2”的同学的课程编号为“1”的成绩；

DELETE FROM score WHERE student_id = 2 AND course_id = 1