Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

1 DEVOIR Semestre : 1er Session : Principale Module : Bases de données utilisat

1 DEVOIR Semestre : 1er Session : Principale Module : Bases de données utilisation avancée Enseignant(s) : Faten Labbene Ayachi Classes : 3 LFIG2 Documents autorisés : NON Nombre de pages : 1 Date : 13/11/2019 Heure: 10h30 Durée : 1H30. Exercice 1 : SQL (10 points = 2pts * 5) On rappelle le schéma relationnel suivant : PILOTE (plunum, plnom, plprenom, ville, salaire) AVION (avnum, avnom, capacité, localisation) VOL (volnum, plnum#, avnum#, villedep, villearr, heuredep, heurearr) Les attributs soulignés correspondent aux clés primaires, les attributs suivis de # correspondent aux clés étrangères. Ecrire les requêtes suivantes dans le langage SQL. Indication : Vous utiliserez pour R1 et R3 les deux opérateurs MINUS et NOT EXISTS ; pour la requête R2 les deux opérateurs INTERSECT et EXISTS. R1 : Numéros des pilotes conduisant exactement les mêmes avions que le pilote dont le plnum=1. R2 : Numéros des pilotes conduisant au moins un même avion que le pilote dont le plnum=1. R3 : Numéros des pilotes conduisant toutes les avions conduites par le pilote dont le plnum=1. R4 : Nombre de vols assurés par le pilote dont le nom est Lakhal. R5 : Le nom du pilote assurant le nombre maximum de vols dont la ville de départ est Tunis. Exercice 2 : (10 points) Soit le schéma relationnel suivant : Personne (numPers, nom, prenom, sexe, numpere#, nummere#) union (numMari#, numEpouse#) Les attributs soulignés correspondent aux clés primaires, les attributs suivis de # correspondent aux clés étrangères. Ecrire les requêtes suivantes dans le langage SQL : Q1 (4 pts) : Ecrire les commandes SQL qui permettent de créer les deux tables Personne et Union. Q2 (1,5 pts) : Quels sont les prénoms des enfants de Ali AGHA ? Q3 (1,5 pts) : Quel est le nom de la femme de Ali AGHA ? Q4 (1 pt) : Quels sont les numéros des oncles paternels et des tantes paternelles de Ali AGHA ? Q5 (2 pts) : Quels sont les numéros des grands-parents et tous les aïeuls (arrière, arrière, …, grands- parents) paternels de Ali AGHA ? 2 Correction devoir du 13/11/2019 Exercice I : Requêtes SQL R1 : Numéros des pilotes conduisant exactement les mêmes avions que le pilote dont le plnum=1. select plnum from pilote where not exists ( select avnum from vol where plnum = 1 minus select avnum from vol where (pilote.plnum=vol.plnum) and (plnum<>1) ) and not exists ( select avnum from vol where (pilote.plnum=vol.plnum) and (plnum<>1) Minus select avnum from vol where plnum = 1 ) ; R2 : Numéros des pilotes conduisant au moins un même avion que le pilote dont le plnum=1. select plnum from pilote where exists ( select avnum from vol where (pilote.plnum=vol.plnum) and (plnum<>1) intersect select avnum from vol where plnum = 1 ) R3 : Numéros des pilotes conduisant toutes les avions conduites par le pilote dont le plnum=1. select plnum from pilote where not exists ( select avnum from vol where plnum = 1 minus select avnum from vol where (pilote.plnum=vol.plnum) 3 and (plnum<>1) ) ; select plnum from vol v1 where plnum <>1 and avnum in (select avnum from vol v2 where (v2.plnum = 1) ) Group by plnum Having count(distinct avnum)= (select count(distinct avnum) from vol v3 where (v3.plnum = 1) ) R4 : Nombre de vols assurés par le pilote dont le nom est Lakhal. select count(*) from vol where plnum in (select plnum from pilote where upper(plnom)=’LAKHAL’) ; R5 : Le nom du pilote assurant le nombre maximum de vols dont la ville de départ est Tunis. select plnom from vol, pilote where (pilote.plnum=vol.plnum) and (upper(villedep)=’TUNIS’) group by pilote.plnum having count(*) = (select max(count(*)) from vol where upper(ville)=’TUNIS’ group by plnum ) Exercice II : Requêtes SQL 1. create table Personne ( numPers int primary key, nom varchar(20), prenom varchar(20), sexe char(1), numPere int references Personne(numPers), 4 numMere int references Personne(numPers) ) ; create table Union ( numMari int references Personne(numPers), numEpouse int references Personne(numPers), constraint union_PK primary key(numMari, numEpouse) ) ; 2. Quels sont les enfants de Ali AGHA ? select A.nom from Personne a, Personne b where (a.numPere=b.numPers) and (upper(b.nom)=’ALI’) and (upper(b.prenom)=’AGHA’); 3. Quel est le nom de la femme de Ali AGHA ? select a.nom from Personne a, Personne b, Union C where (c.numMari=b.num) and (upper(b.nom)=’ALI’) and (upper(b.prenom)=’AGHA’) and (c.NumEpouse=a.numPers); 4. Quels sont les oncles et les tantes paternels de ALI AGHA ? select a.numPers from Personne a, Personne b, Personne c where ((a.numPere=b.numPere)or(a.numMere=b.numMere))and (upper(c.nom)=’SALMA’) and (upper(c.prenom)=’BELLAGHA’) and (c.numPere=b.numPers); 5. Quels sont les numéros des grands-parents et tous les aïeuls (arrière, arrière, …, grands-parents) paternels de Ali AGHA ? select a.nom, a.prenom from Personne where numPers in (select numPere from personne connect by numPers = prior numpere start with numPers in (select numpers from personne 5 where upper(b.nom)=’ALI’) and(upper(b.prenom)=’AGHA’); uploads/Geographie/ devoir-3lifg-2019-corr.pdf