一、实验目的
1．掌握Management Studio的使用。
2．掌握SQL中连接查询和嵌套查询的使用。
二、实验内容及要求（请同学们尝试每道题使用连接和嵌套两种方式来进行查询，如果可以的话）
1．找出所有任教“数据库”的教师的姓名。
--找出所有任教“数据库”的教师的姓名。
--连接
SELECT DISTINCT Teac_name ,Course_name
from Teacher,Course,CourseTeacher
WHERE Course.Course_name = '数据库原理'
and Course.Course_id=CourseTeacher.Course_id
and CourseTeacher.Teac_id= Teacher.Teac_id
--嵌套
SELECT DISTINCT Teac_name FROM Teacher
WHERE Teac_id IN (SELECT Teac_id
from CourseTeacher
WHERE Course_id IN
(SELECT Course_id
from Course
WHERE Course_name='数据库原理'
)
)
找出学号为“980101011”的学生选修的课程号和课程名。
--找出学号为“980101011”的学生选修的课程号和课程名。
--由于所提供数据并无学号为“980101011”，因此本题选用“140102006”作为查询对象
--连接
SELECT Stu_name,Course.Course_id,Course.Course_name
from Course,CourseTeacher,Student
WHERE Stu_id='140102006'
and Student.Class_id=CourseTeacher.Class_id
and CourseTeacher.Course_id=Course.Course_id
--嵌套
SELECT Course_id,Course_name from Course
WHERE Course_id IN(SELECT Course_id
from CourseTeacher
WHERE Class_id IN
(SELECT Class_id
from Student
WHERE Stu_id='140102006'
)
)
3．“涂杰杰”所选修的全部课程号及成绩。（注意：school中可能有同名，即可能有多名学生叫“涂杰杰”。）
-- “涂杰杰”所选修的全部课程号及成绩。（注意：school中可能有同名，即可能有多名学生叫“涂杰杰”。）
--连接
SELECT DISTINCT Stu_name,Course_id,Grade
from Student,StudentGrade
WHERE Stu_name='涂杰杰'
and Student.Stu_id=StudentGrade.Stu_id
--嵌套
SELECT Course_id,Grade from StudentGrade
WHERE Stu_id IN (SELECT Stu_id
from Student
WHERE Stu_name='涂杰杰')
4. 找出“苏贤兴”同学所学课程的名称和成绩。
--找出“苏贤兴”同学所学课程的名称和成绩。
--连接
SELECT Stu_name,Course_name,Grade
from StudentGrade,Course,Student
WHERE Stu_name='苏贤兴'
and Student.Stu_id=StudentGrade.Stu_id
and StudentGrade.Course_id=Course.Course_id
--嵌套
SELECT Course_name,Grade from StudentGrade
join Course on Course.Course_id = StudentGrade.Course_id
WHERE Stu_id IN(SELECT Stu_id
from Student
WHERE Stu_name='苏贤兴')
5．显示所有课程的选修情况（最终结果列包含课程号，课程名，选修人数）。（外连接）
--显示所有课程的选修情况（最终结果列包含课程号，课程名，选修人数）。
--外连接
SELECT Course.Course_id,Course_name,count(StudentGrade.Stu_id) as 选修人数
from Course join StudentGrade on Course.Course_id=StudentGrade.Course_id
Group by Course.Course_id,Course_name
--嵌套
--解析 用join操作符将 Course 表和 StudentGrade 表进行连接，关联条件为它们之间的 Course_Id 列。
--同样使用了子查询的方式来获取 StudentGrade 表中的 Course_id 和 Stu_id 列，因此在 JOIN 子句中使用了一个括号内嵌套的 SELECT 语句，并命名为 StudentGrade。
--根据 Course 表的 Course_id 列和 Course_name 列同时进行 GROUP BY 分组操作
SELECT Course.Course_id,Course_name,count(StudentGrade.Stu_id) as 选修人数
from Course join(SELECT Course_id,Stu_id from StudentGrade)
as StudentGrade on Course.Course_id=StudentGrade.Course_id
Group by Course.Course_id,Course_name
6．检索选修课程号为“0109”或“0111”的学生学号、姓名和所在班级编号。
--检索选修课程号为“0109”或“0111”的学生学号、姓名和所在班级编号。
--由于提供的材料缺少“0109”和“0111”，因此本体选取“0101”和“0102”作为条件
--连接
SELECT Student.Stu_id,Stu_name,Class_id
from Student join StudentGrade on Student.Stu_id=StudentGrade.Stu_id
WHERE Course_id='0101' or Course_id='0102'
--嵌套
SELECT Stu_id,Stu_name,Class_id from Student
WHERE Stu_id IN (SELECT Stu_id
from StudentGrade
WHERE Course_id='0101' or Course_id='0102')
7．检索既选修课程“数据结构”又选修了“C语言”的学生学号。
--检索既选修课程“数据结构”又选修了“C语言”的学生学号。
--由于材料未提供“C语言”，因此将其替换为“数据库原理”
--内部查询使用 OR 运算符查找出符合条件的课程编号，并使用聚合函数 COUNT 和 HAVING 子句确保每个学生都选修了这两门课
--连接
SELECT Stu_id
from StudentGrade join Course on StudentGrade.Course_id=Course.Course_id
WHERE Course_name='数据库原理' or Course_name='数据结构'
GROUP By Stu_id
HAVING COUNT(*) = 2
--嵌套
SELECT Stu_id from StudentGrade
WHERE Course_id IN (SELECT Course_id
from Course
WHERE Course_name='数据库原理' or Course_name='数据结构'
)
GROUP By Stu_id
HAVING COUNT(*) = 2
8.检索选修了课程“数据结构”和“C语言”两门课程并且“数据结构”分数高于“C语言”的学生学号和姓名。
--检索选修了课程“数据结构”和“C语言”两门课程并且“数据结构”分数高于“C语言”的学生学号和姓名。
--由于材料未提供“C语言”，因此将其替换为“数据库原理”
--该查询语句使用了 JOIN 操作符将 Student 表和 StudentGrade 表进行连接，其中又对 StudentGrade 表使用了两次JOIN操作符。第一次是将 StudentGrade 表自联接为一个别名为 DSID （代表数据结构），关联条件是 StudentGrade 表中的 Stu_id 列与 Student 表中的 Stu_id 列相等。第二次是再次将 StudentGrade 表自联接为一个别名为 CLID（代表数据库原理），关联条件是 StudentGrade 表中的 Stu_id 列与 Student 表中的 Stu_id 列相等并且 DSID 表中的 Course_id 列不等于 CLID 表中的 Course_id 列。
--接着，在 JOIN 子句中分别使用了两个括号内嵌套的 SELECT 语句，并命名为 DS 和 CL，以获取对应课程名称的 Course 记录，同时关联各自的 Course_id 列。
--最后，在 WHERE 子句中设置了筛选条件，即要求 DSID 表中的 Grade 列大于 CLID 表中的 Grade 列。
SELECT Student.Stu_id,Student.Stu_name
from Student
join StudentGrade as DSID
on DSID.Stu_id = Student.Stu_id
join StudentGrade as CLID
on CLID.Stu_id = Student.Stu_id and DSID.Course_id <> CLID.Course_id
join Course as DS
on DSID.Course_id = DS.Course_id and DS.Course_name = '数据结构'
join Course as CL
on CLID.Course_id = CL.Course_id and CL.Course_name = '数据库原理'
where DSID.Grade > CLID.Grade
