(4)SELECT pname,COUNT(pname) FROM p,spj WHERE jno='J2' AND spj.pno=p.pno GROUP BY pname

(5)SELECT p.pno FROM s,p,spj WHERE city='上海' AND s.sno=spj.sno AND spj.pno=p.pno

(6)SELECT DISTINCT j.jno FROM s,j,spj WHERE s.city='上海' AND s.sno=spj.sno AND spj.jno =j.jno

(7)SELECT jno FROM spj WHERE jno NOT IN(SELECT jno FROM spj,s WHERE s.sno=spj.sno AND s.city天津')

(8)UPDATE p SET color='蓝' WHERE color='红'

(9)UPDATE spj SET sno='S3' WHERE sno='S5' AND jno='J4' AND pno='P6'

(10)DELETE FROM spj WHERE sno='S2'

(11)INSERT INTO spj (sno,pno,jno,qty) VALUES ('S2','J6','P4',200)

三、实验数据 SPJ数据库

J表

jno j1 j2 j3 j4 j5 j6 j7 jname 三建 一汽 弹簧厂 造船厂 机车厂 无线电厂 半导体厂 city 北京 长春 天津 天津 唐山 常州 兰州 P表

pno p1 p2 p3 p4 p5 p6 pname 螺母 螺栓 螺丝刀 螺丝刀 凸轮 齿轮 color 红 绿 蓝 红 蓝 红 weight 12 17 14 14 40 30 S表

sno s1 s2 s3 s4 s5 sname JY SX DFH FTS WM status city 20 天津 10 北京 30 北京 20 天津 30 上海 SPJ表

sno s1 s1 s1 s1 s2 s2 s2 pno p1 p1 p1 p2 p3 p3 p3 jno j1 j3 j4 j2 j1 j2 j4 qty 200 100 700 100 400 200 500 s2 s2 s3 s3 s4 s4 s4 s5 s5 s5 s5 p3 p5 p1 p3 p5 p6 p6 p2 p3 p6 p6 j5 j2 j1 j1 j1 j3 j4 j4 j1 j2 j4 400 100 200 200 100 300 200 100 200 200 500

四、思考题