ESCPI - Année universitaire 213/2014

Cours de Bases de Données

TP 1 - MySQL - Création et remplisage de la base

CREATE TABLE `enseignant` (
  `Numens` int(4) NOT NULL auto_increment,
  `Nomens` varchar(20) NOT NULL,
  `Grade` varchar(3) NOT NULL,
  `Ancien` int(11) NOT NULL,
  PRIMARY KEY  (`Numens`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;

CREATE TABLE `etudiant` (
  `Numetu` int(4) NOT NULL auto_increment,
  `Nometu` varchar(20) NOT NULL,
  `Dtnaiss` datetime default NULL,
  `Cdsexe` varchar(1) NOT NULL,
  PRIMARY KEY  (`Numetu`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

CREATE TABLE `matiere` (
  `Numat` int(4) NOT NULL auto_increment,
  `Nomat` varchar(20) NOT NULL,
  `Coeff` int(3) NOT NULL,
  `Numens` int(4) NOT NULL,
  PRIMARY KEY  (`Numat`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;


CREATE TABLE `notes` (
  `Numetu` int(4) NOT NULL,
  `Numat` int(4) NOT NULL,
  `Note` float default NULL,
  PRIMARY KEY  (`Numetu`,`Numat`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `sexe` (
  `Cdsexe` varchar(1) NOT NULL,
  `Lbsexe` varchar(5) NOT NULL,
  PRIMARY KEY  (`Cdsexe`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;






INSERT INTO `enseignant` VALUES (10, 'Bertrand, Pierre', 'ASS', 2);
INSERT INTO `enseignant` VALUES (11, 'Dupond, Auguste', 'MCF', 3);
INSERT INTO `enseignant` VALUES (15, 'Simon, Etienne', 'ASS', 5);
INSERT INTO `enseignant` VALUES (12, 'Aubois, Jean', 'MCF', 5);
INSERT INTO `enseignant` VALUES (13, 'Boyer, Julie', 'ASS', 2);
INSERT INTO `enseignant` VALUES (14, 'Vincent, Paul', 'PR', 4);
 
INSERT INTO `etudiant` VALUES (1, 'Dupont, Charles', '1981-03-18', 'H');
INSERT INTO `etudiant` VALUES (2, 'Dubois, Jules', '1982-11-02', 'H');
INSERT INTO `etudiant` VALUES (3, 'Favier, Isabelle', '1979-02-02', 'F');
INSERT INTO `etudiant` VALUES (4, 'Gros, Sophie', '1980-06-21', 'F');
INSERT INTO `etudiant` VALUES (5, 'Henri, Pierre', NULL, 'H');
INSERT INTO `etudiant` VALUES (6, 'Humbert, Odile', '1980-03-19', 'F');
INSERT INTO `etudiant` VALUES (7, 'Laurent, Max', '1982-08-10', 'H');
INSERT INTO `etudiant` VALUES (8, 'Martin, Julie', '1980-04-25', 'F');
INSERT INTO `etudiant` VALUES (9, 'Paris, Jeanne', '1981-01-03', 'F');
INSERT INTO `etudiant` VALUES (10, 'Robert, Guy', '1978-09-25', 'H');
INSERT INTO `etudiant` VALUES (11, 'Romain, Georges', '1977-06-21', 'H');
 
INSERT INTO `matiere` VALUES (1, 'Mathématiques', 3, 11);
INSERT INTO `matiere` VALUES (2, 'Informatique', 2, 13);
INSERT INTO `matiere` VALUES (3, 'Sociologie', 2, 10);
INSERT INTO `matiere` VALUES (4, 'Histoire', 1, 15);
INSERT INTO `matiere` VALUES (5, 'Géographie', 1, 15);
 
INSERT INTO `notes` VALUES (1, 1, 10.50);
INSERT INTO `notes` VALUES (1, 2, 9.00);
INSERT INTO `notes` VALUES (1, 3, 12.50);
INSERT INTO `notes` VALUES (1, 4, 13.00);
INSERT INTO `notes` VALUES (1, 5, 7.00);
INSERT INTO `notes` VALUES (2, 1, 11.00);
INSERT INTO `notes` VALUES (2, 2, 14.50);
INSERT INTO `notes` VALUES (2, 3, 8.50);
INSERT INTO `notes` VALUES (2, 4, 10.00);
INSERT INTO `notes` VALUES (2, 5, 13.00);
INSERT INTO `notes` VALUES (3, 1, 14.00);
INSERT INTO `notes` VALUES (3, 2, 9.00);
INSERT INTO `notes` VALUES (3, 3, 5.00);
INSERT INTO `notes` VALUES (3, 4, 7.50);
INSERT INTO `notes` VALUES (3, 5, 12.00);
INSERT INTO `notes` VALUES (4, 1, 11.50);
INSERT INTO `notes` VALUES (4, 2, 10.00);
INSERT INTO `notes` VALUES (4, 4, 13.00);
INSERT INTO `notes` VALUES (4, 5, 10.00);
INSERT INTO `notes` VALUES (5, 1, 7.00);
INSERT INTO `notes` VALUES (5, 2, 13.00);
INSERT INTO `notes` VALUES (5, 3, 10.00);
INSERT INTO `notes` VALUES (5, 4, 16.00);
INSERT INTO `notes` VALUES (5, 5, 6.00);
INSERT INTO `notes` VALUES (6, 2, 13.00);
INSERT INTO `notes` VALUES (6, 3, 10.00);
INSERT INTO `notes` VALUES (6, 4, 11.00);
INSERT INTO `notes` VALUES (7, 1, 12.50);
INSERT INTO `notes` VALUES (7, 2, 11.50);
INSERT INTO `notes` VALUES (7, 3, 9.00);
INSERT INTO `notes` VALUES (7, 4, 8.00);
INSERT INTO `notes` VALUES (7, 5, NULL);
INSERT INTO `notes` VALUES (8, 1, 15.00);
INSERT INTO `notes` VALUES (8, 2, 16.00);
INSERT INTO `notes` VALUES (8, 3, 12.00);
INSERT INTO `notes` VALUES (8, 4, 13.00);
INSERT INTO `notes` VALUES (8, 5, 10.00);
INSERT INTO `notes` VALUES (9, 1, 8.00);
INSERT INTO `notes` VALUES (9, 2, 12.00);
INSERT INTO `notes` VALUES (9, 4, 8.00);
INSERT INTO `notes` VALUES (10, 1, NULL);
INSERT INTO `notes` VALUES (10, 2, 16.00);
INSERT INTO `notes` VALUES (10, 3, 11.50);
INSERT INTO `notes` VALUES (10, 4, 9.00);
INSERT INTO `notes` VALUES (10, 5, 9.00);
 
 
INSERT INTO `sexe` VALUES ('F', 'Femme');
INSERT INTO `sexe` VALUES ('H', 'Homme');