--第三大题
--3.1
select *
from S
where A=10;
--3.2
select A,B
from S;
--3.3
select A,B,C,D,E,F
from S,T
where S.c=T.c AND S.d=T.d
--3.4
select *
from S,T
where S.c=T.c
--3.5
select *
from S,T
where A<E
--3.6
select
from (select C,D from S) as S1,T;
--第四大题
--建立表
create table S(SNO char(2),
SNAME char(10),
STATUS smallint,
CITY char(10))
create table P(PNO char(2),
PNAME char(10),
COLOR char(2),
WEIGHT smallint)
create table J(JNO char(2),
JNAME char(10),
CITY char(10))
create table SPJ(SNO char(2),
PNO char(2),
JNO char(2),
QTY smallint)
INSERT INTO S
VALUES ('S1','精益',20,'天津');
INSERT INTO S
VALUES ('S2','盛锡',10,'北京');
INSERT INTO S
VALUES ('S3','东方红',20,'北京');
INSERT INTO S
VALUES ('S4','丰泰盛',20,'天津');
INSERT INTO S
VALUES ('S5','为民',20,'上海');
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);
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);
--(1)
select distinct SNO
from SPJ
where JNO = 'J1';
--(2)
select SNO
from SPJ
where JNO = 'J1' AND PNO='P1';
--(3)
select SNO
from SPJ
where JNO='J1' AND PNO IN (select PNO from P where COLOR = '红');
--(4)
select distinct JNO
from SPJ
where NOT(SNO IN (select SNO
from S
where CITY='天津')
AND
PNO IN (select PNO
from P
where COLOR ='红'))
--(5)
--没有一个零件s1供应了,而j没有用
select distinct PNO
from SPJ
where SNO='S1';
select JNO
from J
where NOT EXISTS ( select *
from SPJ SPJX
where SPJX.SNO='S1' AND NOT EXISTS( select *
from SPJ SPJY
where SPJX.SNO=SPJY.SNO AND SPJX.PNO=SPJY.PNO AND J.JNO=SPJY.JNO))
--第五大题
--5.1
select SNAME,CITY
from S;
--5.2
select PNAME,COLOR,WEIGHT
from P;
--5.3
select distinct JNO
from SPJ
where SNO='S1';
--5.4
select PNAME,QTY
from SPJ,P
where SPJ.PNO=P.PNO AND SPJ.JNO='J2';
--5.5
select PNO
from SPJ,S
where S.SNO=SPJ.SNO AND S.CITY='上海'
--5.6
select JNO
from SPJ,S
where S.SNO=SPJ.SNO AND S.CITY='上海'
--5.7
select JNO
from J
where NOT EXISTS(select *
from SPJ,S
where J.JNO=SPJ.JNO AND SPJ.SNO=S.SNO AND CITY='天津');
--5.8
update P
set COLOR='蓝色'
where COLOR='红色'
--5.9
update SPJ
set SNO=S3
where SNO='S5' AND JNO='J4' AND PNO='P6';
--5.10
delete
from SPJ
where SNO='S2'--这里是不是可以用cascade 有待思考
delete
from S
where SNO='S2'
--5.11
insert
into SPJ
values('S2','J6','P4',200)
--第九大题
create view SJ_QTY
as
select SNO,PNO,QTY
from SPJ,J
where SPJ.JNO=J.JNO AND J.JNAME='三建'
--9.1
select PNO,QTY
from SJ_QTY;
--9.2
select PNO,QTY
from SJ_QTY
where SNO='S1';
做了但是还没有检查答案,感觉查询那确实很难。
文章目录 Docker概念 启动第一个Docker容器 Docker执行流程以及一些配置镜像仓库...
我们已经写了两篇关于 Ansible 的文章,这是第三篇。 如果你是 Ansible 新手,我...
企业开源应用势不可挡容器技术将引领新一轮云计算技术浪潮。据 Gartner预测到202...
很快2020年就要过去了,而Adobe Flash Player也要跟大家彻底说再见了, 微软已经...
引言 在前端项目开发过程中,el-table展示的结果列使用组件形式引入,其中某些字...
写在前面 在实际工作中有一种非常普遍的并发场景那就是读多写少的场景。在这种场...
CSS Code 复制内容到剪贴板 .bottomTable{ background-color : rgb (249,249,249...
WPF中使slide控件拖动完成后改变变量值这个问题一开始觉得还是挺简单的,网上也...
一般的表单都是通过ajax方式提交,所以碰到带文件上传的表单就比较麻烦。基本原...
复制代码 代码如下: object ID="javademo" CLASSID="clsid:CFCDAA03-8BE4-11cf-B...