TP5
/* Autocommit ON */
set autocommit on;
/* Cr ez une table ECRIVAIN comme une copie du contenu de la table [Link]
que vous avez utilis e lors des TP pr c dent. */
drop table ecrivain cascade constraints; -- par pr caution, si jamais
select *
from [Link];
create table ecrivain
as
select idecr, enom, pnom, sexe, date_n, pays_n
from [Link];
/* V rifiez que votre propre table a une cl primaire. Si ce n'est pas le cas, cr ez la. */
-- la table n'a pas de cl primaire
ALTER TABLE ECRIVAIN
add CONSTRAINT PK_ECRIVAIN PRIMARY KEY(IDECR);
-- on voit la cl dans la repr sentation graphique
/* Si votre auteur favori n'est pas dans la table, faites une insertion. */
DESC ECRIVAIN;
INSERT INTO ECRIVAIN
VALUES (8686, 'Tokarczuk', 'Olga', 'F', to_date('29/01/1962', 'dd/mm/yyyy'), 'Pologne');
/* V rifiez que le contenu de la table vous convient (une requ te SELECT). */
select *
from ecrivain;
-- le nom apparait bien
/* Cr ez une table LECTEUR. Importer des noms des lecteurs depuis le fichier votre
disposition.
*/
-- pour l'importation : souris sur l'icone Tables, choix importation
-- choisir ; comme s parateur et UTF-8 comme encodage
-- on v rifie
select *
from lecteur;
-- si le nom est mauvais (c' tait mon cas) on le change
alter table lecetur RENAME to lecteur;
-- le select * fonctionne
/* Ins rez deux autres enregistrements dans la table LECTEUR. V rifiez que le contenu
de votre table LECTEUR est correct. */
desc lecteur;
insert into lecteur (idl, prenom) values (90, 'Michel');
insert into lecteur values (100, 'Mireille', NULL, 'dba');
-- les deux syntaxes possibles
select *
from lecteur;
/* Cr ez la table LIVRE. */
CREATE TABLE LIVRE
(idlivre numeric(5) primary key,
titre varchar2(40),
annee_publi numeric(4) default 2024,
ecr_id numeric(4) references ecrivain(idecr));
/* V rifiez qu'elle correspond bien au mod le propos (cl primaire et ventuelle cl s
trang res). */
-- je regarde le mod le graphique
/* Ins rez une bonne demi-douzaine de livres. */
insert into livre values (2000, 'Sur les ossement des morts', 2009, 8686);
insert into livre values(2010, 'Histoires bizarro des', 2018, 8686);
insert into livre values(2015, 'Les Livres de Jak b', 2018, 8686);
insert into livre (idlivre, titre, ecr_id) values (2020, 'L''Impossible Retour', 8127);
insert into livre values(2030, 'Stupeurs et tremblements', 2018, 8127);
insert into livre values(2040, 'M taphysique des tubes', 2000, 8127);
insert into livre values(2050, 'Premier Sang', 2021, 8127);
/* Tentez d'ins rer un livre avec une mauvaise cl trang re. Tentez d'ins rer deux fois un
livre. Commentez les r sultats. */
insert into livre values(2050, 'Premier Sang', 2021, 8127);
--violation de contrainte unique
insert into livre values(2060, 'Premier Sang', 2021, 8000);
--violation de contrainte d'int grit , cl parent introuvable
/* Faites les n cessaire pour que les lectures et les recommandations puisse tre
g r es. :*/
-- les recommandation : ajout d'une colonne la table LIVRE avec le id du lectueur qui
fait la recommandation
ALTER TABLE LIVRE ADD
(REC_ID_LECTEUR numeric(4) REFERENCES LECTEUR(idl));
-- erreur
-- table lecteur sans cl
alter table lecteur add constraint pk_lecteur primary key (idl);
ALTER TABLE LIVRE ADD
(REC_ID_LECTEUR numeric(4) REFERENCES LECTEUR(idl));
-- c'est OK
-- la relation de lecture se traduit par une table
CREATE TABLE LECTURE
(lecteur_id numeric(4) references lecteur(idl),
livre_id numeric(4) references livre(idlivre),
constraint pk_lecture primary key (lecteur_id, livre_id));
/* Peuplez votre bases de donn es selon votre imagination en ayant au
moins quatre livres distincts lus par divers lecteurs,
au moins un lecteur ayant lu au moins trois livres,
au moins deux livres avec des recommandations. */
-- un livre avec beaucoup de lecteurs
insert into lecture values (90, 2050);
insert into lecture values (80, 2050);
insert into lecture values (70, 2050);
insert into lecture values (60, 2050);
-- un lecteur qui lit beacoup
insert into lecture values (90, 2040);
insert into lecture values (90, 2030);
insert into lecture values (90, 2020);
insert into lecture values (90, 2010);
insert into lecture values (90, 2000);
-- un autre lecteur qui lit beaucoup
insert into lecture values (100, 2040);
insert into lecture values (100, 2030);
insert into lecture values (100, 2020);
insert into lecture values (100, 2010);
insert into lecture values (100, 2000);
-- qq recommandations
-- on doit faire un UPDATE
update livre
set REC_ID_LECTEUR = 100
where idlivre in (2000, 2010, 2015);
-- autre lectures
insert into lecture values (60, 2020);
insert into lecture values (70, 2015);
insert into lecture values (40, 2000);
-- recommandation
update livre
set rec_id_lecteur = 40
where idlivre = 2000;
update livre
set rec_id_lecteur = 90
where idlivre in (2010, 2020, 2030);
/** partie 3 **/
/* Ahichez la liste des livres avec les auteurs, le nombre total de lectures et,
ventuellement, le nom de celui qui a fait la recommandation */
select [Link] as Titre, [Link] || ' ' || upper([Link]) as Auteur,
[Link] as Recommand , count(lct.lecteur_id) as Nombre_lecteurs
from livre lv, ecrivain e, lecteur le, lecture lct
where lv.ecr_id = [Link]
and [Link] (+) = lv.rec_id_lecteur
and lct.livre_id(+) = [Link]
group by [Link], [Link] || ' ' || upper([Link]), [Link];
/* Ahichez les livres qui ne sont pas encore lus. */
select [Link] as Titre, [Link] || ' ' || upper([Link]) as Auteur
from livre lv, ecrivain e
where lv.ecr_id = [Link]
and [Link] not in (select livre_id from lecture);
--- la requ te ne retourne rien.
select count(*)
from livre;
select count(distinct livre_id)
from lecture;
-- tous les livres sont lus
-- on en rajoute un
insert into livre values(2060, 'Journal d''une hirondelle', 2006, 8127, NULL);
-- OK la requ te fonctionne
/* Ahichez le lecteur le plus assidu. */
-- la liste de lecteurs avec le nombre de livres lus
select [Link], count(lt.livre_id)
from lecteur l, lecture lt
where [Link] = lt.lecteur_id
group by [Link];
select [Link]
from lecteur l
where idl in (select lecteur_id
from lecture
group by lecteur_id
having count(livre_id) >=
all (select count(livre_id) from lecture group by lecteur_id));
/* Ahichez le lecteur ayant fait le plus de recommandations. */
select [Link]
from lecteur l
where idl in (select rec_id_lecteur
from livre
group by rec_id_lecteur
having count(idlivre) >=
all (select count(rec_id_lecteur) from livre group by rec_id_lecteur));
-- celle-ci est horrible
/* Ahichez les lecteurs ayant eu les m mes lectures, en exceptant les lecteurs qui n'ont
rien lu. */
-- je fabrique un couple de lecteur qui ont lu les m me deux livres, histoire de voir si j'ai
-- des bons r sultats
select [Link], [Link]
from lecteur l
where idl not in (select lecteur_id from lecture);
insert into lecture values(10, 2010);
insert into lecture values(10, 2015);
insert into lecture values(10, 2020);
insert into lecture values(50, 2010);
insert into lecture values(50, 2015);
insert into lecture values(50, 2020);
-- on voit les livres d'un lecteur 'toto' (Mireille, Paul et les autres pour v rifier que tout est
OK
select [Link]
from livre lv, lecture lt, lecteur l
where [Link] = lt.livre_id
and lt.lecteur_id = [Link]
and [Link] = 'Mireille';
-- l'"attroce chose"
-- on calcule le nombre de livres lus par chacun et le nombre de livres lus en commun et
on teste (avec having) si on tombe sur les m mes valeurs
select distinct [Link], [Link], [Link] as LUs_par_1, [Link] as LUs_par_2,
count(lt1.livre_id) as LUs_en_commun
from lecteur l1, lecteur l2, lecture lt1, lecture lt2,
(select count(*) cc , lecteur_id from lecture group by lecteur_id) T1,
(select count(*) cc , lecteur_id from lecture group by lecteur_id) T2
where [Link] < [Link]
and [Link] = lt1.lecteur_id
and lt1.livre_id = lt2.livre_id
and [Link] = lt2.lecteur_id
and [Link] = T1.lecteur_id
and [Link] = T2.lecteur_id
group by [Link], [Link], [Link], [Link]
having
count(lt1.livre_id) = [Link]
and count(lt2.livre_id) = [Link];
-- pas besoin de test les compteurs not ( ... = 0).