带答案--实验6：SQL高级查询

实验六高级查询
【实验目的与要求】
1、熟练掌握IN子查询
2、熟练掌握比较子查询（尤其要注意ANY、ALL谓词如何用集函数代替）
3、熟练掌握EXISTS子查询（尤其是如何将全称量词和逻辑蕴含用EXISTS谓词代替）
4、熟练掌握复杂查询的select语句
【实验准备】
1．准备好测试数据
2．熟悉多表查询与嵌套查询的用法。

【实验内容】
5.1.嵌套子查询
以下实验在前面实验中创建的CPXS数据库中完成，请根据前面实验创建的表结构和数据，完成如下嵌套查询：（也可以不按指导书给出的思路写查询语句，只要是正确的即可，有疑问时可以和同学及老师商量你的查询语句是否正确）
查询在2004年3月18日没有销售的产品名称（不允许重复）。

用IN子查询：
写出对应SQL语句并给出查询结果：
USE CPXS
SELECT产品名称
FROM CP
WHERE产品编号not IN
(SELECT产品编号FROM CPXSB WHERE销售日期='2004-3-12')
select distinct 产品名称
from CP
where 产品编号 not in
(
select 产品编号
from CPXSB
where 销售日期='2004-3-18'
);
用EXISTS子查询：
写出对应SQL语句并给出查询结果：
select distinct 产品名称
from CP
where 产品名称!=all
(
select 产品名称
from CP
where exists
(
select 产品编号
from CPXSB
where 销售日期!='2004-03-18' and
CP.产品编号=CPXSB.产品编号
)
)
查询名称为“家电市场”的客户在2004年3月18日购买的产品名称和数量。

用IN子查询：
写出对应SQL语句并给出查询结果：
select 产品名称,数量
from CPXSB left join CP on(CPXSB.产品编号=CP.产品编号)
where 客户编号 in
(
select 客户编号
from XSS
where 客户名称='家电市场'
)
and 销售日期='2004-03-18'
用EXISTS子查询：
写出对应SQL语句并给出查询结果：
select 产品名称,数量
from CPXSB left join CP on(CPXSB.产品编号=CP.产品编号)
where CPXSB.客户编号 =
(
select 客户编号
from XSS
where 客户名称='家电市场'
)
and exists
(
select distinct 产品名称
from CP
where 销售日期='2004-03-18' and
CP.产品编号=CPXSB.产品编号
)
查询销售量大于所有2004年3月18日销售的各产品销售数量的产品编号。

用ALL谓词：
写出对应SQL语句并给出查询结果：
select 产品编号
from CPXSB
where 数量>all(select 数量
from CPXSB
where 销售日期='2004-03-18'
)
用集函数：
写出对应SQL语句并给出查询结果：
select 产品编号
from CPXSB
group by 产品编号,数量
having 数量> (select max(数量)
from CPXSB
where 销售日期='2004-03-18'
)
查询购买了所有产品的客户的名称。

写出对应SQL语句并给出查询结果：
select 客户名称
from XSS
where not exists(
select 产品编号
from CP
where not exists(
select 客户编号
from CPXSB
where CP.产品编号=CPXSB.产品编号and CPXSB.客户编号=XSS.客户编号
)
)
5.2.集合操作
1. 准备工作
创建如下两表X和Y，并添加相应的值
图5-1 测试表X和Y 阅读并执行以下语句，理解其功能，给出运行结果。

2. 集合并：union
执行以下语句：
请给出运行结果：
3. 集合交：intersect:
执行以下语句：
请给出运行结果：
5.3以下操作请使用PUBS数据库中的数据表进行操作
1、查询PUBS数据库中的employee表中其出版社所在国家为’USA’的所有出版社员工的信息。

2、查询PUBS数据库中的SALES表中书籍出版时间pubdate在‘1991-10-1’以后的书籍的销售信息。

3、查询PUBS数据库中的titles表中书籍价格高于类别为‘business ’的任一书籍价格的所有书籍的信息。

4、查询PUBS数据库中的titles表中书籍价格高于类别为‘business ’的所有书籍价格的所有书籍的信息。