实验6_高级查询.doc

【实验目的与要求】n 掌握基本的连接查询操作l 等值连接l 非等值连接l 自身连接l 外连接n 掌握嵌套查询操作【实验内容与步骤】思考问题：如何使用SQL来创建数据库表？准备工作：若上个实验的数据库表还没有创建，请先创建之，并按照实验要求输入测试数据。6.1连接连接查询主要包括： n 等值连接n 非等值连接n 自身连接n 外连接1等值连接以等于运算符 (=) 为基础的连接称为等值连接。通常情况下，所有连接都是等值连接。等值连接的语法如下：SELECT FROM WHERE mon_column = mon_column AND mon_column = mon_column;实验6-1 查询所有产品的名称、类别名称和库存量等数据。请给出结果截图：实验6-2 查询所有订单的订单代号、客户公司名称和运货费，查询结果按订单代号的升序排列。请给出结果截图：实验6-3 查询由北京客户订购的运货费等于于8元的订单的订单代号、客户公司名称和运货费。请给出结果截图：等值连接可在两个表上执行，也可在多个表上执行。在一个连接中使用四个表：实验6-4 查询每一客户的客户代号、公司名称及其订单的订单代号、订购日期、产品名称和数量等数据。请写出查询结果：练习6-1 修改实验6-4中给出的语句并以如下方式给出，然后观察发生什么情况。SELECT Cno,Company, Ono,Order_date,Pname,QtyFROM Customer,Orders,Order_items,ProductWHERE Customer.Cno=Orders.Cno AND Orders.Ono=Order_items.Ono AND Order_items.Pno=Product.Pno;给出上面的选择语句后，会出现 “Column ambiguously defined”（列定义模糊不清）的出错信息。这是因为“Ono”存在于两个表（即Orders表 和Order_items表）之中。我们需要明确指定要从哪个表选择Ono。在这种情况下，可以从任何一个表中选择Ono。2非等值连接实验6-5 查询所有单价介于20和35元之间的产品的名称、类别名称、单价和库存量等数据。请给出结果截图：3自身连接自身连接是连接的另一种形式。在这种情况下，连接是在单个表上进行的，就像它是两个独立的表。 表的每个行都与其本身组合，并与表的所有行组合。实验6-6 查询单价正好相差0.5倍的每一对产品的名称及其单价。请给出结果截图：注意：在进行表的自身连接时，须在FROM子句中给同一个表取不同的别名，并在书写SELECT语句的其它子句时把这些代表同一表的不同别名看成是具有相同数据的“不同表”（读者在理解自身连接查询时，可以将它们看成不同的表，但实际上只有一个表）。练习6-2 查询与“Du Wei”客户在同一城市的其他客户的姓名及其电话。写出相应的SQL语句，并给出查询的结果：4. 外连接前面介绍的查询都是内连接。内连接不选择表中不符合查询条件的记录。这些值可通过外连接获得，外连接利用运算符加号 (+)。 语法如下：SELECT (column_names)FROM (tablenames)WHERE (table1.column1 = table2.column2)(+)；实验6-7 查询所有客户的代号、姓名以及其订单的代号和订购日期与订单状态，查询结果按客户代号的升序排列。先请看下面的SQL语句及其查询结果：请给出结果截图：上面的查询是一个内连接，由于客户“C0003”和“C0004”没有订单，他们都不满足查询条件，因此他们的信息没有出现在查询结果中；如果我们想让这两个客户的信息也出现在查询结果中就需要使用下面的外连接语句：请给出结果截图：试比较上面的两个查询语句中查询结果有何区别？6.2 嵌套查询嵌套查询是指将一个SELECT-FROM-WHERE查询块嵌套在另一个查询块的WHERE或HAVING短语的条件中的查询。1带有比较运算符的子查询实验6-8查询与“Du Wei”客户在同一城市的其他客户的姓名及其电话。请给出结果截图：本查询包括了两个SELECT-FROM-WHERE查询块，其中下层的查询块嵌套在上层的查询块中。我们将上层的查询块称为父查询，而把下层的查询块称为子查询。子查询的处理先于父查询。本嵌套查询的过程如下: 先通过子查询，在Customer表中找到“Du Wei”客户所在的城市，结果为“Shanghai”； 利用子查询得到的City值“Shanghai”，求出父查询中位于“Shanghai”的客户的姓名及其电话。2带有IN谓词的子查询当子查询的查询结果包含多个值时，经常会使用谓词IN来连接子查询和父查询。实验6-9 查询订购了单价比“3004”号产品大的那些产品的订单的代号。请给出结果截图：本查询嵌套了两个SELECT子查询，其中最内层的子查询找出了“3004”号产品的单价，结果是单值30，在该子查询前可以使用比较运算符“”，而其上一层的子查询找出了单价大于30的产品的代号，结果有多个值，故在该子查询前需用谓词“IN”。3带有ANY或ALL谓词的子查询当子查询的查询结果包含多个值时，光用谓词“IN”来连接子查询和父查询是不够的，有时会用到前置了比较运算符(、=、=、=、)的谓词“ANY”或“ALL”。其中“ANY”代表子查询结果中的某个值，“ALL”代表子查询结果中的所有值。这样，“ANY”表示大于子查询结果中的某一个值，“ALL”表示大于子查询结果中的所有值。其它如“=ANY”、“=ANY”、“ANY”、“=ANY”、“ANY”、“=ALL”、“=ALL”、“ALL”、“=ALL”、“ALL”的含义依此类推。实验6-10 查询价格大于所有1号类产品的产品的名称及其价格。请给出结果截图：实验6-11 查询其它类中、价格大于某个1号类产品的产品的名称及其价格。请给出结果截图：4带有EXISTS谓词的子查询EXISTS 运算符检查两个表中是否存在值。它只可用于子查询，即没有像存在测试的简单比较测试。EXISTS 运算符不能返回 NULL 值。 实验6-12 查询没有订购“1001”号产品的订单的代号。请给出结果截图：从上面的查询可以看出，带有EXISTS谓词的子查询与前面的几类子查询是有区别的，主要区别有以下两点： 前几类子查询返回的是某一列的值(单值或多值)，而带有EXISTS谓词的子查询返回的是逻辑值真或假。当在子查询的FROM子句的表中找到满足条件的元组时，子查询返回逻辑真值，否则返回逻辑假值。 前几类子查询的WHERE子句中的条件与父查询是无关的，而带有EXISTS谓词的子查询的WHERE条件的值却依赖于父查询的某个(或某些)属性列的值，如上例子查询的结果是真还是假就依赖于父查询中的Orders.Ono属性列的当前值。6.3 集合操作6.3.0 准备工作创建如下表X和Y6.3.1 union执行以下语句：写出运行结果：6.3.2 minus:执行以下语句：写出运行结果：6.3.3 intersect:执行以下语句：写出运行结果：6.4 实验练习1. 练习：完成下列查询，写出相应的SQL语句1) 列出至少有一个雇员的所有部门。2) 列出薪金比“SMITH”多的所有雇员。3) 列出所有雇员的姓名及其上级的姓名。4) 列出入职日期早于其直接上级的所有雇员。5) 列出部门名称和这些部门的雇员，同时列出那些没有雇员的部门。6) 列出所有“CLERK”（办