实例(stu考试).txt

经典的sql资料

-- 综合题：

-- 1. 创建一个数据库 student


-- 2. 创建表 stu_info(t_number char(8) 主键,t_name char(10),t_gender char(2),t_birthday datetime)
   -- 创建表 course(c_number char(6) 主键,c_name char(20),c_credit int,c_hour int,c_teacher char(10))
   -- 创建表 emam(t_number char(8),c_number char(6),t_grade decimal(5,2))


-- 3.为表设置约束
  -- 把exam表的t_number字段设为外键，参照stu_info表的t_number字段,约束名为fk_t 
  -- 把exam表的c_number字段设为外键，参照course表的c_number字段,约束名为fk_c 


-- 4. 插入数据

--向stu_info表插入数据
insert into stu_info values('20040301','张华','女','19840113')
insert into stu_info values('20040302','王立','男','19830624')
insert into stu_info values('20040303','蒋超','男','19841115')
insert into stu_info values('20040304','王浩雨','男','19851020')
insert into stu_info values('20040305','张静','女','19840418')
insert into stu_info values('20050301','李华','女','19830113')
insert into stu_info values('20050302','张立','男','19840624')
insert into stu_info values('20050303','黄超','男','19851125')
insert into stu_info values('20050304','汪雨','男','19861020')
insert into stu_info values('20050305','王静','女','19850418')

--向course表插入数据
insert into course values('100101','高等数学',2,60,'赵金')
insert into course values('100102','大学英语',3,80,'王维')
insert into course values('100103','大学物理',2,60,'李华')
insert into course values('100104','大学英语',4,80,'刘杰')


--向exam表插入数据
insert into exam values('20040301','100101',79)
insert into exam values('20040301','100102',88)
insert into exam values('20040302','100101',90)
insert into exam values('20040302','100103',75)
insert into exam values('20040303','100101',79)
insert into exam values('20040303','100102',75)
insert into exam values('20040303','100103',95)
insert into exam values('20040304','100102',43)
insert into exam values('20040304','100103',68)
insert into exam values('20040305','100101',64)
insert into exam values('20040305','100102',87)
insert into exam values('20040305','100103',92)

-- 5. 向course表插入数据'100105','大学英语',NULL,80,'刘杰';'100106','大学语文',8,80,'钱影'


-- 6. 把刚插入的老师姓名是'刘杰'的记录修改姓名为'周义'


-- 7. 删除'100106'号课程


-- 8. 查询 学号为20040301的考试记录


-- 9. 查询 学号为20040301的考试平均成绩记录


-- 10. 查询 课程号为100101的考试记录


-- 11. 查询 课程号为100101的考试平均成绩记录


-- 12. 显示每位学生(显示学号即可)考试的平均分，并按平均分降序排序


-- 13. 显示每科课程(显示课程号即可)考试的平均分，并按平均分降序排序


-- 14. 用COMPUTE计算课程100101考试的平均分


-- 15. 查询显示学生姓名、课程号、考试成绩


-- 16. 查询显示学生号、课程名称、考试成绩


-- 17. 查询显示学生姓名、课程名称、考试成绩


-- 18. 显示每位学生(显示姓名)考试的平均分，并按平均分降序排序


-- 19. 显示每科课程(显示课程号)考试的平均分、最高分，并按平均分降序排序


-- 20. 查询所有学生平均分数大于80的记录


-- 21. 查询所有课程平均分数大于80的记录


-- 22. 使用子查询，查询test数据库中学习了'高等数学'的学生编号、学生姓名


-- 23. 使用标准SQL嵌套语句，查询test数据库中不学习课程编号为'100101'的学生编号、学生姓名


-- 24. 查询test数据库中,选修了课程超过2门的学生编号、学生姓名


-- 25. 查询 姓名为张静的考试记录（使用多表查询）


-- 26. 查询 姓名为张静的平均成绩（使用多表查询）


-- 27. 删除学生张静的考试记录(使用子查询)


-- 28. 创建一个视图v_c1，查询每门课程的平均成绩，显示字段课程名称、平均分,并查看


-- 29. 创建一个视图v_c2，查询每门课程的平均成绩大于80分的记录，显示字段课程名称、平均分,并查看


-- 30. 创建一个复合索引in_c，在course表的c_name,c_teacher字段上


-- 31. 创建一个存储过程p_g1，查询显示每门课程平均分,按平均分降序排序（不带参数的存储过程）


