CREATE TABLE Etudiant (
id_Etudiant NUMBER PRIMARY KEY,
nom VARCHAR(50) NOT NULL,
prenom VARCHAR(50) NOT NULL,
date_naissance DATE NOT NULL,
adresse VARCHAR(100),
ville VARCHAR(50),
code_postal VARCHAR(50),
fax VARCHAR(20),
email VARCHAR(100)
);
CREATE TABLE Reservation (
id_Reservation NUMBER PRIMARY KEY,
Batiment VARCHAR(50),
Numero_Salle NUMBER,
id_Enseignement NUMBER,
id_Enseignant NUMBER,
id_Departement NUMBER,
Date_Reservation DATE,
Heure_Debut DATE,
Heure_Fin DATE,
Nombre_Heures NUMBER,
FOREIGN KEY (id_Enseignement) REFERENCES Enseignement(id_Enseignement),
FOREIGN KEY (id_Enseignant) REFERENCES Enseignant(id_Enseignant),
FOREIGN KEY (id_Departement) REFERENCES Departement(id_Departement),
FOREIGN KEY (Batiment, Numero_Salle) REFERENCES Salle(Batiment, Numero_Salle),
CHECK (Nombre_Heures >= 1),
CHECK (Heure_Debut < Heure_Fin)
);
CREATE TABLE salle (
batiment VARCHAR(50),
numero_salle NUMBER,
capacite NUMBER,
PRIMARY KEY (batiment, numero_salle),
CHECK (capacite > 1)
);
CREATE TABLE Enseignement (
id_Enseignement NUMBER PRIMARY KEY,
id_Departement NUMBER,
intitule VARCHAR(50),
description VARCHAR(100),
FOREIGN KEY (id_Departement) REFERENCES Departement(id_Departement)
);
CREATE TABLE Enseignant (
id_Enseignant NUMBER PRIMARY KEY,
id_Departement NUMBER,
nom VARCHAR(50),
prenom VARCHAR(50),
grade VARCHAR(50) CHECK (grade = 'PES A' OR grade = 'PES B' OR grade = 'PES C'),
telephone VARCHAR(50),
fax VARCHAR(50),
email VARCHAR(50),
FOREIGN KEY (id_Departement) REFERENCES Departement(Departement_ID)
);
CREATE TABLE Departement (
id_Departement NUMBER PRIMARY KEY,
nom_departement VARCHAR(100) UNIQUE
);
INSERT INTO Etudiant (id, nom, prenom, date_naissance, adresse, ville, code_postal, fax, email)
VALUES (1, 'Alami', 'Hicham', TO_DATE('1970-09-23', 'YYYY-MM-DD'), '12 Bd Anfa', 'Casa', '22000',
'0522876756', 'a.h@[Link]');
INSERT INTO Reservation (id_Reservation, Batiment, Numero_Salle, id_Enseignement, id_Enseignant,
id_Departement, Date_Reservation, Heure_Debut, Heure_Fin, Nombre_Heures)
VALUES (1, 'sud', 1, 1, 1, 1, TO_DATE('2009-12-12', 'YYYY-MM-DD'), TO_DATE('09:00', 'HH24:MI'),
TO_DATE('11:15', 'HH24:MI'), 2);
INSERT INTO salle (batiment, numero_salle, capacite)
VALUES ('sud',1,100);
INSERT INTO Enseignement (id_Enseignement, id_Departement, intitule, description)
VALUES (1, 1, 'SGBD', 'Système de Gestion de Base de Données');
INSERT INTO Enseignant (id_Enseignant, id_Departement, nom, prenom, grade, telephone, fax, email)
VALUES (1, 1, 'Lamrani', 'Ahmed', 'Prof', '0666124323', '0536453439', 'l.a@[Link]');
INSERT INTO Departement (id_Departement, nom_departement)
VALUES(1,'informatique')
CREATE SEQUENCE S_Etudiant
INCREMENT BY 1
START WITH 1;
CREATE SEQUENCE S_Enseignant
INCREMENT BY 10
START WITH 10;
SELECT nom, prenom
FROM Etudiant
WHERE ville = 'Tanger';
SELECT COUNT(*) AS Nombre_Enseignants_Avec_Email
FROM Enseignant
WHERE email IS NOT NULL;
SELECT AVG(Nombre_Heures) AS Moyenne_Nombre_Heures
FROM Reservation;
SELECT DISTINCT [Link], [Link]
FROM Enseignant
INNER JOIN Reservation ON Enseignant.id_Enseignant = Reservation.id_Enseignant
WHERE DATE(Date_Reservation) = '2020-10-15';
SELECT Numero_Salle, MAX(Nombre_Heures) AS Capacite_Max
FROM Reservation
GROUP BY Numero_Salle;
