1.查詢“某1”課程比“某2”課程成績高的所有學(xué)生的學(xué)號；

select a.sid from (select sid,score from SC where cid=1) a,(select sid,score from SC where cid=2) b where a.score>b.score and a.sid=b.sid;

2.查詢平均成績大于60分的同學(xué)的學(xué)號和平均成績；

select sid,AVG(score) from SC group by sid having avg(score) > 60;

3.查詢所有同學(xué)的學(xué)號、姓名、選課數(shù)、總成績

select Student.sid,Student.sname,count(SC.cid),sum(SC.score) from Student left join SC on Student.sid=SC.sid group by sid,sname;?

4.查詢姓“李”的老師的個數(shù)；

select count(Teacher.tid) from Teacher where Teacher.tname like '李%';

5.查詢沒學(xué)過“張三”老師課的同學(xué)的學(xué)號、姓名；

select s.sid,s.sname from Student as s where sid not in(select DISTINCT(s.sid) from Course as c,SC as s,Teacher as t where c.cid=s.cid AND c.tid=t.tid AND t.tname='張三');?

6.查詢學(xué)過“```”并且也學(xué)過編號“```”課程的同學(xué)的學(xué)號、姓名；

select a.sid,a.sname? from?

(select s.sid,s.sname from Student as s,Course as c,SC as? sc where c.cname='數(shù)學(xué)' AND s.sid=sc.sid AND c.cid=sc.cid) a,?

(select s.sid,s.sname from Student as s,Course as c,SC as? sc where c.cname='語文' AND s.sid=sc.sid AND c.cid=sc.cid) b

?WHERE a.sid=b.sid;

7.查詢學(xué)過“張三”老師所教的所有課的同學(xué)的學(xué)號、姓名；

SELECT a.sid,a.sname from (select s.sid,s.sname from Student as s,Course as c,SC as sc ,Teacher as t where t.tname='王五' AND s.sid=sc.sid AND c.cid=sc.cid AND c.tid=t.tid) a;

8.查詢課程編號“01”的成績比課程編號“02”課程低的所有同學(xué)的學(xué)號、姓名；

SELECT DISTINCT(a.sid),a.sname from (select sc.score,s.sid,s.sname from Student as s,SC as sc where sc.cid=01) a, (select sc.score,s.sid,s.sname from Student as s,SC as sc where sc.cid=02) b where a.score<b.score AND a.sid=b.sid;

9.查詢所有課程成績小于60分的同學(xué)的學(xué)號、姓名；

select s.sid,s.sname,sc.cid from Student as s,SC as sc where sc.score<60 AND sc.sid=s.sid;

10.查詢沒有學(xué)全所有課的同學(xué)的學(xué)號、姓名；

select s.sid,s.sname from Student as s,SC as sc where s.sid=sc.sid GROUP BY s.sid,s.sname HAVING count(sc.cid)<(select count(cid) from Course);

11.查詢至少有一門課與學(xué)號為“01”的同學(xué)所學(xué)相同的同學(xué)的學(xué)號和姓名；

? ? SELECT? Student.sid,Student.sname FROM SC,Student WHERE? EXISTS (

? ? ? ? SELECT cid FROM SC WHERE sid=01 AND SC.cid=cid

? ? ) AND Student.sid=SC.sid GROUP BY Student.sid,Student.sname;

12.查詢和"01"號的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的學(xué)號和姓名?

select * from Student where sid in (

SELECT sid FROM SC WHERE?

cid in (SELECT cid FROM SC WHERE sid=01) AND sid!=01 GROUP BY sid HAVING COUNT(*)=(SELECT COUNT(*) FROM SC WHERE sid=01)

)

13.把“SC”表中“張三”老師教的課的成績都更改為此課程的平均成績；

update SC,(

SELECT t.tid,avg(score) as ascore

from Course as c,SC as sc,Teacher as t?

WHERE t.tname='張三' AND c.tid=t.tid AND c.cid=sc.cid

)a set score=a.ascore

;

14、查詢沒學(xué)過"張三"老師講授的任一門課程的學(xué)生姓名

SELECT * FROM Student WHERE sid in(

? ? ? ? SELECT sid FROM SC WHERE cid NOT in(

? ? ? ? ? ? ? ? ? ? SELECT cid

? ? ? ? ? ? ? ? ? ? FROM Teacher,Course WHERE Teacher.tname='張三' AND Teacher.tid=Course.tid

? ? ? ? )

)

15、查詢兩門及其以上不及格課程的同學(xué)的學(xué)號，姓名及其平均成績?

SELECT s.sid,s.sname,AVG(score)

FROM SC sc? JOIN Student s on sc.sid=s.sid WHERE

sc.score<60? GROUP BY s.sid,s.sname HAVING COUNT(sc.score)>=2

16、檢索"01"課程分數(shù)小于60，按分數(shù)降序排列的學(xué)生信息

SELECT? Student.*,SC.score from Student,SC WHERE EXISTS (

SELECT? sid,score FROM SC WHERE cid=01 AND score<60 AND sid=Student.sid

) AND Student.sid=SC.sid AND SC.cid=01 ORDER BY SC.score DESC;

