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