爱站程序员基地综合练习题1-1
- 写一个sql，从这个dw.sq_grbm中取值，输出格式：grbm。
要求取值结果以当前年月日开头+sequence。如：20140412-100001
select to_char(sysdate,\’yyyymmdd\’)||\’-\’||dw.sq_grbm.nextval grbm
from dual
- 查询姓张的病人姓名和出生年份。输出格式：xm,csnf
select a.xm, to_char(a.csrq, \’yyyy\’) csnf
from dw.per_natl a
where a.xm like \’张%\’
- 查询药品的平均单价，输出格式：pjdj
select trunc(avg(a.dj),2) pjdj
from dw.medi_item a
where a.ylxmlb = \’1\’
- 查询发生过费用的病人总人数，输出格式：zrs
select count(distinct(grbm)) zrs
from dw.patient_script_genl
- 查询2000年以后（不包含2000年出生的）出生的病人姓名。输出格式：xm
select xm
from dw.per_natl
where csrq>to_date(\’20001231\’,\’yyyymmdd\’);
- 查询没有被使用过的医疗项目编码和名称。输出格式：ylxmbm,ylxmmc
select a.ylxmbm, a.ylxmmc
from dw.medi_item a
where not exists (select b.ylxmbm
from dw.patient_script_detl b
where a.ylxmbm = b.ylxmbm)
- 查询与自己的科室负责人同名的医师的编码和姓名。输出格式：ysbm,ysxm
select a.ysbm,a.xm ysxm
from dw.doctor a,dw.doctor b
where a.ksfzr = b.ysbm
and a.xm = b.xm
and a.ysbm<>b.ysbm
- 查询未结算费用凭单的总金额、自付金额和报销金额。输出格式：zje_wjs,zfje_wjs,bxje_wjs
select sum(nvl(zje,0)) zje_wjs,
sum(nvl(zfje,0)) zfje_wjs,
sum(nvl(bxje,0)) bxje_wjs
from dw.patient_script_genl
where jshid is null
- 新增科室，科室编码为’999’，科室名称为’核磁共振室’。
insert into dw.dept (ksbm,ksmc) values(\’9999\’,\’核磁共振室\’)
- 查询2012年的平均报销金额。输出格式：pjbxje_2012
select round(avg(bxje),2) pjbxje_2012
from dw.patient_script_genl a
where a.fyfsrq >= to_date(\’20120101\’, \’yyyymmdd\’)
and a.fyfsrq < to_date(\’20130101\’, \’yyyymmdd\’)
- 查询重名的病人姓名及该姓名重名的人数。输出格式：xm,cmrs
select xm, count(*) cmrs
from dw.per_natl
group by xm
having count(*) > 1
- 查询总报销金额大于100元的病人个人编码和姓名。输出格式：grbm,xm
select distinct a.grbm, a.xm
from dw.per_natl a,
dw.patient_script_genl b
where a.grbm = b.grbm
group by a.grbm, a.xm
having sum(b.bxje) > 100
- 查询使用医疗项目数目最多的费用凭单编码，假设数目最多的凭单只有一个。输出格式：fyid
select fyid
from (select fyid, count(distinct ylxmbm) sl
from dw.patient_script_detl
group by fyid
order by sl desc) t
where rownum = 1
- 删除没有发生过费用的病人信息。（先备份，备份表存放在si0001用户下，再根据备份删除）
create table si0001.per_natl_free
as
select *
from dw.per_natl a
where not exists(select \’\’ from dw.patient_script_genl b where a.grbm = b.grbm);
delete from dw.per_natl a
where exists (select \’\’ from si0001.per_natl_free b where a.grbm = b.grbm)
and not exists (select \’\’ from dw.patient_script_genl b where a.grbm = b.grbm);
- 将高于平均价的药品的价格降低10%。（先备份，备份表存放在si0001用户下，再更新）
create table si0001.medi_natl_above_avg
as
select *
from dw.medi_item;
update dw.medi_item a
set dj = dj * 0.9
where dj > (select avg(dj)
from dw.medi_item
where ylxmlb = \’1\’)
and ylxmlb = \’1\’
and exists(select \’x\’ from si0001.medi_natl_above_avg where ylxmbm=a.ylxmbm );
综合练习题1-1
- 写一个sql，从这个dw.sq_grbm中取值，输出格式：grbm。
要求取值结果以当前年月日开头+sequence。如：20140412-100001
select to_char(sysdate,\’yyyymmdd\’)||\’-\’||dw.sq_grbm.nextval grbm
from dual
- 查询姓张的病人姓名和出生年份。输出格式：xm,csnf
select a.xm, to_char(a.csrq, \’yyyy\’) csnf
from dw.per_natl a
where a.xm like \’张%\’
- 查询药品的平均单价，输出格式：pjdj
select trunc(avg(a.dj),2) pjdj
from dw.medi_item a
where a.ylxmlb = \’1\’
- 查询发生过费用的病人总人数，输出格式：zrs
select count(distinct(grbm)) zrs
from dw.patient_script_genl
- 查询2000年以后（不包含2000年出生的）出生的病人姓名。输出格式：xm
select xm
from dw.per_natl
where csrq>to_date(\’20001231\’,\’yyyymmdd\’);
- 查询没有被使用过的医疗项目编码和名称。输出格式：ylxmbm,ylxmmc
select a.ylxmbm, a.ylxmmc
from dw.medi_item a
where not exists (select b.ylxmbm
from dw.patient_script_detl b
where a.ylxmbm = b.ylxmbm)
- 查询与自己的科室负责人同名的医师的编码和姓名。输出格式：ysbm,ysxm
select a.ysbm,a.xm ysxm
from dw.doctor a,dw.doctor b
where a.ksfzr = b.ysbm
and a.xm = b.xm
and a.ysbm<>b.ysbm
- 查询未结算费用凭单的总金额、自付金额和报销金额。输出格式：zje_wjs,zfje_wjs,bxje_wjs
select sum(nvl(zje,0)) zje_wjs,
sum(nvl(zfje,0)) zfje_wjs,
sum(nvl(bxje,0)) bxje_wjs
from dw.patient_script_genl
where jshid is null
- 新增科室，科室编码为’999’，科室名称为’核磁共振室’。
insert into dw.dept (ksbm,ksmc) values(\’9999\’,\’核磁共振室\’)
- 查询2012年的平均报销金额。输出格式：pjbxje_2012
select round(avg(bxje),2) pjbxje_2012
from dw.patient_script_genl a
where a.fyfsrq >= to_date(\’20120101\’, \’yyyymmdd\’)
and a.fyfsrq < to_date(\’20130101\’, \’yyyymmdd\’)
- 查询重名的病人姓名及该姓名重名的人数。输出格式：xm,cmrs
select xm, count(*) cmrs
from dw.per_natl
group by xm
having count(*) > 1
- 查询总报销金额大于100元的病人个人编码和姓名。输出格式：grbm,xm
select distinct a.grbm, a.xm
from dw.per_natl a,
dw.patient_script_genl b
where a.grbm = b.grbm
group by a.grbm, a.xm
having sum(b.bxje) > 100
- 查询使用医疗项目数目最多的费用凭单编码，假设数目最多的凭单只有一个。输出格式：fyid
select fyid
from (select fyid, count(distinct ylxmbm) sl
from dw.patient_script_detl
group by fyid
order by sl desc) t
where rownum = 1
- 删除没有发生过费用的病人信息。（先备份，备份表存放在si0001用户下，再根据备份删除）
create table si0001.per_natl_free
as
select *
from dw.per_natl a
where not exists(select \’\’ from dw.patient_script_genl b where a.grbm = b.grbm);
delete from dw.per_natl a
where exists (select \’\’ from si0001.per_natl_free b where a.grbm = b.grbm)
and not exists (select \’\’ from dw.patient_script_genl b where a.grbm = b.grbm);
- 将高于平均价的药品的价格降低10%。（先备份，备份表存放在si0001用户下，再更新）
create table si00
综合练习题1-1
- 写一个sql，从这个dw.sq_grbm中取值，输出格式：grbm。
要求取值结果以当前年月日开头+sequence。如：20140412-100001
select to_char(sysdate,\’yyyymmdd\’)||\’-\’||dw.sq_grbm.nextval grbm
from dual
- 查询姓张的病人姓名和出生年份。输出格式：xm,csnf
select a.xm, to_char(a.csrq, \’yyyy\’) csnf
from dw.per_natl a
where a.xm like \’张%\’
- 查询药品的平均单价，输出格式：pjdj
select trunc(avg(a.dj),2) pjdj
from dw.medi_item a
where a.ylxmlb = \’1\’
- 查询发生过费用的病人总人数，输出格式：zrs
select count(distinct(grbm)) zrs
from dw.patient_script_genl
- 查询2000年以后（不包含2000年出生的）出生的病人姓名。输出格式：xm
select xm
from dw.per_natl
where csrq>to_date(\’20001231\’,\’yyyymmdd\’);
- 查询没有被使用过的医疗项目编码和名称。输出格式：ylxmbm,ylxmmc
select a.ylxmbm, a.ylxmmc
from dw.medi_item a
where not exists (select b.ylxmbm
from dw.patient_script_detl b
where a.ylxmbm = b.ylxmbm)
- 查询与自己的科室负责人同名的医师的编码和姓名。输出格式：ysbm,ysxm
select a.ysbm,a.xm ysxm
from dw.doctor a,dw.doctor b
where a.ksfzr = b.ysbm
and a.xm = b.xm
and a.ysbm<>b.ysbm
- 查询未结算费用凭单的总金额、自付金额和报销金额。输出格式：zje_wjs,zfje_wjs,bxje_wjs
select sum(nvl(zje,0)) zje_wjs,
sum(nvl(zfje,0)) zfje_wjs,
sum(nvl(bxje,0)) bxje_wjs
from dw.patient_script_genl
where jshid is null
- 新增科室，科室编码为’999’，科室名称为’核磁共振室’。
insert into dw.dept (ksbm,ksmc) values(\’9999\’,\’核磁共振室\’)
- 查询2012年的平均报销金额。输出格式：pjbxje_2012
select round(avg(bxje),2) pjbxje_2012
from dw.patient_script_genl a
where a.fyfsrq >= to_date(\’20120101\’, \’yyyymmdd\’)
and a.fyfsrq < to_date(\’20130101\’, \’yyyymmdd\’)
- 查询重名的病人姓名及该姓名重名的人数。输出格式：xm,cmrs
select xm, count(*) cmrs
from dw.per_natl
group by xm
having count(*) > 1
- 查询总报销金额大于100元的病人个人编码和姓名。输出格式：grbm,xm
select distinct a.grbm, a.xm
from dw.per_natl a,
dw.patient_script_genl b
where a.grbm = b.grbm
group by a.grbm, a.xm
having sum(b.bxje) > 100
- 查询使用医疗项目数目最多的费用凭单编码，假设数目最多的凭单只有一个。输出格式：fyid
select fyid
from (select fyid, count(distinct ylxmbm) sl
from dw.patient_script_detl
group by fyid
order by sl desc) t
where rownum = 1
- 删除没有发生过费用的病人信息。（先备份，备份表存放在si0001用户下，再根据备份删除）
create table si0001.per_natl_free
as
select *
from dw.per_natl a
where not exists(select \’\’ from dw.patient_script_genl b where a.grbm = b.grbm);
delete from dw.per_natl a
where exists (select \’\’ from si0001.per_natl_free b where a.grbm = b.grbm)
and not exists (select \’\’ from dw.patient_script_genl b where a.grbm = b.grbm);
- 将高于平均价的药品的价格降低10%。（先备份，备份表存放在si0001用户下，再更新）
create table si0001.medi_natl_above_avg
as
select *
from dw.medi_item;
update dw.medi_item a
set dj = dj * 0.9
where dj > (select avg(dj)
from dw.medi_item
where ylxmlb = \’1\’)
and ylxmlb = \’1\’
and exists(select \’x\’ from si0001.medi_natl_above_avg where ylxmbm=a.ylxmbm );
综合练习题1-1
- 写一个sql，从这个dw.sq_grbm中取值，输出格式：grbm。
要求取值结果以当前年月日开头+sequence。如：20140412-100001
select to_char(sysdate,\’yyyymmdd\’)||\’-\’||dw.sq_grbm.nextval grbm
from dual
- 查询姓张的病人姓名和出生年份。输出格式：xm,csnf
select a.xm, to_char(a.csrq, \’yyyy\’) csnf
from dw.per_natl a
where a.xm like \’张%\’
- 查询药品的平均单价，输出格式：pjdj
select trunc(avg(a.dj),2) pjdj
from dw.medi_item a
where a.ylxmlb = \’1\’
- 查询发生过费用的病人总人数，输出格式：zrs
select count(distinct(grbm)) zrs
from dw.patient_script_genl
- 查询2000年以后（不包含2000年出生的）出生的病人姓名。输出格式：xm
select xm
from dw.per_natl
where csrq>to_date(\’20001231\’,\’yyyymmdd\’);
- 查询没有被使用过的医疗项目编码和名称。输出格式：ylxmbm,ylxmmc
select a.ylxmbm, a.ylxmmc
from dw.medi_item a
where not exists (select b.ylxmbm
from dw.patient_script_detl b
where a.ylxmbm = b.ylxmbm)
- 查询与自己的科室负责人同名的医师的编码和姓名。输出格式：ysbm,ysxm
select a.ysbm,a.xm ysxm
from dw.doctor a,dw.doctor b
where a.ksfzr = b.ysbm
and a.xm = b.xm
and a.ysbm<>b.ysbm
- 查询未结算费用凭单的总金额、自付金额和报销金额。输出格式：zje_wjs,zfje_wjs,bxje_wjs
select sum(nvl(zje,0)) zje_wjs,
sum(nvl(zfje,0)) zfje_wjs,
sum(nvl(bxje,0)) bxje_wjs
from dw.patient_script_genl
where jshid is null
- 新增科室，科室编码为’999’，科室名称为’核磁共振室’。
insert into dw.dept (ksbm,ksmc) values(\’9999\’,\’核磁共振室\’)
- 查询2012年的平均报销金额。输出格式：pjbxje_2012
select round(avg(bxje),2) pjbxje_2012
from dw.patient_script_genl a
where a.fyfsrq >= to_date(\’20120101\’, \’yyyymmdd\’)
and a.fyfsrq < to_date(\’20130101\’, \’yyyymmdd\’)
- 查询重名的病人姓名及该姓名重名的人数。输出格式：xm,cmrs
select xm, count(*) cmrs
from dw.per_natl
group by xm
having count(*) > 1
- 查询总报销金额大于100元的病人个人编码和姓名。输出格式：grbm,xm
select distinct a.grbm, a.xm
from dw.per_natl a,
dw.patient_script_genl b
where a.grbm = b.grbm
group by a.grbm, a.xm
having sum(b.bxje) > 100
- 查询使用医疗项目数目最多的费用凭单编码，假设数目最多的凭单只有一个。输出格式：fyid
select fyid
from (select fyid, count(distinct ylxmbm) sl
from dw.patient_script_detl
group by fyid
order by sl desc) t
where rownum = 1
- 删除没有发生过费用的病人信息。（先备份，备份表存放在si0001用户下，再根据备份删除）
create table si0001.per_natl_free
as
select *
from dw.per_natl a
where not exists(select \’\’ from dw.patient_script_genl b where a.grbm = b.grbm);
delete from dw.per_natl a
where exists (select \’\’ from si0001.per_natl_free b where a.grbm = b.grbm)
and not exists (select \’\’ from dw.patient_script_genl b where a.grbm = b.grbm);
- 将高于平均价的药品的价格降低10%。（先备份，备份表存放在si0001用户下，再更新）
create table si0001.medi_natl_above_avg
as
select *
from dw.medi_item;
update dw.medi_item a
set dj = dj * 0.9
where dj > (select avg(dj)
from dw.medi_item
where ylxmlb = \’1\’)
and ylxmlb = \’1\’
and exists(select \’x\’ from si0001.medi_natl_above_avg where ylxmbm=a.ylxmbm );
综合练习题1-1
- 写一个sql，从这个dw.sq_grbm中取值，输出格式：grbm。
要求取值结果以当前年月日开头+sequence。如：20140412-100001
select to_char(sysdate,\’yyyymmdd\’)||\’-\’||dw.sq_grbm.nextval grbm
from dual
- 查询姓张的病人姓名和出生年份。输出格式：xm,csnf
select a.xm, to_char(a.csrq, \’yyyy\’) csnf
from dw.per_natl a
where a.xm like \’张%\’
- 查询药品的平均单价，输出格式：pjdj
select trunc(avg(a.dj),2) pjdj
from dw.medi_item a
where a.ylxmlb = \’1\’
- 查询发生过费用的病人总人数，输出格式：zrs
select count(distinct(grbm)) zrs
from dw.patient_script_genl
- 查询2000年以后（不包含2000年出生的）出生的病人姓名。输出格式：xm
select xm
from dw.per_natl
where csrq>to_date(\’20001231\’,\’yyyymmdd\’);
- 查询没有被使用过的医疗项目编码和名称。输出格式：ylxmbm,ylxmmc
select a.ylxmbm, a.ylxmmc
from dw.medi_item a
where not exists (select b.ylxmbm
from dw.patient_script_detl b
where a.ylxmbm = b.ylxmbm)
- 查询与自己的科室负责人同名的医师的编码和姓名。输出格式：ysbm,ysxm
select a.ysbm,a.xm ysxm
from dw.doctor a,dw.doctor b
where a.ksfzr = b.ysbm
and a.xm = b.xm
and a.ysbm<>b.ysbm
- 查询未结算费用凭单的总金额、自付金额和报销金额。输出格式：zje_wjs,zfje_wjs,bxje_wjs
select sum(nvl(zje,0)) zje_wjs,
sum(nvl(zfje,0)) zfje_wjs,
sum(nvl(bxje,0)) bxje_wjs
from dw.patient_script_genl
where jshid is null
- 新增科室，科室编码为’999’，科室名称为’核磁共振室’。
insert into dw.dept (ksbm,ksmc) values(\’9999\’,\’核磁共振室\’)
- 查询2012年的平均报销金额。输出格式：pjbxje_2012
select round(avg(bxje),2) pjbxje_2012
from dw.patient_script_genl a
where a.fyfsrq >= to_date(\’20120101\’, \’yyyymmdd\’)
and a.fyfsrq < to_date(\’20130101\’, \’yyyymmdd\’)
- 查询重名的病人姓名及该姓名重名的人数。输出格式：xm,cmrs
select xm, count(*) cmrs
from dw.per_natl
group by xm
having count(*) > 1
- 查询总报销金额大于100元的病人个人编码和姓名。输出格式：grbm,xm
select distinct a.grbm, a.xm
from dw.per_natl a,
dw.patient_script_genl b
where a.grbm = b.grbm
group by a.grbm, a.xm
having sum(b.bxje) > 100
- 查询使用医疗项目数目最多的费用凭单编码，假设数目最多的凭单只有一个。输出格式：fyid
select fyid
from (select fyid, count(distinct ylxmbm) sl
from dw.patient_script_detl
group by fyid
order by sl desc) t
where rownum = 1
- 删除没有发生过费用的病人信息。（先备份，备份表存放在si0001用户下，再根据备份删除）
create table si0001.per_natl_free
as
select *
from dw.per_natl a
where not exists(select \’\’ from dw.patient_script_genl b where a.grbm = b.grbm);
delete from dw.per_natl a
where exists (select \’\’ from si0001.per_natl_free b where a.grbm = b.grbm)
and not exists (select \’\’ from dw.patient_script_genl b where a.grbm = b.grbm);
- 将高于平均价的药品的价格降低10%。（先备份，备份表存放在si0001用户下，再更新）
create table si0001.medi_natl_above_avg
as
select *
from dw.medi_item;
update dw.medi_item a
set dj = dj * 0.9
where dj > (select avg(dj)
from dw.medi_item
where ylxmlb = \’1\’)
and ylxmlb = \’1\’
and exists(select \’x\’ from si0001.medi_natl_above_avg where ylxmbm=a.ylxmbm );
01.medi_natl_above_avg
as
select *
from dw.medi_item;
update dw.medi_item a
set dj = dj * 0.9
where dj > (select avg(dj)
from dw.medi_item
where ylxmlb = \’1\’)
and ylxmlb = \’1\’
and exists(select \’x\’ from si0001.medi_natl_above_avg where ylxmbm=a.ylxmbm );
