数据库(SQL Server )经典例题(三):对S表、P表、J表、SPJ表的操作——多表查询

一、运行环境说明
1、电脑环境:win10
2、数据库软件:SQL Server 2008 R2

二、经典例题原型
题目:设有一个 SPJ 数据库,包括 S、P、J 及 SPJ 4 个关系模式:


    S (SNO,SNAME,STATUS,CITY) ;
    P (PNO,PNAME,COLOR,WEIGHT) ;
    J (JNO,JNAME,CITY) ;
    SPJ (SNO,PNO,JINO,QTY) ;

应商表 S 由供应商代码(SNO)、供应商姓名(SNAME)、供应商状态(STATUS)、供应商所在城市(CITY)组成。

零件表P由零件代码(PNO)、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成。

工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成。

供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成,表示某供应商供应某种零件给某工程项目的数量为QTY。
在这里插入图片描述

三、经典例题
如果某个小题用连接查询和嵌套查询均能完成,要求写出两种查询命令。
1、查询每种颜色的零件中重量大于等于该颜色零件平均重量的零件信息;
命令:

SELECT *  --嵌套查询
FROM P M
WHERE WEIGHT>=(SELECT AVG(WEIGHT) FROM P N WHERE M.COLOR=N.COLOR)

SELECT p.*  --连接查询
FROM P,(SELECT COLOR,AVG(WEIGHT) PING FROM P GROUP BY COLOR) Q
WHERE P.COLOR=Q.COLOR AND P.WEIGHT>=Q.PING  

2、查询工程J1使用的数量最大的零件号及数量;
命令:

SELECT PNO,MAX(QTY) MAX  
FROM SPJ 
WHERE QTY=(SELECT MAX(QTY) MAX FROM SPJ WHERE JNO='J1')
GROUP BY PNO;

3、查询使用了所有零件的工程号。
命令:

SELECT JNO
FROM J
WHERE NOT EXISTS
(SELECT *
FROM P
WHERE NOT EXISTS
(SELECT *
FROM SPJ
WHERE SPJ.PNO = P.PNO AND J.JNO = SPJ.JNO))

4、查询给所有工程都供应了零件的供应商信息。
命令:

SELECT *
FROM S
WHERE NOT EXISTS
(SELECT *
FROM J
WHERE NOT EXISTS
(SELECT *
FROM SPJ
WHERE SPJ.JNO = J.JNO AND S.SNO = SPJ.SNO))

5、查询至少使用了供应商S1供应的全部零件的工程号。
命令:

SELECT DISTINCT JNO 
FROM SPJ M
WHERE NOT EXISTS
(SELECT * FROM SPJ N WHERE N.SNO='S1' AND NOT EXISTS
(SELECT * FROM SPJ Q WHERE Q.PNO=N.PNO AND Q.JNO=M.JNO));

6、查询至少使用了工程J3使用的全部零件的工程号。
命令:

SELECT DISTINCT JNO  --第六题查询成功
FROM SPJ M
WHERE NOT EXISTS
(SELECT * FROM SPJ N WHERE N.JNO='J3' AND NOT EXISTS
(SELECT * FROM SPJ Q WHERE Q.PNO=N.PNO AND Q.JNO=M.JNO));


未完,待续。不足之处请大家多多批评指正!!!如有借鉴请评论和点赞!!!

  • 10
    点赞
  • 82
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值