mysql篇---mysql習題

標籤：擷取   esc   沒有   均值   isnull   left join   python   思路   大於   

2.查詢’生物‘課程比’物理‘課程成績高的所有學生的學號思路：擷取所有有生物課程的人（學號，成績）-暫存資料表擷取所有有物理課程的人（學號，成績）-暫存資料表根據【學號】串連兩個暫存資料表：學號 物理成績 生物成績生物---》SELECT score.sid,score.student_id,course.cname,score.num FROM scoreLEFT JOIN course ON score.course_id=course.cid WHERE course.cname=‘生物‘;select A.student_id from (select score.sid,score.student_id,course.cname,score.num from score LEFT JOIN course on score.course_id=course.cid where course.cname="生物") as AINNER JOIN(select score.sid,score.student_id,course.cname,score.num from score LEFT JOIN course on score.course_id=course.cid where course.cname="物理") as Bon A.student_id = B.student_idwhere A.num > B.num3.查詢平均成績大於60分的同學的學號和平均成績； 思路：        根據學生分組，使用avg擷取平均值，通過having對avg進行篩選         select student_id,avg(num) from score group by student_id having avg(num) > 60進行連表操作：SELECT B.student_id,student.sname,B.mean FROM (SELECT student_id,avg(num) as mean FROM score GROUP BY student_id HAVING avg(num) > 60) as BLEFT JOIN student ON  B.student_id=student.sid 4.查詢所有同學的學號，姓名，選課數，總成績；SELECT score.student_id,student.sname,count(student_id),sum(num) FROM score LEFT JOIN student on score.student_id=student.sidGROUP BY score.student_id5.查詢姓’李‘的老師的個數SELECT count(1) FROM teacher WHERE tname like ‘李%‘;6.查詢沒學過’李平老師‘課的同學的學號，姓名；思路：先查到’李平老師‘老師教的所有課的 ID擷取選過課的所有學生ID學生表中篩選SELECT student.sid,student.sname FROM student WHERE sid NOT in (SELECT student_id FROM score WHERE course_id in (SELECT course.cid FROM course LEFT JOIN teacher ON course.teacher_id=teacher.tid WHERE teacher.tname = ‘李平老師‘)GROUP BY student_id)7、查詢學過“001”並且也學過編號“002”課程的同學的學號、姓名；思路：先查到既選擇001又選擇002課程的所用同學根據學生進行分組，如果學生數量等於2 表示 兩門均已選擇SELECT student_id,sname FROM(SELECT student_id,course_id FROM score WHERE course_id=1 OR course_id=2 ) as BLEFT JOIN student on B.student_id = student.sid GROUP BY student_id HAVING count(student_id) > 18.查詢學過’李平‘老師課的同學的學號，姓名；同上，只不過001和002 變成 in SELECT student.sid,student.sname FROM student WHERE sid in (SELECT student_id FROM score WHERE course_id in (SELECT course.cid FROM course LEFT JOIN teacher ON course.teacher_id=teacher.tid WHERE teacher.tname = ‘李平老師‘)GROUP BY student_id)10.查詢有課程成績小於60分的同學的學號，姓名；SELECT sid,sname FROM student WHERE sid in (SELECT DISTINCT student_id FROM score WHERE num < 60)11.查詢沒有學全所有課的同學的學號，姓名；思路：在分數表中根據學生進行分組，擷取每一個學生選課數量如果 數量== 總課程數量，表示已經選擇了所有課程SELECT student_id,sname FROM score LEFT JOIN student on score.student_id=student.sidGROUP BY student_id HAVING count(course_id) = (SELECT count(1) FROM course)12.查詢至少一門課程與學號為’001‘的同學所學相同的同學的學號和姓名；思路：擷取 001 同學 選擇的所有課程擷取課程在其中的所有人以及所有課程根據學生篩選，擷取所有學生資訊再與學生表 串連，擷取姓名SELECT student_id,sname,count(course_id) FROM score LEFT JOIN student on score.student_id=student.sidWHERE student_id != 1 AND course_id in (SELECT course_id FROM score WHERE student_id=1)GROUP BY student_id13.查詢至少學過學號為 ’001‘同學所有課的其他同學學號和姓名；先找到和001的學過所有人然後個數 = 001所有學科  ==》 其他人可能選擇的更多SELECT student_id,sname,count(course_id) FROM scoreLEFT JOIN student on score.student_id=student.sidWHERE student_id != 1 AND course_id in (SELECT course_id FROM score WHERE student_id = 1)GROUP BY student_id HAVING count(course_id) = (SELECT count(course_id) FROM score WHERE student_id=1) 14.查詢和’002‘號的同學學習的課程完全相同的其他同學學號和姓名；（！！！）個數相同002學過的也學過select student_id from score where  student_id in (select student_id from score where student_id !=1 GROUP BY student_id HAVING count(1) = (select count(1) from score where student_id = 1) ) and course_id in (select course_id from score where student_id = 1) GROUP BY student_id HAVING count(1) = (select count(1) from score where student_id = 1)15.刪除16.向sc表中插入一些記錄，這些記錄要求符合以下條件：1.沒有上過編號’002‘課程的同學學號2.插入’002‘號課程的平均成績思路；由於insert支援insert into tb1(xx,xx) select x1,x2 from tb2;所有，擷取所有沒上過002課 的所有人，擷取002的平均成績17.18.查詢各科成績最高和最低分：如下形式顯示：課程ID，最高分，最低分；SELECT course_id,max(num) AS max_num,min(num) AS min_num FROM score GROUP BY course_id;19.按各科平均成績從低到高和及格率的百分數從高到低順序；方法一：SELECT course_id,avg(num),sum(CASE WHEN num<60 THEN 0 ELSE 1 END),sum(1),sum(CASE WHEN num<60 THEN 0 ELSE 1 END)/sum(1) *100AS percent FROM scoreGROUP BY course_idORDER BY avg(num) ASC,percent desc;方法二：select course_id, avg(num) as avgnum,sum(case when score.num > 60 then 1 else 0 END)/count(1)*100 as percent from score group by course_id order by avgnum asc,percent desc;20.課程平均分從高到低顯示（任課老師）SELECT avg(if(isnull(score.num),0,score.num)),teacher.tname FROM courseLEFT JOIN score ON course.cid=score.course_idLEFT JOIN teacher ON course.teacher_id=teacher.tidGROUP BY score.course_id21.查詢各科成績前三名的記錄：（不考慮成績並列情況）SELECT score.sid,score.course_id,score.num,T.first_num,T.second_num FROM scoreLEFT JOIN (SELECT sid,(SELECT num FROM score AS s2 WHERE s2.course_id=s1.course_id ORDER BY num DESC LIMIT 0,1) AS first_num,(SELECT num FROM score AS s2 WHERE s2.course_id=s1.course_id ORDER BY num DESC LIMIT 3,1) AS second_numFROM score as s1) AS TON score.sid = T.sidWHERE score.num <= T.first_num AND score.num >= T.second_num22.查詢每門課程被選修的學生數；SELECT course_id,count(1) FROM score GROUP BY course_id23查詢出只選修了一門課程的全部學生的學號和姓名；（！！！）SELECT student.sid,student.sname,count(1) FROM scoreLEFT JOIN student ON score.student_id=student.sidGROUP BY course_id HAVING count(1) = 124.查詢男生，女生的人數SELECT * FROM(SELECT count(1) AS man FROM student WHERE gender=‘男‘) AS A,(SELECT count(1) AS woman FROM student WHERE gender=‘女‘) AS B;25.查詢姓’張‘的學生名單SELECT sname FROM student WHERE sname LIKE ‘張%‘26.查詢同名同姓學生名單，並統計同名人數SELECT sname,count(1) AS count FROM student GROUP BY sname27.查詢每門課的平均成績，結果按平均成績升序排列，平均成績相同時，按課程降序排列SELECT course_id,avg(if(isnull(num),0,num)) AS avg FROM scoreGROUP BY course_id ORDER BY avg ASC,course_id desc28.查詢平均成績大於85的所有學生的學號，姓名和平均成績（！！！！未取出 >85）SELECT student_id,sname,avg(if(isnull(num),0,num)) FROM score LEFT JOIN student on score.student_id=student.sid GROUP BY student_id 29.查詢課程名稱為’生物‘，且分數低於60的學生姓名和分數SELECT student.sname,score.num FROM scoreLEFT JOIN course ON score.course_id=course.cidLEFT JOIN student ON score.student_id=student.sidWHERE score.num < 60 AND course.cname=‘生物‘30.查詢課程編號為003且課程成績在80分以上的學生的學號和姓名SELECT * FROM score WHERE score.student_id = 3 and score.num > 8031.求選了課程的學生人數select count(c) from (select count(student_id) as c from score group by student_id) as A;32.查詢選修’張磊‘老師所授課程的學生中，成績最高的學生姓名及其成績SELECT sname,num FROM scoreLEFT JOIN student ON score.student_id=student.sidWHERE score.course_id in (SELECT course.cid FROM course LEFT JOIN teacher ONcourse.teacher_id=teacher.tid WHERE tname=‘張磊老師‘) ORDER BY num DESC LIMIT 133.查詢各個課程及相應的選修人數；    select course.cname,count(1) from score    left join course on score.course_id = course.cid    group by course_id;34.查詢不同課程但成績相同的學生的學號，課程號，學產生績（！！！）SELECT DISTINCT s1.course_id,s2.course_id,s1.num,s2.numFROM score AS s1,score AS s2 WHERE s1.num=s2.num AND s1.course_id != s2.course_id36.檢索至少選修兩門課程的學生學號SELECT student_id FROM score GROUP BY student_id HAVING count(student_id) > 1;37.查詢全部學生都選修的課程號和課程名（！！！）SELECT course_id,count(1) FROM  scoreGROUP BY course_id HAVING count(1) = (SELECT count(1) FROM student);38.查詢沒學過‘李平‘老師講授的任意一門課程的學生姓名SELECT student_id,student.sname FROM scoreLEFT JOIN student on score.student_id=student.sidWHERE score.course_id NOT in (SELECT cid FROM course LEFT JOIN teacher ON course.teacher_id=teacher.tid WHERE tname=‘李平老師‘)GROUP BY student_id加 課程名SELECT student_id,student.sname,course.cname FROM scoreLEFT JOIN student on score.student_id=student.sidLEFT JOIN course on course_id=course.cidWHERE score.course_id NOT in (SELECT cid FROM course LEFT JOIN teacher ON course.teacher_id=teacher.tid WHERE tname=‘李平老師‘)GROUP BY student_id39.查詢兩門以上不及格課程的同學的學號及其平均成績（！！！）SELECT student_id,count(1) FROM score WHERE num < 60 GROUP BY student_id HAVING count(1) > 2 40.檢索’004‘課程分數小於60，按分數 降序 排列的同學學號SELECT student_id FROM score WHERE num<60 and course_id=4 ORDER BY num DESC;

 

mysql篇---mysql習題