bestivike 练习2 --Oracle数据库

A、基础安装和应用：
	一、在一台win7或win10下安装数据库oracle10或11服务器端，实例名称：orcl。(一定要检查之前是否安装过oracle，如安装过，不需再安装)
	
	二、安装plsql,并用system用户，连接刚安装好的oracle数据库。
	
	三、在system用户下，创建bestvike用户，赋予dba的权限，并应用命令赋予权限。
	
	四、在system用户下，创建表空间APP_DAT_TBS，路径和大小自定义，并手工应用命令创建。
	
	五、具体应用如下，其应用都是在bestvike用户下操作进行。


B、基础开发应用和技能：
	(一)、基础应用操作，DDL和DML测试题：
	
	练习1、使用命令create table，新建部门表和员工岗位表。
				部门表要求：表为tmp_dept ，主键为：部门号， 字段包括：deptno  部门号(字符型2位)   deptname  部门名称(字符型60位)
				其中部门号和部门名称的字典为：01(产品部),02(金融部),03(技术部),04(开发部),并分别应用insert into 命令写入部门表中。
	
				员工岗位表要求：表为tmp_leve ，主键为：岗位号， 字段包括：leveno  岗位号(字符型2位)   levename  岗位名称(字符型20位)
				其中岗位号和岗位名称字典：P1(实习/试用期),P2(助理工程师),P3(初级工程师),P4(工程师),P5(高级工程师),P6(资深工程师),并分别应用insert into 命令写入员工岗位表中。
	
	练习2、使用命令create table，新建员工表，要求：表为tmp_emp ，主键为：员工号， 字段包括：
			empno      员工号(字符3位)
			ename      员工名称(字符20位)
			age        年龄(数字2位)
			sex				 性别(字符4位)
			deptno     部门号
			leveno     岗位号
			flag       员工状态(字符1位)(字典0：在职  1：离职)
			
	练习3、使用命令create table，新建员工工资表，要求：表为tmp_sala ，唯一性主键为：月份+员工号， 字段包括：
			emp_month  月份(字符10位，格式：yyyy-mm-dd)	
			
在PL/SQL（即Oracle的过程式语言）中，创建表时定义emp_month字段，您需要使用VARCHAR2类型，并确保字符串的格式符合yyyy-mm-dd。在Oracle中，不推荐使用DATE类型来存储文本格式的日期，因为DATE类型是用于存储实际日期的，而不是字符串。
			empno      员工号(字符3位)
			base_pay   基本工资(数字(10,2)位)
			merit_pay  绩效工资(数字(10,2)位)			

				
	练习4、用Insert语句，向表tmp_emp写入15条记录，并提交；
	
	练习5、用Insert语句，向tmp_sala写入20条记录，并提交，其中2021-12-31和2022-03-31各10条，且与tmp_emp的前10条记录的员工信息一样；
	
	练习6、使用命令建立一个表（表名tmp_emp_test），表结构与tmp_emp相同且员工信息也完全一致。
	
	练习7、在表tmp_emp_test中，使用命令删除员工号为010的所有员工。
	
	练习8、查看tmp_emp与tmp_emp_test在表结构上，有何不一致的地方，并应用命令让两表在完整性约束上保持一致。
	
	练习9、将表tmp_sala员工号为003且月份为2021-12-31的员工基本工资加300，绩效工资加600。
	
  练习10、应用命令alter table，在表tmp_sala中，新增列“工资描述(pay_desc)”，字符型200位。
	
	练习11、使用命令，让员工表中的列"部门号"做为部门表中的列"部门号"的外键，让员工表中的列"级别号"做为员工级别表中的"列级别号"的外键。

	练习12、查询去年年底的在职员工信息，展示列为：员工号、员工名称、部门名称、岗位名称、基本工资、绩效工资，并按照员工号降序展示。	
	
	练习13、查询统计每个部门的在职员工的基本工资之和、绩效工资之和，展示列为：月份、部门名称、基本工资之和、绩效工资之和，并按月份升序、绩效工资之和降序展示。	
	
	练习14、查询统计第一季度末的每个部门的在职员工的各岗位的基本工资与绩效工资之和，展示列为：部门名称、岗位名称、基本工资与绩效工资之和，并按之和升序展示。
	
	练习15、查询统计第一季度末的每个部门的基本工资与绩效工资之和的最大值和最小值，展示列为：月份、部门名称、最大值、最小值。
	
	练习16、查询统计去年年底员工的基本工资与绩效工资之和大于5000的员工信息，展示列：员工号、员工名称、部门名称、岗位名称、基本工资与绩效工资之和。
	
	练习17、查询统计每个部门的在职员工岗位的基本工资和绩效工资，
	        展示列为：月份、部门名称、P1岗位基本工资、P1岗位绩效工资、P2岗位基本工资、P2岗位绩效工资、P3岗位基本工资、P3岗位绩效工资，并按月份和部门号升序展示。	
	        

做练习一最大的感触就是逗号加不加还有就是如果要分组那么说select里面的groupby里都得有，orderby里有的，select和grouppby里也都得有

-- 在system用户下，创建bestvike用户，赋予dba的权限，并应用命令赋予权限。
create user bestvike identified by "Liuzhengwei1234";
GRANT  DBA to bestvike;

create tablespace APP_DAT_TBS datafile 'D:\tablespace\practice2-liuzhengwei.dbf' 
size 256M
online;


-- 练习1
CREATE TABLE tmp_dept(
deptno char(2) primary key,
deptname varchar2(60)
)tablespace APP_DAT_TBS;
insert into tmp_dept(deptno,deptname) values ('01','产品部');
insert into tmp_dept(deptno,deptname) values ('02','金融部');
insert into tmp_dept(deptno,deptname) values ('03','技术部');
insert into tmp_dept(deptno,deptname) values ('04','开发部');

create table tmp_level(
leveno char(2) primary key,
levename varchar2(20)
)tablespace APP_DAT_TBS;

insert all
into tmp_level(leveno ,levename) values ('P1','实习/试用期')
into tmp_level(leveno ,levename) values ('P2','助理工程师')
into tmp_level(leveno ,levename) values ('P3','初级工程师')
into tmp_level(leveno ,levename) values ('P4','工程师')
into tmp_level(leveno ,levename) values ('P5','高级工程师')
into tmp_level(leveno ,levename) values ('P6','资深工程师')
select 1 from dual;

