THnjNFB6

1SET search_path TO 'TPNOTE';
2
3DROP TABLE IF EXISTS Lecture;
4DROP TABLE IF EXISTS Possède;
5DROP TABLE IF EXISTS Critique;
6DROP TABLE IF EXISTS Utilisateur;
7DROP TABLE IF EXISTS Livre;
8
9CREATE TABLE Livre (
10idLivre int NOT NULL PRIMARY KEY,
11isbn text NOT NULL UNIQUE,
12titre text NOT NULL,
13auteur text NOT NULL);
14
15INSERT INTO Livre VALUES
16(1, 'abcd', 'Peter Pan', 'Nicolas'),
17(2, 'efgh', 'Pinocchio', 'Henri'),
18(3, 'ijkl', 'Zelda', 'Thomas'),
19(4, 'mnop', 'A ne pas lire', 'Ingrid'),
20(5, 'qrst', 'A ne voir', 'Ingrid');
21
22CREATE TABLE Utilisateur (
23login text NOT NULL PRIMARY KEY,
24mdp text NOT NULL,
25email text NOT NULL UNIQUE CHECK (email LIKE '%@%'));
26
27INSERT INTO Utilisateur VALUES
28('Mario', 'mushroom', 'Mario@hotmail.fr'),
29('Peach', 'princesse', 'Peach@hotmail.fr'),
30('Link', 'mastersword', 'Link@hotmail.fr');
31
32CREATE TABLE Critique (
33idLivre int NOT NULL REFERENCES Livre(idLivre),
34login text NOT NULL REFERENCES Utilisateur(login),
35note int NOT NULL,
36texte text NOT NULL CHECK (LENGTH(texte)>=10),
37PRIMARY KEY (idLivre, login));
38
39INSERT INTO Critique VALUES
40(3, 'Link', 10, 'j adore le hero'),
41(3, 'Mario', 6, 'je n aime pas beaucoup la princesse'),
42(1, 'Peach', 7, 'se lit bien'),
43(1, 'Mario', 8, 'le hero est cool'),
44(2, 'Peach', 4, 'decevant comme livre');
45
46CREATE TABLE Possède (
47idLivre int NOT NULL REFERENCES Livre(idLivre),
48login text NOT NULL REFERENCES Utilisateur(login),
49PRIMARY KEY (idLivre, login));
50
51INSERT INTO Possède VALUES
52(1, 'Mario'),
53(3, 'Mario'),
54(1, 'Peach'),
55(2, 'Peach'),
56(3, 'Link');
57
58CREATE TABLE Lecture (
59idLivre int NOT NULL REFERENCES Livre(idLivre),                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
60login text NOT NULL REFERENCES Utilisateur(login),
61idLecture int NOT NULL,
62dateLecture date NOT NULL,
63PRIMARY KEY (idLivre, login, idLecture));
64
65INSERT INTO Lecture VALUES 
66(1, 'Mario', 1, '2016-4-1'),
67(3, 'Mario', 2, '2015-10-1'),
68(1, 'Peach', 3, '2016-9-4'),
69(2, 'Peach', 4, '2014-1-9'),
70(3, 'Link', 5, '2016-4-20'),
71(1, 'Mario', 6, '2016-12-1'),
72(2, 'Peach', 7, '2014-12-20');
73
74SET search_path TO 'TPNOTE';
75
76/*
774.a
78
79SELECT DISTINCT idLivre, titre, auteur FROM Livre
80JOIN Lecture USING (idLivre)
81WHERE login='Mario';
82*/
83
84/*
854.b
86
87SELECT DISTINCT titre FROM Livre
88JOIN Lecture USING (idLivre)
89WHERE login='Mario'
90AND dateLecture >= '2016-1-1'
91AND dateLecture < '2017-1-1';
92*/
93
94/*
954.c
96
97SELECT idLivre, titre, avg(note) AS MoyenneLivre FROM Livre
98JOIN Critique USING (idLivre)
99GROUP BY idLivre
100order by MoyenneLivre DESC;
101*/
102
103/*
1044.d
105
106SELECT auteur, count(*) AS Nblecture FROM Livre
107JOIN Lecture USING (idLivre)
108GROUP BY idLivre;
109*/
110
111/*
1124.e
113
114SELECT idLivre, titre FROM Livre
115JOIN Lecture USING (idLivre)
116WHERE dateLecture >= '2014-1-1'
117AND dateLecture < '2015-1-1'
118GROUP BY idLivre
119HAVING count(*)>=2
120*/
121
122/*
1234.f
124
125SELECT idLivre, titre FROM Livre
126WHERE idLivre NOT IN ( select idLivre from Lecture)
127*/