Mise en pratique du SQL

Sql sur ce site

Pour connaître l'ensemble des tables de la base chargée dans les éditeurs SQL de ce site, il suffit d'écrire :

SELECT * 
FROM sqlite_master 
WHERE TYPE ="table";

et de valider.

Base de données Prix Nobel

Activité 1 - Base de donnée des prix Nobel

A l'aide de la cellule ci-dessous, répondre aux questions :

Base Prix Nobel

SELECT * FROM sqlite_master WHERE TYPE ="table";

Exécuter le code
(Ctrl+S)

a) Combien de relation possède la base de données?

b) Combien d'attributs possède la relation nobel?

c) Quel est le type de l'attribut annee?

Réponse

a. Il y a une seule relation : nobel
b. Elle possède trois attributs : annee, sujet, laureat
c. L’attribut annee est du type Entier

Activité 2 - Requêtes d'interrogation

Chaque requête devra se terminer par un ; avant d'être exécutée.

Base Prix Nobel

Exécuter le code
(Ctrl+S)

1. Comment afficher le nom de tous les lauréats?
2. Comment afficher le nom de toutes les disciplines en évitant les doublons?
3. Quelle est la discipline de Wilhelm Conrad Röntgen?
4. Dans quelle discipline Paul Krugman est-il devenu Prix Nobel?
5. En quelle année Albert Fert a-t-il eu le prix Nobel?
6. Quelle est l’année de distinction de Pierre Curie?
7. Quelle est l’année de distinction et la matière de Bertha von Suttner?
8. Quels sont les lauréats distingués au XXI e siècle?
9. Quels sont les lauréats du prix Nobel de la Paix durant la deuxième guerre mondiale?
10. Quels sont les lauréats distingués en Médecine en 1901 et 2001?
11. Quels sont les lauréats des prix nobel de Physique et de Médecine en 2008?

Réponse

1.

SELECT laureat
FROM nobel;

2.

SELECT DISTINCT sujet
FROM nobel;

3.

SELECT sujet
FROM nobel 
WHERE laureat LIKE "Wilhelm Conrad R%ntgen";

4.

SELECT sujet
FROM nobel 
WHERE laureat = "Paul Krugman";

5.

SELECT annee
FROM nobel 
WHERE laureat = "Albert Fert";

6.

SELECT annee
FROM nobel 
WHERE laureat = "Pierre Curie";

7.

SELECT annee, sujet
FROM nobel 
WHERE laureat = "Bertha von Suttner";

8.

SELECT laureat
FROM nobel 
WHERE annee >= 2000;

9.

SELECT laureat
FROM nobel 
WHERE annee >= 1939 AND annee <= 1945 AND sujet = "Paix";

10.

SELECT laureat
FROM nobel 
WHERE (annee = 1901 OR annee = 2001) AND sujet LIKE "M%decine";

11.

SELECT laureat
FROM nobel 
WHERE (sujet = "Physique" OR sujet LIKE "M%decine") AND annee = 2008;

Activité 3 - Requêtes d'agrégation

Base Prix Nobel

Exécuter le code
(Ctrl+S)

1. Combien d’enregistrements au total comporte la relation?
2. Combien de personnes ont reçu le prix Nobel de la paix?
3. Combien de personnes ont reçu le prix Nobel de litérature?
4. Combien de personnes ont reçu le prix Nobel de mathématiques?
5. Combien de personnes ont reçu un prix Nobel en 1901?
6. Combien de personnes ont reçu un prix Nobel de chimie en 1939?
7. En quelle année a été décerné le premier prix Nobel d’économie?
8. Combien de prix Nobel a reçu Marie Curie?
9. Quels sont les prix lauréats, leur discipline et l’année de distinction de tous les prix Nobel contenant cohen dans leur nom (on ne fera pas de distinction de casse)?
10. Combien y’a t’il eu de lauréats en Physique et en Chimie?
11. Combien y’a t’il eu de lauréats de Médecine et de littérature en 2000?
12. Nombre de lauréats différents parmi les prix nobels de la paix?

Réponse

1.