17、按平均成績從高到低顯示所有學(xué)生的所有課程的成績以及平均成績

SELECT a.*

? ? ? ?,SUM(CASE WHEN b.cid='01' THEN b.score ELSE 0 END) AS s01

? ? ? ?,SUM(CASE WHEN b.cid='02' THEN b.score ELSE 0 END) AS s02

? ? ? ?,SUM(CASE WHEN b.cid='03' THEN b.score ELSE 0 END) AS s03

? ? ? ?,AVG(CASE WHEN b.score IS NULL THEN 0 ELSE b.score END) avs

FROM Student a

LEFT JOIN SC b

ON a.sid=b.sid

GROUP BY 1,2,3,4

ORDER BY avs DESC;

18.查詢各科成績最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率

SELECT a.cid

? ? ? ?,a.cname

? ? ? ?,MAX(b.score)

? ? ? ?,MIN(b.score)

? ? ? ?,AVG(b.score)

? ? ? ?,SUM(CASE WHEN b.score>=60 THEN 1 ELSE 0 END)/COUNT(1)

? ? ? ?,SUM(CASE WHEN b.score>=70 AND b.score<80 THEN 1 ELSE 0 END)/COUNT(1)

? ? ? ?,SUM(CASE WHEN b.score>=80 AND b.score<90 THEN 1 ELSE 0 END)/COUNT(1)

? ? ? ?,SUM(CASE WHEN b.score>=90 THEN 1 ELSE 0 END)/COUNT(1)

FROM Course a

INNER JOIN SC b

ON a.cid=b.cid

GROUP BY 1,2;

19.按各科平均成績從低到高和及格率的百分數(shù)從高到低順序

SELECT cid,avg_score,CONCAT(de,'%') 及格率

?FROM(

SELECT sc0.cid,

? ? AVG(sc0.score) avg_score,

? ? CAST((

? ? ? ? SELECT COUNT(1) FROM SC WHERE cid=sc0.cid AND score>=60

? ? )*1.0/(

? ? ? ? SELECT COUNT(1) FROM SC WHERE cid=sc0.cid

? ? )*100 AS DECIMAL(10,2)) de

FROM SC sc0

GROUP BY sc0.cid

ORDER BY avg_score,de DESC)a;

20、查詢學(xué)生的總成績并進行排名

select a.sid,a.sname,a.sage,a.sum,count(b.sum) rank

? ? from?

(select a.sid,

? ? a.sname,

? ? a.sage,

? ? a.ssex,

? ? sum(b.score) sum

? ? from student a inner join sc b?

? ? on a.sid=b.sid

? ? group by a.sid,

? ? a.sname,

? ? a.sage,

? ? a.ssex) a left join?

(select a.sid,

? ? a.sname,

? ? a.sage,

? ? a.ssex,

? ? sum(b.score) sum

? ? from student a inner join sc b?

? ? on a.sid=b.sid

? ? group by a.sid,

? ? a.sname,

? ? a.sage,

? ? a.ssex) b

? ? on 1=1

? ? where b.sum>a.sum

? ? group by a.sid,a.sname,a.sage,a.sum order by rank;

21、查詢不同老師所教不同課程平均分從高到低顯示?

SELECT Course.tid,Teacher.tname,a.cid,a.avg_score

? ? FROM? (SELECT cid,avg(score) avg_score FROM SC GROUP BY cid ) a,Course,Teacher

? ? WHERE a.cid=Course.cid AND Teacher.tid=Course.tid ORDER BY a.avg_score DESC;

22、查詢所有課程的成績第2名到第3名的學(xué)生信息及該課程成績

SELECT s.*,sc0.*

? ? FROM SC sc0 INNER JOIN Student s ON s.sid=sc0.sid

WHERE (SELECT COUNT(DISTINCT score) FROM SC WHERE sc0.cid=SC.cid AND sc0.score>SC.score) BETWEEN 2 AND 3

ORDER BY sc0.cid;

23、統(tǒng)計各科成績各分數(shù)段人數(shù)：課程編號,課程名稱,[100-85],[85-70],[70-60],[0-60]及所占百分比

這道題 太復(fù)雜了懶的寫

SELECT cid,count(SELECT score FROM SC WHERE score BETWEEN 85 AND 100) a,count(SELECT score FROM SC WHERE score BETWEEN 85 AND 100)/count(score),count(score) FROM SC? GROUP BY cid;

SELECT count(1) FROM (SELECT score FROM SC WHERE score BETWEEN 85 AND 100)a;

24、查詢學(xué)生平均成績及其名次?

SELECT a.sid,

a.avg_score,

sum(case when a.avg_score<b.avg_score then 1 else 0 end)+1 scc

FROM (SELECT sid,avg(score) avg_score FROM SC GROUP BY sid) a INNER JOIN?

(SELECT sid,avg(score) avg_score FROM SC GROUP BY sid) b

GROUP BY a.sid,

