SQL语句实验（查询、新建表、删除表、更新表）

涉及：查询、新建表、删除表、更新表操作

题目:

1.用 SQL 语句建立第二章习题5中的4个表。

--S表
create table S
(
SNO varchar(50) not null,
SNAME varchar(50) not null,
STATUS int,
CITY varchar(50)
)
insert into S(SNO,SNAME,STATUS,CITY) 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','上海');


--P表
create table P
(
PNO varchar(50) not null,
PNAME varchar(50) not null,
COLOR varchar(50),
WEIGHT int
)
insert into P(PNO,PNAME,COLOR,WEIGHT) 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');


--J表
create table J
(
JNO varchar(50) not null,
JNAME varchar(50) not null,
CITY varchar(50)
)
insert into J(JNO,JNAME,CITY) 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','半导体厂','南京');


--SPJ表
create table SPJ
(
SNO varchar(50) not null,
PNO varchar(50) not null,
JNO varchar(50),
QTY int
)

insert into SPJ(SNO,PNO,JNO,QTY) 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');

2.针对上述表试用 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 P.PNAME,SPJ.QTY
from P,SPJ
where P.PNO=SPJ.PNO
AND SPJ.JNO='J2';

 (5)找出上海厂商供应的所有零件号码。

select distinct SPJ.PNO--distinct去重
from S,SPJ
where S.SNO=SPJ.SNO
AND S.CITY='上海';

 (6)找出使用上海产的零件的工程名称。

select distinct JNAME
from S,J,SPJ
where S.CITY='上海'
AND S.SNO=SPJ.SNO
AND SPJ.JNO=J.JNO;

 (7)找出没有使用天津产的零件的工程号码。

select distinct JNO from J
where not exists(
select * from SPJ,S
where SPJ.JNO=J.JNO
and S.SNO=SPJ.SNO
and S.CITY='天津'
);

 (8)把全部红色零件的颜色改成蓝色。

update P
set COLOR='蓝'
where COLOR='红'

 

 (9)由S5供给J4的零件P6改为由S3供应。

update SPJ
set SNO='S3'
where SNO='S5'and PNO='P6' and JNO='J4'

 

 (10)从供应商关系中删除供应商号是S2的记录，并从供应情况关系中删除相应的记录。

delete from SPJ
where SNO='S2'
delete from S
where SNO='S2'

 

 

 (11)请将（S2,J6,P4,200）插入供应情况关系。

insert into SPJ
values('S2','J6','P4','200')