广医信息系统oracle作业.docx

1、广医信息系统oracle作业案例1： 供应商-供应-药品数据库中包括以下3张表，表结构参见表3.1、表3.2、表3.3。表3.1 供货商表（ Provider）字段中文名字段名类型空值备注供应商代码ProviderCodeChar(4)No主码供应商ProviderNameChar(60)No拼音简码PyCodeChar(10)Yes地址AddressChar(50)Yes电话TelChar(15)Yes邮编ZipChar(6)YesEmailEmailChar(30)Yes联系人RelationChar(8)Yes表3.2 药品表（Medicine）字段中文名字段名类型空值备注药品代码Med

2、icineCodeChar(5)No主码药品名称MedicineNameVarchar(50)No拼音简码PyCodeChar(10)Yes剂型DosageFormChar(6)Yes规格StandardChar(15)Yes单位UnitChar(10)Yes批号BatchNumberChar(20)Yes生产日期ProductionDateSmallDatetimeYes失效日期ExpirationDateSmallDatetimeYes药品类别CategoryChar(10)Yes中成药、西药等医保YBChar(2)Yes默认为“否”表3.3 供应表 （PM）字段中文名字段名类型空值备注药

3、品代码MedicineCodeChar(5)No主码供应商代码ProviderCodeChar(4)No主码供应日期PMDateSmallDatetimeNo主码价格PriceMoneyYes数量QytIntYes各张表的数据示例参见表3.4、表3.5、表3.6。表3.4 供货商表（ Provider）实例数据ProviderCodeProviderNamePyCodeAddressTelZipEmailRelationS001河北东风药业Hbdfyy河北省永年县城西0310-*057150hbdf张三S002浙江康恩贝Zzkeb杭州市高新技术开发区0571-*310045ttm_8512李四

4、S003青岛鲁健药业Qdljyy青岛市北区延安路2660001375685404王五S004哈药制药Hyzy哈尔滨市南岗区学府路150000hh王六表3.5 药品表（Medicine）实例数据MedicineCodeMedicineNamePyCodeDosageFormStandardBatchNumberProductionDateExpirationDatecategoryYB10001小儿感冒颗粒Xegmkl颗粒剂12g/袋Z53020405 2009-01-012012-12-31中成药是10002维生素C银翘片Wsscyqp片剂49.5mg/片Z410223182010-01-01

5、2012-06-30中成药是10003清热解毒胶囊Qrjdjn胶囊剂0.3g/粒Z200546632012-06-302014-06-30中成药是10004小柴胡冲剂Xchcj颗粒剂10g/袋Z440207092012-12-012014-08-30中成药是20006新康泰克Xktk胶囊剂0.25g/粒H200104302011-02-252013.08-25西药是20007护彤ht颗粒剂2g/袋H230226132004-10-072007-10-07西药否20008救急散Jjs散剂1.5g/瓶Z110201382012-01-012015-01-01西药否表3.6 供应表 （PM）实例数据

6、MedicineCodeProviderCodePriceQytPMDate10002S0013.001502010-02-0110003S00124.002302012-08-0110004S0019.005002013.01-0110004S0029.001002013.02-0220004S00235.002002012-01-0120008S003701002012-04-01首先：为药库数据库通过DBCA创建了数据库YK，在YK数据库里创建用户数据表空间，建立表空间P_M及临时表空间为P_M_TEMP，创建用户MDAdmin、用户密码为123456。授予角色CONNECT及DBA，完

7、成了药库数据库建设的框架工作，为创建数据库对象做好准备，过程如下： （1）创建用户表空间P_M，用来存放药库的数据表;create tablespace p_m2 datafile C:appAdministratororadataYKp_m1 size 100m;（2）创建临时表空间P_M_TEMP；Create temporary tablespace p_m2_temp tempfileC:appAdministratororadataYKp_m2_temp.dbf size 100m;（3）创建用户MDADMIN并指定表空间;create user MDADMIN identified

