10-97 spj-显示每个工程项目使用零件的供应列表

本题目要求编写SQL语句，在SPJ数据库中，显示每个工程项目所使用的提供零件数量少于100的供应列表，如工程项目尚未有供应商提供零件的也要列出。输出结果集以工程项目jno升序排序。

提示：请使用SELECT语句作答。

表结构:

请在这里写定义表结构的SQL语句。例如：

CREATE TABLE `j` (  --  工程项目表
  `jno` char(3) NOT NULL,-- 工程项目号
  `jname` varchar(10) DEFAULT NULL,
  `city` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`jno`)
);
CREATE TABLE `p` (  -- 零件表
  `pno` char(3) NOT NULL,  -- 零件号
  `pname` varchar(10) DEFAULT NULL,
  `color` char(2) DEFAULT NULL,
  `weight` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`pno`)
);
CREATE TABLE `s` (  -- 供应商表
  `sno` char(3) NOT NULL,  -- 供应商号
  `sname` varchar(10) DEFAULT NULL,
  `status` char(2) DEFAULT NULL,
  `city` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`sno`)
);
CREATE TABLE `spj` (  -- 零件供应表
  `sno` char(3) NOT NULL,
  `pno` char(3) NOT NULL,
  `jno` char(3) NOT NULL,
  `qty` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`sno`,`pno`,`jno`),
  CONSTRAINT `fk_jno` FOREIGN KEY (`jno`) REFERENCES `j` (`jno`),
  CONSTRAINT `fk_pno` FOREIGN KEY (`pno`) REFERENCES `p` (`pno`),
  CONSTRAINT `fk_sno` FOREIGN KEY (`sno`) REFERENCES `s` (`sno`)
);

 

 

表样例

请在这里给出上述表结构对应的表样例。例如

s表：

p表：

j表：

spj表：

输出样例:

请在这里给出输出样例。例如：

select a.jno,jname,sname,pname,qty
from (select spj.jno,jname
from j,spj
where j.jno=spj.jno and qty<100) a inner join (select spj.jno,sname
from s,spj
where s.sno=spj.sno and qty<100) b on b.jno=a.jno
inner join (select spj.jno,pname,qty
from p,spj
where p.pno=spj.pno and qty<100) c on c.jno=a.jno
union
select jno,jname,NULL as sname,NULL as pname,0 as qty
from j
where jno not in (select distinct jno
from spj)
order by jno asc