Centrale 2020 - Partie SQL

Sujet intégral Informatique Centrale 2020

Partie SQL⚓︎

Base de données pour choisir des photos⚓︎

Une première étape dans la conception d’une photomosaïque est le choix d’une image source et de vignettes. Cette partie est consacrée à la sélection d’images dans la banque. Les images de la banque sont répertoriées dans une base de données dont le modèle physique est présenté figure 4, dans laquelle les clés primaires sont notées en italique.

Ecrire votre propre code, puis l'exécuter⚓︎

Votre code SQL

Les relations de l'exercice:⚓︎

tester SQL avec du code pré-saisi

Les contenu des relations:⚓︎

tester SQL avec du code pré-saisi

-- Contenu Relation Photo
INSERT INTO Photo (PH_id, PH_date, PH_larg, PH_haut, PH_auteur, PH_fichier) VALUES
(1, '2004-10-01 10:00:00', 1920, 1080, 1, 'photo_1.jpg'),
(2, '2004-10-01 11:00:00', 960, 720, 1, 'photo_2.jpg'),
(3, '2005-10-02 09:30:00', 2560, 1440, 2, 'photo_3.jpg'),
(4, '2006-10-02 12:15:00', 3000, 2000, 2, 'photo_4.jpg'),
(5, '2006-10-03 14:00:00', 1920, 1080, 3, 'photo_5.jpg'),
(6, '2007-10-03 15:30:00', 1280, 720, 3, 'photo_6.jpg'),
(7, '2010-10-04 08:45:00', 2560, 1440, 4, 'photo_7.jpg'),
(8, '2015-10-04 10:20:00', 3000, 2000, 4, 'photo_8.jpg'),
(9, '2017-10-05 09:00:00', 1920, 1080, 5, 'photo_9.jpg'),
(10, '2020-10-05 10:15:00', 1280, 720, 5, 'photo_10.jpg'),
(11, '2022-10-06 14:30:00', 2560, 1440, 1, 'photo_11.jpg'),
(12, '2024-10-06 16:00:00', 3000, 2000, 1, 'photo_12.jpg'),
(13, '2024-10-07 11:00:00', 1920, 1080, 2, 'photo_13.jpg'),
(14, '2024-10-07 13:30:00', 1280, 720, 2, 'photo_14.jpg'),
(15, '2024-10-08 09:45:00', 2560, 1440, 3, 'photo_15.jpg'),
(16, '2024-10-08 11:00:00', 3000, 2000, 3, 'photo_16.jpg'),
(17, '2024-10-09 10:00:00', 1920, 1080, 4, 'photo_17.jpg'),
(18, '2024-10-09 12:30:00', 1280, 720, 4, 'photo_18.jpg'),
(19, '2024-10-10 14:15:00', 2560, 1440, 5, 'photo_19.jpg'),
(20, '2024-10-10 16:00:00', 3000, 2000, 5, 'photo_20.jpg');

-- Contenu Relation Personne
INSERT INTO Personne (PE_id, PE_sexe, PE_prenom) VALUES
(1, 'F', 'Alice'),
(2, 'M', 'Bernard'),
(3, 'M', 'Charles'),
(4, 'F', 'Diane'),
(5, 'M', 'Eric'),
(6, 'F', 'Fiona'),
(7, 'M', 'Gustave'),
(8, 'F', 'Hélène'),
(9, 'M', 'Igor'),
(10, 'F', 'Julie'),
(11, 'M', 'Kevin'),
(12, 'F', 'Laura');

