mysql复杂查询选择题_复杂查询优质习题

#1)查询选修了 Oracle 的学生姓名；

SELECT t1.`name`

FROM stu t1,course t2,score t3

WHERE t1.`id`=t3.`stu_id` AND t2.`id`=t3.`course_id`

AND t2.`name`='Oracle';

#2)查询 姜振国 同学选修了的课程名字；

SELECT t2.`name`

FROM stu t1,course t2,score t3

WHERE t1.`id`=t3.`stu_id` AND t2.`id`=t3.`course_id`

AND t1.`name`='姜振国';

#3)查询只选修了1门课程的学生学号和姓名；

#子句

SELECT stu_id,COUNT(id)

FROM score

GROUP BY stu_id HAVING COUNT(id)=1;

SELECT id,NAME

FROM stu t1,(SELECT stu_id,COUNT(id)

FROM score

GROUP BY stu_id HAVING COUNT(id)=1) t2

WHERE t1.`id`=t2.stu_id;

#4)查询选修了至少3门课程的学生信息；

SELECT stu_id,COUNT(id)

FROM score

GROUP BY stu_id HAVING COUNT(id)>=3;

SELECT t1.`id`,t1.`name`,t1.`sex`,t1.`bj_id`

FROM stu t1,(SELECT stu_id,COUNT(id)

FROM score

GROUP BY stu_id HAVING COUNT(id)>=3) t2

WHERE t1.`id`=t2.stu_id;

#5)查询选修了所有课程的学生；

SELECT COUNT(id) FROM course;

SELECT stu_id

FROM score

GROUP BY stu_id HAVING COUNT(course_id)=(SELECT COUNT(id) FROM course);

SELECT *

FROM stu

WHERE stu_id IN();

#6)查询选修课程的学生人数；

SELECT COUNT(DISTINCT stu_id) FROM score;

#7)查询所学课程至少有一门跟 姜振国 所学课程相同的学生信息。

SELECT t2.`course_id`

FROM stu t1,score t2

WHERE t1.`id`=t2.`stu_id` AND t1.`name`='姜振国';

SELECT t1.*

FROM stu t1,score t2

WHERE t1.`id`=t2.`stu_id` AND t2.`course_id`=ANY(?);

SELECT DISTINCT t1.*

FROM stu t1,score t2

WHERE t1.`id`=t2.`stu_id` AND t2.`course_id`=ANY(SELECT t2.`course_id`

FROM stu t1,score t2

WHERE t1.`id`=t2.`stu_id` AND t1.`name`='姜振国') AND t1.`name`<>'姜振国';

#8)查询两门及两门以上不及格同学的平均分(<90)

#子句 查询所有学生的id和不及格课程的数量(小余90)

SELECT stu_id,COUNT(id)

FROM score t1

WHERE t1.grade<90

GROUP BY stu_id;

#子句 查询所有学生的id和不及格课程的数量大于等于2的(小余90)

SELECT stu_id,COUNT(id),AVG(grade) temp

FROM score t1

WHERE t1.grade<90

GROUP BY stu_id HAVING COUNT(id)>=2;

SELECT t1.stu_id,t1.temp

FROM (SELECT stu_id,COUNT(id),AVG(grade) temp

FROM score t1

WHERE t1.grade<90

GROUP BY stu_id HAVING COUNT(id)>=2) t1;

#可以在关联学生姓名