同济大学数据库 实验三四 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 ('男','女')),
credits smallint,
check (credits>=0 and credits<=8),
descr char(40),
dept char(30),
textbook char(40)
)
insert into course
values('c116','大学英语',6,'必修课','外语系','《大学英语》，同济大学出版社'),
)
insert into sc
values('s001','11601',77),
('s001','12001',80),
('s001','12601',89),
('s001','13002',90),
('s001','13201',92),
('s001','13501',94),
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-11111111'),
('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) ,
('s002','11602',90),
('s002','12601',88),
('s002','13201',98),
('s003','11601',90),
('s003','12002',94),
('s003','12601',88),
('s004','11601',89),
('s004','13001',90),
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='计算机系'
('c120','高等数学',6,'必修课','数学系','《高等数学》，复旦大学出版社'),
('c126','大学物理',3,'必修课','物理系','《大学物理》，高等教育出版社'),
('c130','数据库技术',3,'限选课','计算机系','《数据库技术与应用》，高等教育出版社'),
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
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 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
references student,
secnum char(5) not null,
foreign key(secnum)
references section,
score int
check (score>= 0 and score<=100),
primary key (snum,secnum)
('s005','李晓静','女','生物工程','1995-6-21','030-22222222'),
('s006','金之林','女','计算机','1995-9-11','040-66666666'),
('s007','张东晓','男','城市规划','1994-8-2','050-77777777'),
('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
having count(um)=(select count(*) from course)
5.select student.snum as 学号,sname as 姓名
from student
where snum not in(select snum
('s004','13201',92),
('s004','13501',89),
('s005','11602',56),
('s006','11601',88),
('s006','12601',78),
('s007','11602',90),
('s007','13201',95),
('s007','13501',50),
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
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
where student.snum=sc.snum and sc.secnum=section.secnum and um=um and cname='大学英语'