3.有两个关系S(A,B,C,D)和T(C,D,E,F),写出与下列查询等价的SQL表达式:
(1)
σ
A
=
10
(
S
)
\sigma_{A = 10}(S)
σA=10(S);
SELECT*
FROM S
WHERE A=10;
(2) Π A , B ( S ) \Pi_{A,B}(S) ΠA,B(S);
SELECT A,B
FROM S
(3) S ⋈ T S\Join T S⋈T;
SELECT S.*,T.*
FROM S,T
(4) S ⋈ S . C = T . C T S \underset{S.C=T.C}\Join T SS.C=T.C⋈T;
SELECT S.*,T.*
FROM S,T
WHERE S.C=T.C;
(5) S ⋈ A < E T S\underset{A<E}\Join T SA<E⋈T;
SELECT S.*,T.*
FROM S,T
WHERE A<E;
(6) Π C , D ( S ) × T \Pi_{C,D}(S) \times T ΠC,D(S)×T ;
SELECT C,D
FROM S
UNION
SELECT *
FROM T
4、用SQL语句建立第二章习题6中的4个表;针对建立的4个表用SQL完成第二章习题6中的查询。
CREATE TABLE S
(
SNO CHAR(4) PRIMARY KEY,
SNAME CHAR(10),
STATUS SMALLINT,
CITY CHAR(6)
);
CREATE TABLE P
(
PNO CHAR(4) PRIMARY KEY,
PNAME CHAR(10),
COLORS CHAR(2),
WEIGHT SMALLINT
);
CREATE TABLE J
(
JNO CHAR(4) PRIMARY KEY,
JNAME CHAR(20),
CITY CHAR(6)
);
CREATE TABLE SPJ
(
SNO CHAR(4),
PNO CHAR(4),
JNO CHAR(4),
QTY SMALLINT,
FOREIGN KEY (SNO) REFERENCES S(SNO),
FOREIGN KEY (PNO) REFERENCES P(PNO),
FOREIGN KEY (JNO) REFERENCES J(JNO)
);
INSERT INTO S(SNO,SNAME,STATUS,CITY) VALUES('S1','精益',20,'天津');
INSERT INTO S(SNO,SNAME,STATUS,CITY) VALUES('S2','盛锡',10,'北京');
INSERT INTO S(SNO,SNAME,STATUS,CITY) VALUES('S3','东方红',30,'北京');
INSERT INTO S(SNO,SNAME,STATUS,CITY) VALUES('S4','丰泰盛',20,'天津');
INSERT INTO S(SNO,SNAME,STATUS,CITY) VALUES('S5','为民',30,'上海');
INSERT INTO P(PNO,PNAME,COLORS,WEIGHT) VALUES('P1','螺母','红',12);
INSERT INTO P(PNO,PNAME,COLORS,WEIGHT) VALUES('P2','螺栓','绿',17);
INSERT INTO P(PNO,PNAME,COLORS,WEIGHT) VALUES('P3','螺丝刀','蓝',14);
INSERT INTO P(PNO,PNAME,COLORS,WEIGHT) VALUES('P4','螺丝刀','红',14);
INSERT INTO P(PNO,PNAME,COLORS,WEIGHT) VALUES('P5','凸轮','蓝',40);
INSERT INTO P(PNO,PNAME,COLORS,WEIGHT) VALUES('P6','齿轮','红',30);
INSERT INTO J(JNO,JNAME,CITY) VALUES('J1','三建','北京');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J2','一汽','长春');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J3','弹簧厂','天津');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J4','造船厂','天津');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J5','机车厂','唐山');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J6','无线电厂','常州');
INSERT INTO J(JNO,JNAME,CITY) VALUES('J7','半导体厂','南京');
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P1','J1',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P1','J3',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P1','J4',700);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S1','P2','J2',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J1',400);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J2',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J4',500);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P3','J5',400);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P5','J1',400);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S2','P5','J2',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S3','P1','J1',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S3','P3','J1',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S4','P5','J1',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S4','P6','J3',300);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S4','P6','J4',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P2','J4',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P3','J1',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P6','J2',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY) VALUES('S5','P6','J4',500);
(1)求供应工程J1零件的供应商号码SNO;
SELECT SNO
FROM SPJ
WHERE JNO='J1';
(2)求供应工程J1零件P1的供应商号码SNO;
SELECT SNO
FROM SPJ
WHERE JNO='J1'
AND PNO = 'P1';
(3)求供应工程J1零件为红色的供应商号码SNO;
SELECT SNO
FROM SPJ
WHERE JNO='J1'
AND PNO IN(SELECT PNO
FROM P
WHERE COLORS='红'
);
(4)求没有使用天津供应商生产的红色零件的工程号JNO;
SELECT JNO
FROM SPJ
WHERE PNO IN(SELECT PNO
FROM P
WHERE COLORS='红'
)
AND SNO NOT IN(SELECT SNO
FROM S
WHERE CITY='天津'
);
(5)求至少用了供应商S1所供应的全部零件的工程号JNO。
(这个真的做不出来,想了好久也不会,然后我就查了一下资料)
SELECT DISTINCT JNO
FROM SPJ SPJX
WHERE NOT EXISTS(
SELECT *
FROM SPJ SPJY
WHERE SPJY.SNO='S1'
AND NOT EXISTS(
SELECT *
FROM SPJ SPJZ
WHERE SPJZ.JNO=SPJX.JNO
AND SPJZ.PNO=SPJY.PNO
)
);
5、针对习题4中的4个表使用SQL完成以下各项操作:
(1)找出所有供应商的姓名和所在城市;
SELECT SNAME,CITY
FROM S
(2)找出所有零件的名称、颜色、重量;
SELECT PNAME,COLORS,WEIGHT
FROM P
(3)找出使用供应商S1所供应零件的工程号码;
SELECT JNO
FROM SPJ
WHERE SNO='S1';
(4)找出工程项目J2使用的各种零件的名称及其数量;
SELECT PNAME,QTY
FROM P,SPJ
WHERE JNO='J2'
AND P.PNO=SPJ.PNO;
(5)找出上海厂商供应的所有零件号码;
SELECT PNO
FROM SPJ
WHERE SNO =(SELECT SNO
FROM S
WHERE CITY='上海'
);
(6)找出使用上海产的零件的工程项目;
SELECT JNO
FROM SPJ
WHERE SNO =(SELECT SNO
FROM S
WHERE CITY='上海'
);
(7)找出没有使用天津产的零件的工程号码;
SELECT JNO
FROM SPJ
WHERE JNO NOT IN(SELECT JNO
FROM S,SPJ
WHERE CITY='天津'
AND S.SNO=SPJ.SNO
);
(8)把全部红色零件的颜色改成蓝色;
UPDATE P
SET COLORS='蓝'
WHERE COLORS='红';
(9)由S5供给J4的零件P6改为由S3供应,请作必要的修改;
UPDATE SPJ
SET SNO='S3'
WHERE SNO='S5' AND PNO='P6'
AND JNO='J4';
(10)从供应商关系中删除S2的记录,并从供应情况关系中删除相应的记录;
DELETE
FROM SPJ
WHERE SNO='S2';
DELETE
FROM S
WHERE SNO='S2';
(11)请将(S2,J6,P4,200)插入供应情况关系;
INSERT
INTO SPJ
VALUES('S2','P4','J6',200);
9、请为三建工程项目建立一个供应情况的视图,包括供应商代码(SNO)、零件代码(PNO)、供应商数量(QTY)。针对该视图完成下列查询:
CREATE VIEW SPJVIEW
AS
SELECT SNO,PNO,QTY
FROM SPJ
WHERE JNO IN(SELECT JNO
FROM J
WHERE JNAME='三建'
);
(1)找出三建工程项目使用的各种零件代码及其数量;
SELECT PNO,QTY
FROM SPJVIEW
(2)找出供应商S1的供应情况;
SELECT *
FROM SPJVIEW
WHERE SNO='S1';
参考:添加链接描述