SELECT count(*)
FROM nobel;
→ 816 

2.

SELECT count(laureat)
FROM nobel 
WHERE sujet= "Paix";
→ 119

3.

SELECT count(laureat)
FROM nobel 
WHERE sujet="Literature";
→ 105

4.

SELECT count(laureat)
FROM nobel 
WHERE sujet="Mathematiques";
→ 0

5.

SELECT count(laureat)
FROM nobel 
WHERE annee = 1901;
→ 6

6.

SELECT count(laureat)
FROM nobel 
WHERE annee = 1939 AND sujet = "Chimie";
→ 2

7.

SELECT MIN(annee)
FROM nobel 
WHERE sujet = "Economie";
→ 1969

8.

SELECT count(annee)
FROM nobel 
WHERE laureat LIKE "%Marie Curie %";
→ 2

9.

SELECT laureat, sujet, annee
FROM nobel 
WHERE UPPER(laureat) LIKE "%COHEN %";
→ Claude Cohen-Tannoudji|Physique|1997
Stanley Cohen|Médecine|1986

10.

SELECT count(DISTINCT laureat)
FROM nobel 
WHERE sujet = "Physique" OR sujet = "Chimie";
→ 335

11.

SELECT count(DISTINCT laureat)
FROM nobel 
WHERE (sujet = "Literature" OR sujet LIKE "M%decine") AND annee = 2000;
→ 4

12.

SELECT count(DISTINCT laureat)
FROM nobel 
WHERE sujet = "Paix";
→ 116

Activité 4 - Requêtes d'insertion

Base Prix Nobel

Exécuter le code
(Ctrl+S)

1. En 2019, Esther Duflo a reçu le prix Nobel d’économie. Écrivez la requête permettant d’insérer cet enregistrement.
2. Quelle requête permet de modifier l’enregistrement précédent pour accoler le nom d’époux (Banerjee) après celui de Duflo?
3. De nombreuses pétitions circulent pour retirer le prix Nobel à Aung San Suu Kyi. Quelle requête permettrait cela?

Réponse

1.

INSERT INTO nobel
VALUES (2019, "Economie", "Esther Duflo");

2.

UPDATE nobel
SET laureat = "Esther Duflo Banerjee" 
WHERE laureat = "Esther Duflo";

3.

DELETE FROM nobel
WHERE laureat LIKE "Aung %";

Base de données Collectivités

Activité 5 - Base de données collectivités

Dans la cellule ci-dessous, on a chargé la base de donnée Collectivites.db.

Base Collectivites

SELECT * FROM sqlite_master WHERE TYPE ="table";

Exécuter le code
(Ctrl+S)

1. Combien de relation possède la base de données?
2. Réalisez un schéma relationnel de cette base de données, sous la forme graphique, en précisant pour chaque attribut son type et s'il doit impérativement être rempli.

Réponse

1. Deux relations : Ville et Departement

2.

Ville	Departement
idVille (entier non nul)	idDepartement (entier non nul)
nom (chaine non nulle)	numero (chaine non nulle)
codePostal (entier non nul)	nom (chaine non nulle)
nbHabitants (entier)
#idDepartement(entier non nul)

Activité 6 - Collecte des informations

La base Collectivites est vide. Il faut la remplir. Pour cela, en vous aidant d'internet, complétez les tableaux suivants :

idCommune	Commune	Code Postal	Département	Nombre d'habitants
	Rouen
	Dieppe
	Enverneu
	Le Neubourg	27110	Eure
	Igoville

idDepartement	Département	Code d'immatriculation
	Seine-Maritime
	Eure
	Calvados

Réponse

IdCommune	Commune	Code Postal	Département	Nombre d’habitants
1	Rouen	76000	Seine-Maritime	110169
2	Dieppe	76200	Seine-Maritime	29080
3	Envermeu	76630	Seine-Maritime	2097
4	Le Neubourg	27110	Eure	4166
5	Igoville	27460	Eure	1746

IdDepartement	Département	Code d’immatriculation
1	Seine-Maritime	76
2	Eure	27
3	Calvados	14

