SQL SERVER查询经典 40题createdatabase test3 use test3 -1.学生表 createtable student( s# varchar( 10), sname varchar( 10), sage datetime, ssex varchar( 10 ) ); insertinto student values( 01, 赵雷 , 1990-01-01, 男 ) insertinto student values( 02, 钱电 , 1990-12-21, 男 ) insertinto student values( 03, 孙风 , 1990-05-20, 男 ) insertinto student values( 04, 李云 , 1990-08-06, 女 ) insertinto student values( 05, 周梅 , 1991-12-01, 女 ) insertinto student values( 06, 吴兰 , 1992-03-01, 女 ) insertinto student values( 07, 郑竹 , 1989-07-01, 女 ) insertinto student values( 08, 王菊 , 1990-01-20, 女 ) createtable course( c# varchar( 10), cname varchar( 10), t# varchar( 10) ); insertinto course values( 01, 语文 , 02) insertinto course values( 02, 数学 , 01) insertinto course values( 03, 英语 , 03) createtable teacher( t# varchar( 10), tname varchar( 10), ); insertinto teacher values( 01, 张兰 ) insertinto teacher values( 02, 李四 ) insertinto teacher values( 03, 王五 ) createtable SC ( S# varchar( 10), C# varchar( 10), score decimal( 18, 1) ) insertinto sc values( 01,01, 80 ) insertinto sc values( 01,02, 90 ) insertinto sc values( 01,03, 99 ) insertinto sc values( 02,01, 70 ) insertinto sc values( 02,02, 60 ) insertinto sc values( 02,03, 80 ) insertinto sc values( 03,01, 80 ) insertinto sc values( 03,02, 80 ) insertinto sc values( 03,03, 80 ) insertinto sc values( 04,01, 50 ) insertinto sc values( 04,02, 30 ) insertinto sc values( 04,03, 20 ) insertinto sc values( 05,01, 76 ) insertinto sc values( 05,02, 87 ) insertinto sc values( 06,01, 31 ) insertinto sc values( 06,03, 34 ) insertinto sc values( 07,02, 89 ) insertinto sc values( 07,03, 98 ) go -1 、查询 “01“ 课程比 “02“ 课程成绩高的学生的信息及课程分数-1.1、查询同时存在 “01“ 课程和 “02“ 课程的情况 select st. s#, st . sname , b. score , c. score from student st innerjoin sc b on st. s#=b. s# and b . c# =01 innerjoin sc c on st. s#=c. s# and c . c# =02 where b . score c. score -1.2、 查询同时存在 “01“ 课程和“02“ 课程的情况和存在 “01“ 课程但 - 可能不存在 “02“ 课程的情况 ( 不存在时显示为 null)(以下存在相同 内容时不再解释 ) select st. s#, st . sname , b. score , c. score from student st leftjoin sc b on st. s#=b. s# and b . c# =01 leftjoin sc c on st. s#=c. s# and c . c# =02 where b . score isnull( c. score , 0) - ISNULL(check_expression,replacement_value) -2 、查询 “01“ 课程比 “02“ 课程成绩低的学生的信息及课程分数 -2.1、查询同时存在 “01“ 课程和 “02“ 课程的情况 select st. s#, st . sname , b. score , c. score from student st innerjoin sc b on st. s#=b. s# and b . c# =01 innerjoin sc c on st. s#=c. s# and c . c# =02 where b . score 60 orderby 平均成绩 desc -4 、查询平均成绩小于分的同学的学生编号和学生姓名和平均成绩 -4.1、查询在 sc 表存在成绩的学生信息的SQL语句。 -4.2、查询在 sc 表中不存在成绩的学生信息的SQL语句 select st. s#, st . sname , isnull( cast ( avg ( sc . score )as decimal( 18 , 2),0)平均成绩 from student st innerjoin sc on st. s#=sc . s# groupby st. s# , st . sname havingisnull( cast ( avg ( sc . score )as decimal( 18 , 2),0) 01 -13 、查询和 “01“ 号的同学学习的课程完全相同的其他同学的信息 select st.*from Student st whereS# in( selectdistinctSC. S# fromSC wheresc . S# =90 select c . c# 课程ID , c. cname 课程name, max( sc . score )最高 分, min ( sc . score )最低分 , cast ( avg ( sc . score)asdecimal( 18 , 2)平均分 , cast ( selectcount ( sc . s# ) fromsc wheresc . c# =c. c# and score =60)* 100.0 /( selectcount ( sc . s# )from sc where sc . c# =c. c# )asdecimal( 18 , 2) 及格率(%) , cast ( selectcount ( sc . s# ) fromsc wheresc . c# =c. c# and sc . score =70 and sc . score =80 and sc . score =90)* 100.0 /( selectcount ( sc . s# )from sc where sc . c#=c. c# )asdecimal( 18, 2) 优秀率 (%) from course c innerjoin sc on c . c# =sc . c# groupby c . c# , c. cname -24 、查询学生平均成绩及其名次 selectt .*, px = rank ()over ( orderby 平均成绩 desc ) from ( select m . S# 学生编号 ,m. Sname 学生姓名 , isnull( cast ( avg ( score )asdecimal( 18, 2),0) 平均成绩 from Student m leftjoin SC n on m . S# = n . S# groupby m. S# , m. Sname ) t orderby px select t .*, px = DENSE_RANK ()over ( orderby 平均成 绩 desc )from ( select m . S# 学生编号 ,m. Sname 学生姓名 , isnull( cast ( avg ( score )asdecimal( 18, 2),0) 平均成绩 from Student m leftjoin SC n on m . S# = n . S# groupby m. S# , m. Sname ) t orderby px -25 、查询各科成绩前三名的记录 -25.1 分数重复时保留名次空缺 select st. s#, st . sname , sc1 . c# , sc1 . score fromstudent st , sc sc1 wherest . s# =sc1 . s# and sc1 . score in ( selecttop 3 score from sc sc2 where sc2 . c# =sc1 . c# orderby sc2 . score desc ) orderby sc1 . c# , sc1 . score desc -25.2 分数重复时不保留名次空缺，合并名次 select*from( select t .*, px = DENSE_RANK ()over ( partitionby c# orderbyscore desc )from sc t) m where px between 1 and 3 orderby m. C# , m. px -26 、查询每门课程被选修的学生数 select c . cname, count ( sc . s# ) from course c innerjoin sc on c . c# =sc . c#