实验六多表查询doc.docx

实验六多表查询doc.docx

《实验六多表查询doc.docx》由会员分享，可在线阅读，更多相关《实验六多表查询doc.docx（13页珍藏版）》请在冰豆网上搜索。

实验六多表查询doc

实验六多表查询

实验目的

1、掌握嵌套查询的概念

2、掌握连接查询

3、进一步掌握分组操作

4、掌握Union的使用

实验环境

硬件环境：

PC机一台

软件环境：

SQLServer2008/2005

实验内容

在供应商数据库中

供应商关系：

S（SNO，SNAME，ADDR）

零件关系：

P（PNO，PNAME，COLOR，WEIGHT）

工程项目关系：

J（JNO,JNAME，CITY，BALANCE）

供应情况关系：

SPJ（SNO,PNO,JNO，PRICE，QTY）

上述各属性的含义是：

供应商号（SNO）、供应商名（SNAME）和地址（ADDR），零件号（PNO）、零件名（PNAME）、颜色（COLOR）、重量（WEIGHT）、单价（PRICE）、工程项目号（JNO）、工程项目名称（JNAME）、城市（CITY）、余额（BALANCE）、供应数量（QTY）。

1、查询供应商的名字，以及各自负责的项目数，结果要求按照项目数的降序排列（连接、分组、排序）

Selectsname,count（distinctJNO）

FromS,SPj

WhereS.sno=SPJ.sno

Groupbysname

Selectsname,count（distinctJNO）

Froms,spj

WhereS.sno=spj.sno

Groupbysname

Orderbycount（distinctJNO）

2、查询重量比螺栓重的零件名称，零件重量（子查询或自身连接）

Selectpname,weight

FromP

Whereweight>

（Selectweight

FromP

Wherepname=‘螺栓’）

Selecta.name,a.weight

fromPa,Pb

wherea.weight>b.weight

andb.pname=’螺栓’

3、查询P（零件）表中各种颜色及其对应的零件种类的数目（分组和聚集查询）

Selectcolor,count（PNO）

FromP

Groupbycolor

4、统计天津地区的项目使用零件的种数（超过3种）和零件总数量。

要求查询结果按零件的种数升序排列，种数相同时按总数量降序排列。

（连接、分组、聚集函数、排序）

SelectJNO,count（PNO），sum（qty）

FromJ,SPJ

WhereJ.jno=spj.jnoandcity=’天津’

GroupbyJNO

Havingcount（PNO）>3

5、查询既供应P1零件又供应P2零件的供应商编号，供应商名称（多重条件查询）

6、查询每个供应商供应零件的情况，要求列出供应商名称，零件名称，供应数量，并按供应数量的升序排序（连接、分组、聚集函数、排序）

7、查询与“万胜”供应商在同一城市的供应商的详细资料（子查询）

8、查询供应商品种类最多的供应商编号、供应商名。

9、查询既生产螺母，也生产螺栓的供应商编号和供应商名。

10、查询生产螺丝刀但不生产螺母的供应商名

11、查询“万胜”和“精益”两个供应商的供货情况，查询结果中包括供应商名、项目名、零件名和供应数量。

用两个select语句实现查询，并用union将两个语句的查询结果合并在一起。

三、实验步骤

1、对文件夹中的‘SPJ’数据库进行附加还原。

2、按照以上要求完成题目。

3、要求写出查询代码并将查询结果截图附在代码后。

参考解答

1、查询供应商的名字，以及各自负责的项目数，结果要求按照项目数的降序排列（连接、分组、排序）

解题思路：

1）本题要查询供应商名，这项数据仅在供应商表（S）中才有，而要统计每个供应商向几个项目供货，则需要对SPJ表中的数据进行统计。

这里需要对S和SPJ进行连接，两表的共同字段是SNO，用该字段进行连接。

SELECTSNAME,JNO,PNO

FROMS,SPJ

WHERES.SNO=SPJ.SNO

代码执行结果：

图1-1

说明：

该结果表明，这些供应商向项目提供零件的信息，这里项目和零件都用编号表示。

2）这还没有达到题目要求的效果。

题目要求统计每个供应商负责的项目数。

这里需要分组。

用供应商名进行分组，计算每个组中项目号的数目。

这里请注意，“万胜”这个供应商的供应记录有6条，但是仔细看，不难发现，他供应的项目其实是4个，这里需要考虑到项目编号重复的情况（由于供应商每向一个项目供一种零件，数据库中都会产生相应的记录。

）因此，对代码再进行修改。

SELECTSNAME,COUNT（JNO）

FROMS,SPJ

WHERES.SNO=SPJ.SNO

GROUPBYSNAME

执行结果：

图1-2

运行结果说明：

这里的执行结果已经可以统计出各供应商负责的项目数，但是由于没有考虑到一个供应商有可能存在多条供货记录，因此，这个结果依然不正确。

3）对代码添加关键字“DISTINCT”可不统计重复的字段。

SELECTSNAME,COUNT（DISTINCTJNO）

FROMS,SPJ

WHERES.SNO=SPJ.SNO

GROUPBYSNAME

ORDERBYCOUNT（DISTINCTJNO）DESC

执行结果：

图1-3

这才是最终的正确结果。

2、查询重量比螺栓重的零件名称，零件重量（子查询或自身连接）

解法：

子查询

解题思路：

先查询螺栓的重量，再用这重量与零件表中的其他零件的重量进行比较。

1）

SELECTWEIGHT

FROMP

WHEREPNAME='螺栓'

图2-1螺栓的重量

2）将第一步查询中的重量作为外层查询的条件。