Activité 7 - Insertion des enregistrements

Base Collectivites

Exécuter le code
(Ctrl+S)

1. Insérez le département de Seine-Maritime.
2. Insérez la commune de Rouen.
3. Faîtes de même, en une seule requête, avec les communes de Dieppe et d’Envermeu.
4. Insérez la commune d’Igoville.
5. Insérez la commune du Neubourg.

Réponse

1.

INSERT INTO Departement
VALUES (1, "76", "Seine-Maritime");

2.

INSERT INTO Ville
VALUES (1, "Rouen", 76000, 110169, 1);

3.

INSERT INTO Ville
VALUES (2, "Dieppe", 76200, 29080, 1), (3, "Envermeu", 76630, 2097, 1);

4.

INSERT INTO Departement
VALUES (2, "27", "Eure");
INSERT INTO Ville 
VALUES (5, "Igoville", 27460, 1746, 2);

5.

INSERT INTO Ville
VALUES (4, "Le Neubourg", 27110, 4166, 2);

Activité 8 - Recherche d'informations

Base Collectivites

Exécuter le code
(Ctrl+S)

En recherchant éventuellement les informations manquantes, indiquez le code SQL permettant de répondre aux consignes suivantes :

1. Trouville, Mézidon-Canon et Crèvecoeur-en-Auge sont des villes du Calvados.
2. Le vrai nom de Trouville est en fait Trouville-Sur-Mer.
3. Médizon-Canon et Crèvecoeur-en-Auge n’existent plus. Elles ont fusionné pour donner une nouvelle commune : Mézidon-Vallée-d’Auge.

Réponse

1.

INSERT INTO Departement
VALUES (3, "14", "Calvados");
INSERT INTO Ville 
VALUES (6, "Trouville", 14360, 4675, 3),
(7, "Mezidon-Canon", 14270, 4838, 3),
(8, "Crevecoeur-en-Auge", 14240, 480, 3);

2.

UPDATE Ville
SET nom= "Trouville-sur-Mer"
WHERE nom= "Trouville";

3.

INSERT INTO Ville
VALUES (9, "Mezidon-Vallee-d’Auge", 14270, 9712) ;
DELETE  FROM  Ville 
WHERE  nom  =  "Mezidon-Canon"  OR  nom  =  "Crevecoeur-en-Auge";

Activité 9 - Vérification des enregistrements

Base Collectivites

Exécuter le code
(Ctrl+S)

Indiquez le code SQL permettant de répondre aux consignes suivantes :

1. Combien il y a-t-il de départements différents enregistrés dans la base? (réponse : 3)
2. Combien il y a-t-il de communes différentes enregistrées dans la base? (réponse : 7)
3. Combien il y a-t-il de communes dans l’Eure enregistrées dans la base? (réponse : 2)
4. Combien il y a-t-il de communes en Seine-Maritime enregistrées dans la base? (réponse : 3)
5. Combien il y a-t-il de communes dans le Calvados enregistrées dans la base? (réponse : 2)

Réponse

1.

SELECT count(*)
FROM Departement;

2.

SELECT count(*)
FROM Ville;

3.

SELECT count(*)
FROM Ville 
WHERE idDepartement = 2;

4.

SELECT count(*)
FROM Ville 
WHERE idDepartement = 1;

5.

SELECT count(*)
FROM Ville 
WHERE idDepartement = 3;

Base de donnée Cinéma

Activité 10 - Base de donnée Cinéma

Dans la cellule ci-dessous, on a chargé la base de donnée Cinema.sqlite.

Base Cinema

Exécuter le code
(Ctrl+S)

1. Combien de relations possède la base de données?
2. Combien d’attributs possède la relation Artiste?
3. Quelle est sa clé primaire?
4. Combien d’attributs possède la relation Film?
5. Quelle est sa clé primaire?
6. Quelle est sa clé étrangère?
7. Quelle est la référence de la clé étrangère.
8. En procédant de la même façon pour toutes les tables, représentez le schéma relationnel sous forme graphique, en faisant bien figurer les relations entre les clé primaires et clés étrangères.

