Miage-BDAV

Page principale de l'EC "Bases de données avancées" en M1 Miage UNC

TP introduction à SQL - version PostgreSQL

Prise en main

Différents clients PostgreSQL sont utilisables, dont :

Requêtes simples sur la base Stanford

Créer un utilisateur de travail non privilégié avec un mot de passe. Ensuite, créer une nouvelle base de données soit avec avec une GUI, avec l’outil CLI createdb ou avec la commande SQL CREATE DATABASE. Penser à donner les droits de propriété à l’utilisateur de travail.

Télécharger le script SQL base-stanford.sql puis exécuter le dans la base nouvellement créer

  1. Donner les noms et les moyennes des candidats
  2. Donner les états des USA où se trouvent des établissements
  3. Donner les établissements, dans l’ordre alphabétique, où il y a des candidatures
  4. Donner la liste des disciplines proposées dans l’ordre alphabétique, tous établissements confondus
  5. Donner les noms des candidats et les établissements où ils candidatent
  6. Donner le nombre de candidats de chaque établissement, classés par ordre décroissant du nombre de candidatures
  7. Donner les identifiants des candidats qui ont candidaté en CS ou en EE
  8. Donner les noms des candidats qui n’ont candidaté qu’à une seule discipline

Mise-à-jour des données

Donner les requêtes SQL INSERT, DELETE, UDPATE pour :

  1. ajouter un étudiant avec les valeurs de votre choix
  2. changer le nom de l’étudiant numéro 234 en Babar
  3. mettre à NULL toutes les capacités d’accueil des établissement de Californie

Requêtes sur la base Stanford

Reprendre le premier exercice avec les requêtes classiques suivantes :

  1. Donner la liste des étudiants candidats à aucun établissement. Le faire différentes façons avec une différence (EXCEPT), une jointure ouverte (LEFT JOIN) ou une requête imbriquée (NOT IN).
  2. Donner pour chaque candidat son nombre de candidatures. Pour ceux de la réponse précédente, on veut les voir apparaître avec le nombre 0.
  3. Donner pour chaque discipline la moyenne (2 chiffres après la virgule) des GPAs des candidats, tous les établissements confondus
  4. Donner les identifiants des candidats qui ont candidaté en CS et en EE
  5. Donner la liste des noms pour lesquels il y a des homonymes.

Compléments

A faire chez soi, pour s’exercer de façon ludique. Il est important d’être à l’aise en SQL pour la suite du cours.

PostgreSQL Exercises

https://www.pgexercises.com/ propose de nombreux exercices SQL en PostgreSQL avec un schéma “réaliste”. Vous pouvez travailler en ligne ou installer la base et faire les exercices localements avec le script psql. Attention, ce n’est pas du SQL mais bien un script pour la CLI psql.

SQL Murder Mystery

Résoudre l’enquête https://mystery.knightlab.com/.

La base est disponible au format SQLite https://github.com/NUKnightLab/sql-mysteries (fichier sql-murder-mystery.db). Un script compatible PG sera extrait