记一次数据库课后作业：SQL查询应用

2023-11-09 18:49:16

本文仅学习使用

创建关系

仅简单创建了几个关于工程的关系

create table S           --创建供应商关系
( S#    varchar(10),     --供应商号码
  SNAME varchar(10),     --供应商名字
  SCITY varchar(10),     --供应商城市
  primary key(S#));      --主码为S#
  insert into S 
values('S1','东','Nanjing');
insert into S 
values('S2','南','Shanghai');
insert into S 
values('S3','西','Beijing');
insert into S 
values('S4','北','Shanghai');
  
create table P       --创建零件关系
( P#    varchar(10),    --零件号
  PNAME varchar(10),   --零件名字
  COLOR varchar(10),    --零件颜色
  WEIGHT smallint,   --零件重量
  primary key(P#));  
insert into P
values('P1','壹','red',10);
insert into P
values('P2','贰','yellow',18);
insert into P 
values('P3','叁','blue',20);
insert into P 
values('P4','肆','red',22);   
  
create table J       --创建工程关系
( J#   varchar(10),    --工程号
  JNAME varchar(10),   --工程名
  JCITY varchar(10),    --工程所在城市
  primary key(J#));
  insert into J
values('J1','一','北京');
insert into J
values('J2','二','上海');
insert into J 
values('J3','三','天津');
insert into J 
values('J4','四','南京');
  
create table SPJ       --创建SPJ关系
( S#   varchar(10),
  P#   varchar(10),
  J#   varchar(10),
  QTY varchar(20),     --供应商、零件、工程三者的关系
  primary key(S#,P#,J#),
  foreign key(S#) references S,
  foreign key(P#) references P,
  foreign key(J#) references J,);
INSERT INTO SPJ(S#,P#,J#,QTY) VALUES('S1','P1','J1',200);
INSERT INTO SPJ(S#,P#,J#,QTY) VALUES('S1','P1','J3',100);
INSERT INTO SPJ(S#,P#,J#,QTY) VALUES('S1','P1','J4',700);
INSERT INTO SPJ(S#,P#,J#,QTY) VALUES('S1','P2','J2',100);
INSERT INTO SPJ(S#,P#,J#,QTY) VALUES('S2','P3','J1',400);
INSERT INTO SPJ(S#,P#,J#,QTY) VALUES('S2','P3','J2',200);
INSERT INTO SPJ(S#,P#,J#,QTY) VALUES('S2','P3','J4',500);
INSERT INTO SPJ(S#,P#,J#,QTY) VALUES('S2','P3','J3',400);
INSERT INTO SPJ(S#,P#,J#,QTY) VALUES('S2','P4','J1',400);
INSERT INTO SPJ(S#,P#,J#,QTY) VALUES('S2','P4','J2',100);

a.找出给project J1 和J2都提供零件的所有供应商号码

select S#
from SPJ
where J#='J1' AND J#='J2';  --写法1

(select S#
FROM SPJ
WHERE J#='J1')
INTERSECT
(select S#
FROM SPJ
WHERE J#='J2') ;             --写法2
 
select distinct S#
FROM SPJ
WHERE J#='J1' AND
	  S# IN(SELECT S#
			FROM SPJ
			WHERE J#='J2'); --写法3

b.找出为北京或上海的任何工程提供了零件的全部供应商号码

select S#
FROM SPJ ,J
where JCITY='Beijing'or JCITY='Shanghai'and SPJ.J#=J.J#;   --写法1

(select S#
FROM SPJ ,J
where JCITY='Beijing' AND SPJ.J#=J.J#)
UNION
(select S#
FROM SPJ ,J
where JCITY='Shanghai' AND SPJ.J#=J.J#);                    --写法2

select S#
FROM SPJ,J
WHERE JCITY='Beijing' and SPJ.J#=J.J# or
	  S# IN (SELECT S#
			 FROM SPJ,J
			 WHERE JCITY='Beijing' and SPJ.J#=J.J#);       --写法3

c 找出所有为上海的工程提供零件的所有上海的供应商号码

SELECT S.S#
from S,J,SPJ
WHERE J.J#=SPJ.J# and S.S#=SPJ.S# AND JCITY='Shanghai' and SCITY='Shanghai';  --写法1

(SELECT S.S#
FROM S,J,SPJ
WHERE J.J#=SPJ.J# and S.S#=SPJ.S# AND JCITY='Shanghai' )
INTERSECT
(SELECT S.S#
FROM S,J,SPJ
WHERE J.J#=SPJ.J# and S.S#=SPJ.S# AND SCITY='Shanghai');    --写法2

SELECT S.S#
FROM S,J,SPJ 
WHERE  J.J#=SPJ.J# and S.S#=SPJ.S# AND JCITY='Shanghai'AND
	   S.S# IN(SELECT S.S#
			   FROM S,J,SPJ 
			   WHERE  J.J#=SPJ.J# and S.S#=SPJ.S# AND SCITY='Shanghai'); --写法3

d.找出从不提供红色零件的北京供应商号码

(select S.S#
FROM S
WHERE SCITY='Beijing')
except
(select SPJ.S#
FROM SPJ,P
WHERE SPJ.P#=P.P# AND COLOR='red');          --写法1

select S.S#
FROM S
WHERE SCITY='Beijing' AND
      S.S# NOT IN (select SPJ.S#
				   FROM SPJ,P
				   WHERE SPJ.P#=P.P# AND COLOR='red');   --写法2
				   
select S.S#
FROM S
WHERE SCITY= 'Beijing' and 
       not exists(select SPJ.S#
				  FROM SPJ,P
                  WHERE SPJ.P#=P.P# AND COLOR='red');    --写法3

e 找出至少使用供应商S1提供的零件的工程的工程号码://只要用过s1提供过的零件就可以(题目理解1）

select distinct J#
FROM SPJ
WHERE S#='S1';

–e找出至少使用供应商S1所供应的全部零件的工程号码：（题目理解2）
–间接法：不存在这样的零件y：供应商s1供应了y,而工程没有选择y（不存在工程使用y);

select distinct J#
FROM SPJ AS SPJ3   --第三步：下一行的not exists表示若这种情况不存在则这个工程符合

where not exists (select *                          --第一步找出S1供应的所有零件
				  from SPJ AS SPJ1           
				  WHERE S#='S1'and not exists(select*      --第二步找出其他工程使用过的S1零件，取不存在：找出其他工程没有使用过S1提供的零件
											  from SPJ AS SPJ2
											  where SPJ1.P#=SPJ2.P#
											      AND SPJ2.J#=SPJ3.J#)); --返回该工程编号，让SPJ3接收

–直接法：首先找出S1供应商供应的全部零件集合P1

/*
select A.P#
FROM SPJ AS A
where A.S#='S1';

--再对某一个工程找出其使用的零件集合Pn
SELECT B.P#
FROM SPJ AS B
WHERE B.J#='某工程的工程号' 

--如果P1⊆Pn，则说明该工程使用了S1供应商供应的全部零件
--只要求P1-Pn，若P1-Pn为空集（查询结果不存在），则说明P1⊆Pn。
select *
from (SELECT A.P#
	  FROM SPJ AS A                  --P1
	  WHERE A.S#='S1')AS C
WHERE C.P# NOT IN(SELECT B.P#        -- -Pn
				  FROM SPJ AS B
				  WHERE B.J#='某工程的工程号' --返回为空则说明P1⊆Pn         
*/
SELECT D.J#      --用D.J#代替B.J#='某工程的工程号' 的后者的占位，遍历J表中的每一个工程
FROM J AS D  
WHERE NOT EXISTS(SELECT*
				FROM (SELECT A.P# 
					  FROM SPJ AS A
					  WHERE A.S#='S1')AS C
			    WHERE C.P# NOT IN(SELECT B.P#
								  FROM SPJ AS B
								  WHERE B.J#=D.J#));

–f.找出提供所有零件的供应商姓名
–直接法：找出P关系中的所有零件集合P1,找出某一供应商供应的所有零件集合Pn
–若P1⊆Pn（P1-Pn查询结果不存在），则找出了提供所有零件的供应商

select SNAME
FROM S 
WHERE NOT EXISTS(select A.P#
				 from P AS A
				 WHERE A.P# NOT IN(SELECT B.P#
								   FROM SPJ AS B
								   WHERE B.S#=S.S#));

–g 找出这样的供应商的号码：供应商S1能提供的零件该供应商都能提供
–令S1所提供的零件集合为P1，该供应商能提供的零件为Pn
–若P1⊆Pn（P1-Pn查询结果不存在），则找出了S1能提供的零件该供应商都能提供的供应商

select S#
FROM S
WHERE NOT EXISTS(SELECT *
				 FROM (SELECT A.P#
					   FROM SPJ AS A
				        WHERE A.S#='S1' ) AS C   --注意集合里的元素的零件，零件与零件之间相比较
				 WHERE C.P# NOT IN(SELECT B.P#
										FROM SPJ AS B
										WHERE B.S#=S.S#));

码农公寓

创建关系

相关文章