Réponse

1. 5 relations : Artiste, Cinema, Film, Role, Seance.
2. Artiste à 3 attributs : nom, prenom, anneeNaissance.
3. La clé primaire d’Artiste est nom.
4. Film a 4 attributs : idFilm, titre, annee, nomRealisateur.
5. La clé primaire de Film est idFilm.
6. La clé étrangère de Film est nomRealisateur.
7. La clé étrangère de Film fait référence à l’attribut nom de la relation Artiste.
8. 

Activité 11 - Requêtes simples

Base Cinema

Exécuter le code
(Ctrl+S)

Indiquez le code SQL permettant d’afficher :

1. les titres des films triés par ordre alphabétique.
2. Les prénoms, noms et année de naissance des artites nés avant 1950.
3. Les cinémas qui sont situés dans le 12ème arrondissement.
4. Les artistes dont le nom commence par la lettre H (on utilisera la commande LIKE).
5. Les acteurs dont on ignore la date de naissance (cela signifie que la valeur n’existe pas).
6. Le nombre de fois où Bruce Willis a joué le rôle de McLane (on utilisera la commande UPPER() pour s’affranchir de la casse).

Réponse

1.

SELECT titre
FROM Film 
ORDER BY titre;

2.

SELECT prenom, nom, anneeNaissance
FROM artiste 
WHERE anneeNaissance <= 1950;

3.

SELECT nomCinema
FROM Cinema 
WHERE arrondissement = 12;

4.

SELECT prenom, nom
FROM Artiste 
WHERE nom LIKE "H %";

5.

SELECT prenom, nom
FROM Artiste 
WHERE anneeNaissance IS NULL;

6.

SELECT count(idFilm))
FROM Role 
WHERE UPPER(nomRole) = "MCLANE" AND UPPER(nomActeur) = "WILLIS";

Activité 12 - Requêtes avec jointure

On rapelle qu’une jointure consiste à rapprocher les clé primaire et étrangère de deux relations.

La syntaxe est la suivante :

SELECT  attribut 
FROM  relation1 JOIN  relation2  
ON  relation1.cle1  =  relation2.cle2;

Base Cinema

Exécuter le code
(Ctrl+S)

Indiquez le code SQL permettant de répondre aux questions suivantes :

1. Quel est le nom et le prénom de l’acteur qui a joué Tarzan (pensez à la commande UPPER()).
2. Quelle est l’année de naissance du réalisateur de Reservoir Dogs?
3. Quels sont les titres des films dans lesquels a joué Woody Allen. Donnez aussi le rôle joué.
4. Quels films peut-on voir au cinéma Rex? (Attention aux doublons)
5. Quels films peut-on voir à 15h dans un cinéma parisien?
6. Quels sont les cinémas (nom, adresse et arrondissement) qui diffusent des films le matin.
7. Quels sont les cinémas (nom, adresse et arrondissement) qui diffusent des films le matin.
   Indiquez aussi le titre du film. 8.Quels films peut-on voir dans un cinéma du 12 e arrondissement? On donnera le titre du film, le cinéma dans lequel il est joué et son adresse.
8. Quels sont les nom et prénom des acteurs qui ont joué dans le film Vertigo.
9. Quel réalisateur a tourné dans ses propres films? Donnez le nom, le rôle et le titre des films.
10. Où peut-on voir le film Pulp Fiction? On donnera le nom, l’adresse du cinéma et numéro de la séance.
11. Où peut-on voir un film avec Clint Eastwood? On donnera le titre du film, le nom et l’adresse du cinéma, ainsi que l’heure de début du film.
12. Quels sont les cinémas (nom, adresse et arrondissement) ayant une salle de plus de 150 places et passant un film avec Bruce Willis?

Réponse

1.

SELECT nom,prenom
FROM Artiste
JOIN Role ON Artiste.nom = Role.nomActeur 
WHERE UPPER(nomRole) = "TARZAN";
→ Lambert Christophe

2.

