强力推荐:SQL 50句
上一篇 / 下一篇 2008-11-27 13:27:37 / 个人分类:数据库
Student(S#,Sname,Sage,Ssex) 学生表 51Testing软件测试网iRs wGW
Course(C#,Cname,T#) 课程表
1?#Cs!H;`2u/e*s0SC(S#,C#,score) 成绩表
x&A!G2^f-E0Teacher(T#,Tname) 教师表
;UO _xs)H\k5[y0问题: 51Testing软件测试网l&ga5P e_5n
1、查询“001”课程比“002”课程成绩高的所有学生的学号;
$ws a/F7TQq0 select a.S# from (select s#,score from SC where C#='001') a,(select s#,score 51Testing软件测试网6|!f'rjW~#Qz
from SC where C#='002') b
'W{ a&U!o#V1\0 where a.score>b.score and a.s#=b.s#; 51Testing软件测试网J M'e$Z8h*m-A)nf
2、查询平均成绩大于60分的同学的学号和平均成绩; 51Testing软件测试网;z CJ;|c?})N
select S#,avg(score)
d W$icx0 from sc
7K+Np E a?s'T0 group by S# having avg(score) >60;
wRaF8jC_{03、查询所有同学的学号、姓名、选课数、总成绩; 51Testing软件测试网R4[{ b;P!MU
select Student.S#,Student.Sname,count(SC.C#),sum(score)
A+l@'fd2?2A0 from Student left Outer join SC on Student.S#=SC.S#
(og bC]1RxV.@7k0 group by Student.S#,Sname
.U9b\)Z,o7~ Q04、查询姓“李”的老师的个数;
2J0[Q7A&Cnn0 select count(distinct(Tname))
t&Roe+ykc,U-x0 from Teacher
8j \7]fhH3r~&j0 where Tname like '李%'; 51Testing软件测试网(e+J8]'{x
5、查询没学过“叶平”老师课的同学的学号、姓名; 51Testing软件测试网2VY*GjS.?
select Student.S#,Student.Sname
0J'm)p0D` `8_-q MK RUJ2f0 from Student 51Testing软件测试网'S_,yJ6Z%l S
where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平');
bO5]#Q ^5A7q06、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名; 51Testing软件测试网J#r/na$VVm\
select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002'); 51Testing软件测试网 V,M`bc
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名; 51Testing软件测试网 dN&r M8z(Z ow
select S#,Sname 51Testing软件测试网|4EN+gMp&^C
from Student 51Testing软件测试网d!f)ld)B7@.i
where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平' group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname='叶平')); 51Testing软件测试网"Tb#M7z:rV
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
n] z;pXsUD1S l0c0 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 51Testing软件测试网X LE kA+] S
from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2 <score;
iV6f ]-s09、查询所有课程成绩小于60分的同学的学号、姓名; 51Testing软件测试网0r6])UG,L N
select S#,Sname 51Testing软件测试网Ww~o l,Z!D
from Student 51Testing软件测试网7|1c$}_0G-D
where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60); 51Testing软件测试网9x9l [W RpP?z
10、查询没有学全所有课的同学的学号、姓名; 51Testing软件测试网(\7bzsB1nN3xf
select Student.S#,Student.Sname 51Testing软件测试网%p K!hR:? aT#]
from Student,SC 51Testing软件测试网X\!cB3b+Z9? aO{%`
where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);
j#T~Q0R(@8B011、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
:e!MF-DbN0i0 select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#='1001'; 51Testing软件测试网0e$^1|\TD,w fG
12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名; 51Testing软件测试网y9Ds`k(GO'V_f
select distinct SC.S#,Sname
%uK$~H7M/L ^ Hcoe0 from Student,SC
0yWpB5o+d0 where Student.S#=SC.S# and C# in (select C# from SC where S#='001'); 51Testing软件测试网I4qi:gj!ZJ bA#K
13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩; 51Testing软件测试网:h&C_G`8](G!M
update SC set score=(select avg(SC_2.score) 51Testing软件测试网5n;UD C3h
from SC SC_2
z0wD.X~@5a(W5?0 where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平'); 51Testing软件测试网ZMz+Og2P$s
14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;
6LW)H7E.FFS9J:Y0 select S# from SC where C# in (select C# from SC where S#='1002')
~fh0S5L0 group by S# having count(*)=(select count(*) from SC where S#='1002'); 51Testing软件测试网"p'r"~Tgv1AU _
15、删除学习“叶平”老师课的SC表记录; 51Testing软件测试网 Z.t#B$HS:q,~
Delect SC
&y q!A4M@.A h|0 from course ,Teacher 51Testing软件测试网HK:r.oBu
where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平';
{2R}K _P}2p016、向SC表中插入一些记录,这些记录要求符合以下条件:没有上过编号“003”课程的同学学号、2、
Zw;yT @0 号课的平均成绩; 51Testing软件测试网Nmr'u4r8J5wn:V
Insert SC select S#,'002',(Select avg(score) 51Testing软件测试网/dav+wZ Jl
from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002'); 51Testing软件测试网5D5O yh+c"k `+?
17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,按如下形式显示: 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分
.P H?8S1w fv0 SELECT S# as 学生ID
H%ke{6rKd0 ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 数据库
+gA1j0`;IQI XKe0 ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企业管理
nMRI4L2i ?B0 ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英语
S^{"N+v0 ,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩 51Testing软件测试网*]5\ZeC#dX
FROM SC AS t
`8EAMqW)E.A)x8q0 GROUP BY S# 51Testing软件测试网h5\(\ z?:~ h
ORDER BY avg(t.score)
ej9{fu+o+[w018、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
0QOBU#B}w&c2A0 SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分
ZU.e6[!h X0 FROM SC L ,SC AS R
;k!V#M'UC7P }7|0 WHERE L.C# = R.C# and
/A7jJ$I+`"k)`0 L.score = (SELECT MAX(IL.score)
/lJ-H?P$k0 FROM SC AS IL,Student AS IM 51Testing软件测试网/JTA"zrN
WHERE L.C# = IL.C# and IM.S#=IL.S#
S B,B{/A^ i8?0 GROUP BY IL.C#)
tj&^#F*W%yz&Bub_0 AND 51Testing软件测试网M{7w~]1qDn5K
R.Score = (SELECT MIN(IR.score) 51Testing软件测试网d;h(ak(V7dNBg-A
FROM SC AS IR
*\E E8A!SP0 WHERE R.C# = IR.C#
8u,C$c\*vQ't7R0 GROUP BY IR.C# 51Testing软件测试网3I]fGm
); 51Testing软件测试网8~({~;l/gt'J/w
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
Y `#s7b$vm0 SELECT t.C# AS 课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0) AS 平均成绩 51Testing软件测试网']Oy,M+O9z*q1QY&Q
,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数
6N:pn3h:\${GWk0 FROM SC T,Course
.nMa6v!M C,X a0 where t.C#=course.C# 51Testing软件测试网yw@)Z B6]h
GROUP BY t.C# 51Testing软件测试网V Y|7x3X tY
ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC 51Testing软件测试网~#RR9k"BK"M6Fl
20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理(001),马克思(002),OO&UML (003),数据库(004) 51Testing软件测试网bA-[8Ly7Q3p]
SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分
HH}6rg4ey G0 ,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数
1Kz3]$K2nX8t%l0 ,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分
d} AK2K U;y6s;l |0 ,100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数
m AKL:@0F,[Fb0 ,SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分
:}H C O!G DL{$Mp0 ,100 * SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分数
a4c aaNUM0 ,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分 51Testing软件测试网%FaMlmSX
,100 * SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数 51Testing软件测试网 dzaXva@ Ib
FROM SC 51Testing软件测试网h`s3w6z$J;@)k
21、查询不同老师所教不同课程平均分从高到低显示
|*I;Kq NPh_F0 SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ID,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩 51Testing软件测试网[J3Q'R ^%lb&@7q T
FROM SC AS T,Course AS C ,Teacher AS Z 51Testing软件测试网4^*IW-Hd)iS
where T.C#=C.C# and C.T#=Z.T# 51Testing软件测试网Y1o\]Ozv0?G*j
GROUP BY C.C#
!S+z Jc v6B?R^no3}0 ORDER BY AVG(Score) DESC 51Testing软件测试网y+Eow K2w,\"Lb
22、查询如下课程成绩第 3 名到第 6 名的学生成绩单:企业管理(001),马克思(002),UML (003),数据库(004) 51Testing软件测试网'v5UK0ErGp
[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩 51Testing软件测试网z(C1Z8t5Jc1ju
SELECT DISTINCT top 3 51Testing软件测试网w6H#b#{ B Fy;_%Sz.Z
SC.S# As 学生学号, 51Testing软件测试网7Y&rL:CiX
Student.Sname AS 学生姓名 ,
8zL'\'b(X{-C4[6T!?g0 T1.score AS 企业管理, 51Testing软件测试网Ew*lwgC&l\
T2.score AS 马克思,
TC` n*jy/T2?!Z*[#U0 T3.score AS UML, 51Testing软件测试网"e%C Yu-nD
T4.score AS 数据库,
l+f*o|B#Qy0 ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 总分
D-rb Q:a0 FROM Student,SC LEFT JOIN SC AS T1
.~P8E6]xM|0 ON SC.S# = T1.S# AND T1.C# = '001'
i'E$Tx/K2\)Q0 LEFT JOIN SC AS T2 51Testing软件测试网A Dk j{ NI7xd
ON SC.S# = T2.S# AND T2.C# = '002' 51Testing软件测试网 i.v k#bh`
LEFT JOIN SC AS T3
%DU2AF)_N0 ON SC.S# = T3.S# AND T3.C# = '003' 51Testing软件测试网-k*W^9^5C zk.@
LEFT JOIN SC AS T4
)DH;m\r`"D!Pjk#{4F0 ON SC.S# = T4.S# AND T4.C# = '004' 51Testing软件测试网-G#K:EL [/z+m0it
WHERE student.S#=SC.S# and 51Testing软件测试网#m'izR/p9c qv
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
w3BI2@h4^)Osl0 NOT IN 51Testing软件测试网9Jv0h&Qc'N P9ju
(SELECT 51Testing软件测试网!njT5N&X`5p
DISTINCT
f"i }Lh{(nVzrl0 TOP 15 WITH TIES 51Testing软件测试网!MBH+X)Q%Cv
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) 51Testing软件测试网#xN!cS&]u {"n
FROM sc
-R/?Q\6w0 LEFT JOIN sc AS T1
6u7Hv|7c]J;^7R0 ON sc.S# = T1.S# AND T1.C# = 'k1'
O)mn U8E!Xv]0 LEFT JOIN sc AS T2
Course(C#,Cname,T#) 课程表
1?#Cs!H;`2u/e*s0SC(S#,C#,score) 成绩表
x&A!G2^f-E0Teacher(T#,Tname) 教师表
;UO _xs)H\k5[y0问题: 51Testing软件测试网l&ga5P e_5n
1、查询“001”课程比“002”课程成绩高的所有学生的学号;
$ws a/F7TQq0 select a.S# from (select s#,score from SC where C#='001') a,(select s#,score 51Testing软件测试网6|!f'rjW~#Qz
from SC where C#='002') b
'W{ a&U!o#V1\0 where a.score>b.score and a.s#=b.s#; 51Testing软件测试网J M'e$Z8h*m-A)nf
2、查询平均成绩大于60分的同学的学号和平均成绩; 51Testing软件测试网;z CJ;|c?})N
select S#,avg(score)
d W$icx0 from sc
7K+Np E a?s'T0 group by S# having avg(score) >60;
wRaF8jC_{03、查询所有同学的学号、姓名、选课数、总成绩; 51Testing软件测试网R4[{ b;P!MU
select Student.S#,Student.Sname,count(SC.C#),sum(score)
A+l@'fd2?2A0 from Student left Outer join SC on Student.S#=SC.S#
(og bC]1RxV.@7k0 group by Student.S#,Sname
.U9b\)Z,o7~ Q04、查询姓“李”的老师的个数;
2J0[Q7A&Cnn0 select count(distinct(Tname))
t&Roe+ykc,U-x0 from Teacher
8j \7]fhH3r~&j0 where Tname like '李%'; 51Testing软件测试网(e+J8]'{x
5、查询没学过“叶平”老师课的同学的学号、姓名; 51Testing软件测试网2VY*GjS.?
select Student.S#,Student.Sname
0J'm)p0D` `8_-q MK RUJ2f0 from Student 51Testing软件测试网'S_,yJ6Z%l S
where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平');
bO5]#Q ^5A7q06、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名; 51Testing软件测试网J#r/na$VVm\
select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002'); 51Testing软件测试网 V,M`bc
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名; 51Testing软件测试网 dN&r M8z(Z ow
select S#,Sname 51Testing软件测试网|4EN+gMp&^C
from Student 51Testing软件测试网d!f)ld)B7@.i
where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平' group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname='叶平')); 51Testing软件测试网"Tb#M7z:rV
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
n] z;pXsUD1S l0c0 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 51Testing软件测试网X LE kA+] S
from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2 <score;
iV6f ]-s09、查询所有课程成绩小于60分的同学的学号、姓名; 51Testing软件测试网0r6])UG,L N
select S#,Sname 51Testing软件测试网Ww~o l,Z!D
from Student 51Testing软件测试网7|1c$}_0G-D
where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60); 51Testing软件测试网9x9l [W RpP?z
10、查询没有学全所有课的同学的学号、姓名; 51Testing软件测试网(\7bzsB1nN3xf
select Student.S#,Student.Sname 51Testing软件测试网%p K!hR:? aT#]
from Student,SC 51Testing软件测试网X\!cB3b+Z9? aO{%`
where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);
j#T~Q0R(@8B011、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
:e!MF-DbN0i0 select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#='1001'; 51Testing软件测试网0e$^1|\TD,w fG
12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名; 51Testing软件测试网y9Ds`k(GO'V_f
select distinct SC.S#,Sname
%uK$~H7M/L ^ Hcoe0 from Student,SC
0yWpB5o+d0 where Student.S#=SC.S# and C# in (select C# from SC where S#='001'); 51Testing软件测试网I4qi:gj!ZJ bA#K
13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩; 51Testing软件测试网:h&C_G`8](G!M
update SC set score=(select avg(SC_2.score) 51Testing软件测试网5n;UD C3h
from SC SC_2
z0wD.X~@5a(W5?0 where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平'); 51Testing软件测试网ZMz+Og2P$s
14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;
6LW)H7E.FFS9J:Y0 select S# from SC where C# in (select C# from SC where S#='1002')
~fh0S5L0 group by S# having count(*)=(select count(*) from SC where S#='1002'); 51Testing软件测试网"p'r"~Tgv1AU _
15、删除学习“叶平”老师课的SC表记录; 51Testing软件测试网 Z.t#B$HS:q,~
Delect SC
&y q!A4M@.A h|0 from course ,Teacher 51Testing软件测试网HK:r.oBu
where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平';
{2R}K _P}2p016、向SC表中插入一些记录,这些记录要求符合以下条件:没有上过编号“003”课程的同学学号、2、
Zw;yT @0 号课的平均成绩; 51Testing软件测试网Nmr'u4r8J5wn:V
Insert SC select S#,'002',(Select avg(score) 51Testing软件测试网/dav+wZ Jl
from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002'); 51Testing软件测试网5D5O yh+c"k `+?
17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,按如下形式显示: 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分
.P H?8S1w fv0 SELECT S# as 学生ID
H%ke{6rKd0 ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 数据库
+gA1j0`;IQI XKe0 ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企业管理
nMRI4L2i ?B0 ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英语
S^{"N+v0 ,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩 51Testing软件测试网*]5\ZeC#dX
FROM SC AS t
`8EAMqW)E.A)x8q0 GROUP BY S# 51Testing软件测试网h5\(\ z?:~ h
ORDER BY avg(t.score)
ej9{fu+o+[w018、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
0QOBU#B}w&c2A0 SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分
ZU.e6[!h X0 FROM SC L ,SC AS R
;k!V#M'UC7P }7|0 WHERE L.C# = R.C# and
/A7jJ$I+`"k)`0 L.score = (SELECT MAX(IL.score)
/lJ-H?P$k0 FROM SC AS IL,Student AS IM 51Testing软件测试网/JTA"zrN
WHERE L.C# = IL.C# and IM.S#=IL.S#
S B,B{/A^ i8?0 GROUP BY IL.C#)
tj&^#F*W%yz&Bub_0 AND 51Testing软件测试网M{7w~]1qDn5K
R.Score = (SELECT MIN(IR.score) 51Testing软件测试网d;h(ak(V7dNBg-A
FROM SC AS IR
*\E E8A!SP0 WHERE R.C# = IR.C#
8u,C$c\*vQ't7R0 GROUP BY IR.C# 51Testing软件测试网3I]fGm
); 51Testing软件测试网8~({~;l/gt'J/w
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
Y `#s7b$vm0 SELECT t.C# AS 课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0) AS 平均成绩 51Testing软件测试网']Oy,M+O9z*q1QY&Q
,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数
6N:pn3h:\${GWk0 FROM SC T,Course
.nMa6v!M C,X a0 where t.C#=course.C# 51Testing软件测试网yw@)Z B6]h
GROUP BY t.C# 51Testing软件测试网V Y|7x3X tY
ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC 51Testing软件测试网~#RR9k"BK"M6Fl
20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理(001),马克思(002),OO&UML (003),数据库(004) 51Testing软件测试网bA-[8Ly7Q3p]
SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分
HH}6rg4ey G0 ,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数
1Kz3]$K2nX8t%l0 ,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分
d} AK2K U;y6s;l |0 ,100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数
m AKL:@0F,[Fb0 ,SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分
:}H C O!G DL{$Mp0 ,100 * SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分数
a4c aaNUM0 ,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分 51Testing软件测试网%FaMlmSX
,100 * SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数 51Testing软件测试网 dzaXva@ Ib
FROM SC 51Testing软件测试网h`s3w6z$J;@)k
21、查询不同老师所教不同课程平均分从高到低显示
|*I;Kq NPh_F0 SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ID,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩 51Testing软件测试网[J3Q'R ^%lb&@7q T
FROM SC AS T,Course AS C ,Teacher AS Z 51Testing软件测试网4^*IW-Hd)iS
where T.C#=C.C# and C.T#=Z.T# 51Testing软件测试网Y1o\]Ozv0?G*j
GROUP BY C.C#
!S+z Jc v6B?R^no3}0 ORDER BY AVG(Score) DESC 51Testing软件测试网y+Eow K2w,\"Lb
22、查询如下课程成绩第 3 名到第 6 名的学生成绩单:企业管理(001),马克思(002),UML (003),数据库(004) 51Testing软件测试网'v5UK0ErGp
[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩 51Testing软件测试网z(C1Z8t5Jc1ju
SELECT DISTINCT top 3 51Testing软件测试网w6H#b#{ B Fy;_%Sz.Z
SC.S# As 学生学号, 51Testing软件测试网7Y&rL:CiX
Student.Sname AS 学生姓名 ,
8zL'\'b(X{-C4[6T!?g0 T1.score AS 企业管理, 51Testing软件测试网Ew*lwgC&l\
T2.score AS 马克思,
TC` n*jy/T2?!Z*[#U0 T3.score AS UML, 51Testing软件测试网"e%C Yu-nD
T4.score AS 数据库,
l+f*o|B#Qy0 ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 总分
D-rb Q:a0 FROM Student,SC LEFT JOIN SC AS T1
.~P8E6]xM|0 ON SC.S# = T1.S# AND T1.C# = '001'
i'E$Tx/K2\)Q0 LEFT JOIN SC AS T2 51Testing软件测试网A Dk j{ NI7xd
ON SC.S# = T2.S# AND T2.C# = '002' 51Testing软件测试网 i.v k#bh`
LEFT JOIN SC AS T3
%DU2AF)_N0 ON SC.S# = T3.S# AND T3.C# = '003' 51Testing软件测试网-k*W^9^5C zk.@
LEFT JOIN SC AS T4
)DH;m\r`"D!Pjk#{4F0 ON SC.S# = T4.S# AND T4.C# = '004' 51Testing软件测试网-G#K:EL [/z+m0it
WHERE student.S#=SC.S# and 51Testing软件测试网#m'izR/p9c qv
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
w3BI2@h4^)Osl0 NOT IN 51Testing软件测试网9Jv0h&Qc'N P9ju
(SELECT 51Testing软件测试网!njT5N&X`5p
DISTINCT
f"i }Lh{(nVzrl0 TOP 15 WITH TIES 51Testing软件测试网!MBH+X)Q%Cv
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) 51Testing软件测试网#xN!cS&]u {"n
FROM sc
-R/?Q\6w0 LEFT JOIN sc AS T1
6u7Hv|7c]J;^7R0 ON sc.S# = T1.S# AND T1.C# = 'k1'
O)mn U8E!Xv]0 LEFT JOIN sc AS T2