CREATE TABLE S
(Sno CHAR(4) PRIMARY KEY,
Sname CHAR(20) UNIQUE,
Sstatus int,
Citychar(10),
);
CREATE TABLE P
(Pno CHAR(4) PRIMARY KEY,
Pname CHAR(20),
Color char(4),
Weight float(4),
);
CREATE TABLE J
(Jno char(4) PRIMARY KEY,
Jname CHAR(20) UNIQUE,
CityCHAR(10),
);
CREATE TABLE SPJ
(Sno char(4),
Pnochar(4),
Jnochar(4),
QTYint,
primary key(Sno,Pno,Jno),
foreign key(Sno) references S(Sno),
foreign key(Pno) references P(Pno),
foreign key(Jno) references J(Jno),
);
insert into S values('S1','Smith',20,'London');
insert into S values('S2','Jones',10,'Paris');
insert into S values('S3','Blake',30,'Paris');
insert into S values('S4','Clark',20,'London');
insert into S values('S5','Adams',30,'Athens');
insert into S values('S6','Brown',null,'New York');
alter table P alter column Color char(5);
insert into P values('P1','Nut','Red',12);
insert into Pvalues('P2','Bolt','Green',17);
insert into Pvalues('P3','Screw','Blue',14);
insert into P values('P4','Sew','Red',14);
insert into P values('P5','Cam','Blue',12);
insert into P values('P6','Cog','Red',19);
insert into Jvalues('J1','Sorter','Paris');
insert into J values('J2','Punch','Rome');
insert into J values('J3','Reader','Athens');
insert into J values('J4','Console','Athens');
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','P5','J2',100);
/*定义S表、P表、J表和SPJ表时，除S表中的STATUS属性外，其余各属性均不为空*/
alter table S add constraint Sno check (Snois not null);
/*S表中的SNAME属性和P表中的PNAME属性取值均唯一*/
alter table S add unique(Sname);
alter table P add unique(Pname);
/*定义产品的颜色只允许取“Red”、“Yellow”、“Green”或“Blue”*/
alter table P add constraint Color check(Color in('Red','Yellow','Green','Blue'));
/*定义厂家位于“London”时其STATUS（状态）均为20*/
alter table S add constraint Sstatus check(City<>'London'or Sstatus=20);
/*定义S表、P表、J表和SPJ表上的主码和外码，以保证实体完整性和参照完整性。*/
/*已定义*/
--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',200);
INSERT INTO SPJ VALUES('S5','P6','J4',500);
--Sno(供应商号),Sname(供应商名),Status(状态),City(供应商所在城市)
--Pno(零件代码),Pname(零件名),Color(颜色),Weight(重量)
--Jno(工程号),Jname(工程名),City(工程所在城市),QTY(供应数量)
--针对实验一建立的SPJ数据库查询
--一、简单查询
--1.查询供应工程J1零件P1的供应商号码SNO。
select Sno from SPJ where Jno='J1' ANDPno='P1';
--2.查询所有供应商的姓名和所在城市。
select Sname,City from S;
--3.查询所有零件的名称、颜色、重量。
select Pname,Color,Weight from P;
--4.查询使用供应商S1所供应零件的工程号。
select Jno from SPJ where Sno='S1';
--5.查询为工程J1供货的供应商号，并按供应商号升序排列。
select distinct Jno,Sno from SPJ where Jno='J1' order by Jno ASC;
--6.查询供货量在300-500之间的所有供货情况。
select * from SPJ where QTY between 300 and500;
--7.查询由供应者S1提供零件的工程项目总数。
select count(distinct Jno) from SPJ whereSno='S1';
--8.查询由供应者S1提供零件P1的总量及总供应量。
select count(Pno)Number ,sum(QTY)Quantityfrom SPJ where Sno='S1' and Pno='P1';
--二、普通多表连接查询
--9.查询供应工程J1零件为红色的供应商号码SNO。
select distinct Sno from SPJ,P whereJno='J1' and color='红';
--10.查询工程项目J2使用的各种零件的名称及其数量。
select P.Pname,count(SPJ.Pno) from SPJ,Pwhere SPJ.Pno=P.Pno and Jno='J2' group by P.Pname;
--11.查询使用上海供应商供应零件的工程名称。
select J.Jname from SPJ,J whereSPJ.Jno=J.Jno and CITY='上海';
--12.查询重量15以下，Paris供应的零件代码和零件名。
select P.Pno,P.Pname from SPJ,P,S whereSPJ.Pno=P.Pno and SPJ.Sno=S.Sno and CITY='Paris'and weight<15;
--13.查询由S1提*品的工程名。
select J.Jname from SPJ,S,J whereSPJ.Jno=J.Jno and SPJ.Sno=S.Sno and SPJ.Sno='S1';
--14.查询使用了颜色为Red产品、在城市Athens的工程名。
select P.Pname,J.Jname from SPJ,P,J whereSPJ.Jno=J.Jno and SPJ.Pno=P.Pno and J.Jname='Athens' and color='red';
--15.查询由London的供应商供给London的工程的零件名。
select Jname from SPJ,S,J whereSPJ.Jno=J.Jno and SPJ.Sno=S.Sno and J.Jname='London'and S.Sname='London';
--16.查询给天津的工程提供齿轮零件的供应商代码和供应商名。
select S.Sno,S.Sname from SPJ,J,P,S whereSPJ.Jno=J.Jno and SPJ.Pno=P.Pno and SPJ.Sno=S.Sno and J.Jname='天津'and P.Pname='齿轮';
--三、自表查询
--17.查询状态高于Clark的所有厂家号和厂家名。
select Sno,Sname from s where status>(selectstatus from s where Sname='Clark');
--18.查询与供应商 S2 在同一城市的供应商代码和供应商名。
select Sno,Sname from s where CITY=(selectcity from s where Sno='S2')and Sno!='S2';
--or
select x.Sno,x.Sname from S x,S y wherex.CITY=y.CITY and y.Sno='S2'and x.Sno!='S2';
--19.查询与零件名Nut颜色相同的零件代码和零件名。
select Pno,Pname from P where color=(selectcolor from P where Pname='Nut' )and Pname!='Nut';
--or
select pno,pname from p px wherepx.color=(select py.color from p py where py.pname='Nut');
--四、外连接查询
--20.查询每个工程及其使用零件的情况。
select J.*,P.* from J,P,S left outer joinSPJ on (S.Sno=SPJ.Sno) where SPJ.Jno=J.Jno and P.Pno=SPJ.Pno;
--五、表示否定的查询
--21.查询没有使用天津生产的零件的工程号码。
select Jno from S,SPJ,J where SPJ.Sno=S.Snoand SPJ.Sno=J.Jno and J.City<>'天津';
--22.查询未采用由London供应者提供颜色为Red零件的工程名。
select Jname from J,SPJ where SPJ.Sno=J.Jnoand SPJ.Jno not in (select Jno from SPJ,S,P where SPJ.Pno=P.Pno andSPJ.Sno=S.Sno and Sname='London'and Color='red');
--23.查询不使用零件代码为P2的工程号和工程名。
select J.Jno,Jname from J,SPJ,P whereSPJ.Sno=J.Jno and SPJ.Pno=P.Pno and P.Pno<>'P2';
--24.查询供应商S3没有供应的零件名。
select Pname from SPJ,P where SPJ.Pno=P.Pnoand not exists (select SPJ.Pno from P,SPJ where SPJ.Pno=P.Pno and Sno='S3');
--25.查询不供应红色产品的厂家号和厂家名。
select distinct S.Sno,Sname from SPJ,Swhere SPJ.Sno=S.Sno and Jno not in (select Jno from SPJ,P where SPJ.Pno=P.Pnoand Color='Red');
--六、表示只（不…非）的查询
--26.查询只供应红色零件的供应商代码和供应商名。
select distinct Sno,Sname from S where notexists (select * from P,SPJ where Color<>'Red' and SPJ.Sno=S.Sno );
--27.查询只使用了供应商S1所供应的零件的工程号和工程名。
select distinct Jno,Jname from J where notexists (select * from S,SPJ where S.Sno<>'S1' and J.Jno=SPJ.Jno );
--28.查询全部由S2提供零件的工程名。
select distinct Jname from J where notexists (select * from S,SPJ where S.Sno<>'S2' and J.Jno=SPJ.Jno );
--七、表示蕴含（至少）的查询
--29.查询至少使用供应商S1所供应的全部零件的工程名。
select distinct Jname from J,SPJ SPJ3 whereJ.Jno=SPJ3.Jno and not exists(
select* from SPJ SPJ1 where Sno='S1' and not exists(
select* from SPJ SPJ2 where SPJ2.Pno=SPJ1.Pno and SPJ2.Jno=SPJ3.Jno));
--30.查询至少给London的所有工程都供应零件的供应商代码和供应商名。
select distinct S.Sno,Sname from S,SPJ SPJ2where S.Sno=SPJ2.Sno and not exists(
select* from J where City='London'and not exists(
select* from SPJ SPJ1 where SPJ1.Jno=J.Jno and SPJ1.Sno=SPJ2.Sno));
--八、表示全称（全部、所有…都）的查询
--31.求使用了全部零件的工程名
select Jname from J where not exists(
select* from P where not exists(
select* from SPJ where Pno=P.Pno and Jno=J.Jno));
--32.查询所有London供应商都供应的零件名。
select Sname from S where not exists(
select* from P where not exists(
select* from SPJ where Pno=P.Pno and Sno=S.Sno));
--33.查询London 的所有工程都使用的零件名。
select Pname from P where not exists(
select* from S where City='London'and not exists(
select* from SPJ where Pno=P.Pno and Sno=S.Sno));
--34.查询使用了全部零件的工程名。
select Pname from P where not exists(
select* from J where City='London'and not exists(
select* from SPJ where Pno=P.Pno and Jno=J.Jno));
--九、表示交集的查询
--35.查询（至少）供应零件代码为P1、P2两种零件的供应商名。
select Sname from S where Sno in(select Snofrom SPJ where Pno='P1' intersect select Sno from SPJ where Pno='P2');
--十、表示差集的查询
--36.查询查询供应零件代码为P2但不供应P3的供应商名。
select Sname from S where Sno in (selectSno from SPJ where Pno='P2' except select Sno from SPJ where Pno='P3');