SELECT anneeNaissance
FROM Artiste
JOIN Film ON Artiste.nom = Film.nomRealisateur 
WHERE titre = "Reservoir Dogs";
→ 1948

3.

SELECT titre, nomRole
FROM Film
JOIN Role ON Film.idFilm = Role.idFilm 
WHERE nomActeur = "Allen";
→ Manhattan, Davis
→ Annie Hall, Jonas

4. ...

Activité 13 - Modification de la base cinéma

Base Cinema

Exécuter le code
(Ctrl+S)

1. On souhaite insérer un nouvel artiste. Il s’agit de Ridley Scott, né en 1937. Indiquez la requête SQL permettant de réaliser cette opération.
2. Ridely Scott est le réalisateur du film Blade Runner, sorti en 1982. Indiquez la requête SQL permettant de saisir ce nouvel enregistrement.
3. Les acteurs principaux sont Harisson Ford (né en 1942) dans le rôle de Rick Deckard et Rutger Hauer (né en 1944) dans le rôle de Roy Batty. Indiquez la requête SQL permettant de saisir ces nouveaux enregistrements.
4. Mark Hamill (né en 1951), Harisson Ford (né en 1942) et Carrie Fisher (née en 1956) sont les acteurs principaux du film L’empire contre attaque réalisé en 1980 par Irvin Kershner (né en 1923). Indiquez les requêtes SQL permettant de saisir ces enregistrements. Indiquez l’ordre dans lequel il faut remplir les relations.
5. Le réalisateur moldave Mevatlave Kraspeck est décédé brutalement hier à l’âge de 103 ans. Le Rex décide de lui rendre hommage en diffusant le film Gant, Savon, Serviette et Ponge tourné en 1957 avec la star du cinéma muet Agathe Zeblouse (née en 1932) dans la salle 1 de 13h à 16h, le film Shower on the Beach tourné en 1963 avec Camille Honnête (née en 1940) dans la salle 1 de 16h à 18h et Bains Moussants avec Jacques Célère (né en 1945 et décédé trop vite en 1969) tourné en 1967 dans la salle 2 de 15h à 18h. Indiquez les requêtes SQL permettant de modifier les séances du cinéma Rex.

Réponse

1.

INSERT INTO Artiste
VALUES(‘Scott’, "Ridley", 1937);

2.

INSERT INTO Film
VALUES(143, "Blade Runner", 1982, "Scott");

3.

INSERT INTO Artiste
VALUES("Ford", "Harisson", 1942);
INSERT INTO Artiste  
VALUES("Hauer", "Rutger", 1944);
INSERT INTO Role  
VALUES("Rick Deckard", 143, "Ford");
INSERT INTO Role  
VALUES("Roy Batty", 143, "Hauer");

4.

INSERT INTO Artiste
VALUES("Hamill", "Mark", 1951) 
INSERT INTO Artiste  
VALUES("Fisher", "Carrie", 1956);
INSERT INTO Artiste  
VALUES("Kershner", "Irvin", 1923);
INSERT INTO Film 
VALUES(144, "L empire contre attaque", 1980, "Kershner");
INSERT INTO Role 
VALUES("Luke Skywalker", 144, "Hamill");
INSERT INTO Role 
VALUES("Princesse Leia", 144, "Fisher");
INSERT INTO Role 
VALUES("Han Solo", 144, "Ford");

5. ...

Base de données Voyageurs

Activité 14 - Base Voyageurs

Schéma de la base

