Page 1261 - IT2

#Correcteur INT FOREIGN KEY REFERENCES ENSEIGNANT(Code), Note
FLOAT,PRIMARY KEY(#CodeControle, #CodeElève, #Correcteur))

2.

ALTER TABLE PASSER ADD Constraint Ck_Notecheck(note between 0 AND
20)

3.
ALTER TABLE CONTROLE ADD NombreJour AS DATEDIFF(DAY,GETDATE(),Date)

4.
INSERT INTO MATIERE VALUES …

5.
UPDATE MATIERE SET MasseHoraire=15*MasseHoraire/100 WHERE Coef>3

6.
SELECT * FROM MATIERE M INNER JOIN CONTROLE C ON M.Code=C.Code WHERE
Date='02/01/2015'

7.
SELECT * FROM ELEVE WHERE Code NOT IN (SELECT #CodeElève FROM PASSER
P INNER JOIN CONTROLE C ON P.#CodeControle=C.Code WHERE
C.Date>DATEADD(DAY,-30,GETDATE()))

8.
SELECT * FROM ENSEIGNANT WHERE Code IN(SELECT E.Code FROM Enseignant
E INNER JOIN PASSER P ON E.Code=P.#Correcteur GROUP BY E.Code HAVING
COUNT(P.#Correcteur)>100)

9.
CREATE FUNCTION MoyenneMat(@CodeEleve INT,@CodeMatiere INT) RETURNS
FLOAT
AS
BEGIN
DECLARE @Moy FLOAT
SELECT @Moy=AVG(P.Note) FROM Passer P INNER JOIN Controle C ON
P.#CodeControle=C.Code WHERE C.CodeMatiere=@CodeMatiere AND
P.#CodeElève=@CodeEleve
RETURN @Moy
END

10.
CREATE FUNCTION ListeMoyenneMat(@CodeEleve INT) RETURNS TABLE
AS
RETURN (SELECT Nom,dbo.MoyenneMat(@CodeEleve,Code)AS MoyMat,Coef
FROM Matiere)

www.itlearning-settat.com

Email: admission@itlearning-settat.com

Tél.:0661077812