实验六.-多表查询doc
实验六 多表查询
实验目的
1、 掌握嵌套查询的概念
2、 掌握连接查询
3、 进一步掌握分组操作
4、 掌握Union的使用
实验环境
硬件环境：PC机一台
软件环境：SQL Server 2008/2005
实验内容
在供应商数据库中
上述各属性的含义是：供应商号(SNO)、供应商名(SNAME)和地址(ADDR)，零件号(PNO)、零件名（PNAME）、颜色（COLOR）、重量（WEIGHT）、单价(PRICE)、工程项目号(JNO)、工程项目名称(JNAME)、城市(CITY)、余额（BALANCE）、供应数量(QTY)。
供应商
m
供应pn
项目价格供应量零件
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（PNO）
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
执行结果：
GROUP BY SNAME
ORDER BY COUNT(DISTINCT JNO) DESC
执行结果：
'
图2-1螺栓的重量
2）将第一步查询中的重量作为外层查询的条件。
SELECT PNAME,WEIGHT
FROM P
WHERE WEIGHT>(
SELECT WEIGHT
FROM P
WHERE PNAME='螺栓'
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
FROM J,SPJ
WHERE J.JNO=SPJ.JNO AND CITY='天津
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=
)
图5-3
思考：本题如果用以下语句是否可以实现
SELECT SNO,SNAME
第11 / 18页
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) '供应数量'
FROM S,P,SPJ
WHERE S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO
GROUP BY SNAME,PNAME
ORDER BY SUM(QTY
图7-1
2)用第一步中的结果作为外层查询的条件。
SELECT * FROM S
WHERE CITY=(SELECT CITY FROM S WHERE SNAME='万胜') AND SNAME!='万胜'
FROM S,SPJ
WHERE S.SNO=SPJ.
SNO
图8-1
2）对第一步中的查询结果进行目测，可以得知，S1供应2种零件，S2供应2种，所以，这里要用供应商进行分组，统计每组的零件类别数。
SELECT DISTINCT S.SNO,SNAME,COUNT(DISTINCT PNO) FROM S,SPJ
WHERE S.SNO=SPJ.SNO
GROUP BY S.SNO,
FROM S,SPJ
WHERE S.SNO=SPJ.SNO
GROUP BY S.SNO,SNAME
HAVING COUNT(DISTINCT PNO)>=ALL(SELECT
COUNT(DISTINCT PNO)
FROM S,SPJ
WHERE S.SNO=SPJ.SNO
GROUP BY S.SNO,SNAME)
第15 / 18页
http://www.99jianzhu.com/包含内容：PDF/word/ppt 流程，表格，案例，最新作文 数学 英语 考试题库等内容免费下载。