--练习2
create table tmp_emp(
empno char(3) primary key,
ename varchar2(20),
age number(2),
sex char(4),
deptno char(2) ,
leveno char(2),
flag char(1)
)tablespace APP_DAT_TBS;

--练习3
create table tmp_sale(
emp_month varchar2(10),
empno char(3),
base_pay number(10,2),
merit_pay number(10,2),
Unique (emp_month,empno)
)tablespace APP_DAT_TBS;


--练习4
insert all
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('001','刘政委',21,'女','01','P1','0')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('002','赵继伟',28,'男','02','P2','0')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('003','杨力维',28,'女','04','P3','0')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('004','魏晨',38,'男','03','P3','0')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('005','黄思静',28,'女','03','P1','0')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('006','周琦',28,'男','04','P2','0')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('007','郭艾伦',28,'男','02','P3','0')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('008','张镇麟',25,'男','01','P4','0')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('009','李虎翼',21,'男','03','P1','1')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('010','赵睿',28,'男','01','P2','0')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('011','金泫雅',30,'女','02','P4','0')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('012','蒲熠星',29,'男','04','P1','0')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('013','郭文韬',29,'男','01','P2','1')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('014','石凯',24,'男','02','P3','0')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('015','杨和苏',30,'男','03','P4','0')
select 1 from dual;
delete from tmp_emp
select *from tmp_emp;

--练习5
insert all
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2021-12-31','001',6000,1000)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2021-12-31','002',7000,1000)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2021-12-31','003',8000,1000)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2021-12-31','004',9000,1000)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2021-12-31','005',6600,1000)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2021-12-31','015',7700,1000)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2021-12-31','014',8800,1000)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2021-12-31','008',9900,1000)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2021-12-31','009',10000,1000)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2021-12-31','010',6000,10)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2022-03-31','001',6000,85)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2022-03-31','002',6000,700)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2022-03-31','003',6000,89)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2022-03-31','004',6000,852)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2022-03-31','005',6000,369)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2022-03-31','011',6000,41)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2022-03-31','012',6000,58)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2022-03-31','013',6000,425)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2022-03-31','014',6000,87)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2022-03-31','015',6000,254)
select 1 from dual;

select *from tmp_sale;
delete from tmp_sale



--练习6
create table tmp_emp_test as 
select *from tmp_emp;

--练习7

delete from tmp_emp_test where empno='010';
select *from tmp_emp_test;
delete from tmp_emp_test
drop table tmp_emp_test

-- 练习8
SELECT constraint_name, constraint_type
FROM all_constraints
WHERE table_name IN ('TMP_EMP', 'TMP_EMP_TEST')
AND constraint_name IN (
    SELECT constraint_name
    FROM all_constraints
    WHERE table_name = 'TMP_EMP'
)
AND constraint_name IN (
    SELECT constraint_name
    FROM all_constraints
    WHERE table_name = 'TMP_EMP_TEST'
);
--select *from all_constraints;


--练习9
update tmp_sale
set base_pay=base_pay+300,merit_pay=merit_pay+600
where empno='003' and emp_month='2021-12-31';
select * from tmp_sale;

 -- 练习10
 alter table tmp_sale  
 add pay_desc varchar2(200);
 
 --练习11
 alter table tmp_emp
 add constraint  departmentnumber
 foreign key (deptno)
 references tmp_dept(deptno);
 
 alter table tmp_emp
 add constraint  levelnumber
 foreign key (leveno)
 references tmp_level(leveno);
 
 --练习12
 select tmp_emp.empno,tmp_emp.ename,tmp_dept.deptname,tmp_level.levename,tmp_sale.base_pay,tmp_sale.merit_pay
 from tmp_emp
 join tmp_sale on tmp_sale.empno=tmp_emp.empno
 join tmp_dept on tmp_dept.deptno=tmp_emp.deptno
 join tmp_level on tmp_level.leveno=tmp_emp.leveno
 where flag='0' and emp_month='2021-12-31'
 order by empno desc;
 
 --练习13
 select tmp_sale.emp_month,tmp_dept.deptname,sum(tmp_sale.base_pay),sum(tmp_sale.merit_pay)
 from tmp_emp
 join tmp_dept on tmp_dept.deptno=tmp_emp.deptno
 join tmp_sale on tmp_sale.empno=tmp_emp.empno
 where  flag='0'
 group by tmp_dept.deptname,tmp_sale.emp_month
 order by emp_month asc , sum(tmp_sale.merit_pay) desc;
 


 --练习14
 select tmp_dept.deptname,tmp_level.levename,
 sum(tmp_sale.base_pay+tmp_sale.merit_pay) as sumsalary
 from tmp_emp
 join tmp_level on tmp_level.leveno=tmp_emp.leveno
 join tmp_dept on tmp_dept.deptno=tmp_emp.deptno
 join tmp_sale on tmp_sale.empno=tmp_emp.empno
 where  flag='0' and emp_month='2022-03-31'
 group by tmp_dept.deptname,tmp_level.levename--最后一个参数不能写逗号
 order by sumsalary asc;
 
 
 --练习15
 select tmp_sale.emp_month,tmp_dept.deptname,
 max(tmp_sale.base_pay+tmp_sale.merit_pay) as maxsalary,
 min(tmp_sale.base_pay+tmp_sale.merit_pay) as minsalary
 from tmp_emp
 join tmp_dept on tmp_emp.deptno=tmp_dept.deptno
 join tmp_sale on tmp_emp.empno=tmp_sale.empno
 where emp_month='2022-03-31'
 group by tmp_dept.deptname,tmp_sale.emp_month
 
 --练习16
 select tmp_emp.empno,tmp_emp.ename,tmp_dept.deptname,tmp_level.levename,
 (tmp_sale.base_pay+tmp_sale.merit_pay) as sumsalary
 from tmp_emp
 join tmp_level on tmp_level.leveno=tmp_emp.leveno
 join tmp_dept on tmp_dept.deptno=tmp_emp.deptno
 join tmp_sale on tmp_sale.empno=tmp_emp.empno
 where emp_month='2021-12-31' and (tmp_sale.base_pay+tmp_sale.merit_pay)>5000
 
 --练习17
