056
057
--9、查询所有课程成绩小于分的同学的学号、姓名;
058
selectS#,Sname
059
fromStudent
060
whereS#notin(selectStudent.S#fromStudent,SCwhereS.S#=SC.S#andscore>60);
061
062
--10、查询没有学全所有课的同学的学号、姓名;
063
selectStudent.S#,Student.Sname
064
fromStudent,SC
065
whereStudent.S#=SC.S#groupby Student.S#,Student.Snamehavingcount(C#)<(selectcount(C#)fromCourse);
066
067
--11、查询至少有一门课与学号为“”的同学所学相同的同学的学号和姓名;
068
selectS#,SnamefromStudent,SCwhereStudent.S#=SC.S#andC#inselectC#fromSCwhereS#='1001';
069
070
--12、查询至少学过学号为“”同学所有一门课的其他同学学号和姓名;
071
selectdistinctSC.S#,Sname
072
fromStudent,SC
073
whereStudent.S#=SC.S#andC#in(selectC#fromSCwhereS#='001');
074
075
--13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;
076
updateSCsetscore=(selectavg(SC_2.score)
077
fromSCSC_2
078
whereSC_2.C#=SC.C#)fromCourse,TeacherwhereCourse.C#=SC.C#andCourse.T#=Teacher.T#andTeacher.Tname='叶平');
079
080
--14、查询和“”号的同学学习的课程完全相同的其他同学学号和姓名;
081
selectS#fromSCwhereC#in(selectC#fromSCwhereS#='1002')
082
groupbyS#havingcount(*)=(selectcount(*)fromSCwhereS#='1002');
083
084
--15、删除学习“叶平”老师课的SC表记录;
085
DelectSC
086
fromcourse,Teacher
087
whereCourse.C#=SC.C#andCourse.T#=Teacher.T#andTname='叶平';
088
089
--16、向SC表中插入一些记录,这些记录要求符合以下条件:
没有上过编号“”课程的同学学号、、
090
号课的平均成绩;
091
InsertSCselectS#,'002',(Selectavg(score)
092
fromSCwhereC#='002')fromStudentwhereS#notin(SelectS#fromSCwhereC#='002');
093
094
--17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,按如下形式显示:
学生ID,,数据库,企业管理,英语,有效课程数,有效平均分
095
SELECTS#as学生ID
096
,(SELECTscoreFROMSCWHERESC.S#=t.S#ANDC#='004')AS数据库
097
,(SELECTscoreFROMSCWHERESC.S#=t.S#ANDC#='001')AS企业管理
098
,(SELECTscoreFROMSCWHERESC.S#=t.S#ANDC#='006')AS英语
099
,COUNT(*)AS有效课程数,AVG(t.score)AS平均成绩
100
FROMSCASt
101
GROUPBYS#
102
ORDERBYavg(t.score)
103
104
--18、查询各科成绩最高和最低的分:
以如下形式显示:
课程ID,最高分,最低分
105
SELECTL.C#As课程ID,L.scoreAS最高分,R.scoreAS最低分
106
FROMSCL,SCASR
107
WHEREL.C#=R.C#and
108
L.score=(SELECTMAX(IL.score)
109
FROMSCASIL,StudentASIM
110
WHEREL.C#=IL.C#andIM.S#=IL.S#
111
GROUPBYIL.C#)
112
AND
113
R.Score=(SELECTMIN(IR.score)
114
FROMSCASIR
115
WHERER.C#=IR.C#
116
GROUPBYIR.C#
117
);
118
119
--19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
120
SELECTt.C#AS课程号,max(course.Cname)AS课程名,isnull(AVG(score),0)AS平均成绩
121
,100*SUM(CASEWHEN isnull(score,0)>=60THEN1ELSE0END)/COUNT(*)AS及格百分数
122
FROMSCT,Course
123
wheret.C#=course.C#
124
GROUPBYt.C#
125
ORDERBY100*SUM(CASEWHEN isnull(score,0)>=60THEN1ELSE0END)/COUNT(*)DESC
126
127
--20、查询如下课程平均成绩和及格率的百分数(用"1行"显示):
企业管理(),马克思(),OO&UML(),数据库()
128
SELECTSUM(CASEWHENC#='001'THENscoreELSE0END)/SUM(CASEC#WHEN'001'THEN1ELSE0END)AS企业管理平均分
129
,100*SUM(CASEWHENC#='001'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENC#='001'THEN1ELSE0END)AS企业管理及格百分数
130
,SUM(CASEWHENC#='002'THENscoreELSE0END)/SUM(CASEC#WHEN'002'THEN1ELSE0END)AS马克思平均分
131
,100*SUM(CASEWHENC#='002'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENC#='002'THEN1ELSE0END)AS马克思及格百分数
132
,SUM(CASEWHENC#='003'THENscoreELSE0END)/SUM(CASEC#WHEN'003'THEN1ELSE0END)ASUML平均分
133
,100*SUM(CASEWHENC#='003'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENC#='003'THEN1ELSE0END)ASUML及格百分数
134
,SUM(CASEWHENC#='004'THENscoreELSE0END)/SUM(CASEC#WHEN'004'THEN1ELSE0END)AS数据库平均分
135
,100*SUM(CASEWHENC#='004'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENC#='004'THEN1ELSE0END)AS数据库及格百分数
136
FROMSC
137
138
--21、查询不同老师所教不同课程平均分从高到低显示
139
SELECTmax(Z.T#)AS教师ID,MAX(Z.Tname)AS教师姓名,C.C#AS课程ID,MAX(C.Cname)AS课程名称,AVG(Score)AS平均成绩
140
FROMSCAST,CourseASC,TeacherASZ
141
whereT.C#=C.C#andC.T#=Z.T#
142
GROUPBYC.C#
143
ORDERBYAVG(Score)DESC
144
145
--22、查询如下课程成绩第名到第名的学生成绩单:
企业管理(),马克思(),UML(),数据库()
146
[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩
147
SELECT DISTINCTtop3
148
SC.S#As学生学号,
149
Student.SnameAS学生姓名,
150
T1.scoreAS企业管理,
151
T2.scoreAS马克思,
152
T3.scoreASUML,
153
T4.scoreAS数据库,
154
ISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,0)as总分
155
FROMStudent,SC LEFTJOINSCAST1
156
ONSC.S#=T1.S#ANDT1.C#='001'
157
LEFTJOINSCAST2
158
ONSC.S#=T2.S#ANDT2.C#='002'
159
LEFTJOINSCAST3
160
ONSC.S#=T3.S#ANDT3.C#='003'
161
LEFTJOINSCAST4
162
ONSC.S#=T4.S#ANDT4.C#='004'
163
WHEREstudent.S#=SC.S#and
164
ISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,0)
165
NOTIN
166
(SELECT
167
DISTINCT
168
TOP15WITHTIES
169
ISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,0)
170
FROMsc
171
LEFTJOINscAST1
172
ONsc.S#=T1.S#ANDT1.C#='k1'
173
LEFTJOINscAST2
174
ONsc.S#=T2.S#ANDT2.C#='k2'
175
LEFTJOINscAST3
176
ONsc.S#=T3.S#ANDT3.C#='k3'
177
LEFTJOINscAST4
178
ONsc.S#=T4.S#ANDT4.C#='k4'
179
ORDERBYISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,0)DESC);
180
181
--23、统计列印各科成绩,各分数段人数:
课程ID,课程名称,[100-85],[85-70],[70-60],[<60]
182
SELECTSC.C#as课程ID,Cnameas课程名称
183
,SUM(CASEWHENscoreBETWEEN85AND100THEN1ELSE0END)AS[100-85]
184
,SUM(CASEWHENscoreBETWEEN70AND85THEN1ELSE0END)AS[85-