---
# System prepended metadata

title: Untitled

---

### Exercice 1

### 2)
W(c( p ),d,e)
assoc_1(a(p,e),b(p,e),c(p,e),f)
V(a( p ),b( p ),g(e))
X(g ( p ),h)
Y(k( p ))
Z(l( p ))
assoc_3 (k( p,e ),g(p,e),i)
assoc_4 (k (p,e), l(p,e),j)

### Exercice 2

INSERT INTO bateau (num_bateau,nom_bateau) VALUES
    (1,'Titanic'),
    (2,'Concordia'),
    (3,'Black_Pearl'),
    (4,'France')
    (5,'La_Licorne')
    (6,'Le_faucon_millenium')
    (7,'L_astrolabe');

INSERT INTO escale (date_arrive, date_depart, num_bateau) VALUES
    ('09/04/1912', '15/04/1912',1)
    ('13/11/2014', '15/11/2014',2)
    ('02/08/2014', '03/10/2014',3)
    ('17/08/2014', '15/10/2014',4)
    ('12/07/2014', '25/07/2014',5)
    ('17/08/3016', '15/10/3016',6)
    ('01/08/1785', '05/10/1786',7);

INSERT INTO proprietaire (num_propriétaire,nom, prenom) VALUES
    (1,'Costa', 'Croisiere'),
    (2,'Sparrow', 'Jack'),
    (3,'Macron', 'Manu'),
    (4,'Haddock','Archibald'),
    (5,'Solo', 'Hanne'),
    (6,'De la Pérouse', 'Jean-François');

INSERT INTO conteneur (num_proprietaire,num_conteneur) VALUES
    (1,1),
    (1,2),
    (2,3),
    (3,4),
    (4,5),
    (5,6),
    (6,7),
    (7,7);

INSERT INTO mouvement (num_escale, num_conteneur, poids, pays, est_chargement, date_mouvement) VALUES
    (1, 1, 12325, 'Etats-Unis', TRUE, '12/04/1912'),
    (2, 1, 1325, 'Italie', FALSE, '14/11/2014'),
    (3, 2, 12325, 'Bresil', TRUE, '01/09/2014'),
    (4, 2, 20325, 'Poudlard', TRUE, '03/09/2014'),
    (5, 1, 12325, 'Liechenstein', TRUE, '15/07/2014'),
    (6, 1, 5678, 'Zimbabwe', FALSE, '20/08/3016');

-- Autres insertions pour les tests :

INSERT INTO bateau (nom_bateau) VALUES
    ('Porte-Conteneurs AG111'),
    ('Petit Porte-Conteneurs P12');

INSERT INTO proprietaire (nom, prenom) VALUES
    ('Baya', ''),
    ('Totruna', ''),
    ('Tex wix', ''),
    ('ART 24', '');

INSERT INTO conteneur (num_proprietaire) VALUES
    (1),
    (2),
    (3),
    (4),
    (2),
    (2),
    (5),
    (5);

INSERT INTO escale (date_arrive, date_depart, num_bateau) VALUES
    ('10/11/2014', '18/11/2014', 3),
    ('30/11/2014', '15/12/2014', 2);

INSERT INTO mouvement (num_escale, num_conteneur, poids, pays, est_chargement, date_mouvement) VALUES
    (1, 2, 15248, 'Portugal', TRUE,  '13/12/2014'),
    (1, 3, 7854,  'Bresil',   TRUE,  '12/12/2014'),
    (2, 4, 11589, 'Bresil',   TRUE,  '17/11/2014'),
    (2, 5, 20325, 'Portugal', FALSE, '11/11/2014'),
    (3, 5, 19874, 'Portugal', TRUE,  '16/11/2014'),
    
JPP    
INSERT INTO bateau (nom_bateau) VALUES
    ('Titanic'),
    ('Concordia'),
    ('Black_Pearl'),
    ('France'),
    ('La_Licorne'),
    ('Le_faucon_millenium'),
    ('Lastrolabe');

INSERT INTO escale (date_arrive, date_depart, num_bateau) VALUES
    ('09/04/1912', '15/04/1912', 1),
    ('13/11/2014', '15/11/2014',2),
    ('02/08/2014', '03/10/2014',3),
    ('17/08/2014', '15/10/2014',4),
    ('12/07/2014', '25/07/2014',5),
    ('17/08/3016', '15/10/3016',6),
    ('01/08/1785', '05/10/1786',7);

INSERT INTO proprietaire (nom, prenom) VALUES
    ('Costa', 'Croisiere'),
    ('Sparrow', 'Jack'),
    ('Macron', 'Manu'),
    ('Haddock','Archibald'),
    ('Solo', 'Hanne'),
    ('De la Pérouse', 'Jean-François');

INSERT INTO conteneur (num_proprietaire) VALUES
    (1),
    (1),
    (2),
    (3),
    (4),
    (5),
    (6),
    (7);

INSERT INTO mouvement (num_escale, num_conteneur, poids, pays, est_chargement, date_mouvement) VALUES
    (1, 1, 12325, 'Etats-Unis', TRUE, '12/04/1912'),
    (2, 1, 1325, 'Italie', FALSE, '14/11/2014'),
    (3, 2, 12325, 'Bresil', TRUE, '01/09/2014'),
    (4, 2, 20325, 'Poudlard', TRUE, '03/09/2014'),
    (5, 1, 12325, 'Liechenstein', TRUE, '15/07/2014'),
    (6, 1, 12325, 'Zimbabwe', FALSE, '11/12/2014');
    
    
    JPP2
    INSERT INTO bateau (nom_bateau, num_bateau) VALUES
    ('Titanic',1),
    ('Concordia',2),
    ('Black Pearl',3),
    ('France',4),
    ('La Licorne',5),
    ('Le faucon millenium',6),
    ('Lastrolabe',7);

INSERT INTO escale (date_arrive, date_depart, num_bateau, num_escale) VALUES
    ('09/04/1912', '15/04/1912', 1, 1),
    ('13/11/2014', '15/11/2014',2, 2),
    ('02/08/2014', '03/10/2014',3, 3),
    ('17/08/2014', '15/10/2014',4, 4),
    ('12/07/2014', '25/07/2014',5, 5),
    ('17/08/3016', '15/10/3016',6, 6),
    ('01/08/1785', '05/10/1786',6, 7);

INSERT INTO proprietaire (nom, prenom, num_proprietaire) VALUES
    ('Costa', 'Croisiere', 1),
    ('Sparrow', 'Jack', 2),
    ('Macron', 'Manu', 3),
    ('Haddock','Archibald', 4),
    ('Solo', 'Hanne', 5),
    ('De la Pérouse', 'Jean-François', 6);

INSERT INTO conteneur (num_proprietaire, num_conteneur) VALUES
    (1, 1),
    (1, 2),
    (2, 3),
    (3, 4),
    (4, 5),
    (5, 6),
    (6, 7);

INSERT INTO mouvement (num_escale, num_conteneur, poids, pays, est_chargement, date_mouvement) VALUES
    (1, 1, 12325, 'Etats-Unis', TRUE, '12/04/1912'),
    (2, 1, 1325, 'Italie', FALSE, '14/11/2014'),
    (3, 2, 12325, 'Bresil', TRUE, '01/09/2014'),
    (4, 2, 20325, 'Poudlard', TRUE, '03/09/2014'),
    (5, 1, 12325, 'Liechenstein', TRUE, '15/07/2014'),
    (6, 1, 12325, 'Zimbabwe', FALSE, '11/12/2014');

Select poids from mouvement
where date_mouvement = '*/2014';

2) Select sum(poids) from mouvement
where extract (YEAR FROM date_mouvement)=2014;

3) SELECT COUNT(num_conteneur) from conteneur
JOIN mouvement using(num_conteneur)
JOIN escale using(num_escale)
where extract (YEAR FROM date_depart)=2014 and est_chargement= TRUE;

6) SELECT nom_bateau FROM bateau
JOIN escale using(num_bateau)
JOIN mouvement using(num_escale)
WHERE extract (YEAR FROM date_depart)=2014 and est_chargement= TRUE