sql高级查询50题.docx
《sql高级查询50题.docx》由会员分享，可在线阅读，更多相关《sql高级查询50题.docx（16页珍藏版）》请在冰点文库上搜索。
sql高级查询50题
sql高级查询50题
SQL高级查询——50句查询（含答案）
--一个题目涉及到的50个Sql语句
--（下面表的结构以给出,自己在数据库中建立表.并且添加相应的数据,数据要全面些.其中Student表中,SId为学生的ID）
------------------------------------表结构--------------------------------------
--学生表tblStudent（编号StuId、姓名StuName、年龄StuAge、性别StuSex）
--课程表tblCourse（课程编号CourseId、课程名称CourseName、教师编号TeaId）
--成绩表tblScore（学生编号StuId、课程编号CourseId、成绩Score）
--教师表tblTeacher（教师编号TeaId、姓名TeaName）
---------------------------------------------------------------------------------
--问题：
--1、查询“001”课程比“002”课程成绩高的所有学生的学号；
SelectStuIdFromtblStudents1
Where（SelectScoreFromtblScoret1Wheret1.StuId=s1.stuIdAndt1.CourseId='001'）>
（SelectScoreFromtblScoret2Wheret2.StuId=s1.stuIdAndt2.CourseId='002'）
--2、查询平均成绩大于60分的同学的学号和平均成绩；
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='002'）>0
--7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
SelectStuId,StuNameFromtblStudentstWherenotexists
（
SelectCourseIDFromtblCoursecuInnerJointblTeachertcOncu.TeaID=tc.TeaID
Wheretc.TeaName='叶平' AndCourseIDnotin
（SelectCourseIDFromtblScoreWhereStuID=st.StuID）
）
--8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
SelectStuId,StuNameFromtblStudents1
Where（SelectScoreFromtblScoret1Wheret1.StuId=s1.stuIdAndt1.CourseId='001'）>
（SelectScoreFromtblScoret2Wheret2.StuId=s1.stuIdAndt2.CourseId='002'）
--9、查询所有课程成绩小于60分的同学的学号、姓名；
SelectStuId,StuNameFromtblStudentst
WhereStuIdNotIN
（SelectStuIdFromtblScorescWherest.StuId=sc.StuIdAndScore>60）
--10、查询没有学全所有课的同学的学号、姓名；
SelectStuId,StuNameFromtblStudentst
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='1001'）
）
--12、查询至少学过学号为“1001”同学所有课程的其他同学学号和姓名；
SelectStuId,StuNameFromtblStudent
WhereStuIdIn
（
SelectDistinctStuIdFromtblScoreWhereCourseIdNotIn（SelectCourseIdFromtblScoreWhereStuId='1001'）
--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='1002'））
And
NotExists（Select*FromtblScoreWhereStuId='1002'AndCourseIdNotIn（SelectCourseIdFromtblScorescWheresc.StuId=st.StuId））
--15、删除学习“叶平”老师课的SC表记录；
DeleteFromtblScoreWhereCourseIdIN
（SelectCourseIdFromtblCoursecsINNERJOINtblTeachertcONcs.TeaId=tc.TeaIdWheretc.TeaName='叶平'）
--16、向SC表中插入一些记录，这些记录要求符合以下条件：
没有上过编号“003”课程的同学学号、'002'号课的平均成绩；
InsertIntotblScore（StuId,CourseId,Score）
SelectStuId,'002',（SelectAvg（Score）FromtblScoreWhereCourseId='002'）FromtblScoreWhere
StuIdNotIn（SelectStuIdFromtblScoreWhereCourseId='003'）
--17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示：
学生ID,,数据库,企业管理,英语,有效课程数,有效平均分
SelectStuId
,数据库=（SelectScoreFromtblScorescInnerJointblCoursecsOnsc.CourseId=cs.CourseIdWhereCourseName='数据库'Andsc.StuID=st.StuId）
,企业管理=（SelectScoreFromtblScorescInnerJointblCoursecsOnsc.CourseId=cs.CourseIdWhereCourseName='企业管理'Andsc.StuID=st.StuId）
,英语=（SelectScoreFromtblScorescInnerJointblCoursecsOnsc.CourseId=cs.CourseIdWhereCourseName='英语'Andsc.StuID=st.StuId）
,有效课程数=（SelectCount（Score）FromtblScorescInnerJointblCoursecsOnsc.CourseId=cs.CourseIdWhere（CourseName='数据库'orCourseName='企业管理'orCourseName='英语'）Andsc.StuID=st.StuId）
,有效平均分=（SelectAvg（Score）FromtblScorescInnerJointblCoursecsOnsc.CourseId=cs.CourseIdWhere（CourseName='数据库'orCourseName='企业管理'orCourseName='英语'）Andsc.StuID=st.StuId）
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）
FromtblCoursecs
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='企业管理'Andsc.StuID=st.StuId）
,马克思=（SelectScoreFromtblScorescInnerJointblCoursecsOnsc.CourseId=cs.CourseIdWhereCourseName='马克思'Andsc.StuID=st.StuId）
,UML=（SelectScoreFromtblScorescInnerJointblCoursecsOnsc.CourseId=cs.CourseIdWhereCourseName='UML'Andsc.StuID=st.StuId）
,数据库=（SelectScoreFromtblScorescInnerJointblCoursecsOnsc.CourseId=cs.CourseIdWhereCourseName='数据库'Andsc.StuID=st.StuId）
,平均成绩=（SelectAvg（Score）FromtblScorescInnerJointblCoursecsOnsc.CourseId=cs.CourseIdWhere（CourseName='数据库'orCourseName='企业管理'orCourseName='UML'orCourseName='马克思'）Andsc.StuID=st.StuId）
,排名=Row_Number（）Over（Orderby（SelectAvg（Score）FromtblScorescInnerJointblCoursecsOnsc.CourseId=cs.CourseIdWhere（CourseName='数据库'orCourseName='企业管理'orCourseName='UML'orCourseName='马克思'）Andsc.StuID=st.StuId）DESC）
FromtblStudentst
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<60）
FromtblCoursecs
--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）
FromtblCoursecs
--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）
FromtblCoursecs
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
升级会员 