- 查询每个工程及其使用零件的情况。
select distinct J.Jno,Jname,SPJ.pno
from J left join SPJ on J.Jno=SPJ.Jno;
- 查询所有供应商供应零件的情况。
select distinct S.Sno, Sname,SPJ.pno
from S left join SPJ on S.Sno=SPJ.Sno;
- 查询每个工程使用不同供应商的零件的个数。
select j.jname,sum(spj.qty)
from j left join spj
on j.jno = spj.jno
group by j.jname,spj.sno;
- 查询没有供应任何零件的供应商号,供应商名。(外连接)
select s.sno, sname
from s left join spj
on s.sno= spj.sno
where pno is null;
或者:
Select sno, sname
From s
Where sno not in ( select sno from spj)
- 查询没有使用天津生产的零件的工程号码。
select J.Jno
from J
where Jno not in (select Jno from S,SPJ where SPJ.Sno=S.Sno and S.City='天津');
- 查询未采用由北京供应者提供颜色为‘红色’零件的工程名。
select Jname
from J
where Jno not in (select Jno from SPJ,S,P where SPJ.Pno=P.Pno and SPJ.Sno=S.Sno and S.city='北京'and Color='红');
- 查询不使用零件代码为P2的工程号和工程名。
select J.Jno, J.Jname
from J
where Jno not in (select Jno from SPJ where Pno='P2');
错误方法:
select J.Jno, J.Jname
from J,SPJ,P
where SPJ.Pno=P.Pno and SPJ.Jno=J.Jno and P.Pno != 'P2';