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é)生表中最長(zhǎng)姓名的長(zhǎng)度。

 select len(stuname) from student where len(stuname)>=all(select  len(stuname) from student)

2．查詢學(xué)生表中姓名最長(zhǎng)的學(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