广医信息系统oracle作业.docx

广医信息系统oracle作业.docx

《广医信息系统oracle作业.docx》由会员分享，可在线阅读，更多相关《广医信息系统oracle作业.docx（16页珍藏版）》请在冰豆网上搜索。

广医信息系统oracle作业

案例1：

供应商-供应-药品数据库中包括以下3张表，表结构参见表3.1、表3.2、表3.3。

表3.1供货商表（Provider）

字段中文名

字段名

类型

空值

备注

供应商代码

ProviderCode

Char（4）

No

主码

供应商

ProviderName

Char（60）

No

拼音简码

PyCode

Char（10）

Yes

地址

Address

Char（50）

Yes

电话

Tel

Char（15）

Yes

邮编

Zip

Char（6）

Yes

Email

Email

Char（30）

Yes

联系人

Relation

Char（8）

Yes

表3.2药品表（Medicine）

字段中文名

字段名

类型

空值

备注

药品代码

MedicineCode

Char（5）

No

主码

药品名称

MedicineName

Varchar（50）

No

拼音简码

PyCode

Char（10）

Yes

剂型

DosageForm

Char（6）

Yes

规格

Standard

Char（15）

Yes

单位

Unit

Char（10）

Yes

批号

BatchNumber

Char（20）

Yes

生产日期

ProductionDate

SmallDatetime

Yes

失效日期

ExpirationDate

SmallDatetime

Yes

药品类别

Category

Char（10）

Yes

中成药、西药等

医保

YB

Char

（2）

Yes

默认为“否”

表3.3供应表（PM）

字段中文名

字段名

类型

空值

备注

药品代码

MedicineCode

Char（5）

No

主码

供应商代码

ProviderCode

Char（4）

No

主码

供应日期

PMDate

SmallDatetime

No

主码

价格

Price

Money

Yes

数量

Qyt

Int

Yes

各张表的数据示例参见表3.4、表3.5、表3.6。

表3.4供货商表（Provider）实例数据

ProviderCode

ProviderName

PyCode

Address

Tel

Zip

Email

Relation

S001

河北东风药业

Hbdfyy

河北省永年县城西

0310-*******

057150

hbdf@

张三

S002

浙江康恩贝

Zzkeb

杭州市高新技术开发区

0571-********

310045

ttm_8512@

李四

S003

青岛鲁健药业

Qdljyy

青岛市北区延安路

266000

1375685404@

王五

S004

哈药制药

Hyzy

哈尔滨市南岗区学府路

150000

hh@

王六

表3.5药品表（Medicine）实例数据

MedicineCode

MedicineName

PyCode

DosageForm

Standard

BatchNumber

ProductionDate

ExpirationDate

category

YB

10001

小儿感冒颗粒

Xegmkl

颗粒剂

12g/袋

Z53020405

2009-01-01

2012-12-31

中成药

是

10002

维生素C银翘片

Wsscyqp

片剂

49.5mg/片

Z41022318

2010-01-01

2012-06-30

中成药

是

10003

清热解毒胶囊

Qrjdjn

胶囊剂

0.3g/粒

Z20054663

2012-06-30

2014-06-30

中成药

是

10004

小柴胡冲剂

Xchcj

颗粒剂

10g/袋

Z44020709

2012-12-01

2014-08-30

中成药

是

20006

新康泰克

Xktk

胶囊剂

0.25g/粒

H20010430

2011-02-25

2013.08-25

西药

是

20007

护彤

ht

颗粒剂

2g/袋

H23022613

2004-10-07

2007-10-07

西药

否

20008

救急散

Jjs

散剂

1.5g/瓶

Z11020138

2012-01-01

2015-01-01

西药

否

表3.6供应表（PM）实例数据

MedicineCode

ProviderCode

Price

Qyt

PMDate

10002

S001

3.00

150

2010-02-01

10003

S001

24.00

230

2012-08-01

10004

S001

9.00

500

2013.01-01

