TP SQL N° 2

Najib Tounsi

Soit la base de données constituée des relations (autoexplicatives) suivantes^(*):

aime
+----------+----------+
| personne | boisson  |
+----------+----------+
| ali      | coca     |
| ali      | 7up      |
| ali      | oulmes   |
| said     | coca     |
| said     | orangina |
| aziz     | oulmes   |
| aziz     | 7up      |
| amine    | coca     |
| amine    | oulmes   |
+----------+----------+


frequente
+----------+----------+
| personne | cafe     |
+----------+----------+
| ali      | rif      |
| ali      | atlas    |
| amine    | commerce |
| aziz     | rif      |
| aziz     | commerce |
| aziz     | atlas    |
| said     | atlas    |
+----------+----------+


sert
+----------+----------+
| cafe     | boisson  |
+----------+----------+
| commerce | coca     |
| commerce | 7up      |
| atlas    | 7up      |
| rif      | coca     |
| commerce | oulmes   |
| commerce | orangina |
| atlas    | oulmes   |
+----------+----------+

Ecrire en SQL les requêtes suivantes

1. Quelles sont les personnes, les cafés qu'ils fréquentent, et les boissons servies par ces cafés.
2. Quelles sont les personnes qui fréquentent  des cafés qui servent des boissons qu'ils aiment.
3. Quels sont les café servant toutes les boissons (commerce).
4. Quelles sont les personnes qui ne fréquentent que les cafés qui servent des boissons qu'ils aiment (ali).
   
5. Quelles sont les personnes qui ne fréquentent que les cafés qui servent des boissons qu'ils n'aiment pas (said).

(*): Il faut compléter cette BD avec les tables, non nécessaires ici:

TP SQL N° 3

Soit la base de donnée contenant la relation parent (parent, enfant) suivante:

parent
+--------+--------+
| parent | enfant |
+--------+--------+
| Ali    | Fatima |
| Ali    | Kacem  |
| Fatima | Amina  |
| Fatima | Aziz   |
| Kacem  | Aziza  |
| Aziz   | Saida  |
| Saida  | Farid  |
+--------+--------+

et qui signifie que "Ali" est père de "Fatima" et que celle-ci est mère de "Amina" etc.

Exercice 1. Définir les vues suivantes (ou la requête SQL pour créer la relation correspondante):

• grand-pere (gp, pf)
  
• frere (f1, f2)
• cousin (c1, c2)
• oncle (o, n)

Ne pas tenir compte du genre mâle femelle.

Exercice 2. Comment définir cette fois-ci les relations qui tiennent compte du genre mâle / femelle de la personne?

• frere (a, b) ,  soeur (a, b)       où b est le frère (resp. la sœur) de a.
• oncle (a, b) , tante (a, b)       où b est l'oncle (resp. la tante) de a.

Indication: utiliser les relations suivantes pour tester le genre:

male             femelle
+--------+	+--------+
| nom    |	| nom    |
+--------+	+--------+
| Ali    |	| Fatima |
| Aziz   |	| Saida  |
| Kacem  |	| Amina  |
| Farid  |	| Aziza  |
+--------+	+--------+

par exemple: quels sont les fils de ''Ali' ('Kacem' ici)

select enfant 
from parent 
where parent='Ali' 
and enfant in (select * from male);

ou bien

select enfant 
from parent, male
where parent='Ali'  
and enfant = male.nom;

Exercice 3. Peut-on de la même façon définir une relation ancetre (x, y) ? sachant que:

    ancetre (x, y) = parent (x, y) ou ancetre (x, z) et parent (z, y),

i.e. l'ancêtre est un parent ou un ancêtre du parent.

Indication: Réfléchir à un algorithme qui calcul progressivement les ancêtres.