8、 by 123456 default tablespace p_m2 temporary tablespace p_m2_temp;（4）为用户授予权限;grant connect to MDADMIN;grant dba to MDADMIN;（5）通过SQL DEVELOPER建立用户MDAdmin 到YK数据库的连接，更直观的对数据库进行操作。 再完成以下工作：1)创建药品表（Medicine），药品代码是主码，批号取值唯一，在创建表的过程中使用check约束和默认(DEFAULT)约束。create table Medicine(MedicineCode CHAR(5) primary

9、 key not null,MedicineName Varchar(50) not null,BatchNumber CHAR(20) unique,YB char(2) default 否,CHECK (YB =是or YB=否),PyCode CHAR(10),DosageForm CHAR(6),Standard CHAR(15),Unit CHAR(10),ProductionDate Date,ExpirationDate Date,Category CHAR(10);2)创建供货商表（Provider），主码建为表级约束。create table Provider(Provide

10、rCode CHAR(4) primary key not null, ProviderName CHAR(60) not null, PyCode CHAR(10) null, Address CHAR(50) null, Tel CHAR(15) null, Zip CHAR(6) null, Email CHAR(30) null, Relation CHAR(8) null, FOREIGN KEY (ProviderCode) REFERENCES Provider(ProviderCode);3)建立供应表（PM），包含主码、外码，均为表级约束。create table PM(Me

11、dicineCode CHAR(5) not null, ProviderCode char(4) not null, PMDate Date not null, Price number null, Qyt Int null, primary key (MedicineCode,ProviderCode, PMDate), foreign key (MedicineCode) references Medicine(MedicineCode), foreign key (ProviderCode) references Provider(ProviderCode);4)向Medicine表增

12、加“使用说明（Memo）”列，其数据类型为字符串类型。ALTER TABLE Medicine ADD Memo CHAR;5)将Medicine表的Memo列删除。ALTER TABLE Medicine DROP column Memo;6)将Provider表的Address列的数据类型由Char(50)改为Char(60)。ALTER TABLE Provider modify Address char(60);7)要求PM表的Qyt取值在0至1000之间。 ALTER TABLE PM ADD constraint Qyt check(Qyt0 and Qyt1000);8)要求供应

13、表（PM）中的PMDate默认为当前时间。 ALTER TABLE PM modify PMDate DATE default sysdate;9)删除PM表的默认约束DF_PMDate。 ALTER TABLE PM drop constraint DF_PMDate;10)查询所有药品的药品代码、药品名称。 select MedicineCode,MedicineName from Medicine;11)查询所有药品的剂型、药品名称、药品类别。 select DosageForm,MedicineName,Category from Medicine;12)查询所有药品的详细记录。 se

14、lect * from Medicine;13)查询所有药品的药品代码、药品名称、生产年份。 select MedicineCode,MedicineName,ProductionDate from Medicine;14)查询药品名称、批号，生产年份、保质期。可使用列别名改变查询结果的列标题。 select MedicineName,BatchNumber,ProductionDate,ExpirationDate-ProductionDate AS 保质期 from Medicine;15)查询供应了药品的供应商代码。 select ProviderCode from PM;16)查询中成

15、药类的药品名称。 select MedicineName from Medicine where category=中成药;17)查询所有过期药品的药品代码、药品名称、失效日期。 select MedicineCode,MedicineName,ExpirationDate from Medicine where ExpirationDatesysdate; 18)查询供应价格在10元以内的药品代码。 select MedicineCode from PM where Priceto_date(2011-06-01,YYYY-MM-DD) and ProductionDateto_date(2

16、012-01-01,YYYY-MM-DD);30)查询供应药品代码为的供应商代码，供应日期，价格，数量查询结果按价格降序排列。 select MedicineCode,ProviderCode,PMDate,Price,Qyt from PM order by Price desc;31)查询药品基本信息，查询结果按照药品类别升序排列，同一类别按照生产日期降序排列。 select * from Medicine order by Category,ProductionDate desc;32)查询药品的总个数。 select count(*) from PM;33)查询供应药品的供应商个数。

