-- data si ulozime do samostatneho schematu ordinace

-- pro pripad, ze tam uz to schema je
-- drop schema ordinace cascade;

create schema ordinace;

set search_path to ordinace;

-- pro pripad, ze tabulky uz existuji
--drop table lek cascade;
--drop table pac cascade;
--drop table nav cascade;

create table lek(
   idl integer,
   jml varchar(30),
   spe varchar(30),
   constraint PK_lek primary key (idl));

create table pac (
   idp integer,
   jmp varchar(30),
   adp varchar(30),
   tep integer,
   dnp date,
   constraint PK_pac primary key (idp));

create table nav (
   idl integer,
   idp integer,
   dna date,
   dia varchar(20),
   typ varchar(5),
   cen integer,
   constraint PK_nav primary key (idl,idp,dna),
   constraint FK_nav_lek foreign key (idl)
       references lek(idl),
   constraint FK_nav_pac foreign key (idp)
       references pac(idp)); 


insert into lek values (1,'King','orthoped');
insert into lek values (3,'Jones','dentist');
insert into lek values (5,'Ford','internist');
insert into lek values (2,'Smith','internist');

insert into lek values (6,'Blake','orthoped');
insert into lek values (4,'Simon','face-lifter');

insert into pac values (3,'ALLEN','Wall Street 112',123456,'20-FEB-1942');                          
insert into pac values (2,'WARD','Baker Street 62',654321,'22-FEB-1925');                           
insert into pac values (4,'MARTIN','Butcher Street 77',456789,'28-SEP-1919');                         
insert into pac values (8,'SCOTT','Picadili 12',987654,'09-DEC-1936');                          
insert into pac values (7,'TURNER','Wall Street 56',123789,'08-SEP-1989');                         
insert into pac values (6,'ADAMS','Baker Street 47',987321,'12-JAN-1983');                          
insert into pac values (5,'JAMES','Picadili 56',963852,'03-DEC-1971');                          
insert into pac values (1,'CLARK','Wall Street 56',852741,'09-JUN-1963');                          
insert into pac values (9,'MILLER','Wall Street 112',147258,'23-JAN-1948');                         

--alter session set nls_date_format='DD-MON-RR';
-- naplnime vztahovou tabulku derivovanymi daty 
Insert Into nav
       Select idl,idp, current_date - (idl+idp),
              'D'||(idl+idp), 
              case mod(idl+idp,3) 
                when 0 then NULL
                else chr(ascii('A')+idl+idp)
              end,
              case when mod(idp+idl,5)=0 then NULL
                   else idl*10+idp
              end
      From pac join lek on (mod(idp+idl,2) = 0);

-- a jeste nejaka data explicitne

insert into nav values (1,1,'18-JAN-97','D2','H',52);
insert into nav values (2,1,'8-JAN-96','D2','H',52);
insert into nav values (3,1,'1-JAN-93','D1','H',2);
insert into nav values (4,1,'18-JAN-93','D2','H',5);
insert into nav values (5,1,'18-JAN-97','D2','H',52);
insert into nav values (1,7,'1-JAN-90','D2','H',12);
insert into nav values (1,9,'1-JAN-99','D2','H',15);
insert into nav values (1,3,'23-FEB-89','D3','O',45);
insert into nav values (3,3,'01-JUN-89','D1',NULL,NULL);
insert into nav values (3,9,'04-NOV-99','D1','O',NULL);
insert into nav values (3,5,'03-JAN-87','D2','H',NULL);
insert into nav values (1,1,'09-MAR-96','D4','O',NULL);
insert into nav values (4,4,'23-DEC-87','D3','H',85);
insert into nav values (4,6,'30-JAN-88','D2','H',89);
insert into nav values (5,7,'14-OCT-96','D4','O',63);
insert into nav values (5,4,'23-OCT-99','D3','O',78);
insert into nav values (3,5,'12-JUN-95','D2','H',54);
insert into nav values (4,4,'23-JAN-98','D1',NULL,NULL);
insert into nav values (5,5,'15-DEC-87','D2','H',65);
insert into nav values (1,9,'13-NOV-94','D1','O',45);
insert into nav values (1,7,'24-MAR-99','D1','O',23);
insert into nav values (2,2,'26-MAR-99','D1','O',23);
insert into nav values (2,1,'27-JAN-99','D2','O',28);
insert into nav values (2,4,'24-MAR-97','D1','H',99);

-- lekar 7 a pacient 5 nemaji zadne navstevy
Delete From nav 
where idp=7 or idl=5;

commit;
Select count(*) from pac;
Select count(*) from lek;
Select count(*) from nav;