hooyantsing's Blog

第44次课程

字数统计: 3.8k阅读时长: 20 min
2019/07/22

源辰74班

第44次课程

07.22.2019

**内容  **

**Oracle作业检查、复习及指导[蒋斌]  **

作业

SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
--练习2:
--创建部门表Department 字段DepartmentID(主键约束)DepartmentName(部门名称非空唯一约束)
--部门表添加三条以上的记录
--创建员工表 Employees 字段Employees(主键约束)EmployeeName(非空约束) sex(检查约束) age(检查约束) DepartmentID(外键约束和部门产生联系),LinkPhone(员工电话,非空唯一约束)
---员工表添加四条以上的记录
----分别为部门表和员工表创建序列,在添加记录时,主键通过序列自动生成

create table Department(
       DepartmentID number(8) primary key,
       DepartmentName varchar2(50) not null unique
);
select * from Department;
insert into Department values(d_up.nextval,'源辰教学部');
insert into Department values(d_up.nextval,'源辰教研部');
insert into Department values(d_up.nextval,'源辰招生部');
create sequence d_up start with 101 increment by 1;
create table Employees(
       Employess number(10),
       EmployeeName varchar2(50) not null,
       sex varchar2(4) constraint ck_employess_sex check(sex='男' or sex='女'),
       age number(4) constraint ck_employess_age check(age between 18 and 45),
       DepartmentID number(8) constraint fk_employess_did references Department(DepartmentID),
       LinkPhone varchar2(30) not null unique
);
select * from Employees;
create sequence e_up start with 192168 increment by 1;
insert into Employees values(e_up.nextval,'张三','男',20,101,46552134856);
insert into Employees values(e_up.nextval,'李四','男',31,102,89851234856);
insert into Employees values(e_up.nextval,'王五','女',24,103,36485157422);
insert into Employees values(e_up.nextval,'麻六','男',41,101,89889871313);

SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
/*
--练习1:
--创建供应商信息表Supplier,字段:SupplierID:(主键约束)SupplierName(唯一约束),Phone(电话),Address(非空约束),PostalCode(邮编),City(非空约束)
--供应商信息表添加三条以上的记录。
--商品信息表Products 的字段 ProductID(主键) ProductName(非空约束)SupplierID(外键约束指向供应商信息表的主键)
---商品信息表添加四条以上的记录
--分别为供应商信息表和商品信息表创建序列,在添加记录时,主键通过序列自动生成
*/

create table Supplier(
       SupplierID number(4) primary key,
       SupplierName varchar2(20) unique,
       Phone varchar2(30),
       Address varchar2(50) not null,
       PostalCode varchar2(20),
       City varchar2(50) not null
);
select * from Supplier;
delete from Supplier;
insert into Supplier values(s_up.nextval,'中国石油',1154685481,'北京市房山区',130611,'北京市');
insert into Supplier values(s_up.nextval,'中国石化',1154574381,'湖南省长沙市',536611,'长沙市');
insert into Supplier values(s_up.nextval,'中国钢铁',1753355481,'河北省唐山市',453453,'唐山市');
create table Products(
       ProductID number(4) primary key,
       ProductName varchar2(20) not null,
       SupplierID number(4) constraint fk_pdt_sid references Supplier(SupplierID)
);
select * from Products;
delete from Products;
insert into Products values(p_up.nextval,'汽油',1001);
insert into Products values(p_up.nextval,'柴油',1001);
insert into Products values(p_up.nextval,'煤油',1002);
insert into Products values(p_up.nextval,'不锈钢',1003);
create sequence s_up start with 1001 increment by 1;
create sequence p_up start with 001 increment by 1;

SQL

1
2
3
4
5
6
7
8
9
10
11
--需求:查询员工表工资前三名的员工,练习
create table hwcopy(
       empno number(10) primary key,
       ename varchar2(20),
       sal number(20),
       dr number(10)
);
insert into hwcopy(empno,ename,sal,dr) select empno,ename,sal,dense_rank() over(order by sal desc) from emp  ;
select * from hwcopy where dr<4;

select a.* from (select empno,ename,sal,dense_rank() over(order by sal desc) as do from emp) a where do<=3;

SQL

1
2
3
4
5
6
7
8
9
10
11
12
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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
create table Users(
       Id number(4) primary key,
       LoginPWD varchar2(10),
       NickName varchar2(10),
       Sex varchar2(6),
       StarID number(4) constraint fk_users_starid references star(Id),
       BloodTypeID number(4) constraint fk_user_bloodtypeid references bloodtype(Id)
);

create table star(
       Id number(4) primary key,
       Star varchar2(10)
);

