5.17

今天上数据库原理，实验二是给出数据后对数据进行多方面的查询。

4、在数据库 test1 中进行下列查询操作，将查询语句与结果写入实验报告。

（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 concat('%','厂','%');

（4）查询供应商名称中第二个字为“方”的供应商情况。

select * from s where sname like concat('_','方','%');

（5）查询所有零件中的最大、最小、平均重量。

select max(weight),min(weight),avg(weight) from p ;

（6）查询零件中名为“螺丝刀”的零件的种类数、平均重量。

select count(*),avg(weight) from p where pname='螺丝刀';

（7）查询供应商 S1 所供应的各种零件的名称和数量。

select   p.pname,sum(qty) from spj join p on spj.pno=p.pno  where sno='s1' group by spj.pno,p.pname;

（8）查询工程 J1 所使用的各种零件的名称和数量。

select p.pname,sum(qty) from spj join p on spj.pno=p.pno where jno='j1'group by spj.pno, p.pname;

（9）查询没有使用红色螺丝刀的工程名称。

select jname from j where jno IN (select jno from spj where pno NOT IN (select pno from p where pname='螺丝刀' and color='红') group by jno);

（10）查询没有供应红色螺丝刀的供应商名称。

select sname from s where sno in(select sno from spj where pno not in(select pno from p where pname='螺丝刀' and color ='红') group by sno);

（ 11）查询所用零件数量超过 500 的工程项目号。

select jno from spj  group by jno having sum(qty)>500;

（ 12）查询所用零件种类超过 3 种的工程项目名称。

select jname from j where jno in(select jno from (select jno,count(distinct pno) as sum from spj group by jno)as subquery where sum >3);

（ 13）查询使用了全部零件的工程项目名称。

select jname from j where jno in(select jno from (select jno,count(distinct pno) as sum from spj group by jno) as subquery where sum=(select count(*) from p));

（ 14）查询至少供应了工程 J1 所使用的全部零件的供应商名称。

SELECT s.sname FROM spj JOIN p ON spj.pno = p.pno JOIN s ON spj.sno = s.sno WHERE NOT EXISTS ( SELECT * FROM spj WHERE spj.jno = 'j1' AND spj.pno NOT IN ( SELECT pno FROM spj WHERE jno = 'j1' ) AND spj.sno = s.sno )

（ 15）查询供应情况，显示内容为供应商名称、零件名、工程名称、数量。

select s.sname,p.pname,j.jname,spj.qty from spj join j on spj.jno=j.jno join s on spj.sno=s.sno join p on spj.pno =p.pno;

（ 16）查询“东方红”供应商供应情况，显示供应的零件名、工程名称、数量。

select p.pname,j.jname,spj.qty from spj join j on spj.jno=j.jno join s on spj.sno=s.sno join p on spj.pno =p.pno where sname='东方红';