Hm7Qqj41

1SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
2SET time_zone = "+00:00";
3
4DROP DATABASE IF EXISTS `examens`;
5CREATE DATABASE IF NOT EXISTS `examens` CHARACTER SET utf8 COLLATE utf8_general_ci;
6USE `examens`;
7
8DROP TABLE IF EXISTS `academie`;
9CREATE TABLE IF NOT EXISTS `academie` (
10  `aca_id` int(11) NOT NULL AUTO_INCREMENT,
11  `aca_nom` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
12  PRIMARY KEY (`aca_id`)
13) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
14
15INSERT INTO `academie` (`aca_id`, `aca_nom`) VALUES
16(1, 'Paris'),
17(2, 'Cr�t�il'),
18(3, 'Versailles');
19
20DROP TABLE IF EXISTS `candidat`;
21CREATE TABLE IF NOT EXISTS `candidat` (
22  `cdt_id` int(11) NOT NULL AUTO_INCREMENT,
23  `cdt_civilite` int(1) NOT NULL,
24  `cdt_nom` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
25  `cdt_prenom` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
26  `cdt_dnais` date NOT NULL,
27  `etb_id` int(11) NOT NULL,
28  PRIMARY KEY (`cdt_id`),
29  KEY `etb_id` (`etb_id`)
30) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
31
32INSERT INTO `candidat` (`cdt_id`, `cdt_civilite`, `cdt_nom`, `cdt_prenom`, `cdt_dnais`, `etb_id`) VALUES
33(1, 1, 'Dupond', 'Toto', '2005-02-09', 1),
34(2, 2, 'Dupond', 'Titi', '2006-04-01', 1),
35(3, 1, 'Dufour', 'D�d�', '2004-02-09', 2),
36(4, 2, 'Dufour', 'Didi', '2005-04-01', 2),
37(5, 1, 'Renaud', 'R�r�', '2004-02-09', 3),
38(6, 2, 'Renaud', 'Riri', '2005-04-01', 3),
39(7, 1, 'Martin', 'Koko', '2004-09-10', 4),
40(8, 2, 'Martin', 'Kiki', '2005-05-02', 4),
41(9, 1, 'Martinez', 'Max', '2004-03-08', 5),
42(10, 2, 'Dubois', 'Marie', '2005-07-18', 5),
43(11, 2, 'Martin', 'Sylvie', '2004-12-01', 6),
44(12, 2, 'Martin', 'L�a', '2004-12-01', 6),
45(13, 2, 'Martin', 'lili', '2004-12-01', 6);
46
47DROP TABLE IF EXISTS `epreuve`;
48CREATE TABLE IF NOT EXISTS `epreuve` (
49  `ep_id` int(11) NOT NULL AUTO_INCREMENT,
50  `ep_nom` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
51  `ep_coef` int(2) NOT NULL,
52  PRIMARY KEY (`ep_id`)
53) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
54
55INSERT INTO `epreuve` (`ep_id`, `ep_nom`, `ep_coef`) VALUES
56(1, 'Informatique', 5),
57(2, 'G�n�rale', 4);
58
59DROP TABLE IF EXISTS `etablissement`;
60CREATE TABLE IF NOT EXISTS `etablissement` (
61  `etb_id` int(11) NOT NULL AUTO_INCREMENT,
62  `etb_nom` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
63  `etb_cp` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
64  `aca_id` int(11) NOT NULL,
65  PRIMARY KEY (`etb_id`),
66  KEY `aca_id` (`aca_id`)
67) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
68
69INSERT INTO `etablissement` (`etb_id`, `etb_nom`, `etb_cp`, `aca_id`) VALUES
70(1, 'Christophe Clombe', '75001', 1),
71(2, 'Alkhawarizmi', '75002', 1),
72(3, 'Pablo Neruda', '94001', 2),
73(4, 'Plateau', '94002', 2),
74(5, 'Picasso', '92001', 3),
75(6, 'Renaud', '92002', 3);
76
77DROP TABLE IF EXISTS `notes`;
78CREATE TABLE IF NOT EXISTS `notes` (
79  `cdt_id` int(11) NOT NULL,
80  `ep_id` int(11) NOT NULL,
81  `note` float NOT NULL,
82  `annee` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
83  KEY `cdt_id` (`cdt_id`,`ep_id`)
84) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
85
86INSERT INTO `notes` (`cdt_id`, `ep_id`, `note`, `annee`) VALUES
87(1, 1, 10.5, '2015'),
88(1, 2, 12.5, '2015'),
89(2, 1, 11.5, '2015'),
90(2, 2, 13.5, '2015'),
91(3, 1, 10.5, '2015'),
92(3, 2, 13.5, '2015'),
93(4, 1, 14.7, '2015'),
94(4, 2, 12.5, '2015'),
95(5, 1, 17.5, '2015'),
96(5, 2, 10.5, '2015'),
97(6, 1, 8.5, '2015'),
98(6, 2, 9.4, '2015'),
99(7, 1, 12.5, '2015'),
100(7, 2, 14.5, '2015'),
101(8, 1, 10, '2015'),
102(8, 2, 11.5, '2015'),
103(9, 1, 9.5, '2015'),
104(9, 2, 15.5, '2015'),
105(10, 1, 18.5, '2015'),
106(10, 2, 10.5, '2015'),
107(11, 1, 5.5, '2015'),
108(11, 2, 18.5, '2015'),
109(13, 1, 13, '2015'),
110(13, 2, 11.5, '2015'),
111(7, 1, 8.5, '2014'),
112(7, 2, 5.5, '2014');