sql高级查询50题.docx
- 文档编号:10399343
- 上传时间:2023-02-10
- 格式:DOCX
- 页数:16
- 大小:44.87KB
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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- sql 高级 查询 50