select tmp_sale.emp_month,tmp_dept.deptno, tmp_dept.deptname,
sum(case when tmp_emp.leveno='P1' then tmp_sale.base_pay  else 0 end)as P1岗位基本工资,
sum(case when tmp_emp.leveno='P1' then tmp_sale.merit_pay else 0 end)as P1岗位绩效工资,
sum(case when tmp_emp.leveno='P2' then tmp_sale.base_pay  else 0 end)as P2岗位基本工资,
sum(case when tmp_emp.leveno='P2' then tmp_sale.merit_pay else 0 end)as P2岗位绩效工资,
sum(case when tmp_emp.leveno='P3' then tmp_sale.base_pay  else 0 end)as P3岗位基本工资,
sum(case when tmp_emp.leveno='P3' then tmp_sale.merit_pay else 0 end)as P3岗位绩效工资
FROM tmp_emp
join tmp_level on tmp_emp.leveno=tmp_level.leveno
join tmp_dept on tmp_emp.deptno=tmp_dept.deptno
join tmp_sale on tmp_emp.empno=tmp_sale.empno
where flag='0'
group by tmp_sale.emp_month,tmp_dept.deptno,tmp_dept.deptname
order by tmp_sale.emp_month ,tmp_dept.deptno 

(二)、中级应用操作，统计查询测试题：
应用plsql，将qdfh_branch.dmp、qdfh_data_manage2.dmp导入数据库，连接用户为bestvike。

练习1、统计查询表bestvike.qdfh_data_manage2中数据日期为2017-08-31号且支行名称为“青岛城阳支行营业部”的网点号、网点名称、资产金额、存款金额。

练习2、统计查询表bestvike.qdfh_data_manage2中数据日期为2017-08-31号和2017-06-30号且客户号为380300061055410的支行号、支行名称、网点号、网点名称、资产金额、存款金额。

练习3、统计查询表bestvike.qdfh_data_manage2中数据日期为2017年且网点名称为青岛南京路支行的资产金额、存款金额、理财金额、贵金属金额。

练习4、统计查询表bestvike.qdfh_data_manage2中数据日期为2017-08-31号且支行名称包含“山东路”的网点号、网点名称、资产金额、存款金额、资产金额占比(网点合计/网点所属支行的合计)。

练习5、统计查询数据日期是2017-08-31且资产金额大于200000的所有机构的支行号、支行名称、资产金额、存款金额、存款金额占比(支行合计/全辖合计)的信息，并按存款金额升序排列。

练习6、统计查询数据日期是2017-08-31且支行名称为即墨支行营业部的网点号、网点名称、资产金额、存款金额的信息，并按资产金额降序排列。

练习7、统计查询数据日期是2017-08-31且资产金额最大的客户号、支行号、支行名称、网点号、网点名称、资产金额、存款金额、资产金额占比(客户/支行合计)。

练习8、统计查询数据日期是2017-08-31的所有机构的支行号、支行名称、网点号、网点名称、存款金额、国债金额、存款金额与国债金额之和，且金额单位为万元并保留2位小数。

练习9、统计查询数据日期是2016-12-31的支行号、支行名称、资产金额、存款金额、三方存管金额、保险金额，并按照支行的“资产金额总额”做排名展示(从大到小)。

练习10、统计查询数据日期是2017-08-31的且支行名称包含“市南”的资产金额、存款金额、资产金额较上月、存款金额较上月、资产金额较年初、存款金额较年初。

-- 练习1

select * from qdfh_data_manage2;
select * from qdfh_branch

select m.phybrno  网点号,b.phybrname 网点名称,m.asset_bal 资产金额,m.dpsit_bal 存款金额
from qdfh_data_manage2 m
join  qdfh_branch b on m.phybrno=b.phybrno
where m.trandate='2017-08-31' and b.actbrname='青岛城阳支行营业部';

--练习2

select m1.actbrno 支行号,b1.actbrname 支行名称,m1.phybrno 网点号,b1.phybrname 网点名称,
       m1.asset_bal 资产金额,m1.dpsit_bal 存款金额
from qdfh_data_manage2 m1
join  qdfh_branch b1 on m1.phybrno=b1.phybrno
where m1.trandate='2017-08-31'  and m1.cuno=380300061055410
union all
select m2.actbrno,b2.actbrname,m2.phybrno,b2.phybrname,m2.asset_bal,m2.dpsit_bal
from qdfh_data_manage2 m2
join  qdfh_branch b2 on m2.phybrno=b2.phybrno
where m2.trandate='2017-06-30'  and m2.cuno=380300061055410;

--练习3
select sum(m.asset_bal) 支行资产金额,sum(m.dpsit_bal) 支行存款金额,sum(m.wcurr_bal) 支行理财金额,sum(m.guiji_bal) 支行贵金属金额
from qdfh_data_manage2 m
join qdfh_branch b on m.phybrno=b.phybrno
where b.phybrname='青岛南京路支行' and 
      m.trandate between '2017-01-01' and '2017-12-31';
      --to_char(trunc(to_date(m.trandate),'yyyy'),'yyyy')='2017'
      
-- 练习4
select m.phybrno 网点号,
       b.phybrname 网点名称,
       sum(m.asset_bal) 网点总资产金额,
       sum(m.dpsit_bal) 网点总存款金额,
       (sum(m.asset_bal)/actbr_asset_bal.numassetbal) as 网点资产金额占比
--sum(m.asset_bal)/NULLIF(sum(m.asset_bal) over( partition by m.actbrno) ,0)  as 网点资产金额占比
from qdfh_data_manage2 m,
     qdfh_branch b,
     (select m.actbrno,sum(m.asset_bal) as numassetbal
     from qdfh_data_manage2 m,qdfh_branch b
     where m.phybrno=b.phybrno
     and m.actbrno=b.actbrno
     and m.trandate='2017-08-31'
     group by  m.actbrno)  actbr_asset_bal
where  m.phybrno=b.phybrno
and    m.actbrno=b.actbrno
and    m.trandate='2017-08-31' 
and    b.actbrname like '%山东路%'
group by  m.phybrno,b.phybrname,actbr_asset_bal.numassetbal;

--练习5

select m.actbrno 支行号,
       b.actbrname 支行名称,
       sum(m.asset_bal) 支行总资产金额,
       sum(m.dpsit_bal) 支行总存款金额,
       (sum(m.dpsit_bal)/actbr_dpsit_bal.numdpsitbal) as 支行存款金额占比
from qdfh_data_manage2 m,
     qdfh_branch b,
     (select sum(m.dpsit_bal) as numdpsitbal
     from qdfh_data_manage2 m
     where m.trandate='2017-08-31' ) actbr_dpsit_bal
