Plan indisponible pour le moment.

I) Données

Soit les tables suivantes :
etudiant (numetu, nom, prenom, datenaiss, rue, cp, ville)
matiere (codemat, libelle, coef)
epreuve (numepreuve, datepreuve, lieu, codemat)
notation (#numetu, #numepreuve, note)

1) Table "etudiant"

Création de la table « etudiant » :

			CREATE TABLE etudiant(
			numetu numeric (3) default 0 NOT NULL,
			nom varchar(255) NOT NULL,
			prenom varchar(255) NOT NULL,
			datenaiss date NOT NULL,
			rue varchar(255) NOT NULL,
			cp char(5) NOT NULL,
			ville varchar(255) NOT NULL,
			PRIMARY KEY (numetu));

Insertion des données dans la table « etudiant » :

			INSERT INTO etudiant (numetu, nom, prenom, datenaiss, rue, cp, ville)
			VALUES (110, ‘Dupont’, ‘Albert’, ‘1980-06-01’, ‘Rue de Crimée’, 69001, ‘Lyon’) ,
			(222, ‘West’, ‘James’, ‘1983-09-03’, ‘Studio’, ‘’, ‘Hollywood’),
			(300, ‘Martin’, ‘Marie’, ‘1988-06-05’, ‘Rue des Acacias’, 69130, ‘Ecully’),
			(421, ‘Durant’, ‘Gaston’, ‘1980-11-15’, ‘Rue de la Meuse’, 69008, ‘Lyon’),
			(575, ‘Titgoutte’, ‘Justine’, ‘1985-02-28’, ‘Chemin du Château’, 69630, ‘Chaponost’),
			(667, ‘Dupond’, ‘Noémie’, ‘1987-09-18’, ‘Rue de Dôle’, 69007, ‘Lyon’),
			(999, ‘Phantom’, ‘Marcel’, ‘1960-01-30’, ‘‘, ‘’, ‘’);

2) Table « matiere »

Création de la table « matiere » :

			CREATE TABLE matiere(
			codemat varchar(255) NOT NULL,
			libelle varchar(255) NOT NULL,
			coef numeric(2,1) NOT NULL,
			PRIMARY KEY (codemat));

Insertion des données dans la table « matiere » :

			INSERT INTO matiere (codemat, libelle, coef)
			VALUES (‘STA’, ‘Statistique’,0.4),
			(‘INF’, ‘Informatique’,0.4),
			(‘ECO’, ‘Econometrie’,0.2);

3) Table epreuve

Création de la table « epreuve » :

			CREATE TABLE epreuve(
			numepreuve numeric(5) NOT NULL,
			datepreuve date NOT NULL,
			lieu varchar(255) NOT NULL,
			codemat varchar (255) NOT NULL,
			PRIMARY KEY (numepreuve));

Insertion des données dans la table « epreuve » :

			INSERT INTO epreuve (numepreuve, datepreuve, lieu, codemat)
			VALUES (11031, ‘2003-12-15’, ‘Salle 191L’, ‘STA’),
			(11032, ‘2004-04-01’, ‘Amphi G’, ‘STA’),
			(21031, ‘2003-10-30’, ‘Salle 191L’, ‘INF’),
			(21032, ‘2004-06-01’, ‘Salle 192L’, ‘INF’),
			(31030, ‘2004-06-02’, ‘Salle 05R’, ‘ECO’);

4) Table notation

Création de la table « notation » :

			CREATE TABLE notation(
			numetu numeric (3) default 0 NOT NULL,
			numepreuve numeric(5) default 0 NOT NULL,
			note numeric(4,2),
			constraint numetu_fk FOREIGN KEY (numetu) REFERENCES etudiant (numetu) ,
			constraint numepreuve_fk FOREIGN KEY (numepreuve) REFERENCES epreuve (numepreuve), );

Insertion des données dans la table « notation » :

			INSERT INTO notation 
			VALUES (110, 11031, 10),
			(110, 11032, 11.5),
			(110, 21031, 8.5),
			(110, 21032, NULL),
			(110, 31030, 13),
			(222, 11031, 9),
			(222, 11032, 14),
			(222, 21031, 12),
			(222, 11032, 16),
			(222, 31030, 20),
			(300, 11031, 14),
			(300, 11032, 20),
			(300, 21031, 20),
			(300, 21032, 13.5),
			(300, 31030, 16),
			(421, 11031, 5.5),
			(421, 11032, 7),
			(421, 21031, 1.5),
			(421, 21032, NULL),
			(421, 31030, 10),
			(575, 11031, 13),
			(575, 11032, 9),
			(575, 21031, 12.5),
			(575, 21032, 14),
			(575, 31030, 7),
			(667, 21032, 16),
			(667, 11032, 20),
			(667, 21031, 8.5),
			(667, 21032, 9.5),
			(667, 31030, NULL);

2) Exercices

Exercice #1

Liste de tous les étudiants, classée par ordre alphabétique inverse. 

				SELECT * 
				FROM etudiant 
				ORDER BY nom DESC;

Exercice #2

Libellé et coefficient (exprimé en pourcentage) de chaque matière. 

				SELECT libelle, SUM(coef)*100 as Pourcentage_Coef
				FROM matiere
				GROUP BY libelle ;

Exercice #3

Nom et prénom des étudiants domiciliés à Lyon. 

				SELECT nom, prenom 
				FROM etudiant 
				WHERE ville = 'Lyon';

Exercice #4

Liste des notes supérieures ou égales à 10. 

				SELECT note 
				FROM notation 
				WHERE note >= 10;

Exercice #5

Liste des épreuves dont la date se situe entre le 1er janvier le 30 juin 2004. 

				SELECT * FROM epreuve 
				WHERE datepreuve BETWEEN ‘2004-01-01’ AND ‘2004-06-30’;

Exercice #6

Nombre total d’épreuves. 

				SELECT COUNT(*) 
				FROM epreuve;

Exercice #7

Nombre de notes indéterminées (NULL). 

				SELECT COUNT(*)
				FROM notation
				WHERE note is NULL;

Exercice #8

Liste des notes en précisant pour chacune le nom et le prénom de l’étudiant qui l’a obtenu. 

				SELECT nom, prenom, note 
				FROM etudiant, notation 
				WHERE etudiant.numetu = notation.numetu ;

Exercice #9

Moyennes de notes de chaque étudiant (indiquer le nom et le prénom), classés de la meilleure à la moins bonne. 

				SELECT nom, prenom, AVG(note)
				FROM etudiant, notation
				WHERE etudiant.numetu=notation.numetu
				GROUP BY nom
				ORDER BY note DESC;

Exercice #10

Moyennes des notes pour les matières (indiquer le libellé) comportant plus d’une épreuve. 

				SELECT libelle, AVG (note), COUNT(*) AS n
				FROM matiere, notation, epreuve
				WHERE matiere.codemat = epreuve.codemat
				AND epreuve.numepreuve = notation.numepreuve
				GROUP BY libelle
				HAVING n>=2;


up