/*********************************************/
/* Création des tables de la base de données */
/*********************************************/

CREATE TABLE membre (
	id SERIAL PRIMARY KEY,
	nom VARCHAR,
	prenom VARCHAR,
	adresse VARCHAR,
	tel VARCHAR(10),
	email VARCHAR );

CREATE TABLE fonction (
	id SERIAL PRIMARY KEY,
	nom VARCHAR(15) );

CREATE TABLE fonction_membre (
	id_membre INTEGER REFERENCES membre,
	id_fonction INTEGER REFERENCES fonction );

CREATE TABLE compte (
	id SERIAL PRIMARY KEY,
	solde FLOAT,
	id_membre INTEGER REFERENCES membre);

CREATE TABLE avion (
	id SERIAL PRIMARY KEY,
	type VARCHAR(10),
	nom VARCHAR(6) );

CREATE TABLE type_resa (
	id SERIAL PRIMARY KEY,
	nom VARCHAR(10) );

CREATE TABLE reservation (
	id SERIAL PRIMARY KEY,
	dateDebut TIMESTAMP,
	dateFin TIMESTAMP,
	duree INTEGER,
	id_membre INTEGER REFERENCES membre,
	id_avion INTEGER REFERENCES avion,
	id_type INTEGER REFERENCES type_resa,
	instructeur VARCHAR(3) );

/************************************/
/* Peuplement de la base de données */
/************************************/

INSERT INTO membre (nom, prenom, adresse, tel, email) -- peuplement table 'membre'
	VALUES ('Ballereau', 'Hervé', 'La Tranche sur Mer', '0251274171', 'herve.ballereau@online.fr'),
	('Berges', 'Olivier', 'Paris', '0147836691', 'olivier_berges@hotmail.fr'),
	('Bichon', 'Jean-Paul', 'Luçon', '0251279101', 'jpbflg@aol.com'),
	('Breniaux', 'Alex', 'Mareuil-sur-Lay', '0699070175', 'alex.breniaux@laposte.net'),
	('Cadou', 'Vincent', 'Luçon', '0251561386', 'vincentcadou@yahoo.fr'),
	('Chevalier', 'Joël', 'Mareuil-sur-Lay', '0251568439', 'chevallierjoe@wanadoo.fr'),
	('Faucher', 'Bastien', 'La Rochelle', '0251563654', 'guy-marie.faucher@wanadoo.fr'),
	('Guéneau', 'Matthieu', 'Longeville sur Mer', '0251203000', 'mgueneau@yahoo.fr'),
	('Lumet', 'Édouard', 'La Rochelle', '0611868076', 'edouard.lumet85@gmail.com'),
	('Léoni', 'Philippe', 'Luçon', '0251560690', 'philippe.leo@voila.fr'),
	('Michaud', 'Mélissa', 'Nantes', '0244396101', 'melissa.michaud34@gmail.com'),
	('Michel', 'Jean-Yves', 'Luçon', '0251276855', 'michel.jeanyves@sfr.fr'),
	('Mignon', 'Louis', 'Luçon', '0251569682', 'mignon.louis10@hotmail.fr'),
	('Neau', 'Bénédicte', 'Nancy', '0321610487', 'benedicte.neau@laposte.net'),
	('Pelletreau', 'Fabrice', 'Mareuil-sur-Lay', '0251973130', 'fabrice@pelletreauelectricite.com'),
	('Percot', 'Frank', 'Les Magnils', '0251561564', 'franck18janv@hotmail.com'),
	('Perrier', 'Pierre-Guy', 'Luçon', '0674895279', 'perrier.lucon@wanadoo.fr'),
	('Prouteau', 'Jean-Noël', 'Fontenay-le-Comte', '0251971615', 'jnprouteau@orange.fr'),
	('Rouillé', 'Philippe', 'Luçon', '0689736969', 'philippe.rouille@ymail.com'),
	('Ulens', 'Samuel', 'Luçon', '0244391472', 'samuel-ulens@hotmail.com'),
	('Bogaert', 'Michel', 'Luçon', '0661762531', 'bogaert85400@aol.com'),
	('Comtet', 'François', 'Paris', '0146420333', 'francois.comtet2@wanadoo.fr'),
	('Faucher', 'Guy-Marie', 'Luçon', '0251563654', 'guy-marie.faucher@wanadoo.fr'),
	('Louineau', 'Lionel', 'La Roche sur Yon', '0251562213', 'llouineau@wanadoo.fr'),
	('Richardson', 'Brian', 'Southampton', '+441403790', 'brianr5@mac.com'),
	('Roseblade', 'David', 'Chasnais', '0251290278', NULL),
	('Cabot', 'Bruno', 'Luçon', '0251278992', 'cathy.berland@free.fr');

INSERT INTO fonction (nom) -- peuplement table 'fonction'
	VALUES ('pilote'), ('élève'), ('sympathisant'), ('trésorier'),
	('instructeur'), ('président'), ('secrétaire'),	('maintenance');

INSERT INTO fonction_membre (id_membre, id_fonction) -- peuplement table d'association
	VALUES (1, 1), (1, 5), (2, 1), (3, 1), (3, 4),
	(4, 1), (5, 1), (5, 7), (6, 1), (6, 8), (7, 1),
	(8, 1), (9, 1), (10, 1), (10, 6), (11, 2), (12, 1),
	(13, 2), (14, 2), (15, 2), (16, 2), (17, 1), (18, 1),
	(18, 5), (19, 1), (19, 5), (20, 2), (21, 3), (22, 3),
	(23, 3), (24, 3), (25, 3), (26, 3), (27, 2);

INSERT INTO compte (solde, id_membre) -- peuplement table 'compte'
	VALUES (251.30, 1), (158.44, 2), (-45.20, 3), (746.00, 4),
	(550.18, 5), (0, 6), (25.87, 7), (132.80, 8), (158.3, 9),
	(-47.21, 10), (1002.50, 11), (850.00, 12), (-100.46, 13),
	(668.02, 14), (495.25, 15), (10.25, 16), (142.00, 17), (-1.56, 18),
	(0, 19), (758.2, 20), (NULL, 21), (NULL, 22), (NULL, 23), (NULL, 24), (NULL, 25),
	(204.3, 26);

INSERT INTO avion (type, nom) -- peuplement table 'avion'
	VALUES ('DA40', 'F-GUVA'), ('DA40', 'F-HABQ');

INSERT INTO type_resa (nom) -- peuplement table 'type_resa'
	VALUES ('local'), ('navigation'), ('école');

INSERT INTO reservation (dateDebut, dateFin, duree, id_membre, id_avion, id_type, instructeur) -- peuplement table 'reservation'
	VALUES ('2015-03-19 11:00:00', '2015-03-19 11:45:00', 45, 10, 1, 1, NULL),
	('2015-04-27 16:30:00', '2015-04-28 18:15:00', 1545, 2, 1, 2, NULL),
	('2015-04-27 18:00:00', '2015-04-27 19:30:00', 90, 7, 2, 1, NULL),
	('2015-04-29 16:00:00', '2015-04-29 20:30:00', 270, 19, 1, 2, NULL),
	('2015-05-03 17:00:00', '2015-05-03 17:45:00', 45, 4, 1, 1, NULL),
	('2015-05-08 15:45:00', '2015-05-08 16:45:00', 60, 10, 1, 1, NULL),
	('2015-05-09 09:30:00', '2015-05-09 10:00:00', 30, 16, 2, 3, 'BLU'),
	('2015-05-09 11:00:00', '2015-05-09 12:30:00', 90, 20, 2, 3, 'BLU'),
	('2015-05-09 11:00:00', '2015-05-09 12:00:00', 60, 19, 1, 1, NULL),
	('2015-05-09 17:00:00', '2015-05-09 20:30:00', 210, 26, 2, 3, 'PTU'),
	('2015-05-10 10:00:00', '2015-05-10 20:15:00', 615, 6, 2, 2, NULL),
	('2015-05-10 15:00:00', '2015-05-10 16:30:00', 90, 5, 1, 1, NULL),
	('2015-05-10 18:00:00', '2015-05-10 19:00:00', 60, 17, 1, 1, NULL),
	('2015-05-11 10:30:00', '2015-05-11 12:00:00', 90, 16, 1, 3, 'BLU'),
	('2015-05-11 12:00:00', '2015-05-11 12:15:00', 75, 3, 1, 1, NULL),
	('2015-05-13 14:00:00', '2015-05-13 18:00:00', 240, 16, 1, 3, 'BLU'),
	('2015-05-14 17:15:00', '2015-05-14 18:15:00', 60, 4, 1, 1, NULL),
	('2015-05-15 10:30:00', '2015-05-15 18:30:00', 480, 10, 2, 2, NULL),
	('2015-05-17 09:00:00', '2015-05-17 12:00:00', 180, 9, 1, 3, NULL),
	('2015-05-17 11:45:00', '2015-05-17 14:15:00', 150, 4, 2, 2, NULL),
	('2015-05-17 14:00:00', '2015-05-17 16:30:00', 150, 27, 1, 3, 'PTU'),
	('2015-05-17 14:15:00', '2015-05-17 15:15:00', 60, 16, 2, 3, 'BLU'),
	('2015-05-17 17:00:00', '2015-05-17 18:30:00', 90, 11, 1, 3, 'BLU'),
	('2015-05-17 17:00:00', '2015-05-17 17:45:00', 45, 19, 2, 1, NULL),
	('2015-05-17 18:00:00', '2015-05-17 19:30:00', 90, 9, 2, 1, NULL),
	('2015-05-22 15:00:00', '2015-05-17 17:00:00', 120, 13, 1, 3, 'BLU'),
	('2015-05-24 17:00:00', '2015-05-24 18:00:00', 60, 19, 1, 1, NULL),
	('2015-05-24 18:00:00', '2015-05-24 19:00:00', 60, 10, 2, 1, NULL),
	('2015-05-25 18:00:00', '2015-05-25 18:45:00', 45, 5, 1, 1, NULL),
	('2015-05-27 11:00:00', '2015-05-27 15:00:00', 240, 9, 1, 2, NULL),
	('2015-05-29 12:30:00', '2015-05-29 13:30:00', 60, 7, 1, 1, NULL),
	('2015-05-29 16:00:00', '2015-05-29 19:00:00', 180, 9, 2, 3, 'BLU'),
	('2015-05-29 18:30:00', '2015-05-29 19:30:00', 60, 4, 1, 1, NULL),
	('2015-06-02 14:15:00', '2015-06-02 18:00:00', 225, 3, 1, 2, NULL),
	('2015-06-03 14:00:00', '2015-06-02 17:30:00', 210, 3, 1, 2, NULL),
	('2015-06-05 11:00:00', '2015-06-05 16:15:00', 315, 4, 1, 2, NULL),
	('2015-06-05 10:00:00', '2015-06-05 17:15:00', 435, 12, 2, 2, NULL),
	('2015-06-06 11:00:00', '2015-06-06 14:30:00', 210, 7, 1, 2, NULL),
	('2015-06-06 17:30:00', '2015-06-06 18:15:00', 45, 4, 1, 1, NULL),
	('2015-06-06 19:00:00', '2015-06-06 21:00:00', 120, 8, 1, 2, NULL),
	('2015-06-07 10:30:00', '2015-06-07 12:30:00', 120, 12, 1, 1, NULL),
	('2015-06-07 10:00:00', '2015-06-07 18:00:00', 480, 6, 2, 2, NULL),
	('2015-06-07 15:00:00', '2015-06-07 16:30:00', 90, 3, 1, 1, NULL),
	('2015-06-07 18:00:00', '2015-06-07 19:00:00', 60, 17, 1, 1, NULL),
	('2015-06-12 18:30:00', '2015-06-12 21:00:00', 150, 13, 1, 3, 'BLU'),
	('2015-06-13 10:30:00', '2015-06-13 12:00:00', 90, 7, 2, 1, NULL),
	('2015-06-13 10:30:00', '2015-06-13 19:30:00', 540, 12, 1, 2, NULL),
	('2015-06-13 20:00:00', '2015-06-13 22:00:00', 120, 17, 2, 1, NULL),
	('2015-06-13 17:00:00', '2015-06-14 18:00:00', 60, 10, 1, 1, NULL),
	('2015-06-17 16:00:00', '2015-06-17 17:30:00', 90, 8, 1, 1, NULL),
	('2015-06-19 14:00:00', '2015-06-19 17:00:00', 180, 13, 1, 3, NULL),
	('2015-06-19 17:30:00', '2015-06-19 18:30:00', 60, 4, 1, 1, NULL),
	('2015-06-20 16:30:00', '2015-06-20 17:30:00', 60, 10, 1, 1, NULL),
	('2015-06-21 10:30:00', '2015-06-21 12:30:00', 120, 17, 1, 1, NULL),
	('2015-06-21 19:00:00', '2015-06-21 20:00:00', 60, 4, 1, 1, NULL),
	('2015-06-21 21:30:00', '2015-06-21 22:00:00', 45, 2, 1, 1, NULL),
	('2015-06-22 17:00:00', '2015-06-22 19:00:00', 120, 13, 1, 1, NULL),
	('2015-06-24 14:30:00', '2015-06-24 16:00:00', 90, 20, 1, 3, 'BLU'),
	('2015-06-25 11:00:00', '2015-06-25 12:30:00', 90, 20, 1, 3, 'BLU'),
	('2015-06-25 14:00:00', '2015-06-25 21:00:00', 420, 7, 1, 2, NULL),
	('2015-06-26 06:15:00', '2015-06-26 13:30:00', 435, 12, 1, 2, NULL),
	('2015-06-26 06:30:00', '2015-06-26 13:45:00', 435, 3, 1, 2, NULL),
	('2015-06-26 14:00:00', '2015-06-26 16:00:00', 120, 13, 1, 3, 'BLU'),
	('2015-06-27 10:00:00', '2015-06-27 20:00:00', 600, 3, 1, 2, NULL),
	('2015-06-27 10:00:00', '2015-06-27 20:00:00', 600, 19, 1, 2, NULL),
	('2015-06-28 10:00:00', '2015-06-28 20:00:00', 600, 3, 1, 2, NULL),
	('2015-06-28 10:00:00', '2015-06-28 20:00:00', 600, 19, 1, 2, NULL),
	('2015-06-30 06:15:00', '2015-07-07 10:30:00', 000, 19, 1, 2, NULL),
	('2015-07-01 15:45:00', '2015-07-01 17:15:00', 90, 11, 1, 3, 'BLU'),
	('2015-07-03 16:00:00', '2015-07-03 18:30:00', 150, 12, 1, 2, NULL),
	('2015-07-04 19:00:00', '2015-07-04 19:30:00', 30, 10, 1, 1, NULL),
	('2015-07-05 10:00:00', '2015-07-05 12:30:00', 150, 5, 1, 1, NULL),
	('2015-07-06 18:00:00', '2015-07-06 19:30:00', 90, 11, 1, 3, 'BLU'),
	('2015-07-10 09:00:00', '2015-07-10 12:45:00', 225, 13, 2, 3, 'ROU'),
	('2015-07-10 17:30:00', '2015-07-10 19:00:00', 90, 13, 2, 3, 'ROU');

/******************************************************/
/* Requêtes SQL d'interrogation de la base de données */
/******************************************************/

-- affichage de l'ensemble des membres par ordre alphabétique des noms
SELECT nom AS "Nom", prenom AS "Prénom", adresse AS "Adresse", tel AS "Téléphone", email AS "Adresse e-mail" FROM membre
ORDER BY nom;

-- affichage des noms et prénoms de membres ainsi que leur fonction par ordre alphabétique
SELECT membre.nom AS "Nom", membre.prenom AS "Prénom", fonction.nom AS "Inscrit en tant que" FROM membre, fonction, fonction_membre
WHERE fonction_membre.id_membre = membre.id AND fonction_membre.id_fonction = fonction.id
ORDER BY membre.nom;

-- requête affichage coordonnées instructeurs
SELECT membre.nom AS "Nom", prenom AS "Prénom", adresse AS "Adresse", tel AS "Téléphone", email AS "Adresse e-mail" FROM membre, fonction, fonction_membre
WHERE membre.id = fonction_membre.id_membre AND fonction.id = fonction_membre.id_fonction
GROUP BY membre.nom, prenom, adresse, tel, email, fonction_membre.id_fonction
HAVING fonction_membre.id_fonction = 5;

-- affichage noms des élèves pilotes par ordre alphabétique des noms
SELECT membre.nom AS "Nom", prenom AS "Prénom" FROM membre, fonction, fonction_membre
WHERE membre.id = fonction_membre.id_membre AND fonction.id = fonction_membre.id_fonction
GROUP BY membre.nom, prenom, fonction_membre.id_fonction
HAVING fonction_membre.id_fonction = 2
ORDER BY membre.nom;

-- affichage des comptes par membre
SELECT membre.nom AS "Nom", membre.prenom AS "Prénom", compte.solde AS "Solde" FROM membre, compte
WHERE membre.id = compte.id_membre
ORDER BY membre.nom;

-- affichage des comptes pour solde inférieur à 130€
SELECT membre.nom AS "Nom", membre.prenom AS "Prénom", compte.solde AS "Solde (à réguler)" FROM membre, compte
WHERE membre.id = compte.id_membre AND compte.solde <= 130
ORDER BY compte.solde;

-- affichage de l'ensemble des réservations (dates, durée, nom et prénom pilote, instructeur, avion)
SELECT membre.nom AS "Nom", prenom AS "Prénom", datedebut AS "Début de réservation", datefin AS "Fin de réservation", duree AS "Durée (min)", avion.nom AS "Avion", type_resa.nom AS "Type réservation" FROM membre, reservation, type_resa, avion
WHERE reservation.id_membre = membre.id AND reservation.id_avion = avion.id AND reservation.id_type = type_resa.id
ORDER BY reservation.datedebut;

-- affichage des réservations pour école
SELECT membre.nom AS "Nom", prenom AS "Prénom", datedebut AS "Début de réservation", datefin AS "Fin de réservation", duree AS "Durée (min)", avion.nom AS "Avion", type_resa.nom AS "Type réservation" FROM membre, reservation, type_resa, avion
WHERE reservation.id_membre = membre.id AND reservation.id_avion = avion.id AND reservation.id_type = type_resa.id AND reservation.id_type = 3
ORDER BY reservation.datedebut;

-- requête statistiques durée réservations
SELECT CAST(avg(reservation.duree) AS NUMERIC (10,1)) AS "Durée moyenne d''une réservation (min)", sum(reservation.duree)/60 AS "Durée totale des réservations (h)", count(reservation.id) AS "Nombre total de réservations"
FROM reservation
WHERE datedebut BETWEEN '2015-01-01 00:00:00' AND '2015-12-31 00:00:00';
-- NB: << CAST(xxx AS NUMERIC (n1, n2)) >> permet de formater la donnée xxx pour n'afficher que n1 chiffres significatifs dont n2 chiffres après la virgule.

-- calcul potentiel par avion et affichage si supérieur à 100h
SELECT avion.nom AS "Avion(s) d''un potentiel > 100h", CAST(sum(reservation.duree)/60 AS NUMERIC (10,1)) AS "Potentiel (h)"
FROM avion, reservation
WHERE reservation.id_avion = avion.id
GROUP BY avion.nom
HAVING sum(reservation.duree)/60 >= 100;
