1. 환자정보 DB 테이블 생성

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. 환자정보 DB 데이터 검색

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 = '결핵';