10004

S002

9.00

100

2013.02-02

20004

S002

35.00

200

2012-01-01

20008

S003

70

100

2012-04-01

首先：

为药库数据库通过DBCA创建了数据库YK，在YK数据库里创建用户数据表空间，建立表空间P_M及临时表空间为P_M_TEMP，创建用户MDAdmin、用户密码为123456。

授予角色CONNECT及DBA，完成了药库数据库建设的框架工作，为创建数据库对象做好准备，过程如下：

（1）创建用户表空间P_M，用来存放药库的数据表;

createtablespacep_m2datafile'C:

\app\Administrator\oradata\YK\p_m1'size100m;

（2）创建临时表空间P_M_TEMP；

Createtemporarytablespacep_m2_temptempfile'C:

\app\Administrator\oradata\YK\p_m2_temp.dbf'size100m;

（3）创建用户MDADMIN并指定表空间;

createuserMDADMINidentifiedby123456defaulttablespacep_m2temporarytablespacep_m2_temp;

（4）为用户授予权限;

grantconnecttoMDADMIN;

grantdbatoMDADMIN;

（5）通过SQLDEVELOPER建立用户MDAdmin到YK数据库的连接，更直观的对数据库进行操作。

再完成以下工作：

1）创建药品表（Medicine），药品代码是主码，批号取值唯一，在创建表的过程中使用check约束和默认（DEFAULT）约束。

createtableMedicine

（MedicineCodeCHAR（5）primarykeynotnull,

MedicineNameVarchar（50）notnull,

BatchNumberCHAR（20）unique,

YBchar

（2）default'否',

CHECK（YB='是'orYB='否'）,

PyCodeCHAR（10）,

DosageFormCHAR（6）,

StandardCHAR（15）,

UnitCHAR（10）,

ProductionDateDate,

ExpirationDateDate,

CategoryCHAR（10）

）;

2）创建供货商表（Provider），主码建为表级约束。

createtableProvider

（ProviderCodeCHAR（4）primarykeynotnull,

ProviderNameCHAR（60）notnull,

PyCodeCHAR（10）null,

AddressCHAR（50）null,

TelCHAR（15）null,

ZipCHAR（6）null,

EmailCHAR（30）null,

RelationCHAR（8）null,

FOREIGNKEY（ProviderCode）REFERENCESProvider（ProviderCode）

）;

3）建立供应表（PM），包含主码、外码，均为表级约束。

createtablePM

（MedicineCodeCHAR（5）notnull,

ProviderCodechar（4）notnull,

PMDateDatenotnull,

Pricenumbernull,

QytIntnull,

primarykey（MedicineCode,ProviderCode,PMDate）,

foreignkey（MedicineCode）referencesMedicine（MedicineCode）,

foreignkey（ProviderCode）referencesProvider（ProviderCode）

）;

4）向Medicine表增加“使用说明（Memo）”列，其数据类型为字符串类型。

ALTERTABLEMedicineADDMemoCHAR;

5）将Medicine表的Memo列删除。

ALTERTABLEMedicineDROPcolumnMemo;

6）将Provider表的Address列的数据类型由Char（50）改为Char（60）。

ALTERTABLEProvidermodifyAddresschar（60）;

7）要求PM表的Qyt取值在0至1000之间。

ALTERTABLEPMADDconstraintQytcheck（Qyt>0andQyt<1000）;

8）要求供应表（PM）中的PMDate默认为当前时间。

ALTERTABLEPMmodifyPMDateDATEdefaultsysdate;

9）删除PM表的默认约束DF_PMDate。

ALTERTABLEPMdropconstraintDF_PMDate;

10）查询所有药品的药品代码、药品名称。

selectMedicineCode,MedicineNamefromMedicine;

11）查询所有药品的剂型、药品名称、药品类别。

selectDosageForm,MedicineName,CategoryfromMedicine;

12）查询所有药品的详细记录。

select*fromMedicine;

