MySql经典语句练习50题 —- 30 ~ 39题
目录
32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列:
33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩:
34、查询课程名称为"数学",且分数低于60的学生姓名和分数:
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数:
30、查询同名同性学生名单,并统计同名人数:
SELECT s_name, COUNT(*) num
FROM student
GROUP BY s_name, s_sex
HAVING COUNT(s_name) >= 2;
31、查询1990年出生的学生名单:
SELECT *
FROM student
WHERE s_birth LIKE "1990%";
32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列:
SELECT AVG(s_score)
FROM score
GROUP BY c_id
ORDER BY AVG(s_score) DESC, c_id;
33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩:
SELECT student.s_id, s_name, AVG(s_score)
FROM student, score
WHERE student.s_id = score.s_id
GROUP BY student.s_id
HAVING AVG(s_score) >= 85;
34、查询课程名称为"数学",且分数低于60的学生姓名和分数:
SELECT s_name, s_score
FROM student, course, score
WHERE course.c_name = "数学" AND course.c_id = score.c_id AND student.s_id = score.s_id AND score.s_score < 60;
35、查询所有学生的课程及分数情况:
SELECT *
FROM score;
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数:
SELECT student.s_name, c_name, s_score
FROM student, score, course
WHERE student.s_id = score.s_id AND score.s_score > 70 AND course.c_id = score.c_id;
37、查询不及格的课程:
SELECT student.s_name, c_name, s_score
FROM student, score, course
WHERE student.s_id = score.s_id AND score.s_score <60 AND course.c_id = score.c_id;
38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名:
SELECT student.s_id, s_score
FROM student, score
WHERE score.c_id = '01' AND score.s_score > 80 AND score.s_id = student.s_id;
39、求每门课程的学生人数:
SELECT c_id, COUNT(*)
FROM score
GROUP BY c_id;
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
THE END
二维码