where m.actbrno=b.actbrno
and m.trandate=('2017-08-31')
having sum(m.asset_bal)>200000
group by m.actbrno, b.actbrname ,actbr_dpsit_bal.numdpsitbal
order by  sum(m.dpsit_bal);
--练习6
select m.phybrno,
       b.phybrname,
       sum(m.asset_bal) 网点总资产金额,
       sum(m.dpsit_bal) 网点总存款金额
from   qdfh_data_manage2 m,
       qdfh_branch b
where  m.trandate='2017-08-31'
and    b.actbrname='即墨支行营业部'
group by m.phybrno,b.phybrname
order by  sum(m.asset_bal) desc;

--练习7

select m.cuno,
       m.actbrno,
       b.actbrname,
       m.phybrno,
       b.phybrname,
       m.asset_bal,
       m.dpsit_bal,
       m.asset_bal/(select sum(m1.asset_bal) 
                    from qdfh_data_manage2 m1
                    where m1.actbrno=m.actbrno
                    and   m1.trandate='2017-08-31') as 客户资产金额占比
from qdfh_data_manage2 m,
     qdfh_branch b
where m.phybrno=b.phybrno
and   m.actbrno=b.actbrno
and   m.trandate='2017-08-31'
and   m.asset_bal=(select max(m.asset_bal) from qdfh_data_manage2 m where m.trandate='2017-08-31')
group by m.cuno,m.actbrno,b.actbrname,m.phybrno,b.phybrname,m.dpsit_bal,m.asset_bal
--having   max(m.asset_bal)

--练习8 
select m.phybrno,b.phybrname,m.actbrno,b.actbrname,
round(sum(m.dpsit_bal)/10000,2),
round(sum(m.tbond_bal)/10000,2),
round(sum(m.dpsit_bal+m.tbond_bal)/10000,2) 存款金额与国债金额之和 --新的命名里面不能包含括号
from qdfh_data_manage2 m,
     qdfh_branch b
where m.phybrno=b.phybrno
and   m.actbrno=b.actbrno
and   m.trandate='2017-08-31'
group by m.phybrno,b.phybrname,m.actbrno,b.actbrname


--练习9
select 
rank() over (order by sum(m.asset_bal) desc) as rank,
       m.actbrno,b.actbrname,
       sum(m.asset_bal) 支行资产金额,
       sum(m.dpsit_bal) 支行存款金额,
       sum(m.presv_bal) 支行三方存款金额,
       sum(m.insure_bal) 支行保险金额
from  qdfh_data_manage2 m,
      qdfh_branch b
where m.actbrno=b.actbrno
and   m.trandate='2016-12-31'
group by m.actbrno,b.actbrname


--练习10
--按照支行姓名分组了可是还有重复的
select b.actbrname,
       sum(m.asset_bal) 资产金额,
       sum(m.dpsit_bal) 存款金额,
       --coalesce(lag(sum(m.asset_bal) over (partition by b.actbrname order by m.trandate)),0)-sum(m.asset_bal) 资产金额较上月,
       --(select s1 from ordertable where m.trandate='2017-07-31')-sum(m.asset_bal) 资产金额较上月
       ordertable1.s1-sum(m.asset_bal) 资产金额较上月,
       ordertable1.s2-sum(m.dpsit_bal) 存款金额较上月,
       ordertable2.s1-sum(m.asset_bal) 资产金额较年初,
       ordertable2.s2-sum(m.dpsit_bal) 存款金额较年初      

from   (select b.actbrname,sum(m.asset_bal) s1, sum(m.dpsit_bal) s2
        from qdfh_data_manage2 m,qdfh_branch b
        where m.actbrno=b.actbrno
        and m.trandate like '%2017-06%'--没有七月的值
        group by b.actbrname
        )  ordertable1,
        (select b.actbrname,sum(m.asset_bal) s1, sum(m.dpsit_bal) s2
        from qdfh_data_manage2 m,qdfh_branch b
        where m.actbrno=b.actbrno
        and m.trandate like '%2017-04%'--没有1月的值,也没有2、3月的值
        group by b.actbrname
        )  ordertable2,
        qdfh_data_manage2 m,
        qdfh_branch b
where  m.actbrno=b.actbrno
and    m.trandate='2017-08-31'
and    b.actbrname like '%市南%'
group by b.actbrname,ordertable1.s1,ordertable1.s2,ordertable2.s1,ordertable2.s2

你提到的两个SQL查询语句都是用来连接两个表table1和table2，并根据id字段进行匹配。这两个查询在功能上是等价的，它们都会返回table1和table2中id相等的记录。但是，它们的语法和适用场景有所不同。

1. 使用JOIN语法：

SELECT table1.column1, table2.column2
FROM table1
JOIN table2 ON table1.id = table2.id;

这种语法是专门用来进行表连接的，它清晰地表达了两个表之间的关系。JOIN关键字后面跟着ON子句，用于指定连接条件。这种语法更加直观，尤其是在处理多个表连接时。
2. 使用WHERE语法进行表连接：

SELECT table1.column1, table2.column2
FROM table1, table2
WHERE table1.id = table2.id;

这种语法实际上是使用WHERE子句来模拟表连接。在这里，WHERE子句用来过滤那些id相等的记录。这种方法在处理两个表的简单连接时是可以的，但是当连接的表增多时，使用JOIN关键字会更加清晰和易于管理。
总结一下区别：

• 使用JOIN语法是专门用来进行表连接的，它提供了更清晰的语法和更易于管理的多表操作。

• 使用WHERE语法进行表连接是一种较为原始的方法，它通过WHERE子句来实现相同的功能，但在处理多个表连接时可能不够直观和易于维护。
  在实际应用中，建议优先使用JOIN语法来进行表连接，因为它更加标准和规范。

  (三)、高级应用操作，测试题：
  练习1、编写一个PL/SQL，应用FOR游标，把员工表中级别号为P5的部门名称、员工号、员工名称、年龄、性别、绩效工资输出。

  练习2、建立一个自定义函数，实现输入“任一参数日期(yyyy-mm-dd)”，输出一组日期字符串(自“任一参数日期”的去年底日期和“任一参数日期”的自年初每月底日期，且日期格式为：yyyy-mm-dd)，
  如输入：‘2020-05-20’，得到一串日期：‘2019-12-31’,‘2020-01-31’,‘2020-02-29’,‘2020-03-31’,‘2020-04-30’,‘2020-05-20’ 。

  练习3、建立一个存储过程，在员工表中，利用游标实现：若部门号为01，则基本工资加100；部门号为02，基本工资加200；部门号为03，绩效工资加300；部门号为04，绩效工资加400；
  其他部门，基本工资和绩效工资都加400。

