⽤SQL语句建⽴第2章习题6的4个表：针对建⽴的4个表⽤sql完成第2章习题6的
查询
第三章第4和5题
建表语句和插⼊表转载于:
blog.csdn/weixin_42545675/article/details/100513076
1创建表
CREATE TABLE S(
SNO CHAR(3)PRIMARY KEY,
SNAME CHAR(10),
STATUS CHAR(2),
CITY CHAR(10));
CREATE TABLE P(
PNO CHAR(3),
PNAME CHAR(10),
COLOR CHAR(4),
WEIGHT INT,
PRIMARY KEY(PNO));
CREATE TABLE J(
JNO CHAR(3),
JNAME CHAR(10),
CITY CHAR(10),
PRIMARY KEY(JNO));
CREATE TABLE SPJ(
SNO CHAR(3),
PNO CHAR(3),
JNO CHAR(3),
QTY INT,
PRIMARY KEY(SNO,PNO,JNO),
FOREIGN KEY(SNO)REFERENCES S(SNO),
FOREIGN KEY(PNO)REFERENCES P(PNO),
FOREIGN KEY(JNO)REFERENCES J(JNO)
);
2插⼊表
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','上海');
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); INSE
RT 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);
3第三章第4题
1求供应⼯程J1零件的供应商号码SNO
select distinct sno
from spj
where jno='J1';
2求供应⼯程J1零件p1的供应商号码SNO
select distinct sno
from spj
where jno='J1'and pno='P1';
3求供应⼯程J1零件为红⾊的供应商号码SNO
嵌套查询
select sno
from spj
where jno='j1'
and pno in/*出红⾊的零件号码pno*/
(select pno
from p /*从p表中*/
where color='红');
或⽤连接查询
select sno
from spj,p
where p.pno = spj.pno and-- 连接两个表
jno='J1'and p.color='红';
4求没有使⽤天津供应商⽣产的红⾊零件的⼯程号
⼯程表J中不包含: 1和2为嵌套关系
1天津的供应商⽣产的
2红⾊的零件
select jno
from j -- 从j表⼊⼿，以包含那些尚未使⽤任何零件的⼯程号
where not exists
(select*
from spj
where spj.jno = j.jno
and sno in
(select sno -- 天津供应商的sno
from s
where city='天津')
and pno in
(select pno -- 红⾊零件的pno
from p
where color='红'));
或
select Jno
from j
where not exists
(select*
from spj,s,p
where spj.sno =s.sno and spj.jno = j.jno
and spj.pno = p.pno -- 两个and，三个等于⽤于，连接三个表
and s.city ='天津'
lor ='红');
5求⾄少⽤了供应商s1所供应的全部零件的的⼯程号JNO
select distinct jno
from spj spjz
where not exists-- 这是⼀个相关⼦查询
(select*-- ⽗查询和⼦查询均引⽤了spj表
from spj spjx -- ⽤别名spjz,spjx将⽗查询与⼦查询分开
where sno='s1'
and not exists
(select*-- ⽤别名spjy与⽗查询中spj表分开
from spj spjy
where spjy.pno = spjx.pno
and spjy.jno = spjz.jno));
select*-- ⽗查询和⼦查询均引⽤了spj表
from spj spjx -- ⽤别名spjz,spjx将⽗查询与⼦查询分开
where sno='s1'
and not exists
(select*-- ⽤别名spjy与⽗查询中spj表分开
from spj spjy
where spjy.pno = spjx.pno);-- 查询结果为0
会循环显⽰sno不是s1的项⽬
select distinct fromselect*
from spj spjx ,spj spjy -- ⽤别名spjz,spjx将⽗查询与⼦查询分开
where not spjx.sno='s1'and not spjy.pno = spjx.pno;
4第三章第5题
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 Pname,QTy
from spj,p
where spj.pno = p.pno
and spj.jno='j2';
5出上海⼚商供应的所有零件号码
select distinct Pno
from spj
where sno in
(select sno
from s
where s.city='上海');
select distinct pno
from s,spj
where spj.sno=s.sno
and s.city='上海';
6出使⽤上海产的零件的⼯程名字
select Jname
from j
where jno in
(select jno
from s,spj
where s.sno=spj.sno
and s.city='上海');
或
select jname
from j,s,spj
where j.jno=spj.jno and s.sno=spj.sno
and where s.city='上海');
7出没有使⽤天津产的零件的⼯程号
select jno
from j
where not exists
(select*
from spj
where spj.jno=j.jno
and sno in
(select sno
from s
where1=1/*s.sno=spj.sno 可有可⽆*/
and city='天津'));
或
select jno
from j
where not exists
(select*
from spj,s
where spj.jno=j.jno
and spj.sno=s.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 jno='j4'and pno='p6';
10从供应商关系删除s2的记录，并从供应关系中删除相应的记录
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论