SQL SERVER实验指导-课后作业

分享自己在做这本书的课后习题时的答案，欢迎指错！

1、创建数据库jxsk：

create database jxsk
on
(name=jxsk_dat,filename='d:\jxsk_dat.mdf')
log on
(name=jxsk_log,filename='d:\jxsk_log.ldf')
go

2、创建jxsk中的表：

use jxsk
create table s(
    sno char(2) not null,
    sn char(8) not null,
    sex char(2) null,
    age tinyint null,
    dept char(10) null)
create table t(
    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)
create table c(
    cno char(2) not null,
    cn char(10) not null,
    ct tinyint null,
    id_tc tinyint null)
create table sc(
    sno char(2) not null,
    cno char(2) not null,
    score tinyint null)
create table tc(
    tno char(2) not null,
    cno char(2) not null)

3、插入jxsk库中各表值：

se jxsk

insert into s values('s2','钱尔','男',18,'信息')
insert into s values('s3','孙姗','女',20,'信息')
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')