sql 作业答案.doc

现有学生表（学号，姓名，性别，年龄，入学年份，籍贯，手机号码，系号，班长学号），学号是主码，系号和班长学号是外部码，手机号码必须唯一，学生的年龄不得小于10岁和大于50岁，性别必须是'男'或者'女'。
系表（系号，系名，系主任），其中系号是主码，系名不能有重复的。
选课表（学号，课程号，成绩），（学号，课程号）是主码，学号和课程号是外部码，成绩不能小于0分和大于100分
课程表（课程号，课程名，先修课，学分），课程号是主码，课程名必须唯一，学分必须大于0小于5。
学分计算表（最低成绩，最高成绩，计算比率）。最低成绩和最高成绩都必须大于0小于100，同时最低成绩不能大于最高成绩。
试完成以下工作：

create table 系表
(系号char(4) PRIMARY KEY,
系名varchar(50) UNIQUE,
系主任varchar(10))

create table 学生表
(学号 char(10) PRIMARY KEY,
姓名 nvarchar(10),
性别 char(2) CHECK(性别 IN ('男','女')) ,
年龄 int CHECK(年龄 between 10 and 50 ) ,
入学年份 datetime,
籍贯 nvarchar(100),
手机号码 varchar(20) UNIQUE,
系号 char(4),
班长学号 char(10),
FOREIGN KEY(班长学号)REFERENCES 学生表(学号),
FOREIGN KEY(系号)REFERENCES 系表(系号)
)

create table 课程表
(课程号 char(10) PRIMARY KEY,
课程名 nvarchar(100) UNIQUE,
先修课 char(10) FOREIGN KEY REFERENCES 课程表(课程号),
学分 int CONSTRAINT CK_课程表_学分 CHECK(学分 between 0 and 5)
)

create table 选课表
(学号 char(10),
课程号 char(10),
成绩 int CHECK(成绩 between 0 and 100 ) default 0,
PRIMARY KEY(学号,课程号),
FOREIGN KEY(学号)REFERENCES 学生表(学号),
FOREIGN KEY(课程号)REFERENCES 课程表(课程号))

create table 学分计算表
(最低成绩 real CHECK(最低成绩 between 0 and 100),
最高成绩 real CHECK(最高成绩 between 0 and 100),
计算比率 real ,
CHECK(最高成绩>= 最低成绩)
)

2. 在学生表中插入学号为26，姓名为'李四'，性别为'女'，年龄为20，入学年份为2008，籍贯为'广东'，手机号码为10010001000，班长学号为10 的一条记录。
insert into 学生表
values('26','李四','女',20,'2008','广东','10010001000', NULL,'10')
或者
insert into 学生表（学号，姓名，性别，年龄，入学年份，籍贯，手机号码，班长学号）
values('26','李四','女',20,'2008','广东','10010001000', '10')

3. 删除上述记录
delete from 学生表
where 学号='26'

4. 将学生表中的姓名字段的长度改为6个汉字
alter table 学生表ALTER COLUMN 姓名 nvarchar(6)

5. 为学生表增加一个字段电子邮件，20个字符。
ALTER TABLE 学生表
ADD 电子邮件 varchar(200) NULL

6. 对课程表的学分字段上的完整性约束进行修改，使其在0到6之间取值。
ALTER TABLE 课程表
DROP CONSTRAINT CK_课程表_学分

ALTER TABLE 课程表
ADD
CONSTRAINT CK_课程表_学分 CHECK(学分 between 0 and 6)

7. 为学生表在学号列上创建cluster索引。
CREATE CLUSTERED
INDEX IX_学号ON 学生表(学号)

8. 创建一个视图，计算每门课的最高分。
CREATE VIEW v_每门课的最高分(课程号,每门课的最高分)
AS
SELECT 课程号,MAX(成绩)
FROM 选课表
group by 课程号
或者
CREATE VIEW v_每门课的最高分
AS
SELECT 课程号,MAX(成绩) as 每门课的最高分
FROM 选课表
group by 课程号

9. 将6系所有学生的年龄，改为7系学生的平均年龄。
update 学生表
set 年龄=(select avg(年龄)
from 学生表
where 系号='7系')
where 系号='6系'

