Aller au contenu

C11 Introduction aux bases de données

" A SQL query walks into a bar and sees two tables. He walks up to them and says ‘Can I join you?"
Anonyme

Cours

Support de cours chapitre 11

Attention

Ce diaporama ne vous donne que quelques points de repères lors de vos révisions. Il devrait être complété par la relecture attentive de vos propres notes de cours et par une révision approfondie des exercices.

Travaux dirigés

Fiche de TD11

Travaux pratiques

â–ª Exercice 1 : Dette et croissance

  1. Télécharger les données présentées dans la vidéo précédente sous la forme d'une base de données : Dette et Croissance et l'ouvrir à l'aide de sqlitebrowser. Cette base est composée d'une seule table :
    RR
    IdINTEGER
    CountryTEXT
    YearINTEGER
    DebtToGDPREAL
    GrowthREAL
    DebtCategoryINTEGER
    On précise la signification des champs suivants :
    DebtToGDP : la dette en pourcentage du pib.
    Growth : la croissance.
  2. Quelques requêtes pour s'échauffer
    1. Vérifier comme indiqué dans la vidéo que les données contiennent bien 20 pays. Les lister.
    2. Vérifier aussi que les années vont de 1946 à 2009 comme indiqué dans la vidéo.
    3. Lister par ordre croissant les dix années où la croissance a été la plus faible en France.
    4. Lister les pays où la croissance a dépassé 15 %

  3. Des opérations sur les colonnes

    1. Ecrire une requête sql permettant d'obtenir le pays et l'année où la dette a été la plus importante en utilisant la fonction d'agrégation max

      Note

      On pourrait aussi écrire une requête de classe par ordre décroissant associée à une clause LIMIT

  4. Retrouver à l'aide de sql les vraies valeurs de croissance moyenne en fonction de la catégorie de dette dans laquelle se trouve le pays et qu'on rappelle ci-dessous :

    Catégorie Croissance moyenne
    1 4,2 %
    2 3,1 %
    3 3,2 %
    4 2,2 %

  5. Les opérations sur les colonnes sont particulièrement utiles en lien avec la clause GROUP BY. Par exemple, si on souhaite obtenir la somme de la croissance pour chacun des 20 pays depuis 1970 : 

        SELECT country, SUM(Growth) from RR WHERE Year>=1970 GROUP BY Country

    On peut même renommer la colonne SUM(Growth) des résultats grâce à AS et donc l'utiliser comme critère de classement:

    sql SELECT country, SUM(Growth) AS moy70 from RR WHERE Year>=1970 GROUP BY Country ORDER BY moy70 DESC

    Ecrire une seule requête permettant d'obtenir les taux de croissance moyenne pour chacune des catégories de dette.

â–ª Exercice 2 : Prix Nobel

  1. Télécharger ci-dessous une base de données des Prix Nobel : Prix Nobel
  2. Ouvrir cette base avec sqlitebrowser
  3. Dans Parcourir les données prendre note du noms des colonnes et de leur signification.
  4. Ecrire les requêtes sql permettant :
    1. d'obtenir les catégories dans lesquelles sont attribuées les prix Nobel,
    2. de lister par ordre alphabétique les lauréats du prix Nobel nés en France ou travaillant pour une organisation Française,
    3. de lister les années où le "Comité international de la Croix Rouge" a obtenu le prix Nobel,
    4. de connaître le nombre de femmes ayant obtenu un prix Nobel,
    5. de lister par âge décroissant les lauréats du prix Nobel qui sont toujours en vie,
    6. de rechercher les lauréats dont le nom contient "Curie".

â–ª Exercice 3 : Tremblements de terre

  1. Télécharger ci-dessous une base de données des tremblements de terre : Tremblements de terre
  2. Ouvrir cette base avec sqlitebrowser
  3. Dans Parcourir les données prendre note du noms des colonnes et de leur signification.

  4. Ecrire les requêtes sql permettant :

    1. de lister tremblements de terre qui se sont produits au Japon,

    2. de lister les tremblements de terre qui se sont produits en l'an 2000 classés par magnitude,

      Aide

      Remarquer que le champ occured_on qui indique la date est au format texte.

  5. Utiliser cette base pour répondre aux questions suivantes :

    1. les tremblements de terre sont-ils plus fréquents dans l'hémisphère nord ou l'hémisphère sud ?

      Aide

      On rappelle que l'hémisphère nord correspond à une latitude positive et le sud à une latitude négative.

    2. Où étaient effectuées les essais nucléaires français ?

    3. L'un des séisme de cette base de données s'est-il produit à proximité de l'île de la Réunion ?

      Aide

      Rechercher la latitude et la longitude de l'île de Réunion et considérer que "à proximité" signifie qu'on s'écarte de moins de 10 degrés de latitude ou de longitude

â–ª Exercice 4 : Exercices en ligne (une seule table)

  1. Des exercices en lignes de requêtes sur une seule table crées par N. Reveret. Ces exercices peuvent être faits à la maison (aucune installation nécessaire) et les réponses sont fournis.

    1. Les prénoms en France au XX-ème siècle
    2. Ramens

  2. Même principe sur ce site de Q. Fortier :

    1. Métro parisien
    2. Pays

  3. Enfin, un site (en anglais) qui vous permettra de réviser l'ensemble de ce chapitre en faisant les tutoriels de 1 à 5:

    1. Tutoriel 1 : introduction
    2. Tutoriel 2 : requêtes avec conditions partie 1
    3. Tutoriel 3 : requêtes avec conditions partie 2
    4. Tutoriel 4 : filtrage et tri des résultats
    5. Tutoriel 5 : exercice de synthèse

▪ Exercice 5 : Une enquête policière avec SQL

Le but de cette activité, proposé sur le site de l'université américaine Northwerstern University est de résoudre un crime en utilisant des requêtes SQL dans une base de données dont voici le schéma relationnel : schemabase

Toutes les requêtes peuvent être exécutées directement sur le site, cependant vous pouvez télécharger ci-dessous la base afin d'exécuter vos requêtes en local : SQL Murder Mystery

Cliquer sur l'image ci-dessous pour accéder à l'enquête et trouver le coupable. illustration

â–ª Exercice 6 : Exercices en ligne de SQL (plusieurs tables)

  1. Des exercices en lignes de requêtes sur plusieurs tables crées par N. Reveret. Ces exercices peuvent être faits à la maison (aucune installation nécessaire) et les réponses sont fournies.

    1. Autour des films
    2. Voitures
    3. World
    4. Netflix

  2. Même principe sur ce site de Q. Fortier avec bases de données sur :

    1. Les concours
    2. Les Pokémon

▪ Exercice 7 : SQL Island : un jeu de rôle en SQL

Un jeu de rôle en SQL où vous vous retrouvez seul sur une île après un crash d'avion (site d'une université allemande)

Aide

Pour changer la langue (initialement en allemand), cliquer sur le menu (en haut et à gauche de la page du jeu) puis sélectionner le 4eme item.

Humour d'informaticien

punition