python/MySQL練習題(二)

標籤：from   strong   des   log   desc   結果   code   where   number   

python/MySQL練習題(二)

21、查詢各科成績前三名的記錄:(不考慮成績並列情況)

 1  select score.sid,score.course_id,score.num,T.first_num,T.second_num from score left join 2     ( 3     select 4         sid, 5         (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 0,1) as first_num, 6         (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 3,1) as second_num 7     from 8         score as s1 9     ) as T10     on score.sid =T.sid11     where score.num <= T.first_num and score.num >= T.second_num

22、查詢每門課程被選修的學生數

1 SELECT count(student_id) from score LEFT JOIN course on course.cid=score.course_id2 GROUP BY course_id3 HAVING COUNT(1) >4

23、查詢出只選修了一門課程的全部學生的學號和姓名

1 SELECT student.sid,student.sname from score LEFT JOIN student on student.sid=score.student_id2 LEFT JOIN course on course.cid=score.course_id3 GROUP BY student_id4 HAVING count(score.course_id)=‘1‘

24、查詢男生、女生的人數

1 SELECT gender,count(sid) from student2 GROUP BY gender3 HAVING count(sid)

25、查詢姓“張”的學生名單

1 SELECT * from student where sname like ‘張%‘

26、查詢同名同姓學生名單，並統計同名人數

1 SELECT sname,COUNT(sname) from student2 GROUP BY sname3 HAVING COUNT(sname)

27、查詢每門課程的平均成績，結果按平均成績升序排列，平均成績相同時，按課程號降序排列

1  SELECT course_id,avg(num) from score LEFT JOIN course on course.cid=score.course_id2  GROUP BY course_id3  ORDER BY avg(num) asc 

28、查詢平均成績大於85的所有學生的學號、姓名和平均成績

1  SELECT student.sid,student.sname,avg(num) from score LEFT JOIN student on student.sid=score.student_id2  GROUP BY student_id3  HAVING avg(num) > 85

29、查詢課程名稱為“物理”，且分數低於60的學生姓名和分數

1 SELECT student.sname,score.num  from score LEFT JOIN student on student.sid=score.student_id2 LEFT JOIN course on course.cid=score.course_id3 where course.cname=‘生物‘ and score.num <60

30、查詢課程編號為003且課程成績在80分以上的學生的學號和姓名

1 SELECT student.sid,student.sname from score LEFT JOIN course on course.cid=score.course_id2 LEFT JOIN student on student.sid=score.student_id3 where course.cid=‘3‘ and num > 80

31、求選了課程的學生人數

1 SELECT COUNT(c) from2 (SELECT count(student_id)as c from score GROUP BY student_id)as A

32、查詢選修“李平”老師所授課程的學生中，成績最高的學生姓名及其成績

1 SELECT sname,num from score2 LEFT JOIN course on course.cid=score.course_id3 LEFT JOIN student on student.sid=score.student_id4 LEFT JOIN teacher on teacher.tid=course.teacher_id5 where teacher.tname=‘李平老師‘6 GROUP BY student_id7 ORDER BY num DESC8 LIMIT 1

33、查詢各個課程及相應的選修人數

1 SELECT cname,COUNT(1)from score LEFT JOIN course on course.cid=score.course_id2 GROUP BY course_id

34、查詢不同課程但成績相同的學生的學號、課程號、學產生績

1 select A1.student_id,A1.course_id,A2.course_id,A1.num,A2.num from score as A1 ,score as A22 where A1.course_id!=A2.course_id and A1.num=A2.num3 GROUP BY student_id

35、查詢每門課程成績最好的前兩名

 1    select score.sid,score.course_id,score.num,T.first_num,T.second_num from score left join 2     ( 3     select 4         sid, 5         (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 0,1) as first_num, 6         (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 1,1) as second_num 7     from 8         score as s1 9     ) as T10     on score.sid =T.sid11     where score.num <= T.first_num and score.num >= T.second_num

36、檢索至少選修兩門課程的學生學號

1 -- SELECT count(A.c) from (select count(1)as c from score GROUP BY course_id)as A2 SELECT student_id from score LEFT JOIN student on student.sid=score.student_id3 GROUP BY student_id4 HAVING count(1) > 2

37、查詢全部學生都選修的課程的課程號和課程名

1 select course_id from score GROUP BY course_id HAVING COUNT(1)=(select count(1) from student)

38、查詢沒學過“李平”老師講授的任一門課程的學生姓名

 1 SELECT 2     student.sname 3 FROM 4     student 5 WHERE 6     sid NOT IN ( 7         SELECT 8             course_id 9         FROM10             score11         LEFT JOIN course ON course.cid = score.course_id12         LEFT JOIN student ON student.sid = score.student_id13         LEFT JOIN teacher ON teacher.tid = course.teacher_id14         WHERE15             teacher.tname = ‘李平老師‘16     )

39、查詢兩門以上不及格課程的同學的學號及其平均成績

1 SELECT student_id,avg(num) from score where num <60 GROUP BY student_id HAVING count(1)>2

40、檢索“004”課程分數小於60，按分數降序排列的同學學號

1 SELECT student_id from score where course_id=4 and num <60 ORDER BY num DESC

41、刪除“002”同學的“001”課程的成績

1 DELETE from score where student_id=2 and course_id=1

 

 

python/MySQL練習題(二)