实验六 多表查询                                 
实验目的
1、 掌握嵌套查询的概念
2、 掌握连接查询
3、 进一步掌握分组操作
4、 掌握Union的使用
实验环境
    硬件环境：PC机一台
    软件环境：SQL Server 2008/2005
实验内容
在供应商数据库中
上述各属性的含义是：供应商号(SNO)、供应商名(SNAME)和地址(ADDR)，零件号(PNO)、零件名（PNAME）、颜（COLOR）、重量（WEIGHT）、单价(PRICE)、工程项目号(JNO)、工程项目名称(JNAME)、城市(CITY)、余额（BALANCE）、供应数量(QTY)。
1、 查询供应商的名字，以及各自负责的项目数，结果要求按照项目数的降序排列（连接、分组、排序）
Select sname, count（distinct JNO）
From S, SPj
Where S.sno = SPJ .sno
Group by sname
Select sname, count（distinct JNO）
From s, spj
Where S.sno = spj.sno
Group by sname
Order by count（distinct JNO）
2、 查询重量比螺栓重的零件名称，零件重量（子查询或自身连接）
Select pname,weight
From P
Where weight >
（Select  weight
From  P
Where pname = ‘螺栓’）
Select a.name,a.weight
from P a, P b
where a.weight > b.weight
and b.pname=’ 螺栓’
3、 查询P（零件）表中各种颜及其对应的零件种类的数目（分组和聚集查询）
Select color, count（PNOsql中union多表合并）
From P   
Group by color
4、 统计天津地区的项目使用零件的种数(超过3种)和零件总数量。要求查询结果按零件的种数升序排列，种数相同时按总数量降序排列。（连接、分组、聚集函数、排序）
Select JNO, count（PNO），sum(qty)
From J,SPJ
Where J.jno = spj.jno and city=’天津’
Group by JNO
Having count（PNO）>3
5、查询既供应P1零件又供应P2零件的供应商编号，供应商名称（多重条件查询）
6、查询每个供应商供应零件的情况，要求列出供应商名称，零件名称，供应数量，并按供
应数量的升序排序（连接、分组、聚集函数、排序）
7、查询与“万胜”供应商在同一城市的供应商的详细资料（子查询）
8、查询供应商品种类最多的供应商编号、供应商名。
9、查询既生产螺母，也生产螺栓的供应商编号和供应商名。
10、查询生产螺丝刀但不生产螺母的供应商名
11、查询“万胜”和“精益”两个供应商的供货情况，查询结果中包括供应商名、项目名、零件名和供应数量。用两个select语句实现查询，并用union将两个语句的查询结果合并在一起。
三、实验步骤
1、对文件夹中的‘SPJ’数据库进行附加还原。
2、按照以上要求完成题目。
3、要求写出查询代码并将查询结果截图附在代码后。
参考解答
1、查询供应商的名字，以及各自负责的项目数，结果要求按照项目数的降序排列（连接、分组、排序）
解题思路：
1)本题要查询供应商名，这项数据仅在供应商表（S）中才有，而要统计每个供应商向几个项目供货，则需要对SPJ表中的数据进行统计。
这里需要对S和SPJ进行连接，两表的共同字段是SNO，用该字段进行连接。
SELECT SNAME,JNO,PNO
FROM S,SPJ
WHERE S.SNO=SPJ.SNO
代码执行结果：
图1-1
说明：该结果表明，这些供应商向项目提供零件的信息，这里项目和零件都用编号表示。
2）这还没有达到题目要求的效果。题目要求统计每个供应商负责的项目数。这里需要分组。
用供应商名进行分组，计算每个组中项目号的数目。这里请注意，“万胜”这个供应商的供应记录有6条，但是仔细看，不难发现，他供应的项目其实是4个，这里需要考虑到项目编号重复的情况（由于供应商每向一个项目供一种零件，数据库中都会产生相应的记录。）因此，对代码再进行修改。
SELECT SNAME,COUNT(JNO)
FROM S,SPJ
WHERE S.SNO=SPJ.SNO
GROUP BY SNAME
执行结果：
图1-2
运行结果说明：这里的执行结果已经可以统计出各供应商负责的项目数，但是由于没有考虑到一个供应商有可能存在多条供货记录，因此，这个结果依然不正确。
3）对代码添加关键字“DISTINCT”可不统计重复的字段。
SELECT SNAME,COUNT(DISTINCT JNO)
FROM S,SPJ
WHERE S.SNO=SPJ.SNO
GROUP BY SNAME
ORDER BY COUNT(DISTINCT JNO) DESC
执行结果：
图1-3
这才是最终的正确结果。
2、查询重量比螺栓重的零件名称，零件重量（子查询或自身连接）
解法：子查询
解题思路：先查询螺栓的重量，再用这重量与零件表中的其他零件的重量进行比较。
1)
SELECT WEIGHT
FROM P
WHERE PNAME='螺栓'
图2-1螺栓的重量
2）将第一步查询中的重量作为外层查询的条件。
SELECT PNAME,WEIGHT
FROM P
WHERE WEIGHT>(
                SELECT WEIGHT
                FROM P
                WHERE PNAME='螺栓')