create table bloodtype(
       Id number(4) primary key,
       BloodType varchar2(10)
);

select * from users;
select * from star;
select * from bloodtype;

create sequence seq_users start with 1 increment by 1;
create sequence seq_star start with 1 increment by 1;
create sequence seq_bloodtype start with 1 increment by 1;

insert into Users values (seq_users.nextval,'0000','豆豆','男',5,1);
insert into Users values (seq_users.nextval,'00A0','小强','男',4,4);
insert into Users values (seq_users.nextval,'0000','静静','男',3,2);
insert into Users values (seq_users.nextval,'00B0','Java','男',6,3);
insert into Users values (seq_users.nextval,'0CC0','bobo','男',2,1);
insert into Users values (seq_users.nextval,'0BB0','我爱Java','男',4,2);
insert into Users values (seq_users.nextval,'0AA0','风筝','女',1,1);
insert into Users values (seq_users.nextval,'A000','benben','男',1,1);
insert into Users values (seq_users.nextval,'000C','吕洞宾','男',11,1);
insert into Users values (seq_users.nextval,'A000','清凉一夏','女',6,2);

insert into star values (seq_star.nextval,'白羊座');
insert into star values (seq_star.nextval,'金牛座');
insert into star values (seq_star.nextval,'双子座');
insert into star values (seq_star.nextval,'巨蟹座');
insert into star values (seq_star.nextval,'狮子座');
insert into star values (seq_star.nextval,'处女座');
insert into star values (seq_star.nextval,'天秤座');
insert into star values (seq_star.nextval,'天蝎座');
insert into star values (seq_star.nextval,'射手座');
insert into star values (seq_star.nextval,'魔蝎座');
insert into star values (seq_star.nextval,'水瓶座');
insert into star values (seq_star.nextval,'双鱼座');

insert into bloodtype values (seq_bloodtype.nextval,'A型');
insert into bloodtype values (seq_bloodtype.nextval,'B型');
insert into bloodtype values (seq_bloodtype.nextval,'O型');
insert into bloodtype values (seq_bloodtype.nextval,'AB型');

--查出血型为“O型” 的用户姓名、性别
select users.nickname,users.sex,bloodtype.bloodtype
from users
left join bloodtype
on bloodtype.id = users.bloodtypeid
where bloodtype.bloodtype = 'O型';

--查出血型为“A型” 并且星座为“白羊座”的用户姓名 性别
select users.nickname,users.sex,bloodtype.bloodtype,star.star
from users
left join bloodtype
on bloodtype.id = users.bloodtypeid
left join star
on star.id = users.starid
where bloodtype.bloodtype = 'A型' and star.star = '白羊座';

--用户“.NET”更新自己的名字为“天外飞仙”,请编写SQL语句进行更新;
update users set NickName = '天外飞仙' where id = 4;
--查询输出用户的详细信息,输出结果如下图所示:
select users.nickname,users.sex,star.star,bloodtype.bloodtype
from users
left join bloodtype
on bloodtype.id = users.bloodtypeid
left join star
on star.id = users.starid
--查询用户的密码中包含字符“A”的用户姓名、性别
update users set NickName = '天外飞仙' where id = 4;
--查询输出用户的详细信息,输出结果如下图所示:
select users.nickname,users.sex,star.star,bloodtype.bloodtype
from users
left join bloodtype
on bloodtype.id = users.bloodtypeid
left join star
on star.id = users.starid
where users.loginpwd like ('%A%')

SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
--银行转账练习题
create table bank(
       accountID varchar2(10) primary key,
       userName varchar2(20) not null,
       balance number(10) constraint ck_bank_balance check (balance >= 0)
);

insert into bank values (101,'张三',900);
insert into bank values (102,'李四',1200);

update bank set balance=balance-1000 where accountID=101;
savepoint p1;
update bank set balance=balance+1000 where accountID=102;
rollback to savepoint p1;

select * from bank;

drop table bank;

delete from bank;

项目SQL

销售信息管理系统实现要求.doc

