TD 3.02 — LaRaquette, la suite

SELECT DISTINCT a.ano, a.anom
FROM abonne a
JOIN abonne f ON f.parrain = a.ano;

Le parrain est repéré par la clé étrangère parrain de la table abonne elle-même : il faut donc une auto-jointure — a joue le parrain, f le filleul. Un filleul référence exactement un parrain, mais un parrain peut avoir plusieurs filleuls : d'où le DISTINCT pour ne pas répéter un parrain deux fois.

SELECT a.ano, a.anom, COUNT(*) AS nb
FROM abonne a
JOIN reservation r ON r.respno = a.ano
GROUP BY a.ano, a.anom
ORDER BY nb DESC;

Une réservation compte par ligne de reservation où l'abonné est responsable (respno). Variantes selon la consigne : compter aussi les invitations (invitno), ou ajouter LEFT JOIN pour afficher aussi les abonnés sans réservation (avec 0). Attention : on groupe par les deux colonnes affichées, sinon PostgreSQL refuse.

SELECT tno, jour, heure
FROM reservation
GROUP BY tno, jour, heure
HAVING COUNT(*) > 1;

Le créneau d'un terrain est identifié par le triplet (tno, jour, heure). Si ce triplet apparaît plus d'une fois, deux réservations se chevauchent : c'est l'anomalie. Le filtre porte sur un résultat de regroupement → HAVING, pas WHERE.

SELECT r.respno
FROM reservation r
JOIN terrain t ON t.tno = r.tno
GROUP BY r.respno
HAVING COUNT(DISTINCT t.type) > 1;

Le piège : ce n'est pas « plusieurs réservations » mais plusieurs sports. Un client qui a réservé trois fois du tennis ne compte pas. Il faut rejoindre terrain et compter les types distincts (COUNT(DISTINCT …)), pas les lignes.

SELECT t.tno, t.tnom, c.jour, c.heure
FROM terrain t
CROSS JOIN creneau c
WHERE c.jour = '2023-09-16'
  AND NOT EXISTS (
    SELECT 1 FROM reservation r
    WHERE r.tno = t.tno
      AND r.jour = c.jour
      AND r.heure = c.heure
  );

Un créneau est « disponible » pour un terrain s'il n'existe aucune réservation pour ce couple (terrain, créneau). On fabrique toutes les combinaisons avec un CROSS JOIN, puis on élimine celles réservées avec NOT EXISTS.

SELECT parrain
FROM abonne
WHERE parrain IS NOT NULL
GROUP BY parrain
HAVING COUNT(*) = (
  SELECT MAX(nb) FROM (
    SELECT COUNT(*) AS nb
    FROM abonne
    WHERE parrain IS NOT NULL
    GROUP BY parrain
  ) compteur
);

Deux pièges. Un : exclure les NULL — « parrain inconnu » n'est pas un parrain. Deux : LIMIT 1 renverrait un seul ex æquo, alors que l'énoncé demande « des abonnés si égalité ». La méthode robuste : comparer le compte de chaque parrain au maximum global calculé en sous-requête.

SELECT anom, prenom, datnaiss
FROM abonne
ORDER BY datnaiss ASC;

Piège classique : trier par âge décroissant, c'est trier par date de naissance croissante — le plus âgé est né le plus tôt. Oublier l'inversion donne l'ordre exactement contraire.

SELECT anom, prenom, dateFinAbo
FROM abonne
WHERE dateFinAbo < CURRENT_DATE;

CURRENT_DATE donne la date du jour au serveur. Strictement avant : un abonnement qui finit aujourd'hui est encore valable.

SELECT anom, prenom
FROM abonne
WHERE EXTRACT(MONTH FROM datnaiss) = EXTRACT(MONTH FROM CURRENT_DATE)
  AND EXTRACT(DAY   FROM datnaiss) = EXTRACT(DAY   FROM CURRENT_DATE);

On ne peut pas comparer les dates directement : l'année diffère. On compare mois puis jour séparément. Alternative : to_char(datnaiss, 'MM-DD') = to_char(CURRENT_DATE, 'MM-DD').

SELECT anom, prenom, adresse
FROM abonne
WHERE adresse ILIKE '%place%' AND adresse ILIKE '%lille%';

Deux mots dans l'adresse, pas forcément l'un à côté de l'autre : donc deux LIKE, pas un seul motif « place … lille ». % remplace n'importe quelle chaîne. ILIKE (ou LOWER(adresse) LIKE '%place%') rend la recherche insensible à la casse.

DELETE FROM reservation
WHERE utilise = false;

utilise est un booléen : WHERE NOT utilise est équivalent. Réflexe avant tout DELETE : lancer SELECT COUNT(*) FROM reservation WHERE utilise = false; pour mesurer la casse.

DELETE FROM abonne
WHERE dateFinAbo < CURRENT_DATE - INTERVAL '1 year';

CURRENT_DATE - dateFinAbo > 365 marche presque, mais se plante les années bissextiles — l'arithmétique d'intervalle (INTERVAL '1 year') est la version propre. Attention aussi : si des réservations référencent l'abonné, la clé étrangère peut refuser la suppression (prévoir ON DELETE CASCADE ou supprimer dans le bon ordre).

SELECT r3.jour, r3.heure
FROM reservation r3
WHERE r3.tno = 3
  AND r3.jour = '2023-09-11'
  AND NOT EXISTS (
    SELECT 1 FROM reservation r2
    WHERE r2.tno = 2
      AND r2.jour = r3.jour
      AND r2.heure = r3.heure
  );

La question retourne les créneaux du terrain 3 libres sur le terrain 2. S'il ne renvoie aucune ligne, alors le terrain 2 est libre pour toutes les réservations du 3 — condition indispensable avant la question 4. C'est exactement le NOT EXISTS de la question 5, appliqué à un cas réel.

UPDATE reservation
SET tno = 2
WHERE tno = 3
  AND jour = '2023-09-11';

La bascule ne se fait qu'après la vérification de la question 3 — c'est le scénario raconté par l'énoncé (« après vérification on se rend compte que le 2 était libre »). Le WHERE porte sur le jour exact : sans lui, on déplacerait toutes les réservations du terrain 3, tous jours confondus.

UPDATE abonne
SET dateFinAbo = dateFinAbo + INTERVAL '1 year'
WHERE dateFinAbo <= CURRENT_DATE + INTERVAL '1 year'
  AND dateFinAbo >= CURRENT_DATE
  AND ano IN (
    SELECT parrain FROM abonne
    WHERE parrain IS NOT NULL
    GROUP BY parrain
    HAVING COUNT(*) >= 3
  );

Trois conditions à ne pas mélanger. « Abonnement en cours, s'achèvera dans l'année » : la date de fin est comprise entre aujourd'hui et aujourd'hui + 1 an. « A parrainé au moins trois personnes » : sous-requête `GROUP BY parrain HAVING COUNT(*)

= 3`. Et l'ajout se fait en arithmétique d'intervalle, jamais en ajoutant un entier à la date.