1、Select StuId,Avg(Score) as AvgScore From tblScoreGroup By StuIdHaving Avg(Score)60-3、查询所有同学的学号、姓名、选课数、总成绩;Select StuId,StuName,SelCourses=(Select Count(CourseId) From tblScore t1 Where t1.StuId=s1.StuId),SumScore=(Select Sum(Score) From tblScore t2 Where t2.StuId=s1.StuId)From tblStudent s1And(Selec
2、t Count(*) From tblScore s2 Where s2.StuId=st.StuId And s2.CourseId=-7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;Select StuId,StuName From tblStudent st Where not exists(Select CourseID From tblCourse cu Inner Join tblTeacher tc On cu.TeaID=tc.TeaIDWhere tc.TeaName=叶平And CourseID not in(Select CourseID From tblScor
3、e Where StuID=st.StuID)-8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;Select StuId,StuName From tblStudent s1-9、查询所有课程成绩小于60分的同学的学号、姓名;Select StuId,StuName From tblStudent stWhere StuId Not IN(Select StuId From tblScore sc Where st.StuId=sc.StuId And Score60)-10、查询没有学全所有课的同学的学号、姓名;Where (Select Count(*)
4、From tblScore sc Where st.StuId=sc.StuId)(Select Count(*) From tblCourse)-11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;-运用连接查询Select DistInct st.StuId,StuName From tblStudent stInner Join tblScore sc ON st.StuId=sc.StuIdWhere sc.CourseId IN (Select CourseId From tblScore Where StuId=1001-嵌套子查询Select StuId,
5、StuName From tblStudentWhere StuId InSelect Distinct StuId From tblScore Where CourseId In (Select CourseId From tblScore Where StuId=-12、查询至少学过学号为“1001”同学所有课程的其他同学学号和姓名;Select Distinct StuId From tblScore Where CourseId Not In (Select CourseId From tblScore Where StuId=-13、把“SC”表中“叶平”老师教的课的成绩都更改为此课
6、程的平均成绩; (从子查询中获取父查询中的表名,这样也行?-创建测试表Select * Into Sc From tblScoregoUpdate Sc Set Score=(Select Avg(Score) From tblScore s1 Where s1.CourseId=sc.CourseId)Where CourseId IN(Select CourseId From tblCourse cs INNER JOIN tblTeacher tc ON cs.TeaID=tc.TeaID WHERE TeaName =-14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和
7、姓名; Select StuID,StuName From tblStudent stWhere StuId 1002AndNot Exists(Select * From tblScore sc Where sc.StuId=st.StuId And CourseId Not In (Select CourseId From tblScore Where StuId=)Not Exists(Select * From tblScore Where StuId= And CourseId Not In (Select CourseId From tblScore sc Where sc.Stu
8、Id=st.StuId)-15、删除学习“叶平”老师课的SC表记录;Delete From tblScore Where CourseId IN(Select CourseId From tblCourse cs INNER JOIN tblTeacher tc ON cs.TeaId=tc.TeaId Where tc.TeaName=-16、向SC表中插入一些记录,这些记录要求符合以下条件:没有上过编号“003”课程的同学学号、号课的平均成绩;Insert Into tblScore (StuId,CourseId,Score)Select StuId,(Select Avg(Score)
9、 From tblScore Where CourseId=) From tblScore WhereStuId Not In (Select StuId From tblScore Where CourseId=003-17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,按如下形式显示: 学生ID,数据库,企业管理,英语,有效课程数,有效平均分Select StuId,数据库=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where Cou
10、rseName=数据库 And sc.StuID=st.StuId),企业管理=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName=企业管理,英语=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName=英语,有效课程数=(Select Count(Score) From tblScore sc Inner J
11、oin tblCourse cs On sc.CourseId=cs.CourseId Where (CourseName= or CourseName=) And sc.StuID=st.StuId),有效平均分=(Select Avg(Score) From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where (CourseName=From tblStudent stOrder by 有效平均分 Desc-18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分Select Cours
12、eId as 课程ID, 最高分=(Select Max(Score) From tblScore sc Where sc.CourseId=cs.CourseId ),最低分=(Select Min(Score) From tblScore sc Where sc.CourseId=cs.CourseId )From tblCourse cs-19、按各科平均成绩从低到高和及格率的百分数从高到低顺序 (百分数后如何格式化为两位小数?Select 课程ID,平均分,及格率 From(Select CourseId as 课程ID, 平均分=(Select Avg(Score) From tbl
13、Score sc Where sc.CourseId=cs.CourseId ),及格率=Convert(varchar(10),(Select Count(*) From tblScore sc Where sc.CourseId=cs.CourseId And sc.Score=60)*10000/(Select Count(*) From tblScore sc Where sc.CourseId=cs.CourseId)/100)+%From tblScore cs) as tmpGroup by 课程ID,平均分,及格率Order by 平均分, Convert(float,subs
14、tring(及格率,1,len(及格率)-1) Desc-20、查询如下课程平均成绩和及格率的百分数(用1行显示): 企业管理(001),马克思(002),OO&UML (003),数据库(004)Select 课程ID=sc.CourseId,课程名称=cs.CourseName,平均成绩=Avg(Score),及格率 =Convert(varchar(10),(Select Count(Score) From tblScore Where CourseId=sc.CourseId And Score=60)*10000/Count(Score)/100.0)+From tblScore s
15、cInner Join tblCourse cs ON sc.CourseId=cs.CourseIdWhere sc.CourseId like 001234Group By sc.CourseId,cs.CourseName-21、查询不同老师所教不同课程平均分从高到低显示Select 课程ID=CourseId,课程名称=CourseName,授课教师=TeaName,平均成绩=(Select Avg(Score) From tblScore Where CourseId=cs.CourseId)Inner Join tblTeacher tc ON cs.TeaId=tc.TeaIdO
16、rder by 平均成绩 Desc-22、查询如下课程成绩第 3 名到第 6 名的学生成绩单:企业管理(001),马克思(002),UML (003),数据库(004) 格式:学生ID,学生姓名,企业管理,马克思,UML,数据库,平均成绩Select * FromSelect Top 6 学生ID=StuId,学生姓名=StuName,马克思=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName=马克思,UML=(Select Score From t
17、blScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName=UML,平均成绩=(Select Avg(Score) From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where (CourseName=or CourseName=,排名=Row_Number() Over(Order by(Select Avg(Score) From tblScore sc Inner Join tblCourse cs On
18、sc.CourseId=cs.CourseId Where (CourseName=) And sc.StuID=st.StuId) DESC)Order by 排名) as tmpWhere 排名 between 3 And 6-23、统计列印各科成绩,各分数段人数:课程ID,课程名称,100-85,85-70,70-60, 60Select 课程ID=CourseId, 课程名称=CourseName,100-85=(Select Count(*) From tblScore sc Where CourseId=cs.CourseId And Score between 85 And 10
19、0),85-70=(Select Count(*) From tblScore sc Where CourseId=cs.CourseId And Score between 70 And 84),70-60=(Select Count(*) From tblScore sc Where CourseId=cs.CourseId And Score between 60 And 69),60=(Select Count(*) From tblScore sc Where CourseId=cs.CourseId And Score =2-31、1981年出生的学生名单(注:Student表中S
20、age列的类型是datetime)Select * From tblStudent Where Year(Sage)=1981-32、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列Select 课程ID=CourseId,课程名称=CourseName,平均成绩=(Select Avg(Score) From tblScore Where CourseId=cs.CourseId)Order by 平均成绩,CourseId Desc-33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩Select 学号=StuId,姓名=StuName,平均成绩=(Select Avg(Score) From tblScore Where StuId=st.StuId) From tblStudent stWhere (Select Avg(Score) From tblScore Where StuId=st.StuId)85-34、查询课程名称为“数据库”,且分数低于60的学生姓名和分数Select 姓名=StuName,分数=Score From tblScore scInne
copyright@ 2008-2023 冰点文库 网站版权所有
经营许可证编号:鄂ICP备19020893号-2