sqlserver 期中考试题

 create database DB
use DB

--专业表
create table major
(spno char(5) not null primary key,
 spname varchar(20) not null,
 pno char(2) )

--学生表
create table student
(sno char(7) not null primary key,
 sname varchar(20) not null,
 ssex char(2) not null,
 sage smalldatetime ,
 spno char(5) not null foreign key references major(spno),
 classid char(5),
 Inyear char(4) not null )

 

select datediff(yyyy,'1981-8-12',getdate())from student

--课程表
create table course
(cno char(10) not null primary key,
cname varchar(20) not null,
credit smallint ,
tno char(3))

--选课表
create table scourse
(sno char(7) not null foreign key references student(sno),
 cno char(10) not null foreign key references course(cno),
 Gmark numeric(4,1),
primary key(sno,cno))

//第一题
select * from student where datediff(yyyy,sage,getdate