目录
创建数据库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 -
insert into student values('002102','李王','男',20,'信息') insert into student values('991101','张彬','男',18,'计算机') insert into student values('991102','王蕾','女',19,'计算机') insert into student values('991103','张建国','男',18,'计算机') insert into student values('991104','李平方','男',18,'计算机') insert into student values('991201','陈东辉','男',19,'计算机') insert into student values('991202','葛鹏','男',21,'计算机') insert into student values('991203','潘桃芝','女',19,'计算机') insert into student values('991204','姚一峰','男',18,'计算机') insert into student values('001102','许辉','女',22,'计算机') insert into student values('001201','王一山','男',20,'计算机') insert into student values('001202','牛莉','女',19,'计算机') insert into student values('002101','李丽丽','女',19,'信息')
/*insert values to teacher table*/
insert into teacher values('000006','许红霞','女',39,'讲师',1100,1200,'计算机') insert into teacher values('000007','许永军','男',57,'教授',2000,3000,'计算机') insert into teacher values('000008','李贵青','女',65,'教授',2000,3000,'计算机') insert into teacher values('000009','王一凡','女',43,'讲师',1200,1200,'计算机') insert into teacher values('000010','田峰','男',33,'助教',500,800,'信息') insert into teacher values('000001','李英','女',39,'副教授',1500,2000,'信息') insert into teacher values('000002','张雪','女',51,'教授',1900,3000,'信息') insert into teacher values('000003','张朋','男',30,'讲师',1000,1200,'计算机') insert into teacher values('000004','王平','女',28,'讲师',850,1200,'信息') insert into teacher values('000005','李力','男',47,'教授',1800,3000,'计算机')
/*insert values to course table*/
insert into course values('01001','计算机基础',60,3) insert into course values('01002','程序设计',80,5) insert into course values('02003','数据结构',60,6) insert into course values('02001','数据库',80,6) insert into course values('02002','计算机网络',60,6) insert into course values('01003','微机原理',60,8) insert into course values('02004','操作系统',60,6) insert into course values('03001','软件工程',60,3) insert into course values('03002','大型数据库',48,2) insert into course values('03003','图像处理',48,2)
/*insert values to sc table*/
insert into sc values('991101','01001',88) insert into sc values('991102','01001',93) insert into sc values('991103','01001',90) insert into sc values('991101','01002',90) insert into sc values('991102','01002',98)
- 4 -