数据库实验四——嵌套SQL查询

一．实验步骤

任选以上步骤中创建好的表格, 自己设计问题, 逐一展示如下命令的用法.

（1）Set Operations: union, intersect, except
先执行语句：

select* 
from instructor
where salary>100000

找出教工表里薪水大于100000的教师，结果如下：

再执行语句：

select*
from instructor
where dept_name='Biology'or dept_name= 'physics'

找出教工表里院系为生物系或物理系的老师，结果如下：

然后对这两个集合做并集，交集，差集：
并集：执行语句：

(select* 
from instructor
where salary>100000)
union 
(select*
from instructor
where dept_name='Biology'or dept_name= 'physics')

结果如下：

交集：执行语句：

(select* 
from instructor
where salary>100000)
intersect
(select*
from instructor
where dept_name='Biology'or dept_name= 'physics')

结果如下：

差集：执行代码：

(select* 
from instructor
where salary>100000)
except
(select*
from instructor
where dept_name='Biology'or dept_name= 'physics')

结果如下：

（2）Set Comparison: some, all
执行语句：

select* 
from instructor
where salary>some
(select salary
from instructor
where dept_name='Biology'or dept_name= 'physics')

找出教工表内，工资至少高于生物系或物理系一位老师的工资的老师。
结果如下：

执行以下语句：

select* 
from instructor
where salary>all
(select salary
from instructor
where dept_name='Biology'or dept_name= 'physics')

找出教工表内，工资大于所有生物系和物理系的老师工资的老师。
结果如下：

（3）Test for Empty Relations: exists

Where Not exists( (B) except (A) )表示B-A是不存在的，换言之，A是B的超集。
执行以下代码：

select S.ID ,name,dept_name,course_id
from (student as S inner join takes on S.ID=takes.ID)
where not exists ( 
(select course_id
from course
where dept_name='Physics'
)
except
(select course_id 
from takes as T
where S.ID = T.ID
))

找到学习了物理学院全部课程的学生。
结果如下：（没有这样的学生，哈哈哈）

（4）Test Duplicate Tuples: unique
重复元组存在性测试：unique
执行以下代码：

select C.course_id
from course as C
where unique 
(select S.course_id
from section as S ,course as C
where (C.course_id=S.course_id) and S.year=2009);

（注：程序跑不动，暂未发现哪里有问题。。。明明书上就这样写的）

（5）from 后接子查询
执行以下代码：

select dept_name
from
(select dept_name,count(course_id) as number
from course
group by dept_name)as S
where number=6;

找出开设了6门课程的院系
结果如下：

（6）with … as …
With子句用来定义临时关系
执行语句：

with max_salary(val) as
(select max(salary)
from instructor)
select ID,name,salary
from instructor as I,max_salary
where I.salary=max_salary.val;

找出教工里薪水最高的人。
结果如下：

二．实验总结

1.表格是没错，但是要重命名一下。

2.列名ID不明确（改成student.ID即可）
不清楚这里两张表的ID是一样的，为啥会造成不明确。。。而且书上就这么写的。

3.不能直接join或者natural join ，需要inner join 加on 或者using
（注：using的格式没有使用成功，不知道啥问题）
4.无法绑定由多个部分组成的标识符。把括号加全；在from里面把几个重命名的再写一遍。

5.unique执行失败（报错信息：在应使用条件的上下文中使用了非布尔变量的表达式。暂时没找到解决方法）

最后附上所有代码：

**

use university_3

select* 
from instructor
where salary>100000

select*
from instructor
where dept_name='Biology'or dept_name= 'physics'



select* 
from instructor
where salary>all
(select salary
from instructor
where dept_name='Biology'or dept_name= 'physics')



select ID ,name,dept_name,course_id
from (student as S inner join takes on S.ID=takes.ID)
where not exists ( 
(select course_id
from course
where dept_name='Physics'
)
except
(select course_id 
from takes as T
where S.ID = T.ID
))

select dept_name
from
(select dept_name,count(course_id) as number
from course
group by dept_name)as S
where number=6;



select C.course_id
from course as C
where unique 
(select S.course_id
from section as S ,course as C
where (C.course_id=S.course_id) and S.year=2009);


with max_salary(val) as
(select max(salary)
from instructor)
select ID,name,salary
from instructor as I,max_salary
where I.salary=max_salary.val;