1
2
3
4
5
6
7
8
9
10
11
12
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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
--员工信息表
create table tbEmp(
       eid number(10) primary key,
       ename varchar2(20) not null unique  constraint ck_tbEmp_ename  check(length(ename) between 2 and 5),
       esex varchar2(10) default '男' not null  constraint ck_tbEmp_esex  check(esex in('男','女')),
       eAge number(3) not null constraint ck_tbEmp_eage check(eage  between 18 and 50),
       eaddr varchar2(30)
);
--产品信息表
create table tbProd(
       pid number(10) primary key,
       ptype varchar2(20) not null,
       pmark varchar2(20),
       pspec varchar2(20)
);
--销售信息表
create table tbSales(
       srno number(10) primary key,
       eid number(10) constraint fk_tbsales_eid references tbEmp(eid),
       pid number(10) constraint fk_tbsales_pid references tbProd(pid),
       pqty number(10) constraint ck_tbsales_pqty check(pqty>=0),
       pamount number(10) constraint ck_tbsales_pamount  check(pamount>=0),
       sdate date
);
create sequence tbemp_eid start with 10000 increment by 1;
create sequence tbprod_pid start with 1 increment by 1;
create sequence tbsales_srno start with 1 increment by 1;
insert into tbemp values (tbemp_eid.nextval,'张三','男',19,'湖南省长沙市');
insert into tbemp values (tbemp_eid.nextval,'李四','女',27,'湖南省衡阳市');
insert into tbemp values (tbemp_eid.nextval,'王五','男',24,'湖南省湘潭市');
insert into tbemp values (tbemp_eid.nextval,'麻六','男',25,'河北省保定市');
insert into tbemp values (tbemp_eid.nextval,'白洁','男',21,'河北省石家庄市');
insert into tbemp values (tbemp_eid.nextval,'李云','男',31,'湖南省长沙市');
insert into tbemp values (tbemp_eid.nextval,'杜甫','女',29,'湖北省武汉市');
insert into tbprod values (tbprod_pid.nextval,'笔记本','外星人','17寸');
insert into tbprod values (tbprod_pid.nextval,'笔记本','华硕','15寸');
insert into tbprod values (tbprod_pid.nextval,'笔记本','惠普','13寸');
insert into tbprod values (tbprod_pid.nextval,'笔记本','联想','17寸');
insert into tbprod values (tbprod_pid.nextval,'电视机','长虹','29寸');
insert into tbprod values (tbprod_pid.nextval,'电视机','TLC','55寸');
insert into tbprod values (tbprod_pid.nextval,'电视机','熊猫','32寸');
insert into tbprod values (tbprod_pid.nextval,'笔记本','紫光','15寸');
insert into tbsales values  (tbsales_srno.nextval,10001,1,6,60000,to_date('2019-03-14','yyyy-mm-dd'));
insert into tbsales values  (tbsales_srno.nextval,10002,2,7,70000,to_date('2019-04-07','yyyy-mm-dd'));
insert into tbsales values  (tbsales_srno.nextval,10003,2,10,120000,to_date('2019-03-28','yyyy-mm-dd'));
insert into tbsales values  (tbsales_srno.nextval,10004,3,4,50000,to_date('2019-04-21','yyyy-mm-dd'));
insert into tbsales values  (tbsales_srno.nextval,10005,4,8,780000,to_date('2019-05-01','yyyy-mm-dd'));
insert into tbsales values  (tbsales_srno.nextval,10006,5,12,40000,to_date('2019-03-10','yyyy-mm-dd'));
insert into tbsales values  (tbsales_srno.nextval,10005,4,4,440000,to_date('2019-03-01','yyyy-mm-dd'));
insert into tbsales values  (tbsales_srno.nextval,10000,1,22,320000,to_date('2019-04-07','yyyy-mm-dd'));
insert into tbsales values  (tbsales_srno.nextval,10004,5,8,240000,to_date('2019-05-24','yyyy-mm-dd'));
insert into tbsales values  (tbsales_srno.nextval,10000,1,4,5200,to_date('2019-03-11','yyyy-mm-dd'));
insert into tbsales values  (tbsales_srno.nextval,10003,7,10,200000,to_date('2019-06-26','yyyy-mm-dd'));
insert into tbsales values  (tbsales_srno.nextval,10005,8,6,60000,to_date('2019-03-28','yyyy-mm-dd'));
insert into tbsales values  (tbsales_srno.nextval,10004,8,9,90000,to_date('2019-03-11','yyyy-mm-dd'));
--1.查询三表的数据
select * from tbemp;
select * from tbprod;
select * from tbsales;
--2.查询男员工名单
select * from tbemp
where esex = '男';
---多表查询(不需要查询总销售量和总销售价值,只需显示商品的销售情况即可)
--1. 所有职员,所有商品的销售情况,包括:职员姓名,商品类别,商品品牌,商品规格,销售数量,销售价值,销售时间,按销售时间从高到低排列
--【法一】
/*select *
from tbemp,tbprod,tbsales
where tbsales.eid = tbemp.eid and tbsales.pid = tbprod.pid
order by sdate desc;*/
select *
from tbemp
full join tbsales
on tbsales.eid = tbemp.eid
--最终
select *
from (
     select *
     from tbemp
     full join tbsales
     on tbsales.eid = tbemp.eid
     ) a
