MQL实验（二）作业

1.求供应工程J1零件的供应商号码SNO，输出结果不重复。

select distinct sno from spj where jno='j1';

2.求供应工程J1零件P1的供应商号码SNO，输出结果不重复。

select distinct sno from spj where jno='j1' and pno='p1';

3.求供应工程J1零件为红色的供应商号码SNO，输出结果不重复。

select distinct sno from p join spj on p.pno=spj.pno where color='红'and jno='j1';

4.查询各供应商供应各个项目各种零件的数据，列出SNO，SNAME，PNO，PNAME，JNO，JNAME，QTY属性取值

select distinct s.sno,s.sname ,p.pno,p.pname,spj.jno,j.jname,spj.qty from spj -- 四表链接
inner join s on spj.sno=s.sno -- inner可以省略不写
inner join p on spj.pno=p.pno-- 写注释的时候‘--’后要空一格再写
inner join j on j.jno=spj.jno;-- spj.jno=j.jno也可以

5.查询每个供应商代码及其提供零件数量均值，输出SNO，AVGQTY(四舍五入取整)。

select sno,round(avg(qty),0) from spj group by sno;

取整函数ROUND(要四舍五入的数，保留小数点后几位小数)

6.求每个供应商超过其提供零件数量均值的零件号，输出SNO，PNO（输出内容不重复）。

select distinct sno,pno from spj where qty>(select avg(qty) from spj )

带有比较运算符的嵌套子查询：where qty>(select avg(qty) from spj )   

7.查询各供应商供应的各种零件数量总和，输出sno,pno,sumqty。

select sno,pno,sum(qty) from spj group by sno,pno;

sum函数：select sno,pno,sum(qty)

8.找出工程项目J2使用的各种零件的名称pname、数量qty、及供应商名称sname。

select pname,qty,sname from spj join s on spj.sno=s.sno join p on spj.pno=p.pno
where jno='j2';

9.求所有项目的供应商名称（如项目无供应商，则输出‘无供应商’），输出jno,sname，输出结果不能重复。

SELECT DISTINCT j.jno, ifnull(S.SNAME, '无供应商') 
FROM j left join spj on j.jno=spj.jno 
left join s on spj.sno=s.sno;

10.求没有使用天津供应商生产的红色零件的工程号JNO，输出结果不重复。

SELECT DISTINCT j.jno
FROM j
WHERE j.jno NOT IN (SELECT DISTINCT j.jno 
    FROM s,p,j,spj
    WHERE s.sno = spj.sno 
    AND p.pno = spj.pno 
    AND j.jno = spj.jno 
    AND p.color = '红' 
    AND s.city = '天津');

11.查询既使用了供应商S1又使用了供应商S2供应的零件的项目号JNO，输出不能重复。

select distinct jno 
from spj 
where sno IN ('s1','s2') GROUP BY JNO
HAVING COUNT(DISTINCT SNO)=2;

HAVING COUNT(DISTINCT SNO)=2;

12.由S5供给J4的零件P6改为由S3供应；用select * from spj where jno= 'J4' and pno= 'P6'命令显示更新结果。

UPDATE SPJ
SET SNO='S3'
WHERE SNO='S5' AND JNO='J4' AND PNO='P6';
select * from spj where jno= 'J4' and pno= 'P6';

13.将供应商名“精益”供应的所有零件的数量增加10%，用Select * from spj命令显示更新结果。

UPDATE SPJ
SET QTY=QTY*1.1
WHERE SNO IN (SELECT SNO FROM S WHERE SNAME='精益');
Select * from spj;

14.根据spj表统计使用的零件种类数目超过（含）2种的项目及其使用的零件种类数，将结果存入临时表temp_result中。temp_result临时表包括项目号jno及种类数category_num两个属性列，用Select * from temp_result显示结果。

CREATE TEMPORARY TABLE temp_result(
    jno long,
    category_num int);
    

INSERT INTO temp_result(jno,category_num)
SELECT SPJ.JNO AS JNO,COUNT(DISTINCT SPJ.PNO)AS category_num
FROM SPJ GROUP BY SPJ.JNO
HAVING COUNT(DISTINCT SPJ.PNO)>=2;
Select * from temp_result;

INSERT INTO temp_result(jno,category_num)

15.删除所有使用过“齿轮”的项目，用Select * from j命令显示更新结果。

delete from j where jno in
(select jno from spj where pno in
 (select pno from p where pname='齿轮'));
Select * from j;