实验六多表查询doc.docx
《实验六多表查询doc.docx》由会员分享,可在线阅读,更多相关《实验六多表查询doc.docx(16页珍藏版)》请在冰豆网上搜索。
![实验六多表查询doc.docx](https://file1.bdocx.com/fileroot1/2023-5/29/13e3ffa8-2ec1-4e0d-9799-7081e34a252e/13e3ffa8-2ec1-4e0d-9799-7081e34a252e1.gif)
实验六多表查询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=(SELECTCITYFROMSWHERESNAME='万胜')
ANDSNAME!
='万胜'
图7-2
题目要求查询供应商的信息,这就包括了供应商号、供应商名、供应商所在城市这些信息。
所以在SELECT子句中用‘*’替代所有的字段。
另外,题目要插叙你的目标应该是万胜之外的其他供应商,因此应该用“SNAME!
='万胜'”这个条件排除万胜这个供应商自身的信息。
代码的执行结果如上图所示。
8、查询供应商品种类最多的供应商编号、供应商名。
解题思路:
本题即要查询供应商编号,又要供应商的名字和供应的零件类别数,因此,本题需要连接供应商表(S)和供应关系表(SPJ)。
1)先进行连接查询,获取每个供应商名及其供应的零件编号,并用DISTINCT关键字消除重复的记录。
SELECTDISTINCTS.SNO,SNAME,PNO
FROMS,SPJ
WHERES.SNO=SPJ.SNO
图8-1
2)对第一步中的查询结果进行目测,可以得知,S1供应2种零件,S2供应2种,所以,这里要用供应商进行分组,统计每组的零件类别数。
SELECTDISTINCTS.SNO,SNAME,COUNT(DISTINCTPNO)
FROMS,SPJ
WHERES.SNO=SPJ.SNO
GROUPBYS.SNO,SNAME
图8-2
3)由第2步的执行结果可知,S1,S2,S3是供应零件种类最多的供应商,现在需要再对代码进行改进,导出数量最多的三个供应商。
SELECTDISTINCTS.SNO,SNAME
FROMS,SPJ
WHERES.SNO=SPJ.SNO
GROUPBYS.SNO,SNAME
HAVINGCOUNT(DISTINCTPNO)>=ALL(SELECTCOUNT(DISTINCTPNO)
FROMS,SPJ
WHERES.SNO=SPJ.SNO
GROUPBYS.SNO,SNAME)
图8-3
在第三步中,在HAVING子句中使用子查询,获取每个供应商提供的零件类别数,外层查询的逻辑与内层查询相同,如果满足‘>=ALL’这一条件,则是供应种类数目最多的供应商。
这个部分需要各位同学多花时间好好理解。
9、查询既生产螺母,也生产螺栓的供应商编号和供应商名。
解题思路:
本题求的是两个集合的交集。
一个集合是上次螺母的集合,另一个集合是生产螺栓的集合。
先编写语句,分别求生产螺母的供应商号和生产螺栓的供应商号。
1)先分别从零件表(P)中查询螺母、螺栓的编号,然后用此编号到供应关系(SPJ)中查询有提供此商品的供应商号(SNO)
SELECTDISTINCTSNOFROMSPJ
WHEREPNOIN(SELECTPNOFROMPWHEREPNAME='螺母')
图9-1
SELECTDISTINCTSNOFROMSPJ
WHEREPNOIN(SELECTPNOFROMPWHEREPNAME='螺栓')
图9-2
2)从查询结果可知,这两个集合的交集是‘S1’,因此语句可以修改为以下代码:
SELECTSNO,SNAMEFROMS
WHERESNOIN(
SELECTDISTINCTSNOFROMSPJ
WHEREPNOIN(SELECTPNOFROMPWHEREPNAME='螺母')
)
ANDSNOIN(
SELECTDISTINCTSNOFROMSPJ
WHEREPNOIN(SELECTPNOFROMPWHEREPNAME='螺栓')
)
图9-3
注意,在这里,两个条件之间是‘且’的关系,必须用‘AND’连接。
10、查询生产螺丝刀但不生产螺母的供应商名
解题思路:
本题的思路与第9题类似,也是集合运算,但是它的要求是要在螺丝刀的生产商中扣除生产螺母的产商。
代码如下:
SELECTSNAME
FROMS
WHERESNOIN(SELECTSNOFROMSPJ
WHEREPNOIN(SELECTPNOFROMPWHEREPNAME='螺丝刀'))
ANDSNONOTIN(
SELECTDISTINCTSNOFROMSPJ
WHEREPNOIN(SELECTPNOFROMPWHEREPNAME='螺母')
)
图10-1