ecsmrUm3

1DROP TABLE IF EXISTS ESTAFFECTE, PANNE, LIGNE,CHAUFFEUR, BUS, MARQUE, TYPEPANNE;
2
3-- tables sans les contraintes
4
5CREATE TABLE MARQUE(
6        idMarque Int AUTO_INCREMENT
7        , intitule Varchar (255)
8        , PRIMARY KEY (idMarque )
9)ENGINE=InnoDB DEFAULT CHARACTER SET utf8  DEFAULT COLLATE utf8_general_ci;
10
11CREATE TABLE BUS(
12        idBus    Int AUTO_INCREMENT
13        , modele   Varchar (255)
14        , idMarque Int
15        , PRIMARY KEY (idBus )
16)ENGINE=InnoDB DEFAULT CHARACTER SET utf8  DEFAULT COLLATE utf8_general_ci;
17
18
19CREATE TABLE CHAUFFEUR(
20        idChauffeur Int AUTO_INCREMENT
21        , nom         Varchar (255)
22        , prenom      Varchar (255)
23        , adresse     Varchar (255)
24        , PRIMARY KEY (idChauffeur )
25)ENGINE=InnoDB DEFAULT CHARACTER SET utf8  DEFAULT COLLATE utf8_general_ci;
26
27
28CREATE TABLE LIGNE(
29        idLigne Int AUTO_INCREMENT 
30        , libelle Varchar (255) 
31        , PRIMARY KEY (idLigne )
32)ENGINE=InnoDB DEFAULT CHARACTER SET utf8  DEFAULT COLLATE utf8_general_ci;
33
34
35
36CREATE TABLE TYPEPANNE(
37        idTypePanne int (11)  AUTO_INCREMENT 
38        , libelle     Varchar (255) 
39        , PRIMARY KEY (idTypePanne )
40)ENGINE=InnoDB DEFAULT CHARACTER SET utf8  DEFAULT COLLATE utf8_general_ci;
41
42
43CREATE TABLE PANNE(
44        idBus       Int 
45        , idTypePanne Int 
46        , datePanne   Date 
47        , PRIMARY KEY (idBus ,idTypePanne, datePanne )
48)ENGINE=InnoDB DEFAULT CHARACTER SET utf8  DEFAULT COLLATE utf8_general_ci;
49
50CREATE TABLE ESTAFFECTE(
51        idChauffeur int
52        , idLigne int
53        , idBus int
54        , dateAffectation Date
55        , PRIMARY KEY (idBus ,idLigne ,idChauffeur ,dateAffectation )        
56)ENGINE=InnoDB DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
57
58LOAD DATA LOCAL INFILE 'MARQUE.csv' INTO TABLE MARQUE CHARACTER SET utf8 FIELDS TERMINATED BY ',';
59LOAD DATA LOCAL INFILE 'BUS.csv' INTO TABLE BUS CHARACTER SET utf8  FIELDS TERMINATED BY ',';
60LOAD DATA LOCAL INFILE 'CHAUFFEUR.csv' INTO TABLE CHAUFFEUR CHARACTER SET utf8 FIELDS TERMINATED BY ',';
61LOAD DATA LOCAL INFILE 'LIGNE.csv' INTO TABLE LIGNE CHARACTER SET utf8 FIELDS TERMINATED BY ',';
62LOAD DATA LOCAL INFILE 'TYPEPANNE.csv' INTO TABLE TYPEPANNE CHARACTER SET utf8 FIELDS TERMINATED BY ',';
63LOAD DATA LOCAL INFILE 'PANNE.csv' INTO TABLE PANNE CHARACTER SET utf8 FIELDS TERMINATED BY ',';
64LOAD DATA LOCAL INFILE 'ESTAFFECTE.csv' INTO TABLE ESTAFFECTE CHARACTER SET utf8 FIELDS TERMINATED BY ',';
65/*
66SELECT idBus, BUS.modele, MARQUE.intitule FROM BUS
67INNER JOIN MARQUE
68ON BUS.idMarque = MARQUE.idMarque
69WHERE MARQUE.intitule = 'marquedebus1';
70
71SELECT ESTAFFECTE.dateAffectation, COUNT(distinct(ESTAFFECTE.idChauffeur))as nbChauffeur FROM ESTAFFECTE
72WHERE ESTAFFECTE.dateAffectation = '2016-12-2';
73
74SELECT ESTAFFECTE.idBus, CHAUFFEUR.nom FROM ESTAFFECTE
75INNER JOIN CHAUFFEUR
76ON ESTAFFECTE.idChauffeur = CHAUFFEUR.idChauffeur
77INNER JOIN LIGNE
78ON ESTAFFECTE.idLigne = LIGNE.idLigne
79WHERE LIGNE.libelle = 'ligne 1' AND ESTAFFECTE.dateAffectation = '2016-12-2';
80
81SELECT MARQUE.idMarque, MARQUE.intitule, COUNT(PANNE.idTypePanne) as nbPANNE FROM MARQUE
82INNER JOIN PANNE
83ON MARQUE.idMarque = PANNE.idTypePanne
84WHERE PANNE.datePanne >'2015-12-31' AND PANNE.datePanne <'2017-01-1'
85GROUP BY MARQUE.idMarque, MARQUE.intitule;
86
87SELECT BUS.idBus, BUS.modele FROM BUS
88WHERE BUS.idMarque=(SELECT BUS.idMarque FROM BUS WHERE BUS.idBus = 6);
89
90SELECT distinct ESTAFFECTE.idChauffeur, CHAUFFEUR.nom FROM ESTAFFECTE
91INNER JOIN CHAUFFEUR
92ON ESTAFFECTE.idChauffeur = CHAUFFEUR.idChauffeur
93INNER JOIN LIGNE
94ON ESTAFFECTE.idLigne = LIGNE.idLigne
95WHERE LIGNE.libelle = 'ligne 1' 
96AND ESTAFFECTE.dateAffectation > '2016-11-30' 
97AND ESTAFFECTE.dateAffectation < '2017-01-1' 
98AND ESTAFFECTE.idChauffeur NOT IN (SELECT ESTAFFECTE.idChauffeur FROM ESTAFFECTE where ESTAFFECTE.idLigne in (2, 3));
99
100SELECT distinct ESTAFFECTE.idChauffeur, CHAUFFEUR.nom FROM ESTAFFECTE
101INNER JOIN CHAUFFEUR
102ON ESTAFFECTE.idChauffeur = CHAUFFEUR.idChauffeur
103INNER JOIN LIGNE
104ON ESTAFFECTE.idLigne = LIGNE.idLigne
105WHERE LIGNE.libelle != 'ligne 1' AND ESTAFFECTE.dateAffectation > '2016-11-30' AND ESTAFFECTE.dateAffectation < '2017-01-1';
106*/
107SELECT distinct CHAUFFEUR.nom, CHAUFFEUR.prenom, COUNT(ESTAFFECTE.idLigne)as nbAffectation FROM ESTAFFECTE
108INNER JOIN CHAUFFEUR
109ON ESTAFFECTE.idChauffeur = CHAUFFEUR.idChauffeur
110INNER JOIN LIGNE
111ON ESTAFFECTE.idLigne = LIGNE.idLigne
112WHERE LIGNE.libelle = 'ligne 1'
113AND ESTAFFECTE.dateAffectation > '2016-11-30' 
114AND ESTAFFECTE.dateAffectation < '2017-01-1'
115AND  > 4
116GROUP BY CHAUFFEUR.nom, CHAUFFEUR.prenom;