spj查询零件、工程、供应商表

设有一个spj数据库，包括s、p、j及spj 共4个关系模式：

s(sno, sname, city)  ：供应商表（供应商号，供应商名，供应商所在城市）;   

p(pno, pname, weight)  ：零件表（零件号，零件名，零件颜色，零件重量）;

j(jno, jname, city)  ：工程表（工程号，工程名，工程所在城市）;

spj(sno, pno, jno, qty)  供应表（供应商号，零件号，工程号，供应工程的零件数量）;

# 1.按城市降序列出所有供应商名和城市名：

SELECT sname,city
FROM s
ORDER BY city DESC;

# 2.查供应零件总数量超过100的供应商号：

SELECT sno,SUM(qty)
FROM spj
GROUP BY sno
HAVING SUM(qty) > 100;

# 3.查供应商号'S1'所供应的工程名：

SELECT j.jname
FROM spj
JOIN j
ON spj.jno=j.jno
WHERE sno='s1';

# 4.查工程号'j1'所使用的零件名：

SELECT DISTINCT pname
FROM spj
JOIN p
ON spj.pno=p.pno
WHERE jno='j1';

# 5.查'上海'供应商所供应的零件号：

SELECT sno FROM s WHERE city='上海';        # '上海'供应商

SELECT DISTINCT pno
FROM spj
WHERE sno IN(SELECT sno FROM s WHERE city='上海');

# 6.查'上海'供应商所供应的工程名：

SELECT DISTINCT j.jname
FROM spj
JOIN j
ON spj.jno = j.jno
WHERE sno IN(SELECT sno FROM s WHERE city='上海');

# 7.查'上海'供应商供应'红'色零件的工程名：

SELECT sno FROM s WHERE city='上海';        # '上海'供应商

SELECT pno FROM p WHERE color='红';        # '红'色零件

#方法一
SELECT j.jname
FROM spj
JOIN j
ON spj.jno=j.jno
WHERE sno IN (SELECT sno FROM s WHERE city='上海') 
AND pno IN(SELECT pno FROM p WHERE color='红');
#方法二
SELECT jname
FROM spj 
JOIN j
ON spj.jno=j.jno
JOIN p
ON spj.pno=p.pno
WHERE color='红' AND sno 
IN(SELECT sno FROM s WHERE city='上海');
#方法三
SELECT jname
FROM spj 
JOIN j
ON spj.jno=j.jno
JOIN p
ON spj.pno=p.pno
JOIN s
ON spj.sno=s.sno
WHERE color='红' AND s.city='上海';

# 8.增加('s8', '莱特', '武汉')供应商：

INSERT INTO s VALUES('s8', '莱特', '武汉');

# 9.删除工程号'j1'的全部供应记录：

DELETE FROM spj
WHERE jno='j1';

# 10.将全部'红'色零件改为'蓝'色：

UPDATE p 
SET color='蓝'
WHERE color='红';

以上是学习过程的笔记，仅供参考，有问题望大家多多指点赐教。