SELECTPNAME,WEIGHT

FROMP

WHEREWEIGHT>（

SELECTWEIGHT

FROMP

WHEREPNAME='螺栓'）

图2-2

执行结果说明：

先执行内层查询，其返回结果就是螺栓的重量，用这个重量与外层查询中的零件重量进行比较，查询“WEIGHT”字段的值大于17的记录。

得到如上结果。

这里注意，在外层查询的WHERE字句中的比较字段与子查询的返回结果应该相同，此外，为了让查询层次明确，子查询部分最好缩进。

解法2：

自身连接

SELECTA.PNAME,A.WEIGHT

FROMPA,PB

WHEREA.WEIGHT>B.WEIGHTANDB.PNAME='螺栓'

图2-2

说明：

在自身连接时，需将一个表理解成两个表。

在本题中，将P理解成A,B两表，用B表查询螺栓的重量，再用A表找出重量比它重的零件信息。

3、查询P（零件）表中各种颜色及其对应的零件种类的数目（分组和聚集查询）

SELECTCOLOR,COUNT（PNO）

FROMP

GROUPBYCOLOR

图3-1

说明：

本题仅对零件信息进行查询，不涉及到项目和供应商，因此无需多表连接。

但是请大家注意分析题目的要求：

求各种颜色的零件各有几类。

即，题目要查询的是“红色的零件有几类，绿色的零件有几类”这样的信息，思考题目要求，不难发现这时需要用到分组，分组的依据就是颜色。

因此在GROUPBY子句中用的是COLOR字段。

4、统计天津地区的项目使用零件的种数（超过3种）和零件总数量。

要求查询结果按零件的种数升序排列，种数相同时按总数量降序排列。

（连接、分组、聚集函数、排序）

1）本题需要分成两个步骤考虑。

其一，考虑项目的编号，项目的名称，项目使用的零件（这里只要统计每个项目用的零件类别，因此只要零件号），每次使用的数量。

因此，需要连接项目表（J）和供应关系表（SPJ）。

并且需要指明项目所在的城市是天津。

SELECTJ.JNO,JNAME,PNO,QTY

FROMJ,SPJ

WHEREJ.JNO=SPJ.JNOANDCITY='天津'

图4-1

由查询结果可知，天津地区只有‘弹簧厂’和‘造船厂’两个项目，用目测就知道前者使用了一种零件，后者有3种。

按照题目要求，还需要进行分组。

2）统计零件类别数，用COUNT函数，统计总数量，则要用SUM

SELECTJ.JNO,JNAME,COUNT（PNO）,SUM（QTY）

FROMJ,SPJ

WHEREJ.JNO=SPJ.JNOANDCITY='天津'

GROUPBYJ.JNO,JNAME

图4-2

5、查询既供应P1零件又供应P2零件的供应商编号，供应商名称（多重条件查询）

解题思路：

题目要求查询供应商号和供应商名，这两项信息要通过供应商表（S）获得，同时，还需要供应的一些具体情况，这时就要用到SPJ。

这题可以用子查询或连接查询实现。

本例用子查询。

1）先到SPJ表中查询供应’P1’零件的供应商号：

SELECTSNOFROMSPJWHEREPNO='P1'

图5-1

2）在再到SPJ表中查询供应’P2’零件的供应商号：

SELECTSNOFROMSPJWHEREPNO='P2'

图5-2

3）由以上两个步骤的查询结果可知，两种零件都有提供的供应商只有S1，本题求的是两个集合的交集。

最后的查询是，用两个子查询获得同时提供两种零件的供应商号，再用此结果到供应商表（S）中获取相应供应商信息。

SELECTSNO,SNAME

FROMS

WHERESNOIN（SELECTSNOFROMSPJWHEREPNO='P1'）

ANDSNOIN（SELECTSNOFROMSPJWHEREPNO='P2'）

图5-3

思考：

本题如果用以下语句是否可以实现

SELECTSNO,SNAME

FROMS

WHERESNOIN（SELECTSNOFROMSPJWHEREPNO='P1'ANDPNO='P2'）

6、查询每个供应商供应零件的情况，要求列出供应商名称，零件名称，供应数量，并按供应数量的升序排序（连接、分组、聚集函数、排序）

解题思路：

1）本题要求查询供应商名字、零件名称、数量，从题目要求的字段可以看出，本题需要连接供应商表（S），零件表（P）和供应情况表（SPJ）

SELECTSNAME,PNAME,JNO,QTY

FROMS,P,SPJ

WHERES.SNO=SPJ.SNOANDP.PNO=SPJ.PNO

图6-1

这个查询步骤显示供应商名，零件名，项目号，以及这次供应零件的数量。

按照题目的要求，要统计每个供应商提供的某一类零件的总数量，比如，“精益”供应的螺母总量是200+100+700+100.

2）还需要用供应商名和零件名共同分组,并排序。

SELECTSNAME,PNAME,SUM（QTY）'供应数量'

FROMS,P,SPJ

WHERES.SNO=SPJ.SNOANDP.PNO=SPJ.PNO

GROUPBYSNAME,PNAME

ORDERBYSUM（QTY）

图6-2

7、查询与“万胜”供应商在同一城市的供应商的详细资料（子查询）

解题思路：

本题用子查询，先查询“万胜”所在的城市：

然后用子查询（内层查询）的返回结果作为外层查询的条件。

1）查询“万胜”的城市

SELECTCITYFROMSWHERESNAME='万胜'

图7-1

2）用第一步中的结果作为外层查询的条件。

SELECT*FROMS

WHERECITY=（SELECTCITYFR