《数据库原理与应用》课程实验报告 --数据库的嵌套查询

一、实验目的

1.熟练掌握SQL Server Management Studio中查询分析的使用方法，加深对Ｔ-SQL结构化查询的理解。
2.掌握数据查询中嵌套查询语法格式，及使用IN,ANY,ALL,EXISTS操作符和比较运算符对数据库进行嵌套查询的操作。
１．基本操作实验
学生选课数据库中表结构如下：
学生（学号，姓名，年龄，性别，所在系）；
课程（课程号，课程名，学分，先行课）；
选课（学号，课程号，成绩）；
在学生选课数据库中实现下面的数据查询操作。
1)求计算机系学生选课的课程号和成绩。
2)求选修了高等数学的学生学号和姓名。
3)求选修成绩在90~95之间的课程名。
4)针对C1课程，查询C1课程的成绩高于张三的学生学号和成绩。
5)求其他系中比计算机系某学生年龄小的学生（即求年龄小于计算机系年龄最大者的学生）。
6)求其他系中比计算机系学生年龄都小的学生。
7)求没有选修C2课程的学生姓名。
8)查询选修了“c1”课程但没有选修“c2”课程的学生学号。
9)查询选修了全部课程的学生姓名。

二、实验过程及分析

实验过程

--创建学生表
create table student
(
  s_num  int PRIMARY KEY,
  s_name varchar(20)not null,
  s_age int check(s_age between 10 and 35),
  s_sex varchar(10) check(s_sex in('男', '女')),
  s_unit varchar(20)
)
drop table student 
select * from student
--插入数据
insert into student values (1815001,'张三',17,'男','数学系'),
                          (1815002,'李白',20,'女','计算机系'),
						  (1815003,'后裔',18,'男','计算机系'),
						  (1815004,'翠花',25,'女','数学系'),
						  (1815005,'狗蛋',19,'男','机械系');
						  
--创建课程表
create table course
(
c_num varchar(20) PRIMARY KEY,
c_name varchar(20) not null,
c_credit  int check(c_credit in (1,2,3,4,5,6)),
front varchar(20) 
)
--插入数据
drop table course
insert into  course values ('C1','高等数学',2,' '),
							('C2','程序设计电路',2,'c1'),
							('C3','数字电路',3,'C1'),
							('C4','计算机组成',3,'C3'),
							('C5','计算机网络',4,'C4'),
							('C6','操作系统',3,'C5')

select * from course
--创建选课表
create table selectCourse
(
 s_num  int,
 c_num varchar(20),
 score numeric(3,1)   ,
 primary key (s_num,c_num),
 foreign key ( s_num) references student(s_num),
 foreign key (c_num) references course(c_num),
)
drop table selectCourse
--插入数据

insert into  selectCourse(s_num, c_num, score) 
                           values(1815001,'C1',93),
						         (1815002,'C1',90),
								 (1815003,'C1',99),
								 (1815004,'C1',96),								
                                 (1815001,'C2',80),
								 (1815001,'C3',90),
								 (1815001,'C4',78),
								 (1815001,'C5',88),
								 (1815001,'C6',98),
								 (1815005,'C3',88),							   
								 (1815004,'C3',98)
insert into  selectCourse(s_num, c_num ) 
                                 values(1815005,'C4')
select * from  Course
select * from selectCourse

嵌套查询

--1)求计算机系学生选课的课程号和成绩
select  selectCourse.c_num  as '课程号'  ,  selectCourse.score   as '成绩'
from  selectCourse
join  course  on  selectCourse.c_num = course.c_num
join   student  on  student.s_num = selectCourse.s_num
where  student.s_unit= '计算机系'

--2)求选修了高等数学的学生学号和姓名。
select  s_num as '学号'  ,s_name as '姓名'  from student
where student.s_num in
	(select  s_num from  selectCourse
    where  selectCourse.c_num  in 
	           (
				Select  c_num from course 
				where  course.c_name =  '高等数学'
				)
	)

--3)求选修成绩在90~95之间的课程名。
select course.c_name  as  课程名 from  course
where  course.c_num  in 
			(
			Select  selectCourse.c_num from selectCourse
			where ( score between  90 and 95)
			) 

--4)针对C1课程，查询C1课程的成绩高于张三的学生学号和成绩。

 select   selectCourse.s_num as 学号  ,  selectCourse.score as  成绩
 from   selectCourse
 where   selectCourse.score > 
       (  select  score from  selectCourse
         where  c_num= 'C1'
		 and  s_num=
		 (
         select  s_num from  student
         where  s_name = '张三' 
		 )
	   )  

--5)求其他系中比计算机系某学生年龄小的学生（即求年龄小于计算机系年龄最大者的学生）。
select  s_num  as 学号 , s_name as 姓名  from   student
where s_age  < 
		any(
		select  s_age from  student
		where   s_unit= '计算机系'
		)
		and    s_unit!= '计算机系'

--6)求其他系中比计算机系学生年龄都小的学生
select  s_num  as 学号 , s_name as 姓名  from   student
where s_age  < 
		all(
		select  s_age from  student
		where   s_unit= '计算机系'
		)
		and    s_unit not in ( '计算机系')

--7)求没有选修C2课程的学生姓名
 select   s_name as 姓名 from student
where  s_num not in 
        (
		select  s_num  from  selectCourse
		where  c_num ='C2'
		)


--8)查询选修了“c1”课程但没有选修“c2”课程的学生学号。
一
select s_num as 学号
from  selectCourse A
where A.c_num = 'C1' and  not exists (select * from selectCourse B
                                      where A.s_num=B.s_num  and  c_num ='C2' )
--9)查询选修了全部课程的学生姓名
SELECT s_name  FROM Student
         WHERE NOT EXISTS
            (SELECT *  FROM course
              WHERE NOT EXISTS
                  (SELECT *FROM selectCourse
                   WHERE s_num= Student.s_num
                      AND c_num= Course.c_num))

3.实验结果

4.实验分析

①分析实验结果；
达到了预期的效果
②说明实验过程中遇到的问题及怎么解决。
第八题和第九的的exists 查询还不是掌握的很好。
Exists执行的流程Exists首先执行外层查询，再执行内存查询，与IN相反。
流程为首先取出外层中的第一元组， 再执行内层查询，将外层表的第一元组代入，若内层查询为真，即有结果时。返回外层表中的第一元 组，接着取出第二元组，执行相同的算法。一直到扫描完外层整表 。

③对错误方面进行分析；
不能熟练的掌握语法。下去还需继续深入的学习。