--(三)、高级应用操作，测试题：
--练习1
declare
  cursor emp_cursor is
    select D.DEPTNAME, E.EMPNO, E.ENAME, E.AGE, E.SEX, S.MERIT_PAY
    from tmp_emp E
    left join tmp_dept D on E.DEPTNO = D.DEPTNO
    left join tmp_level L on E.LEVENO = L.LEVENO 
    left join tmp_sale S on E.EMPNO = S.EMPNO
    where L.LEVENO = 'P5';
  
  v_DEPTNAME     varchar2(60);
  v_EMPNO            char(3);
  v_ENAME        varchar2(20);
  v_AGE            number(2);
  v_SEX              char(4);
  v_MERIT_PAY      number(10,2);
  
begin
  for rec in emp_cursor loop
      v_DEPTNAME := rec.DEPTNAME;
      v_EMPNO := rec.EMPNO;
      v_ENAME := rec.ENAME;
      v_AGE := rec.AGE;
      v_SEX := rec.SEX;
      v_MERIT_PAY := rec.MERIT_PAY;
      
      dbms_output.put_line(
        '部门名称：' || v_DEPTNAME ||
        '，员工号：' || v_EMPNO ||
        '，员工名称：' || v_ENAME ||
        '，年龄：' || v_AGE ||
        '，性别：' || v_SEX ||
        '，绩效工资：' || v_MERIT_PAY);
  end loop;
end;

--练习2
CREATE OR REPLACE FUNCTION generate_date(input_date IN DATE)
RETURN CLOB AS
  result_clob CLOB;
  CURSOR date_cursor IS
    SELECT TO_CHAR(TRUNC(input_date, 'Y') - 1, 'YYYY-MM-DD') AS end_date
    FROM dual
    UNION ALL
    SELECT end_date
    FROM (
    SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(input_date, -LEVEL)), 'YYYY-MM-DD') AS end_date
    FROM dual
    CONNECT BY LEVEL <= MONTHS_BETWEEN(TRUNC(input_date,'MM'),TRUNC(input_date,'Y'))
    ) 
    UNION ALL
    SELECT TO_CHAR(input_date, 'YYYY-MM-DD') as end_date FROM dual
    ORDER BY end_date;
BEGIN
  DBMS_LOB.CREATETEMPORARY(result_clob, TRUE);
  FOR rec IN date_cursor LOOP
    DBMS_LOB.APPEND(result_clob, rec.end_date || CHR(10));
  END LOOP;
  RETURN result_clob;
END generate_date;




SELECT generate_date(to_date('2021-5-20', 'YYYY-MM-DD')) FROM dual;

--练习3

CREATE OR REPLACE PROCEDURE update_emp_sale IS
  CURSOR emp_cursor IS
    SELECT E.EMPNO, E.DEPTNO, S.BASE_PAY, S.MERIT_PAY
    FROM tmp_emp E
    LEFT JOIN tmp_sale S ON E.EMPNO = S.EMPNO;

BEGIN
  FOR rec IN emp_cursor LOOP
    CASE rec.DEPTNO
      WHEN '01' THEN
        UPDATE tmp_sale
        SET BASE_PAY = BASE_PAY + 100
        WHERE EMPNO = rec.EMPNO;
      WHEN '02' THEN
        UPDATE tmp_sale
        SET BASE_PAY = BASE_PAY + 200
        WHERE EMPNO = rec.EMPNO;
      WHEN '03' THEN
        UPDATE tmp_sale
        SET MERIT_PAY = MERIT_PAY + 300
        WHERE EMPNO = rec.EMPNO;
      WHEN '04' THEN
        UPDATE tmp_sale
        SET MERIT_PAY = MERIT_PAY + 400
        WHERE EMPNO = rec.EMPNO;
      ELSE
        UPDATE tmp_sale
        SET BASE_PAY = BASE_PAY + 400, MERIT_PAY = MERIT_PAY + 400
        WHERE EMPNO = rec.EMPNO;
    END CASE;
  END LOOP;
 
END update_emp_sale;

 --调用   
BEGIN
    update_emp_sale;
END;
      
 --查看
select  E.EMPNO,E.DEPTNO,S.BASE_PAY, S.MERIT_PAY
from tmp_emp E
left join tmp_sale S on E.EMPNO = S.EMPNO

select * from tmp_sale

汇总：

-- 在system用户下，创建bestvike用户，赋予dba的权限，并应用命令赋予权限。
create user bestvike identified by "Liuzhengwei1234";
GRANT  DBA to bestvike;

create tablespace APP_DAT_TBS datafile 'D:\tablespace\practice2-liuzhengwei.dbf' 
size 256M
online;


--(一)、基础应用操作，DDL和DML测试题：
-- 练习1
CREATE TABLE tmp_dept(
deptno char(2) primary key,
deptname varchar2(60)
)tablespace APP_DAT_TBS;
insert into tmp_dept(deptno,deptname) values ('01','产品部');
insert into tmp_dept(deptno,deptname) values ('02','金融部');
insert into tmp_dept(deptno,deptname) values ('03','技术部');
insert into tmp_dept(deptno,deptname) values ('04','开发部');

create table tmp_level(
leveno char(2) primary key,
levename varchar2(20)
)tablespace APP_DAT_TBS;

insert all
into tmp_level(leveno ,levename) values ('P1','实习/试用期')
into tmp_level(leveno ,levename) values ('P2','助理工程师')
into tmp_level(leveno ,levename) values ('P3','初级工程师')
into tmp_level(leveno ,levename) values ('P4','工程师')
into tmp_level(leveno ,levename) values ('P5','高级工程师')
into tmp_level(leveno ,levename) values ('P6','资深工程师')
select 1 from dual;

--练习2
create table tmp_emp(
empno char(3) primary key,
ename varchar2(20),
age number(2),
sex char(4),
deptno char(2) ,
leveno char(2),
flag char(1)
)tablespace APP_DAT_TBS;

--练习3
create table tmp_sale(
emp_month varchar2(10),
empno char(3),
base_pay number(10,2),
merit_pay number(10,2),
Unique (emp_month,empno)
)tablespace APP_DAT_TBS;


