数据库练习题6--数据库复杂查询+索引

一、实验目的
1.掌握SELECT语句的基本语法和查询条件表示方法；
2.掌握查询条件种类和表示方法；
3.掌握连接查询的表示及使用；
4.掌握嵌套查询的表示及使用；
5.了解集合查询的表示及使用。
6.掌握创建管理索引的使用方法；

(1)查询以‘DB_’开头,且倒数第3个字符为‘s’的课程的详细情况；

select * from course
where cname like 'DB\_%s_'

(2)查询名字中第2个字为‘阳’的学生姓名和学号及选修的课程号、课程名；

select sname,student.sno,sc.cno,cname
from student,sc,course
where sname like '_阳%' 
and student.sno=sc.sno
and sc.cno=course.cno

(3)列出选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩；

select student.sno,sname,sdept,sc.cno,grade
from student,sc,course
where student.sno=sc.sno 
and sc.cno=course.cno
and cname in ('数学','大学英语')

(4)查询缺少成绩的所有学生的详细情况；

select * from student,sc,course 
where student.sno=sc.sno 
and sc.cno=course.cno 
and grade is null

(5)查询与‘张力’(假设姓名唯一)年龄不同的所有学生的信息；

select * from student
where sage<>(
select sage from student 
where sname='张力'
)

(6)查询所选课程的平均成绩大于张力的平均成绩的学生学号、姓名及平均成绩；

select student.sno,sname,平均成绩=AVG(grade)
from student,sc
where student.sno=sc.sno
group by student.sno,sname 
having AVG(grade)>(
	select AVG(grade)
	from student,sc
	where student.sname='张力' and student.sno=sc.sno
	group by student.sno,sname
)

(7)按照“学号，姓名，所在院系，已修学分”的顺序列出学生学分的获得情况。其中已修学分为考试已经及格的课程学分之和；

select student.sno,sname,sdept,已修学分=SUM(ccredit)
from student,course,sc
where student.sno=sc.sno and sc.cno=course.cno
group by student.sno,sname,sdept 

(8)列出只选修一门课程的学生的学号、姓名、院系及成绩；

select student.sno,sname,sdept,grade
from sc,student
where student.sno=sc.sno and student.sno in (
select student.sno
from sc,student
where student.sno=sc.sno
group by student.sno
having COUNT (sc.cno)=1) 

(9)查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号；

select student.sno,sname,cno
from student,sc
where student.sno=sc.sno and cno in (
select cno
from sc,student
where student.sno=sc.sno and student.sname='张力'
)

(10)只选修“数据库”和“数据结构”两门课程的学生的基本信息；

select student.sno,sname,Ssex,Sage,Sdept
from student,sc,course
where student.sno=sc.sno and
sc.cno=course.cno and
sc.sno in(select sc.sno from sc,course
where (cname='数据库'or cname='数据结构')and 
sc.cno=course.cno
group by sc.sno
having COUNT(*)=2)
group by student.sno,sname,Ssex,Sage,Sdept
having COUNT(*)=2

(11)至少选修“数据库”或“数据结构”课程的学生的基本信息；

select student.sno,sname,sdept,sc.Cno,cname,grade
from student,sc,course
where student.sno=sc.sno 
and sc.Cno=course.cno and
sc.sno in(
select sc.sno from sc,course 
where (cname='数据库'or cname='数据结构')
and sc.Cno=course.cno )

(12)列出所有课程被选修的详细情况，包括课程号、课程名、学号、姓名及成绩；

select course.cno,cname,student.sno,sname,grade
from student,sc,course
where student.sno=sc.sno
and sc.cno=course.cno
order by course.cno

(13)*查询只被一名学生选修的课程的课程号、课程名；

select sc.cno,cname
from sc,course
where sc.cno=course.cno
group by sc.cno,cname 
having COUNT(sc.sno)=1

(14)*检索所学课程包含学生‘张向东’所学课程的学生学号、姓名；

select Student.Sno,Student.Sname
from Student,SC
where Student.Sno=SC.Sno 
and SC.Cno in (
select SC.Cno
from Student,SC
where Student.Sno=SC.Sno 
and Student.Sname='张向东')

2.请使用T-SQL 语句实现进行以下操作：
(1)在student表的sno列上创建唯一性聚集索引index_sno

create unique clustered index index_sno on student (sno)

(2)在student表的sname列上创建唯一性非聚集索引index_sname

create unique NONCLUSTERED index index_sname on student (sname)

(3)在student表的sage列上创建非聚集索引index_sage

CREATE NONCLUSTERED INDEX index_sage ON student(sage)

(4)在sc表的sno列和cno列上创建复合非聚集索引index_sno_cno

CREATE NONCLUSTERED INDEX index_sno_cno ON sc(sno,cno)

(5)删除上面的索引index_snocno

DROP index index_sno_cno ON sc