一.实验目的
熟悉对表的查询
二.实验设备
PC机一台
三.实验内容
练习对表的查询
四.实验步骤
打开SQL Server Management Studio 18,弹出一个连接到服务器的窗口,在这里面,需要将服务器名称改为(local),点击连接。
1.求供应工程J1零件为红色的供应商号码SNO
SELECT SNO
FROM SPJ,P
WHERE JNO='J1' AND SPJ.PNO=P.PNO AND COLOR='红';
2. 求没有使用天津供应商生产的红色零件的工程号JNO
SELECT JNO
FROM J
WHERE NOT EXISTS
(SELECT*
FROM SPJ,S,P
WHERE SPJ.JNO=J.JNO AND SPJ.SNO=S.SNO AND SPJ.PNO=P.PNO AND S.CITY='天津'AND P.COLOR='红'
);
3.求至少用了供应商S1所供应的全部零件的工程号JNO
SELECT DISTINCT JNO
FROM SPJ SPJI
WHERE NOT EXISTS
(SELECT*
FROM SPJ SPJX
WHERE SNO='S1' AND NOT EXISTS
(SELECT*
FROM SPJ SPJY
WHERE SPJY.PNO=SPJX.PNO AND SPJY.JNO=SPJI.JNO)
);
4.找出使用上海产的零件的工程名称
SELECT JNAME
FROM J,SPJ,S
WHERE J.JNO=SPJ.JNO AND SPJ.SNO =S.SNO AND S.CITY='上海';
5.找出没有使用天津产的零件的工程号码
SELECT JNO
FROM J
WHERE NOT EXISTS
(SELECT*
FROM SPJ
WHERE SPJ.JNO=J.JNO AND SNO IN
(SELECT SNO
FROM S
WHERE CITY='天津')
);
6.把全部红色零件的颜色改为蓝色
UPDATE P
SET COLOR='蓝'
WHERE COLOR='红';
/* 由S5供给J4的零件P6改为由S3供应,请作必要的修改 */
UPDATE SPJ
SET SNO='S3'
WHERE SNO='S5' AND JNO='J4' AND PNO='P6';
/*从供应商关系中删除供应商号是S2的记录,并从供应情况关系中删除相应的记录*/
DELETE
FROM SPJ
WHERE SNO='S2';
DELETE
FROM S
WHERE SNO='S2';
/*请将(S2,J6,P4,200)插入供应情况关系*/
INSERT
INTO SPJ(SNO,JNO,PNO,QTY)
VALUES('S2','J6','P4',200);