--练习4
insert all
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('001','刘政委',21,'女','01','P5','0')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('002','赵继伟',28,'男','02','P5','0')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('003','杨力维',28,'女','04','P5','0')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('004','魏晨',38,'男','03','P5','0')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('005','黄思静',28,'女','03','P1','0')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('006','周琦',28,'男','04','P2','0')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('007','郭艾伦',28,'男','02','P3','0')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('008','张镇麟',25,'男','01','P4','0')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('009','李虎翼',21,'男','03','P1','1')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('010','赵睿',28,'男','01','P2','0')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('011','金泫雅',30,'女','02','P6','0')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('012','蒲熠星',29,'男','04','P1','0')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('013','郭文韬',29,'男','01','P2','1')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('014','石凯',24,'男','02','P3','0')
into tmp_emp(empno,ename,age,sex,deptno,leveno,flag) values ('015','杨和苏',30,'男','03','P6','0')
select 1 from dual;
--delete from tmp_emp
select *from tmp_emp;

--练习5
insert all
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2021-12-31','001',6000,1000)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2021-12-31','002',7000,1000)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2021-12-31','003',8000,1000)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2021-12-31','004',9000,1000)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2021-12-31','005',6600,1000)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2021-12-31','015',7700,1000)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2021-12-31','014',8800,1000)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2021-12-31','008',9900,1000)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2021-12-31','009',10000,1000)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2021-12-31','010',6000,10)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2022-03-31','001',6000,85)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2022-03-31','002',6000,700)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2022-03-31','003',6000,89)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2022-03-31','004',6000,852)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2022-03-31','005',6000,369)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2022-03-31','011',6000,41)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2022-03-31','012',6000,58)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2022-03-31','013',6000,425)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2022-03-31','014',6000,87)
into tmp_sale(emp_month,empno,base_pay,merit_pay) values ('2022-03-31','015',6000,254)
select 1 from dual;

select *from tmp_sale;
--delete from tmp_sale



--练习6
create table tmp_emp_test as 
select *from tmp_emp;

--练习7

delete from tmp_emp_test where empno='010';
select *from tmp_emp_test;
--delete from tmp_emp_test
--drop table tmp_emp_test

-- 练习8
SELECT constraint_name, constraint_type
FROM all_constraints
WHERE table_name IN ('TMP_EMP', 'TMP_EMP_TEST')
AND constraint_name IN (
    SELECT constraint_name
    FROM all_constraints
    WHERE table_name = 'TMP_EMP'
)
AND constraint_name IN (
    SELECT constraint_name
    FROM all_constraints
    WHERE table_name = 'TMP_EMP_TEST'
);
--可以看到结果中为空，即没有不一样的
--select *from all_constraints;


--练习9
update tmp_sale
set base_pay=base_pay+300,merit_pay=merit_pay+600
where empno='003' and emp_month='2021-12-31';
select * from tmp_sale;

 -- 练习10
 alter table tmp_sale  
 add pay_desc varchar2(200);
 
 --练习11
 alter table tmp_emp
 add constraint  departmentnumber
 foreign key (deptno)
 references tmp_dept(deptno);
 
 alter table tmp_emp
 add constraint  levelnumber
 foreign key (leveno)
 references tmp_level(leveno);
 
 --练习12
 select tmp_emp.empno,tmp_emp.ename,tmp_dept.deptname,tmp_level.levename,tmp_sale.base_pay,tmp_sale.merit_pay
 from tmp_emp
 join tmp_sale on tmp_sale.empno=tmp_emp.empno
 join tmp_dept on tmp_dept.deptno=tmp_emp.deptno
 join tmp_level on tmp_level.leveno=tmp_emp.leveno
 where flag='0' and emp_month='2021-12-31'
 order by empno desc;
 
 --练习13
 select tmp_sale.emp_month,tmp_dept.deptname,sum(tmp_sale.base_pay),sum(tmp_sale.merit_pay)
 from tmp_emp
 join tmp_dept on tmp_dept.deptno=tmp_emp.deptno
 join tmp_sale on tmp_sale.empno=tmp_emp.empno
 where  flag='0'
 group by tmp_dept.deptname,tmp_sale.emp_month
 order by emp_month asc , sum(tmp_sale.merit_pay) desc;
 


 --练习14
 select tmp_dept.deptname,tmp_level.levename,
 sum(tmp_sale.base_pay+tmp_sale.merit_pay) as sumsalary
 from tmp_emp
 join tmp_level on tmp_level.leveno=tmp_emp.leveno
 join tmp_dept on tmp_dept.deptno=tmp_emp.deptno
 join tmp_sale on tmp_sale.empno=tmp_emp.empno
 where  flag='0' and emp_month='2022-03-31'
 group by tmp_dept.deptname,tmp_level.levename--最后一个参数不能写逗号
 order by sumsalary asc;
 
 
 --练习15
 select tmp_sale.emp_month,tmp_dept.deptname,
 max(tmp_sale.base_pay+tmp_sale.merit_pay) as maxsalary,
 min(tmp_sale.base_pay+tmp_sale.merit_pay) as minsalary
 from tmp_emp
 join tmp_dept on tmp_emp.deptno=tmp_dept.deptno
 join tmp_sale on tmp_emp.empno=tmp_sale.empno
 where emp_month='2022-03-31'
 group by tmp_dept.deptname,tmp_sale.emp_month;
 
 --练习16
 select tmp_emp.empno,tmp_emp.ename,tmp_dept.deptname,tmp_level.levename,
 (tmp_sale.base_pay+tmp_sale.merit_pay) as sumsalary
 from tmp_emp
 join tmp_level on tmp_level.leveno=tmp_emp.leveno
 join tmp_dept on tmp_dept.deptno=tmp_emp.deptno
 join tmp_sale on tmp_sale.empno=tmp_emp.empno
 where emp_month='2021-12-31' and (tmp_sale.base_pay+tmp_sale.merit_pay)>5000;
 
 --练习17