-- Contenu Relation Present
INSERT INTO Present (PE_id, PH_id) VALUES
(1, 1),  -- Alice dans la photo 1
(2, 1),  -- Bernard dans la photo 1
(3, 1),  -- Charles dans la photo 1
(4, 1),  -- Diane dans la photo 1
(5, 1),  -- Eric dans la photo 1
(1, 2),  -- Alice dans la photo 2
(2, 2),  -- Bernard dans la photo 2
(1, 3),  -- Alice dans la photo 3
(2, 3),  -- Bernard dans la photo 3
(3, 3),  -- Charles dans la photo 3
(4, 3),  -- Diane dans la photo 3
(5, 3),  -- Eric dans la photo 3
(1, 4),  -- Alice dans la photo 4
(2, 4),  -- Bernard dans la photo 4
(1, 5),  -- Alice dans la photo 5
(3, 5),  -- Charles dans la photo 5
(6, 6),  -- Fiona dans la photo 6
(7, 7),  -- Gustave dans la photo 7
(8, 8),  -- Hélène dans la photo 8
(9, 9),  -- Igor dans la photo 9
(10, 10), -- Julie dans la photo 10
(11, 11), -- Kevin dans la photo 11
(12, 12), -- Laura dans la photo 12
(1, 12), -- Alice dans la photo 12
(2, 12); -- Bernard dans la photo 12

-- Contenu Relation Motcle
INSERT INTO Motcle (MC_id, MC_texte) VALUES
(1, 'surf'),
(2, 'nature'),
(3, 'portrait'),
(4, 'architecture'),
(5, 'sport');

-- Contenu Relation Decrit
INSERT INTO Decrit (MC_id, PH_id) VALUES
(1, 1),  -- Photo 1 avec le mot clé "surf"
(1, 3),  -- Photo 3 avec le mot clé "surf"
(2, 1),  -- Photo 1 avec le mot clé "nature"
(2, 3),  -- Photo 3 avec le mot clé "nature"
(1, 5),  -- Photo 5 avec le mot clé "surf"
(2, 2),  -- Photo 2 avec le mot clé "nature"
(3, 4),  -- Photo 4 avec le mot clé "portrait"
(4, 6),  -- Photo 6 avec le mot clé "architecture"
(5, 7);  -- Photo 7 avec le mot clé "sport"

Questions⚓︎

Les questions

Q 19. Écrire une requête SQL donnant les identifiants de toutes les photographies au ratio 4:3, c’est-à-dire dont le rapport largeur sur hauteur vaut exactement 4/3.

Q 20. Écrire une requête qui compte le nombre de photos prises par « Alice » ou « Bernard ».

Q 21. Écrire une requête qui fournit l’identifiant et la date des photographies prises avant 2006 et associées au mot-clé « surf »

Q 22. Écrire une requête qui donne le prénom de l’auteur et l’identifiant de tous les selfies, c’est-à-dire les photographies sur lesquelles l’auteur est présent.

Q 23. Écrire une requête qui sélectionne toutes les photographies sur lesquelles sont présents « Alice » et « Bernard », à l’exclusion de toute autre personne.

Réponses

Q 19.

SELECT PH_id FROM Photo WHERE PH_larg * 4 = PH_haut * 3 OR PH_larg * 3 = PH_haut * 4

Q 20.

SELECT COUNT(*) FROM Photo JOIN Personne ON PH_auteur = PE_id WHERE PE_prenom = "Alice" OR PE_prenom = "Bernard"

Q 21.

SELECT PH_id,PH_date From Photo NATURAL JOIN Decrit NATURAL JOIN Motcle WHERE MC_texte = "surf" AND PH_date < '2006-01-01'

Q 22. SELECT PH_id, PE_prenom FROM Photo JOIN Personne ON PH_auteur = PE_id NATURAL JOIN Present WHERE PH_auteur = Present.PE_id

Q 23.

-- Relation des photos ayant exactement deux personnes présentes

SELECT PH_id FROM Photo NATURAL JOIN Present NATURAL JOIN Personne GROUP BY PH_id HAVING COUNT(PE_id) = 2

EXCEPT

-- Relation des photos qui contiennent des personnes autre que Bernard ou Alice

SELECT PH_id FROM Photo NATURAL JOIN Present NATURAL JOIN Personne WHERE NOT PE_prenom IN ("Alice", "Bernard")