CREATE TABLE kniha (
begin
  for i in (select 'drop table '||table_name||' cascade constraints' tbl from user_tables) 
  loop
     execute immediate i.tbl;
  end loop;
end;












id INTEGER NOT NULL,
nazev VARCHAR2(30 CHAR) NOT NULL
)
LOGGING;

ALTER TABLE kniha ADD CONSTRAINT kniha_pk PRIMARY KEY ( id );

CREATE TABLE vypujcka (
datum DATE NOT NULL,
kniha_id INTEGER NOT NULL,
zakaznik_id INTEGER NOT NULL
)
LOGGING;

ALTER TABLE vypujcka
ADD CONSTRAINT vypujcka_pk PRIMARY KEY ( kniha_id,zakaznik_id,datum );

CREATE TABLE zakaznik (
id INTEGER NOT NULL,
jmeno VARCHAR2(30 CHAR) NOT NULL,
vek integer
)
LOGGING;

ALTER TABLE zakaznik ADD CONSTRAINT zakaznik_pk PRIMARY KEY ( id );

ALTER TABLE vypujcka
ADD CONSTRAINT vypujcka_kniha_fk FOREIGN KEY ( kniha_id )
REFERENCES kniha ( id )
NOT DEFERRABLE;

ALTER TABLE vypujcka
ADD CONSTRAINT vypujcka_zakaznik_fk FOREIGN KEY ( zakaznik_id )
REFERENCES zakaznik ( id )
NOT DEFERRABLE;






alter session set nls_date_format = 'DD.MM.YYYY'; 

insert into zakaznik (id, jmeno, vek) VALUES (0, 'Karel', null);
insert into zakaznik (id, jmeno, vek) VALUES (1, 'Jan', 30);
insert into zakaznik (id, jmeno, vek) VALUES (2, 'Pepa', 20);

insert into kniha (id, nazev) VALUES (0, 'Temno');
insert into kniha (id, nazev) VALUES (1, 'Svejk');
insert into kniha (id, nazev) VALUES (2, 'Svetlo');

insert into vypujcka (datum, kniha_id, zakaznik_id) VALUES ('01.01.2025', 0, 1);
insert into vypujcka (datum, kniha_id, zakaznik_id) VALUES ('02.01.2025', 2, 0);
insert into vypujcka (datum, kniha_id, zakaznik_id) VALUES ('02.01.2025', 1, 2);
insert into vypujcka (datum, kniha_id, zakaznik_id) VALUES ('04.01.2025', 2, 2);

update zakaznik set jmeno = 'Jakub' where jmeno = 'Jan';

select * from zakaznik;

delete from zakaznik where id = 1;

drop table vypujcka;