-- 32. 创建一个存储过程p_g2，查询显示某指定课程平均分（带参数的存储过程）


-- 33. 创建一个触发器t_g，当插入或修改数据的考试分数>100，拒绝插入，并验证




-- 综合题参考答案：

-- 1. 创建一个数据库 student
create database student

-- 2. 创建表 stu_info(t_number char(8) 主键,t_name char(10),t_gender char(2),t_birthday datetime)
   -- 创建表 course(c_number char(6) 主键,c_name char(20),c_credit int,c_hour int,c_teacher char(10))
   -- 创建表 emam(t_number char(8),c_number char(6),t_grade decimal(5,2))

create table stu_info
(
t_number char(8) PRIMARY KEY,
t_name char(10),
t_gender char(2),
t_birthday datetime
)

create table course
(
c_number char(6) PRIMARY KEY,
c_name char(20),
c_credit int,
c_hour int,
c_teacher char(10)
)


create table exam
(
t_number char(8),
c_number char(6),
t_grade decimal(5,2)
)


-- 3.为表设置约束
  -- 把exam表的t_number字段设为外键，参照stu_info表的t_number字段,约束名为fk_t 
  -- 把exam表的c_number字段设为外键，参照course表的c_number字段,约束名为fk_c 
alter table exam add constraint fk_t foreign key (t_number) references stu_info(t_number)
alter table exam add constraint fk_c foreign key (c_number) references course(c_number)


-- 4. 插入数据

--向stu_info表插入数据
insert into stu_info values('20040301','张华','女','19840113')
insert into stu_info values('20040302','王立','男','19830624')
insert into stu_info values('20040303','蒋超','男','19841115')
insert into stu_info values('20040304','王浩雨','男','19851020')
insert into stu_info values('20040305','张静','女','19840418')
insert into stu_info values('20050301','李华','女','19830113')
insert into stu_info values('20050302','张立','男','19840624')
insert into stu_info values('20050303','黄超','男','19851125')
insert into stu_info values('20050304','汪雨','男','19861020')
insert into stu_info values('20050305','王静','女','19850418')

--向course表插入数据
insert into course values('100101','高等数学',2,60,'赵金')
insert into course values('100102','大学英语',3,80,'王维')
insert into course values('100103','大学物理',2,60,'李华')
insert into course values('100104','大学英语',4,80,'刘杰')


--向exam表插入数据
insert into exam values('20040301','100101',79)
insert into exam values('20040301','100102',88)
insert into exam values('20040302','100101',90)
insert into exam values('20040302','100103',75)
insert into exam values('20040303','100101',79)
insert into exam values('20040303','100102',75)
insert into exam values('20040303','100103',95)
insert into exam values('20040304','100102',43)
insert into exam values('20040304','100103',68)
insert into exam values('20040305','100101',64)
insert into exam values('20040305','100102',87)
insert into exam values('20040305','100103',92)

-- 5. 向course表插入数据'100105','大学英语',NULL,80,'刘杰';'100106','大学语文',8,80,'钱影'
insert into course values('100105','大学英语',NULL,80,'刘杰')
insert into course values('100106','大学语文',8,80,'钱影')

-- 6. 把刚插入的老师姓名是'刘杰'的记录修改姓名为'周义'
update course set c_teacher='周义' where c_number='100105'

-- 7. 删除'100106'号课程
delete from course where c_number='100106'

-- 8. 查询 学号为20040301的考试记录
select * from exam where t_number='20040301'

-- 9. 查询 学号为20040301的考试平均成绩记录
select avg(t_grade) from exam where t_number='20040301'

-- 10. 查询 课程号为100101的考试记录
select * from exam where c_number='100101'

-- 11. 查询 课程号为100101的考试平均成绩记录
select avg(t_grade) from exam where c_number='100101'

-- 12. 显示每位学生(显示学号即可)考试的平均分，并按平均分降序排序
select t_number,avg(t_grade) from exam group by t_number order by avg(t_grade) desc

-- 13. 显示每科课程(显示课程号即可)考试的平均分，并按平均分降序排序
select c_number,avg(t_grade) from exam group by c_number order by c_number

-- 14. 用COMPUTE计算课程100101考试的平均分
select * from exam where c_number='100101' compute avg(t_grade) 

