SQL语句练习题
选自王珊的《数据库系统概论》第五版 教材
习题3:设有一个SPJ数据库,包括S、P、J、SPJ四个关系模式:
S(SNO,SNAME,STATUS,CITY);
P(PN0,PNAME,CLOLR,WEIGHT);
J(JNO,JNAME,CITY);
SPJ(SNO,PNO,JNO,QTY);
供应商表S由供应商(SNO)、供应商姓名(SNAME)、供应商状态(STATUS)、供应商所在城市(CITY)组成;
零件表P由零件代码(PNO)、零件名(PNAME)、颜色(CLOLR)、重量(WEIGHT)组成;
工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成;
供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成,表示某供应商供应某种零件给某工程项目的数量为QTY。
各表中均有若干数据
先建好四个表。
CREATE TABLE S(
SNO CHAR(3),
SNAME CHAR(10),
STATUS CHAR(2),
CITY CHAR(10)
)
CREATE TABLE P(
PNO CHAR(3),
PNAME CHAR(10),
CLOLR CHAR(4),
WEIGHT INT
)
CREATE TABLE J(
JNO CHAR(3),
JNAME CHAR(10),
CITY CHAR(10)
)
CREATE TABLE SPJ(
SNO CHAR(3),
PNO CHAR(3),
JNO CHAR(3),
QTY INT
)
再插入一些数据(这里选用了王珊的《数据库系统概论》第五版 教材配套的习题解析与实验指导上的数据)
J表:
S表
P表
SPJ表
试用SQL语言完成如下查询:
(1)求供应工程J1零件的供应商号码SNO;
SELECT DISTINCT SNO FROM SPJ WHERE JNO='J1'
这个题王珊版教材的习题解析给出的代码是SELECT SNO FROM SPJ WHERE JNO='J1' ,没有DISTINCT,但是这样会重复结果,后面的题也是一样的。
(2)求供应工程J1零件P1的供应商号码SNO;
SELECT DISTINCT SNO
FROM SPJ
WHERE JNO='J1' AND PNO='P1'
(3)求供应工程J1零件为红色的供应商号码SNO;
SELECT DISTINCT SNO FROM SPJ
WHERE JNO='J1' AND
PNO IN(SELECT PNO FROM P WHERE CLOLR='红')
(4)求没有使用天津供应商生产的红色零件的工程号JNO
方法一:
SELECT JNO
FROM J
WHERE NOT EXISTS
(SELECT *
FROM SPJ
WHERE SPJ.JNO=J.JNO
AND SNO IN(
SELECT SNO
FROM S
WHERE CITY='天津')
AND PNO IN
(SELECT PNO
FROM P
WHERE CLOLR='红'))
方法二:
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.CLOLR='红');
(5)求至少用了S1供应商所供应的全部零件的工程号JNO;
SELECT DISTINCT JNO
FROM SPJ SPJZ
WHERE NOT EXISTS(SELECT *FROM SPJ SPJXWHERE SNO='S1'AND NOT EXISTS(SELECT *FROM SPJ SPJYWHERE SPJX.PNO=SPJY.PNOAND SPJY.JNO=SPJZ.JNO))
针对习题3中的四个表试用SQL语言完成以下各项操作:
(1)找出所有供应商的姓名和所在城市。
SELECT SNAME, CITYFROM S;
(2)找出所有零件的名称、颜色、重量。
SELECT PNAME, CLOLR, WEIGHTFROM P;
(3)找出使用供应商S1所供应零件的工程号码。
SELECT JNOFROM SPJWHERE SNO= 'S1'
(4)找出工程项目J2使用的各种零件的名称及其数量。
SELECT P.PNAME, SPJ.QTYFROM P, SPJWHERE P.PNO=SPJ.PNOAND SPJ.JNO='J2';
(5)找出上海厂商供应的所有零件号码。
SELECT DISTINCT PNO
FROM SPJ
WHERE SNO IN(SELECT SNO
FROM S
WHERE CITY='上海');
(6)找出使用上海产的零件的工程名称。
SELECT DISTINCT JNAME
FROM J, SPJ, S
WHERE J. JNO=SPJ. JNO
AND SPJ. SNO=S.SNO
AND S.CITY='上海';
(7)找出没有使用天津产的零件的工程号码。
SELECT JNO
FROM J
WHERE NOT EXISTS(SELECT *FROM SPJ,SWHERE SPJ.JNO=J.JNO AND SPJ.SNO=S.SNO AND S.CITY ='天津')
(8)把全部红色零件的颜色改成蓝色。
UPDATE PSET COLOR='蓝'WHERE COLOR='红';
(9)由S5供给J4的零件P6改为由S3供应,请作必要的修改。
UPDATE SPJSET SNO='S3'WHERE SNO='S5' AND JNO='J4' AND PNO='P6';
(10)从供应商关系中删除S2的记录,并从供应情况关系中删除相应的记录。
DELETE FROM SPJWHERE SNO=’S2’;DELETE FROM SWHERE SNO=’S2’;
注意删除顺序,应该先从 SPJ表中删除供应商S2所供应零件的记录,然后从S表中删除S2。
(11)请将(S2,J6,P4,200)插入供应情况关系。
方法一:
INSERT INTO SPJ(SNO, JNO, PNO, QTY)VALUES (S2,J6,P4,200);
方法二:注意一下顺序,建表的时候是SPJ(SNO,PNO,JNO,QTY),不能反了。
INSERT INTO SPJVALUES (S2,P4,J6,200);
请为三建工程项目建立一个供应情况的视图,包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)。针对该视图完成下列查询:
创建视图:
CREATE VIEW VSP AS SELECT SNO,SPJ.PNO,QTY FROM SPJ,JWHERE SPJ.JNO=J.JNO AND J.JNAME='三建'
或者用下面这个
CREATE VIEW V_SPJ AS
SELECT SNO, PNO, QTY
FROM SPJ
WHERE JNO IN(SELECT JNO
FROM J
WHERE JNAME='三建');
结果一样。
(1)找出三建工程项目使用的各种零件代码及其数量。
SELECT PNO, SUM(QTY) AS 数量
FROM V_SPJ
GROUP BY PNO;
配套的习题解析给出的答案是
SELECT PNO,QTY FROM VSP
这么看,P1,P3,P5有重复的。
(2)找出供应商S1的供应情况。
SELECT *FROM VSPWHERE SNO='S1'