13）查询所有药品的药品代码、药品名称、生产年份。

selectMedicineCode,MedicineName,ProductionDatefromMedicine;

14）查询药品名称、批号，生产年份、保质期。

可使用列别名改变查询结果的列标题。

selectMedicineName,BatchNumber,ProductionDate,ExpirationDate-ProductionDateAS"保质期"fromMedicine;

15）查询供应了药品的供应商代码。

selectProviderCodefromPM;

16）查询中成药类的药品名称。

selectMedicineNamefromMedicinewherecategory='中成药';

17）查询所有过期药品的药品代码、药品名称、失效日期。

selectMedicineCode,MedicineName,ExpirationDatefromMedicinewhere

ExpirationDate

18）查询供应价格在10元以内的药品代码。

selectMedicineCodefromPMwherePrice<10;

19）查询生产日期在2011-06-01与2012-06-01之间的药品信息。

select*fromMedicinewhereProductionDate>＝to_date（'2011-06-01','YYYY-MM-DD'）andProductionDate

20）查询生产日期不在2011-06-01与2011-06-01之间的药品代码、药品名称、批号。

selectMedicineCode,MedicineName,BatchNumberfromMedicinewhereProductionDatenotbetweento_date（'2011-06-01','YYYY-MM-DD'）andto_date（'2012-06-01','YYYY-MM-DD'）;

21）查询片剂、散剂和颗粒剂的药品代码、药品名称。

selectMedicineCode,MedicineNamefromMedicinewhereDosageForm='片剂'orDosageForm='散剂'orDosageForm='颗粒剂';

22）查询既不片剂、也不是散剂的药品代码、药品名称。

selectMedicineCode,MedicineNamefromMedicinewhereDosageForm!

='片剂'andDosageForm!

='散剂';

23）查询所有拼音简码以x开头的药品代码、药品名称。

selectMedicineCode,MedicineNamefromMedicinewherePyCodelike'x_';

24）查询邮编以“3”开头倒数第二个字符为“4”的供应商信息。

select*fromProviderwhereZiplike'3_%4_';

25）查询邮箱为“****************”的供应商信息。

select*fromProviderwhereEmail='****************';

26）查询邮箱为以“tt_”开头，且倒数第二个字符为“6”的供应商信息。

select*fromProviderwhereEmaillike'tt\_%6_'escape'\';

27）查询没有提供联系电话的供应商基本信息。

select*fromProviderwhereTelisnull;

28）查询所有有联系电话的供应商基本信息。

select*fromProviderwhereTelisnotnull;

29）查询2012年以后生产的剂型为“胶囊”的药品名称。

selectMedicineCodefromMedicinewhereDosageForm='胶囊剂'andProductionDate>＝to_date（'2012-01-01','YYYY-MM-DD'）;

30）查询供应药品代码为的供应商代码，供应日期，价格，数量查询结果按价格降序排列。

selectMedicineCode,ProviderCode,PMDate,Price,QytfromPMorderbyPricedesc;

31）查询药品基本信息，查询结果按照药品类别升序排列，同一类别按照生产日期降序排列。

select*fromMedicineorderbyCategory,ProductionDatedesc;

32）查询药品的总个数。

selectcount（*）fromPM;

33）查询供应药品的供应商个数。

selectcount（distinctProviderCode）fromPM;

34）计算“10004”号药品的平均供应价格。

selectavg（Price）fromPMwhereMedicineCode=10004;

35）计算“10004”号药品的最高供应价格。

selectmax（Price）fromPMwhereMedicineCode=10004;

36）查询“S001”供应商供应的药品总数量。

selectsum（Qyt）fromPMwhereProviderCode='S001';

37）求每个供应商供应的药品个数。

selectProviderCode,sum（Qyt）fromPMgroupbyProviderCode;

38）查询供应了3种以上药品的供应商代码。

selectProviderCodefromPMgroupbyProviderCodehavingcount（*）>3;

