SQL 查询

最新推荐文章于 2020-05-07 10:45:18 发布

cybbsc

最新推荐文章于 2020-05-07 10:45:18 发布

阅读量234

点赞数

分类专栏：随笔

本文链接：https://blog.csdn.net/ACM2017/article/details/83115191

版权

随笔专栏收录该内容

3 篇文章 0 订阅

订阅专栏

接上例
（1）求供应工程J1零件的供应商号码SNO；

Select Sno from SPJ where Jno='J1'

在这里插入图片描述
作业未竟，后续补充……

----------------------我是分割线----------------------
（2）

Select Sno 
From SPJ 
Where Jno='J1' and Pno='P1'

（3）

Select Sno 
From SPJ,P 
Where Jno='J1' and P.Pno=SPJ.Pno and P.COLOR='红'

（4）

Select J.Jno 
From J
Where J.Jno Not In (
Select SPJ.Jno 
From S, SPJ, P
Where S.City = '天津' and S.Sno = SPJ.Sno and SPJ.Pno = P.Pno and P.Color = '红'
	)

（5）


	
Select Distinct Jno
From SPJ SPJa
Where Not Exists (
	Select *
	From SPJ SPJb
	Where SPJb.Sno = 'S1' and NOT Exists (
		Select *
		From SPJ SPJc
		Where SPJc.Jno = SPJa.Jno and SPJc.Pno=SPJb.Pno
	)
)
-------------------------------------------------------------------------	
Create View SP(Pno)
As
	Select Distinct Pno 
	From SPJ
	Where SPJ.Sno = 'S1'

Select Distinct Jno
From SPJ SPJ1
Where Not Exists(
	Select *
	From SP
	Where Not Exists(
		Select *
		From SPJ SPJ2
		Where SPJ2.Jno = SPJ1.Jno and SPJ2.Pno = SP.Pno
	)
)