TD et TP 21 à 29 — Synthèse

Les TD et TP de R2.06 avancent par paires : le TD prépare la technique, le TP la rejoue sur machine. Voici le déroulé — les exercices types et les pièges de chaque semaine.

Semaines 1-2 — Premier contact : Access et les jointures

Les TD 21-22 travaillent la base théâtre (spectacle, acteur, jouer, representations) : restrictions, projections, LIKE 'D%', tris multi-critères (Q1-Q15), puis jointures SQL-89 et SQL-92 en parallèle. Exercices clés : les couples d'acteurs jouant dans un même spectacle (auto-jointure j1.sno = j2.sno), les doublons par symétrie à éliminer (j1.nom_acteur < j2.nom_acteur), les spectacles sans acteur et les acteurs avec éventuellement leur spectacle — deux anti-jointures LEFT JOIN … IS NULL. Le TP 21 (Access) importe import.txt (numetu;nom;prenom;groupe;mat;numcont;coeff;note), ventile vers etudiants, controles, notes (146, 12 puis 1747 lignes), puis enchaîne les requêtes en mode SQL ; son exercice « for fun » fait compter SELECT * FROM controles, bidon — 60 lignes, le produit cartésien. Le TP 22 couvre calculs (LEFT$, fréquences des prénoms ORDER BY … DESC) et regroupements (COUNT par groupe, HAVING COUNT(*) > 1, moyennes coefficientées).

Semaines 3-4 — Sous-requêtes, passage à PostgreSQL

Changement d'outil : psql -h psqlserv -U prenomnometu but1, avec \d, \d table, \i. TD 23-24 : moyenne coefficientée, « moins cher que la moyenne » (scalaire), « au tarif le plus élevé » (comparaison au MAX), > ANY (moins cher qu'au moins un spectacle de Delon), puis les corrélatives : tarifs supérieurs à la moyenne du même jour, spectacles toujours joués le même jour de la semaine, spectacles joués tous les jours entre première et dernière représentation, acteurs dans tous les spectacles d'Auteuil (double NOT EXISTS). Le TP 23 rejoue tout sur la base etudiants et ajoute les tableaux croisés (\crosstabview) et l'histogramme des notes avec repeat('*', freq). Le TP 24 fait réécrire des requêtes (EXISTS → IN, NOT IN avec HAVING → LEFT JOIN … IS NULL), puis des mises à jour sur une copie (CREATE TABLE etudiants AS SELECT * FROM PUBLIC.etudiants) : DELETE, réinsertion, RETURNING *.

Semaines 5-6 — Le DDL : trafic aérien

TD 25 : du MCD aérien au schéma, puis au script — rno en CHAR(3), lno en INTEGER, pno en SERIAL (trois codages de clé), ON UPDATE CASCADE partout, ON DELETE SET NULL pour ligne et CASCADE ailleurs, CHECK (places BETWEEN 100 AND 500), données pilote obligatoires. Suit l'exercice qui apprend tout : 21 INSERT à accepter ou refuser en justifiant (doublon, CHECK, clé étrangère inexistante, NULL interdit), puis 10 mises à jour (UPDATE croisé de clés, DELETE en cascade…). TP 25 : un script fournisseur.sql ré-exécutable, 9 commandes insérées en une requête (produit cartésien personnes × produits), découverte du TRUNCATE … RESTART IDENTITY. TP 26 : tables TEMPORARY, 64 pilotes créés par produit cartésien 4×4×4, CREATE TABLE … AS SELECT (compagnies, modeles), CREATE SEQUENCE numeros START 130 + nextval, complétion ville = libelle quand NULL, suppression des lignes vers Lannion sauf ORY/CDG, et la table des correspondances.

Semaines 7-8 — Vues, droits, renormalisation

TD 27 : la base de notes (etudiant, controle, note), les vues par catégorie (étudiants/enseignants/secrétaires), la politique de sécurité en notations L/E/M/S/T, puis les ordres GRANT/REVOKE et les scénarios de délégation. TP 27 : table jeu/scores, vue messcores filtrée sur USER (dynamique : un INSERT y apparaît aussitôt), \dv, \sv, GRANT USAGE ON SCHEMA, vérification \z, insertion à travers la vue via CREATE RULE … DO INSTEAD, et le travail collaboratif « communes » où l'on trace qui saisit (USER), quand (CURRENT_TIMESTAMP) et d'où (inet_client_addr()) sans le montrer aux contributeurs. TD 28 (rappels, base cinéma) : homonymes, « toujours projetés dans une salle plus grande que la moyenne », décalage d'une semaine, type INTERVAL, vue chevauchements. TP 28 : renormalisation de pers(nom, prenom) vers noms, prenoms et l'association asso — avec puis sans NULL (4 lignes, puis 7) —, vue result de reconstruction et preuve d'équivalence ensembliste ; enfin requêtes sur la méta-base pg_* et sur information_schema (qui a fait le TP aviation ? qui n'a pas fait son boulot ?).

Semaine 9 — Données réelles : la SAé

TD 29 : un tableur électoral simplifié (9 communes, 3 candidats) — avantages/inconvénients, problèmes de données, MCD, MLD, puis requêtes de ventilation depuis la table import. TP 29 : le vrai fichier des résultats par bureau de vote de 2022 — 105 colonnes, iso-8859-1 → utf-8 par iconv, head/wc, table temporaire import à 98 colonnes, \copy import from data.csv delimiter ';', ventilation vers candidats/departements/communes/bureaux/ votes (les voix des 12 candidats empilées par 12 UNION ALL), ajout des clés primaires/étrangères mesuré avec \timing et \di, fouille de données comparée à Wikipédia, export final par \copy … TO.

---