select a.S# from (select s#,score from SC where C#='001') a,(select s#,score from SC where C#='002') b where a.score>b.score and a.s#=b.s#;
2、查詢平均成績大于60分的同學(xué)的學(xué)號和平均成績;
select S#,avg(score) from sc groupby S# havingavg(score) >60;
3、查詢所有同學(xué)的學(xué)號、姓名、選課數(shù)、總成績;
select Student.S#,Student.Sname,count(SC.C#),sum(score) from Student leftOuterjoin SC on Student.S#=SC.S# groupby Student.S#,Sname
4、查詢姓“李”的老師的個數(shù);
selectcount(distinct(Tname)) from Teacher where Tname like'李%';
5、查詢沒學(xué)過“葉平”老師課的同學(xué)的學(xué)號、姓名;
select Student.S#,Student.Sname from Student where S# notin (selectdistinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='葉平');
select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'andexists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');
7、查詢學(xué)過“葉平”老師所教的所有課的同學(xué)的學(xué)號、姓名;
select S#,Sname
from Student
where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='葉平'groupby S# havingcount(SC.C#)=(selectcount(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname='葉平'));
Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2
from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2
9、查詢所有課程成績小于60分的同學(xué)的學(xué)號、姓名;
select S#,Sname
from Student
where S# notin (select Student.S# from Student,SC where S.S#=SC.S# and score>60);
10、查詢沒有學(xué)全所有課的同學(xué)的學(xué)號、姓名;
select Student.S#,Student.Snamefrom Student,SCwhere Student.S#=SC.S# groupby Student.S#,Student.Sname havingcount(C#) <(selectcount(C#) from Course);
selectdistinct SC.S#,Snamefrom Student,SCwhere Student.S#=SC.S# and C# in (select C# from SC where S#='001');
13、把“SC”表中“葉平”老師教的課的成績都更改為此課程的平均成績;
update SC set score=(selectavg(SC_2.score)from SC SC_2where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='葉平');
SELECT S# as 學(xué)生ID,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 數(shù)據(jù)庫,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企業(yè)管理,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英語,COUNT(*) AS 有效課程數(shù), AVG(t.score) AS 平均成績FROM SC AS tGROUPBY S#ORDERBYavg(t.score)
18、查詢各科成績最高和最低的分:以如下形式顯示:課程ID,最高分,最低分
SELECT L.C# As 課程ID,L.score AS 最高分,R.score AS 最低分FROM SC L ,SC AS RWHERE L.C# = R.C# andL.score = (SELECTMAX(IL.score)FROM SC AS IL,Student AS IMWHERE L.C# = IL.C# and IM.S#=IL.S#GROUPBY IL.C#)ANDR.Score = (SELECTMIN(IR.score)FROM SC AS IRWHERE R.C# = IR.C#GROUPBY IR.C#);
19、按各科平均成績從低到高和及格率的百分?jǐn)?shù)從高到低順序
SELECT t.C# AS 課程號,max(course.Cname)AS 課程名,isnull(AVG(score),0) AS 平均成績,100 * SUM(CASEWHEN isnull(score,0)>=60THEN1ELSE0END)/COUNT(*) AS 及格百分?jǐn)?shù)FROM SC T,Coursewhere t.C#=course.C#GROUPBY t.C#ORDERBY100 * SUM(CASEWHEN isnull(score,0)>=60THEN1ELSE0END)/COUNT(*) DESC
SELECTmax(Z.T#) AS 教師ID,MAX(Z.Tname) AS 教師姓名,C.C# AS 課程ID,MAX(C.Cname) AS 課程名稱,AVG(Score) AS 平均成績FROM SC AS T,Course AS C ,Teacher AS Zwhere T.C#=C.C# and C.T#=Z.T#GROUPBY C.C#ORDERBYAVG(Score) DESC
SELECT SC.C# as 課程ID, Cname as 課程名稱,SUM(CASEWHEN score BETWEEN 85AND100THEN1ELSE0END) AS [100 - 85],SUM(CASEWHEN score BETWEEN 70AND85THEN1ELSE0END) AS [85 - 70],SUM(CASEWHEN score BETWEEN 60AND70THEN1ELSE0END) AS [70 - 60],SUM(CASEWHEN score < 60THEN1ELSE0END) AS [60 -]FROM SC,Coursewhere SC.C#=Course.C#GROUPBY SC.C#,Cname;
24、查詢學(xué)生平均成績及其名次
SELECT1+(SELECTCOUNT( distinct 平均成績)FROM (SELECT S#,AVG(score) AS 平均成績FROM SCGROUPBY S#) AS T1WHERE 平均成績 > T2.平均成績) as 名次,S# as 學(xué)生學(xué)號,平均成績FROM (SELECT S#,AVG(score) 平均成績FROM SCGROUPBY S#) AS T2ORDERBY 平均成績 desc;
25、查詢各科成績前三名的記錄:(不考慮成績并列情況)
SELECT t1.S# as 學(xué)生ID,t1.C# as 課程ID,Score as 分?jǐn)?shù)FROM SC t1WHERE score IN (SELECT TOP 3 scoreFROM SCWHERE t1.C#= C#ORDERBY score DESC)ORDERBY t1.C#;
select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and Score>80and C#='003';
39、求選了課程的學(xué)生人數(shù)
selectcount(*) from sc;
40、查詢選修“葉平”老師所授課程的學(xué)生中,成績最高的學(xué)生姓名及其成績
select Student.Sname,scorefrom Student,SC,Course C,Teacherwhere Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname='葉平'and SC.score=(selectmax(score)from SC where C#=C.C# );
41、查詢各個課程及相應(yīng)的選修人數(shù)
selectcount(*) from sc groupby C#;
42、查詢不同課程成績相同的學(xué)生的學(xué)號、課程號、學(xué)生成績
selectdistinct A.S#,B.score from SC A ,SC B where A.Score=B.Score and A.C# <>B.C# ;
43、查詢每門功成績最好的前兩名
SELECT t1.S# as 學(xué)生ID,t1.C# as 課程ID,Score as 分?jǐn)?shù)FROM SC t1WHERE score IN (SELECT TOP 2 scoreFROM SCWHERE t1.C#= C#ORDERBY score DESC)ORDERBY t1.C#;