数据库二级课后习题

给定供应商供应零件的数据库db_sp，其中包含了供应商表S /零件P和供应情况表SP，表结构如下：

• 供应商S（sNO，sName，状态，城市），各部分的含义为供应商号供应商名称，状态和所在城市，状态为整型，其他为字符型
• 零件P（pNo，pName，颜色，重量），表示：零件编号，零件名称，颜色和重量，重量为浮点型，其他为字符型供应
• SP（sNo，pNo，qty）供应商编号，零件编号，供应量，qty为整型其他为字符型

使用Mysql控制台客户端创建一个名称为db_sp的数据库

create database db_sp charset = utf8;

请使用MYSQL控制台客户端在数据库db_sp中创建表，要求如下

1. 定义表S，表P，表SP的主键和外键，以保证参照并和实体合并
2. S表中的S名称属性取值不为空且不唯一
3. 定义产品颜色只能在"Red",“Yellow”,“Green”,“Blue”
4. 定义供应商所在城市为伦敦时其状态为20

添加表内容

数据表S:
create table S( 
sNo varchar(10), 
sName varchar(20) not null unique, 
status int, 
city varchar(20), 
constraint pk_sNo primary key(sNo), 
constraint ch_status check(city!="London" or status=20)
)engine=innodb;

数据表P:
create table P( 
pNo varchar(10), 
pName varchar(20) not null, 
color varchar(10) check(color in("Red","Yellow","Green","Blue")), 
weight float, 
constraint pk_pNo primary key(pNo)
)engine=innodb;

数据表SP:
create table SP( 
sNo varchar(10), 
pNo varchar(10), 
qty int, 
constraint fk_sNo foreign key(sNo) references S(sNo),
constraint fk_pNo foreign key(pNo) references P(pNo)
)engine=innodb;

使用select语句完成下列问题

• 1、查询供应零件号为P1的供应商号码

select distinct sNo from SP where pNo="P1";

• 2、查询供货量在300〜500之间的所有供货情况

select * from SP where qty between 300 and 500;

• 3、查询供应红色零件的供应商号码和供应商名称

	select distinct S.sNo, S.sName from S 
	join SP join P on 
	S.sNo=SP.sNo and SP.pNo=P.pNo 
	where P.color="red";

• 4、查询重量在15以下，巴黎供应商供应商的零件和零件名

	select pNo, pName from P where weight<=15 and pNo in 
     (select pNo from SP where sNo in
     (select sNo from S where city="Paris"));

• 5、查询由伦敦供应商供应的零件名

select distinct pName from P, SP, S 
where S.sNo=SP.sNo and SP.pNo=P.pNo and city="London";

• 6、查询不供应红色零件的供应商名称

select sName from S where sNo not in 
(select sNo from SP join P on SP.pNo=P.pNo where color="Red");

• 7、查询供应商S3没有供应的零件名

select pName from P 
where not exists 
(select * from SP where SP.pNo=P.pNo and sNo="S3");

• 8、查询供应商零件为P1和P2的两个零件的供应商名称

select sName from S join SP 
 on S.sNo=SP.sNo
 where pNo="P1" and S.sNo in 
(select sNo from SP where pNo="P2");

• 9、查询与零件名称的颜色相同的零件和零件名称

select PX.pNO, PX.pName from P PX join P PY 
 on PX.color=PY.color
 where PY.pName="Nut" and PX.pName!="Nut";

• 10、查询供应了全部零件的供应商名称

select sName from S where not exists
(select * from P where not exists
(select * from SP where S.sNo=SP.sNo and SP.pNo=P.pNo)
);

• 1、insert语句添加数据内容

insert into S values
("S1", "Smith", 20, "London"),
("S2", "Jones", 10, "Paris"),
("S3", "Blake", 30, "Paris"),
("S4", "Clark", 20, "London"),
("S5", "Adams", 30, "Athens"),
("S6", "Brown", null, "New York");


insert into P values
("P1", "Nut", "Red", 12),
("P2", "Bolt", "Green", 17),
("P3", "Screw", "Blue", 17),
("P4", "Screw", "Red", 14),
("P5", "Cam", "Blue", 12),
("P6", "Cog", "Red",19)

insert into SP(sNo, pNo, qty) values
("S1", "P1", 200),
("S1", "P4", 700),
("S1", "P5", 400),
("S2", "P1", 200),
("S2", "P2", 200),
("S2", "P3", 500),
("S2", "P4", 600),
("S2", "P5", 400),
("S2", "P6", 800),
("S3", "P3", 200),
("S3", "P4", 500),
("S4", "P2", 300),
("S4", "P5", 300),
("S5", "P1", 100),
("S5", "P6", 200),
("S5", "P2", 100),
("S5", "P3", 200),
("S5", "P5", 400)

• 2、update将数据库db_sp表P中Blue零件的中量增加20%

update P set weight=weight*1.2 where color="Blue";

• 3、Delete将数据库db_sp表S中状态为空的供应商信息删除

delete from S where status is null;

• 4、Delete将数据库db_sp中没有零件的供应商信息删除

delete from S where sNo not in (select distinct sNo from SP);