sql高级查询50题Word文档下载推荐.docx
《sql高级查询50题Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《sql高级查询50题Word文档下载推荐.docx（16页珍藏版）》请在冰点文库上搜索。
SelectStuId,Avg（Score）asAvgScoreFromtblScore
GroupByStuId
HavingAvg（Score）>
60
--3、查询所有同学的学号、姓名、选课数、总成绩；
SelectStuId,StuName,
SelCourses=（SelectCount（CourseId）FromtblScoret1Wheret1.StuId=s1.StuId）,
SumScore=（SelectSum（Score）FromtblScoret2Wheret2.StuId=s1.StuId）
FromtblStudents1
And
（SelectCount（*）FromtblScores2Wheres2.StuId=st.StuIdAnds2.CourseId='
--7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
SelectStuId,StuNameFromtblStudentstWherenotexists
（
SelectCourseIDFromtblCoursecuInnerJointblTeachertcOncu.TeaID=tc.TeaID
Wheretc.TeaName='
叶平'
AndCourseIDnotin
（SelectCourseIDFromtblScoreWhereStuID=st.StuID）
--8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
SelectStuId,StuNameFromtblStudents1
--9、查询所有课程成绩小于60分的同学的学号、姓名；
SelectStuId,StuNameFromtblStudentst
WhereStuIdNotIN
（SelectStuIdFromtblScorescWherest.StuId=sc.StuIdAndScore>
60）
--10、查询没有学全所有课的同学的学号、姓名；
Where（SelectCount（*）FromtblScorescWherest.StuId=sc.StuId）<
（SelectCount（*）FromtblCourse）
--11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；
------运用连接查询
SelectDistInctst.StuId,StuNameFromtblStudentst
InnerJointblScorescONst.StuId=sc.StuId
Wheresc.CourseIdIN（SelectCourseIdFromtblScoreWhereStuId='
1001'
------嵌套子查询
SelectStuId,StuNameFromtblStudent
WhereStuIdIn
SelectDistinctStuIdFromtblScoreWhereCourseIdIn（SelectCourseIdFromtblScoreWhereStuId='
--12、查询至少学过学号为“1001”同学所有课程的其他同学学号和姓名；
SelectDistinctStuIdFromtblScoreWhereCourseIdNotIn（SelectCourseIdFromtblScoreWhereStuId='
--13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；
（从子查询中获取父查询中的表名，这样也行？
？
--创建测试表
Select*IntoScFromtblScore
go
UpdateScSetScore=（SelectAvg（Score）FromtblScores1Wheres1.CourseId=sc.CourseId）
WhereCourseIdIN
（SelectCourseIdFromtblCoursecsINNERJOINtblTeachertcONcs.TeaID=tc.TeaIDWHERETeaName='
--14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；
SelectStuID,StuNameFromtblStudentst
WhereStuId<
>
'
1002'
And
NotExists（Select*FromtblScorescWheresc.StuId=st.StuIdAndCourseIdNotIn（SelectCourseIdFromtblScoreWhereStuId='
））
NotExists（Select*FromtblScoreWhereStuId='
AndCourseIdNotIn（SelectCourseIdFromtblScorescWheresc.StuId=st.StuId））
--15、删除学习“叶平”老师课的SC表记录；
DeleteFromtblScoreWhereCourseIdIN
（SelectCourseIdFromtblCoursecsINNERJOINtblTeachertcONcs.TeaId=tc.TeaIdWheretc.TeaName='
--16、向SC表中插入一些记录，这些记录要求符合以下条件：
没有上过编号“003”课程的同学学号、'
号课的平均成绩；
InsertIntotblScore（StuId,CourseId,Score）
SelectStuId,'
（SelectAvg（Score）FromtblScoreWhereCourseId='
）FromtblScoreWhere
StuIdNotIn（SelectStuIdFromtblScoreWhereCourseId='
003'
--17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示：
学生ID,,数据库,企业管理,英语,有效课程数,有效平均分
SelectStuId
数据库=（SelectScoreFromtblScorescInnerJointblCoursecsOnsc.CourseId=cs.CourseIdWhereCourseName='
数据库'
Andsc.StuID=st.StuId）
企业管理=（SelectScoreFromtblScorescInnerJointblCoursecsOnsc.CourseId=cs.CourseIdWhereCourseName='
企业管理'
英语=（SelectScoreFromtblScorescInnerJointblCoursecsOnsc.CourseId=cs.CourseIdWhereCourseName='
英语'
有效课程数=（SelectCount（Score）FromtblScorescInnerJointblCoursecsOnsc.CourseId=cs.CourseIdWhere（CourseName='
orCourseName='
）Andsc.StuID=st.StuId）
有效平均分=（SelectAvg（Score）FromtblScorescInnerJointblCoursecsOnsc.CourseId=cs.CourseIdWhere（CourseName='
FromtblStudentst
Orderby有效平均分Desc
--18、查询各科成绩最高和最低的分：
以如下形式显示：
课程ID，最高分，最低分
SelectCourseIdas课程ID,最高分=（SelectMax（Score）FromtblScorescWheresc.CourseId=cs.CourseId）,
最低分=（SelectMin（Score）FromtblScorescWheresc.CourseId=cs.CourseId）
FromtblCoursecs
--19、按各科平均成绩从低到高和及格率的百分数从高到低顺序（百分数后如何格式化为两位小数?
?
Select课程ID,平均分,及格率From
（SelectCourseIdas课程ID,平均分=（SelectAvg（Score）FromtblScorescWheresc.CourseId=cs.CourseId）,
及格率=Convert（varchar（10）,（（SelectCount（*）FromtblScorescWheresc.CourseId=cs.CourseIdAndsc.Score>
=60）*10000/（SelectCount（*）FromtblScorescWheresc.CourseId=cs.CourseId））/100）+'
%'
FromtblScorecs）astmp
Groupby课程ID,平均分,及格率
Orderby平均分,Convert（float,substring（及格率,1,len（及格率）-1））Desc
--20、查询如下课程平均成绩和及格率的百分数（用"
1行"
显示）:
企业管理（001），马克思（002），OO&
UML（003），数据库（004）
Select课程ID=sc.CourseId,课程名称=cs.CourseName,平均成绩=Avg（Score）
及格率=Convert（varchar（10）,（（SelectCount（Score）FromtblScoreWhereCourseId=sc.CourseIdAndScore>
=60）*10000/Count（Score））/100.0）+'
FromtblScoresc
InnerJointblCoursecsONsc.CourseId=cs.CourseId
Wheresc.CourseIdlike'
00[1234]'
GroupBysc.CourseId,cs.CourseName
--21、查询不同老师所教不同课程平均分从高到低显示
Select课程ID=CourseId,课程名称=CourseName,授课教师=TeaName,平均成绩=（SelectAvg（Score）FromtblScoreWhereCourseId=cs.CourseId）
InnerJointblTeachertcONcs.TeaId=tc.TeaId
Orderby平均成绩Desc
--22、查询如下课程成绩第3名到第6名的学生成绩单：
企业管理（001），马克思（002），UML（003），数据库（004）格式：
[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩
Select*From
SelectTop6学生ID=StuId,学生姓名=StuName
马克思=（SelectScoreFromtblScorescInnerJointblCoursecsOnsc.CourseId=cs.CourseIdWhereCourseName='
马克思'
UML=（SelectScoreFromtblScorescInnerJointblCoursecsOnsc.CourseId=cs.CourseIdWhereCourseName='
UML'
平均成绩=（SelectAvg（Score）FromtblScorescInnerJointblCoursecsOnsc.CourseId=cs.CourseIdWhere（CourseName='
orCourseName='
排名=Row_Number（）Over（Orderby（SelectAvg（Score）FromtblScorescInnerJointblCoursecsOnsc.CourseId=cs.CourseIdWhere（CourseName='
）Andsc.StuID=st.StuId）DESC）
Orderby排名
）astmp
Where排名between3And6
--23、统计列印各科成绩,各分数段人数:
课程ID,课程名称,[100-85],[85-70],[70-60],[<
60]
Select课程ID=CourseId,课程名称=CourseName
[100-85]=（SelectCount（*）FromtblScorescWhereCourseId=cs.CourseIdAndScorebetween85And100）
[85-70]=（SelectCount（*）FromtblScorescWhereCourseId=cs.CourseIdAndScorebetween70And84）
[70-60]=（SelectCount（*）FromtblScorescWhereCourseId=cs.CourseIdAndScorebetween60And69）
[<
60]=（SelectCount（*）FromtblScorescWhereCourseId=cs.CourseIdAndScore<
--24、查询学生平均成绩及其名次
Select学号=st.StuId,姓名=StuName,平均成绩=sc.AvgScore,名次=（Dense_Rank（）Over（Orderbysc.AvgScoreDesc））FromtblStudentst
InnerJoin（SelectStuId,Avg（Score）asAvgScoreFromtblScoreGroupbyStuId）asscOnsc.StuId=st.StuId
Orderby学号
--25、查询各科成绩前三名的记录:
（不考虑成绩并列情况）
Select学号=StuId,课程号=CourseId,分数=Score
From
（SelectRow_Number（）Over（orderbyCourseId,ScoreDesc）asi,*FromtblScore）astmp
--得到一个临时的排名表,其中i表示编号
WhereiIn
SelectTop3iFrom（SelectRow_Number（）Over（orderbyCourseId,ScoreDesc）asi,*FromtblScore）ast1Wheret1.CourseId=tmp.CourseId
--26、查询每门课程被选修的学生数
Select课程ID=CourseId,选修人数=（SelectCount（*）From（SelectDistinctStuIdFromtblScoreWhereCourseId=cs.CourseId）astmp）
--27、查询出只选修了一门课程的全部学生的学号和姓名
Select学号=StuId,姓名=StuName
FromtblStudentst
Where（SelectCount（*）From（SelectDistinctCourseIdFromtblScoreWhereStuId=st.StuId）astmp）=1
--28、查询男生、女生人数
Select男生人数=（selectCount（*）FromtblStudentWhereStuSex='
男'
）,
女生人数=（selectCount（*）FromtblStudentWhereStuSex='
女'
--29、查询姓“张”的学生名单
Select*FromtblStudentWhereStuNamelike'
张%'
--30、查询同名同性学生名单，并统计同名人数
SelectDistinct学生姓名=StuName,同名人数=（SelectCount（*）FromtblStudents2Wheres2.StuName=st.StuName）FromtblStudentst
Where（SelectCount（*）FromtblStudents2Wheres2.StuName=st.StuName）>
=2
--31、1981年出生的学生名单（注：
Student表中Sage列的类型是datetime）
Select*FromtblStudentWhereYear（Sage）=1981
--32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
Select课程ID=CourseId,课程名称=CourseName,平均成绩=（SelectAvg（Score）FromtblScoreWhereCourseId=cs.CourseId）
Orderby平均成绩,CourseIdDesc
--33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
Select学号=StuId,姓名=StuName,平均成绩=（SelectAvg（Score）FromtblScoreWhereStuId=st.StuId）FromtblStudentst
Where（SelectAvg（Score）FromtblScoreWhereStuId=st.StuId）>
85
--34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数
Select姓名=StuName,分数=ScoreFromtblScoresc
Inne
升级会员 