50个经典SQL查询语句Word文档下载推荐.docx
《50个经典SQL查询语句Word文档下载推荐.docx》由会员分享,可在线阅读,更多相关《50个经典SQL查询语句Word文档下载推荐.docx(16页珍藏版)》请在冰豆网上搜索。
HavingAvg(Score)>
60
--3、查询所有同学的学号、姓名、选课数、总成绩;
SelectStuId,StuName,
SelCourses=(SelectCount(CourseId)FromtblScoret1Wheret1.StuId=s1.StuId),
SumScore=(SelectSum(Score)FromtblScoret2Wheret2.StuId=s1.StuId)
FromtblStudents1
--4、查询姓“李”的老师的个数;
SelectCount(*)FromtblTeacherWhereTeaNamelike'
李%'
--5、查询没学过“叶平”老师课的同学的学号、姓名;
SelectStuId,StuNameFromtblStudent
WhereStuIdNotIn
(
SelectStuIDFromtblScoresc
InnerJointblCoursecuONsc.CourseId=cu.CourseId
InnerJointblTeachertcONcu.TeaId=tc.TeaId
Wheretc.TeaName='
叶平'
)
--6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
SelectStuId,StuNameFromtblStudentst
Where(SelectCount(*)FromtblScores1Wheres1.StuId=st.StuIdAnds1.CourseId='
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分的同学的学号、姓名;
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'
------嵌套子查询
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='
马克思=(SelectScoreFromtblScorescInnerJointblCoursecsOnsc.CourseId=cs.CourseIdWhereCourseName='
马克思'
UML=(SelectScoreFromtblScorescInnerJointblCoursecsOnsc.CourseId=cs.CourseIdWhereCourseName='
UML'
数据库=(SelectScoreFromtblScorescInnerJointblCoursecsOnsc.CourseId=cs.CourseIdWhereCourseName='
平均成绩=(SelectAvg(Score)FromtblScorescInnerJointblCoursecsOnsc.CourseId=cs.CourseIdWhere(CourseName='
orCourseName='
排名=Row_Number()Over(Orderby(SelectAvg(Score)FromtblScorescInnerJointblCoursecsOnsc.CourseId=cs.CourseIdWhere(CourseName='
)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<
--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、查询