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