没有合适的资源?快使用搜索试试~ 我知道了~
数据库第三次作业 18373528 杨凌华1
需积分: 0 0 下载量 55 浏览量
2022-08-03
15:37:11
上传
评论
收藏 1.05MB PDF 举报
温馨提示
![preview](https://dl-preview.csdnimg.cn/86290673/0001-88183abb8cc50e2f3631a93659b2c270_thumbnail.jpeg)
![preview-icon](https://csdnimg.cn/release/downloadcmsfe/public/img/scale.ab9e0183.png)
试读
11页
1、综合统一 2、高度非过程化 3、面向集合的操作方式 4、以同一种语法结构提供两种使用方式 5、语言简洁,易学易用
资源详情
资源评论
资源推荐
![](https://csdnimg.cn/release/download_crawler_static/86290673/bg1.jpg)
数据库第三次作业-习题3
——杨凌华18373528
T1、SQL的特点
1、综合统一
2、高度非过程化
3、面向集合的操作方式
4、以同一种语法结构提供两种使用方式
5、语言简洁,易学易用
T3、
T4、
(1)
SELECT *
FROM S
WHERE A = 10;
(2)
SELECT DISTINCT A, B
FROM S;
(3)
SELECT A, B, S.C, S.D, E, F
FROM S, T
WHERE S.C = T.C AND S.D = T.D;
(4)
SELECT A, B, S.C, S.D, T.C, T.D, E, F
FROM S, T
WHERE S.C = T.C;
(5)
SELECT A, B, S.C, S.D, T.C, T.D, E, F
FROM S, T
WHERE A < E;
(6)
SELECT S_.C, S_.D, T.C, T.D, E, F
FROM T, (SELECT DISTINCT C, D FROM S) AS S_;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
S表:
S(SNO,SNAME,STATUS,CITY);
Create Table S
(SNO char(5) not null unique,
SNAME char(20) not null,
STATUS int,
CITY char(15),
Primary Key(SNO));
INSERT INTO S VALUES ('S1', '精益', 20, '天津');
INSERT INTO S VALUES ('S2', '盛锡', 10, '北京');
INSERT INTO S VALUES ('S3', '东方红', 30, '北京');
1
2
3
4
5
6
7
8
9
10
11
12
![](https://csdnimg.cn/release/download_crawler_static/86290673/bg2.jpg)
INSERT INTO S VALUES ('S4', '丰泰盛', 20, '天津');
INSERT INTO S VALUES ('S5', '为民', 30, '上海');
P表:
P(PNO,PNAME,COLOR,WEIGHT);
Create Table P
(PNO char(5) not null unique,
PNAME char(5) not null,
COLOR char(1) not null,
WEIGHT int,
Primary key(PNO));
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);
J表:
J(JNO,JNAME,CITY);
Create Table J
(JNO char(5) not null unique,
JNAME char(10) not null,
CITY char(15),
Primary key(JNO));
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', '半导体厂', '南京');
SPJ表:
SPJ(SNO,PNO,JNO,QTY);
Create Table SPJ
(SNO char(5) not null,
PNO char(5) not null,
JNO char(5) not null,
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));
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);
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
![](https://csdnimg.cn/release/download_crawler_static/86290673/bg3.jpg)
T5
(1)
(2)
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 DISTINCT SNO FROM SPJ WHERE JNO = 'J1' AND PNO = 'P1';
(3) SELECT DISTINCT SNO FROM SPJ, P WHERE JNO = 'J1' AND SPJ.PNO = P.PNO
AND P.COLOR = '红';
(4) SELECT DISTINCT JNO FROM SPJ WHERE
SPJ.SNO NOT IN (SELECT SNO FROM S WHERE CITY = '天津') AND
SPJ.PNO NOT IN (SELECT PNO FROM P WHERE COLOR = '红');
(5)
SELECT DISTINCT JNO FROM SPJ X
WHERE NOT EXISTS
(SELECT * FROM SPJ Y
WHERE Y.SNO = 'S1' AND NOT EXISTS
(SELECT * FROM SPJ Z
WHERE Z.PNO = Y.PNO
AND Z.JNO = X.JNO));
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
SELECT SNAME, CITY FROM S;1
SELECT PNAME, COLOR, WEIGHT FROM P;1
剩余10页未读,继续阅读
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![docx](https://img-home.csdnimg.cn/images/20210720083331.png)
![avatar](https://profile-avatar.csdnimg.cn/0d995096ec5e497dafaf67be9c7c861a_weixin_35756624.jpg!1)
焦虑肇事者
- 粉丝: 58
- 资源: 310
上传资源 快速赚钱
我的内容管理 展开
我的资源 快来上传第一个资源
我的收益
登录查看自己的收益我的积分 登录查看自己的积分
我的C币 登录后查看C币余额
我的收藏
我的下载
下载帮助
![voice](https://csdnimg.cn/release/downloadcmsfe/public/img/voice.245cc511.png)
![center-task](https://csdnimg.cn/release/downloadcmsfe/public/img/center-task.c2eda91a.png)
安全验证
文档复制为VIP权益,开通VIP直接复制
![dialog-icon](https://csdnimg.cn/release/downloadcmsfe/public/img/green-success.6a4acb44.png)
评论0