select tmp_sale.emp_month,tmp_dept.deptno, tmp_dept.deptname,
sum(case when tmp_emp.leveno='P1' then tmp_sale.base_pay  else 0 end)as P1岗位基本工资,
sum(case when tmp_emp.leveno='P1' then tmp_sale.merit_pay else 0 end)as P1岗位绩效工资,
sum(case when tmp_emp.leveno='P2' then tmp_sale.base_pay  else 0 end)as P2岗位基本工资,
sum(case when tmp_emp.leveno='P2' then tmp_sale.merit_pay else 0 end)as P2岗位绩效工资,
sum(case when tmp_emp.leveno='P3' then tmp_sale.base_pay  else 0 end)as P3岗位基本工资,
sum(case when tmp_emp.leveno='P3' then tmp_sale.merit_pay else 0 end)as P3岗位绩效工资
FROM tmp_emp
join tmp_level on tmp_emp.leveno=tmp_level.leveno
join tmp_dept on tmp_emp.deptno=tmp_dept.deptno
join tmp_sale on tmp_emp.empno=tmp_sale.empno
where flag='0'
group by tmp_sale.emp_month,tmp_dept.deptno,tmp_dept.deptname
order by tmp_sale.emp_month ,tmp_dept.deptno ;
 

--(二)、中级应用操作，统计查询测试题：

-- 练习1

select * from qdfh_data_manage2;
select * from qdfh_branch

select m.phybrno  网点号,b.phybrname 网点名称,m.asset_bal 资产金额,m.dpsit_bal 存款金额
from qdfh_data_manage2 m
join  qdfh_branch b on m.phybrno=b.phybrno
where m.trandate='2017-08-31' and b.actbrname='青岛城阳支行营业部';

--练习2

select m1.actbrno 支行号,b1.actbrname 支行名称,m1.phybrno 网点号,b1.phybrname 网点名称,
       m1.asset_bal 资产金额,m1.dpsit_bal 存款金额
from qdfh_data_manage2 m1
join  qdfh_branch b1 on m1.phybrno=b1.phybrno
where m1.trandate='2017-08-31'  and m1.cuno=380300061055410
union all
select m2.actbrno,b2.actbrname,m2.phybrno,b2.phybrname,m2.asset_bal,m2.dpsit_bal
from qdfh_data_manage2 m2
join  qdfh_branch b2 on m2.phybrno=b2.phybrno
where m2.trandate='2017-06-30'  and m2.cuno=380300061055410;

--练习3
select sum(m.asset_bal) 支行资产金额,sum(m.dpsit_bal) 支行存款金额,sum(m.wcurr_bal) 支行理财金额,sum(m.guiji_bal) 支行贵金属金额
from qdfh_data_manage2 m
join qdfh_branch b on m.phybrno=b.phybrno
where b.phybrname='青岛南京路支行' and 
      m.trandate between '2017-01-01' and '2017-12-31';
      --to_char(trunc(to_date(m.trandate),'yyyy'),'yyyy')='2017'
      
-- 练习4
select m.phybrno 网点号,
       b.phybrname 网点名称,
       sum(m.asset_bal) 网点总资产金额,
       sum(m.dpsit_bal) 网点总存款金额,
       (sum(m.asset_bal)/actbr_asset_bal.numassetbal) as 网点资产金额占比
--sum(m.asset_bal)/NULLIF(sum(m.asset_bal) over( partition by m.actbrno) ,0)  as 网点资产金额占比
from qdfh_data_manage2 m,
     qdfh_branch b,
     (select m.actbrno,sum(m.asset_bal) as numassetbal
     from qdfh_data_manage2 m,qdfh_branch b
     where m.phybrno=b.phybrno
     and m.actbrno=b.actbrno
     and m.trandate='2017-08-31'
     group by  m.actbrno)  actbr_asset_bal
where  m.phybrno=b.phybrno
and    m.actbrno=b.actbrno
and    m.trandate='2017-08-31' 
and    b.actbrname like '%山东路%'
group by  m.phybrno,b.phybrname,actbr_asset_bal.numassetbal;

--练习5

select m.actbrno 支行号,
       b.actbrname 支行名称,
       sum(m.asset_bal) 支行总资产金额,
       sum(m.dpsit_bal) 支行总存款金额,
       (sum(m.dpsit_bal)/actbr_dpsit_bal.numdpsitbal) as 支行存款金额占比
from qdfh_data_manage2 m,
     qdfh_branch b,
     (select sum(m.dpsit_bal) as numdpsitbal
     from qdfh_data_manage2 m
     where m.trandate='2017-08-31' ) actbr_dpsit_bal
where m.actbrno=b.actbrno
and m.trandate=('2017-08-31')
having sum(m.asset_bal)>200000
group by m.actbrno, b.actbrname ,actbr_dpsit_bal.numdpsitbal
order by  sum(m.dpsit_bal);
--练习6
select m.phybrno,
       b.phybrname,
       sum(m.asset_bal) 网点总资产金额,
       sum(m.dpsit_bal) 网点总存款金额
from   qdfh_data_manage2 m,
       qdfh_branch b
where  m.trandate='2017-08-31'
and    b.actbrname='即墨支行营业部'
group by m.phybrno,b.phybrname
order by  sum(m.asset_bal) desc;

--练习7

select m.cuno,
       m.actbrno,
       b.actbrname,
       m.phybrno,
       b.phybrname,
       m.asset_bal,
       m.dpsit_bal,
       m.asset_bal/(select sum(m1.asset_bal) 
                    from qdfh_data_manage2 m1
                    where m1.actbrno=m.actbrno
                    and   m1.trandate='2017-08-31') as 客户资产金额占比
from qdfh_data_manage2 m,
     qdfh_branch b
where m.phybrno=b.phybrno
and   m.actbrno=b.actbrno
and   m.trandate='2017-08-31'
and   m.asset_bal=(select max(m.asset_bal) from qdfh_data_manage2 m where m.trandate='2017-08-31')
group by m.cuno,m.actbrno,b.actbrname,m.phybrno,b.phybrname,m.dpsit_bal,m.asset_bal
--having   max(m.asset_bal)

--练习8 
select m.phybrno,b.phybrname,m.actbrno,b.actbrname,
round(sum(m.dpsit_bal)/10000,2),
round(sum(m.tbond_bal)/10000,2),
round(sum(m.dpsit_bal+m.tbond_bal)/10000,2) 存款金额与国债金额之和 --新的命名里面不能包含括号
from qdfh_data_manage2 m,
     qdfh_branch b
where m.phybrno=b.phybrno
and   m.actbrno=b.actbrno
and   m.trandate='2017-08-31'
group by m.phybrno,b.phybrname,m.actbrno,b.actbrname


