专科mysql数据库大作业_MySQL大作业

1、查询所有的课程的名称以及对应的任课老师姓名SELECTcourse.cname,

teacher.tnameFROMcourseINNER JOIN teacher ON course.teacher_id =teacher.tid;2、查询学生表中男女生各有多少人SELECTgender,count(sid)FROMstudentGROUP BYgender;3、查询物理成绩等于100的学生的姓名SELECTsnameFROMstudentWHEREsidIN(SELECTscore.student_idFROMscoreLEFT JOIN course ON score.course_id =course.cidWHEREscore.num= 100

AND course.cname ="物理"

);4、查询平均成绩大于八十分的同学的姓名和平均成绩SELECTt1.sname,

t2.avg_numFROMstudent t1INNER JOIN(SELECTstudent_id,avg(num) avg_numFROMscoreGROUP BYstudent_idHAVING

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

sname,

t1.count_course,

t1.sum_numFROMstudentLEFT JOIN(SELECTstudent_id,count(course_id) count_course,sum(num) sum_numFROMscoreGROUP BYstudent_id

) t1ON student.sid =t1.student_id;6、 查询姓李老师的个数SELECT

count(tid)FROMteacherWHEREtnameLIKE '李%';7、 查询没有报李平老师课的学生姓名(找出报名李平老师课程的学生，然后取反就可以)SELECTsnameFROMstudentWHEREsidNOT IN(SELECT DISTINCTstudent_idFROMscoreWHEREcourse_idIN(SELECTcidFROMcourseWHEREteacher_id=(SELECTtidFROMteacherWHEREtname= '李平')

)

);8、 查询物理课程的分数比生物课程的分数高的学生的学号SELECTt1.student_idFROM(SELECTstudent_id,

numFROMscoreLEFT JOIN course ON score.course_id =course.cidWHEREcname= '物理') t1LEFT JOIN(SELECTstudent_id,

numFROMscoreLEFT JOIN course ON score.course_id =course.cidWHEREcname= '生物') t2ON t1.student_id =t2.student_idWHEREt1.num>t2.num;9、 查询没有同时选修物理课程和体育课程的学生姓名(没有同时选修指的是选修了一门的，思路是得到物理+体育课程的学生信息表，然后基于学生分组，统计count(课程)=1)SELECTsnameFROMstudentWHEREsidIN(SELECTstudent_idFROMscoreWHEREcourse_idIN(SELECTcidFROMcourseWHEREcnameIN ('物理', '体育')

)GROUP BYstudent_idHAVING

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

# 方式一：SELECTt1.sname,

t2.captionFROM(SELECTclass_id,

snameFROMstudentWHEREsidIN(SELECTstudent_idFROMscoreWHEREnum< 60

GROUP BYstudent_idHAVING

count(course_id) > 1)

) t1INNER JOIN (SELECT cid, caption FROM class) t2 ON t1.class_id =t2.cid;

# 方式二：SELECTstudent.sname,

class.captionFROMstudentINNER JOIN(SELECTstudent_idFROMscoreWHEREnum< 60

GROUP BYstudent_idHAVING

count(course_id) >= 2)ASt1INNER JOIN class ON student.sid =t1.student_idAND student.class_id =class.cid;11、查询选修了所有课程的学生姓名(先从course表统计课程的总数，然后基于score表按照student_id分组，统计课程数据等于课程总数即可)SELECTsnameFROMstudentWHEREsidIN(SELECTstudent_idFROMscoreGROUP BYstudent_idHAVING

count(course_id) =(SELECT

count(cid) AScourse_totalFROMcourse

)

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

# 方式一：SELECT

*

FROMscoreWHEREcourse_idIN(SELECTcidFROMcourseWHEREteacher_id=(SELECTtidFROMteacherWHEREtname= '李平')

);

# 方式二：SELECT

*

FROMscoreWHEREcourse_idIN(SELECTcidFROMcourseINNER JOIN teacher ON course.teacher_id =teacher.tidWHEREteacher.tname= '李平');13、查询全部学生都选修了的课程号和课程名(先统计出有课程的学生总数，再以课程分组统计出有多少学生学，如果有课程跟学生总数相等，就说明这就是都选修的课程)SELECTcid,

cnameFROMcourseWHEREcidIN(SELECTcourse_idFROMscoreGROUP BYcourse_idHAVING

count(student_id) =(SELECT

count(DISTINCTstudent_id)FROMscore

)

)14、查询每门课程被选修的次数SELECTcourse_id,count(student_id)FROMscoreGROUP BYcourse_id;15、查询只选修了一门课程的学生学号和姓名SELECTsid,

snameFROMstudentWHEREsidIN(SELECTstudent_idFROMscoreGROUP BYstudent_idHAVING

count(course_id) = 1);16、查询所有学生考出的成绩并按从高到低排序(成绩去重)SELECT DISTINCTnumFROMscoreORDER BYnumDESC;17、查询平均成绩大于85的学生姓名和平均成绩SELECTsname,

t1.avg_numFROMstudentINNER JOIN(SELECTstudent_id,avg(num) ASavg_numFROMscoreGROUP BYstudent_idHAVING

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

t1.numFROMstudentINNER JOIN(SELECTstudent_id,

numFROMscoreWHEREcourse_id=(SELECTcidFROMcourseWHEREcname= '生物')HAVINGnum< 60) t1ON student.sid =t1.student_id;19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名SELECTsnameFROMstudentWHEREsid=(SELECTstudent_idFROMscoreWHEREcourse_idIN(SELECTcourse.cidFROMteacherINNER JOIN course ON teacher.tid =course.teacher_idWHEREteacher.tname= '李平')GROUP BYstudent_idORDER BY

avg(num) DESCLIMIT1);20、查询每门课程成绩最好的课程id、学生姓名和分数SELECTcourse_id,

sname,

numFROMstudentINNER JOIN(SELECTt1.course_id,

student_id,

numFROMscoreINNER JOIN(SELECTcourse_id,max(num) ASmax_numFROMscoreGROUP BYcourse_id

) t1ON score.course_id =t1.course_idWHEREscore.num=t1.max_num

) t2ON student.sid =t2.student_id;21、查询不同课程但成绩相同的课程号、学生号、成绩SELECT DISTINCTs1.course_id,

s2.course_id,

s1.student_id,

s2.student_id,

s1.num,

s2.numFROMscoreASs1,

scoreASs2WHEREs1.num=s2.numAND s1.course_id !=s2.course_id;22、查询没学过“李平”老师课程的学生姓名以及选修的课程名称SELECTstudent.sname,

course.cnameFROMstudentINNER JOIN(SELECTstudent_id,

course_idFROMscoreWHEREstudent_idIN(SELECTsidFROMstudentWHEREsidNOT IN(SELECTstudent_idFROMscoreWHEREcourse_idIN(SELECTcidFROMcourseINNER JOIN teacher ON course.teacher_id =teacher.tidWHEREtname= '李平')

)

)

) t1ON student.sid =t1.student_idINNER JOIN course ON course.cid =t1.course_id;23、查询所有选修了学号为2的同学选修过的一门或者多门课程的同学学号和姓名SELECTsid,

snameFROMstudentWHEREsidIN(SELECT DISTINCTstudent_idFROMscoreWHEREcourse_idIN(SELECTcourse_idFROMscoreWHEREstudent_id= 2)

)AND sid != 2;24、任课最多的老师中学生单科成绩最高的课程id、学生姓名和分数SELECTt1.course_id,

student.sname,

numFROMscoreINNER JOIN(SELECTcourse_id,max(num) ASmax_numFROMscoreWHEREcourse_idIN(SELECTcidFROMcourseWHEREteacher_id=(SELECTteacher_idFROMcourseGROUP BYteacher_idORDER BY

count(teacher_id) DESCLIMIT1)

)GROUP BYcourse_id

) t1ON score.course_id =t1.course_idINNER JOIN student ON score.student_id =student.sidWHEREscore.course_id=t1.course_idAND num = t1.max_num;