同济大学数据库 实验三四 lab3,4 sql server 12版

create table student
(
snum char(4) not null primary key
check (snum like 's[0-9][0-9][0-9]'),
sname char(20) ,
sex char(2) check (sex in ('男','女')),
dept char(30) ,
birthday date,
telephone char(13)
check (isnumeric(left(telephone,3))=1 and isnumeric(right(telephone,8))=1)
)

insert into student
values('s001','赵剑','男','计算机','1994-3-25','010-********'),
('s002','王谦','男','交通工程','1993-1-1','027-********'),
('s003','孙启明','男','土木工程','1994-4-1','021-********'),
('s004','宇帆','男','机械工程','1994-9-17','021-********'),
('s005','李晓静','女','生物工程','1995-6-21','030-22222222'),
('s006','金之林','女','计算机','1995-9-11','040-66666666'),
('s007','张东晓','男','城市规划','1994-8-2','050-77777777'),
('s008','海琳','女','城市规划','1995-5-23','070-88888888')

create table course
(
cnum char(4) not null primary key
check (cnum like 'c[0-9][0-9][0-9]'),
cname char(30) ,
credits smallint,
check (credits>=0 and credits<=8),
descr char(40),
dept char(30),
textbook char(40)
)

insert into course
values('c116','大学英语',6,'必修课','外语系','《大学英语》，同济大学出版社'),
('c120','高等数学',6,'必修课','数学系','《高等数学》，复旦大学出版社'),
('c126','大学物理',3,'必修课','物理系','《大学物理》，高等教育出版社'),
('c130','数据库技术',3,'限选课','计算机系','《数据库技术与应用》，高等教育出版社'),
('c132','多媒体技术',3,'限选课','计算机系','《多媒体技术与应用》，清华大学出版社'),
('c135','VB程序设计',3,'限选课','计算机系','《程序设计》，高等教育出版社')

create table section
(
secnum char(5) not null primary key
check (secnum like '[0-9][0-9][0-9][0-9][0-9]'),
cnum char(4),
pnum char(4)
check (pnum like 'p[0-9][0-9][0-9]'),
foreign key(cnum)
references course
)

insert into section
values('11601','c116','p001'),
('11602','c116','p002'),
('12001','c120','p003'),
('12002','c120','p003'),
('12601','c126','p004'),
('13001','c130','p005'),
('13002','c130','p006'),
('13201','c132','p007'),
('13501','c135','p007')

create table sc
(
snum char(4) not null,
foreign key(snum)
references student,
secnum char(5) not null,
foreign key(secnum)
references section,
score int
check (score>= 0 and score<=100),
primary key (snum,secnum)
)

insert into sc
values('s001','11601',77),
('s001','12001',80),
('s001','12601',89),
('s001','13002',90),
('s001','13201',92),
('s001','13501',94),
('s002','11602',90),
('s002','12601',88),
('s002','13201',98),
('s003','11601',90),
('s003','12002',94),
('s003','12601',88),
('s004','11601',89),
('s004','13001',90),
('s004','13201',92),
('s004','13501',89),
('s005','11602',56),
('s006','11601',88),
('s006','12601',78),
('s007','11602',90),
('s007','13201',95),
('s007','13501',50),
('s008','11601',89),
('

s008','12001',90),
('s008','12601',93)

1.select student.snum as 学号,student.snum,sname as 姓名
from student,sc
where student.snum=sc.snum and score<60
group by student.snum,sname

2.select student.sname as 姓名,FLOOR(datediff(DY,birthday,getdate())/365.25) as 年龄
from student,course,sc,section
where student.snum=sc.snum and sc.secnum=section.secnum and um=um and course.dept='计算机系'
group by student.snum,sname,student.birthday

3.select student.sname as 姓名,student.dept as 系别
from student,course,sc,section
where student.snum=sc.snum and sc.secnum=section.secnum and um=um and ame='数据库技术'

4.select student.snum as 学号,sname as 姓名
from student,course,sc,section
where student.snum=sc.snum and sc.secnum=section.secnum and um=um
group by student.snum,sname
having count(um)=(select count(*) from course)

5.select student.snum as 学号,sname as 姓名
from student
where snum not in(select snum
from sc
where score<=80)

6.select student.snum as 学号,sname as 姓名
from student,sc
where student.snum=sc.snum and student.snum not in(select snum
from sc
where score<=80)
group by student.snum,sname
having avg(score*1.0)>=90

7.select student.snum as 学号,sname as 姓名,score as 分数
from student,course,sc,section
where student.snum=sc.snum and sc.secnum=section.secnum and um=um and cname='大学英语'
order by score desc

8.select cnum as 课程号,count(snum) as 人数
from sc,section
where sc.secnum=section.secnum
group by cnum

9.select sname as 姓名,student.dept as 系别
from student,course,sc,section
where student.snum=sc.snum and sc.secnum=section.secnum and um=um and cname='数据库技术' and sname not in (select sname
from student,course,sc,section
where student.snum=sc.snum and sc.secnum=section.secnum and um=um and cname='高等数学')

10.select cname as 课程名
from course
where textbook like '%高等教育出版社'

11.select cname as 课程名,max(score) as 最高分,min(score) as 最低分,avg(score) as 平均分
from course,sc,section
where um=um and sc.secnum=section.secnum
group by um,cname

12.select cname as 课程名,count(snum) as 选课人数,sum(1-score/60) as 不及格人数
from course,sc,section
where um=um and sc.secnum=section.secnum
group by um,cname

13.select snum as 学号,sname as 姓名,dept as 系别
from student
where dept='土木工程'
union
select snum as 学号,sname as 姓名,dept as 系别
from student
where dept='交通工程'
union
select snum as 学号,sname as 姓名,dept as 系别
from student
where dept='城市规划'

14.select student.snum as 学号
from student,course,sc,section
where student.snum=sc.snum and sc.secnum=section.secnum and um=um and ame=

'数据库技术'
union
select student.snum
from student,course,sc,section
where student.snum=sc.snum and sc.secnum=section.secnum and um=um and ame='多媒体技术'

15.select *
from student
where dept='计算机'
intersect
select *
from student
where FLOOR(datediff(DY,birthday,getdate())/365.25)<=19

16.select *
from student
where dept='计算机'
intersect
select *
from student
where FLOOR(datediff(DY,birthday,getdate())/365.25)>19