51到数据库经典训练题
内容纲要
-- 创建测试数据库 CREATE DATABASE test COLLATE Chinese_PRC_CI_AS -- 创建学生表 create table Student ( S# varchar(10) not null primary key, Sname nvarchar(10), Sage datetime, Ssex nvarchar(10) ); -- 创建教师表 create table Teacher ( T# varchar(10) NOT null primary key, Tname nvarchar(10) ); -- 创建课程表 create table Course ( C# varchar(10) not null PRIMARY key, Cname nvarchar(10), T# varchar(10) FOREIGN KEY REFERENCES Teacher(T#) ); -- 创建成绩表 create table SC ( S# varchar(10) FOREIGN key REFERENCES Student(S#), C# varchar(10) FOREIGN key REFERENCES Course(C#), score decimal(18,1) ); -- 插入学生数据 insert into Student values('01' , '赵雷' , '1990-01-01' , '男'); insert into Student values('02' , '钱电' , '1990-12-21' , '男'); insert into Student values('03' , '孙风' , '1990-05-20' , '男'); insert into Student values('04' , '李云' , '1990-08-06' , '男'); insert into Student values('05' , '周梅' , '1991-12-01' , '女'); insert into Student values('06' , '吴兰' , '1992-03-01' , '女'); insert into Student values('07' , '郑竹' , '1989-07-01' , '女'); insert into Student values('08' , '王菊' , '1990-01-20' , '女'); -- 插入教师数据 insert into Teacher values('01' , '张三'); insert into Teacher values('02' , '李四'); insert into Teacher values('03' , '王五') ; -- 插入课程数据 insert into Course values('01' , '语文' , '02') insert into Course values('02' , '数学' , '01') insert into Course values('03' , '英语' , '03') -- 插入成绩数据 insert into SC values('01' , '01' , 80) insert into SC values('01' , '02' , 90) insert into SC values('01' , '03' , 99) insert into SC values('02' , '01' , 70) insert into SC values('02' , '02' , 60) insert into SC values('02' , '03' , 80) insert into SC values('03' , '01' , 80) insert into SC values('03' , '02' , 80) insert into SC values('03' , '03' , 80) insert into SC values('04' , '01' , 50) insert into SC values('04' , '02' , 30) insert into SC values('04' , '03' , 20) insert into SC values('05' , '01' , 76) insert into SC values('05' , '02' , 87) insert into SC values('06' , '01' , 31) insert into SC values('06' , '03' , 34) insert into SC values('07' , '02' , 89) insert into SC values('07' , '03' , 98) -- 1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数 SELECT a.S# as '学生编号', Sname as '学生姓名', Sage as '出生年月', Ssex AS '性别', b.score as '课程分数' from ( SELECT * FROM SC WHERE SC.C#='01' ) AS a , ( SELECT * FROM SC WHERE SC.C#='02' ) AS b , Student c WHERE a.score > b.score AND a.S#=b.S# AND c.S#=a.S#; -- 1.1 查询同时存在" 01 "课程和" 02 "课程的情况 SELECT a.S# as '学生编号', Sname as '学生姓名', Sage as '出生年月', Ssex AS '性别' -- b.score as '课程分数' from ( SELECT * FROM SC WHERE SC.C#='01' ) AS a JOIN ( SELECT * FROM SC WHERE SC.C#='02' ) AS b on( a.S#=b.S#) JOIN Student c on (a.S#=c.S#) -- 1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null ) SELECT a.S# as '学生编号', Sname as '学生姓名', Sage as '出生年月', Ssex AS '性别', b.C# as '课程号' from ( SELECT * FROM SC WHERE SC.C#='01' ) AS a LEFT JOIN ( SELECT * FROM SC WHERE SC.C#='02' ) AS b on( a.S#=b.S#) JOIN Student c on (a.S#=c.S#) -- 1.3 查询不存在" 01 "课程但存在" 02 "课程的情况 SELECT b.S# as '学生编号', Sname as '学生姓名', Sage as '出生年月', Ssex AS '性别', b.C# as '课程号' from ( SELECT * FROM SC WHERE SC.C#='01' ) AS a RIGHT JOIN ( SELECT * FROM SC WHERE SC.C#='02' ) AS b on( a.S#=b.S#) JOIN Student c on (b.S#=c.S#) WHERE a.S# is NULL; -- 2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩 SELECT a.S# as '学生编号', a.Sname as '学生姓名', AVG(b.score) as '平均成绩' from Student a RIGHT JOIN SC b on (a.S#=b.S#) group by a.S# ,a.Sname HAVING AVG(b.score) >= 60; -- 3. 查询在 SC 表存在成绩的学生信息 SELECT DISTINCT a.S# as '学生编号', Sname as '学生姓名', Sage as '出生年月', Ssex AS '性别' from Student a JOIN SC c on (a.S#=c.S#) WHERE c.score is not NULL; -- 4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null ) SELECT a.S# as '学生编号', Sname as '学生姓名', count(b.S#) as '选课总数', AVG(b.score) AS '总成绩' from Student a LEFT JOIN SC b on (a.S#=b.S#) GROUP by a.S# ,a.Sname; -- 4.1 查有成绩的学生信息 SELECT DISTINCT a.S# as '学生编号', Sname as '学生姓名', Sage as '出生年月', Ssex AS '性别' from Student a RIGHT JOIN SC b on(a.S#=b.S#) WHERE b.score IS not NULL -- 5. 查询「李」姓老师的数量 SELECT COUNT(*) AS '「李」姓老师的数量' from Teacher a WHERE a.Tname like CONCAT('李','%') GROUP BY a.Tname; -- 6. 查询学过「张三」老师授课的同学的信息? SELECT a.S# as '学生编号', Sname as '学生姓名', Sage as '出生年月', Ssex AS '性别' , c.C# from Student a , ( SELECT b.* from Teacher a JOIN Course b on (a.T#=b.T# ) WHERE a.Tname='张三' ) AS b JOIN SC as c on (b.C#=c.C#) WHERE a.S#=c.S#; -- ↓ 效率低 SELECT a.S# as '学生编号', Sname as '学生姓名', Sage as '出生年月', Ssex AS '性别', c.C# from Student a JOIN SC C on (a.S#=c.S# and c.C# in ( SELECT b.C# from Teacher a JOIN Course b on (a.T#=b.T# ) WHERE a.Tname='张三' ) ); -- 7. 查询没有学全所有课程的同学的信息? SELECT DISTINCT a.S# as '学生编号', a.Sname as '学生姓名', a.Sage as '出生年月', a.Ssex AS '性别', count(b.S#) AS '已学课程数', c.courseNum as '全部课程数' from Student a JOIN ( select a.* from Student a JOIN SC c on (a.S#=c.S#) ) as b on (a.S#=b.S#), ( select COUNT(*) as courseNum from Course c ) as c GROUP BY a.S#, a.Sage ,a.Sname, a.Ssex ,b.S# ,c.courseNum HAVING count(b.S#) < c.courseNum; -- 8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息? -- select c.C# ,c.Cname -- from SC a JOIN Course c on(a.C#=c.C#) -- WHERE a.S#='01'; SELECT DISTINCT a.S# as '学生编号', a.Sname as '学生姓名', a.Sage as '出生年月', a.Ssex AS '性别' FROM Student a , SC b INNER JOIN ( select c.C# , c.Cname from SC a JOIN Course c on(a.C#=c.C#) WHERE a.S#='01' ) as c on(b.C#=c.C#) WHERE a.S#=b.S# -- 9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息? SELECT DISTINCT a.S# as '学生编号', a.Sname as '学生姓名', a.Sage as '出生年月', a.Ssex AS '性别', count(b.S#) AS '已学课程数', c.courseNum as '全部课程数' from Student a JOIN ( select a.* from Student a JOIN SC c on (a.S#=c.S#) ) as b on (a.S#=b.S#), ( select DISTINCT COUNT(c.C#) as courseNum from SC a JOIN Course c on(a.C#=c.C#) WHERE a.S#='01' ) as c GROUP BY a.S#, a.Sage ,a.Sname, a.Ssex ,b.S# ,c.courseNum HAVING count(b.S#) = c.courseNum; -- 10. 查询没学过"张三"老师讲授的任一门课程的学生姓名? SELECT b.C# from Teacher a JOIN Course b on (a.T#=b.T# ) WHERE a.Tname='张三'; select distinct a.C#, a.S# from SC a LEFT JOIN ( SELECT b.C# from Teacher a JOIN Course b on (a.T#=b.T# ) WHERE a.Tname='张三' ) as c ON (a.C#=c.C#) WHERE c.C# IS NULL select distinct a.Sname AS '学生姓名' from Student a INNER JOIN ( select distinct a.C#, a.S# from SC a LEFT JOIN ( SELECT b.C# from Teacher a JOIN Course b on (a.T#=b.T# ) WHERE a.Tname='张三' ) as c ON (a.C#=c.C#) WHERE c.C# IS NULL )as b on (a.S#=b.S#) -- 11. 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩? SELECT a.S# as '学生编号', a.Sname as '学生姓名', a.Sage as '出生年月', a.Ssex AS '性别', AVG(c.score) from ( select a.* from Student a JOIN SC c on (a.S#=c.S#) WHERE c.score < 60 ) as a INNER JOIN SC c ON(a.S#=c.S#) GROUP BY a.S#, a.Sage ,a.Sname, a.Ssex HAVING count(a.S#) >=2; -- 12. 检索" 01 "课程分数小于 60,按分数降序排列的学生信息 select b.S# as '学生编号', Sname as '学生姓名', Sage as '出生年月', Ssex AS '性别' from SC a INNER JOIN Student b on(a.S#=b.S#) WHERE a.C#='01' GROUP BY b.S# ,a.score,Sage,Ssex,b.Sname HAVING a.score < 60 ORDER BY a.score DESC; -- 13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 SELECT AVG(b.score) as avg , a.S# FROM Student a JOIN SC b on(a.S#=b.S#) GROUP by a.S#; SELECT a.Sname as '学生姓名' , c.Cname as '课程名称', b.score as '课程分数', d.[avg] as '平均分' from Student a INNER JOIN SC b ON(a.S#=b.S#) JOIN Course c ON(b.C#=c.C#) JOIN ( SELECT AVG(b.score) as avg , a.S# FROM Student a JOIN SC b on(a.S#=b.S#) GROUP by a.S# ) d on (a.S#=d.S#) order by d.[avg] DESC; -- 14. 查询各科成绩最高分、最低分和平均分: -- 以如下形式显示: -- 课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率 -- 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90 -- 要求输出最低分和平均分:课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列 -- 及格 SELECT a.C#, (a.num *1.0 )/b.num *100 as rate FROM ( SELECT COUNT(a.C#) as num , a.C# from SC a WHERE a.score >=60 GROUP BY a.C# ) as a JOIN ( SELECT COUNT(a.C#) as num, b.C# from SC a JOIN Course b ON(a.C#=b.C#) GROUP BY b.C# ) as b on (a.C#=b.C#) -- 中等 SELECT a.C#, (a.num *1.0 )/b.num *100 as rate FROM ( SELECT COUNT(a.C#)as num , a.C# from SC a WHERE a.score BETWEEN 70 and 79 GROUP BY a.C# ) as a JOIN ( SELECT COUNT(a.C#) as num, b.C# from SC a JOIN Course b ON(a.C#=b.C#) GROUP BY b.C# ) as b on (a.C#=b.C#) -- 优良率 SELECT a.C#, (a.num *1.0 )/b.num *100 as rate FROM ( SELECT COUNT(a.C#)as num , a.C# from SC a WHERE a.score BETWEEN 80 and 89 GROUP BY a.C# )as a JOIN ( SELECT COUNT(a.C#) as num, b.C# from SC a JOIN Course b ON(a.C#=b.C#) GROUP BY b.C# )as b on (a.C#=b.C#) -- 优秀率 SELECT a.C#, (a.num *1.0 )/b.num *100 as rate from ( SELECT COUNT(a.C#) as num, a.C# from SC a WHERE a.score >=90 GROUP BY a.C# ) as a JOIN ( SELECT COUNT(a.C#) as num, b.C# from SC a JOIN Course b ON(a.C#=b.C#) GROUP BY b.C# ) as b on(a.C#=b.C#) -- 各科选修人数 SELECT COUNT(a.C#) as num, b.C# from SC a JOIN Course b ON(a.C#=b.C#) GROUP BY b.C# SELECT b.C# as '课程ID', b.Cname as '课程名称', numSubjects.num as '选修人数', MAX(a.score) as '最高分', MIN(a.score) as '最低分', AVG(a.score) as '平均分', cast( convert( decimal(4,2), sum( iif( (a.score >=0 and a.score <=60), 1, 0 ) )*1.0/numSubjects.num*100 ) as varchar(100) )+'%' as '及格率', cast( convert( decimal(4,2), sum( iif( (a.score >=60 and a.score <70), 1, 0 ) )*1.0/numSubjects.num*100 ) as varchar(100) )+'%' as "中等率", cast( convert( decimal(4,2), sum( iif( (a.score >=80 and a.score <90), 1, 0 ) ) * 1.0 /numSubjects.num *100 ) as varchar(100) )+'%' as '优良率', cast( convert( decimal(4,2), sum( iif( (a.score >=90 ), 1, 0 ) )*1.0/numSubjects.num*100 ) as varchar(100) )+'%' as '优秀率' FROM Course b LEFT JOIN SC a ON(a.C#=b.C#) LEFT JOIN ( SELECT COUNT(a.C#) as num, b.C# from SC a JOIN Course b ON(a.C#=b.C#) GROUP BY b.C# )as numSubjects ON(b.C#=numSubjects.C#) GROUP BY b.Cname,b.C#,numSubjects.num ORDER BY numSubjects.num DESC , b.C#; -- 15. 按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺 select a.C# '课程id', Cname '课程名称', score '分数', rank() over(partition by a.C# order by score desc) '排名' from SC a JOIN Course b ON(a.C#=b.C#) -- 15.1 按各科成绩进行排序,并显示排名, Sacore 重复时合并名次 SELECT b.C# as '课程id', b.Cname as '课程名称' , a.score as '分数' , ( select count(distinct b.score ) from SC b where b.Score +1 > a.Score and a.C#=b.C# )as '排名' from SC a JOIN Course b ON(a.C#=b.C#) ORDER BY b.C# ,'排名'; select a.C# '课程id', Cname '课程名称', score '分数', DENSE_RANK () over(partition by a.C# order by score desc) '排名' from SC a JOIN Course b ON(a.C#=b.C#) -- 16. ?查询学生的总成绩,并进行排名,总分重复时保留名次空缺 SELECT b.S# '学生编号', b.Sname '姓名' , SUM(a.score) '总分', rank() OVER (order by SUM(a.score) desc) '排名' FROM SC a LEFT JOIN Student b on (a.S#=b.S#) GROUP by b.Sname , b.S# order by SUM(a.score) DESC -- 16.1 查询学生的总成绩,并进行排名,总分重复时不保留名次空缺 SELECT b.S# '学生编号', b.Sname '姓名' , SUM(a.score) '总分', DENSE_RANK() OVER (order by SUM(a.score) desc) '排名' FROM SC a LEFT JOIN Student b on (a.S#=b.S#) GROUP by b.Sname , b.S# order by SUM(a.score) DESC -- 17. 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比 BEGIN if 5 =1 SELECT * FROM SC ELSE SELECT * FROM Teacher END SELECT b.C# '课程编号', b.Cname '课程名称', SUM( IIF( (a.score >=85 and a.score <=100), 1, 0 ) ) '[100-85]', cast( convert( decimal(4,2), sum( iif( (a.score >=85 and a.score <=100), 1, 0 ) )*1.0/count(*)*100 ) as varchar(100) )+'%' as '[100-85]占比', SUM( iif( (a.score >=70 and a.score <85), 1, 0 ) ) '[85-70)', cast( convert( decimal(4,2), sum( iif( (a.score >=70 and a.score <85), 1, 0 ) )*1.0/count(*)*100 ) as varchar(100) )+'%' as '[85-70)占比', SUM( iif( (a.score >=60 and a.score <70), 1, 0 ) ) '[70-60]' , cast( convert( decimal(4,2), sum( iif( (a.score >=60 and a.score <70), 1, 0 ) )*1.0/count(*)*100 ) as varchar(100) )+'%' as '[70-60]占比', SUM( iif( (a.score >=0 and a.score <60), 1, 0 ) ) '[60-0]', cast( convert( decimal(4,2), sum( iif( (a.score >=0 and a.score <=60), 1, 0 ) )*1.0/count(*)*100 ) as varchar(100) )+'%' as '[60-0] 占比' FROM SC a LEFT JOIN Course b on a.C#=b.C# group by b.C#,b.Cname -- 18. 查询各科成绩前三名的记录 select a.课程id , a.课程名称, a.名字 , a.分数 , a.rank '排名' from ( select a.C# '课程id', c.Cname '课程名称', score '分数', b.Sname '名字', rank() over(partition by a.C# order by score desc) rank from SC a LEFT JOIN Student b on (a.S#=b.S#) JOIN Course c ON(a.C#=c.C#) ) a WHERE rank < =3 ORDER by a.课程id; -- 19. 查询每门课程被选修的学生数? SELECT b.C# '课程id', b.Cname '课程名称', COUNT(a.C#) as '选修人数' from SC a JOIN Course b ON(a.C#=b.C#) GROUP BY b.C# ,b.Cname,a.C# ORDER by a.C#; -- 20. 查询出只选修两门课程的学生学号和姓名? SELECT b.S#, b.Sname FROM SC a INNER join Student b on a.S#=b.S# GROUP by b.S#,b.Sname having COUNT(a.S#)=2; SELECT * FROM SC INNER JOIN Student on SC.S#=Student.S#; -- 21. 查询男生、女生人数 SELECT Ssex '性别', COUNT(*) '人数' from Student group by Ssex; -- 22. 查询名字中含有「风」字的学生信息 SELECT a.S# '学号', a.Sname '名字' , a.Sage '出生日期' , a.Ssex '性别' from Student a WHERE a.Sname LIKE CONCAT('%','风','%') -- 23. 查询同名同性学生名单,并统计同名人数 SELECT * FROM Student select Sname, Ssex, COUNT(*) '人数' from Student group by Sname,Ssex having COUNT(*)>1; -- 24. 查询 1990 年出生的学生名单 SELECT a.S# '学号', a.Sname '名字' , a.Sage '出生日期' , a.Ssex '性别' FROM Student a WHERE YEAR(a.Sage)='1990' -- 25. 查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列 select C# '课程编号', CONVERT(decimal(4,2), AVG(score)) '平均成绩' from SC group by C# order by AVG(score) desc,C# -- 26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩? SELECT b.S# '学号', b.Sname '姓名', CONVERT(decimal(4,2), AVG(score)) '平均成绩' FROM SC a left JOIN Student b on a.S#=b.S# GROUP BY b.S#,b.Sname HAVING AVG(a.score) >=85 -- 27. 查询课程名称为「数学」,且分数低于 60 的学生姓名和分数? SELECT b.Sname '姓名', a.score '分数' FROM SC a left JOIN Student b ON a.S#=b.S# LEFT join Course c ON a.C#=c.C# WHERE c.Cname='数学' and a.score < 60 -- 28.查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况) SELECT a.S# '学号', a.Sname '名字' , c.Cname '课程名称' , b.score '分数' FROM Student a LEFT JOIN SC b on a.S#=b.S# LEFT JOIN Course c on b.C#=c.C# -- 29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数 SELECT b.Sname '姓名' , c.Cname '课程名' , a.score '分数' FROM SC a LEFT JOIN Student b on a.S#=b.S# LEFT JOIN Course c on a.C#=c.C# WHERE a.score > 70 -- 30. 查询不及格的课程 SELECT DISTINCT b.Cname '课程' from SC a left JOIN Course b on a.C#=b.C# WHERE a.score < 60 -- 31. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名 SELECT c.S# '学号' , c.Sname '姓名' from SC a LEFT JOIN Student c on a.S#=c.S# WHERE a.score > 80 and a.C#='01' SELECT * FROM SC WHERE C#='01' -- 32. 求每门课程的学生人数 SELECT b.C# '课程id', b.Cname '课程名称', Str(COUNT(a.C#)) +'人' as '选修人数' from SC a JOIN Course b ON(a.C#=b.C#) GROUP BY b.C# ,b.Cname,a.C# ORDER by a.C#; -- 33. 成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩 SELECT top(1) a.score , c.Sname FROM SC a LEFT JOIN Course b on a.C# = b.C# LEFT JOIN Student c on a.S# = c.S# LEFT JOIN Teacher d on b.T#=d.T# WHERE d.Tname='张三' GROUP by a.score ,c.Sname ORDER BY a.score DESC -- 34. 成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩 select a.课程id , a.课程名称, a.学号, a.名字 , a.分数 , a.rank '排名' from ( select a.C# '课程id', c.Cname '课程名称', score '分数', b.S# '学号' , b.Sname '名字', rank() over(partition by a.C# order by score desc) rank from SC a LEFT JOIN Student b on (a.S#=b.S#) JOIN Course c ON(a.C#=c.C#) LEFT join Teacher d on c.T#=d.T# WHERE d.Tname = '张三' ) a WHERE rank < =1 ORDER by a.课程id; -- 35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩? SELECT DISTINCT a.C# , a.S# , a.score FROM SC a join SC b on (a.S#=b.S# and a.score=b.score) WHERE a.C# != b.C# -- 36. 查询每门功成绩最好的前两名 select a.课程id , a.课程名称, a.名字 , a.分数 , a.rank '排名' from ( select a.C# '课程id', c.Cname '课程名称', score '分数', b.Sname '名字', rank() over(partition by a.C# order by score desc) rank from SC a LEFT JOIN Student b on (a.S#=b.S#) JOIN Course c ON(a.C#=c.C#) ) a WHERE rank < =3 ORDER by a.课程id; -- 37. 统计每门课程的学生选修人数(超过 5 人的课程才统计) SELECT b.C# '课程id', b.Cname '课程名称', Str(COUNT(a.C#)) +'人' as '选修人数' from SC a JOIN Course b ON(a.C#=b.C#) GROUP BY b.C# ,b.Cname,a.C# HAVING COUNT(a.C#) > 5 ORDER by a.C# -- 38. 检索至少选修两门课程的学生学号? SELECT a.S# '学号' -- ,b.Sname '姓名' from SC a LEFT JOIN Student b on a.S#=b.S# GROUP BY a.S# ,b.Sname HAVING COUNT(*) >2 ORDER by a.S# -- 39. 查询选修了全部课程的学生信息 SELECT a.S# '学号', b.Sname '姓名' from SC a LEFT JOIN Student b on a.S#=b.S# , ( select DISTINCT COUNT(a.C#) as num from Course a ) as c GROUP BY a.S# ,b.Sname ,c.num HAVING COUNT(a.C#) =c.num ORDER BY a.S# -- 40. 查询各学生的年龄,只按年份来算? SELECT a.S# '学号', a.Sname '名字' , Str(YEAR(GETDATE())-YEAR(a.Sage)) +'岁' '年龄' FROM Student a -- 41. 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一 DECLARE @a DATETIME; SET @a= GETDATE(); SELECT a.S# '学号', a.Sname '名字' , IIF( ( MONTH(Sage) < MONTH(@a) Or ( MONTH(Sage) =MONTH(@a) and (DAY(Sage) < DAY(@a)))), Str(YEAR(@a)-YEAR(a.Sage) -1) +'岁', Str(YEAR(@a)-YEAR(a.Sage)) +'岁' ) '年龄' FROM Student a -- 42. 查询本周过生日的学生 SELECT a.S# '学号', a.Sname '名字', a.Sage '出生日期' FROM Student a WHERE DATEPART(WEEK,Sage) = DATEPART(ww,GETDATE()); -- 43. 查询下周过生日的学生 SELECT a.S# '学号', a.Sname '名字', a.Sage '出生日期' FROM Student a WHERE DATEPART(WEEK,Sage) = DATEPART(ww,GETDATE()) +1; -- Update rows in table 'Student' UPDATE Student SET [Sage] = '2020-07-14' WHERE S#='16' GO -- 44. 查询本月过生日的学生 SELECT a.S# '学号', a.Sname '名字', a.Sage '出生日期' FROM Student a WHERE DATEPART(mm,Sage)=DATEPART(mm,GETDATE()) -- 45. 查询下月过生日的学生 SELECT a.S# '学号', a.Sname '名字', a.Sage '出生日期' FROM Student a WHERE DATEPART(mm,Sage)=DATEPART(mm,GETDATE())+1