1、將所有的課程的名稱以及對應的任課老師姓名打印出來，如下： SELECT cname,tname FROM course INNER JOIN teacher WHERE course.teacher_id=teacher.tid2、查詢學生表中男女生各有多少人？ 如下： SELECT gender,COUNT(gender) 人數 FROM student GROUP BY gender 3、查詢物理成績等于100的學生的姓名？如下： SELECT sid,sname FROM student WHERE sid in (SELECT student_id FROM score WHERE course_id=2 and num=100)4、查詢平均成績大于八十分的同學的姓名和平均成績，如下： SELECT sname,平均成績 from (SELECT sid,sname from student) s INNER JOIN (SELECT student_id,avg(num) 平均成績 from score GROUP BY student_id HAVING avg(num)>80) c WHERE sid=student_id5、查詢所有學生的學號，姓名，選課數，總成績 SELECT s.sid,s.sname,x.課程數,x.總成績 FROM (SELECT sid,sname FROM student) s INNER JOIN (SELECT student_id,COUNT(course_id) 課程數,SUM(num) 總成績 from score GROUP BY student_id) x WHERE sid=student_id6、查詢姓李老師的個數 SELECT COUNT(tid) FROM teacher WHERE tname like '李%'7、查詢沒有報李平老師課的學生姓名 SELECT DISTINCT sname FROM student WHERE sid not in (SELECT DISTINCT student_id FROM score WHERE course_id in (SELECT cid from course WHERE teacher_id=2))8、查詢物理課程比生物課程高的學生的學號 SELECT sw.student_id from (SELECT student_id,num from score WHERE course_id=2) wl INNER JOIN ( SELECT student_id,num from score WHERE course_id=1) sw WHERE wl.num>sw.num and wl.student_id=sw.student_id9、查詢沒有同時選修物理課程和體育課程的學生姓名 SELECT sname FROM student WHERE sid in (SELECT wl.student_id FROM (SELECT student_id,course_id from score WHERE course_id=2) wl INNER JOIN (SELECT student_id,course_id from score WHERE course_id=3) ty WHERE wl.student_id=ty.student_id)10、查詢掛科超過兩門(包括兩門)的學生姓名和班級 SELECT sname,caption FROM class INNER JOIN (SELECT sname,class_id from student INNER JOIN (SELECT student_id,COUNT(student_id) 掛科次數 FROM score WHERE num<60 GROUP BY student_id) gk WHERE sid=student_id and 掛科次數>=2) s WHERE cid=class_id11 、查詢選修了所有課程的學生姓名 SELECT sname FROM student WHERE sid in (SELECT c.student_id FROM (SELECT a.student_id FROM (SELECT * from score WHERE course_id=1) a INNER JOIN (SELECT * from score WHERE course_id=2) b WHERE a.student_id=b.student_id) c INNER JOIN (SELECT a.student_id FROM (SELECT * from score WHERE course_id=2) a INNER JOIN (SELECT * from score WHERE course_id=3) b WHERE a.student_id=b.student_id) d WHERE c.student_id=d.student_id)12、查詢李平老師教的課程的所有成績記錄SELECT sid,sname,cname,num FROM course INNER JOIN (SELECT student.sid,sname,course_id,num FROM student INNER JOIN (SELECT * FROM score WHERE course_id in (SELECT cid FROM course WHERE teacher_id=(SELECT tid FROM teacher WHERE tid=2))) a WHERE student.sid=a.student_id) a WHERE cid=a.course_id13、查詢全部學生都選修了的課程號和課程名：沒有 SELECT * from (SELECT course_id,COUNT(student_id) 報名人數 FROM score GROUP BY course_id) a INNER JOIN (SELECT COUNT(sid) 學生總數 FROM student) b WHERE 報名人數=學生總數-- 14、查詢每門課程被選修的次數 SELECT course.cname,a.aa from (select course_id,count(course_id)as aa from score GROUP BY course_id)as a INNER JOIN course on course.cid=a.course_id-- 15、查詢之選修了一門課程的學生姓名和學號 SELECT student.sid,student.sname,a.aa from (SELECT student_id,count(student_id) as aa from score GROUP BY student_id having aa=1) as a INNER JOIN student on student.sid=a.student_id-- 16、查詢所有學生考出的成績并按從高到低排序（成績去重） select DISTINCT(num) FROM score ORDER BY num DESC

-- 17、查詢平均成績大于85的學生姓名和平均成績
SELECT student.sname,a.aa from (SELECT student_id,avg(num) as aa from score GROUP BY student_id having aa >85) as a
INNER JOIN student on student.sid=a.student_id

-- 18、查詢生物成績不及格的學生姓名和對應生物分數
select student.sname,a.num from (SELECT * from score where num < 60 and course_id=
(SELECT cid FROM course where cname='生物')) as a
?INNER JOIN student on student.sid=a.student_id

?

轉載于:https://www.cnblogs.com/fangjie0410/p/7252469.html

總結

以上是生活随笔為你收集整理的MySQL之练习题5的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。