Oracle数据库练习题

综合练习题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 );