mysql的sql作业_SQL学习笔记四(补充-2-1)之MySQL SQL查询作业答案

#1、查询所有的课程的名称以及对应的任课老师姓名

SELECT

course.cname,

teacher.tname

FROM

course

INNER JOIN teacher ON course.teacher_id=teacher.tid;#2、查询学生表中男女生各有多少人

SELECT

gender 性别,

count(1) 人数

FROM

student

GROUP BY

gender;#3、查询物理成绩等于100的学生的姓名

SELECT

student.sname

FROM

student

WHERE

sid IN (

SELECT

student_id

FROM

score

INNER JOIN course ON score.course_id=course.cid

WHERE

course.cname= '物理'AND score.num= 100);#4、查询平均成绩大于八十分的同学的姓名和平均成绩

SELECT

student.sname,

t1.avg_num

FROM

student

INNER JOIN (

SELECT

student_id,

avg(num) AS avg_num

FROM

score

GROUP BY

student_id

HAVING

avg(num)> 80) AS t1 ON student.sid=t1.student_id;#5、查询所有学生的学号，姓名，选课数，总成绩(注意：对于那些没有选修任何课程的学生也算在内)

SELECT

student.sid,

student.sname,

t1.course_num,

t1.total_num

FROM

student

LEFT JOIN (

SELECT

student_id,

COUNT(course_id) course_num,

sum(num) total_num

FROM

score

GROUP BY

student_id

) AS t1 ON student.sid=t1.student_id;#6、 查询姓李老师的个数

SELECT

count(tid)

FROM

teacher

WHERE

tname LIKE'李%';#7、 查询没有报李平老师课的学生姓名(找出报名李平老师课程的学生，然后取反就可以)

SELECT

student.sname

FROM

student

WHERE

sid NOT IN (

SELECT DISTINCT

student_id

FROM

score

WHERE

course_id IN (

SELECT

course.cid

FROM

course

INNER JOIN teacher ON course.teacher_id=teacher.tid

WHERE

teacher.tname= '李平老师')

);#8、 查询物理课程比生物课程高的学生的学号(分别得到物理成绩表与生物成绩表，然后连表即可)

SELECT

t1.student_id

FROM

(

SELECT

student_id,

num

FROM

score

WHERE

course_id=(

SELECT

cid

FROM

course

WHERE

cname= '物理')

) AS t1

INNER JOIN (

SELECT

student_id,

num

FROM

score

WHERE

course_id=(

SELECT

cid

FROM

course

WHERE

cname= '生物')

) AS t2 ON t1.student_id=t2.student_id

WHERE

t1.num>t2.num;#9、 查询没有同时选修物理课程和体育课程的学生姓名(没有同时选修指的是选修了一门的，思路是得到物理+体育课程的学生信息表，然后基于学生分组，统计count(课程)=1)

SELECT

student.sname

FROM

student

WHERE

sid IN (

SELECT

student_id

FROM

score

WHERE

course_id IN (

SELECT

cid

FROM

course

WHERE

cname= '物理'OR cname= '体育')

GROUP BY

student_id

HAVING

COUNT(course_id)= 1);#10、查询挂科超过两门(包括两门)的学生姓名和班级(求出<60的表，然后对学生进行分组，统计课程数目>=2)

SELECT

student.sname,class.caption

FROM

student

INNER JOIN (

SELECT

student_id

FROM

score

WHERE

num< 60GROUP BY

student_id

HAVING

count(course_id)>= 2) AS t1

INNER JOINclass ON student.sid =t1.student_id

AND student.class_id= class.cid;#11、查询选修了所有课程的学生姓名(先从course表统计课程的总数，然后基于score表按照student_id分组，统计课程数据等于课程总数即可)

SELECT

student.sname

FROM

student

WHERE

sid IN (

SELECT

student_id

FROM

score

GROUP BY

student_id

HAVING

COUNT(course_id)=(SELECT count(cid) FROM course)

);#12、查询李平老师教的课程的所有成绩记录

SELECT*FROM

score

WHERE

course_id IN (

SELECT

cid

FROM

course

INNER JOIN teacher ON course.teacher_id=teacher.tid

WHERE

teacher.tname= '李平老师');#13、查询全部学生都选修了的课程号和课程名(取所有学生数，然后基于score表的课程分组，找出count(student_id)等于学生数即可)

SELECT

cid,

cname

FROM

course

WHERE

cid IN (

SELECT

course_id

FROM

score

GROUP BY

course_id

HAVING

COUNT(student_id)=(

SELECT

COUNT(sid)

FROM

student

)

);#14、查询每门课程被选修的次数

SELECT

course_id,

COUNT(student_id)

FROM

score

GROUP BY

course_id;#15、查询之选修了一门课程的学生姓名和学号

