TD SQL

CREATE TABLE bateau(num_bateau INT PRIMARY KEY, nom TEXT);
https://hackmd.io/new
CREATE TABLE bateau ( num_bateau INT PRIMARY KEY , nom TEXT);
CREATE TABLE escale (num_escale INT PRIMARY KEY , date_arrivee DATE , date_depart DATE, num_bateau INT REFERENCES bateau);
CREATE TABLE proprietaire(num_proprietaire INT PRIMARY KEY , nom TEXT , prenom TEXT);
CREATE TABLE conteneur(num_conteneur INT PRIMARY KEY , taille INT , num_proprietaire INT REFERENCES proprietaire);
CREATE TABLE mouvement(num_escale INT REFERENCES escale , num_conteneur INT REFERENCES conteneur , poids INT , pays TEXT , est_chargement BOOLEAN , date_mouvement DATE , PRIMARY KEY(num_escale , num_conteneur));

INSERT INTO adherent VALUES
(5, 'Duchemin', 'Pierre', '12 Route de Brest 35000 Rennes'),
(6, 'Martin', 'Jean', '11 rue du technopole 29200 Brest'),
(1, 'Dupont', 'Marie', '4 rue de Saint-Malo 35000 Rennes'),

TO_DATE('1994', 'YYYY')),

TO_DATE('19-08-2020', 'DD-MM-YYYY')

INSERT INTO escale VALUES (1, TO_DATE('03-08-2020', 'DD-MM-YYYY'),TO_DATE('15-08-2020', 'DD-MM-YYYY'), 1)
INSERT INTO proprietaire VALUES (1,'BONOMO','Adrien')
INSERT INTO conteneur VALUES (1,1,1)

INSERT INTO bateau VALUES
(1, ‘titanic’);
INSERT INTO escale VALUES
(2,TO_DATE(‘02-02-2000’, ‘DD-MM-YYYY’),TO_DATE(‘18-04-2000’, ‘DD-MM-YYYY’),1);
INSERT INTO mouvement VALUES
(2,3,81,‘france’,TRUE,TO_DATE(‘13-03-2000’, ‘DD-MM-YYYY’));
INSERT INTO conteneur VALUES
(3,12,4);
INSERT INTO proprietaire VALUES
(4,‘bonomo’,‘adrien’);

INSERT INTO bateau VALUES
(1, 'Titanic')

INSERT INTO bateau VALUES (240, 'la terreur des mers');
INSERT INTO escale VALUES (1,TO_DATE('19-08-2020', 'DD-MM-YYYY'),TO_DATE('02-10-2020', 'DD-MM-YYYY'),240);
INSERT INTO proprietaire VALUES (14,'Darcheville','Jean-Claude');
INSERT INTO conteneur VALUES (218,2,14);
INSERT INTO mouvement VALUES (1,218,2678,'Costa Rica',TRUE,TO_DATE('12-09-2020', 'DD-MM-YYYY'));

INSERT INTO bateau VALUES (0, 'Le vaillant');
INSERT INTO escale VALUES (637, 0, '2012-03-07', '2012-03-08');
INSERT INTO proprietaire VALUES (13, 'Einstein', 'Albert');
INSERT INTO conteneur VALUES (48, 'Demi', 13);
INSERT INTO mouvement VALUES (637, 48, 525, 'France', TRUE, '2012-03-08');

SELECT nom FROM bateau ORDER BY nom ASC

SELECT SUM(poids)
FROM mouvement
JOIN escale
ON mouvement.num_escale = escale.num escale
WHERE date_arrivee BETWEEN '2014-01-01' AND '2014-12-31'

Select SUM(m.poids) from mouvement as m JOIN escale as E ON m.num_escale=e.num_escale where e.date_arrivee =('2014', 'YYYY')

SELECT SUM(poids) FROM mouvement
WHERE TO_CHAR(date_mouvement,'YYYY')='2020';

SELECT SUM(poids) FROM mouvement
JOIN escale
USING (num_escale)
WHERE 'date_depart' = '2000/%%/%%'
SELECT COUNT(*) date_depart FROM escale

SELECT COUNT(num_conteneur) FROM conteneur JOIN mouvement USING(num_conteneur) JOIN escale USING(num_escale) WHERE TO_CHAR(date_depart,'YYYY') = '2020';

COUNT

CREATE OR REPLACE VIEW variation_poids AS (
SELECT
nom_bateau,
SUM(poids * (CASE WHEN est_chargement THEN 1 ELSE -1 END)) as variation_poids
FROM bateau
JOIN escale
USING (num_bateau)
JOIN mouvement
USING (num_escale)
GROUP BY num_bateau, nom_bateau);

SELECT *
FROM variation_poids;

SELECT nom FROM bateau ORDER BY nom;
SELECT SUM(poids) FROM mouvement JOIN escale USING(num_escale) WHERE TO_CHAR(date_arrivee,'YYYY') = '2020';
SELECT COUNT(num_conteneur) FROM conteneur JOIN mouvement USING(num_conteneur) JOIN escale USING(num_escale) WHERE TO_CHAR(date_depart,'YYYY') = '2020';
SELECT nom, SUM(poids * (CASE WHEN est_chargement THEN 1 ELSE -1 END)) as variation_poids FROM bateau JOIN escale USING (num_bateau) JOIN mouvement
USING (num_escale) GROUP BY num_bateau, nom;