目录
1 有两个关系S(A,B,C,D)和T(C,D,E,F),写出与下列查询等价的SQL表达式：
2.用SQL语句建立第2章习题6中的4个表；针对建立的4个表用SQL完成第2章习题6中的查询
1 有两个关系S(A,B,C,D)和T(C,D,E,F),写出与下列查询等价的SQL表达式：
(1)σA=10(S)
SELECT *
FROM S
WHERE A=10;
(2)πA,B(S)
SELECT DISTINCT A,B
FROM S;
(3)S⨝T
SELECT A,B,S.C,S.D,E,F
FROM S,T
WHERE S.C=T.C AND S.D=T.D;
(4)S⨝S.C=T.CT
SELECT A,B,S.C,S.D,T.C,T.D,E,F
FROM S,T
WHERE S.C=T.C;
(5)S⨝A<ET
SELECT A,B,S.C,S.D,T.C,T.D,E,F
FROM S,T
WHERE A<E;
(6)πC,D(S)×T
SELECT S.C,S.D,T.C,T.D,E,F
FROM S,T;
2.用SQL语句建立第2章习题6中的4个表；针对建立的4个表用SQL完成第2章习题6中的查询
(1)建表
①S表
CREATE TABLE S
(
SNO CHAR(2) UNIQUE,
SNAME CHAR(6),
STATUS CHAR(2),
CITY CHAR(4),
);
INSERT
INTO S(SNO,SNAME,STATUS,CITY)
VALUES ('s1','精益','20','天津'),
('s2','盛锡','10','北京'),
('s3','东方红','30','北京'),
('s4','丰泰盛','20','天津'),
('s5','为民','30','上海');
②P表
CREATE TABLE P
(
PNO CHAR(2) UNIQUE,
PNAME CHAR(6),
COLOR CHAR(2),
WEIGHT INT,
);
INSERT
INTO P(PNO, PNAME, COLOR, WEIGHT)
VALUES ('p1','螺母','红',12),
('p2','螺栓','绿',17),
('p3','螺丝刀','蓝',14),
('p4','螺丝刀','红',14),
('p5','凸轮','蓝',40),
('p6','齿轮','红',30);
③J表
CREATE TABLE J
(
JNO CHAR(2),
JNAME CHAR(8),
CITY CHAR(4),
);
INSERT
INTO J(JNO, JNAME, CITY)
VALUES ('j1','三建','北京'),
('j2','一汽','长春'),
('j3','弹簧厂','天津'),
('j4','造船厂','天津'),
('j5','机车厂','唐山'),
('j6','无线电厂','常州'),
('j7','半导体厂','南京');
④SPJ表
CREATE TABLE SPJ
(
SNO CHAR(2),
PNO CHAR(2),
JNO CHAR(2),
QTY INT,
);
INSERT
INTO SPJ(SNO,PNO,JNO,QTY)
VALUES ('s1','p1','j1',200),
('s1','p1','j3',100),
('s1','p1','j4',700),
('s1','p2','j2',200),
('s2','p3','j1',400),
('s2','p3','j2',200),
('s2','p3','j4',500),
('s2','p3','j5',400),
('s2','p5','j1',400),
('s2','p5','j2',100),
('s3','p1','j1',200),
('s3','p3','j1',200),
('s4','p5','j1',100),
('s4','p6','j3',300),
('s4','p6','j4',200),
('s5','p2','j4',100),
('s5','p3','j1',200),
('s5','p6','j2',200),
('s5','p6','j4',500);
(2)查询
①求供应工程J1零件的供应商号码SNO
SELECT DISTINCT SNO
FROM SPJ
WHERE JNO=’J1’;
②求供应工程J1零件P1的供应商号码SNO
SELECT DISTINCT SNO
FROM SPJ
WHERE JNO=’J1’ AND PNO=’P1’;
③求供应工程J1零件为红色的供应商号码SNO
方法1：
SELECT SNO
FROM SPJ
WHERE JNO=’J1’
AND PNO IN
(SELECT PNO
FROM P
WHERE COLOR=’红’);
方法2：
SELECT SNO
FROM SPJ,P
WHERE P.PNO=SPJ.PNO
AND JNO=’J1’ AND COLOR=’红’;
④求没有使用天津供应商生产的红色零件的工程号JNO
方法1：
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
FORM P
WHERE COLOR=’红’));
方法2：
SELECT JNO
FROM J
WHERE NOT EXISTS
(SELECT *
FROM SPJ,S,P
WHERE SPJ.SNO=S.SNO AND SPJ.JNO=J.JNO AND SPJ.PNO=P.PNO
AND S.CITY=’天津’
AND P.COLOR=’红’);
⑤求至少用了供应商S1所供应的全部零件的工程号JNO
SELECT DISTINCT JNO
FROM SPJ AS SPJZ
WHERE NOT EXISTS (
SELECT *
FROM SPJ AS SPJX
WHERE SNO = 'S1'
AND NOT EXISTS (
SELECT *
FROM SPJ AS SPJY
WHERE SPJY.PNO = SPJX.PNO
AND SPJY.JNO = SPJZ.JNO
)
);
3.什么是基本表？什么是视图？两者的区别和联系是什么？
基本表是本身独立存在的表，在SQL中一个关系就对应一个表。视图是从一个或几个基本表导出的表，视图本身不独立存储在数据库中，是一个虚表，即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中。视图在概念上与基本表等同，用户可以如同基本表那样使用视图，可以在视图上再定义视图。
