create table student(
stuid varchar(20),
stuname varchar(20),
birthday datetime,
age int,
nextbirth datetime
);
insert into student values('200911001','david','1990-08-02',null,null);
insert into student values('200911002','田中一郎','1991-05-25',null,null);
insert into student values('201011001','王小虎','1991-01-18',null,null);
insert into student values('201011002','赵强','1992-02-01',null,null);
1．查询学生表中最长姓名的长度。
select len(stuname) from student where len(stuname)>=all(select len(stuname) from student)
2．查询学生表中姓名最长的学生信息。
select * from student where len(stuname)>=all(select len(stuname) from student)
3．修改学生信息，将学号中的2009替换成09。
update student
set stuid=replace(stuid,'2009','09')
4．查询姓名为“王小虎”的学号的最末两位。
select right(stuid,2) from student where stuname='王小虎'
5．计算学生的年龄，替换学生表中的age列。
update student
set age=year(getdate())-year(birthday)
6．查询出生年是1991的学生信息。
select *from student where year(birthday)='1991'
7．查询出生月份是1至6月的学生信息。
select *from student where month(birthday) between 1 and 6
8．查询当月有哪些学生过生日。
select * from student where month(getdate())>= month(birthday)and day(getdate())>=day(birthday)
9．计算下一次过生日的日期，替换学生表中的nextbirth列。
update student
set nextbirth= dateadd(year,ceiling(datediff(day,birthday,getdate())/365.0),birthday)
10．计算下一次过生日距离现在还有多少天。
select datediff(
day,
getdate(),
dateadd(year,ceiling(datediff(day,birthday,getdate())/365.0),
birthday)
) from student