10. 查找所有学生的姓名、入学年份和籍贯。
select 姓名,入学年份,籍贯
from 学生表

11. 列出籍贯为'山东'的同学的所有属性。
select *
from 学生表
where 籍贯 like '%山东%'

12. 查找年龄最小的学生的学号和姓名。
select 学号,姓名
from 学生表
where 年龄=(select min(年龄)
from 学生表)

13. 查找选修了'数据库'的学生的学号。
select 学号
from 选课表
where 课程号 = (select 课程号
from 课程表
where 课程名='数据库')
或者
select 学号
from 选课表
join 课程表
on 选课表.课程号= 课程表.课程号
where 课程名='数据库'

14. 查找选修了'编译技术'的女学生的学号和姓名。
select 学号,姓名
from 学生表
where 性别='女'and 学号in (select 学号
from 选课表
where 课程号in (select 课程号
from 课程表
where 课程名='编译技术'))

或者
select 学生表.学号,姓名
from 学生表
join 选课表
on 学生表.学号= 选课表.学号
join 课程表
on 选课表.课程号= 课程表.课程号
where 课程名='编译技术' and 性别='女'

15. 查找'李明'同学的班长所选修的课程的课程号。
select 课程号
from 选课表
where 学号 in (select 班长学号
from 学生表
where 姓名='李明')
或者
select 课程号
from 选课表
join 学生表
on 学生表.班长学号 = 选课表.学号
where 姓名='李明'

16. 查找名字中倒数第二字为'浩'的学生的学号、姓名和所在系的系名。
select 学号,姓名,系号
from 学生表
where 姓名 like '%浩_'

17. 计算选修了'数据库'课程的学生的学号和获得的学分。
select 学号,学分*计算比率 as 所获学分
from 选课表
join 课程表
on 选课表.课程号 = 课程表.课程号
join 学分计算表
on 选课表.成绩 between 学分计算表.最低成绩 and 学分计算表.最高成绩
where 课程名='数据库'

18. 查找'李明'同学所有选修课程的总分。
select sum(成绩) as 总分
from 选课表
join 学生表
on 选课表.学号=学生表.学号
where 姓名='李明'
或者
select sum(成绩) as 总分
from 选课表
where 学号 in (select 学号
from 学生表
where 姓名='李明')

19. 查找既选修了'数据库'，也选修了'操作系统'的同学。
select distinct 学号
from 选课表
where 学号in (select 学号
from 选课表
join 课程表
on 选课表.课程号=课程表.课程号
where 课程名='数据库')
and 学号in (select 学号
from 选课表
join 课程表
on 选课表.课程号=课程表.课程号
where 课程名='编译技术')
或者
select a.学号
from 选课表 a
join 选课表 b
on a.学号=b.学号
join 课程表 c
on a.课程号=c.课程号
join 课程表 d
on b.课程号=d.课程号
where c.课程名='数据库' and d.课程名='编译技术'

20. 查找没有选修'数据库'课程的学生的学号和姓名。
select 学号,姓名
from 学生表
where 学号 not in (select 学号
from 选课表
where 课程号in (select 课程号
from 课程表
where 课程名='数据库'))

21. 查找'数据库'课程及格了，但'编译技术'没有及格的学生的学号和姓名。
select 学号,姓名
from 学生表
where 学号 in (select 学号
from 选课表
where 课程号 in (select 课程号
from 课程表
where 课程名='数据库')
and 成绩>=60)
and
学号 in (select 学号
from 选课表
where 课程号 in (select 课程号
from 课程表
where 课程名='编译技术')
and 成绩<60)
或者
select 学生表.学号, 姓名
from 选课表 a
join 选课表 b
on a.学号=b.学号
join 课程表 c
on a.课程号=c.课程号
join 课程表 d
on b.课程号=d.课程号
join 学生表
on a.学号=学生表.学号
where c.课程名='数据库' and d.课程名='编译技术'
and a.成绩>=60 and b.成绩<60