a.avg_score

ORDER BY scc;

25、查詢各科成績前三名的記錄

SELECT sc0.*,

? ? ? ? ? ? (SELECT count(1) FROM SC WHERE SC.cid=sc0.cid AND SC.score>sc0.score)+1 rank

? ? FROM SC sc0

GROUP BY 2,1,3

HAVING rank<=3

ORDER BY sc0.cid,rank;

26.查詢每門課程被選修的學(xué)生數(shù)

SELECT sc.cid,count(sc.sid) from Course c,SC sc WHERE c.cid=sc.cid GROUP BY sc.cid;

27.查詢出只選修了一門課程的全部學(xué)生的學(xué)號和姓名

SELECT s.sid,s.sname from SC as sc,Student AS s WHERE sc.sid=s.sid GROUP BY sc.sid,s.sname HAVING COUNT(cid)=2;

28、查詢男生、女生人數(shù)?

SELECT count(ssex='女'),count(ssex='男') FROM Student;

29、查詢名字中含有"風(fēng)"字的學(xué)生信息

SELECT Student.* FROM Student WHERE sname like '%風(fēng)%';

30、查詢同名同性學(xué)生名單，并統(tǒng)計同名人數(shù)?

SELECT sname,ssex,count(1)

FROM Student GROUP BY 1,2?

HAVING count(1)>1;

31、查詢1990年出生的學(xué)生名單(注：Student表中Sage列的類型是datetime)?

SELECT Student.* FROM Student WHERE YEAR(sage)=1990;

32.查詢每門課程的平均成績，結(jié)果按平均成績升序排列，平均成績相同時，按課程號降序排列

SELECT cid,AVG(score) from SC GROUP BY cid order by avg(score),cid DESC;?

37.查詢不及格的課程，并按課程號從大到小排列?

SELECT cid,score FROM SC WHERE score<60? order by cid DESC;

38.查詢課程編號為"01"且課程成績在60分以上的學(xué)生的學(xué)號和姓名；

SELECT s.sid,s.sname,score,sc.cid

FROM SC as sc,Student as s

WHERE sc.sid=s.sid AND sc.cid=01 AND score>60;?

40.查詢選修“張三”老師所授課程的學(xué)生中，成績最高的學(xué)生姓名及其成績

這道題寫的時候想出了兩種方達，都能實現(xiàn)

SELECT *

FROM? (SELECT s.sid as aid,s.sname as aname,sc.score as ascore,t.tname FROM SC as sc,Student s,Course as c,Teacher as t?

WHERE sc.sid=s.sid AND c.cid=sc.cid AND c.tid=t.tid AND t.tname="張三" ORDER BY score) a? ? LIMIT 0,1;

SELECT s.sid,s.sname,a.ascore

FROM SC as sc,Student s,Course as c,Teacher as t ,

(SELECT MAX(score) ascore FROM SC as sc,Student s,Course as c,Teacher as t?

WHERE sc.sid=s.sid AND c.cid=sc.cid AND c.tid=t.tid AND t.tname="張三" ) a? ? ? ? ?

WHERE sc.sid=s.sid AND c.cid=sc.cid AND c.tid=t.tid AND t.tname="張三" AND sc.score=a.ascore;

42、查詢每門功課成績最好的前兩名?

SELECT sc.* FROM SC sc WHERE (SELECT COUNT(1) FROM SC WHERE SC.cid=sc.cid AND score>sc.score)<2 ORDER BY sc.cid,sc.score;

43、統(tǒng)計每門課程的學(xué)生選修人數(shù)（超過5人的課程才統(tǒng)計）。要求輸出課程號和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號升序排列??

SELECT cid,count(1) count

FROM SC GROUP BY cid HAVING count(1)>=5 ORDER BY count desc,cid ;

44、檢索至少選修兩門課程的學(xué)生學(xué)號

SELECT a.*,b.count

FROM Student a INNER JOIN (SELECT sid,count(cid) count? FROM SC GROUP BY sid HAVING count>1)b

? ? ON a.sid=b.sid GROUP BY 1,2,3,4;

45、查詢選修了全部課程的學(xué)生信息?

SELECT sid,count(cid) count_c FROM SC?

GROUP BY sid?

HAVING count_c=(SELECT COUNT(1) FROM Course);

46、查詢各學(xué)生的年齡

SELECT Student.*,YEAR(CURDATE())-YEAR(Student.sage) FROM Student;

47、查詢本周過生日的學(xué)生

SELECT * FROM Student WHERE WEEKOFYEAR(NOW())-WEEKOFYEAR(sage)=0;

48、查詢下周過生日的學(xué)生

SELECT * FROM Student WHERE WEEKOFYEAR(NOW())-WEEKOFYEAR(sage)=-1;

49、查詢本月過生日的學(xué)生

SELECT * FROM Student WHERE MONTH(NOW())-MONTH(sage)=0;

50、查詢下月過生日的學(xué)生

SELECT * FROM Student WHERE MONTH(NOW())-MONTH(sage)=-1;