create table S(
SNO char(5) primary key,
SNAME char(20) not null,
CITY char(20)
);
create table P(
PNO char(5) primary key,
PNAME char(20) not null,
COLOR char(5),
WEIGHT int,
);
create table J(
JNO char(5) primary key,
JNAME char(20) not null,
CITY char(20)
);
create table SPJ(
SNO char(5),
PNO char(5),
JNO char(5),
primary key (SNO,PNO,JNO),
foreign key (SNO) references S(SNO),
foreign key (PNO) references P(PNO),
foreign key (JNO) references J(JNO),
QTY int
);
insert into S values('S1','N1','上海');
insert into S values('S2','N2','北京');
insert into S values('S3','N3','北京');
insert into S values('S4','N4','上海');
insert into S values('S5','N5','南京');
insert into P values('P1','PN1','红',12);
insert into P values('P2','PN2','绿',18);
insert into P values('P3','PN3','蓝',20);
insert into P values('P4','PN4','红',13);
insert into P values('P5','PN5','蓝',11);
insert into P values('P6','PN6','红',15);
insert into J values('J1','JN1','上海');
insert into J values('J2','JN2','广州');
insert into J values('J3','JN3','南京');
insert into J values('J4','JN4','南京');
insert into J values('J5','JN5','上海');
insert into J values('J6','JN6','武汉');
insert into J values('J7','JN7','上海');
insert into SPJ values('S1','P1','J1',200);
insert into SPJ values('S1','P1','J4',700);
insert into SPJ values('S2','P3','J1',400);
insert into SPJ values('S2','P3','J2',200);
insert into SPJ values('S2','P3','J3',200);
insert into SPJ values('S2','P3','J4',500);
insert into SPJ values('S2','P3','J5',600);
insert into SPJ values('S2','P3','J6',400);
insert into SPJ values('S2','P3','J7',800);
insert into SPJ values('S2','P5','J2',100);
insert into SPJ values('S3','P3','J1',200);
insert into SPJ values('S3','P4','J2',500);
insert into SPJ values('S4','P6','J3',300);
insert into SPJ values('S4','P6','J7',300);
insert into SPJ values('S5','P2','J2',200);
insert into SPJ values('S5','P2','J4',100);
insert into SPJ values('S5','P5','J5',500);
insert into SPJ values('S5','P5','J7',100);
insert into SPJ values('S5','P6','J2',200);
insert into SPJ values('S5','P1','J4',1000);
insert into SPJ values('S5','P3','J4',1200);
insert into SPJ values('S5','P4','J4',800);
insert into SPJ values('S5','P5','J4',400);
insert into SPJ values('S5','P6','J4',500);
2 select * from J
3 select * from J where city in('上海')
4 select pno from p where weight in ( select min(weight) from p)
5 select sno from SPJ where jno in('j1')
6 select distinct sno from SPJ where pno in('p1')
7 select distinct jname from SPJ,j where j.jno=spj.jno and spj.sno in('s1')
8 select color from SPJ,p where p.pno=spj.pno and spj.sno in('s1')
9 select distinct sno from spj where jno='J1' and sno in (select sno from spj where jno='J2')
10 select sno from spj where jno='j1'and pno in (select pno from p where color='红')
11 select distinct sno from j,spj where j.jno=spj.jno and j.city in ('上海')
12 select distinct sno from j,p,spj where j.jno=spj.jno and j.city in ('上海','北京')and p.pno=spj.pno and p.color in('红')
13 select distinct pno from s,j,spj where s.sno=spj.sno and j.jno=spj.jno and s.city=j.city
14 select distinct pno from s,j,spj where s.sno=spj.sno and j.jno=spj.jno and s.city='上海' and j.city='上海'
15 select jno from j where exists (select * from spj where spj.jno=j.jno and sno in(select sno from s where s.city<>j.city))
16 select distinct jno from spj where sno in (select sno from s where city<>'上海')
17 select distinct jno from s,p,spj where s.sno=spj.sno and p.pno=spj.pno and p.color in ('红')
18 select distinct jno from spj where sno='s1'
19 select distinct s.city CITY, j.city CITY from s,j,spj where s.sno=spj.sno and j.jno=spj.jno
20 select distinct s.city CITY, p.pno PNO,j.city CITY from s,p,j,spj where s.sno=spj.sno and j.jno=spj.jno and p.pno=spj.pno
21 select distinct s.city CITY, p.pno PNO,j.city CITY from s,p,j,spj where s.sno=spj.sno and j.jno=spj.jno and p.pno=spj.pno and s.city!=j.city
22 select distinct x.sno from spj x where not exists (select * from j where not exists (select * from spj y where y.sno=x.sno and y.pno=x.pno and y.jno=j.jno))
23 select distinct pno from spj where jno in (select jno from j where city=('上海'))
24 select distinct jno from spj spjz where not exists (select * from spj spjx where sno='s1' and not exists(select * from spj spjy where spjy.pno=spjx.pno and spjy.jno=spjz.jno))
25 update P set color ='橙'where color='红'
26 delete from p where color='红'
27 select sum(distinct qty) from spj where sno='s1' and pno='p1'
28 select count(distinct sno) from spj group by jno
29 select jno from spj group by jno having sum(qty)>1000
30 insert into spj (sno,jno,pno,qit) values(s1,j3,p3,500)
);
评论10