Voyageur (idVoyageur, nom, prénom, ville, région)
Séjour (idSéjour, #idVoyageur, #codeLogement, début, fin)
Logement (code, nom, capacité, type, lieu)
Activité (codeLogement, codeActivité, description)

1. Exprimer les requêtes suivantes sans utiliser d'imbrication.

Nom des villes

Base Voyageurs

Exécuter le code
(Ctrl+S)

Réponse

select ville from Voyageur

Nom des logements en Bretagne

Base Voyageurs

Exécuter le code
(Ctrl+S)

Réponse

select nom from Logement 
where lieu = 'Bretagne'

Nom des logements dont la capacité est inférieure à 20

Base Voyageurs

Exécuter le code
(Ctrl+S)

Réponse

select nom from Logement 
where capacité < 20

Description des activités de plongée

Base Voyageurs

Exécuter le code
(Ctrl+S)

Réponse

select description from Activité 
where codeActivité = 'Plongée'

Nom des logements avec piscine

Base Voyageurs

Exécuter le code
(Ctrl+S)

Réponse

select nom from Logement as l, Activité as a 
where l.code = a.codeLogement
and a.codeActivité = 'Piscine'

Nom des voyageurs qui sont allés en Corse

Base Voyageurs

Exécuter le code
(Ctrl+S)

Réponse

select v.prénom, v.nom
from Logement as l, Séjour as s, Voyageur as v  
where l.code = s.codeLogement
and s.idVoyageur =v.idVoyageur
and lieu= 'Corse'

Les voyageurs qui sont allés ailleurs qu'en Corse

Base Voyageurs

Exécuter le code
(Ctrl+S)

Réponse

select v.prénom, v.nom
from Logement as l, Séjour as s, Voyageur as v    
where l.code = s.codeLogement
and s.idVoyageur =v.idVoyageur
and lieu != 'Corse'

Nom des logements visités par un auvergnat

Base Voyageurs

Exécuter le code
(Ctrl+S)

Réponse

select l.nom as nomLogement, v.nom 
from Logement as l, Séjour as s, Voyageur as v
where l.code = s.codeLogement
and s.idVoyageur =v.idVoyageur
and région= 'Auvergne'

Nom des logements et des voyageurs situés dans la même région

Base Voyageurs

Exécuter le code
(Ctrl+S)

Réponse

select l.nom as nomLogement, v.nom
from Logement as l, Voyageur as v
where région=lieu

Les paires de voyageurs (donner les noms) qui ont séjourné dans le même logement

Base Voyageurs

Exécuter le code
(Ctrl+S)

Réponse

select distinct l.nom as nomLogement, v.nom as voyageur
from Logement as l, Séjour as s1, Voyageur as v, Séjour as s2
where l.code = s1.codeLogement
and l.code = s2.codeLogement
and s1.idVoyageur =v.idVoyageur
and s2.idVoyageur =v.idVoyageur
and s1.idSéjour != s2.idSéjour

Les voyageurs qui sont allés (au moins) deux fois dans le même logement

Base Voyageurs

Exécuter le code
(Ctrl+S)

Réponse

select distinct l.nom as nomLogement, v.nom as voyageur
from Logement as l, Séjour as s1, Voyageur as v, Séjour as s2
where l.code = s1.codeLogement
and l.code = s2.codeLogement
and s1.idVoyageur =v.idVoyageur
and s2.idVoyageur =v.idVoyageur
and s1.idSéjour != s2.idSéjou

Les logements qui ont reçu (au moins) deux voyageurs différents

Base Voyageurs

Exécuter le code
(Ctrl+S)

Réponse

select distinct l.nom as nomLogement, v1.nom as voyageur1, v2.nom as voyageur2
from Logement as l, Séjour as s1, Voyageur as v1, Séjour as s2, Voyageur as v2
where l.code = s1.codeLogement
and  l.code = s2.codeLogement
and  s1.idVoyageur =v1.idVoyageur
and  s2.idVoyageur =v2.idVoyageur
and  s1.idVoyageur != s2.idVoyageur

1. Pour les requêtes suivantes, en revanche, vous avez droit à l'imbrication (il serait difficile de faire autrement).

Nom des voyageurs qui ne sont pas allés en Corse

Base Voyageurs

Exécuter le code
(Ctrl+S)

Réponse

select v.prénom, v.nom
from Voyageur as v
where not exists (select ''
from Logement as l, Séjour as s
where l.code = s.codeLogement
and s.idVoyageur =v.idVoyageur
and lieu='Corse')

Noms des voyageurs qui ne vont qu'en Corse s'ils vont quelque part

Base Voyageurs

Exécuter le code
(Ctrl+S)

Réponse

select v.prénom, v.nom
from Voyageur as v
where not exists (select  ''
from Logement as l, Séjour as s
where l.code = s.codeLogement
and s.idVoyageur =v.idVoyageur
and lieu != 'Corse')

Nom des logements sans piscine

Réponse

select l.nom
fromLogement as l
where not exists (select ''
from Activité as a
where l.code= a.codeLogement
and a.codeActivité= 'Piscine')

Nom des voyageurs qui ne sont allés nulle part

Base Voyageurs

Exécuter le code
(Ctrl+S)

Réponse

select v.prénom, v.nom
from Voyageur as v
where not exists (select ''
from Séjour as s
where s.idVoyageur =v.idVoyageur)

Les logements où personne n'est allé

Base Voyageurs

Exécuter le code
(Ctrl+S)

Réponse

select l.nom
from Logement as l
where not exists (select ''  
from Séjour as s
where l.code =s.codeLogement)

Les voyageurs qui n'ont jamais eu l'occasion de faire de la plongée

Base Voyageurs

Exécuter le code
(Ctrl+S)

Réponse

select v.prénom, v.nom
from Voyageur as v
where not exists (select ''
from Séjour as s,  Activité as a
where s.idVoyageur =v.idVoyageur
and s.codeLogement=a.codeLogement
and a.codeActivité='Plongée')

Les voyageurs et les logements où ils n'ont jamais séjourné

Base Voyageurs

Exécuter le code
(Ctrl+S)

Réponse

select v.nom as nomVoyageur, l.nom as nomLogement
from Voyageur as v, Logement as l
where not exists (  
    select ''
    from Séjour as s
    where s.codeLogement = l.code
    and s.idVoyageur =v.idVoyageur)

Les logements où tout le monde est allé

Base Voyageurs

Exécuter le code
(Ctrl+S)

Réponse

select l.nom
from Logement as l
where not exists (  select *
    from Voyageur as v
    where not exists (     
        select * from Séjour as s
        where s.idVoyageur = v.idVoyageur
        and s.codeLogement = l.code
    )

Base de données Films

Activité 15 - Recherches sur des films et acteurs

Sql

Exécuter le code
(Ctrl+S)

1 - Nom et année de naissance des artistes né́s avant 1950

select  * from Artiste 
where annéeNaiss < 1950

2 - Titre de tous les drames

select titre from Film 
where genre = 'Drame'

3 - Quels rôles a joué Bruce Willis

select nomRôle
from Rôle as r, Artiste as a
where idActeur =idArtiste and nom =   'Willis'

4 - Qui est le réalisateur de Memento

select prénom, nom
from   Film f, Artiste a
where  f.titre = 'Memento'
and    f.idRéalisateur = a.idArtiste

5 - Qui a joué le rôle de Chewbacca?

select prénom, nom
from Rôle as r, Artiste as a
where idActeur =idArtiste
and nomRôle = 'Chewbacca'

6 - Dans quels films Bruce Willis a-t-il joué́ le role de John McClane ?

select titre
from Film as f, Rôle as r, Artiste as a
where f.idFilm=r.idFilm
and idActeur =idArtiste
and nom =   'Willis' and nomRôle='John McClane'

7 - Nom des acteurs de 'Sueurs froides'

select prénom, nom, nomRôle
from Film as f, Rôle as r, Artiste as a
where  titre = 'Sueurs froides'
and    f.idFilm = r.idFilm
and    r.idActeur = a.idArtiste

8 - Films dont le réalisateur est Tim Burton, et l’un des acteurs Jonnhy Depp.

select titre
from   Film as f, Rôle as r, Artiste as a, Artiste as b
where  f.idFilm = r.idFilm
and  r.idActeur = a.idArtiste
and    f.idRéalisateur = b.idArtiste
and     b.nom='Burton'
and    a.nom='Depp'

9 - Titre des films dans lesquels a joué́ Woody Allen. Donner aussi le rôle.

select titre, nomRôle
from   Film as f, Rôle as r, Artiste as a
where  f.idFilm = r.idFilm
and    r.idActeur = a.idArtiste
and    a.nom='Allen'
and    a.prénom='Woody'

10 - Quel metteur en scène a tourné́ dans ses propres films ? Donner le nom, le rôle et le titre des films.

select prénom, nom, nomRôle, titre
from   Rôle as r, Film as f, Artiste as a
where  f.idFilm = r.idFilm
and    f.idRéalisateur = a.idArtiste
and    r.idActeur = a.idArtiste

11 - Titre des films de Quentin Tarantino dans lesquels il n’a pas joué

select titre
from Film as f1, Artiste as s
where f1.idRéalisateur=a.idArtiste
and nom='Tarantino'
and  not exists (select '' from Rôle r
where  f.idFilm = r.idFilm
and r.idActeur=a.idArtiste)

12 - Quel metteur en scène a tourné́ en tant qu’acteur ? Donner le nom, le rôle et le titre des films dans lesquels cet artiste a joué.

select prénom, nom, nomRôle
from   Rôle as r, Film as f1, Film as f2, Artiste as a
where  f1.idFilm = r.idFilm
and    f2.idRéalisateur = a.idArtiste
and    r.idActeur = a.idArtiste

13 - Donnez les films de Hitchcock sans James Stewart

select titre from Film as f, Artiste as a
where f.idRéalisateur = a.idArtiste
and a.nom='Hitchcock'
and not exists (select '' from Artiste as a2, Rôle as r
where a2.idArtiste = r.idActeur
and r.idFilm=f.idFilm
and nom='Stewart')

14 - Dans quels films le réalisateur a-t-il le même pré́nom que l’un des interprètes ? (titre, nom du réalisateur, nom de l’interprète). Le réalisateur et l’interprète ne doivent pas être la même personne.

select a1.prénom as prénom1, a1.nom as nom1, titre,
a2.prénom as prénom2, a2.nom as nom2
from   Rôle as r, Film as f, Artiste as a1, Artiste as a2
where  f.idFilm = r.idFilm
and    a1.idArtiste != a2.idArtiste
and    f.idRéalisateur = a1.idArtiste
and    r.idActeur = a2.idArtiste
and    a1.prénom = a2.prénom

15 - Les films sans rôle

select titre
from Film
where idFilm not in  (select idFilm  from Rôle)

16 - Quels acteurs n’ont jamais réalisé de film ?

select prénom, nom
from Artiste as a
where not exists (select 'c' from Film as f
where a.idArtiste = f.idRéalisateur)

17 - Quelle est la moyenne des notes de Memento

select avg (note) as moyenne
from Film as f, Notation as n
where f.idFilm=n.idFilm
and f.titre='Memento'

18 - id, Nom et prénom des réalisateurs, et nombre de films qu’ils ont tournés.

select idArtiste, nom, prénom, count(titre) as nbFilms
from   Artiste as a, Film as f
where   f.idRéalisateur = a.idArtiste
group by  idArtiste

19 - Nom et prénom des réalisateurs qui ont tourné au moins deux films.

select  nom, prénom
from   Artiste as a, Film as f
where f.idRéalisateur = a.idArtiste
group by nom, prénom
having count(*) > 1

20 - Quels films ont une moyenne des notes supérieure à 7

select titre, avg (note) as moyenne
from Film as f, Notation as n
where f.idFilm=n.idFilm
group by f.idFilm
having avg(note) > 7

SQL Murder Mystery

Activité 16 - SQL Murder Mystery

Un meurtre a été commis. Comme vous ne prenez jamais de notes, vous ne vous souvenez plus du nom du meurtrier. Vous vous souvenez juste que le meurtre a eu lieu le 15 janvier 2018 dans la ville de SQL City.

Vous disposez ensuite de la base de données schématisée ici :

A vous de jouer :

Entrez ici vos requêtes pour mener l'enquête:

Exécuter le code
(Ctrl+S)

Vous rentrerez ensuite votre solution ici :

Votre solution:

INSERT INTO solution VALUES (1, "Entrez ici le nom du meurtrier"); SELECT value FROM solution;

Exécuter le code
(Ctrl+S)

Source: SQL Murder Mystery