要用的表和数据:
CREATE TABLE S(
sno CHAR(4),
sname CHAR(10),
statu SMALLINT,
city CHAR(8)
);
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,'上海');
CREATE TABLE P(
pno CHAR(4),
pname CHAR(10),
color CHAR(10),
weight SMALLINT
);
INSERT INTO p VALUE('p1','螺母','红',12);
INSERT INTO p VALUE('p2','螺拴','绿',17);
INSERT INTO p VALUE('p3','螺丝刀','蓝',14);
INSERT INTO p VALUE('p4','螺丝刀','红',14);
INSERT INTO p VALUE('p5','凸轮','蓝',40);
INSERT INTO p VALUE('p6','齿轮','红',30);
CREATE TABLE j(
jno CHAR(4),
jname CHAR(10),
city CHAR(10)
);
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','半导体厂','南京');
CREATE TABLE spj(
sno CHAR(4),
pno CHAR(10),
jno CHAR(10),
qty SMALLINT
);
INSERT INTO spj VALUE('s1','p1','j1',200);
INSERT INTO spj VALUE('s1','p1','j3',100);
INSERT INTO spj VALUE('s1','p1','j4',700);
INSERT INTO spj VALUE('s1','p2','j2',100);
INSERT INTO spj VALUE('s2','p3','j1',400);
INSERT INTO spj VALUE('s2','p3','j2',200);
INSERT INTO spj VALUE('s2','p3','j4',500);
INSERT INTO spj VALUE('s2','p3','j5',400);
INSERT INTO spj VALUE('s2','p5','j1',400);
INSERT INTO spj VALUE('s2','p5','j2',100);
INSERT INTO spj VALUE('s3','p1','j1',200);
INSERT INTO spj VALUE('s3','p3','j1',100);
INSERT INTO spj VALUE('s4','p5','j1',100);
INSERT INTO spj VALUE('s4','p6','j3',300);
INSERT INTO spj VALUE('s4','p6','j4',200);
INSERT INTO spj VALUE('s5','p2','j4',100);
INSERT INTO spj VALUE('s5','p3','j1',200);
INSERT INTO spj VALUE('s5','p6','j2',200);
INSERT INTO spj VALUE('s5','p6','j4',500);
实验内容:
1. 建立一个数据库,数据库名称自定。
2. 在数据库中建立教材70-71页的四个表,并在各个表中插入记录。(这个步骤建议用SQL语句实现,并将SQL语句以文本文件的形式保存,便于以后使用。为提高效率,可以几个同学合作,每个同学负责建立一个表及插入表中的记录,命令运行没有错误后,提供给其他同学使用。)
3. 用SQL实现如下查询:
1) 找出所有供应商的姓名和所在城市
select SNAME,CITY
from S
2) 找出所有零件的名称,颜色,重量
select PNAME,COLOR,WEIGHT
from P
3) 找出使用供应商S1所供应零件的工程号码
select JNO
from SPJ
where SNO='S1'
4) 找出工程项目J2使用的各种零件的名称及其数量
select PNO,QTY
from SPJ
where JNO='J2'
5) 找出上海厂商供应的所有零件号码
select DISTINCT SPJ.PNO
from SPJ,S
where SPJ.SNO='S5' AND S.SNO=SPJ.SNO
6) 找出使用上海产的零件的工程名称
select DISTINCT J.JNAME
from S,J,SPJ
where J.JNO=SPJ.JNO AND S.SNO=SPJ.SNO AND S.CITY='上海'
7) 找出没有使用天津产的零件的工程号码
select J.JNO
from J
where NOT EXISTS(
select *
from S,SPJ
where S.CITY='天津' and s.SNO=SPJ.SNO
);
select J.JNO
from J
where jno not in(
select jno
from SPJ
where sno in
( select sno
from s
where city='天津'
)
);
8) 找出供应工程J1零件的供应商号码
select distinct S.SNO
from S,SPJ
where s.sno=spj.sno and spj.JNO='J1'
9) 找出供应工程J1零件P1的供应商号码
select distinct S.SNO
from S,SPJ
where s.sno=spj.sno and spj.JNO='J1' and SPJ.PNO='p1'
10) 找出供应工程J1零件为红色的供应商号码
select distinct SNO
from P,SPJ
where P.PNO=spj.pno and spj.JNO='J1' and SPJ.PNO='p1' and p.COLOR='红'
11) 找出没有使用天津供应商生产的红色零件的工程号码
select distinct jno
from spj
where sno not in
(select sno
from s
where city='天津') and PNO not in
( select p.PNO
from p
where COLOR='红'
);
select distinct SPJ.JNO
from s,p,spj
where s.CITY!='天津' AND p.COLOR!='红' and s.SNO=spj.SNO
12) 找出至少使用了供应商S1提供的所有零件的工程号码
Select distinct jno
from spj spjx
where not exists(
select *
from spj spjy
where spjy.sno='s1' and not exists(
select *
from spj spjz
where spjz.sno=spjy.sno and
spjz.pno=spjy.pno))
13) 找出没有使用天津产的零件的工程名称
select jname
from J
where not exists
( select *
from SPJ
where spj.JNO=j.JNO and (SPJ.SNO='S1' OR SPJ.SNO='S4')
)
14) 找出供应工程J1零件的供应商名称
select distinct SNAME
from S,SPJ
where JNO='J1' and SPJ.SNO=S.SNO
15) 找出供应工程J1零件P1的供应商名称
select distinct SNAME
from S,SPJ
where JNO='J1' and SPJ.SNO=S.SNO and PNO='P1'
16) 找出供应工程J1零件为红色的供应商名称
select JNAME
from J
where JNO in
( select JNO
from SPJ
where PNO in
( select PNO
FROM P
WHERE COLOR='红'
)
)
select distinct JNAME
from P,J,SPJ
where COLOR='红' and P.PNO=SPJ.PNO and SPJ.JNO=J.JNO
17) 找出没有使用天津供应商生产的红色零件的工程名称
select JNAME
from J
where JNO not in
( select JNO
from S,P,SPJ
where COLOR='红' and CITY='天津' and P.PNO=SPJ.PNO AND S.SNO=SPJ.SNO
)
18) 找出至少使用了供应商S1提供的所有零件的工程名称
SELECT DISTINCT JNAME
FROM J,SPJ SPJX
WHERE NOT EXISTS
( SELECT *
FROM SPJ SPJY
WHERE SPJY.SNO='S1' and
NOT EXISTS
( SELECT *
FROM SPJ SPJZ
WHERE SPJZ.SNO=SPJX.SNO AND
SPJZ.PNO=SPJX.PNO
)
)
19) 统计一共有多少种零件
SELECT COUNT(PNO)
FROM P
20) 统计每个工程所使用的零件种类
SELECT JNAME,COUNT(DISTINCT PNO)
FROM J,SPJ
WHERE J.JNO=SPJ.JNO
GROUP BY JNAME
21) 统计每个工程所使用的零件数量
SELECT JNAME,COUNT(*)
FROM J,SPJ
WHERE J.JNO=SPJ.JNO
GROUP BY JNAME
22) 找出提供零件种类超过了2种的供应商号码
SELECT SPJ.SNO
FROM S,SPJ
WHERE S.SNO=SPJ.SNO
GROUP BY SPJ.SNO
HAVING COUNT(distinct SPJ.PNO)>2
23) 找出提供零件种类超过了2种的供应商名称
SELECT SNAME
FROM S,SPJ
WHERE S.SNO=SPJ.SNO
GROUP BY SNAME
HAVING COUNT(distinct SPJ.PNO)>2
24) 找出为3个以上的工程提供零件的供应商号码
SELECT SPJ.SNO
FROM S,SPJ
WHERE S.SNO=SPJ.SNO
GROUP BY SPJ.SNO
HAVING COUNT(distinct JNO)>3
25) 找出为3个以上的工程提供零件的供应商名称
SELECT SNAME
FROM S,SPJ
WHERE S.SNO=SPJ.SNO
GROUP BY SNAME
HAVING COUNT(distinct JNO)>3