full join tbprod
on a.pid = tbprod.pid;
--2.笔记本4月份的销售情况,包括:商品编号,商品品牌,商品规格,销售数量,销售价值,按销售数量从低到高排列
/*
select *
from tbsales
left join tbprod
on tbsales.pid = tbprod.pid
where tbprod.ptype = '笔记本' and extract(month from sdate) = 4;
*/
select *
from tbsales
left join tbprod
on tbprod.pid = tbsales.pid
--最终
select a.pid,a.pmark,a.pspec,a.pqty,a.pamount
from (
      select  tbprod.pid,tbprod.pmark,tbprod.pspec,tbsales.pqty,tbsales.pamount,tbsales.sdate
      from tbsales
      left join tbprod
      on tbprod.pid = tbsales.pid
      where tbprod.ptype = '笔记本'
     ) a
where extract(month from a.sdate) = 4
order by pqty asc;
--3. 李云3月份的销售情况,包括:商品编号,销售数量,销售价值
/*
select tbsales.pid,tbsales.pqty,tbsales.pamount
from tbemp
left join tbsales
on tbsales.eid = tbemp.eid
where tbemp.ename = '李云' and extract(month from sdate) = 3;
*/
select tbsales.eid
from tbsales
where extract(month from sdate) = 3
--最终
select ename,pid,pqty,pamount
from (
      select tbsales.eid as eid,pid,pqty,pamount
      from tbsales
      where extract(month from sdate) = 3
     ) a
left join tbemp
on tbemp.eid = a.eid
where tbemp.ename = '李云'
----4. 长虹29英寸艺术电视机的销售情况,包括:销售数量,销售价值,销售时间
/*select tbsales.pqty,tbsales.pamount,sdate
from tbsales
left join tbprod
on tbprod.pid = tbsales.pid
where tbprod.pmark = '长虹' and tbprod.pspec = '29寸';*/
select *
from tbprod
where tbprod.pmark = '长虹'
--最终
select pqty,pamount,sdate
from (
      select pid,pspec
      from tbprod
      where tbprod.pmark = '长虹'
     ) a
left join tbsales
on tbsales.pid = a.pid
where a.pspec = '29寸';
--5.长沙男职员4月销售情况
/*select *
from tbemp
left join tbsales
on tbsales.eid = tbemp.eid
where tbemp.esex = '男' and extract(month from sdate) = 4 and  tbemp.eaddr like ('%长沙%')
*/
select *
from tbemp
where tbemp.esex = '男'
--最终
select *
from (
      select eid
      from tbemp
      where tbemp.esex = '男'
     ) a
left join tbsales
on tbsales.eid = a.eid
--统计
--1.总共有多少个职员
select count(tbemp.eid)
from tbemp
--2.长虹电视被销售了多少次
select count(tbsales.srno)
from tbsales
left join tbprod
on tbprod.pid = tbsales.pid
where tbprod.pmark = '长虹';
--3. 年龄最小的女职员的年龄(年龄最小的女职员的姓名,年龄)
select tbemp.esex,min(tbemp.eage)
from tbemp
where tbemp.esex = '女'
group by tbemp.esex
--4. 笔记本单笔销售价值最高的单笔销售价值
select *
from tbprod
where tbprod.ptype = '笔记本'
--最终
select max(pamount)
from tbsales
left join tbprod
on tbsales.pid = tbprod.pid
where tbprod.ptype = '笔记本'
--5. 熊猫电视机的每次销售的平均销售价值(熊猫电视机的平均销售价格)
select tbsales.pamount/tbsales.pqty
from tbprod
left join tbsales
on tbsales.pid = tbprod.pid
where tbprod.pmark = '熊猫'
--6. 紫光笔记本3月份的销售总额
select sum(tbsales.pamount)
from tbprod
left join tbsales
on tbsales.pid = tbprod.pid
where tbprod.pmark = '紫光' and extract(month from tbsales.sdate) = 3
--分组与合计
--1. 按类型,品牌统计商品的销售总数量,销售总数量低于5的不统计
--按类型
select tbprod.ptype,sum(tbsales.pqty)
from tbsales
left join tbprod
on tbprod.pid = tbsales.pid
group by tbprod.ptype
having sum(tbsales.pqty)>=5
--按品牌
select tbprod.ptype,tbprod.pmark,sum(tbsales.pqty)
from tbsales
left join tbprod
on tbprod.pid = tbsales.pid
group by tbprod.pid,tbprod.ptype,tbprod.pmark
having sum(tbsales.pqty)>=5
--2. 按品牌,规格统计笔记本的平均销售价格,按销售价格从高到低的顺序显示
select tbprod.pmark,tbprod.pspec,sum(tbsales.pamount)/sum(tbsales.pqty)
from tbsales
left join tbprod
on tbprod.pid = tbsales.pid
group by tbprod.pmark,tbprod.pspec
order by sum(tbsales.pamount)/sum(tbsales.pqty) desc
--3.按姓名统计男职员3月份
/*select tbemp.eid,sum(tbsales.pamount)
from tbemp
left join tbsales
on tbsales.eid = tbemp.eid
group by tbemp.eid,tbemp.esex,sdate
having tbemp.esex = '男' and extract(month from sdate) = 3 and  sum(tbsales.pamount)>10000*/
--
select *
from tbemp
left join tbsales
on tbsales.eid = tbemp.eid
where tbemp.esex = '男' and extract(month from tbsales.sdate) = 3;
--最终
select a.ename,sum(a.pamount)
from (
      select *
      from tbemp
      left join tbsales
      on tbsales.eid = tbemp.eid
      where tbemp.esex = '男' and extract(month from tbsales.sdate) = 3
     ) a
