3.有两个关系S(A,B,C,D)和T(C,D,E,F),写出与下列查询等价的SQL表达式
答:
(1)
select * from S where A=10;
(2)
select A,B from S;
(3)
select A,B,C,D,E,F from S,T where S.c=T.c AND S.d=T.d
(4)
select * from S,T where S.c=T.c
(5)
select * from S,T where A < E
(6)
select from (select C,D from S) as S1,T;
4.用SQL语句建立第2章习题6中的4个表;针对建立的4个表用SQL完成第二章习题6中的查询
答:
建表:
CREATE TABLE S(SNO char(2), SNAME char(10), STATUS smallint, CITY char(10)) CREATE TABLE P(PNO char(2), PNAME char(10), COLOR char(2), WEIGHT smallint) CREATE TABLE J(JNO char(2), JNAME char(10), CITY char(10)) CREATE TABLE SPJ(SNO char(2), PNO char(2), JNO char(2), QTY smallint) INSERT INTO S VALUES ('S1','精益',20,'天津'); INSERT INTO S VALUES ('S2','盛锡',10,'北京'); INSERT INTO S VALUES ('S3','东方红',20,'北京'); INSERT INTO S VALUES ('S4','丰泰盛',20,'天津'); INSERT INTO S VALUES ('S5','为民',20,'上海'); INSERT INTO P VALUES ('P1','螺母','红',12); INSERT INTO P VALUES ('P2','螺栓','绿',17); INSERT INTO P VALUES ('P3','螺丝刀','蓝',14); INSERT INTO P VALUES ('P4','螺丝刀','红',14); INSERT INTO P VALUES ('P5','凸轮','蓝',40); INSERT INTO P VALUES ('P6','齿轮','红',30); INSERT INTO J VALUES ('J1','三建','北京'); INSERT INTO J VALUES ('J2','一汽','长春'); INSERT INTO J VALUES ('J3','弹簧厂','天津'); INSERT INTO J VALUES ('J4','造船厂','天津'); INSERT INTO J VALUES ('J5','机车厂','唐山'); INSERT INTO J VALUES ('J6','无线电厂','常州'); INSERT INTO J VALUES ('J7','半导体厂','南京'); INSERT INTO SPJ VALUES ('S1','P1','J1',200); INSERT INTO SPJ VALUES ('S1','P1','J3',100); INSERT INTO SPJ VALUES ('S1','P1','J4',700); INSERT INTO SPJ VALUES ('S1','P2','J2',100); INSERT INTO SPJ VALUES ('S2','P3','J1',400); INSERT INTO SPJ VALUES ('S2','P3','J2',200); INSERT INTO SPJ VALUES ('S2','P3','J4',500); INSERT INTO SPJ VALUES ('S2','P3','J5',400); INSERT INTO SPJ VALUES ('S2','P5','J1',400); INSERT INTO SPJ VALUES ('S2','P5','J2',100); INSERT INTO SPJ VALUES ('S3','P1','J1',200); INSERT INTO SPJ VALUES ('S3','P3','J1',200); INSERT INTO SPJ VALUES ('S4','P5','J1',100); INSERT INTO SPJ VALUES ('S4','P6','J3',300); INSERT INTO SPJ VALUES ('S4','P6','J4',200); INSERT INTO SPJ VALUES ('S5','P2','J4',100); INSERT INTO SPJ VALUES ('S5','P3','J1',200); INSERT INTO SPJ VALUES ('S5','P6','J2',200); INSERT INTO SPJ VALUES ('S5','P6','J4',500);
最近课有点多,事情也比较多,题目没做完,明天补上