-- 15. 查询显示学生姓名、课程号、考试成绩
select s.t_name,e.c_number,e.t_grade from stu_info as s,exam as e where s.t_number=e.t_number 

-- 16. 查询显示学生号、课程名称、考试成绩
select e.t_number,c.c_name,e.t_grade from course as c,exam as e where  c.c_number=e.c_number

-- 17. 查询显示学生姓名、课程名称、考试成绩
select s.t_name,c.c_name,e.t_grade from stu_info as s,course as c,exam as e 
where s.t_number=e.t_number and c.c_number=e.c_number

-- 18. 显示每位学生(显示姓名)考试的平均分，并按平均分降序排序
select s.t_name,avg(t_grade) from exam as e,stu_info as s where s.t_number=e.t_number 
group by s.t_name order by s.t_name

-- 19. 显示每科课程(显示课程号)考试的平均分、最高分，并按平均分降序排序
select c.c_name,avg(t_grade) as 平均分,max(t_grade) as 最高分,min(t_grade) as 最低分 from exam as e,course as c where c.c_number=e.c_number group by c.c_name order by avg(t_grade) desc

-- 20. 查询所有学生平均分数大于80的记录
select t_number,avg(t_grade) from exam group by t_number having avg(t_grade)>80

-- 21. 查询所有课程平均分数大于80的记录
select c_number,avg(t_grade) from exam group by c_number having avg(t_grade)>80

-- 22. 使用子查询，查询test数据库中学习了'高等数学'的学生编号、学生姓名
select t_number,t_name from stu_info  where t_number in 
(select t_number from course as c,exam as e where c.c_number=c.c_number and c.c_name='高等数学')

-- 23. 使用标准SQL嵌套语句，查询test数据库中不学习课程编号为'100101'的学生编号、学生姓名
select t_number,t_name from stu_info  where t_number not in 
(select t_number from exam as e where t_number=e.t_number and e.c_number='100101')

-- 24. 查询test数据库中,选修了课程超过2门的学生编号、学生姓名
select t_number,t_name from stu_info where t_number in( select t_number from exam group by t_number having count(distinct c_number)>2)

-- 25. 查询 姓名为张静的考试记录（使用多表查询）
select s.t_number,s.t_name,e.t_grade from exam as e,stu_info as s where e.t_number=s.t_number and s.t_name='张静'

-- 26. 查询 姓名为张静的平均成绩（使用多表查询）
select avg(e.t_grade) as '平均分' from exam as e,stu_info as s where e.t_number=s.t_number and s.t_name='张静' 

-- 27. 删除学生张静的考试记录(使用子查询)
delete from exam where t_number=(select t_number from stu_info where t_name='张静')

-- 28. 创建一个视图v_c1，查询每门课程的平均成绩，显示字段课程名称、平均分,并查看
create view v_c1 as select c.c_name,avg(e.t_grade) as '平均分' from exam as e,course as c 
where e.c_number=c.c_number group by c.c_name
select * from v_c1

-- 29. 创建一个视图v_c2，查询每门课程的平均成绩大于80分的记录，显示字段课程名称、平均分,并查看
create view v_c2 as select c.c_name,avg(e.t_grade) as '平均分' from exam as e,course as c 
where e.c_number=c.c_number group by c.c_name having avg(e.t_grade)>80
select * from v_c2

-- 30. 创建一个复合索引in_c，在course表的c_name,c_teacher字段上
create index in_c on course(c_name,c_teacher)

-- 31. 创建一个存储过程p_g1，查询显示每门课程平均分,按平均分降序排序（不带参数的存储过程）
create procedure p_t_g1 as select c_number,avg(t_grade) as '平均分' from examgroup by c_number order by avg(t_grade) desc
p_t_g1

-- 32. 创建一个存储过程p_g2，查询显示某指定课程平均分（带参数的存储过程）
create procedure p_t_g2 @c_num int as select c_number,avg(t_grade) as '平均分' from exam where c_number=@c_num group by c_number 
p_t_g2 100101

-- 33. 创建一个触发器t_g，当插入或修改数据的考试分数>100，拒绝插入，并验证
create trigger t_e on exam for insert,update as
if update(t_grade) 
begin
  if(select t_grade from inserted)>100
    begin
    print '分数不能大于 100'
    rollback transaction
    end
end
insert into exam values('20040301','100104',120)  -- 提示出错信息，拒绝插入
insert into exam values('20050301','100104',90)  -- 正常插入