（1）查询所有供应商情况，先按城市升序排列， 城市相同按供应商名称降序排列。
select * from s order by city ASC , sname DESC;
（2）查询所有零件情况， 先按零件名称升序排列， 零件名称相同按重量降序排列。select * from p order by pname ASC , weight DESC;
（3）查询项目名中含有“厂” 的项目情况。
select * from j where jname like '%厂%';
（4）查询供应商名称中第二个字为“方”的供应商情况。
select * from s where sname like '_方%';
（5）查询所有零件中的最大、最小、平均重量。
select min(weight) as minweight, max(weight) as maxweight ,avg(weight) as avgweight from p ;
（6）查询零件中名为“螺丝刀”的零件的种类数、平均重量。
select count(distinct pname),avg(weight) as avgweight from p ;
（7）查询供应商 S1 所供应的各种零件的名称和数量。
SELECT P.pname, SUM(SPJ.qty) AS total_quantity
FROM P
JOIN SPJ ON P.pno = SPJ.pno
JOIN S ON SPJ.sno = S.sno
WHERE S.sno = 'S1'
GROUP BY P.pname;
（8）查询工程 J1 所使用的各种零件的名称和数量。
SELECT P.pname, SUM(SPJ.qty) AS total_quantity
FROM P
JOIN SPJ ON P.pno = SPJ.pno
JOIN J ON SPJ.jno = J.jno
WHERE J.jno = 'J1'
GROUP BY P.pname;
（9）查询没有使用红色螺丝刀的工程名称。
select distinct J.jname
from J
join spj on J.jno=spj.jno
join p on p.pno = spj.pno
where p.pname!='螺丝刀' or p.color!='红';
（10）查询没有供应红色螺丝刀的供应商名称。
select distinct S.sname
from S
join spj on S.sno=spj.sno
join p on p.pno = spj.pno
where p.pname!='螺丝刀' or p.color!='红'
（11）查询所用零件数量超过 500 的工程项目号。
select distinct J.jno
from J
join spj on J.jno=spj.jno
where (select sum(spj.qty)
from spj
where J.jno = spj.jno
)> 500;
或者
select jno
from spj
group by jno
having sum(qty)>500;
;
（12）查询所用零件种类超过 3 种的工程项目名称。
select J.jname
from J
join spj on J.jno = spj.jno
group by J.jname
having count(distinct spj.pno)>3;
（13）查询使用了全部零件的工程项目名称。
select J.jname
from J
join spj on J.jno = spj.jno
group by J.jname
having count(distinct spj.pno)=6;
（14）查询至少供应了工程 J1 所使用的全部零件的供应商名称。
SELECT JNO
FROM SPJ SPJX
WHERE NOT EXISTS
(
SELECT *
FROM SPJ SPJY
WHERE SPJY.SNO='S1' AND NOT EXISTS
(
SELECT *
FROM SPJ SPJZ
WHERE SPJX.JNO=SPJZ.JNO AND SPJY.PNO=SPJZ.PNO
)
) ;
（15）查询供应情况，显示内容为供应商名称、零件名、工程名称、数量。
SELECT S.sname AS 供应商名称,
P.pname AS 零件名,
J.jname AS 工程名称,
SPJ.qty AS 数量
FROM SPJ
JOIN S ON SPJ.sno = S.sno
JOIN P ON SPJ.pno = P.pno
JOIN J ON SPJ.jno = J.jno;
（16）查询“东方红”供应商供应情况， 显示供应的零件名、工程名称、数量。
SELECT P.pname AS 零件名,
J.jname AS 工程名称,
SPJ.qty AS 数量
FROM SPJ
JOIN S ON SPJ.sno = S.sno
JOIN P ON SPJ.pno = P.pno
JOIN J ON SPJ.jno = J.jno
WHERE S.sname = '东方红';
标签：零件,数据库,spj,SPJ,实验,pno,jno,select From： https://www.cnblogs.com/galileo9527/p/18236630