sql语句作业2-高级查询附答案

SQL 作业2：高级查询
一、连接查询
1 不同表之间的连接查询
例1.37 查询每个学生及其选修课程的情况。

SELECT Students.*,Reports.*FROM Students,Reports
WHERE Students.Sno=Reports.Sno;
例1.38 查询每个学生的学号(Sno)、姓名(Sname)、选修的课程名(Cname)及成绩(Grade)。

SELECT Students.Sno, Sname, Cname, Grade FROM Students,Reports,Courses
WHERE Students.Sno=Reports.Sno AND o= o;
补例子：查询选修了C01课程且成绩为70分以上的学生学号 姓名和成绩。

select Students.Sno,Sname, Reports.Grade from Students,Reports
where Students.Sno=Reports.Sno and o='C01' and Grade>702 自身连接
例1.39 查`询每一门课的间接先修课(即先修课的先修课)。

SELECT ame , o, B.Pre_Cno FROM Courses A , Courses B WHERE A.Pre_Cno=o;3 外连接
例1.40把例1.37中的等值连接改为左连接。

SELECT Students.*,o,REPORTS.Grade FROM Students,Reports
WHERE Students.Sno*=Reports.Sno;二、嵌套查询
表1.1 关系Students
Sno Sname Ssex Sage Sdept S01S02S03S04S05S06
王建平刘华范林军李伟黄河长江
男女女男男男
211918191820
自动化自动化计算机数学数学数学
表1.2 关系Courses Cno Cname Pre_Cno
Credits C01C02C03C04C05C06C07
英语数据结构数据库DB_设计C++网络原理操作系统
C05C02C03C07C05
4223333
表1.3 关系Reports
Sno
Cno Grade S01S01S02S02S02S03S03S04
C01C03C01C02C03C01C02C03
9284909482729075
1 带谓词IN的嵌套查询
例1.41 查询选修了编号为“C02”的课程的学生姓名(Sname)和所在系(Sdept)。

SELECT Students.Sno, Sname
FROM Students
where Students.Sno in (select Sno
from Reports
WHERE Cno in (select Cno
from Courses
where Cname='数据结构'))
例1.42查询与“李伟”在同一个系学习的学生学号(Sno)、姓名(Sname)和系名(Sdept)。

SELECT Sno, Sname, Sdept
FROM Students
WHERE Sdept IN ( SELECT Sdept
FROM Students
WHERE Sname=’李伟’);
例1.43查询选修了课程名为“数据结构”的学生学号(Sno)和姓名(Sname)。

SELECT Sno, Sname
FROM Students
WHERE Sno IN ( SELECT Sno
FROM Reports
WHERE Cno IN ( SELECT Cno
FROM Courses
WHERE Cname='数据结构'));
2 带有比较运算符的嵌套查询
例1.44将例1.42改为带有比较运算符的嵌套查询。

SELECT Sno, Sname, Sdept
FROM Students
WHERE Sdept= ( SELECT Sdept
FROM Students
WHERE Sname='李伟');
3 带谓词ANY或ALL的嵌套查询
例1.45查询非自动化系的不超过自动化系所有学生的年龄的学生姓名(Sname)和年龄(Sage)。

SELECT Sname, Sage
FROM Students
WHERE Sage<=ALL( SELECT Sage
FROM Students
WHERE Sdept='自动化') AND Sdept!='自动化';
或者用下面的方法
SELECT Sname, Sage
FROM Students
WHERE Sage<=ALL( SELECT Sage
FROM Students
WHERE Sdept='自动化') AND Sdept NOT IN ('自动化')
4 带谓词EXISTS的嵌套查询
例1.46查询所有选修了编号为“C01”课程的学生姓名(Sname)和所在系(Sdept)。

SELECT Sname,Sdept
FROM Students
WHERE EXISTS (SELECT Sno
FROM Reports
WHERE Students.Sno=Reports.Sno AND Cno='C01');
例1.47将例1.42改为带谓词EXISTS的查询，
SELECT Sno,Sname,Sdept
From Students
Where EXISTS ( SELECT *
From Students
Where Sname='李伟')
例1.48 查询选修了所有课程的学生姓名(Sname)和所在系。

SELECT Sname,Sdept
FROM Students
WHERE EXISTS (SELECT *
FROM Reports
WHERE Students.Sno=Reports.Sno );
三、集合查询
例1.49查询计算机科学系的学生或年龄不大于20岁的学生信息。

SELECT Students.*
FROM Students
WHERE Sdept='计算机'
UNION
SELECT Students.*
FROM Students
WHERE Sage<=20;
例1.50 查询数学系的学生且年龄不大于20岁的学生的交集，这实际上就是查询数学系中年龄不大于20岁的学生。

SELECT Students.*
FROM Students
WHERE Sdept='数学'
INTERSECT
SELECT Students.*
FROM Students
WHERE Sage<=20 ;
例1.51查询数学系的学生与年龄不大于20岁的学生的差集。

SELECT Students.*
FROM Students
WHERE Sdept='数学'
MINUS
SELECT Students.*
FROM Students
WHERE Sage<=20;。