3、
select * from S where A=10; -- (1)
select A,B from S; -- (2)
select A,B,S.C,S.D,E,F from S,T where S.C=T.C and S.D=T.D; -- (3)
select A,B,S.C,S.D,T.C,T.D,E,F from S,T where S.C=T.C; -- (4)
select A,B,S.C,S.D,T.C,T.D,E,F from S,T where A<E; -- (5)
select * from T,
(select C,D from S); -- (6)
4、
create table S
(
SNO varchar(10) primary key,
SNAME varchar(10),
STATUS_ int,
CITY varchar(10)
); --建立S表
create table P
(
PNO varchar(10) primary key,
PNAME varchar(10),
COLOR varchar(5),
WEIGHT_ int
); --建立P表
create table J
(
JNO varchar(10) primary key,
JNAME varchar(10),
CITY varchar(10)
); --建立J表
create table SPJ
(
SNO varchar(10),
PNO varchar(10),
JNO varchar(10),
QTY int,
foreign key(SNO) references S(SNO),
foreign key(PNO) references P(PNO),
foreign key(JNO) references J(JNO)
); --建立SPJ表
建立表后,将题目中的数据插入
insert into S values('S1','精益','20','天津');
insert into S values('S2','盛锡','10','北京');
insert into S values('S3','东方红','30','北京');
insert into S values('S4','丰泰盛','20','天津');
insert into S values('S5','为民','30','上海');
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',300);
insert into SPJ values('S5','P6','J4',200);
进行相关查询
select SNO from SPJ where JNO='J1'; -- (1)
select SNO from SPJ where JNO='J1' and PNO='P1'; -- (2)
select SNO
from SPJ,P
where JNO='J1' and p.PNO=SPJ.PNO and COLOR='红'; -- (3)
select JNO
from J
where not exists
(select *
from S,P,SPJ
where SPJ.JNO=J.JNO and SPJ.SNO=S.SNO
and SPJ.PNO=P.PNO and S.CITY='天津'
and P.COLOR='红'
); -- (4)
select JNO
from SPJ as x
where not exists
(select *
from SPJ as y
where SNO='S1' and not exists
(select *
from SPJ as z
where z.PNO=y.PNO and z.JNO=x.JNO
)
); -- (5)
查询结果如下,从左到右为(1)~(5)
5、
select SNAME,CITY from S; -- (1)
select PNAME,COLOR,WEIGHT_ from P; -- (2)
select JNO FROM SPJ where SNO='S1'; -- (3)
select P.PNAME,SPJ.QTY
from P,SPJ
where SPJ.JNO='J2' and P.PNO=SPJ.PNO; -- (4)
select PNO
from S,SPJ
where S.SNO=SPJ.SNO and CITY='上海'; -- (5)
select JNAME
from S,J,SPJ
where J.JNO=SPJ.JNO and SPJ.SNO=S.SNO and S.CITY='上海'; -- (6)
select JNO
from J
where not exists
(select *
from S,SPJ
where SPJ.JNO=J.JNO and SPJ.SNO=S.SNO
and S.CITY='天津'
); -- (7)
update P set COLOR='蓝色' where COLOR='红'; -- (8)
update SPJ
set SNO='S3'
where SNO='S5' and JNO='J4' and PNO='P6'; -- (9)
delete from S where SNO='S2';
delete from SPJ where SNO='S2'; -- (10)
insert into SPJ values('S2','P4','J6',200); -- (11)
(1)~(5)查询结果如下:
(6)~(7)查询结果如下:
6、
create view san_jian
as
select SNO,PNO,QTY
from J,SPJ
where J.JNO=SPJ.JNO and JNAME='三建'; --建立视图
建立视图后,执行查询
select PNO,QTY from san_jian; -- (1)
select * from san_jian where SNO='S1'; -- (2)
查询结果如下: