实验6：SQL高级查询

实验六高级查询

【实验目的与要求】

1、熟练掌握IN子查询

2、熟练掌握比较子查询（尤其要注意ANY、ALL谓词如何用集函数代替）

3、熟练掌握EXISTS子查询（尤其是如何将全称量词和逻辑蕴含用EXISTS谓词代替）

4、熟练掌握复杂查询的select语句

【实验准备】

1．准备好测试数据

2．熟悉多表查询与嵌套查询的用法。

【实验内容】

5.1.嵌套子查询

以下实验在前面实验中创建的CPXS数据库中完成，请根据前面实验创建的表结构和数据，完成如下嵌套查询：

查询在2004年3月18日没有销售的产品名称（不允许重复）。

用IN子查询：

写出对应SQL语句并给出查询结果：

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 ’的所有书籍价格的所有书籍的信息