SQL实验第二部分

--1．写出实现以下操作的SQL命令并执行，而后在SSMS工具中观察命令的执行结果：
  -- 1）在student表的Sdept列上建立一个按降序排序的非聚集索引，索引名为：Idx_sdept
  create index Idx_sdept
  on student (Sdept desc)
  --2)在teacher表上为Tname列建立一个聚集索引，索引名为Idxjsm
  create clustered index Idxjsm
  on teacher (Tname)
  --3)在course表上为Cname列建立一个唯一的非聚集索引，索引名为Idxkcm
  create unique nonclustered
  index Idxkcm on course (Cname)
  --4)在teacher表上为Tname和tsex列建立一个组合的非聚集唯一索引，索引名为Idxmxb
  create unique nonclustered
  index Idxmxb on teacher (Tname, tsex)
  --5)删除Sdept列上建立的Idx_sdept索引
  drop index student.Idx_sdept
--2．写出创建满足下述要求的视图的SQL语句并执行：
  -- 1）查询学生的学号、姓名、所在系、课程号、课程名、课程学分
  create view test1(sno, sname, sdept, cno, cname, credit)
  as select s.sno, sname, sdept, c.cno, cname, ccredit
  from student s join sc on s.sno = sc.sno join course c on sc.cno = c.cno
  --2）查询学生的学号、姓名，选修的课程名和考试成绩
  create view test2(sno, sname, cname, grade)
  as select s.sno, sname, cname, grade 
  from student s join sc on s.sno = sc.sno join course c on c.cno = sc.cno
  --3）统计每个学生的选课门数，要求列出学号和选课门数
  create view test3
  as select sno, COUNT(cno) 选课门数 from sc
  group by sno
  --4）统计每个学生的修课总学分，要求列出学号和总学分（说明：考试成绩>=60分才可获得此门课程的学分）
  create view test4
  as select sno, COUNT(ccredit) 总学分
  from course c join sc on c.cno = sc.cno 
  where grade >= 60
  group by sno
  --5）创建计算机系的学生视图stv2
  create view stv2
  as select sno, sname, ssex, sage from student
  where sdept='计算机系'
  --6）创建信息系选修了“数据库原理及应用”课程的学生视图stv1
  create view stv1
  as select * from student where sno in( select sno from sc 
  where cno in(select cno from course where cname = '数据库原理及应用'))
  and sdept = '信息系'
--3．利用以上所建的视图，完成如下查询：
  -- 1）查询考试成绩大于等于90分的学生的姓名、课程名和成绩
  select sname, cname, grade from test2
  where grade >= 90
  -- 2）查询选修门数超过3门的学生的学号和选课门数
  select sno, 选课门数 from test3
  where 选课门数 >= 3
  -- 3）查询计算机系选课门数超过3门的学生的姓名和选课门数
  select sname, COUNT(cno) from test1
  where sdept = '计算机系' 
  group by sname 
  having COUNT(cno) > 3
  -- 4）查询修课总学分超过10分的学生的学号、姓名、所在系和修课总学分
  select s.sno, sname, sdept, 总学分 from test4 join student s on s.sno = test4.sno
  where 总学分 > 10
  -- 5）查询在年龄大于等于20岁的学生中，修课总学分超过10分的学生的姓名、年龄、所在系和修课总学分
  select sname, sage, sdept, 总学分 from test4 join student s on test4.sno = s.sno
  where 总学分 > 10
  and sage >= 20
--4．在计算机系学生视图stv2中插入一个新的学生记录：1811110，赵新，男，20岁
insert into stv2 values('1811110', '赵新', '男', 20)
--5．利用视图stv2,将计算机系的学生年龄加1.
update stv2 set sage = sage + 1
  --（打开student表，观察上述两道题的操作效果）
--6．删除视图stv1。
drop view stv1
--7．修改2题的第4）小题所定义的视图，使其查询每个学生的学号、修课总学分及总的修课门数
alter view test4 (sno, 总学分, 总修课门数)
as select sno, COUNT(ccredit), COUNT(sc.cno)
from sc join course c on sc.cno = c.cno
group by sno
--8．查询计算机系的学生人数、姓名、年龄和平均年龄
create view V_SD
as select sdept as 系名, COUNT(*) as 人数, AVG(Sage) as 平均年龄
from student
group by sdept
select 人数, Sname as 姓名, Sage as 年龄, 平均年龄
from V_SD join student on V_SD.系名 = student.sdept
where 系名='计算机系' 
--9．利用2题的第2）小题所定义的视图，查询有缺考行为的学生的学号、姓名和缺考课程的课程名，
--（思考：可通过该视图删除SC表中的缺考记录吗？为什么？）
select sno, sname, cname from test2
where grade is null
--思考：不行