33
--（一）创建教材学生-课程数据库
create database s_c
go
use s_c
go
--建立“学生”表Student,学号是主码,姓名取值唯一。
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY, /* 列级完整性约束条件*/
Sname CHAR(20) UNIQUE, /* Sname取唯一值*/
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20) )
go
--建立一个“课程”表Course
CREATE TABLE Course
( Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40),
Cpno CHAR(4) ,
Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES Course(Cno)
)
go
--建立一个“学生选课”表SC
CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (Sno,Cno), /* 主码由两个属性构成,必须作为表级完整性进行定义*/
FOREIGN KEY (Sno) REFERENCES Student(Sno), /* 表级完整性约束条件,Sno是外码,被参照表是Student */
FOREIGN KEY (Cno) REFERENCES Course(Cno) /* 表级完整性约束条件, Cno是外码,被参照表是Course*/ )
--（二）装载数据
--1、往学生表（student）插入数据
insert into student
values('200215121','李勇','男',20,'CS')
GO
insert into student values('200215122','刘晨','女',19,'CS')
GO
insert into student values('200215123','王敏','女',18,'MA')
GO
insert into student values('200215125','张立','男',19,'IS')
go
--2、往course表插入数据
insert into course values('1','数据库',null,4)
go
insert into course values('2','数学',null,2)
go
insert into course
values('3','信息系统',null,4)
go
insert into course values('4','操作系统',null,3)
go
insert into course values('5','数据结构',null,4)
go
insert into course values('6','数据处理',null,2)
go
insert into course values('7','PASCAL语言',null,4)
go
update course set cpno='5' where cno='1'
go
update course
set cpno='1'
where cno='3'
go
update course
set cpno='6'
where cno='4'
go
update course
set cpno='7'
where cno='5'
go
update course
set cpno='6'
where cno='7'
go
select * from course
go
--3、往sc表插入数据
insert into sc
values('200215121','1',92)
go
insert into sc
values('200215121','2',85)
go
insert into sc values('200215121','3',88)
go
insert into sc values('200215122','2',90)
go
insert into sc values('200215122','3',80)
go
select * from sc
go
delete from sc
go
delete from course
go
delete from student
go
insert into sc values('200215121','c123',null)
go
insert into course values('c123',null,null,null)
go
create table Dept_age
( sdept char(15),
Avg_age smallint )
go
insert into Dept_age(sdept,Avg_age)
go
select sdept,AVG(sage) from student group by sdept
go
update sc set grade=0
where sno in ( selsct sno from student where sdept='cs')
go
delete from sc
where sno in ( selsct sno from student where sdept='cs')
go
delete from sc where sno='s1'
go
delete from student where sno='s1'
go