--练习9
select 
rank() over (order by sum(m.asset_bal) desc) as rank,
       m.actbrno,b.actbrname,
       sum(m.asset_bal) 支行资产金额,
       sum(m.dpsit_bal) 支行存款金额,
       sum(m.presv_bal) 支行三方存款金额,
       sum(m.insure_bal) 支行保险金额
from  qdfh_data_manage2 m,
      qdfh_branch b
where m.actbrno=b.actbrno
and   m.trandate='2016-12-31'
group by m.actbrno,b.actbrname


--练习10
--按照支行姓名分组了可是还有重复的
select b.actbrname,
       sum(m.asset_bal) 资产金额,
       sum(m.dpsit_bal) 存款金额,
       --coalesce(lag(sum(m.asset_bal) over (partition by b.actbrname order by m.trandate)),0)-sum(m.asset_bal) 资产金额较上月,
       --(select s1 from ordertable where m.trandate='2017-07-31')-sum(m.asset_bal) 资产金额较上月
       ordertable1.s1-sum(m.asset_bal) 资产金额较上月,
       ordertable1.s2-sum(m.dpsit_bal) 存款金额较上月,
       ordertable2.s1-sum(m.asset_bal) 资产金额较年初,
       ordertable2.s2-sum(m.dpsit_bal) 存款金额较年初      

from   (select b.actbrname,sum(m.asset_bal) s1, sum(m.dpsit_bal) s2
        from qdfh_data_manage2 m,qdfh_branch b
        where m.actbrno=b.actbrno
        and m.trandate like '%2017-06%'--没有七月的值
        group by b.actbrname
        )  ordertable1,
        (select b.actbrname,sum(m.asset_bal) s1, sum(m.dpsit_bal) s2
        from qdfh_data_manage2 m,qdfh_branch b
        where m.actbrno=b.actbrno
        and m.trandate like '%2017-04%'--没有1月的值,也没有2、3月的值
        group by b.actbrname
        )  ordertable2,
        qdfh_data_manage2 m,
        qdfh_branch b
where  m.actbrno=b.actbrno
and    m.trandate='2017-08-31'
and    b.actbrname like '%市南%'
group by b.actbrname,ordertable1.s1,ordertable1.s2,ordertable2.s1,ordertable2.s2


--(三)、高级应用操作，测试题：
--练习1
declare
  cursor emp_cursor is
    select D.DEPTNAME, E.EMPNO, E.ENAME, E.AGE, E.SEX, S.MERIT_PAY
    from tmp_emp E
    left join tmp_dept D on E.DEPTNO = D.DEPTNO
    left join tmp_level L on E.LEVENO = L.LEVENO 
    left join tmp_sale S on E.EMPNO = S.EMPNO
    where L.LEVENO = 'P5';
  
  v_DEPTNAME     varchar2(60);
  v_EMPNO            char(3);
  v_ENAME        varchar2(20);
  v_AGE            number(2);
  v_SEX              char(4);
  v_MERIT_PAY      number(10,2);
  
begin
  for rec in emp_cursor loop
      v_DEPTNAME := rec.DEPTNAME;
      v_EMPNO := rec.EMPNO;
      v_ENAME := rec.ENAME;
      v_AGE := rec.AGE;
      v_SEX := rec.SEX;
      v_MERIT_PAY := rec.MERIT_PAY;
      
      dbms_output.put_line(
        '部门名称：' || v_DEPTNAME ||
        '，员工号：' || v_EMPNO ||
        '，员工名称：' || v_ENAME ||
        '，年龄：' || v_AGE ||
        '，性别：' || v_SEX ||
        '，绩效工资：' || v_MERIT_PAY);
  end loop;
end;

--练习2
CREATE OR REPLACE FUNCTION generate_date(input_date IN DATE)
RETURN CLOB AS
  result_clob CLOB;
  CURSOR date_cursor IS
    SELECT TO_CHAR(TRUNC(input_date, 'Y') - 1, 'YYYY-MM-DD') AS end_date
    FROM dual
    UNION ALL
    SELECT end_date
    FROM (
    SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(input_date, -LEVEL)), 'YYYY-MM-DD') AS end_date
    FROM dual
    CONNECT BY LEVEL <= MONTHS_BETWEEN(TRUNC(input_date,'MM'),TRUNC(input_date,'Y'))
    ) 
    UNION ALL
    SELECT TO_CHAR(input_date, 'YYYY-MM-DD') as end_date FROM dual
    ORDER BY end_date;
BEGIN
  DBMS_LOB.CREATETEMPORARY(result_clob, TRUE);
  FOR rec IN date_cursor LOOP
    DBMS_LOB.APPEND(result_clob, rec.end_date || CHR(10));
  END LOOP;
  RETURN result_clob;
END generate_date;




SELECT generate_date(to_date('2021-5-20', 'YYYY-MM-DD')) FROM dual;

--练习3

CREATE OR REPLACE PROCEDURE update_emp_sale IS
  CURSOR emp_cursor IS
    SELECT E.EMPNO, E.DEPTNO, S.BASE_PAY, S.MERIT_PAY
    FROM tmp_emp E
    LEFT JOIN tmp_sale S ON E.EMPNO = S.EMPNO;

BEGIN
  FOR rec IN emp_cursor LOOP
    CASE rec.DEPTNO
      WHEN '01' THEN
        UPDATE tmp_sale
        SET BASE_PAY = BASE_PAY + 100
        WHERE EMPNO = rec.EMPNO;
      WHEN '02' THEN
        UPDATE tmp_sale
        SET BASE_PAY = BASE_PAY + 200
        WHERE EMPNO = rec.EMPNO;
      WHEN '03' THEN
        UPDATE tmp_sale
        SET MERIT_PAY = MERIT_PAY + 300
        WHERE EMPNO = rec.EMPNO;
      WHEN '04' THEN
        UPDATE tmp_sale
        SET MERIT_PAY = MERIT_PAY + 400
        WHERE EMPNO = rec.EMPNO;
      ELSE
        UPDATE tmp_sale
        SET BASE_PAY = BASE_PAY + 400, MERIT_PAY = MERIT_PAY + 400
        WHERE EMPNO = rec.EMPNO;
    END CASE;
  END LOOP;
 
END update_emp_sale;

 --调用   
BEGIN
    update_emp_sale;
END;
      
 --查看
select  E.EMPNO,E.DEPTNO,S.BASE_PAY, S.MERIT_PAY
from tmp_emp E
left join tmp_sale S on E.EMPNO = S.EMPNO

select * from tmp_sale