drop table if exists imprumuturi;
drop table if exists carti;
drop table if exists cititori;
create table carti
(
    codcarte number primary key,
    titlu varchar2(50),
    autor varchar2(50),
    editura varchar2(50),
    pret number(8,2),
    anul_aparitiei number,
    categorie varchar2(50),
    tip_format varchar2(50)
);
create table cititori
(
    codcititor number primary key,
    nume varchar2(45),
    prenume varchar2(60),
    adresa varchar2(100),
    localitate varchar2(50),
    email varchar2(60),
    telefon char(11),
    data_nastere date,
    statut varchar2(50)
);
create table imprumuturi
(
    codimprumut number primary key,
    codcarte number references carti(codcarte) on delete cascade,
    codcititor number references cititori(codcititor) on delete cascade,
    data_imprumut date,
    data_returnare date,
    cantitate number
);
insert all
into carti values(1,'Moara cu noroc','Ioan Slavici','Humanitas',45.50,2003,'nuvela','hartie')
into carti values(2,'Amintiri din copilarie','Ion Creanga','Corint',32.25,2005,'roman autobiografic','e-books')
into carti values(3,'Morometii','Marin Preda','Humanitas',50,2002,'roman','e-books')
into carti values(4,'Plumb','George Bacovia','All',85,2003,'poezie','audiobooks')
into carti values(5,'La vulturi','Gala Galaction','Corint',72,2010,'nuvela','hartie')
into cititori values(1,'Popescu','Dragos','Strada 1','Tecuci','popdragos@gmail.com', '12345','10.23.2008','elev')
into cititori values(2,'Visan','Maria','Strada Sperantei nr.3','Bucuresti','mariavisan@yahoo.com', '12346','02.02.1960','pensionar')
into cititori values(3,'Irimia','Sandita','Strada 2','Galati','sandirimia@gmail.com', '12347','05.03.1980','angajat')
into cititori values(4,'Posteuca','Mirel','Strada 3','Tecuci','posteucam@yahoo.com', '12348','04.27.1989','angajat')
into cititori values(5,'Popescu','Maria','Strada 4','Focsani','popmaria@gmail.com', '12349','11.27.2008','elev')
into imprumuturi(codimprumut,codcarte,codcititor,data_imprumut,cantitate) values(100,1,1,'08.30.2025',3)
into imprumuturi(codimprumut,codcarte,codcititor,data_imprumut,cantitate) values(101,1,5,'11.05.2024',1)
into imprumuturi(codimprumut,codcarte,codcititor,data_imprumut,cantitate) values(102,3,3,'04.15.2025',2)
into imprumuturi(codimprumut,codcarte,codcititor,data_imprumut,cantitate) values(103,2,4,'05.20.2026',3)
into imprumuturi(codimprumut,codcarte,codcititor,data_imprumut,cantitate) values(104,1,2,'08.03.2026',2)
into imprumuturi(codimprumut,codcarte,codcititor,data_imprumut,cantitate) values(105,5,3,'11.10.2024',1)
into imprumuturi(codimprumut,codcarte,codcititor,data_imprumut,cantitate) values(106,4,5,'12.28.2026',4)
into imprumuturi(codimprumut,codcarte,codcititor,data_imprumut,cantitate) values(107,2,2,'01.03.2026',2)
into imprumuturi(codimprumut,codcarte,codcititor,data_imprumut,cantitate) values(108,4,3,'09.23.2024',1)
into imprumuturi(codimprumut,codcarte,codcititor,data_imprumut,cantitate) values(109,5,2,'05.15.2025',1)
select * from dual;
update imprumuturi set data_returnare=add_months(data_imprumut,2) where codimprumut in (100,102,104,106,108);
update imprumuturi set data_returnare=add_months(data_imprumut,1) where codimprumut in (101,102,105,107,109);