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()) between 25 and (select datediff(yyyy,sage,getdate()) from student  where sname='李勇')
//第二题
select sno,sname from student where sno in  (  select sno from scourse where cno=(select cno from course where cname='操作系统'))
//第三题
 
select sname from student where sno in (select distinct sno from scourse where cno not in('1'))
//第四题
select sname,sno  from student where sno in(select sno from scourse group by sno having count(sno)=(select count(*) from course))
select student.sname,student.sno  from student where student.sno in(select scourse.sno from scourse group by scourse.sno having count(scourse.sno)=(select count(*) from course))
//第五题
select sname from student where sno in (select sno from student where Inyear='1999') and sno in (select sno from scourse where Gmark is null) and spno in(select spno from major where spname='计算机软件')
select student.sname from student where  student.sno in (select student.sno from student where student.Inyear='1999')   and student.sno in (select scourse.sno from scourse where