Partie I : Base de données (7 points)
1. Création de la base de données MyESIS (1 pt)
sql
Copier le code
CREATE DATABASE MyESIS
ON
( NAME = MyESIS_Data,
FILENAME = 'C:\[Link]',
SIZE = 4MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%)
LOG ON
( NAME = MyESIS_Log,
FILENAME = 'C:\[Link]',
SIZE = 2MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%);
2. Création des tables avec relations (3 pts)
sql
Copier le code
CREATE TABLE ENTREPRISE (
IdEntreprise INT NOT NULL PRIMARY KEY,
NomEntreprise VARCHAR(25),
Siege VARCHAR(25)
);
CREATE TABLE TUTEUR (
NumTuteur INT NOT NULL PRIMARY KEY,
NomTuteur VARCHAR(25),
PrenomTuteur VARCHAR(25),
Telephone VARCHAR(25),
IdEntreprise INT,
FOREIGN KEY (IdEntreprise) REFERENCES ENTREPRISE(IdEntreprise)
);
CREATE TABLE ETUDIANT (
Matricule CHAR(6) NOT NULL PRIMARY KEY,
Nom VARCHAR(25),
Prenom VARCHAR(25),
Age INT,
NumTuteur INT,
FOREIGN KEY (NumTuteur) REFERENCES TUTEUR(NumTuteur)
);
CREATE TABLE PROMOTION (
IdPromotion INT NOT NULL PRIMARY KEY,
NomPromotion CHAR(6),
FilierePromotion CHAR(6),
NiveauPromotion INT
);
CREATE TABLE ANNEE_ACADEMIQUE (
IdAnnee INT NOT NULL PRIMARY KEY,
NomAnnee CHAR(6),
DateDebut DATE
);
CREATE TABLE INSCRIPTION (
IdInscription INT PRIMARY KEY IDENTITY(1,1),
Matricule CHAR(6),
IdPromotion INT,
IdAnnee INT,
DateInscription DATE,
FOREIGN KEY (Matricule) REFERENCES ETUDIANT(Matricule),
FOREIGN KEY (IdPromotion) REFERENCES PROMOTION(IdPromotion),
FOREIGN KEY (IdAnnee) REFERENCES ANNEE_ACADEMIQUE(IdAnnee)
);
3. Insertion des données dans ENTREPRISE (1 pt)
sql
Copier le code
INSERT INTO ENTREPRISE (IdEntreprise, NomEntreprise, Siege) VALUES
(1, 'TechCorp', 'Kinshasa'),
(2, 'SoftDev', 'Lubumbashi'),
(3, 'InnovaTech', 'Goma');
4. Importation des données depuis Excel (2 pts)
sql
Copier le code
BULK INSERT TUTEUR
FROM 'C:\chemin\TUTEUR_EXCEL.csv'
WITH (FORMAT = 'CSV', FIRSTROW = 2);
BULK INSERT ETUDIANT
FROM 'C:\chemin\ETUDIANT_EXCEL.csv'
WITH (FORMAT = 'CSV', FIRSTROW = 2);
Partie II : Visualisation et Correction (16 points)
1. Nombre total de tuteurs (1 pt)
sql
Copier le code
SELECT COUNT(*) AS NombreTuteurs FROM TUTEUR;
2. Problème de numéro de téléphone incorrect (6 pts)
a. Liste des numéros à plus de 10 chiffres (1 pt)
sql
Copier le code
SELECT * FROM TUTEUR WHERE LEN(Telephone) > 10;
b. Liste des numéros à moins de 10 chiffres (1 pt)
sql
Copier le code
SELECT * FROM TUTEUR WHERE LEN(Telephone) < 10;
c. Remplacement des numéros < 10 chiffres par - (2 pts)
sql
Copier le code
UPDATE TUTEUR
SET Telephone = '-'
WHERE LEN(Telephone) < 10;
d. Suppression du dernier chiffre des numéros > 10 chiffres (2 pts)
sql
Copier le code
UPDATE TUTEUR
SET Telephone = LEFT(Telephone, 10)
WHERE LEN(Telephone) > 10;
3. Gestion des doublons (3 pts)
a. Nombre d'occurrences de chaque numéro (1 pt)
sql
Copier le code
SELECT Telephone, COUNT(*) AS Occurrences
FROM TUTEUR
GROUP BY Telephone
ORDER BY Occurrences DESC;
b. Suppression des doublons (2 pts)
sql
Copier le code
UPDATE TUTEUR
SET Telephone = ''
WHERE Telephone IN (
SELECT Telephone
FROM TUTEUR
GROUP BY Telephone
HAVING COUNT(*) > 1
);
Tâche 4 : Jointures (6 pts)
1. Entreprise avec le plus de tuteurs (2 pts)
sql
Copier le code
SELECT TOP 1 [Link], COUNT([Link]) AS NombreTuteurs
FROM ENTREPRISE e
JOIN TUTEUR t ON [Link] = [Link]
GROUP BY [Link]
ORDER BY NombreTuteurs DESC;
2. Entreprises sans tuteurs (2 pts)
sql
Copier le code
SELECT [Link]
FROM ENTREPRISE e
LEFT JOIN TUTEUR t ON [Link] = [Link]
WHERE [Link] IS NULL;
3. Nom de l’étudiant dont l’entreprise du tuteur est en RSA (2 pts)
sql
Copier le code
SELECT [Link]
FROM ETUDIANT et
JOIN TUTEUR t ON [Link] = [Link]
JOIN ENTREPRISE e ON [Link] = [Link]
WHERE [Link] = 'RSA';
Partie III : Gestion des utilisateurs (8 points)
1. Création des connexions et utilisateurs (4 pts)
sql
Copier le code
CREATE LOGIN login_admin_myesis WITH PASSWORD = 'admin1';
CREATE LOGIN login_scolarite_myesis WITH PASSWORD = 'scolarite1';
CREATE USER user_admin_myesis FOR LOGIN login_admin_myesis;
CREATE USER user_scolarite_myesis FOR LOGIN login_scolarite_myesis;
2. Attribution des privilèges (4 pts)
sql
Copier le code
GRANT SELECT, INSERT, UPDATE, DELETE ON DATABASE::MyESIS TO
user_admin_myesis;
GRANT INSERT, UPDATE ON DATABASE::MyESIS TO u