《SQL - Server - 2000 - 实验指导》课后作业

目录

创建数据库JXSK： ......................................................................................................................... - 1 - 创建JXSK中的表： ........................................................................................................................ - 1 - 插入JXSK库中各表值： ................................................................................................................ - 1 - 创建数据库JIAOXUEDB： ................................................................................................................ - 3 - 创建JIAOXUEDB中的表： ............................................................................................................... - 3 - 插入JIAOXUEDB库中各表值： ....................................................................................................... - 3 - 实验4：习题 .................................................................................................................................. - 5 - 实验5：习题 .................................................................................................................................. - 7 - 实验6：习题 .................................................................................................................................. - 7 - 实验7：习题 .................................................................................................................................. - 8 - 实验8：习题 ................................................................................................................................ - 12 - 实验9：习题 ................................................................................................................................ - 17 - 实验10：习题 .............................................................................................................................. - 21 - 实验11：习题 .............................................................................................................................. - 24 - 实验12：习题 .............................................................................................................................. - 26 - 实验13：习题 .............................................................................................................................. - 39 -

创建数据库jxsk： create database jxsk on (name=jxsk_dat,filename='d:\\jxsk_dat.mdf') log on (name=jxsk_log,filename='d:\\jxsk_log.ldf') go

创建jxsk中的表： use jxsk

create table s(

插入jxsk库中各表值： use jxsk insert into s values('s2','钱尔','男',18,'信息') insert into s values('s3','孙姗','女',20,'信息') sno char(2) not null, sn char(8) not null, sex char(2) null, age tinyint null, dept char(10) null) tno char(2) not null, tn char(8) not null, sex char(2) null, age tinyint null, prof char(10) null, sal smallint null, comm smallint null, dept char(10) null) cno char(2) not null, cn char(10) not null, ct tinyint null, id_tc tinyint null) sno char(2) not null, cno char(2) not null, score tinyint null) tno char(2) not null, cno char(2) not null)

create table t(

create table c(

create table sc(

create table tc(

- 1 -

insert into s values('s4','李思','男',21,'自动化') insert into s values('s5','周武','男',19,'计算机') insert into s values('s6','吴丽','女',20,'自动化') insert into s values('s7','李奇','男',18,'外语') insert into s values('s8','古明','男',null,'计算机') insert into s values('s9','赵亦','女',17,'计算机')

insert into t values('t5','张兰','女',39,'副教授',1300,2000,'信息') insert into t values('t4','张雪','女',51,'教授',1600,3000,'自动化') insert into t values('t3','刘伟','男',30,'讲师',900,1200,'计算机') insert into t values('t2','王平','女',28,'讲师',800,1200,'信息') insert into t values('t1','李力','男',47,'教授',1500,3000,'计算机')

insert into c values('c1','程序设计',60,1) insert into c values('c2','微机原理',80,2) insert into c values('c3','数字逻辑',60,3) insert into c values('c4','数据结构',80,4) insert into c values('c5','数据库',60,5) insert into c values('c6','编译原理',60,6) insert into c values('c7','操作系统',60,7)

insert into sc values('s2','c5',57) insert into sc values('s2','c6',80) insert into sc values('s2','c7',null) insert into sc values('s3','c1',75) insert into sc values('s3','c2',70) insert into sc values('s3','c4',85) insert into sc values('s4','c1',93) insert into sc values('s4','c2',85) insert into sc values('s4','c3',83) insert into sc values('s5','c2',89) insert into sc values('s9','c1',90) insert into sc values('s9','c2',85)

insert into tc values('t2','c5') insert into tc values('t3','c1') insert into tc values('t3','c5') insert into tc values('t4','c2') insert into tc values('t4','c3') insert into tc values('t5','c5') insert into tc values('t5','c7') insert into tc values('t1','c1') insert into tc values('t1','c4')

- 2 -

创建数据库jiaoxuedb： create database jiaoxuedb on (name=jiaoxuedb_dat,filename='d:\\jiaoxuedb_dat.mdf') log on (name=jiaoxuedb_log,filename='d:\\jiaoxuedb_log.ldf') go

创建jiaoxuedb中的表： use jiaoxuedb

create table student(

插入jiaoxuedb库中各表值： use jiaoxuedb /*insert values to student table*/ insert into student values('001101','宋大方','男',19,'计算机') sno char(10) not null, sname char(10) not null, sex char(2) null, age tinyint null, dept char(10) null) tno char(10) not null, tname char(10) not null, sex char(2) null, age tinyint null, prof char(10) null, sal smallint null, comm smallint null, dept char(10) null) cno char(10) not null, cname char(10) not null, chour tinyint null, ccredit tinyint null) sno char(10) not null, cno char(10) not null, score tinyint null) tno char(10) not null, cno char(10) not null)

create table teacher(

create table course(

create table sc(

create table tc(

- 3 -