SELECT

sid,

sname

FROM

student

WHERE

sid IN (

SELECT

student_id

FROM

score

GROUP BY

student_id

HAVING

COUNT(course_id)= 1);#16、查询所有学生考出的成绩并按从高到低排序(成绩去重)

SELECT DISTINCT

num

FROM

score

ORDER BY

num DESC;#17、查询平均成绩大于85的学生姓名和平均成绩

SELECT

sname,

t1.avg_num

FROM

student

INNER JOIN (

SELECT

student_id,

avg(num) avg_num

FROM

score

GROUP BY

student_id

HAVING

AVG(num)> 85) t1 ON student.sid=t1.student_id;#18、查询生物成绩不及格的学生姓名和对应生物分数

SELECT

sname 姓名,

num 生物成绩

FROM

score

LEFT JOIN course ON score.course_id=course.cid

LEFT JOIN student ON score.student_id=student.sid

WHERE

course.cname= '生物'AND score.num< 60;#19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

SELECT

sname

FROM

student

WHERE

sid=(

SELECT

student_id

FROM

score

WHERE

course_id IN (

SELECT

course.cid

FROM

course

INNER JOIN teacher ON course.teacher_id=teacher.tid

WHERE

teacher.tname= '李平老师')

GROUP BY

student_id

ORDER BY

AVG(num) DESC

LIMIT1);#20、查询每门课程成绩最好的前两名学生姓名#查看每门课程按照分数排序的信息，为下列查找正确与否提供依据

SELECT*FROM

score

ORDER BY

course_id,

num DESC;#表1：求出每门课程的课程course_id,与最高分数first_num

SELECT

course_id,

max(num) first_num

FROM

score

GROUP BY

course_id;#表2：去掉最高分，再按照课程分组，取得的最高分，就是第二高的分数second_num

SELECT

score.course_id,

max(num) second_num

FROM

score

INNER JOIN (

SELECT

course_id,

max(num) first_num

FROM

score

GROUP BY

course_id

) AS t ON score.course_id=t.course_id

WHERE

score.num

GROUP BY

course_id;#将表1和表2联合到一起，得到一张表t3，包含课程course_id与该们课程的first_num与second_num

SELECT

t1.course_id,

t1.first_num,

t2.second_num

FROM

(

SELECT

course_id,

max(num) first_num

FROM

score

GROUP BY

course_id

) AS t1

INNER JOIN (

SELECT

score.course_id,

max(num) second_num

FROM

score

INNER JOIN (

SELECT

course_id,

max(num) first_num

FROM

score

GROUP BY

course_id

) AS t ON score.course_id=t.course_id

WHERE

score.num

GROUP BY

course_id

) AS t2 ON t1.course_id=t2.course_id;#查询前两名的学生(有可能出现并列第一或者并列第二的情况)

SELECT

score.student_id,

t3.course_id,

t3.first_num,

t3.second_num

FROM

score

INNER JOIN (

SELECT

t1.course_id,

t1.first_num,

t2.second_num

FROM

(

SELECT

course_id,

max(num) first_num

FROM

score

GROUP BY

course_id

) AS t1

INNER JOIN (

SELECT

score.course_id,

max(num) second_num

FROM

score

INNER JOIN (

SELECT

course_id,

max(num) first_num

FROM

score

GROUP BY

course_id

) AS t ON score.course_id=t.course_id

WHERE

score.num

GROUP BY

course_id

) AS t2 ON t1.course_id=t2.course_id

) AS t3 ON score.course_id=t3.course_id

WHERE

score.num>=t3.second_num

AND score.num<=t3.first_num;#排序后可以看的明显点

SELECT

score.student_id,

t3.course_id,

t3.first_num,

t3.second_num

FROM

score

INNER JOIN (

SELECT

t1.course_id,

t1.first_num,

t2.second_num

FROM

(

SELECT

course_id,

max(num) first_num

FROM

score

GROUP BY

course_id

) AS t1

INNER JOIN (

SELECT

score.course_id,

max(num) second_num

FROM

score

INNER JOIN (

SELECT

course_id,

max(num) first_num

FROM

score

GROUP BY

course_id

) AS t ON score.course_id=t.course_id

WHERE

score.num

GROUP BY

course_id

) AS t2 ON t1.course_id=t2.course_id

) AS t3 ON score.course_id=t3.course_id

WHERE

score.num>=t3.second_num

AND score.num<=t3.first_num

ORDER BY

course_id;#可以用以下命令验证上述查询的正确性

SELECT*FROM

score

ORDER BY

course_id,

num DESC;-- 21、查询不同课程但成绩相同的学号，课程号，成绩-- 22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；-- 23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；-- 24、任课最多的老师中学生单科成绩最高的学生姓名