SQLServer 实验二 多表查询

（1）找出同一天进入公司服务的员工

Select a.employeeNo,a.employeeName,a.hireDate,

b.employeeNo,b.employeeName

from Employee a,Employee as b

where a.employeeNo!=b.employeeNo and

a.employeeName>b.employeeName and (a.hireDate=b.hireDate)

参考解答：

SELECT DISTINCT a.hireDate, a.employeeNo, a.employeeName   -- 消除重复的元组

FROM employee a, employee b

WHERE a.employeeNo!=b.employeeNo    --本人不跟本人连接

AND a.hireDate=b.hireDate

ORDER BY a.hireDate, a.employeeNo

select a.hireDate, a.employeeNo, a.employeeName, b.employeeNo, b.employeeName

from employee a, employee b

where a.employeeNo<b.employeeNo    -- 1. 本人不跟本人连接，2. 不同的两人只连接一次

and a.hireDate=b.hireDate

（2）查找与“陈诗杰”在同一个单位工作的员工姓名、性别、部门和职务

Select b.employeeName,b.sex,b.headShip,b.department

from Employee a,Employee as b

where a.employeeName='陈诗杰' and (a.department=b.department)

Select employeeName,sex,headShip,department

from Employee

where department=(select department

                  from Employee

                  where employeeName='陈诗杰')   --用子查询的方法

（3）在Employee表中查询薪水超过员工平均薪水的员工信息。

SELECT *

FROM Employee

WHERE salary > ( SELECT avg(salary) avgsalary

                           FROM Employee)

（4）查找有销售记录的客户编号、名称和订单总额。

select a.customerNo,a.customerName,sum(quantity*price)

from Customer a,OrderMaster b,OrderDetail c

where a.customerNo=b.customerNo and b.orderNo=c.orderNo

group by a.customerNo,a.customerName

--统计每个客户每个订单的销售总金额

  或

select a.customerNo,a.customerName,b.orderNo,orderSum

from Customer a,OrderMaster b

where a.customerNo=b.customerNo

order by a.customerNo,b.orderNo

        --统计每个客户每个订单的销售总金额 

参考解答：

select a.customerNo,a.customerName,sum(orderSum)

from Customer a,OrderMaster b

where a.customerNo=b.customerNo

group by a.customerNo,a.customerName 

--统计每个客户所有订单的总金额

（5）查询没有订购商品的客户编号和客户名称。

SELECT a.customerNo,customerName

FROM Customer a

WHERE a.customerNo NOT IN (SELECT customerNo

                                       FROM OrderMaster )

（6）使用子查询查找32M DRAM的销售情况，要求显示相应的销售员的姓名、性别，销售日期、销售数量和金额，其中性别用“男”、“女”表示。

 SELECT employeeName,case sex

                               when 'F' then '女'

                               when 'M' then '男' end sex,

               orderDate,quantity,quantity*price 金额

FROM Employee a,OrderMaster b,OrderDetail c

WHERE a.employeeNo=b.salerNo AND b.orderNo=c.orderNo

              AND c.ProductNo IN (

                   SELECT ProductNo

                   FROM Product

                   WHERE ProductName='32M DRAM')

参考解答：

       SELECT employeeName,case sex

                               when 'F' then '女'

                               when 'M' then '男' end sex,

               orderDate,quantity,quantity*price 金额

FROM Employee a,OrderMaster b,OrderDetail c

WHERE a.employeeNo=b.salerNo AND b.orderNo=c.orderNo

              AND c.ProductNo IN (

                   SELECT ProductNo

                   FROM Product

                   WHERE ProductName='32M DRAM')

（6）查询OrderMaster表中订单金额最高的订单号及订单金额。

select orderNo,orderSum

from OrderMaster

where orderSum=(select max(orderSum)

                          from OrderMaster)

（7）在订单主表中查询订单金额大于“E2005002业务员在2008-1-9这天所接的任一张订单的金额”的所有订单信息。

SELECT *

FROM OrderMaster

WHERE orderSum>any

          (SELECT orderSum

           FROM OrderMaster

           WHERE salerNo='E2005002' AND orderDate='20080109' )

（8）查询单价高于400元的商品编号、商品名称、订货数量和订货单价。

SELECT a.productNo,productName,quantity,price

FROM Product a, OrderDetail b

WHERE a.productNo=b.productNo AND price>400

ORDER BY productName

（9）分别使用左外连接、右外连接、完整外部连接查询单价高于400元的商品编号、商品名称、订货数量和订货单价，并分析比较检索的结果。

左外连接命令：

SELECT a.productNo,productName,quantity,price

FROM Product a LEFT OUTER JOIN OrderDetail b

                 ON a.productNo=b.productNo

WHERE price>400

     

 

Select a.productNo , a.productName , b.quantity,b.price

From  OrderDetail As b left JOIN  Product As a

           ON (a.productNo=b.productNo)  and price>400

 

 

 

这两个左外连接，第一个是先去掉单价小于等于400的商品，然后执行左外连接，由于商品表是订单明细表的主表，因此在订单明细表里的所有商品在商品表中都存在，因此与普通连接没有区别。

第二个左外连接是将那些单价小于等于400的商品用空值替代

右外连接命令：

SELECT a.productNo,productName,quantity,price

