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','趙強(qiáng)','1992-02-01',null,null);
1．查詢學(xué)生表中最長姓名的長度帖鸦。
select len(stuname) from student where len(stuname)>=all(select len(stuname) from student)
2．查詢學(xué)生表中姓名最長的學(xué)生信息宠叼。
select * from student where len(stuname)>=all(select len(stuname) from student)
3．修改學(xué)生信息，將學(xué)號(hào)中的2009替換成09褐奥。
update student
set stuid=replace(stuid,'2009','09')
4．查詢姓名為“王小虎”的學(xué)號(hào)的最末兩位。
select right(stuid,2) from student where stuname='王小虎'
5．計(jì)算學(xué)生的年齡翘簇，替換學(xué)生表中的age列撬码。
update student
set age=year(getdate())-year(birthday)
6．查詢出生年是1991的學(xué)生信息。
select *from student where year(birthday)='1991'
7．查詢出生月份是1至6月的學(xué)生信息版保。
select *from student where month(birthday) between 1 and 6
8．查詢當(dāng)月有哪些學(xué)生過生日呜笑。
select * from student where month(getdate())>= month(birthday)and day(getdate())>=day(birthday)
9．計(jì)算下一次過生日的日期，替換學(xué)生表中的nextbirth列找筝。
update student
set nextbirth= dateadd(year,ceiling(datediff(day,birthday,getdate())/365.0),birthday)
10．計(jì)算下一次過生日距離現(xiàn)在還有多少天蹈垢。
select datediff(
day,
getdate(),
dateadd(year,ceiling(datediff(day,birthday,getdate())/365.0),
birthday)
) from student