39）查询每个供应商及其供应药品的情况。

selectProvider.*,PM.*fromProvider,PMwhereProvider.ProviderCode=PM.ProviderCode;

40）查询与小儿感冒颗粒相同剂型的药品信息。

select*fromMedicinewhereDosageFormin（selectDosageFormfromMedicinewhereMedicineName='小儿感冒颗粒'）;

41）右外连接：

查询所有药品被供应的情况。

select*fromMedicinerightouterjoinPMon（Medicine.MedicineCode=PM.MedicineCode）;

42）查询维生素C银翘片的供应情况

selectPM.MedicineCode,ProviderCode,Price,Qyt,PMDatefromMedicine,PMwhereMedicine.MedicineCode=PM.MedicineCodeandMedicineName='维生素C银翘片';

43）查询每个供应商供应的药品代码，药品名称、价格、数量、供应商名称、供应年份。

selectPM.MedicineCode,Price,Qyt,PMDate,MedicineName,ProviderNamefromMedicine,Provider,PMwhereProvider.ProviderCode=PM.ProviderCodeandMedicine.MedicineCode=PM.MedicineCode;

44）查询"浙江康恩贝"供应的药品代码和药品名称。

selectMedicineCode,MedicineNamefromMedicinewhereMedicineCodein（selectMedicineCodefromPMwhereProviderCodein（selectProviderCodefromProviderwhereProviderName='浙江康恩贝'））;

45）找出每种药品供应价格超出它的供应平均价格的供应商代码（相关子查询）。

selectMedicineCode,ProviderCodefromPMxwherePrice>=（selectavg（Price）fromPMywherey.MedicineCode=x.MedicineCode）;

46）查询S001供应商供应的药品名称（用EXISTS谓词）。

selectMedicineNamefromMedicinewhereEXISTS（select*fromPMwhereMedicineCode=Medicine.MedicineCodeandProviderCode='S001'）;

47）查询颗粒剂的药品及中成药，用集合操场完成。

select*fromMedicinewhereDosageForm='颗粒剂'unionselect*fromMedicinewherecategory='中成药';

48）查询颗粒剂的药品与中成药的交集。

select*fromMedicinewhereDosageForm='颗粒剂'intersectselect*fromMedicinewherecategory='中成药';

49）查询颗粒剂的药品与中成药的差集。

select*fromMedicinewhereDosageForm='颗粒剂'minusselect*fromMedicinewherecategory='中成药';

将（药品代码:

10007，药品名称：

藿香正气水，拼音简码：

hxzqs，剂型：

口服液，规格：

ml/支，批号：

Z51021352，生产日期：

-12-20，失效日期：

-12-20，药品类别：

中成药，是否医保：

是）插入到Medicine表中。

insertintoMedicine（MedicineCode,MedicineName,PyCode,DosageForm,Standard,BatchNumber,ProductionDate,ExpirationDate,category,YB）values（10007,'藿香正气水','hxzqs','口服液','ml/支','Z51021352',to_date（'-12-20','-MM-DD'）,to_date（'-12-20','-MM-DD'）,'中成药','是'）;

50）增加一条供应商记录（供应商代码：

S005，供应商名称：

华西制药，其他信息暂时未知）

insertintoProvider（ProviderCode,ProviderName）values（'S005','华西制药'）;

对每一种药品，求供应商供应的平均价格，并把结果存入数据库。

（先建立一张表：

CREATETABLEAVG_Medicine_Price

（MedicineCodeChar（10）,

Avg_Pricenumber）;

InsertintoAVG_Medicine_Price（MedicineCode,Avg_Price）selectMedicineCode,avg（Price）fromPMgroupbyMedicineCode;

51）将供应商S001的联系人改为彭大。

updateProvidersetRelation='彭大'whereProviderCode='S001';

52）将所有药品的供应价格提高5%。

updatePMsetPrice=Price*1.05;

53）将浙江康恩贝供应的药品数量置零。