Correction : SGBQ

SQL : Requêtes et maintenance d'une base

>SELECT ... FROM ... WHERE ....

1. Afficher tous les acteurs ayant joué dans le film dont l'id est 285

   
   SELECT acteur FROM LesActeurs WHERE film_id=285;
   				

2. Afficher le titre du film dont l'id est 285

   
   SELECT titre FROM LesFilms WHERE film_id=285;					
   					

3. Afficher le réalisateur de ce film

   
   SELECT realisateur FROM LesRealisateurs WHERE film_id=285;					
   					

4. Afficher la recette de ce film

   
   SELECT recette FROM LesFilms WHERE film_id=285;					
   					

5. Afficher les id des films dans lesquels Daniel Craig a joué

   
   SELECT film_id FROM LesActeurs WHERE acteur='Daniel Craig';					
   					

1. Afficher tous les films tournés en 2014 en langue chinoise (zh).

   
   SELECT titre FROM LesFilms WHERE anSortie=2014 and vo='zh';					
   					

2. Afficher tous les films tournés en 2000 en langue japonaise et ceux sortis en 1993 en français (attention à la traduction du français naturel en opérateurs logiques...)

   
   SELECT titre FROM LesFilms WHERE (anSortie=2000 and vo='ja') or (anSortie=1993 and vo='fr');					
   					

Les commandes d'agrégation COUNT - SUM - MIN MAX

1. Le nombre de films sortis en 2015

   
   SELECT COUNT(*) FROM LesFilms WHERE anSortie=2015;					
   					

2. Le nombre de films ou Daniel Craig a joué

   
   SELECT COUNT(*) FROM LesActeurs WHERE acteur='Daniel Craig';					
   					

3. Le nombre de films réalisés par Tim Burton

   
   SELECT COUNT(*) FROM LesRealisateurs WHERE realisateur='Tim Burton';					
   					

1. La recette totale des films sortis en 2011

   
   SELECT SUM(recette) FROM LesFilms WHERE anSortie='2011';					
   					

2. La recette totale des films tournés en français

   
   SELECT SUM(recette) FROM LesFilms WHERE vo='fr';					
   					

1. Le titre du film le plus long

   
   SELECT titre, MAX(duree) FROM LesFilms ;					
   					

2. Le titre du film le plus long sorti en 2011

   
   SELECT titre, MAX(duree) FROM LesFilms WHERE anSortie=2000;					
   					

3. Le titre du film ayant la recette la plus petite

   
   SELECT titre, MIN(recette) FROM LesFilms;					
   					

DISTINCT - ORDER BY

1. Les films de 1985 classés par durée

   
   SELECT titre FROM LesFIlms WHERE anSortie=1985 ORDER BY duree;					
   					

2. Les films tournés en français classés par recette

   
   SELECT titre FROM LesFIlms WHERE vo='fr' ORDER BY recette;					
   					

1. Le nombre d'acteurs présents dans la base

   
   SELECT COUNT(DISTINCT(acteur)) FROM LesActeurs;					
   					

Croiser des bases : JOIN

1. Tous les titres de films réalisés par Tim Burton

   
   SELECT titre 
   FROM LesRealisateurs R JOIN LesFilms F ON (R.film_id=F.film_id)    
   WHERE realisateur='Tim Burton';
   					
   					

2. Les recettes triées par ordre croissant des films dans lesquels joue l'actrice CCH Pounder

   
   SELECT titre
   FROM LesACteurs A JOIN LesFilms F ON (A.film_id=F.film_id)    
   WHERE acteur='CCH Pounder'
   ORDER BY recette;					
   					

3. Tous les réalisateurs avec lesquels Johnny Depp a tourné un film

   
   SELECT DISTINCT(realisateur) 
   FROM LesRealisateurs R JOIN LesActeurs A ON (R.film_id=A.film_id) 
   WHERE acteur="Johnny Depp";						
   					

Maintenance des bases de données

1. Supprimer L'acteur Cédric Le Bourg des relations LesActeurs et LesRealisateurs

   
   DELETE FROM LesActeurs WHERE acteur='Cedric LE BOURG';
   DELETE FROM LesRealisateurs WHERE realisateur='Cedric LE BOURG';					
   					

1. Ajoutez votre nom dans les relations LesActeurs et LesRealisateurs sur le film de votre choix

   
   INSERT INTO LesActeurs VALUES (3121975,'M. PERROD');					
   					

1. Remplacer le fr par français pour l'attribut vo de la relation LesFilms

   
   UPDATE LesFilms SET vo='français' WHERE vo='fr';					
   					

2. Remplacer le en par anglais pour l'attribut vo de la relation LesFilms

   
   UPDATE LesFilms SET vo='anglais' WHERE vo='en';					
   					

Structure et anomalies d'une base de données

1. La commande qui garanti l'unicité de la clef primaire :

   
   CONSTRAINT PK_realisateurs PRIMARY KEY (film_id)			
   			

2. La commande qui garanti la référence de la clef secondaire :

   
   CONSTRAINT FK_id FOREIGN KEY (film_id) REFERENCES LesFilms(film_id))			
   			

3. Les domaines des deux attributs de la relation :

   
   film_id int, realisateur text  (int et text)			
   			

Dans le zoo :
1. Les deux contraintes référentielles sont :
   
   • le contenu du champ nomA dans la relation lesMaladies est pris dans le contenu du champ nomA de la relation lesAnimaux
   • Le contenu du champ noCage de la relation LesAnimaux est pris dans le champs noCage de la relation LesCages
2. Les contraintes de domaine pour les différents attributs sont :
   nomA : text, sexe : text, type : text, anNais : int, Pays : text, noCage : int, nomM : text, fonction : text, allée : int
3. Commande SQL permettant de créer la relation LesAnimaux(vous pouvez la tester avec DbBrowser).

   
   CREATE TABLE LesAnimaux
   (NomA text, sexe text, type text, anNais int, Pays text, noCage int, 
   CONSTRAINT PK_NomA PRIMARY KEY (nomA),
   CONSTRAINT FK_id FOREIGN KEY (nomA) REFERENCES LesMaladies(nomA));
   		

Anomalies dans le zoo
1. Insertion :

   
   INSERT INTO LesMaladies VALUES ('Rex', 'grippe');
   INSERT INTO lesAnmimaux VALUES ('Rex', 'male','chien','2017','France',77);		
   		

2. Suppression :

   
   DELETE FROM LesAnimaux WHERE nomA='Charlotte';
   DELETE FROM LesCages WHERE NoCage='11';		
   		

3. Mise à jour

   
   UPDATE LesAanimaux SET nomA='Chacha' WHERE nomA='Charly';
   UPDATE LesCages SET noCage='51' WHERE noCaage='5';