--1.學(xué)生表--- Student(S,Sname,Sage,Ssex) --S 學(xué)生編號(hào),Sname 學(xué)生姓名,Sage 出生年月,Ssex 學(xué)生性別--2.課程表 -- Course(C,Cname,T) --C --課程編號(hào),Cname 課程名稱,T 教師編號(hào)--3.教師表 -- Teacher(T,Tname) --T 教師編號(hào),Tname 教師姓名--4.成績(jī)表 -- SC(S,C,score) --S 學(xué)生編號(hào),C 課程編號(hào),score 分?jǐn)?shù)
--創(chuàng)建測(cè)試數(shù)據(jù)
create table Student(S varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));insert into Student values('01' , '趙雷' , '1990-01-01' , '男');insert into Student values('02' , '錢電' , '1990-12-21' , '男');insert into Student values('03' , '孫風(fēng)' , '1990-05-20' , '男');insert into Student values('04' , '李云' , '1990-08-06' , '男');insert into Student values('05' , '周梅' , '1991-12-01' , '女');insert into Student values('06' , '吳蘭' , '1992-03-01' , '女');insert into Student values('07' , '鄭竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
create table Course(C varchar(10),Cname varchar(10),T varchar(10));insert into Course values('01' , '語(yǔ)文' , '02');insert into Course values('02' , '數(shù)學(xué)' , '01');
insert into Course values('03' , '英語(yǔ)' , '03');
create table Teacher(T varchar(10),Tname varchar(10));insert into Teacher values('01' , '張三');insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(S varchar(10),C varchar(10),score decimal(18,1));insert into SC values('01' , '01' , 80);insert into SC values('01' , '02' , 90);insert into SC values('01' , '03' , 99);insert into SC values('02' , '01' , 70);insert into SC values('02' , '02' , 60);insert into SC values('02' , '03' , 80);insert into SC values('03' , '01' , 80);insert into SC values('03' , '02' , 80);insert into SC values('03' , '03' , 80);insert into SC values('04' , '01' , 50);insert into SC values('04' , '02' , 30);insert into SC values('04' , '03' , 20);insert into SC values('05' , '01' , 76);insert into SC values('05' , '02' , 87);insert into SC values('06' , '01' , 31);insert into SC values('06' , '03' , 34);insert into SC values('07' , '02' , 89);insert into SC values('07' , '03' , 98);
--題目--1、查詢"01"課程比"02"課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù)
--2、查詢"01"課程比"02"課程成績(jī)低的學(xué)生的信息及課程分?jǐn)?shù)
--3、查詢平均成績(jī)大于等于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)
--4、查詢平均成績(jī)小于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)
--5、查詢所有同學(xué)的學(xué)生編號(hào)、學(xué)生姓名、選課總數(shù)、所有課程的總成績(jī)
--6、查詢"李"姓老師的數(shù)量?
--7、查詢學(xué)過(guò)"張三"老師授課的同學(xué)的信息?
--8、查詢沒(méi)學(xué)過(guò)"張三"老師授課的同學(xué)的信息
--9、查詢學(xué)過(guò)編號(hào)為"01"并且也學(xué)過(guò)編號(hào)為"02"的課程的同學(xué)的信息
--10、查詢學(xué)過(guò)編號(hào)為"01"但是沒(méi)有學(xué)過(guò)編號(hào)為"02"的課程的同學(xué)的信息
--11、查詢沒(méi)有學(xué)全所有課程的同學(xué)的信息
--12、查詢至少有一門(mén)課與學(xué)號(hào)為"01"的同學(xué)所學(xué)相同的同學(xué)的信息
--13、查詢和"01"號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息
--14、查詢沒(méi)學(xué)過(guò)"張三"老師講授的任一門(mén)課程的學(xué)生姓名
--15、查詢兩門(mén)及其以上不及格課程的同學(xué)的學(xué)號(hào)，姓名及其平均成績(jī)
?--16、檢索"01"課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的學(xué)生信息
--17、按平均成績(jī)從高到低顯示所有學(xué)生的所有課程的成績(jī)以及平均成績(jī)
--18、查詢各科成績(jī)最高分、最低分和平均分：以如下形式顯示：? ? --課程ID，課程name，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率? ? --及格為>=60，中等為：70-80，優(yōu)良為：80-90，優(yōu)秀為：>=90
--19、按各科成績(jī)進(jìn)行排序，并顯示排名
--20、查詢學(xué)生的總成績(jī)并進(jìn)行排名
--21、查詢不同老師所教不同課程平均分從高到低顯示?
--22、查詢所有課程的成績(jī)第2名到第3名的學(xué)生信息及該課程成績(jī)
--23、統(tǒng)計(jì)各科成績(jī)各分?jǐn)?shù)段人數(shù)：課程編號(hào),課程名稱,[100-85],[85-70],[70-60],[0-60]及所占百分比
?--24、查詢學(xué)生平均成績(jī)及其名次
?--25、查詢各科成績(jī)前三名的記錄
--26、查詢每門(mén)課程被選修的學(xué)生數(shù)?
--27、查詢出只有兩門(mén)課程的全部學(xué)生的學(xué)號(hào)和姓名
--28、查詢男生、女生人數(shù)
?--29、查詢名字中含有"風(fēng)"字的學(xué)生信息
--30、查詢同名同性學(xué)生名單，并統(tǒng)計(jì)同名人數(shù)?
--31、查詢1990年出生的學(xué)生名單(注：Student表中Sage列的類型是datetime)?
--32、查詢每門(mén)課程的平均成績(jī)，結(jié)果按平均成績(jī)降序排列，平均成績(jī)相同時(shí)，按課程編號(hào)
--33、查詢平均成績(jī)大于等于85的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)?
--34、查詢課程名稱為"數(shù)學(xué)"，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)
?--35、查詢所有學(xué)生的課程及分?jǐn)?shù)情況；
--36、查詢?nèi)魏我婚T(mén)課程成績(jī)?cè)?0分以上的姓名、課程名稱和分?jǐn)?shù)；?
--37、查詢不及格的課程
--38、查詢課程編號(hào)為01且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名；?
--39、求每門(mén)課程的學(xué)生人數(shù)
?--40、查詢選修"張三"老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生信息及其成績(jī)
--41、查詢不同課程成績(jī)相同的學(xué)生的學(xué)生編號(hào)、課程編號(hào)、學(xué)生成績(jī)
?--42、查詢每門(mén)功成績(jī)最好的前兩名?
--43、統(tǒng)計(jì)每門(mén)課程的學(xué)生選修人數(shù)（超過(guò)5人的課程才統(tǒng)計(jì)）。? ? --要求輸出課程號(hào)和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)升序排列--44、檢索至少選修兩門(mén)課程的學(xué)生學(xué)號(hào)
?--45、查詢選修了全部課程的學(xué)生信息?
--46、查詢各學(xué)生的年齡-
-47、查詢本周過(guò)生日的學(xué)生
--48、查詢下周過(guò)生日的學(xué)生
--49、查詢本月過(guò)生日的學(xué)生
--50、查詢下月過(guò)生日的學(xué)生
--參考SQL--1、查詢"01"課程比"02"課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù)
SELECT a.* ,c.* from (select? ? a.*? ? ? from(SELECT * from sc WHERE sc.C in('01'))aleft JOIN(SELECT * from sc where sc.C in('02'))bON a.s =b.s where a.score>b.score ) a ,student cwhere a.s =c.s;
--2、查詢"01"課程比"02"課程成績(jī)低的學(xué)生的信息及課程分?jǐn)?shù)
SELECT a.* ,c.*FROM(SELECT a.*FROM(SELECT * FROM sc WHERE sc.C in('01'))aLEFT JOIN(SELECT * FROM sc WHERE sc.C in('02'))bON a.s =b.s WHERE a.score<b.score) a,student cWHERE a.s =c.s
--3、查詢平均成績(jī)大于等于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)
SELECTb.s,c.Sname,b.avgscoreFROM(SELECT? ? a.s s? ? , avg(a.score) avgscoreFROM sc aGROUP BY a.s) b? ? ,student cWHEREb.avgscore>60 AND c.s =b.s
--4、查詢平均成績(jī)小于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)
SELECTb.s,c.Sname,b.avgscoreFROM(SELECT? ? a.s s? ? , avg(a.score) avgscoreFROM sc aGROUP BY a.s) b? ? ,student cWHEREb.avgscore<60 AND c.s =b.s
--5、查詢所有同學(xué)的學(xué)生編號(hào)、學(xué)生姓名、選課總數(shù)、所有課程的總成績(jī)
SELECT? ? b.s? ? ,c.Sname? ? ,b.num? ? ,b.sumscore? ? FROM(SELECT? ? a.s s? ? ,COUNT(a.c) num? ? ,SUM(a.score) sumscoreFROM? ? sc aGROUP BY a.s) b? ? ,student cWHERE b.s =c.s
--6、查詢"李"姓老師的數(shù)量
SELECTCOUNT(a.Tname)FROM(SELECT? ? Tname TnameFROM? ? teacher? ? WHERE? ? Tname LIKE '李%') a
--7、查詢學(xué)過(guò)"張三"老師授課的同學(xué)的信息
SELECTd.*FROMstudent d,sc eWHEREe.S =d.SANDe.C=(SELECT? ? b.CFROM? ? teacher a? ? ,course bWHERE? ? a.T =b.T AND a.Tname='張三')
?--8、查詢沒(méi)學(xué)過(guò)"張三"老師授課的同學(xué)的信息
SELECTa.*FROMstudent aWHEREa.S NOT in (SELECT? b.SFROMsc a,student b
WHEREa.S=b.S ANDa.C =(SELECT? ? b.c FROM? ? teacher a? ? ,course bWHERE? ? a.T =b.T AND a.Tname ='張三'))
--9、查詢學(xué)過(guò)編號(hào)為"01"并且也學(xué)過(guò)編號(hào)為"02"的課程的同學(xué)的信息
SELECT*FROM sc a,sc b,student cWHEREa.S =b.S AND a.C='01' and b.C='02'? AND c.S =a.S
--10、查詢學(xué)過(guò)編號(hào)為"01"但是沒(méi)有學(xué)過(guò)編號(hào)為"02"的課程的同學(xué)的信息
SELECT*FROMstudent aWHEREa.S in(SELECTDISTINCT(sc.s)FROMscWHEREsc.SNOTin(SELECT SFROM scWHERE sc.C in('02')GROUP BYsc.S))
--11、查詢沒(méi)有學(xué)全所有課程的同學(xué)的信息
SELECTb.*FROMstudent bWHEREb.S in(SELECTa.sFROM(SELECT? ? a.S s? ? ,COUNT(a.C) NUMFROMsc aGROUP BYa.s) aWHEREa.NUM<3)
--12、查詢至少有一門(mén)課與學(xué)號(hào)為"01"的同學(xué)所學(xué)相同的同學(xué)的信息
SELECTe.*FROMstudent eWHEREe.S in (SELECTDISTINCT(c.s)FROMstudent c,sc dWHEREc.s=d.sAND d.C in (SELECT b.cFROM student a,sc? bWHEREa.s =b.s AND a.s ='01'))
--13、查詢和"01"號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息
SELECT? ? a.cFROM? ? sc a? WHERE? ? a.S ='01'? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
SELECT*FROM(SELECTa.*,COUNT(b.c) numFROMstudent a,sc bWHEREa.s=b.S GROUP BYb.S) aWHEREa.num=3 AND a.s <> '01'
--14、查詢沒(méi)學(xué)過(guò)"張三"老師講授的任一門(mén)課程的學(xué)生姓名
SELECT*FROMstudent aWHEREa.S NOT in (SELECTb.SFROM(SELECTb.c ,b.CnameFROMteacher a,course bWHEREa.T=b.TANDa.Tname='張三') a,sc bWHEREa.c=b.C)
--15、查詢兩門(mén)及其以上不及格課程的同學(xué)的學(xué)號(hào)，姓名及其平均成績(jī)
SELECTb.S,b.Sname,a.avgscoreFROM(SELECTAVG(score) avgscore,s SFROMscWHEREsc.score<60GROUP BYsHAVING COUNT(s)>=2) a,student bWHEREa.S=b.S
--16、檢索"01"課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的學(xué)生信息
SELECTb.*FROM(SELECTa.s sFROMsc aWHEREa.C ='01'ANDa.score<60ORDER BY a.score DESC) a,student bWHEREa.s=b.S
--17、按平均成績(jī)從高到低顯示所有學(xué)生的所有課程的成績(jī)以及平均成績(jī)
SELECT*FROM(SELECTb.S,b.C,b.score,c.avgscoreFROMsc bLEFT JOIN(SELECTa.S,AVG(a.score) avgscoreFROMsc aGROUP BY a.S) cON c.S =b.S) a,student bWHEREa.S =b.SORDER BY avgscore DESC
--18、查詢各科成績(jī)最高分、最低分和平均分：以如下形式顯示：--課程ID，課程name，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率--及格為>=60，中等為：70-80，優(yōu)良為：80-90，優(yōu)秀為：>=90
SELECTa.C,b.Cname,MAX(a.score),MIN(a.score),AVG(a.score),(SELECT COUNT(1) FROM sc WHERE sc.C=b.C and score>60)/(SELECT COUNT(1)FROM sc WHERE sc.C=b.c ) jigelvFROM sc a ,course b WHERE a.C =b.CGROUP BYa.CSELECTa.C,a.Cname,MAX(b.score),MIN(b.score),CAST(AVG(b.score) AS DECIMAL(18,2)) pingqunfen,CAST((SELECT COUNT(1) FROM sc WHERE sc.C=a.C AND sc.score>60)/(SELECT COUNT(1) FROM sc WHERE sc.C =a.C) AS DECIMAL(18,2)) jigelv,CAST((SELECT COUNT(1) FROM sc WHERE sc.C =a.c AND sc.score>=70 AND sc.score<80)/(SELECT COUNT(1) FROM sc WHERE sc.c =a.c) AS DECIMAL (18,2)) youlianlvFROMcourse a,sc bWHEREa.C=b.CGROUP BY a.C,a.CnameORDER BY a.C DESC
--19、按各科成績(jī)進(jìn)行排序，并顯示排名
SELECT c.s,c.sname,b.cname,a.score,(SELECT COUNT(1) FROM sc WHERE sc.C=a.c AND sc.score > a.score)+1 mcFROM sc a ,course b,student c WHERE a.c='01'AND a.c=b.c AND a.s=c.sORDER BY mcSELECT c.s,c.sname,b.cname,a.score,(SELECT COUNT(1) FROM sc WHERE sc.C=a.c AND sc.score > a.score)+1 mcFROM sc a ,course b,student c WHERE a.c='02'AND a.c=b.c AND a.s=c.sORDER BY mcSELECT c.s,c.sname,b.cname,a.score,(SELECT COUNT(1) FROM sc WHERE sc.C=a.c AND sc.score > a.score)+1 mcFROM sc a ,course b,student c WHERE a.c='03'AND a.c=b.c AND a.s=c.sORDER BY mc--20、查詢學(xué)生的總成績(jī)并進(jìn)行排名
SELECT b.s,b.sumscore,@rownum:=@rownum +1 AS rownumFROM(SELECTa.s s,SUM(a.score) sumscoreFROMsc aGROUP BYa.SORDER BY sumscore DESC) as b,(SELECT @rownum:=0)r
--21、查詢不同老師所教不同課程平均分從高到低顯示
SELECTc.Tname,b.Cname,AVG(a.score) FROMsc a,course b,teacher cWHEREa.C =b.CAND b.T =c.TGROUP BY a.CORDER BY AVG(a.score) DESC
--22、查詢所有課程的成績(jī)第2名到第3名的學(xué)生信息及該課程成績(jī)
SELECTa.*,b.*FROM(SELECT b.s ,b.C ,b.score,(SELECT COUNT() FROM sc a WHERE a.c='01'AND a.score>b.score)+1 mcFROM sc b? WHERE b.c='01' HAVING mc BETWEEN 2 AND 3UNION ALLSELECT b.s ,b.C ,b.score,(SELECT COUNT() FROM sc a WHERE a.c='02'AND a.score>b.score)+1 mcFROM sc b? WHERE b.c='02' HAVING mc BETWEEN 2 AND 3UNION ALLSELECT b.s ,b.C ,b.score,(SELECT COUNT(*) FROM sc a WHERE a.c='03'AND a.score>b.score)+1 mcFROM sc b? WHERE b.c='03' HAVING mc BETWEEN 2 AND 3) a,student bWHEREa.s =b.s
--23、統(tǒng)計(jì)各科成績(jī)各分?jǐn)?shù)段人數(shù)：課程編號(hào),課程名稱,[100-85],[85-70],[70-60],[0-60]及所占百分比
SELECTa.*FROM(SELECTc.Cname,(SELECT COUNT()FROM sc a WHERE a.score>85 AND a.C=b.c AND c.c=a.C AND? a.C='01') '85以上',(SELECT COUNT()FROM sc a WHERE a.score<85 AND a.C=b.c AND c.c=a.C AND a.score>70 AND a.C='01') '[70_85]',(SELECT COUNT()FROM sc a WHERE a.score>70 AND a.C=b.c? AND c.c=a.C AND a.score>60 AND a.C='01') '[60_70]',(SELECT COUNT()FROM sc a WHERE a.score<60 AND a.C=b.c? AND c.c=a.C AND a.C='01') '60一下',(SELECT COUNT()FROM sc a WHERE a.score>85 AND a.C=b.c? AND c.c=a.C AND a.C='01')/(SELECT COUNT()FROM sc WHERE c='01'AND C=b.c AND c.c=c) '85以上比率',(SELECT COUNT()FROM sc a WHERE a.score<85 AND a.C=b.c? AND c.c=a.C AND a.score>70 AND a.C='01')/(SELECT COUNT()FROM sc WHERE c='01'AND C=b.c AND c.c=c) '[70_85]以上比率',(SELECT COUNT()FROM sc a WHERE a.score>70 AND a.C=b.c? AND c.c=a.C AND a.score>60 AND a.C='01')/(SELECT COUNT()FROM sc WHERE c='01'AND C=b.c AND c.c=c)'[60_70]以上比率',(SELECT COUNT()FROM sc a WHERE a.score<60 AND a.C=b.c AND c.c=a.C? AND a.C='01')/(SELECT COUNT()FROM sc WHERE c='01'AND C=b.c AND c.c=c)'60以上比率'FROM sc b ,course c WHERE b.c=c.c AND c.c='01'UNION ALLSELECTc.Cname,(SELECT COUNT()FROM sc a WHERE a.score>85 AND a.C=b.c AND c.c=a.C AND? a.C='02') '85以上',(SELECT COUNT()FROM sc a WHERE a.score<85 AND a.C=b.c AND c.c=a.C AND a.score>70 AND a.C='02') '[70_85]',(SELECT COUNT()FROM sc a WHERE a.score>70 AND a.C=b.c? AND c.c=a.C AND a.score>60 AND a.C='02') '[60_70]',(SELECT COUNT()FROM sc a WHERE a.score<60 AND a.C=b.c? AND c.c=a.C AND a.C='02') '60一下',(SELECT COUNT()FROM sc a WHERE a.score>85 AND a.C=b.c? AND c.c=a.C AND a.C='02')/(SELECT COUNT()FROM sc WHERE c='02'AND C=b.c AND c.c=c) '85以上比率',(SELECT COUNT()FROM sc a WHERE a.score<85 AND a.C=b.c? AND c.c=a.C AND a.score>70 AND a.C='02')/(SELECT COUNT()FROM sc WHERE c='02'AND C=b.c AND c.c=c)'[70_85]以上比率',(SELECT COUNT()FROM sc a WHERE a.score>70 AND a.C=b.c? AND c.c=a.C AND a.score>60 AND a.C='02')/(SELECT COUNT()FROM sc WHERE c='02'AND C=b.c AND c.c=c)'[60_70]以上比率',(SELECT COUNT()FROM sc a WHERE a.score<60 AND a.C=b.c AND c.c=a.C? AND a.C='02')/(SELECT COUNT()FROM sc WHERE c='02'AND C=b.c AND c.c=c)'60以上比率'FROM sc b ,course c WHERE b.c=c.c AND c.c='02'UNION ALLSELECTc.Cname,(SELECT COUNT()FROM sc a WHERE a.score>85 AND a.C=b.c AND c.c=a.C AND? a.C='03') '85以上',(SELECT COUNT()FROM sc a WHERE a.score<85 AND a.C=b.c AND c.c=a.C AND a.score>70 AND a.C='03') '[70_85]',(SELECT COUNT()FROM sc a WHERE a.score>70 AND a.C=b.c? AND c.c=a.C AND a.score>60 AND a.C='03') '[60_70]',(SELECT COUNT()FROM sc a WHERE a.score<60 AND a.C=b.c? AND c.c=a.C AND a.C='03') '60一下',(SELECT COUNT()FROM sc a WHERE a.score>85 AND a.C=b.c? AND c.c=a.C AND a.C='03')/(SELECT COUNT()FROM sc WHERE c='03'AND C=b.c AND c.c=c) '85以上比率',(SELECT COUNT()FROM sc a WHERE a.score<85 AND a.C=b.c? AND c.c=a.C AND a.score>70 AND a.C='03')/(SELECT COUNT()FROM sc WHERE c='03'AND C=b.c AND c.c=c)'[70_85]以上比率',(SELECT COUNT()FROM sc a WHERE a.score>70 AND a.C=b.c? AND c.c=a.C AND a.score>60 AND a.C='03')/(SELECT COUNT()FROM sc WHERE c='03'AND C=b.c AND c.c=c)'[60_70]以上比率',(SELECT COUNT()FROM sc a WHERE a.score<60 AND a.C=b.c AND c.c=a.C? AND a.C='01')/(SELECT COUNT()FROM sc WHERE c='03'AND C=b.c AND c.c=c)'60以上比率'FROM sc b ,course c WHERE b.c=c.c AND c.c='03') a
--24、查詢學(xué)生平均成績(jī)及其名次
SELECTa.s,a.avgscore,@rownum:=@rownum +1 AS rownumFROM (SELECTs,AVG(score) avgscoreFROMsc GROUP BY sORDER BY avgscore DESC) a,(SELECT @rownum:=0)r
--25、查詢各科成績(jī)前三名的記錄
SELECTa.*,b.*FROM(SELECT b.s ,b.C ,b.score,(SELECT COUNT() FROM sc a WHERE a.c='01'AND a.score>b.score)+1 mcFROM sc b? WHERE b.c='01' HAVING mc BETWEEN 1 AND 3UNION ALLSELECT b.s ,b.C ,b.score,(SELECT COUNT() FROM sc a WHERE a.c='02'AND a.score>b.score)+1 mcFROM sc b? WHERE b.c='02' HAVING mc BETWEEN 1 AND 3UNION ALLSELECT b.s ,b.C ,b.score,(SELECT COUNT(*) FROM sc a WHERE a.c='03'AND a.score>b.score)+1 mcFROM sc b? WHERE b.c='03' HAVING mc BETWEEN 1 AND 3) a,student bWHEREa.s =b.s
--26、查詢每門(mén)課程被選修的學(xué)生數(shù)
SELECTa.Cname,COUNT(b.c)FROM sc b,course a WHERE a.c =b.cGROUP BY b.c
--27、查詢出只有兩門(mén)課程的全部學(xué)生的學(xué)號(hào)和姓名?
SELECTb.S,b.Sname,a.countcFROM(SELECTs ,COUNT(c) countcFROM scGROUP BY sHAVING countc =2) a,student bWHEREa.s=b.s
--28、查詢男生、女生人數(shù)
select s.Ssex,COUNT()from student s where s.Ssex='男'UNIONselect s.Ssex,COUNT()from student s where s.Ssex='女'
--29、查詢名字中含有"風(fēng)"字的學(xué)生信息
SELECT*,count(*)FROM student WHERESname rLIKE '風(fēng)'
--30、查詢同名同性學(xué)生名單，并統(tǒng)計(jì)同名人數(shù)
SELECTs.Sname,s.Ssex,COUNT(*) countnumfromstudent sGROUP BYs.Sname,s.SsexHAVINGcountnum>=2
--31、查詢1990年出生的學(xué)生名單(注：Student表中Sage列的類型是datetime)
SELECT*FROMstudentWHERESage LIKE '1990%'
--32、查詢每門(mén)課程的平均成績(jī)，結(jié)果按平均成績(jī)降序排列，平均成績(jī)相同時(shí)，按課程編號(hào)
SELECTa.Cname,AVG(sc.score) avgscoreFROM sc ,course aWHERE sc.C =a.CGROUP BY sc.CORDER BY avgscore DESC
--33、查詢平均成績(jī)大于等于85的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)
SELECT*FROM(SELECTa.Sname,a.S,AVG(sc.score) avgscoreFROM sc ,student aWHERE sc.s =a.s GROUP BY sc.sORDER BY avgscore DESC) aWHEREa.avgscore>85
--34、查詢課程名稱為"數(shù)學(xué)"，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)
SELECT*FROMsc a,course bWHERE a.c =b.c AND b.Cname='數(shù)學(xué)' AND a.score>60
--35、查詢所有學(xué)生的課程及分?jǐn)?shù)情況；
SELECT*FROMstudent a,course b,sc cWHEREa.S=c.S AND b.C =c.C
--36、查詢?nèi)魏我婚T(mén)課程成績(jī)?cè)?0分以上的姓名、課程名稱和分?jǐn)?shù)；
SELECTb.Sname,c.Cname,a.scoreFROMsc? a, student b,course cWHEREa.score>70 AND a.C =c.C AND a.S =b.S
--37、查詢不及格的課程
SELECTb.Sname,c.Cname,a.scoreFROMsc? a, student b,course cWHEREa.score<60 AND a.C =c.C AND a.S =b.S
--38、查詢課程編號(hào)為01且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名；
SELECTc.S,c.Sname,a.Cname,b.scoreFROMcourse a ,sc b ,student cWHERE a.C =b.C AND b.score>=80 AND c.S =b.S AND a.C='01'
--39、求每門(mén)課程的學(xué)生人數(shù)
SELECTb.Cname,COUNT(a.c)FROMsc a ,course bWHEREb.c=a.CGROUP BYa.C
--40、查詢選修"張三"老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生信息及其成績(jī)
SELECTb.*,MAX(c.score)FROMteacher a,student b ,sc c ,course dWHEREa.T =d.T AND d.C =c.C AND c.S =b.S AND? a.Tname ='張三'
--41、查詢不同課程成績(jī)相同的學(xué)生的學(xué)生編號(hào)、課程編號(hào)、學(xué)生成績(jī)
SELECT c.* FROM sc c GROUP BY c.C,c.score HAVING COUNT(*)>1;
--42、查詢每門(mén)功成績(jī)最好的前兩名
SELECTa.*,b.*FROM(SELECT b.s ,b.C ,b.score,(SELECT COUNT() FROM sc a WHERE a.c='01'AND a.score>b.score)+1 mcFROM sc b? WHERE b.c='01' HAVING mc BETWEEN 1 AND 2UNION ALLSELECT b.s ,b.C ,b.score,(SELECT COUNT() FROM sc a WHERE a.c='02'AND a.score>b.score)+1 mcFROM sc b? WHERE b.c='02' HAVING mc BETWEEN 1 AND 2UNION ALLSELECT b.s ,b.C ,b.score,(SELECT COUNT(*) FROM sc a WHERE a.c='03'AND a.score>b.score)+1 mcFROM sc b? WHERE b.c='03' HAVING mc BETWEEN 1 AND 2) a,student bWHEREa.s =b.s
--43、統(tǒng)計(jì)每門(mén)課程的學(xué)生選修人數(shù)（超過(guò)5人的課程才統(tǒng)計(jì)）。要求輸出課程號(hào)和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)升序排列?
SELECTb.C,b.Cname ,COUNT(a.C) countcFROMsc a,course bWHEREa.c=b.C GROUP BYa.CHAVING countc>5ORDER BY countc DESC
--44、檢索至少選修兩門(mén)課程的學(xué)生學(xué)號(hào)
SELECTsc.S,COUNT(sc.C) countcFROMscGROUP BY sc.SHAVING countc >=2
--45、查詢選修了全部課程的學(xué)生信息
SELECTa.*,COUNT(sc.C) countcFROMsc ,student? aWHERE sc.S =a.SGROUP BY sc.SHAVING countc =3
--46、查詢各學(xué)生的年齡
SELECT? ? s.s,? ? s.sname,? ? EXTRACT(YEAR FROM NOW())-EXTRACT(YEAR FROM s.sage) 年齡FROM? ? student s
--47、查詢本周過(guò)生日的學(xué)生
SELECTs.S,s.Sname,s.SageFROM student sWHERE YEARWEEK(DATE_FORMAT(s.Sage,'%Y-%m-%d')) =YEARWEEK(NOW())
--48、查詢下周過(guò)生日的學(xué)生
SELECTs.S,s.Sname,s.SageFROM student sWHERE YEARWEEK(DATE_FORMAT(s.Sage,'%Y-%m-%d')) =YEARWEEK(NOW())+1
--49、查詢本月過(guò)生日的學(xué)生
SELECTs.S,s.Sname,s.SageFROM student sWHERE MONTH(DATE_FORMAT(s.Sage,'%Y-%m-%d')) =MONTH(NOW())
--50、查詢下月過(guò)生日的學(xué)生
SELECTs.S,s.Sname,s.SageFROM student sWHERE MONTH(DATE_FORMAT(s.Sage,'%Y-%m-%d')) =MONTH(NOW())+1
