司徒达健,,广东海洋大学电气1135,

create database Student   
on primary
 (name=student_dat,
filename='D:\Student.mdf',
size=10MB,
maxsize=100MB,
filegrowth=10%),
(name=Student1_dat,
filename='D:\ Student1_dat.mdf',
size=10MB,
maxsize=100MB,
filegrowth=10%)
log on 
(name=Student_log, 
 filename='D:\Student_log.ldf',
 size=5MB, 
 filegrowth=10MB)
 create table S 
 ( Sno  int constraint s_prim primary key,
Sname char(10) not null unique, 
Ssex char(2) check(Ssex in ('男','女')),   
Sage smallint, 
Sdept char(20)default '计算机系')
create table Course                                 
( Cno     char(4)    primary key,
Cname   char(10)   not null,
Tno int not null)
create table SC 
(
Sno     int      not null,
Cno char(4)  not null, 
Grade int null, 
primary key (Sno,Cno), 
foreign key (Sno)references S(Sno),
foreign key (Cno)references Course(Cno),
check(Grade between 0 and 100) 
)
create table Teacher
( Tno int primary key, 
Tname char(10)not null, 
Sdapt char(20)default '计算机系')
alter table Course         
add Cpno char(4) ,
Ccredit  int not null
alter table SC                                
alter column Grade smallint
alter table S 
add constraint Sagechk check(Sage between 15 and 45)
alter table Course
add constraint Fk_Tno foreign key (Tno) references Teacher(Tno)
alter table Course
drop column teacher
drop table Teacher
insert into S values( '1001', '王勇', '男', '20', '电子系')
insert into S values( '1002','刘梅','女','19','计算机系') 
insert into S values(  '1003','王芳','女','18','网络系') 
insert into S values( '1004','张兵','男','17','软件系')
insert into SC values('1001','1','92')
insert into SC values('1001','2','85')
insert into SC values('1001','3','88')
insert into SC values('1002','2','90')
insert into SC values('1003','2','55') 
insert into SC values('1004','2','70')

sp_addtype char20,'varchar(20)','null'

create database SSSstudent
create table student
(
sno int primary key,
sname char(10),
ssex char(2) constraint ssex_ch check(ssex in('男','女')),
sage int constraint sage_ch check(sage>=15 and sage<=30),
sdept char(10)
)
insert into student
values(95001,'李敏勇','男',20,'CS')
insert into student
values(95002,'刘晨','女',19,'IS')
insert into student
values(95003,'王敏','女',18,'MA')
insert into student
values (95004,'张立','男',18,'IS')
create table course100(cno int primary key,
cname char (15),
cpno int null,
credit int,
teacher char20
)
insert into course100
values(1,'数据库',5,4,'王芳')
insert into course100
values(2,'数学',null,2,'刘新')
insert into course100
values(3,'信息系统',1,4,'刘新')
insert into course100
values(4,'操作系统',6,3,'高升')
insert into course100
values(5,'数据结构',7,4,'宋明')
insert into course100
values(6,'数据处理',null,2,'张彬')
insert into course100
values(7,'PASCAL语言',6,4,'李磊')
create table sc666(sno int,
cno int,
grade int constraint grade_ch check(grade between 0 and 100),
primary key(sno,cno),
constraint fk_sno foreign key (sno) references
student(sno),
constraint fk_cno foreign key (cno) references
course100(cno),
)
insert into sc666
values(95001,1,92)
insert into sc666
values(95001,2,85)
insert into sc666
values(95001,3,88)
insert into sc666
values(95002,2,90)
insert into sc666
values(95003,2,55)
 insert into sc666
values(95004,2,70)
insert into student
values(95030,'谢非','男',22,'CS')
update sc666
set grade=98
where sc666.cno=(select course100.cno
from course100
where course100.cname='数据库'
and sno=(select sno
from student
where sname='李敏勇')
)
delete
from student
where sname='谢非'

电气