22. 查找数据库成绩低于数据库课平均成绩的同学的学号和姓名。
select 学号,姓名
from 学生表
where 学号 in (select 学号
from 选课表
join 课程表
on 选课表.课程号=课程表.课程号
where 课程名='数据库'
and 成绩<(select avg(成绩)
from 选课表
join 课程表
on 选课表.课程号=课程表.课程号
where 课程名='数据库'))

23. 查找与'李明'同学选修课程完全相同的学生的学号和姓名（不能多选也不能少选）。
select 学号,姓名
from 学生表 a
where not exists (select *
from 选课表 c
join 学生表 b
on b.学号=c.学号
where 姓名='李明'
and not exists(select *
from 选课表 d
where d.学号= a.学号
and d.课程号= c.课程号))
and 学号 not in (select 学号
from 选课表
where 课程号 not in (select 课程号
from 选课表
where 学号 in (select 学号
from 学生表
where 姓名='李明')))

或者
select 学号,姓名
from 学生表
where not exists (select *
from 课程表
where 课程号in (select 课程号
from 选课表
where 学号=(select 学号
from 学生表
where 姓名='李明'))
and not exists (select *
from 选课表
where 学号=学生表.学号and 课程号=课程表.课程号))
and (select count(*)
from 选课表
where 学号=学生表.学号)= (select count(*)
from 选课表
where 学号= (select 学号from 学生表where 姓名='李明'))

24.查找不仅选修了'李明'同学选修的课程，而且还选修了其他课程的同学。
select 学号,姓名
from 学生表 a
where not exists (select *
from 选课表 c
join 学生表 b
on b.学号=c.学号
where 姓名='李明'
and not exists(select *
from 选课表 d
where d.学号= a.学号
and d.课程号= c.课程号))
and 学号 in (select 学号
from 选课表
where 课程号 not in (select 课程号
from 选课表
where 学号 in (select 学号
from 学生表
where 姓名='李明')))

select 学号,姓名
from 学生表
where not exists (select *
from 课程表
where 课程号in (select 课程号
from 选课表
where 学号=(select 学号
from 学生表
where 姓名='李明'))
and not exists (select *
from 选课表
where 学号=学生表.学号and 课程号=课程表.课程号))
and (select count(*)
from 选课表
where 学号=学生表.学号)> (select count(*)
from 选课表
where 学号= (select 学号from 学生表where 姓名='李明'))

25. 查找'高等数学'平均成绩最高的系的系名。
select 系名, avg(成绩)
from 选课表
join 学生表
on 学生表.学号=选课表.学号
join 课程表
on 选课表.课程号=课程表.课程号
join 系表
on 系表.系号=学生表.系号
where 课程名='数据库'
group by 系表.系名
having avg(成绩)>= all (select avg(成绩)
from 选课表
join 学生表
on 学生表.学号=选课表.学号
join 课程表
on 选课表.课程号=课程表.课程号
join 系表
on 系表.系号=学生表.系号
where 课程名='数据库'
group by 系表.系名)
或者
select top 1 *
from (select 系名, avg(成绩) as 平均成绩
from 选课表
join 学生表
on 学生表.学号=选课表.学号
join 课程表
on 选课表.课程号=课程表.课程号
join 系表
on 系表.系号=学生表.系号
where 课程名='数据库'
group by 系名) a

26. 查找至少有一个籍贯为'四川'同学所选修的课程的课程名。
select 课程名
from 课程表
where 课程号 in (select 课程号
from 选课表
where 学号 in (select 学号
from 学生表
where 籍贯 like '%四川%'))

或者
select distinct 课程名
from 课程表
join 选课表
on 课程表.课程号=选课表.课程号
join 学生表
on 学生表.学号=选课表.学号
where 籍贯like '%四川%'

27. 删除学分计算表。
drop table 学分计算表

select 学号,姓名
from 学生表
where 学号in (select 学号
from 选课表
where 课程号in (select 课程号
from 选课表
where 学号in (select 学号 --考虑重名，用IN
from 学生表
where 姓名='李明')))
and
学号not in (select 学号
from 选课表
where 课程号not in (select 课程号
from 选课表
where 学号in (select 学号
from 学生表
where 姓名='李明')))