数据库查询2

数据库查询2

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');