drop table emprunter;
drop table abonne;
drop table exemplaire;
drop table ecrire;
drop table ouvrage;
drop table auteur;

create table auteur
(numauteur int primary key,
prenomauteur varchar(128) not null,
nomauteur varchar(128) not null
);

create table ouvrage
(numouvrage int primary key,
nomouvrage varchar(128) not null
);

create table ecrire
(numauteur int,
numouvrage int,
primary key (numauteur, numouvrage),
foreign key (numauteur) references auteur (numauteur),
foreign key (numouvrage) references ouvrage (numouvrage)
);

create table exemplaire
(numouvrage int,
numexemplaire int,
empruntable boolean not null default true,
foreign key (numouvrage) references ouvrage(numouvrage),
primary key (numouvrage, numexemplaire)
);

create table abonne
(numabonne int primary key,
prenomabonne varchar(64) not null,
nomabonne varchar(64) not null,
daterenouvellement date not null
);

create table emprunter
(numabonne int,
numouvrage int,
numexemplaire int,
dateemprunt date not null, 
dateretour date null,
primary key (numabonne, numouvrage, numexemplaire, dateemprunt),
foreign key (numabonne) references abonne (numabonne),
foreign key (numouvrage, numexemplaire) references exemplaire (numouvrage, numexemplaire)
);

insert into auteur values (1, 'Amedée', 'Morflegroin');
insert into auteur values (2, 'Gisèle', 'Glamouillard');
insert into auteur values (3, 'Oscar', 'Wilde');

insert into ouvrage values (1, 'L\'art des fléchettes');/*'*/
insert into ouvrage values (2, 'les vertus du pastis');
insert into ouvrage values (3, 'Le portrait de Dorian Gray');
insert into ouvrage values (4, 'Ouvrage épuisé...');
insert into ouvrage values (5, 'Tous en circulation');

insert into ecrire values (1, 1);
insert into ecrire values (1, 2);
insert into ecrire values (2, 1);
insert into ecrire values (3, 3);

insert into exemplaire (numouvrage, numexemplaire) values (1, 1);
insert into exemplaire (numouvrage, numexemplaire) values (1, 2);
insert into exemplaire values (1, 3, false);
insert into exemplaire (numouvrage, numexemplaire) values (1, 4);
insert into exemplaire (numouvrage, numexemplaire) values (2, 1);
insert into exemplaire (numouvrage, numexemplaire) values (2, 2);
insert into exemplaire (numouvrage, numexemplaire) values (2, 3);
insert into exemplaire (numouvrage, numexemplaire) values (3, 1);
insert into exemplaire (numouvrage, numexemplaire) values (3, 2);
insert into exemplaire (numouvrage, numexemplaire) values (3, 3);
insert into exemplaire (numouvrage, numexemplaire) values (3, 4);
insert into exemplaire (numouvrage, numexemplaire) values (3, 5);
insert into exemplaire (numouvrage, numexemplaire) values (4, 1);
insert into exemplaire (numouvrage, numexemplaire) values (5, 1);

insert into abonne values (1, 'Gérard', 'Bouchard', '2010-03-28');
insert into abonne values (2, 'Marcel', 'Le Ballon', '2012-01-01');
insert into abonne values (3, 'Ursule', 'Cochonnet', '2009-07-01');

insert into emprunter values (3, 1, 1, '2009-08-23', '2009-09-11');
insert into emprunter values (3, 1, 1, '2009-09-11', '2009-09-20');
insert into emprunter values (1, 3, 5, '2011-03-25', NULL);
insert into emprunter values (1, 1, 4, '2011-03-25', NULL);
insert into emprunter values (2, 1, 1, '2009-08-23', '2009-09-01');
insert into emprunter values (2, 1, 2, '2009-09-01', '2009-09-14');
insert into emprunter values (2, 1, 3, '2009-09-12', NULL);
insert into emprunter values (3, 5, 1, '2011-03-12', NULL);

/* a */

drop view abonnes_plus_a_jour;

create view abonnes_plus_a_jour as
select * 
from abonne
where date_add(daterenouvellement, interval 365 day) <  now();

select * from abonnes_plus_a_jour;

/* b */


drop view ouvrages_en_circulation;

create view ouvrages_en_circulation as
select o.numouvrage, nomouvrage, numexemplaire
from ouvrage o, emprunter e
where o.numouvrage = e.numouvrage
and dateretour is null;

select * from ouvrages_en_circulation;

/* c */

drop view ouvrages_en_retard;

create view ouvrages_en_retard as
select e.numouvrage, o.nomouvrage, e.numexemplaire, e.numabonne
from emprunter e, ouvrage o
where e.numouvrage = o.numouvrage
and datediff(now(), dateemprunt) >=  15
and dateretour is null;

select * from ouvrages_en_retard;

/* d */

drop view ouvrages_disponibles;

create view ouvrages_disponibles as
select numouvrage, nomouvrage
from ouvrage o
where (select count(*)
      from exemplaire e
      where e.numouvrage = o.numouvrage
      and empruntable
      ) >
      (
      select count(*)
      from emprunter e
      where e.numouvrage = o.numouvrage
      and dateretour is not null
      );

select * from ouvrages_disponibles;

/* e */

drop view ouvrages_epuises;

create view ouvrages_epuises as
select o.numouvrage, o.nomouvrage
from ouvrage o
where 
      (select count(*)
      from exemplaire e
      where o.numouvrage = e.numouvrage
      ) = 0;

select * from ouvrages_epuises;

/* f */

drop view ouvrages_tous_en_circulation;

create view ouvrages_tous_en_circulation as
select * 
from ouvrage o
where
	(select count(*)
	from exemplaire e
	where e.numouvrage = o.numouvrage
	)
	=
	(select count(*)
	from emprunter e
	where e.numouvrage = o.numouvrage
	and dateretour is null
	);

select * from ouvrages_tous_en_circulation;

/* g */

drop view nb_emprunts_par_ouvrage;

create view nb_emprunts_par_ouvrage as
select numouvrage, 
       (select count(*)
       from emprunter e
       where o.numouvrage = e.numouvrage       
       ) as nb_emprunts
from ouvrage o;

select * from  nb_emprunts_par_ouvrage;

/* h */

drop view abonnes_pouvant_emprunter;

create view abonnes_pouvant_emprunter as
select numabonne, prenomabonne, nomabonne
from abonne a
where numabonne not in
      (
      select numabonne
      from abonnes_plus_a_jour
      )
      and
      (
      select count(*)
      from emprunter e
      where e.numabonne = a.numabonne
      ) < 5;

select * from abonnes_pouvant_emprunter;

/* i */

drop view nb_ouvrages_circ_par_abonne;

create view nb_ouvrages_circ_par_abonne as
select a.numabonne, 
       (
       select count(*)
       from emprunter e
       where e.numabonne = a.numabonne
       and dateretour is null
       ) as nb_ouvrages
from abonne a;

select * from nb_ouvrages_circ_par_abonne;
