5.27

(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 SUBSTRING(sname, 2, 1) = '方';
(5) 查询所有零件中的最大、最小、平均重量
SELECT MAX(weight) AS 最大重量,
MIN(weight) AS 最小重量,
AVG(weight) AS 平均重量
FROM p;
(6) 查询零件中名为"螺丝刀"的零件的种类数、平均重量
SELECT COUNT(*) AS 种类数,
AVG(weight) AS 平均重量
FROM p
WHERE pname = '螺丝刀';
(7) 查询供应商S1所供应的各种零件的名称和数量
SELECT p.pname, spj.qty
FROM spj JOIN p ON spj.pno = p.pno
WHERE spj.sno = 's1';
(8) 查询工程J1所使用的各种零件的名称和数量
SELECT p.pname, spj.qty
FROM spj JOIN p ON spj.pno = p.pno
WHERE spj.jno = 'j1';
(9) 查询没有使用红色螺丝刀的工程名称
SELECT jname
FROM j
WHERE jno NOT IN (
SELECT jno FROM spj
WHERE pno IN (
SELECT pno FROM p
WHERE pname = '螺丝刀' AND color = '红'
)
);
(10) 查询没有供应红色螺丝刀的供应商名称
SELECT sname
FROM s
WHERE sno NOT IN (
SELECT sno FROM spj
WHERE pno IN (
SELECT pno FROM p
WHERE pname = '螺丝刀' AND color = '红'
)
);
(11) 查询所用零件数量超过500的工程项目号
SELECT jno
FROM spj
GROUP BY jno
HAVING SUM(qty) > 500;
(12) 查询所用零件种类超过3种的工程项目名称
SELECT j.jname
FROM spj JOIN j ON spj.jno = j.jno
GROUP BY spj.jno, j.jname
HAVING COUNT(DISTINCT pno) > 3;
(13) 查询使用了全部零件的工程项目名称
SELECT j.jname
FROM j
WHERE NOT EXISTS (
SELECT pno FROM p
WHERE NOT EXISTS (
SELECT * FROM spj
WHERE spj.jno = j.jno AND spj.pno = p.pno
)
);
(14) 查询至少供应了工程J1所使用的全部零件的供应商名称
SELECT s.sname
FROM s
WHERE NOT EXISTS (
SELECT pno FROM spj AS j1_parts
WHERE j1_parts.jno = 'j1'
AND NOT EXISTS (
SELECT * FROM spj
WHERE spj.sno = s.sno AND spj.pno = j1_parts.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 = '东方红';