--查询所有供应商情况，先按城市升序排列，城市相同按供应商名称降序排列
select * from j order by city asc, jname desc;
--查询所有零件情况，先按零件名称升序排列，零件名称相同按重量降序排列。
select * from p order by pname asc ,weight desc;
--查询项目名中含有“厂”的项目情况
select j.jno,jname,city,pname,qty from j inner join spj on j.jno=spj.jno join p on spj.pno=p.pno where jname like '%厂%';
--查询供应商名称中第二个字为“方”的供应商情况
select distinct s.Sno,Sname,status,city,qty from s inner join spj on s.Sno=spj.Sno where Sname like '_方%';
--查询所有零件中的最大、最小、平均重量。
select max(weight) as max,min(weight) as min, avg(weight) as avg from p;
--查询零件中名为“螺丝刀”的零件的种类数、平均重量
select count(pname) as sum,avg(weight) as avg from p Where pname='螺丝刀' ;
--查询供应商 S1 所供应的各种零件的名称和数量。
select distinct pname,sum(qty) as sum from spj join p on spj.pno=p.pno group by pname,sno having spj.sno='s1';
select pname,qty from spj inner join p on spj.pno=p.pno where spj.sno='s1';
--查询工程 J1 所使用的各种零件的名称和数量。
select p.pname,sum(qty) as sum from spj inner join p on spj.pno=p.pno where jno='j1' group by pname;
--查询没有使用红色螺丝刀的工程名称。
SELECT DISTINCT j.jname
FROM spj a
LEFT JOIN (
SELECT DISTINCT jno
FROM spj
WHERE pno = 'p4'
) b ON a.jno = b.jno
join j on a.jno=j.jno
WHERE b.jno IS NULL;
--查询没有供应红色螺丝刀的供应商名称。
SELECT DISTINCT s.sname
FROM spj a
LEFT JOIN (
SELECT DISTINCT sno
FROM spj
WHERE pno = 'p4'
) b ON a.sno = b.sno
join s on a.sno=s.sno
WHERE b.sno IS NULL;
--查询所用零件数量超过 500 的工程项目号。
select jno from spj group by jno having sum(qty)>500;
--查询所用零件种类超过 3 种的工程项目名称。
select j.jname from spj inner join j on spj.jno=j.jno group by j.jname having count(distinct pno)>3;
--查询使用了全部零件的工程项目名称。
select jname from spj inner join j on spj.jno=j.jno group by j.jname having count(distinct pno)=6;
--查询至少供应了工程 J1 所使用的全部零件的供应商名称。
-- 使用JOIN替代子查询的复杂方法
SELECT s.sname
FROM s -- 供应商表
JOIN (
SELECT sno -- 找出供应了所有J1工程零件的供应商编号
FROM spj AS spj_j1
WHERE spj_j1.jno = 'J1'
GROUP BY sno
HAVING COUNT(DISTINCT spj_j1.pno) = (
SELECT COUNT(DISTINCT pno) -- 计算J1工程使用的零件总数
FROM spj
WHERE jno = 'J1'
)
) AS suppliers_with_all_parts ON s.sno = suppliers_with_all_parts.sno;
--查询供应情况，显示内容为供应商名称、零件名、工程名称、数量。
select sname,pname,jname,qty
from spj
join p on spj.pno=p.pno
join j on spj.jno=j.jno
join s on spj.sno=s.sno;
--查询“东方红”供应商供应情况，显示供应的零件名、工程名称、数量。
SELECT p.pname, j.jname, spj.qty
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 = '东方红';
/*请为机车厂工程项目建立一个供应情况的视图，包括供应商代码，零件代码，
供应数量。针对该试图完成如下查询并将 SQL 语句写入实验报告*/
Go;
create view test AS
select sno,jno,pno,qty
from spj;
go;
--（1）查询机车厂工程项目使用的各种零件代码及其数量。
select pno,qty from test
join j on j.jno=test.jno
where j.jname='机车厂';
--2）查询处供应商 S2 的供应情况。
select pno ,sum(qty) as sum from test group by pno,sno having sno ='s1';
select pno,sum(qty) from test where sno ='s1' group by pno
代码量 80行
时间 3小时
