###题目
练习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