图2-2
执行结果说明：先执行内层查询，其返回结果就是螺栓的重量，用这个重量与外层查询中的零件重量进行比较，查询“WEIGHT”字段的值大于17的记录。得到如上结果。这里注意，在外层查询的WHERE字句中的比较字段与子查询的返回结果应该相同，此外，为了让查询层次明确，子查询部分最好缩进。
解法2：自身连接
SELECT A.PNAME,A.WEIGHT
FROM P A,P B
WHERE A.WEIGHT>B.WEIGHT AND B.PNAME='螺栓'
图2-2
说明：在自身连接时，需将一个表理解成两个表。在本题中，将P理解成A,B两表，用B表查询螺栓的重量，再用A表出重量比它重的零件信息。
3、查询P（零件）表中各种颜及其对应的零件种类的数目（分组和聚集查询）
SELECT COLOR,COUNT(PNO)
FROM P
GROUP BY COLOR
图3-1
说明：本题仅对零件信息进行查询，不涉及到项目和供应商，因此无需多表连接。
但是请大家注意分析题目的要求：求各种颜的零件各有几类。即，题目要查询的是“红的零件有几类，绿的零件有几类”这样的信息，思考题目要求，不难发现这时需要用到分组，分组的依据就是颜。因此在GROUP BY 子句中用的是COLOR字段。
4、统计天津地区的项目使用零件的种数(超过3种)和零件总数量。要求查询结果按零件的种数升序排列，种数相同时按总数量降序排列。（连接、分组、聚集函数、排序）
1)本题需要分成两个步骤考虑。其一，考虑项目的编号，项目的名称，项目使用的零件（这里只要统计每个项目用的零件类别，因此只要零件号），每次使用的数量。因此，需要连接项目表（J）和供应关系表（SPJ）。并且需要指明项目所在的城市是天津。
SELECT J.JNO,JNAME,PNO,QTY
FROM J,SPJ
WHERE J.JNO=SPJ.JNO AND CITY='天津'
图4-1
由查询结果可知，天津地区只有‘弹簧厂’和‘造船厂’两个项目，用目测就知道前者使用了一种零件，后者有3种。按照题目要求，还需要进行分组。
2）统计零件类别数，用COUNT函数，统计总数量，则要用SUM
SELECT J.JNO,JNAME,COUNT(PNO),SUM(QTY)
FROM J,SPJ
WHERE J.JNO=SPJ.JNO AND CITY='天津'
GROUP BY J.JNO,JNAME
图4-2
5、查询既供应P1零件又供应P2零件的供应商编号，供应商名称（多重条件查询）
解题思路：题目要求查询供应商号和供应商名，这两项信息要通过供应商表（S）获得，同时，还需要供应的一些具体情况，这时就要用到SPJ。这题可以用子查询或连接查询实现。本例用子查询。
1） 先到SPJ表中查询供应’P1’零件的供应商号：
SELECT SNO FROM SPJ WHERE PNO='P1'
图5-1
2） 在再到SPJ表中查询供应’P2’零件的供应商号：
SELECT SNO FROM SPJ WHERE PNO='P2'
图5-2
3） 由以上两个步骤的查询结果可知，两种零件都有提供的供应商只有S1，本题求的是两个集合的交集。最后的查询是，用两个子查询获得同时提供两种零件的供应商号，再用此结果到供应商表（S）中获取相应供应商信息。
SELECT SNO,SNAME
FROM S
WHERE SNO IN(SELECT SNO FROM SPJ WHERE PNO='P1')
AND SNO IN(SELECT SNO FROM SPJ WHERE PNO='P2')
图5-3
思考：本题如果用以下语句是否可以实现
SELECT SNO,SNAME
FROM S
WHERE SNO IN(SELECT SNO FROM SPJ WHERE PNO='P1' AND PNO='P2')
6、查询每个供应商供应零件的情况，要求列出供应商名称，零件名称，供应数量，并按供应数量的升序排序（连接、分组、聚集函数、排序）
解题思路：
1）本题要求查询供应商名字、零件名称、数量，从题目要求的字段可以看出，本题需要连接供应商表（S），零件表（P）和供应情况表（SPJ）
SELECT SNAME,PNAME,JNO,QTY
FROM S,P,SPJ
WHERE S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO
图6-1
这个查询步骤显示供应商名，零件名，项目号，以及这次供应零件的数量。按照题目的要求，要统计每个供应商提供的某一类零件的总数量，比如，“精益”供应的螺母总量是200+100+700+100.
2）还需要用供应商名和零件名共同分组,并排序。
SELECT SNAME,PNAME,SUM(QTY) '供应数量'