); go
--2.职员表
create table 职员表 (
员工号 char(3) primary key, 姓名 char(8) not null, 性别 char(2),
部门号 char(2) references 部门表(部门号), 出生日期 smalldatetime , 手机号码 char(11),
工龄 tinyint constraint ck_gl check(工龄>=0 and 工龄<=35), 备注 text ); go
--3.工资表
create table 工资表 (
员工号 char(3) null references 职员表(员工号), 基本工资 decimal(7,2) , 津贴 decimal(5,2), 三金扣款 decimal(7,2),
应发工资 as 基本工资+津贴,
实发工资 as 基本工资+津贴-三金扣款 ) go
--4.工资发放表
create table 工资发放表
(发放编号 int identity(200701,2) , 发放年月 smalldatetime,
员工号 char(3) null references 职员表(员工号),
实发工资 decimal(7,2) --以后可以用函数调用dbo.fn(员工号) ) go
第7章 习题 答案
1. 查询“数据库原理”这门课程的学分和开课学期。 Select 课程号，开课学期 From kc
Where 课程名 =’ 数据库原理’
2. 查询身高在175cm以上的男生信息。 Select * From xs
Where 性别=’男’ and 身高>175
3. 查询所有姓“李”同学的基本情况。 Select * From xs
Where 姓名 like ‘李%’
4.查询至少选修了两门课程的学生学号。 Select 学号 From xs_kc Group by 学号
Having count(课程号)>=2
5. 将所有学生信息按身高逆序输出。 Select * From xs
Order by 身高 desc
6 检索平均成绩在75分以上的学生姓名、性别和专业。 方法一：
Select 姓名，性别，专业名 From xs
Where 学号 in ( Select 学号 From xs_kc
Group by 学号
Having avg(成绩)>75) 方法二：
Select xs.学号, 姓名，性别，专业名 From xs_kc , xs
Where xs.学号=xs_kc.学号
Group by xs.学号, 姓名，性别，专业名 Having avg(成绩)>75
7. 检索“李林”同学不学的课程号。 Select 课程号 From kc
Where 课程号 not in (
Select 课程号 from xs, xs_kc
Where xs.学号=xs_kc.学号 and xs.姓名=’李林’)
8. 查询只有两门课不及格的学生姓名。 Select 姓名 From xs
Where 学号 in (Select 学号 From xs_kc Where 成绩<60 Group by 学号
Having count(课程号)>=2)
9. 检索选修课程包含“程明”同学所选课程之一的学生学号。 Select 学号 From xs_kc
Where 课程号 in (
Select 课程号 from xs, xs_kc
Where xs.学号=xs_kc.学号 and xs.姓名=’ 程明’)
10. 检索同时选修了课程号为101和102这两门课程的学生学号。 方法一，使用自连接： Select a.学号
From xs_kc a , xs_kc b
Where a.学号=b.学号 and a.课程号=’101’ and b. 课程号=’102’
方法二，使用子查询： Select 学号 From xs
Where 学号 in (select 学号 From xs_kc where 课程号=’101’ ) And 学号 in (select 学号 From xs_kc where 课程号=’102’)
方法三、 Select 学号 From xs_kc
Where 课程号=’101’ or 课程号=’102’ group by学号 having count(学号)=2
11. 检索选修课程名为“计算机原理”的学生学号和姓名。 Select a.学号,姓名
From xs a , xs_kc b , kc c
Where a.学号=b.学号 and b.课程号=c. 课程号 and 课程名=’ 计算机原理’
12. 查询每门课的最高分的学生姓名。
SELECT A.学号, 姓名, B.课程号, 成绩 FROM XS A, XS_KC B WHERE A.学号=B.学号 AND 成绩=
(SELECT MAX(成绩) FROM XS_KC C WHERE B.课程号=C.课程号) 或
SELECT 姓名, B.课程号, b.成绩,d.最高成绩
FROM XS A , XS_KC B ,(SELECT 课程号, MAX(成绩) 最高成绩 FROM XS_KC
group by 课程号) as d
WHERE A.学号=B.学号 AND b.课程号= D.课程号 and b.成绩=d.最高成绩
13. 检索选修课程包含学号为061103的学生所修课程的学生学号。
SELECT 学号，姓名 FROM xs
WHERE NOT EXISTS( SELECT *
FROM kc , xs_kc a
WHERE kc.课程号=a. 课程号 and 学号=’ 061103’
and NOT EXISTS(
SELECT * FROM xs_kc b
WHERE b.学号=xs.学号 AND 课程号=kc.课程号 ) )
14. 创建一个名为avg75的视图，包含所有平均成绩在75分以上的学生信息。 Create view avg75 As Select * From xs
Where 学号 in ( Select 学号 From xs_kc
Group by 学号
Having avg(成绩)>75)
第8章习题答案
对于数据库scd，库中包含以下系、学生、班级各表：
student （学号，姓名，年龄，班号） class（班号，专业名，系名，入学年份） department （系号，系名）
请使用T-SQL语言完成以下各题。
1． 为数据库scd建立一个默认对象，使其对应于年龄为18，将其绑定到student表的
年龄列上。
Create default d_age as 18 Go
Exec sp_bindefault ‘d_age’ , ‘student.年龄’
2． 为数据库scd建立一个规则对象，并将其绑定到学生表的专业名列上，规定专业名
的取值只能为‘护理学’、 ‘地质勘探’和‘考古学’之一。 Create rule r_zym as @zym in (‘护理学’, ‘地质勘探’,’考古学’) Go
Exec sp_bindrule ‘r_zym’, ‘xs.专业名’
3． 修改class表为其建立一个CHECK约束，检查入学年份是否小于2008。
Alter table class
Add constraint ck_rxnf check (入学年份<2008 )
4． 将student表的班号与姓名这两列组合创建一个升序的非聚集索引。
Create nonclustered index ix_bj_xm on student (班号 asc , 姓名 asc)
5． 为department表的系名建立一个唯一索引，如果输入了重复的键，将忽略该INSERT
或UPDATE语句，并使用填充因子FILLFACTOR 为50%。
Create unique index uqix_xm on department (系名 asc) WITH IGNORE_DUP_KEY， FILLFACTOR = 50
第9章 答案： 三、设计题
1．使用流程控制语句编写程序：
（1）在CJGL数据库中，使用case函数处理：如果课程的学时在80以上显示学习时间长, 学时在54~80 显示学习时间一般, 否则显示学习时间短。
SELECT 课程号，课程名，学习时间= CASE
WHEN 学时>=80 THEN '学习时间长' WHEN 学时>=54 THEN '学习时间一般' ELSE ‘学习时间短’ END FROM KC
（2）在CJGL数据库中，使用case函数处理：如果学生的专业是“计算机”，显示热门，是“通信工程”显示一般，否则显示冷门。