【写在前面~~】
【PS1：建议SQL初学者一定要自己先做一遍题目，这样才有效果~~（做题时为验证查询结果是否正确，可更改表中数据）】
【PS2：文末最后一条代码整合了全部51道题目及答案~~】
【PS3：题目是网上所找，文中代码为自己练习编写，若有错误，请尽情指出~~】
一、创建数据库
1 create database 测试1 on primary 2 (name='测试1.mdf', 3 filename='E:\学习\SQL\实例目录\DATA\测试1.mdf', 4 SIZE=5MB, 5 MAXSIZE=UNLIMITED, 6 FILEGROWTH=1KB) 7 LOG ON 8 (NAME='测试1.ldf', 9 filename='E:\学习\SQL\实例目录\DATA\测试1.ldf', 10 size=520kb, 11 maxsize=unlimited, 12 filegrowth=20kb)
二、创建表
| 列名 | 类型 | 是否为空值 | 键约束 |
| sid (学生编号) | int | 否 | 主键 |
| sname (学生姓名) | varchar(10) | ||
| sage( 出生年月) | datetime | ||
| ssex (性别) | varchar(10) |
| 列名 | 类型 | 是否为空值 | 键约束 |
| tid (教师编号) | int | 否 | 主键 |
| tname (教师姓名) | varchar(10) |
| 列名 | 类型 | 是否为空值 | 键约束 |
| cid (课程编号) | int | 否 | 主键 |
| cname (课程名) | varchar(10) | ||
| tid (教师编号) | int | 外键 |
| 列名 | 类型 | 是否为空值 | 键约束 |
| sid (学生编号) | int | 外键 | |
| cid (课程编号) | int | 外键 | |
| score (分数) | decimal(5,2) |
1 use 测试1 2 go 3 create table student 4 (sid varchar(10) not null constraint pk_sid primary key, 5 sname varchar(10), 6 sage datetime, 7 ssex varchar(10) constraint ck_ssex check (ssex in ('男','女')))--设置检查约束，性别一列只能输入男或女 8 9 create table teacher 10 (tid varchar(10) not null constraint pk_tid primary key , 11 tname varchar(10)) 12 13 create table course 14 (cid varchar(10) not null constraint pk_cid primary key, 15 cname varchar(10), 16 tid varchar(10) constraint fk_tid foreign key (tid) references teacher (tid))--tid 作为外键，引用teacher表中的主键tid 17 18 create table sc 19 (sid varchar(10) constraint fk_sid foreign key (sid) references student(sid), 20 cid varchar(10) constraint fk_cid foreign key(cid) references course(cid), 21 score decimal(5,2))
三、向表中插入数据
1 insert into student values('01' , '赵雷' , '1990-01-01' , '男') 2 insert into student values('02' , '钱电' , '1990-12-21' , '男') 3 insert into student values('03' , '孙风' , '1990-06-20' , '男') 4 insert into student values('04' , '李云' , '1990-08-06' , '男') 5 insert into student values('05' , '周梅' , '1991-12-01' , '女') 6 insert into student values('06' , '吴兰' , '1992-03-01' , '女') 7 insert into student values('07' , '郑竹' , '1989-06-04' , '女') 8 insert into student values('08' , '王菊' , '1990-05-28' , '女') 9 insert into student values('09' , '风王' , '1989-05-29' , '男') 10 insert into student values('10' , '司徒风风' , '1991-06-05' , '女') 11 insert into student values('11' , '桂易' , '1999-06-21' , '男') 12 insert into student values('12' , '司徒末' , '1991-07-30' , '女') 13 insert into student values('13' , '风王' , '1995-05-30' , '男') 14 insert into student values('14' , '顾唯一' , '1998-06-02' , '男') 15 insert into student values('15' , '圆圆' , '2000-06-06' , '女') 16 insert into student values('16' , '司徒叮当' , '1997-06-03' , '女') 17 insert into student values('17' , '顾未易' , '1999-06-12' , '男') 18 insert into student values('18' , '顾未易' , '1999-06-08' , '男') 19 20 insert into teacher values('01' , '张三') 21 insert into teacher values('02' , '李四') 22 insert into teacher values('03' , '王五') 23 24 insert into course values('01' , '语文' , '02') 25 insert into course values('02' , '数学' , '01') 26 insert into course values('03' , '英语' , '03') 27 insert into course values('04' , '政治' , '01') 28 insert into course values('05' , '物理' , '02') 29 30 insert into SC values('01' , '01' , 80) 31 insert into SC values('01' , '02' , 90) 32 insert into SC values('01' , '03' , 99) 33 insert into SC values('02' , '01' , 80) 34 insert into SC values('02' , '02' , 60) 35 insert into SC values('02' , '03' , 80) 36 insert into SC values('03' , '01' , 80) 37 insert into SC values('03' , '02' , 80) 38 insert into SC values('03' , '03' , 80) 39 insert into SC values('04' , '01' , 80) 40 insert into SC values('04' , '02' , 30) 41 insert into SC values('04' , '03' , 90) 42 insert into SC values('05' , '01' , 76) 43 insert into SC values('05' , '02' , 87) 44 insert into SC values('06' , '01' , 31) 45 insert into SC values('06' , '03' , 34) 46 insert into SC values('07' , '02' , 90) 47 insert into SC values('07' , '03' , 98) 48 insert into SC values('07' , '04' , 90) 49 insert into SC values('07' , '01' , 50) 50 insert into SC values('07' , '05' , 60) 51 52 select student.*,course.*,teacher.tname,sc.score into newtable 53 from student left join sc on student.sid=sc.sid left join course on course.cid= sc.cid left join teacher on teacher.tid=course.tid --将所有信息合在一起，为了后面查看校验查询信息是否正确 54 --若插入数据时重复执行了（若没有设置主键约束情况下）,导致在删除数据时无法删除,提示已更新或删除行值要么不能使该行成为唯一行,要么...,则可用临时表来实现删除重复数据 55 select 各列字段名或全部列用*表示 into #临时表名 from 原表名 group by 字段列表 56 truncate table 原表名 57 insert into 原表名 select*from #临时表名 58 drop table #临时表名
四、51道题目及答案
--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
1 --第一种方法 2 select student.*,m.score [01课程成绩], n.score [02课程成绩]from student 3 join (select sid,score from sc where cid='01') m on student.sid=m.sid 4 join (select sid,score from sc where cid='02')n on student.sid=n.sid 5 where m.score>n.score 6 --第二种方法 7 select student.*,m.[01课程成绩],m.[02课程成绩] from student, 8 (select sid,sum(case when cid='01' then score else null end) [01课程成绩] ,--若else后面是0,则结果会输出有01课程无02课程的学生,若改为null则不会输出只有一门课程的学生成绩 9 sum(case when cid='02' then score else null end) [02课程成绩]from sc group by sid) m 10 where student.sid=m.sid and m.[01课程成绩]>m.[02课程成绩]
--2、查询同时存在"01"课程和"02"课程的情况
1 select student.*,m.score [01课程成绩],n.score [02课程成绩] from student 2 join (select sid ,score from sc where cid='01') m on student.sid=m.sid 3 join (select sid,score from sc where cid='02') n on student.sid=n.sid
--3、查询存在"01"课程但可能不存在"02"课程的情况(不存在时显示为null)
1 select student.*,m.score,n.score from (select sid,score from sc where cid='01') m left join student on student.sid=m.sid 2 left join (select sid,score from sc where cid='02')n on student.sid=n.sid
--4、 查询不存在" 01 "课程但存在" 02 "课程的情况
1 select student.*,score from student join sc on student.sid=sc.sid where cid='02' and student.sid not in (select sid from sc where cid='01')
--5、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
1 select student.sid,sname,cast (avg(score) as decimal(5,2))平均成绩 from student join sc on student.sid=sc.sid 2 group by student.sid,sname having avg(score)>=60
--6、查询在sc表存在成绩的学生信息的SQL语句
1 --第一种 2 select distinct student.* from student join sc on student.sid=sc.sid 3 --第二种 4 select * from student where sid in (select sc.sid from sc )--IN()适合B表(括号内的表是B)比A表数据小的情况 5 --第三种 6 select * from student where exists (select sc.sid from sc where student.sid = sc.sid)--EXISTS()适合B表比A表数据大的情况
--7、查询在sc表中不存在成绩的学生信息的SQL语句
1 select * from student where sid not in (select sc.sid from sc )
--8、查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和(没成绩的显示为 null )
1 select student.sid,sname,count(cid) 选课总数 ,sum(score ) 所有课程成绩的总和 from student 2 left join sc on student.sid=sc.sid group by student.sid,sname
--9、查询「李」姓老师的数量
1 select count (tid) 李姓老师的数量 from teacher where tname like '李%'
--10、查询学过「张三」老师授课的同学的信息
1 select student.* from student join sc on student.sid=sc.sid 2 join course on course.cid=sc.cid 3 join teacher on teacher.tid=course.tid where tname='张三'
--11、查询没学过「张三」老师授课的同学的信息
1 select *from student where sid not in (select sc.sid from sc join course on course.cid=sc.cid 2 join teacher on teacher.tid=course.tid where tname='张三')
--12、查询没有学全所有课程的同学的信息
1 --第一种直接 2 select student.* from student left join sc on student.sid=sc.sid group by student.sid,sname,sage,ssex 3 having count(sc.cid)<(select count(course.cid) from course) 4 ----第二种反向 5 select student.* from student where student.sid not in 6 (select sc.sid from sc group by sid having count(sc.cid)= (select count(cid) from course))
--13、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
1 select distinct student.* from student join sc on student.sid=sc.sid 2 where cid in (select cid from sc where sid='01') and student.sid!='01' --in的意思是:等于其中的一个则输出该条相等数据，直至全部输出为止
--14、查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
--(即所学课程数要一致,学的课程目录也要一致)
1 select student.* from student join 2 (select sid from sc group by sid having count(cid)=(select count(cid) from sc where sid='01' )) m--控制大范围下学生所学课程数=01同学的课程数3个,例如:学4门课程的同学,其中3门与01同学相同,也满足后续程序,但是与01同学就不是完全相同的. 3 on student.sid=m.sid join sc on student.sid=sc.sid 4 where sc.cid in (select cid from sc where sid='01') and student.sid!='01'group by student.sid,sname,sage,ssex 5 having count(sc.cid)=(select count(sc.cid) from sc where sid='01')--控制与01同学所学课程目录相同的数也是3个
--15、查询没学过"张三"老师讲授的任一门课程的学生姓名
1 select * from student where student.sid not in 2 (select sc.sid from sc join course on sc.cid=course.cid 3 join teacher on teacher.tid=course.tid where tname='张三' )
--16、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
1 --第一种 2 select student.sid,sname,cast(avg(score) as decimal(5,2)) avgscore from student join sc 3 on student.sid=sc.sid where sc.score<60 group by student.sid,sname having count(sc.cid)>=2 4 --第二种 5 select student.sid,sname,cast(avg(score) as decimal(5,2)) avgscore from student join sc 6 on student.sid=sc.sid group by student.sid,sname having sum(case when score<60 then 1 else 0 end)>=2
--17、检索"01"课程分数小于60，按分数降序排列的学生信息
1 select student.* from student join sc on student.sid=sc.sid where cid='01' and score<60 order by score desc
--18、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
1 select student.sid,sname, sc.cid,sc.score,m.avgscore from student left join sc on student.sid=sc.sid 2 left join (select sid,cast(avg(score) as decimal(5,2)) avgscore from sc group by sid ) m on sc.sid=m.sid 3 order by m.avgscore desc
--19、查询各科成绩最高分、最低分和平均分：
--以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--（及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90）
1 select sc.cid,course.cname,max(sc.score) 最高分,min(sc.score) 最低分,cast(avg(sc.score) as decimal(5,2)) 平均分 , 2 count(sc.cid) 计数, 3 sum(case when sc.score>=60 then 1 else 0 end )及格数, sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end ) 中等数, 4 sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end ) 优良数,sum(case when sc.score>=90 then 1 else 0 end ) 优秀数,--这里的计数,及格数...优秀数是为了验证后续的及格率...优秀率是否正确的,完全按照题意需省去 5 convert(varchar(10),cast((100*sum(case when score>=60 then 1 else 0 end) /cast(count(sc.cid) as float)) as decimal(5,2)))+'%' 及格率,--分母count(sc.sid)要转化为浮点型数据，否则分子是整数，结果也为整数，舍小数位或者把分子转化为浮点型数据，结果也会为浮点型 6 convert(varchar(10),cast((100*sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end) /cast(count(sc.cid) as float)) as decimal(5,2)))+'%' 中等率, 7 convert(varchar(10),cast((100*sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end) /cast(count(sc.cid) as float)) as decimal(5,2)))+'%' 优良率, 8 convert(varchar(10),cast((100*sum(case when sc.score>=90 then 1 else 0 end) /cast(count(sc.cid) as float)) as decimal(5,2)))+'%' 优秀率 9 from course join sc on course.cid=sc.cid group by sc.cid,course.cname
--20、按各科成绩进行排序，并显示排名,score 重复时保留名次空缺
1 --第一种 2 select a.cid,a.sid,a.score,count(b.score) +1 排名 from sc a 3 left join sc b on a.cid=b.cid and a.score<b.score group by a.cid,a.sid,a.score order by a.cid asc,排名 asc 4 ---select a.cid,a.sid,a.score,b.score from sc a left join sc b on a.cid=b.cid and a.score<b.score group by a.cid,a.sid,a.score,b.score 在课程相同的情况下，计算比自己分数大的有几个再加上1即为排名 5 --例如课程3,学号1,99；2,80；3,80；4,90；6,34;7,98 6 --则对应的比1,99大的为null排名1;比2,80大的为99,90,98排名4;比3,80大的99,90,98排名3;比4,90大的为99,98排名3；比6,34大的为99,90,98,80,80排名6；比7,98大的为99排名2 7 8 --第二种(使用内置函数rank) 9 select cid,sid,score,rank() over (partition by cid order by score desc ) 排名 from sc --partition by 分组 order by 排序
--21、按各科成绩进行排序，并显示排名,score 重复时合并名次空缺
1 --第一种 2 select a.cid,a.sid,a.score,count(distinct b.score) +1 排名 from sc a 3 left join sc b on a.cid=b.cid and a.score<b.score group by a.cid,a.sid,a.score order by a.cid asc,排名 asc 4 --第二种(使用内置函数dense_rank) 5 select cid,sid,score,dense_rank() over (partition by cid order by score desc ) 排名 from sc
--22、查询学生的总成绩，并进行排名，总分重复时合并名次空缺
1 --第一种 2 select a.sid,a.allscore 总成绩,count(distinct b.allscore)+1 排名 from ( select sid,sum(score) allscore from sc group by sid) a 3 left join ( select sid,sum(score) allscore from sc group by sid) b on a.allscore<b.allscore group by a.sid,a.allscore order by 排名 asc 4 --a表与b表中已经进行完全相同的学生sid分组，在左连接时不应再让sid相等连接，否则左连接时b.allscore >a.allscore匹配为空值，每个学生的排名都为1，即在学号sid相等情况，不能再比较不同学生间的成绩排名,只能自我比较 5 --而20/21题的第一种方法有cid左连接相等即在同一课程下，还可以比较不同学生sid间的成绩排名 6 --第二种 7 select sid,sum(score) 总成绩 ,dense_rank()over(order by sum(score) desc ) 排名 from sc group by sid 8 --这里的成绩不是所有同学sid的成绩，有的同学没有成绩，因此我们可以将student与上述两种方法进行左连接--例如： 9 select a.sid ,a.allscore 总成绩,count(distinct b.allscore)+1 排名 from 10 (select student.sid ,isnull(sum(score),0) allscore from student left join sc on student.sid=sc.sid group by student.sid ) a left join 11 (select student.sid ,isnull(sum(score),0) allscore from student left join sc on student.sid=sc.sid group by student.sid ) b on a.allscore<b.allscore 12 group by a.sid,a.allscore order by 排名 asc 13 14 select student.sid,isnull(sum(score),0) 总成绩,dense_rank()over(order by isnull(sum(score),0) desc )排名 15 from student left join sc on student.sid=sc.sid group by student.sid
--23、查询不同老师所教不同课程平均分从高到低显示
1 select course.tid,tname,course.cid,cast(avg(score) as decimal(5,2)) 课程平均分 from course left join sc on sc.cid=course.cid 2 left join teacher on teacher.tid=course.tid group by course.tid ,tname,course.cid order by avg(score) desc
--24、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
1 --第一种 2 select student.*,m.cid,m.成绩,m.排名 from student left join 3 ( select a.cid,a.sid,a.score 成绩,count(b.score)+1 排名 from sc a left join sc b on a.cid=b.cid and a.score<b.score 4 group by a.cid,a.sid,a.score ) m on m.sid=student.sid where m.排名 between 2 and 3 order by m.cid asc,排名 asc--重复排名时保留名次空缺,把count 内改成distinct b.score 即为重复时合并空缺 5 --第二种 6 select student.*,m.cid,m.成绩,m.排名 from student left join 7 (select cid,sid,score 成绩,rank()over(partition by cid order by score desc) 排名 from sc group by cid,sid,score) m 8 on student.sid=m.sid where m.排名 between 2 and 3 order by cid asc--rank 改为dense_rank 即为合并名次空缺
--25、查询各科成绩前三名的记录
1 --第一种 2 select a.cid,a.sid,a.score from sc a left join sc b on a.cid=b.cid and a.score<b.score 3 group by a.cid,a.sid,a.score having count(b.score)<3 order by a.cid asc ,a.score desc --成绩重复时保留名次空缺，输出排名为1-3的学生;也可以按照24题来做,排名改成between 1 and 3即可 4 --第二种 5 select cid,sid,score from sc a where sid in ( 6 select top 3 b.sid from sc b where a.cid=b.cid order by b.cid asc ,b.score desc ) 7 order by cid asc,score desc --这种top 型的只输出前3个学生，对于成绩重复的同一名次会缺失 8 --第三种 9 select * from 10 (select *,row_number()over(partition by cid order by score desc) 排名 from sc) B 11 where B.排名<4--同样只输出前3个学生，对于成绩重复的同一名次会缺失
--26、查询各科成绩最高的记录
1 select sc.cid, sc.sid,m.maxscore from sc join (select cid,max(score) maxscore from sc group by cid ) m 2 on m.maxscore=sc.score and m.cid=sc.cid 3 --其余方法可参直接参考25题方法
--27、查询选修一门以上且不包括最高成绩学生的其余学生成绩
1 --第一种 2 select sid,cid,score from sc where sid not in (select sc.sid from sc join (select cid,max(score) maxscore from sc group by cid ) m 3 on m.maxscore=sc.score and m.cid=sc.cid ) and sid in (select sid from sc group by sid having count (sid)>1) 4 --第二种 5 select sid,cid,score from sc where sid not in 6 ( select a.sid from sc a left join sc b on a.cid=b.cid and a.score<b.score group by a.cid,a.sid,a.score having count(b.score)=0) 7 and sid in (select sid from sc group by sid having count (sid)>1) 8 --若改成-选修一门以上不是最高成绩的其余成绩(还包括最高成绩的学生的其他科目成绩)--每科成绩非最高分的记录 9 select cid,sid,score from sc a where sid not in ( 10 select top 3 b.sid from sc b where a.cid=b.cid order by b.cid asc ,b.score desc ) 11 and sid in (select sid from sc group by sid having count (sid)>1)
--28、查询每门课程被选修的学生数
1 select cid,count(sid) 选修人数 from sc group by cid
--29、查询出只选修两门课程的学生学号和姓名
1 select student.sid 学号,sname 姓名 from student join sc on student.sid=sc.sid group by student.sid,sname having count(student.sid)=2
--30、查询男生、女生人数
1 select ssex, count (ssex) 人数 from student group by ssex
--31、查询名字中含有"风"字的学生信息
1 select * from student where sname like '风%' or sname like '%风'
--32、查询同名同性学生名单，并统计同名人数
1 select a.sname,a.ssex,count(a.sname) 同名人数 from student a,student b 2 where a.sid!=b.sid and a.sname=b.sname and a.ssex=b.ssex group by a.sname,a.ssex
--33、查询1990年出生的学生名单
1 select * from student where datename(yy,sage)='1990'
--34、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
1 select cid,cast(avg(score) as decimal(5,2)) avgscore from sc group by cid order by avgscore desc,cid asc
--35、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
1 select student.sid,sname,avg(score) from student 2 join sc on student.sid =sc.sid group by student.sid,sname having avg(score)>=85
--36、查询课程名称为"数学"，且分数低于60的学生姓名和分数
1 select student.sname,score from student join sc on student.sid=sc.sid 2 join course on course.cid=sc.cid where cname='数学' and score <60
--37、查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
1 select student.*,cid,score from student left join sc on student.sid=sc.sid
--38、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
1 select student.sid,course.cname,sc.score from student join sc on student.sid=sc.sid join course on course.cid=sc.cid where score>70
--39、查询存在不及格的课程
1 --第一种 2 select distinct cid from sc where score<60--distinct确定唯一性 3 --第二种 4 select cid from sc where score<60 group by cid--group by 确定唯一性
--40、查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名
1 select student.sid,student.sname from student join sc on student.sid=sc.sid where cid='01' and score>=80
--41、求每门课程的学生人数
--要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
1 select cid,count(sid) 每门课程人数 from sc group by cid order by 每门课程人数 desc,cid asc
--42、成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
1 select top 1 student.*,m.maxscore from student join ( 2 select sid,max(score) maxscore from teacher join course on course.tid=teacher.tid join sc on sc.cid=course.cid 3 where tname='张三' group by sid ) m on student.sid=m.sid order by m. maxscore desc 4 5 --若改成：每个教师所教每门课程的最高成绩 6 select m.tname,m.cid,sid,m.maxscore from sc join ( 7 select tname,sc.cid,max(score) maxscore from teacher join course on course.tid=teacher.tid join sc on sc.cid=course.cid 8 group by tname,sc.cid) m on sc.cid=m.cid and sc.score=m.maxscore order by m.cid asc
--43、成绩重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
1 select student.*,m.score from student join ( 2 select a.cid,a.sid,a.score from sc a left join sc b on a.cid=b.cid and a.score<b.score group by a.cid,a.sid,a.score having count(b.score)=0) m 3 on student.sid=m.sid join course on course.cid=m.cid join teacher on teacher.tid=course.tid where tname='张三'--m表内容也可以改成用rank函数做成排名，取排名第一即可
--44、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
1 select a.sid,a.cid,a.score from sc a join sc b on a.sid=b.sid where a.cid!=b.cid and a.score=b.score group by a.cid,a.sid,a.score
--45、查询选修了全部课程的学生信息
1 select student.* from student join 2 (select sid from sc group by sid having count(sc.cid)= (select count(cid) from course)) m 3 on student.sid=m.sid
--46、查询各学生的年龄,只按照年份来算
1 select student.*,datediff(yy,sage,getdate()) age from student
--47、查询各学生的年龄,按照出生日期来算，当前月日 < 出生年月的月日则年龄减一
1 --第一种 2 select *, 3 (case when convert(int,convert(varchar(10),getdate(),112))<convert (int,convert(varchar(10),year(getdate()))+substring(convert(varchar(10),sage,112),5,4)) 4 then datediff(yy,sage,getdate())-1 else datediff(yy,sage,getdate()) end )age--出生年月变成现在的年与月日 同现在的时间对比大小 5 from student 6 --第二种 7 select *,(case when month(getdate())<month(sage) then datediff(yy,sage,getdate())-1 8 when month(getdate())=month(sage) and day(getdate())<day(sage) then datediff(yy,sage,getdate())-1 9 else datediff(yy,sage,getdate()) end) age from student 10 --第三种 11 select * , (case when right(convert(varchar(10),getdate(),120),5) < right(convert(varchar(10),sage,120),5) 12 then datediff(yy ,sage,getdate()) - 1 else datediff(yy ,sage, getdate()) end) age from student
--48、查询本周过生日的学生
1 --第一种 2 select *,(case when datename(wk,convert(datetime,(convert(varchar(10),year(getdate()))+substring(convert(varchar(10),sage,112),5,4))))= 3 datename(wk,getdate()) then 1 else 0 end) 生日提醒 from student 4 --第二种 5 select * from student where datediff(wk,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = 0
49、查询下周过生日的学生
1 --第一种 2 select *,(case when datename(wk,convert(datetime,(convert(varchar(10),year(getdate()))+substring(convert(varchar(10),sage,112),5,4))))= 3 datename(wk,getdate())+1 then 1 else 0 end) 生日提醒 from student 4 --第二种 5 select * from student where datediff(wk,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = -1
--50、查询本月过生日的学生
1 --第一种 2 select * from student where month(getdate())=month(sage) 3 --第二种 4 select * from student where datediff(mm,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = 0
--51、查询下月过生日的学生
1 --第一种 2 select * from student where month(getdate())+1=month(sage) 3 --第二种 4 select * from student where datediff(mm,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = -1
【这是一条整合了全部51道题目及答案的代码】
1 --1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数 2 --第一种方法 3 select student.*,m.score [01课程成绩], n.score [02课程成绩]from student 4 join (select sid,score from sc where cid='01') m on student.sid=m.sid 5 join (select sid,score from sc where cid='02')n on student.sid=n.sid 6 where m.score>n.score 7 --第二种方法 8 select student.*,m.[01课程成绩],m.[02课程成绩] from student, 9 (select sid,sum(case when cid='01' then score else null end) [01课程成绩] ,--若else后面是0,则结果会输出有01课程无02课程的学生,若改为null则不会输出只有一门课程的学生成绩 10 sum(case when cid='02' then score else null end) [02课程成绩]from sc group by sid) m 11 where student.sid=m.sid and m.[01课程成绩]>m.[02课程成绩] 12 13 --2、查询同时存在"01"课程和"02"课程的情况 14 15 select student.*,m.score [01课程成绩],n.score [02课程成绩] from student 16 join (select sid ,score from sc where cid='01') m on student.sid=m.sid 17 join (select sid,score from sc where cid='02') n on student.sid=n.sid 18 19 --3、查询存在"01"课程但可能不存在"02"课程的情况(不存在时显示为null) 20 select student.*,m.score,n.score from (select sid,score from sc where cid='01') m left join student on student.sid=m.sid 21 left join (select sid,score from sc where cid='02')n on student.sid=n.sid 22 23 --4、 查询不存在" 01 "课程但存在" 02 "课程的情况 24 select student.*,score from student join sc on student.sid=sc.sid where cid='02' and student.sid not in (select sid from sc where cid='01') 25 --5、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩 26 select student.sid,sname,cast (avg(score) as decimal(5,2))平均成绩 from student join sc on student.sid=sc.sid 27 group by student.sid,sname having avg(score)>=60 28 --6、查询在sc表存在成绩的学生信息的SQL语句 29 --第一种 30 select distinct student.* from student join sc on student.sid=sc.sid 31 --第二种 32 select * from student where sid in (select sc.sid from sc )--IN()适合B表(括号内的表是B)比A表数据小的情况 33 --第三种 34 select * from student where exists (select sc.sid from sc where student.sid = sc.sid)--EXISTS()适合B表比A表数据大的情况 35 --7、查询在sc表中不存在成绩的学生信息的SQL语句 36 select * from student where sid not in (select sc.sid from sc ) 37 --8、查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和(没成绩的显示为 null ) 38 select student.sid,sname,count(cid) 选课总数 ,sum(score ) 所有课程成绩的总和 from student 39 left join sc on student.sid=sc.sid group by student.sid,sname 40 --9、查询「李」姓老师的数量 41 select count (tid) 李姓老师的数量 from teacher where tname like '李%' 42 --10、查询学过「张三」老师授课的同学的信息 43 select student.* from student join sc on student.sid=sc.sid 44 join course on course.cid=sc.cid 45 join teacher on teacher.tid=course.tid where tname='张三' 46 47 --11、查询没学过「张三」老师授课的同学的信息 48 select *from student where sid not in (select sc.sid from sc join course on course.cid=sc.cid 49 join teacher on teacher.tid=course.tid where tname='张三') 50 51 --12、查询没有学全所有课程的同学的信息 52 --第一种直接 53 select student.* from student left join sc on student.sid=sc.sid group by student.sid,sname,sage,ssex 54 having count(sc.cid)<(select count(course.cid) from course) 55 ----第二种反向 56 select student.* from student where student.sid not in 57 (select sc.sid from sc group by sid having count(sc.cid)= (select count(cid) from course)) 58 --13、查询至少有一门课与学号为"01"的同学所学相同的同学的信息 59 select distinct student.* from student join sc on student.sid=sc.sid 60 where cid in (select cid from sc where sid='01') and student.sid!='01' --in的意思是:等于其中的一个则输出该条相等数据，直至全部输出为止 61 --14、查询和" 01 "号的同学学习的课程完全相同的其他同学的信息 62 --(即所学课程数要一致,学的课程目录也要一致) 63 select student.* from student join 64 (select sid from sc group by sid having count(cid)=(select count(cid) from sc where sid='01' )) m--控制大范围下学生所学课程数=01同学的课程数3个,例如:学4门课程的同学,其中3门与01同学相同,也满足后续程序,但是与01同学就不是完全相同的. 65 on student.sid=m.sid join sc on student.sid=sc.sid 66 where sc.cid in (select cid from sc where sid='01') and student.sid!='01'group by student.sid,sname,sage,ssex 67 having count(sc.cid)=(select count(sc.cid) from sc where sid='01')--控制与01同学所学课程目录相同的数也是3个 68 --15、查询没学过"张三"老师讲授的任一门课程的学生姓名 69 select * from student where student.sid not in 70 (select sc.sid from sc join course on sc.cid=course.cid 71 join teacher on teacher.tid=course.tid where tname='张三' ) 72 --16、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 73 --第一种 74 select student.sid,sname,cast(avg(score) as decimal(5,2)) avgscore from student join sc 75 on student.sid=sc.sid where sc.score<60 group by student.sid,sname having count(sc.cid)>=2 76 --第二种 77 select student.sid,sname,cast(avg(score) as decimal(5,2)) avgscore from student join sc 78 on student.sid=sc.sid group by student.sid,sname having sum(case when score<60 then 1 else 0 end)>=2 79 --17、检索"01"课程分数小于60，按分数降序排列的学生信息 80 select student.* from student join sc on student.sid=sc.sid where cid='01' and score<60 order by score desc 81 --18、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 82 select student.sid,sname, sc.cid,sc.score,m.avgscore from student left join sc on student.sid=sc.sid 83 left join (select sid,cast(avg(score) as decimal(5,2)) avgscore from sc group by sid ) m on sc.sid=m.sid 84 order by m.avgscore desc 85 --19、查询各科成绩最高分、最低分和平均分： 86 --以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 87 --（及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90） 88 select sc.cid,course.cname,max(sc.score) 最高分,min(sc.score) 最低分,cast(avg(sc.score) as decimal(5,2)) 平均分 , 89 count(sc.cid) 计数, 90 sum(case when sc.score>=60 then 1 else 0 end )及格数, sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end ) 中等数, 91 sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end ) 优良数,sum(case when sc.score>=90 then 1 else 0 end ) 优秀数,--这里的计数,及格数...优秀数是为了验证后续的及格率...优秀率是否正确的,完全按照题意需省去 92 convert(varchar(10),cast((100*sum(case when score>=60 then 1 else 0 end) /cast(count(sc.cid) as float)) as decimal(5,2)))+'%' 及格率,--分母count(sc.sid)要转化为浮点型数据，否则分子是整数，结果也为整数，舍小数位或者把分子转化为浮点型数据，结果也会为浮点型 93 convert(varchar(10),cast((100*sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end) /cast(count(sc.cid) as float)) as decimal(5,2)))+'%' 中等率, 94 convert(varchar(10),cast((100*sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end) /cast(count(sc.cid) as float)) as decimal(5,2)))+'%' 优良率, 95 convert(varchar(10),cast((100*sum(case when sc.score>=90 then 1 else 0 end) /cast(count(sc.cid) as float)) as decimal(5,2)))+'%' 优秀率 96 from course join sc on course.cid=sc.cid group by sc.cid,course.cname 97 98 --20、按各科成绩进行排序，并显示排名,score 重复时保留名次空缺 99 --第一种 100 select a.cid,a.sid,a.score,count(b.score) +1 排名 from sc a 101 left join sc b on a.cid=b.cid and a.score<b.score group by a.cid,a.sid,a.score order by a.cid asc,排名 asc 102 ---select a.cid,a.sid,a.score,b.score from sc a left join sc b on a.cid=b.cid and a.score<b.score group by a.cid,a.sid,a.score,b.score 在课程相同的情况下，计算比自己分数大的有几个再加上1即为排名 103 --例如课程3,学号1,99；2,80；3,80；4,90；6,34;7,98 104 --则对应的比1,99大的为null排名1;比2,80大的为99,90,98排名4;比3,80大的99,90,98排名3;比4,90大的为99,98排名3；比6,34大的为99,90,98,80,80排名6；比7,98大的为99排名2 105 106 --第二种(使用内置函数rank) 107 select cid,sid,score,rank() over (partition by cid order by score desc ) 排名 from sc --partition by 分组 order by 排序 108 --21、按各科成绩进行排序，并显示排名,score 重复时合并名次空缺 109 --第一种 110 select a.cid,a.sid,a.score,count(distinct b.score) +1 排名 from sc a 111 left join sc b on a.cid=b.cid and a.score<b.score group by a.cid,a.sid,a.score order by a.cid asc,排名 asc 112 --第二种(使用内置函数dense_rank) 113 select cid,sid,score,dense_rank() over (partition by cid order by score desc ) 排名 from sc 114 115 --22、查询学生的总成绩，并进行排名，总分重复时合并名次空缺 116 --第一种 117 select a.sid,a.allscore 总成绩,count(distinct b.allscore)+1 排名 from ( select sid,sum(score) allscore from sc group by sid) a 118 left join ( select sid,sum(score) allscore from sc group by sid) b on a.allscore<b.allscore group by a.sid,a.allscore order by 排名 asc 119 --a表与b表中已经进行完全相同的学生sid分组，在左连接时不应再让sid相等连接，否则左连接时b.allscore >a.allscore匹配为空值，每个学生的排名都为1，即在学号sid相等情况，不能再比较不同学生间的成绩排名,只能自我比较 120 --而20/21题的第一种方法有cid左连接相等即在同一课程下，还可以比较不同学生sid间的成绩排名 121 --第二种 122 select sid,sum(score) 总成绩 ,dense_rank()over(order by sum(score) desc ) 排名 from sc group by sid 123 --这里的成绩不是所有同学sid的成绩，有的同学没有成绩，因此我们可以将student与上述两种方法进行左连接--例如： 124 select a.sid ,a.allscore 总成绩,count(distinct b.allscore)+1 排名 from 125 (select student.sid ,isnull(sum(score),0) allscore from student left join sc on student.sid=sc.sid group by student.sid ) a left join 126 (select student.sid ,isnull(sum(score),0) allscore from student left join sc on student.sid=sc.sid group by student.sid ) b on a.allscore<b.allscore 127 group by a.sid,a.allscore order by 排名 asc 128 129 select student.sid,isnull(sum(score),0) 总成绩,dense_rank()over(order by isnull(sum(score),0) desc )排名 130 from student left join sc on student.sid=sc.sid group by student.sid 131 132 --23、查询不同老师所教不同课程平均分从高到低显示 133 select course.tid,tname,course.cid,cast(avg(score) as decimal(5,2)) 课程平均分 from course left join sc on sc.cid=course.cid 134 left join teacher on teacher.tid=course.tid group by course.tid ,tname,course.cid order by avg(score) desc 135 136 137 --24、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩 138 --第一种 139 select student.*,m.cid,m.成绩,m.排名 from student left join 140 ( select a.cid,a.sid,a.score 成绩,count(b.score)+1 排名 from sc a left join sc b on a.cid=b.cid and a.score<b.score 141 group by a.cid,a.sid,a.score ) m on m.sid=student.sid where m.排名 between 2 and 3 order by m.cid asc,排名 asc--重复排名时保留名次空缺,把count 内改成distinct b.score 即为重复时合并空缺 142 --第二种 143 select student.*,m.cid,m.成绩,m.排名 from student left join 144 (select cid,sid,score 成绩,rank()over(partition by cid order by score desc) 排名 from sc group by cid,sid,score) m 145 on student.sid=m.sid where m.排名 between 2 and 3 order by cid asc--rank 改为dense_rank 即为合并名次空缺 146 147 148 --25、查询各科成绩前三名的记录 149 --第一种 150 select a.cid,a.sid,a.score from sc a left join sc b on a.cid=b.cid and a.score<b.score 151 group by a.cid,a.sid,a.score having count(b.score)<3 order by a.cid asc ,a.score desc --成绩重复时保留名次空缺，输出排名为1-3的学生;也可以按照24题来做,排名改成between 1 and 3即可 152 --第二种 153 select cid,sid,score from sc a where sid in ( 154 select top 3 b.sid from sc b where a.cid=b.cid order by b.cid asc ,b.score desc ) 155 order by cid asc,score desc --这种top 型的只输出前3个学生，对于成绩重复的同一名次会缺失 156 --第三种 157 select * from 158 (select *,row_number()over(partition by cid order by score desc) 排名 from sc) B 159 where B.排名<4--同样只输出前3个学生，对于成绩重复的同一名次会缺失 160 161 --26、查询各科成绩最高的记录 162 select sc.cid, sc.sid,m.maxscore from sc join (select cid,max(score) maxscore from sc group by cid ) m 163 on m.maxscore=sc.score and m.cid=sc.cid 164 --其余方法可参直接参考25题方法 165 166 --27、查询选修一门以上且不包括最高成绩学生的其余学生成绩 167 --第一种 168 select sid,cid,score from sc where sid not in (select sc.sid from sc join (select cid,max(score) maxscore from sc group by cid ) m 169 on m.maxscore=sc.score and m.cid=sc.cid ) and sid in (select sid from sc group by sid having count (sid)>1) 170 --第二种 171 select sid,cid,score from sc where sid not in 172 ( select a.sid from sc a left join sc b on a.cid=b.cid and a.score<b.score group by a.cid,a.sid,a.score having count(b.score)=0) 173 and sid in (select sid from sc group by sid having count (sid)>1) 174 --若改成-选修一门以上不是最高成绩的其余成绩(还包括最高成绩的学生的其他科目成绩)--每科成绩非最高分的记录 175 select cid,sid,score from sc a where sid not in ( 176 select top 3 b.sid from sc b where a.cid=b.cid order by b.cid asc ,b.score desc ) 177 and sid in (select sid from sc group by sid having count (sid)>1) 178 179 --28、查询每门课程被选修的学生数 180 select cid,count(sid) 选修人数 from sc group by cid 181 182 --29、查询出只选修两门课程的学生学号和姓名 183 select student.sid 学号,sname 姓名 from student join sc on student.sid=sc.sid group by student.sid,sname having count(student.sid)=2 184 --30、查询男生、女生人数 185 select ssex, count (ssex) 人数 from student group by ssex 186 --31、查询名字中含有"风"字的学生信息 187 select * from student where sname like '风%' or sname like '%风' 188 --32、查询同名同性学生名单，并统计同名人数 189 select a.sname,a.ssex,count(a.sname) 同名人数 from student a,student b 190 where a.sid!=b.sid and a.sname=b.sname and a.ssex=b.ssex group by a.sname,a.ssex 191 --33、查询1990年出生的学生名单 192 select * from student where datename(yy,sage)='1990' 193 --34、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 194 select cid,cast(avg(score) as decimal(5,2)) avgscore from sc group by cid order by avgscore desc,cid asc 195 --35、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 196 select student.sid,sname,avg(score) from student 197 join sc on student.sid =sc.sid group by student.sid,sname having avg(score)>=85 198 --36、查询课程名称为"数学"，且分数低于60的学生姓名和分数 199 select student.sname,score from student join sc on student.sid=sc.sid 200 join course on course.cid=sc.cid where cname='数学' and score <60 201 --37、查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况） 202 select student.*,cid,score from student left join sc on student.sid=sc.sid 203 --38、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数 204 select student.sid,course.cname,sc.score from student join sc on student.sid=sc.sid join course on course.cid=sc.cid where score>70 205 --39、查询存在不及格的课程 206 select distinct cid from sc where score<60--distinct确定唯一性 207 select cid from sc where score<60 group by cid--group by 确定唯一性 208 --40、查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名 209 select student.sid,student.sname from student join sc on student.sid=sc.sid where cid='01' and score>=80 210 --41、求每门课程的学生人数 211 --要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列 212 select cid,count(sid) 每门课程人数 from sc group by cid order by 每门课程人数 desc,cid asc 213 --42、成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩 214 select top 1 student.*,m.maxscore from student join ( 215 select sid,max(score) maxscore from teacher join course on course.tid=teacher.tid join sc on sc.cid=course.cid 216 where tname='张三' group by sid ) m on student.sid=m.sid order by m. maxscore desc 217 218 --若改成：每个教师所教每门课程的最高成绩 219 select m.tname,m.cid,sid,m.maxscore from sc join ( 220 select tname,sc.cid,max(score) maxscore from teacher join course on course.tid=teacher.tid join sc on sc.cid=course.cid 221 group by tname,sc.cid) m on sc.cid=m.cid and sc.score=m.maxscore order by m.cid asc 222 223 --43、成绩重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩 224 select student.*,m.score from student join ( 225 select a.cid,a.sid,a.score from sc a left join sc b on a.cid=b.cid and a.score<b.score group by a.cid,a.sid,a.score having count(b.score)=0) m 226 on student.sid=m.sid join course on course.cid=m.cid join teacher on teacher.tid=course.tid where tname='张三'--m表内容也可以改成用rank函数做成排名，取排名第一即可 227 228 --44、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 229 select a.sid,a.cid,a.score from sc a join sc b on a.sid=b.sid where a.cid!=b.cid and a.score=b.score group by a.cid,a.sid,a.score 230 231 --45、查询选修了全部课程的学生信息 232 select student.* from student join 233 (select sid from sc group by sid having count(sc.cid)= (select count(cid) from course)) m 234 on student.sid=m.sid 235 --46、查询各学生的年龄,只按照年份来算 236 select student.*,datediff(yy,sage,getdate()) age from student 237 --47、查询各学生的年龄,按照出生日期来算，当前月日 < 出生年月的月日则年龄减一 238 --第一种 239 select *, 240 (case when convert(int,convert(varchar(10),getdate(),112))<convert (int,convert(varchar(10),year(getdate()))+substring(convert(varchar(10),sage,112),5,4)) 241 then datediff(yy,sage,getdate())-1 else datediff(yy,sage,getdate()) end )age--出生年月变成现在的年与月日 同现在的时间对比大小 242 from student 243 --第二种 244 select *,(case when month(getdate())<month(sage) then datediff(yy,sage,getdate())-1 245 when month(getdate())=month(sage) and day(getdate())<day(sage) then datediff(yy,sage,getdate())-1 246 else datediff(yy,sage,getdate()) end) age from student 247 --第三种 248 select * , (case when right(convert(varchar(10),getdate(),120),5) < right(convert(varchar(10),sage,120),5) 249 then datediff(yy ,sage,getdate()) - 1 else datediff(yy ,sage, getdate()) end) age from student 250 --48、查询本周过生日的学生 251 --第一种 252 select *,(case when datename(wk,convert(datetime,(convert(varchar(10),year(getdate()))+substring(convert(varchar(10),sage,112),5,4))))= 253 datename(wk,getdate()) then 1 else 0 end) 生日提醒 from student 254 --第二种 255 select * from student where datediff(wk,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = 0 256 --49、查询下周过生日的学生 257 --第一种 258 select *,(case when datename(wk,convert(datetime,(convert(varchar(10),year(getdate()))+substring(convert(varchar(10),sage,112),5,4))))= 259 datename(wk,getdate())+1 then 1 else 0 end) 生日提醒 from student 260 --第二种 261 select * from student where datediff(wk,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = -1 262 --50、查询本月过生日的学生 263 --第一种 264 select * from student where month(getdate())=month(sage) 265 --第二种 266 select * from student where datediff(mm,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = 0 267 268 --51、查询下月过生日的学生 269 --第一种 270 select * from student where month(getdate())+1=month(sage) 271 --第二种 272 select * from student where datediff(mm,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = -1
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
