Exercice 1 :
1. MLD :
Etudiant (num_etud, nom, prenom, date_naiss, adresse, #code_fil)
Filiere (code_fil, nom_fil)
Matiere (code_matiere, nom_matiere)
Evaluation (#num_etud, #code_matiere, note)
Absence (#num_etud, #code_matiere, Date_Absence, HeureDebut, HeureFin)
2. CREATE DATABASE Ecole ;
3. USE ECOLE ;
4.
➢ CREATE TABLE Etudiant (
num_etud AUTOINCREMENT NOT NULL PRIMARY KEY,
nom VARCHAR(20),
prenom VARCHAR(20),
date_naiss DATE,
adresse TEXT,
code_fil Char (4)
);
➢ CREATE TABLE Filiere (
code_fil Char (4) Not Null Primary Key,
nom_fil varchar(100)
);
➢ CREATE TABLE Matiere (
code_matiere Char (6) Not Null Primary Key,
nom_matiere varchar (100)
);
➢ CREATE TABLE evaluation (num_etud INT Not Null,
code_matiere Char (6) Not Null,
note Float,
CONSTRAINT PK_evaluation Primary Key
(num_etud, code_matiere)
);
➢ CREATE TABLE absence (num_etud INT Not Null,
code_matiere Char (6) Not Null,
date_Absence date,
HeurDebut time,
HeurFin time,
CONSTRAINT PK_absence Primary Key
(num_etud, code_matiere)
);
5.
➢ ALTER TABLE etudiant ADD constraint fk foreign key (code_fil)
REFERENCES filiere (code_fil);
➢ ALTER TABLE Evaluation ADD constraint fk1 foreign key (num_etud)
REFERENCES etudiant (num_etud);
➢ ALTER TABLE Evaluation ADD constraint fk2 foreign key
(code_matiere) REFERENCES matiere (code_matiere);
➢ ALTER TABLE absence ADD constraint fk3 foreign key (num_etud)
REFERENCES etudiant (num_etud);
➢ ALTER TABLE absence ADD constraint fk4 foreign key
(code_matiere) REFERENCES matiere (code_matiere);
6. ALTER TABLE etudiant ALTER COLUMN nom char (28) ;
7.
➢ INSERT INTO filiere VALUES ('TM','Techniques de management');
➢ INSERT INTO etudiant (nom, prenom, date_naiss, adresse, code_fil)
VALUES ('eddamiri','siham','2001-03-12','ElJadida', 'TM');
8. UPDATE filiere SET nom_fil="management" WHERE code_fil="TM";
9. SELECT * FROM Etudiant;
10. DROP Database ecole;
Exercice 2 :
1/ Creation des tables:
Create table Chambre (Num_Chambre int not null primary key,
Prix int not null,
Nbr_Lit int not null,
Nbr_Pers int not null,
Confort varchar (20),
Equ varchar (20)) ;
Create table Client (Num_Client int not null primary key,
Nom varchar (20) not null,
Prenom varchar (20) not null,
Adresse text not null);
Create table Reservation (Num_Client int not null,
Num_Chambre int not null,
Date_Arr date,
Date_Dep date,
Primary key (Num_Client, Num_Chambre),
Constraint fk_client foreign key (Num_Client)
references client (Num_Client),
Constraint fk_chambre foreign key
(Num_Chambre) references Chambre
(Num_Chambre));
2/ Insertion des valeurs:
Insert into chambre values (10, 80, 01, 02, 'wc', 'non'),
(20, 100, 02, 02, 'douche', 'non'),
(25, 180, 03, 03, 'bain', 'tv'),
(26, 140, 02, 02, 'douche', 'tv');
Insert into client values (1000, 'Dhimen', 'Ahmed', rabat),
(1001, 'Mokhtari', 'Asmae', 'fes'),
(1002, 'Dribi', 'Amine', 'oujda'),
(1003, 'Lamrani', 'loubna', 'rabat');
Insert into Reservation values (1000, 20, '2021-08-09', '2021-08-21'),
(1001, 10, '2022-01-31', '2022-02-05'),
(1002, 25, '2022-02-02', '2022-02-11'),
(1001, 26, '2022-03-18', null);
Partie 2:
1/
SELECT adresse
FROM client;
2/
SELECT num_chambre, Nbr_pers
FROM chambre;
3/
SELECT num_chambre
FROM chambre
WHERE equ='tv';
4/
SELECT num_chambre , prix / nbr_pers
FROM chambre
WHERE equ='tv';
5/
SELECT *
FROM chambre
WHERE prix >=100 AND confort='douche';
6/
SELECT num_chambre
FROM chambre
WHERE prix <=80 OR confort='bain' AND prix<=180;
7/
SELECT *
FROM reservation
WHERE month(date_arr) IN (1,2) AND year(date_arr)=2022;
8/
SELECT *
FROM reservation
WHERE date_dep is null;
9/
SELECT nom
FROM client
WHERE adresse IN("Rabat","Fes");
Ou bien
SELECT nom
FROM client
WHERE adresse ="Rabat" OR adresse ="Fes";
10/
SELECT nom
FROM client
WHERE adresse NOT IN("Rabat");
