/*=======================================*/
/* AZ ELSŐ PÉLDÁKHOZ 5 TÁBLA LÉTREHOZÁSA */
/* ÉS ADATOKKAL VALÓ FELTÖLTÉSE + AZ SQL */        
/*=======================================*/

drop table felv cascade constraints;
drop table elofelt cascade constraints;
drop table targy cascade constraints;
drop table hallg cascade constraints;
drop table tanar cascade constraints;

create table tanar
       (oazon varchar2(8) primary key,
        onev varchar2(20) not null, 
        tanszek varchar2(8));

create table hallg
       (hazon varchar2(8) primary key,
        hnev varchar2(20) not null, 
        varos varchar2(20), 
        szak varchar2(8),
        belepdatum date, 
        odij number(8));

create table targy
       (tkod varchar2(8) primary key,
        tmegnev varchar2(16) not null,
        eavgy varchar2(8), 
        kredit number(8),
        oazon varchar2(8) not null references tanar); 

create table elofelt
       (tkod varchar2(8) not null references targy,
        elotkod varchar2(8) not null references targy);

create table felv
       (hazon varchar2(8) not null references hallg,
        tkod varchar2(8) not null references targy,
        teljdatum date, 
        jegy number(8));



insert into tanar values 
       ('HULELTE', 'Hunyadvári László', 'ALG');
insert into tanar values 
       ('BEAELTE', 'Benczúr András', 'INR');
insert into tanar values 
       ('HOZELTE', 'Horváth Zoltán', 'PNY');
insert into tanar values 
       ('MIGELTE', 'Michaletzky György ', 'VAL');
insert into tanar values 
       ('ZEAELTE', 'Zempléni András', 'VAL');
insert into tanar values 
       ('ARMELTE', 'Arató Miklós', 'VAL');
insert into tanar values  
       ('HACELTE', 'Hajas Csilla', 'INR');
insert into tanar values 
       ('KIAELTE', 'Kiss Attila', 'INR');
insert into tanar values 
       ('NITELTE', 'Nikovits Tibor', 'INR');
insert into tanar values 
       ('VIZELTE', 'Vincellér Zoltán', 'INR');


insert into hallg values 
       ('KOPLAAT', 'Kovács Pál', 'Budapest', 'PTM', TO_DATE('2004.08.21','YYYY.MM.DD'), 10000);
insert into hallg values 
       ('KIPLAAT', 'Kiss Péter', 'Budapest', 'PTM', TO_DATE('2004.08.28','YYYY.MM.DD'), 25000);
insert into hallg values 
       ('KIPKAAT', 'Kiss Pál', 'Eger', 'PTM', TO_DATE('2004.09.04','YYYY.MM.DD'), 10000);
insert into hallg values 
       ('KIPMAAT', 'Kis Panni', 'Debrecen', 'MAT', TO_DATE('2004.09.05','YYYY.MM.DD'), NULL);
insert into hallg values 
       ('BAPLAAT', 'Balogh Péter', 'Debrecen', 'PTM', TO_DATE('2005.08.23','YYYY.MM.DD'), NULL);
insert into hallg values 
       ('BOKLAAT', 'Bodnár Károly', 'Szombathely' ,'PTM', TO_DATE('2004.08.28','YYYY.MM.DD'), NULL);
insert into hallg values 
       ('BOKKAAT', 'Bodó Kata', 'Sopron', 'PTM', TO_DATE('2004.09.04','YYYY.MM.DD'), 30000);
insert into hallg values 
       ('BOKMAAT', 'Bors Kriszta', 'Budapest', 'MAT', TO_DATE('2004.09.05','YYYY.MM.DD'), 25000);


insert into targy values 
       ('AB1EA', 'Adatbázisok1', 'EA', 3, 'KIAELTE');
insert into targy values 
       ('AB2EA', 'Adatbázisok2', 'EA', 3, 'BEAELTE');
insert into targy values 
       ('AB1G1', 'Adatbázisok1', 'GY', 2, 'HACELTE');
insert into targy values 
       ('AB1G2', 'Adatbázisok1', 'GY', 2, 'HACELTE');
insert into targy values 
       ('AB1G3', 'Adatbázisok1', 'GY', 2, 'NITELTE');
insert into targy values 
       ('AB1G4', 'Adatbázisok1', 'GY', 2, 'VIZELTE');
insert into targy values 
       ('AB2G1', 'Adatbázisok1', 'GY', 2, 'HACELTE');
insert into targy values 
       ('AB2G2', 'Adatbázisok1', 'GY', 2, 'HACELTE');