group by a.ename
having sum(a.pamount) > 10000;
--4. 找出销售总额最高的职员的姓名
select tbsales.eid,sum(tbsales.pamount)
from tbsales
group by tbsales.eid
--
select max(a.sumpamount)
from (
      select tbsales.eid,sum(tbsales.pamount) as sumpamount
      from tbsales
      group by tbsales.eid
      ) a
--
select a.eid
from (
      select max(a.sumpamount) as maxsumpamount
      from (
            select tbsales.eid,sum(tbsales.pamount) as sumpamount
            from tbsales
            group by tbsales.eid
            ) a
      ) b
left join (
            select tbsales.eid,sum(tbsales.pamount) as sumpamount
            from tbsales
            group by tbsales.eid
            ) a
on a.sumpamount = b.maxsumpamount
--最终
select tbemp.ename,c.maxsumpamount
from (
      select a.eid as maxeid,b.maxsumpamount as maxsumpamount
      from (
            select max(a.sumpamount) as maxsumpamount
            from (
                  select tbsales.eid,sum(tbsales.pamount) as sumpamount
                  from tbsales
                  group by tbsales.eid
                  ) a
            ) b
      left join (
                  select tbsales.eid,sum(tbsales.pamount) as sumpamount
                  from tbsales
                  group by tbsales.eid
                  ) a
      on a.sumpamount = b.maxsumpamount
     ) c
left join tbemp
on tbemp.eid = c.maxeid
--5. 按姓名统计每个职员一共完成了多少笔销售业务
select tbsales.eid,count(tbsales.srno)
from tbsales
group by tbsales.eid
--最终
select tbemp.ename,a.c
from tbemp
right join
  (
  select tbsales.eid,count(tbsales.srno) as c
  from tbsales
  group by tbsales.eid
  ) a
on a.eid = tbemp.eid
--6. 找出完成销售业务笔数最多的职员的姓名和他完成的业务数
select b.eid
from (
      select max(count(tbsales.srno)) as msrno
      from tbsales
      group by tbsales.eid
     ) a
left join (
            select tbsales.eid,count(tbsales.srno) as csrno
            from tbsales
            group by tbsales.eid
           ) b
on b.csrno = a.msrno
--最终
select tbemp.ename
from tbemp
right join (
            select b.eid as eid
            from (
                  select max(count(tbsales.srno)) as msrno
                  from tbsales
                  group by tbsales.eid
                 ) a
            left join (
                        select tbsales.eid,count(tbsales.srno) as csrno
                        from tbsales
                        group by tbsales.eid
                       ) b
            on b.csrno = a.msrno
           ) c
on c.eid = tbemp.eid
--7. 按性别统计职员的最大年龄和最小年龄
select tbemp.esex,max(tbemp.eage),min(tbemp.eage)
from tbemp
where tbemp.esex = '男'
group by tbemp.esex
--
select tbemp.esex,max(tbemp.eage),min(tbemp.eage)
from tbemp
where tbemp.esex = '女'
group by tbemp.esex
--最终
select tbemp.esex,max(tbemp.eage),min(tbemp.eage)
from tbemp
where tbemp.esex = '男'
group by tbemp.esex
union
select tbemp.esex,max(tbemp.eage),min(tbemp.eage)
from tbemp
where tbemp.esex = '女'
group by tbemp.esex
CATALOG