文档介绍:第三章——SQL语言练****br/>(5)求至少用了供应商S1所供应的全部零件的工程号JNO。
(1)找出所有供应商的姓名和所在城市。
(2)找出所有零件的名称、颜色、重量。
(3)找出使用供应商S1所供应零件的工程号码。
SE第三章——SQL语言练****br/>(5)求至少用了供应商S1所供应的全部零件的工程号JNO。
(1)找出所有供应商的姓名和所在城市。
(2)找出所有零件的名称、颜色、重量。
(3)找出使用供应商S1所供应零件的工程号码。
SELECT SNAME, CITY
FROM S;
SELECT PNAME, COLOR, WEIGHT
FROM P;
SELECT JNO
FROM SPJ
WHERE SNO = ‘S1’ ;
(4)找出工程项目J2使用的各种零件的名称及其数量。
(5)找出上海厂商供应的所有零件号码。
SELECT P. PNAME, SPJ. QTY
FROM P, SPJ
WHERE P. PNO = SPJ. PNO
AND SPJ. JNO = ‘J2’ ;
SELECT DISTINCT PNO
FROM SPJ
WHERE SNO IN
(SELECT SNO
FROM S
WHERE CITY = ‘上海’ );
(6)找出使用上海产的零件的工程名称。
SELECT JNAME
FROM J, SPJ, S
WHERE J. JNO = SPJ. JNO
AND SPJ. SNO = S. SNO
AND S. CITY = ‘上海’;
或:
SELECT JNAME
FROM J
WHERE JNO IN
(SELECT JNO
FROM SPJ, S
WHERE SPJ. SNO = S. SNO
AND S. CITY = ‘上海’);
(7)找出没有使用天津产的零件的工程号码。
SELECT JNO
FROM J
WHERE NOT EXISTS
(SELECT *
FROM SPJ
WHERE SPJ. JNO = J. JNO
AND SNO IN
(SELECT SNO
FROM S
WHERE CITY=‘天津’));
或:
SELECT JNO
FROM J
WHERE NOT EXISTS
(SELECT *
FROM SPJ, S
WHERE SPJ. JNO = J. JNO
AND SPJ. SNO = S. SNO
AND S. CITY = ‘天津’ );
(8)把全部红色零件的颜色改成蓝色。
(9)由S5供给J4的零件P6改为由S3供应,请作必要的修改。
UPDATE P
SET COLOR= ‘蓝’
WHERE COLOR =‘红’;
UPDATE SPJ
SET SNO = ‘S3’
WHERE SNO = ‘S5’
AND JNO = ‘J4’
AND PNO = ‘P6’;
(10)从供应商关系中删除S2的记录,并从供应情况关系中删除相应的记录。
DELETE
FROM SPJ
WHERE SNO = ‘S2’ ;
DELETE
FROM S
WHERE SNO = ‘S2’;
感谢您的关注