17、select count(distinct ProviderCode) from PM;34)计算“10004”号药品的平均供应价格。 select avg(Price) from PM where MedicineCode=10004;35)计算“10004”号药品的最高供应价格。 select max(Price) from PM where MedicineCode=10004;36)查询“S001”供应商供应的药品总数量。select sum(Qyt) from PM where ProviderCode=S001;37)求每个供应商供应的药品个数。 select ProviderCo

18、de,sum(Qyt) from PM group by ProviderCode;38)查询供应了3种以上药品的供应商代码。 select ProviderCode from PM group by ProviderCode having count(*)3;39)查询每个供应商及其供应药品的情况。 select Provider.*,PM.* from Provider,PM where Provider.ProviderCode=PM.ProviderCode;40)查询与小儿感冒颗粒相同剂型的药品信息。 select * from Medicine where DosageForm i

19、n(select DosageForm from Medicine where MedicineName=小儿感冒颗粒);41)右外连接：查询所有药品被供应的情况。 select * from Medicine right outer join PM on(Medicine.MedicineCode=PM.MedicineCode);42)查询维生素C银翘片的供应情况 select PM.MedicineCode,ProviderCode,Price,Qyt,PMDate from Medicine,PM where Medicine.MedicineCode=PM.MedicineCode

20、and MedicineName=维生素C银翘片;43)查询每个供应商供应的药品代码，药品名称、价格、数量、供应商名称、供应年份。 select PM.MedicineCode,Price,Qyt,PMDate,MedicineName,ProviderName from Medicine,Provider,PM where Provider.ProviderCode=PM.ProviderCode and Medicine.MedicineCode=PM.MedicineCode;44)查询浙江康恩贝供应的药品代码和药品名称。 select MedicineCode,MedicineName

21、 from Medicine where MedicineCode in(select MedicineCode from PM where ProviderCode in(select ProviderCode from Provider where ProviderName=浙江康恩贝);45)找出每种药品供应价格超出它的供应平均价格的供应商代码（相关子查询）。 select MedicineCode,ProviderCode from PM x where Price=(select avg(Price) from PM y where y.MedicineCode=x.Medicine

22、Code);46)查询S001供应商供应的药品名称（用EXISTS谓词）。 select MedicineName from Medicine where EXISTS(select * from PM where MedicineCode=Medicine.MedicineCode and ProviderCode=S001);47)查询颗粒剂的药品及中成药，用集合操场完成。 select * from Medicine where DosageForm=颗粒剂union select * from Medicine where category=中成药;48)查询颗粒剂的药品与中成药的交集

23、。 select * from Medicine where DosageForm=颗粒剂intersect select * from Medicine where category=中成药;49)查询颗粒剂的药品与中成药的差集。 select * from Medicine where DosageForm=颗粒剂minus select * from Medicine where category=中成药;将（药品代码:10007，药品名称：藿香正气水，拼音简码：hxzqs，剂型：口服液，规格：ml/支，批号：Z51021352，生产日期：-12-20，失效日期：-12-20，药品类别：

24、中成药，是否医保：是）插入到Medicine表中。insert into Medicine(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 ，供应商名称：华西制药，其他信息暂时未知） ins

25、ert into Provider(ProviderCode,ProviderName) values (S005,华西制药);对每一种药品，求供应商供应的平均价格，并把结果存入数据库。（先建立一张表：CREATE TABLE AVG_Medicine_Price (MedicineCode Char(10), Avg_Price number) ; Insert into AVG_Medicine_Price(MedicineCode,Avg_Price) select MedicineCode,avg(Price) from PM group by MedicineCode;51)将供应商S001的联系人改为彭大。 update Provider set Relation=彭大 where ProviderCode=S001;52)将所有药品的供应价格提高5%。 update PM set Price=Price*1.05;53)将浙江康恩贝供应的药品数量置零。 u