1、用一条SQL语句查询出每门课都大于80的学生的姓名
(1)SELECT NAME FROM score GROUP BY NAME HAVING MIN(grade) > 80
(2)SELECT DISTINCT NAME FROM score WHERE NAME NOT IN
(SELECT NAME FROM score GROUP BY NAME HAVING MIN(grade) <= 80)
2、还是上个表,查询平均成绩大于80的学生的姓名
(1)SELECT NAME FROM score GROUP BY NAME HAVING AVG(grade) > 80
(2)SELECT DISTINCT NAME FROM score WHERE NAME NOT IN
(SELECT NAME FROM score GROUP BY NAME HAVING AVG(grade)< 80)
3、查询student表中重名的学生,结果包含id和name,按name和id升序::
表结构:
学生表:student(id,name)
课程表:course(id,name)
学生课程表:student_course(sid,cid,score)
查询student表中重名的学生,结果包含id和name,按name和id升序:
注意:asc为升序排列(从小到大),默认;desc为降序排列(从大到小)
SELECT id, NAME FROM student WHERE NAME IN
(SELECT NAME FROM student GROUP BY NAME HAVING COUNT(*) >1) ORDER BY NAME ;
4、还是上表:在student_course中查询平均分不及格的学生,列出学生id和平均分:
(1)SELECT sid,AVG(score) FROM student_course GROUP BY sid HAVING AVG(score) < 60
(2)为平均分字段加上名字:
SELECT sid,AVG(score) AS avg_score FROM student_course GROUP BY sid HAVING avg_score < 60
5、在student_course表中查询每门课成绩都不低于80的学生id:
(1)SELECT sid FROM student_course GROUP BY sid HAVING MIN(score) >= 80
(2)SELECT DISTINCT sid FROM student_course WHERE sid NOT IN
(SELECT sid FROM student_course WHERE score < 80)
6、查询每个学生的总成绩,结果列出学生姓名和总成绩:
SELECT NAME,SUM(score) FROM student,student_course WHERE student.id = student_course.sid GROUP BY sid;
7、总成绩最高的学生,列出学生id的总成绩:
SELECT sid,SUM(score) AS s FROM student_course GROUP BY sid ORDER BY s DESC LIMIT 1
8、在student_course表查询课程1成绩第2高的学生,如果第2高的不止一个则列出所有的学生:
SELECT * FROM student_course WHERE cid = 1 AND score =
(SELECT score FROM student_course WHERE cid = 1 ORDER BY score DESC LIMIT 1,1)
解析:先查询出成绩第二高的成绩,然后再查询学生信息
9、查询各科成绩最高的学生,列出学生id,课程id和对应成绩:
SELECT * FROM student_course AS s WHERE score>=(SELECT MAX(score) FROM student_course WHERE cid=s.cid);
10、在student_course表中查询每门课的前2名,结果按课程id升序,同一课程按成绩降序 :
SELECT * FROM student_course X WHERE
(SELECT COUNT(*) FROM student_course Y WHERE y.cid=x.cid AND y.score>x.score) <2
ORDER BY cid,score DESC;
解析:
这也是一个相关嵌套查询,对于每一个分数,如果同一门课程下只有0个、1个分数比这个分数还高,那么这个分数肯定是前2名之一