《广医信息系统oracle作业》由会员分享，可在线阅读，更多相关《广医信息系统oracle作业（10页珍藏版）》请在金锄头文库上搜索。
1、案例 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) YesEmail Email Char(30) Yes联系人 Relation Char(8) Yes表 3.2 药品表（Medici
2、ne）字段中文名 字段名 类型 空值 备注药品代码 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)
3、 Yes 中成药、西药等医保 YB Char(2) Yes 默认为“否 ”第 3 章 关系数据库标准语言表 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 章 关系数据库标准语言表 3.4 供货商表（ Provider）实例数据ProviderCode ProviderN
4、ame PyCode Address Tel Zip Email RelationS001 河北东风药业 Hbdfyy 河北省永年县城西 0310-6806999 057150 张三S002 浙江康恩贝 Zzkeb 杭州市高新技术开发区 0571-87774811310045 ttm_ 李四S003 青岛鲁健药业 Qdljyy 青岛市北区延安路 266000 王五S004 哈药制药 Hyzy 哈尔滨市南岗区学府路 150000 王六表 3.5 药品表（Medicine）实例数据MedicineCode MedicineName PyCode DosageForm Standard Bat
5、chNumber ProductionDate ExpirationDate category YB10001 小儿感冒颗粒 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
6、-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 PMDate10002 S001 3.00 150 2010-02-0110003
7、S001 24.00 230 2012-08-0110004 S001 9.00 500 2013.01-0110004 S002 9.00 100 2013.02-0220004 S002 35.00 200 2012-01-0120008 S003 70 100 2012-04-01第 3 章 关系数据库标准语言首先：为药库数据库通过 DBCA 创建了数据库 YK，在 YK 数据库里创建用户数据表空间，建立表空间 P_M 及临时表空间为 P_M_TEMP，创建用户 MDAdmin、用户密码为123456。授予角色 CONNECT 及 DBA，完成了药库数据库建设的框架工作，为创建数据库对象
8、做好准备，过程如下： （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 by 123456 default
9、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 key not nul
10、l,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(ProviderCode CHAR
11、(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,第 3 章 关系数据库标准语言Email CHAR(30) null,Relation CHAR(8) null,FOREIGN KEY (ProviderCode) REFERENCES Provider(ProviderCode);3) 建立供应表（PM ），包含主码、外码，均为表级约束。create table PM(Med
12、icineCode 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表增加“使用说明
13、（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 Qytto_
14、date(2011-06-01,YYYY-MM-DD) and ProductionDateto_date(2012-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
15、;32) 查询药品的总个数。select count(*) from PM;33) 查询供应药品的供应商个数。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;第 3 章 关系数据库标准语言36) 查询“S001”供应商供应的药品总数量。select sum(Q
16、yt) from PM where ProviderCode=S001;37) 求每个供应商供应的药品个数。select ProviderCode,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 DosageFor
