----------------------------------------Gestion des Films----------------------------------------------------
USE master
GO
/* 1 - Création base de donnée avec les tables et les relations avec mise a jour et suppression on cascade*/
CREATE DATABASE Gestion_De_Film
ON PRIMARY ( NAME = N'Gestion_de_Film',
FILENAME = N'F:\tpSQL\Gestion_de_Film_Data.mdf',
SIZE = 3MB,
MAXSIZE = 3MB,
FILEGROWTH = 1%)
LOG ON ( NAME = N'Gestion_de_Film_log',
FILENAME = N'F:\tpSQL\Gestion_de_Film_log.ldf',
SIZE = 3MB,
MAXSIZE = unlimited,
FILEGROWTH = 1%)
GO
USE Gestion_De_Film
GO
CREATE TABLE FILM (
CodeF smallint,
Titre varchar(20),
Pays varchar(20),
Annee date,
Realisateur varchar(20),
Duree smallint,
Genre varchar(20)
)
GO
CREATE TABLE ACTEUR (
CodeAct smallint,
Nom varchar(20),
Prenom varchar(20),
Pays varchar(20),
DateNaiss date,
)
GO
CREATE TABLE PARTICIPER (
CodeF smallint,
CodeAct smallint
)
GO
-- Création des constraints
/* table (FILM) */
-- ajouter la constraint not null pour les champs avant .. primary key
ALTER TABLE FILM ALTER COLUMN CodeF smallint not null
ALTER TABLE FILM ADD CONSTRAINT PK_FILM_CodeF PRIMARY KEY (CodeF)
-- Valur par defaut :
ALTER TABLE FILM ADD CONSTRAINT Dft_FILM_Duree DEFAULT 90 FOR Duree
ALTER TABLE FILM ADD CONSTRAINT Dft_FILM_Annee DEFAULT GETDATE() FOR Annee
-- Modifier le type de la colonne annee
ALTER TABLE FILM ALTER COLUMN Annee char(4)
GO
/* table (ACTEUR) */
ALTER TABLE ACTEUR Alter column CodeAct smallint not null
ALTER TABLE ACTEUR ADD CONSTRAINT PK_ACTEUR_CodeAct PRIMARY KEY (CodeAct)
GO
/* table (PARTICIPER) */
-- ajouter la constraint not null pour les champs avant .. primary key
ALTER TABLE PARTICIPER ALTER COLUMN CodeF smallint not null
ALTER TABLE PARTICIPER ALTER COLUMN CodeAct smallint not null
ALTER TABLE PARTICIPER ADD CONSTRAINT PK_PARTICIPER_Codes PRIMARY KEY (CodeF, CodeAct)
-- Suppression en cascade
ALTER TABLE PARTICIPER ADD CONSTRAINT FK_PARTICIPER_CodeF FOREIGN KEY (CodeF) REFERENCES FILM(CodeF) ON DELETE CASCADE ON UPDATE CASCADE
ALTER TABLE PARTICIPER ADD CONSTRAINT FK_PARTICIPER_CodeAct FOREIGN KEY (CodeAct) REFERENCES ACTEUR(CodeAct) ON DELETE CASCADE ON UPDATE CASCADE
/*ALTER TABLE PARTICIPER ADD CONSTRAINT FK_PARTICIPER_CodeF FOREIGN KEY (CodeF) REFERENCES FILM(CodeF) ON DELETE SET Null
ALTER TABLE PARTICIPER ADD CONSTRAINT FK_PARTICIPER_CodeAct FOREIGN KEY (CodeAct) REFERENCES ACTEUR(CodeAct) ON DELETE SET Null*/
-- supprimer les constraints (juste pour modifier les constraints :))
ALTER TABLE PARTICIPER DROP CONSTRAINT FK_PARTICIPER_CodeF
ALTER TABLE PARTICIPER DROP CONSTRAINT FK_PARTICIPER_CodeAct
GO
/* 3 - Ajouter une colonne email dans la table ACTEUR */
ALTER TABLE ACTEUR ADD Email varchar(35)
GO
/* 4 - Indexer titre dans la table FILM */
CREATE UNIQUE INDEX idx_titre ON FILM (Titre ASC)
GO
/* 5 - Ajouter des ligne pour chaque table */
INSERT into FILM values (1,'Film 1','Maroc','2012','Réalisateur A',120,'DRAMA')
INSERT into FILM values (2,'Film 2','FRANCE','2005','Réalisateur B',90,'DRAMA')
INSERT into FILM values (3,'Film 3','USA','2002','Réalisateur C',100,'ACTION')
INSERT into FILM values (4,'Film 4','KOREA','2010','Réalisateur A',150,'SCIENCE-FICTION')
GO
INSERT INTO ACTEUR VALUES (10,'BELBSIR','Saâd','Maroc','02-07-1990')
INSERT INTO ACTEUR VALUES (20,'SAID','Youness','KOREA','02-07-1990')
INSERT INTO ACTEUR VALUES (30,'TOUHAMI','Yassine','USA','02-07-1990')
INSERT INTO ACTEUR VALUES (40,'MOUNIR','Mohammed','Maroc','02-07-1990')
INSERT INTO ACTEUR VALUES (50,'NADDA','Nezha','BOTSWANA','02-07-1990')
GO
INSERT INTO PARTICIPER VALUES (1,10) -- film 1 --10 20
INSERT INTO PARTICIPER VALUES (2,10) -- film 2
INSERT INTO PARTICIPER VALUES (4,10) -- film 4
INSERT INTO PARTICIPER VALUES (2,20) -- film 2 --
INSERT INTO PARTICIPER VALUES (4,20) -- film 4
INSERT INTO PARTICIPER VALUES (1,30) -- film 1 --
INSERT INTO PARTICIPER VALUES (3,30) -- film 3
INSERT INTO PARTICIPER VALUES (1,40) -- film 1 --
GO
SELECT * FROM FILM
SELECT * FROM ACTEUR
SELECT * FROM PARTICIPER
GO
/****** CRبATION DES VUES ******/
-- 6 - (Film marocains policier de plus de 1h30 triés en croissant)
Select * From FILM Where Pays = 'Maroc' AND Genre = 'Policier' AND Duree > 90
GO
-- 7 - (Trouver les acteurs âgés de moins de 30ans qui ont tourné avec Fadel comme réalisateur dans des films de science-fiction)
-- Méthode Ensembliste :
Select * From ACTEUR
Where DATEDIFF(YEAR,DateNaiss,Getdate()) > 30
AND CodeAct IN (Select CodeAct From PARTICIPER
Where CodeF IN (Select CodeF From FILM
Where Realisateur='Fadel' AND Genre='SCIENCE-FICTION'))
GO
-- Méthode prédicative :
Select a.Nom, a.Prenom, a.Pays From ACTEUR a, PARTICIPER p, FILM f
WHERE a.CodeAct = p.CodeAct
AND DATEDIFF(YEAR, a.DateNaiss, GETDATE()) > 30
AND p.CodeF = f.CodeF
AND f.Realisateur = 'Fadel'
AND f.Genre = 'SCIENCE-FICTION'
GO
-- 8 - (Réalisateurs ayant le plus grand nombre des films)
-- Méthode 1 :
SELECT Realisateur FROM FILM
GROUP BY Realisateur
HAVING COUNT(*) >= ALL(SELECT COUNT(*) FROM FILM GROUP BY Realisateur)
GO
-- Méthode 2 :
SELECT Realisateur FROM FILM
GROUP BY Realisateur
HAVING COUNT(*) = (SELECT TOP 1 COUNT(*) FROM FILM GROUP BY Realisateur ORDER BY COUNT(*) DESC)
GO
-- 9 - (Acteurs ayant joués dans tous les films de tazi ahmed)
--Méthode 1 :
SELECT DISTINCT a.*
FROM ACTEUR a, PARTICIPER p, FILM f
WHERE a.CodeAct = p.CodeAct
AND p.CodeF = f.CodeF
AND f.CodeF IN (SELECT CodeF from film where Realisateur = 'Ahmed Tazi')
GO
--Méthode 2 :
SELECT DISTINCT a.*
FROM ACTEUR a INNER JOIN PARTICIPER p
ON a.CodeAct = p.CodeAct
WHERE p.CodeF IN (SELECT CodeF FROM FILM WHERE Realisateur = 'Réalisateur A')
GO
/*select distinct a.* from ACTEUR a inner join PARTICIPER p
on a.CodeAct=p.CodeAct
where p.CodeF in(select CodeF from FILM where Realisateur = 'Tazi ahmed')*/
GO
-- 10 - (Acteur ayant joués uniquement pour un seul réalisateur)
Select a.*
FROM ACTEUR a INNER JOIN PARTICIPER p
ON a.CodeAct = p.CodeAct
----------------------------------------------------Gestion des menus----------------------------------------------------------------------
USE master
GO
CREATE DATABASE Gestion_des_Menus
ON PRIMARY (NAME=N'Gestion_des_Menus',
FILENAME=N'F:\tpSQL\Gestion_des_Menus_Data.mdf',
SIZE=3MB,
MAXSIZE=3MB,
FILEGROWTH=1%)
LOG ON (NAME=N'Gestion_des_Menus_Log',
FILENAME=N'F:\tpSQL\Gestion_des_Menus_log.ldf',
SIZE=3MB,
MAXSIZE=3MB,
FILEGROWTH=1%)
GO
USE Gestion_des_Menus
GO
CREATE TABLE Menu (
idMenu smallint identity(1,1) not null,
Entree varchar(20),
Plat varchar(20),
Dessert varchar(20),
)
GO
CREATE TABLE Resto (
idResto smallint identity(1,1) not null,
NomResto varchar(20),
Adresse varchar(20),
Tel varchar(12),
NomChef varchar(20),
nbEtoile int
)
GO
CREATE TABLE MenuResto (
idResto smallint,
idMenu smallint,
prix money not null
)
ALTER TABLE Menu ADD CONSTRAINT PK_Menu_idMenu PRIMARY KEY(idMenu)
ALTER TABLE Resto ADD CONSTRAINT PK_Resto_idResto PRIMARY KEY(idResto)
ALTER TABLE MenuResto ALTER COLUMN idResto smallint not null
ALTER TABLE MenuResto ALTER COLUMN idMenu smallint not null
ALTER TABLE MenuResto ADD CONSTRAINT PK_MenuResto PRIMARY KEY(idMenu, idResto)
ALTER TABLE MenuResto ADD CONSTRAINT FK_MenuResto_idMenu FOREIGN KEY(idMenu)REFERENCES Menu(idMenu)ON DELETE CASCADE ON UPDATE CASCADE
ALTER TABLE MenuResto ADD CONSTRAINT FK_MenuResto_idResto FOREIGN KEY(idResto)REFERENCES Resto(idResto)ON DELETE CASCADE ON UPDATE CASCADE
-- Question 2 :
ALTER TABLE Resto ADD CONSTRAINT CH_Resto_nbEtoile CHECK(nbEtoile BETWEEN 1 AND 6)
ALTER TABLE Resto ADD CONSTRAINT Dft_Resto_nbEtoile DEFAULT 2 FOR nbEtoile
-- Question 5 :
ALTER TABLE Menu ADD PrixEuro Money
-- renomer le nom du champ PrixEuro
EXECUTE sp_rename N'dbo.Menu.PrixEuro', N'Prix_Euro'
-- remplir les tables
INSERT INTO Menu VALUES ('Etr 1','Plat A','Dessert A',200)
INSERT INTO Menu VALUES ('Etr 1','Plat B','Dessert A',100)
INSERT INTO Menu VALUES ('Etr 2','Plat C','Dessert B',500)
INSERT INTO Menu VALUES ('Etr 3','Plat D','Dessert B',180)
INSERT INTO Menu VALUES ('Etr 4','Plat E','Dessert X',20)
GO
INSERT INTO Resto VALUES ('Resto A','Berrechid','0102030405','Chef 1',3)
INSERT INTO Resto(NomResto,Adresse,Tel,NomChef) VALUES ('Resto B','Berrechid','0102030405','Chef 1')
INSERT INTO Resto VALUES ('Resto C','Casablanca','0102030405','Chef 2',1)
INSERT INTO Resto VALUES ('Resto D','Agadir','0102030405','Chef 3',6)
GO
INSERT INTO MenuResto VALUES (2,2,250)
INSERT INTO MenuResto VALUES (2,3,250)
INSERT INTO MenuResto VALUES (2,5,250)
INSERT INTO MenuResto VALUES (3,2,250)
INSERT INTO MenuResto VALUES (3,4,250)
INSERT INTO MenuResto VALUES (6,2,200)
INSERT INTO MenuResto VALUES (6,3,100)
INSERT INTO MenuResto VALUES (6,4,170)
INSERT INTO MenuResto VALUES (6,5,90)
GO
select * from Menu
select * from Resto
select * from MenuResto
GO
-- LES REQUتTE.
SELECT m.*,mr.* From Menu m LEFT OUTER JOIN MenuResto mr ON m.idMenu = mr.idMenu Where mr.idResto is null
GO
-- Qustion 6 :
SELECT r.NomResto, r.Adresse, r.Tel From Resto r
WHERE NomResto LIKE 'm%p'
GO
-- Question 7 :
SELECT r.NomResto, r.NomChef, r.nbEtoile,'Prix_MIN'=MIN(mr.prix),'Prix_MAX'=MAX(mr.prix),'Prix_MOY'=AVG(mr.prix)
FROM Resto r, MenuResto mr
Where r.idResto = mr.idResto
GROUP BY mr.idResto,r.NomResto, r.NomChef, r.nbEtoile
-- Question 8 :
SELECT r.NomResto, mr.prix
FROM Resto r, MenuResto mr
Where r.idResto = mr.idResto
AND mr.prix = (Select MAX(prix) from MenuResto)
GO
-- Question 9 :
Select NomChef, SUM(nbEtoile) As 'Nbr Etoile' from Resto
GROUP BY NomChef
HAVING SUM(nbEtoile) = (Select SUM(nbEtoile) From Resto where NomChef='Chef 1')
