データベース構築の実践(患者情報)

4611 ワード

1.患者情報データベース表の作成


create table patient (
	no char(4) not null        primary key,
	name       varchar2(20),
	birth      char(8),
	gender     char(1),
	tel1       char(3),
	tel2       char(4),
	tel3       char(4),
	city       char(2)
 );
create table Lab_test (
	code       char(4) not null        primary key,
	name       varchar2(20)
);
create table result (
	no char(4) not null,
	code char(4) not null,
	sdate date not null,
	status char(1),
	ldate date,
	result     char(1),
	constraint resultPK primary key(no, code, sdate)
);

2.患者情報DBデータの入力




insert into patient values(1001, '김환자', '19850301', 'm', 010, 2222, 0001, 10);
insert into patient values(1002, '이환자', '19900303', 'm', 010, 2222, 0002, 20);
insert into patient values(1003, '박환자', '19770301', 'f', 010, 2222, 0003, 30);
insert into patient values(1004, '조환자', '19650301', 'f', 010, 2222, 0004, 10);
insert into patient values(1005, '황환자', '19400301', 'm', 010, 2222, 0005, 40);
insert into patient values(1006, '양환자', '19440301', 'f', 010, 2222, 0006, 40);
insert into patient values(1007, '허환자', '19760301', 'f', 010, 2222, 0007, 10);
insert into Lab_test values('T001', '결핵');
insert into Lab_test values('T002', '장티푸스');
insert into Lab_test values('T003', '수두');
insert into Lab_test values('T004', '홍역');
insert into Lab_test values('T005', '콜레라');
insert into result values(1001, 'T001', '2020-01-01', 1, '2020-01-02', 'x');
insert into result values(1002, 'T002', '2020-01-01', 2, '2020-01-02', 'p');
insert into result values(1003, 'T003', '2020-01-01', 1, '2020-01-02', 'n');
insert into result values(1004, 'T004', '2020-01-01', 1, '2020-01-02', 'x');
insert into result values(1005, 'T005', '2020-01-01', 2, '2020-01-02', 'n');
insert into result values(1006, 'T001', '2020-01-01', 2, '2020-01-02', 'p');
insert into result values(1007, 'T002', '2020-01-01', 1, '2020-01-02', 'x');
insert into result values(1005, 'T003', '2020-01-01', 1, '2020-01-02', 'p');
insert into result values(1006, 'T004', '2020-01-01', 2, '2020-01-02', 'p');
insert into result values(1007, 'T005', '2020-01-01', 2, '2020-01-02', 'n');

3.患者情報データベースデータの取得


01.検索検査結果Nの患者の名前


-- 1.
select p.name from result r inner join patient p on p.no = r.no where result = 'n';

02.検査状態が2の患者の名前を検索する


-- 2.
select p.name from result r inner join patient p on p.no = r.no where status = 2;

03.検査結果を地域別に行い、地域別検査結果個数を昇順に検索する


-- 3.
select p.city, count(r.no) from result r inner join patient p on p.no = r.no group by p.city order by city asc;

04.検索検査結果Xの患者の全ての情報


--4.
select * from result r inner join patient p on p.no = r.no where r.result = 'x';

05.結核患者の捜索


--5. 
 select p.name from Lab_test l inner join result r on l.code = r.code inner join patient p on p.no = r.no where l.name = '결핵';