数据库实验二

（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 = '东方红';