mysql基础练习题19题

###题目
练习sql下载:
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
上一篇:java中的关联


下一篇:MySQL——一对多和多对多简单模型建表