insert into targy values 
       ('AB2G3', 'Adatbázisok1', 'GY', 2, 'NITELTE');
insert into targy values 
       ('AB2G4', 'Adatbázisok1', 'GY', 2, 'VIZELTE');
insert into targy values 
       ('HALG1', 'Hálózatok', 'GY', 2, 'VIZELTE');
insert into targy values 
       ('PRNEA','Progr.nyelvek','EA', 3, 'HOZELTE');
insert into targy values 
       ('VALEA','Valszám', 'EA', 4, 'MIGELTE');
insert into targy values 
       ('VALG1','Valszám', 'GY', 2, 'ARMELTE');
insert into targy values 
       ('VALG2','Valszám', 'GY', 2, 'ZEAELTE');

insert into elofelt values 
       ('AB2EA', 'AB1EA');
insert into elofelt values 
       ('AB2EA', 'PRNEA');
insert into elofelt values 
       ('VALEA', 'PRNEA');

insert into felv values 
       ('KOPLAAT', 'AB1EA', TO_DATE('2005.12.21','YYYY.MM.DD'), 4);
insert into felv values 
       ('KIPLAAT', 'AB1EA', TO_DATE('2005.12.21','YYYY.MM.DD'), 3);
insert into felv values 
       ('KIPKAAT', 'AB1EA', TO_DATE('2005.12.21','YYYY.MM.DD'), 2);
insert into felv values 
       ('KIPMAAT', 'AB1EA', TO_DATE('2005.12.21','YYYY.MM.DD'), 5);
insert into felv values 
       ('BAPLAAT', 'AB1EA', TO_DATE('2005.12.21','YYYY.MM.DD'), 5);
insert into felv values 
       ('BOKLAAT', 'AB1EA', TO_DATE('2005.12.21','YYYY.MM.DD'), 4);
insert into felv values 
       ('BOKKAAT', 'AB1EA', TO_DATE('2005.12.21','YYYY.MM.DD'), 3);
insert into felv values 
       ('BOKMAAT', 'AB1EA', TO_DATE('2005.12.21','YYYY.MM.DD'), 5);
insert into felv values 
       ('KOPLAAT', 'AB2EA', TO_DATE('2006.12.27','YYYY.MM.DD'), 4);
insert into felv values 
       ('KIPLAAT', 'AB2EA', TO_DATE('2006.12.27','YYYY.MM.DD'), 3);
insert into felv values 
       ('KIPKAAT', 'AB2EA', TO_DATE('2006.12.27','YYYY.MM.DD'), 2);
insert into felv values 
       ('KIPMAAT', 'AB2EA', TO_DATE('2006.12.27','YYYY.MM.DD'), 4);
insert into felv values 
       ('BAPLAAT', 'AB2EA', TO_DATE('2006.12.27','YYYY.MM.DD'), 5);
insert into felv values 
       ('BOKLAAT', 'AB2EA', TO_DATE('2006.12.27','YYYY.MM.DD'), 2);
insert into felv values 
       ('BOKKAAT', 'AB2EA', TO_DATE('2006.12.27','YYYY.MM.DD'), NULL);
insert into felv values 
       ('BOKMAAT', 'AB2EA', TO_DATE('2006.12.27','YYYY.MM.DD'),NULL);
insert into felv values 
       ('BOKLAAT', 'AB1G4', TO_DATE('2005.12.15','YYYY.MM.DD'), 2);
insert into felv values 
       ('BOKLAAT', 'AB2G4', TO_DATE('2006.12.18','YYYY.MM.DD'), 2);
insert into felv values 
       ('BOKLAAT', 'HALG1', TO_DATE('2006.12.18','YYYY.MM.DD'), 2);
insert into felv values 
       ('BAPLAAT', 'AB1G3', TO_DATE('2005.12.15','YYYY.MM.DD'), 5);
insert into felv values 
       ('BAPLAAT', 'AB2G3', TO_DATE('2006.12.18','YYYY.MM.DD'), 5);
insert into felv values 
       ('BOKKAAT', 'AB1G1', TO_DATE('2005.12.15','YYYY.MM.DD'), 4);
insert into felv values 
       ('BOKKAAT', 'AB2G1', TO_DATE('2006.12.18','YYYY.MM.DD'), 3);

grant select on tanar to public;
grant select on hallg to public;
grant select on targy to public;
grant select on elofelt to public;
grant select on felv to public;
commit;

describe tanar;
describe hallg;
describe targy;
describe elofelt;
describe felv;

select * from tanar order by oazon;
select * from hallg order by hazon;
select * from targy order by oazon;
select * from elofelt order by tkod;
select * from felv order by hazon;