FROM Product a RIGHT OUTER JOIN OrderDetail b

         ON a.productNo=b.productNo

WHERE price>400

   

 

Select a.productNo , a.productName , b.quantity,b.price

From  OrderDetail As b RIGHT JOIN  Product As a

         ON (a.productNo=b.productNo) and price>400

   

 

全连接命令：

SELECT a.productNo,productName,quantity,price

FROM Product a FULL OUTER JOIN OrderDetail b

          ON a.productNo=b.productNo

WHERE price>400

 

Select a.productNo , a.productName , b.quantity,b.price

From  OrderDetail As b full JOIN  Product As a

      ON (a.productNo=b.productNo)  and price>400

 

 

右外连接和全外连接的分析同左外连接。

从上述结果可知：若表a和表b做外连接，且b表是外码表，则a和b表左外连接可能会出现空值，但是右连接一定不会出现空值，全外连接与左外连接一样的结果。

（10）使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额，其中订货日期不要显示时间，日期格式为“yyyy-mm-dd”，按客户编号排序，同一客户再按订单金额降序排序输出。

SELECT a.customerNo,customerName,

convert(char(10),orderDate,120) orderDate,orderSum

FROM Customer a LEFT OUTER JOIN OrderMaster b ON

a.customerNo=b.customerNo

ORDER BY a.customerNo,orderSum desc

（11）查找每个员工的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期，其中性别使用“男”和“女”表示，日期使用“yyyy-mm-dd”格式显示。

SELECT salerNo,employeeName, CASE sex WHEN 'M'THEN '男'

                                                  WHEN 'F'THEN '女'

                                        END 性别,

           productName,quantity,price,orderSum,

           convert(char(10),orderDate,120) orderDate

FROM Employee a,OrderMaster b,OrderDetail c,Product d

WHERE a.employeeNo=b.salerNo AND b.orderNo=c.orderNo

         AND c.productNo=d.productNo

（12）查找16M DRAM的销售情况，要求显示相应的销售员的姓名、性别，销售日期、销售数量和金额，其中性别用“男”、“女”表示。

SELECT employeeName,CASE sex

                                WHEN 'M' THEN'男'

                                WHEN 'F' THEN'女'

                            END sex,

          orderDate,quantity,quantity*price total

FROM Employee a,OrderMaster b,OrderDetail c,Product d

WHERE a.employeeNo=b.salerNo AND b.orderNo=c.orderNo AND

           c.productNo=d.productNo AND productName='16M DRAM'

（13）找出公司男业务员所接且订单金额超过2000元的订单号及订单金额。  

SELECT orderNo,orderSum

FROM (SELECT orderNo,orderSum

           FROM OrderMaster a,Employee b

           WHERE a.salerNo=b.employeeNo AND b.sex='M') c

WHERE ordersum>2000

   或：

SELECT orderNo,orderSum

FROM  OrderMaster a,Employee b

WHERE a.salerNo=b.employeeNo AND b.sex='M'  and ordersum>2000

（14）查询每种商品的总销售数量及总销售金额，要求显示出商品编号、商品名称、总数量及总金额，并按商品号从小到大排列。

SELECT a.productNo 商品编号,productName 商品名称,

qtySum 总数量,totalSum 总金额

FROM Product a,

        (SELECT productNo,sum(quantity) qtySum,

sum(quantity*price) totalSum

         FROM OrderDetail

         GROUP BY productNo)b

WHERE a.productNo=b.productNo

ORDER BY a.productNo

 （15）实验问题：

① 连接操作类型有哪些？分析外连接在现实应用中的意义。

答：连接运算包括内连接（等值连接，非等值连接，自然连接）外连接（左外连接，右外连接，全外连接），自表连接。外连接的意义：对于存在多对多的关系的两个关系，将关联不上的部分信息也能显示出来。在现实中对于数据库的数据初始化很有用。

② 查询表可以用在什么地方？使用查询表要注意哪些地方？

答：查询表用在from子句之后，必须为查询表设置一个元组变量名，用该名表示查询表。查询表是执行过程中产生的表，执行完后其表自动丢失。

子查询也可以认为是一个查询表，但是不需要为他指定元组变量名。

③ 分析SQL语句中的IN和OR关键字有何异同点？它们可以互换吗？给出实例说明。

答：in可用于集合查询，也可用于嵌套子查询中。

or则为逻辑查询，逻辑或。

当in用于集合查询时，in和or之间可以相互转换；当in用于嵌套子查询时，则不可以相互转换。

举例：

用IN来实现：SELECT studentNo,courseNo,score

              FROM Score

              WHERE courseNo IN(‘001’,’005’,’003’)

用OR来实现：SELECT studentNo,courseNo

              FROM Score

              WHERE courseNo=’001’ OR courseNo=’005’  OR courseNo=’003’

但是当IN 用于子查询时，此时IN不能和OR互换。

举例：SELECT studentName

            FROM Student

            WHERE Student.studentNo IN (SELECT Score.studentNo  FROM Score)

此时只能用IN。

④ 分析哪几种情况需要使用自表连接。

答：当同表中的某个元组变量与表中的另外的元组变量相关时，可以使用自表连接，从而得到一些特殊的数据或者是说带特定条件的数据。自表连接一般是用来判断并筛选单一表中的一些数据。