Skip to content

III. Structured Query Language (SQL)

Cours : Le SQL

Diaporama de cours

Notebook associé : sur Capytale, code f997-1034988

TD : Le langage SQL

Fichier PDF

Un magasin de meubles veut organiser son inventaire et ses ventes. Pour cela, il crée trois tables, une contenant l’ensemble des produits disponibles à la vente, une contenant la liste des clients enregistrés, et une contenant l’ensemble des achats. Les tables sont construites selon ce modèle :

Produits :

Identifiant Nom Prix
01252369 Lit simple 150
98563242 Lit double 250
65872304 Table 90
66320520 Chaise 40
33220141 Bureau 110

Clients :

Identifiant Nom Prénom
001235 Arnot Amanda
005463 Brahms Bruno
006522 Crestin Céline
008563 Dubois Damien

Achats :

ID Client Produit Date
1 001235 66320520 01/02/2020
2 001235 66320520 01/02/2020
3 001235 66320520 01/02/2020
4 001235 66320520 01/02/2020
5 001235 65872304 01/02/2020
6 005463 98563242 25/10/2019
7 006522 33220141 15/06/2021

  1. Que représentent les attributs Client et Produit dans la table Achats ?

  2. Compléter les commandes suivantes, permettant de créer les tables Clients et Achats, avec les domaines des attributs et les clés :

    Code

    CREATE TABLE Clients(
    Identifiant ... 
    Nom ...
    Prénom ...


);

CREATE TABLE Achats(
    ID ... 
    Client ...
    Produit ...
    Date ...


);

  3. L’entreprise aimerait connaître tous ses produits valant plus de 100 euros. Ecrire la commande permettant de lire tous les éléments de la table Produits dont le prix est supérieur à 100 euros.

  4. L’entreprise aimerait trier ses produits par ordre de référence croissant. Ecrire la commande correspondante.

  5. Ecrire la commande permettant de compter le nombre de transactions réalisées après le 1er janvier 2020.

  6. Donner le résultat de cette commande :

    Code

    SELECT COUNT(DISTINCT Client) FROM Achats ;

  7. Damien vient d’acheter un bureau. Ecrire la commande permettant de mettre à jour la table des achats.

  8. Lors des soldes, le prix du lit simple est baissé à 120 euros. Mettre à jour la table des produits.

  9. Sélectionner le nom des produits qui ont été achetés, sans avoir de doublons.

Cours : Les SGBD

Diaporama de cours

TP : Manipulation d'une table en SQL

Fichier PDF

Dans ce TP, nous allons implémenter la table contenant les figures de l’informatique, et la manipuler avec le SGBD SQLite.

Instructions

Télécharger une console sqlite3, depuis l'ENT. Télécharger aussi la base de données > Figures.db <.
Noter les requêtes utilisées sur votre cahier de TP.

  1. Lancer SQLite, et taper la commande .open Figures.db. Cette commande ouvre le fichier contenant la base de données et importe son contenu.

  2. La base de données a déjà été créée : consulter les différentes tables qu'elle contient avec la commande .table. Consulter les schéma relationnels des tables avec la commande .schema.

  3. On utilise la même table que dans le TP d'introduction. Des enregistrements ont été ajoutés à la base de données. Regarder ce qu'elle contient, en utilisant une requête de sélection.

    Les enregistrements suivants doivent apparaître :

    1 | Al-Khwarismi | Muhammad | 780 | ouzbek
    2 | Hamilton | Margaret | 1936 | américaine
    3 | Lovelace | Ada | 1815 | britannique
    4 | Knuth | Donald | 1938 | américaine
    5 | Chomsky | Noam | 1928 | américaine
    6 | Lamarr | Hedy | 1914 | autrichienne
    7 | Turing | Alan | 1912 | britannique
    8 | Berners-Lee | Tim | 1954 | britannique

  4. Ecrire une requête d'insertion, pour insérer les n-uplets suivants :

    Code

    (9, Hopper, Grace, 1906, américaine)
(10, Von Neumann, John, 1903, hongroise)

  5. Ecrire les requêtes de sélection permettant de récupérer :

    • l'intégralité de l'enregistrement d'identifiant 4.
    • l’année de naissance d’Alan Turing.
    • Ecrire la commande permettant de récupérer le nom et le prénom des personnes de nationalité britannique de cette table, dans l’ordre croissant des dates de naissance.

  6. Ecrire la requête permettant de compter toutes les personnes nées après l’année 1930.

  7. On se rend compte que la date de Tim Berners-Lee est fausse, car il est en réalité né en 1955.
    Ecrire la requête de mise à jour permettant d'actualiser la date de naissance de Tim Berners-Lee avec la bonne valeur.

  8. On décide que Noam Chomsky n’est plus digne d’appartenir à cette table. Supprimer Noam Chomsky de cette liste.

TP : Manipulation de plusieurs tables en SQL

Fichier PDF

Instructions

Nous allons utiliser à nouveau SQLite, pour manipuler des bases de données contenant plusieurs tables. Pour garder une trace des requêtes écrites, noter les requêtes utilisées sur votre cahier de TP.

I. Une base de données de livres

Télécharger la base de données > Bibliotheque.db <.

On s'intéresse à une base de données de livres, avec une table Livres et une table Auteurs.

II. 1. Création des tables

  1. Le schéma relationnel de la table Auteurs est le suivant :
    {id : entier, nom : caractères, prenom : caractères, ann_naissance : entier, langue_ecriture : caractères} avec id la clé primaire.
    Vérifier que ce schéma correspond à la table implémentée avec l'instruction .schema.

  2. On définit la table Livres de la manière suivante :
    {id : entier, titre: caractères, id_auteur: entier, ann_publi: entier, note: entier}
    Grâce à l'instruction sqlite .schema, identifier :
    - la clé primaire :
    - la clé étrangère, et la clé primaire à laquelle elle fait référence :
    - les domaines SQL des attributs :

  3. Vérifier que les tables contiennent bien les enregistrements suivants :

    • La table Auteurs :
    id nom prenom ann_naissance langue_ecriture
    1 ‘Orwell’ ‘George’ 1903 ‘anglais’
    2 ‘Herbert’ ‘Frank’ 1920 ‘anglais’
    3 ‘Asimov’ ‘Isaac’ 1920 ‘anglais’
    4 ‘Huxley’ ‘Aldous’ 1894 ‘anglais’
    5 ‘Bradbury’ ‘Ray’ 1920 ‘anglais’
    6 ‘K.Dick’ ‘Philip’ 1928 ‘anglais’
    7 ‘Barjavel’ ‘René’ 1911 ‘français’
    • la table Livres :
    id titre id_auteur ann_publi note
    1 ‘1984’ 1 1949 10
    2 ‘Dune’ 2 1965 8
    3 ‘Fondation’ 3 1951 9
    4 ‘Le meilleur des mondes’ 4 1931 7
    5 ‘Fahrenheit 451’ 5 1953 7
    6 ‘Chroniques martiennes’ 5 1950 8

  4. Insérer dans la table Livres :

    • "Blade Runner", écrit par K.Dick en 1968 et noté à 8/10,
    • "Les robots", écrit par Asimov en 1950 et noté à 9/10.

II.2. Les projections/sélections

  1. Sélectionner les titres des livres dont l’auteur est Asimov en les rangeant par rapport à leur année de publication.
  2. Projeter la table Livres sur son attribut ann_publi, sachant que l’on ne veut pas de répétitions.
  3. Faire une sélection permettant de compter le nombre de livres écrits après 1950.

II.3. Les jointures

  1. Que va faire la jointure suivante ?

    Code

    SELECT *
FROM Livres
INNER JOIN Auteurs ON Livres.id_auteur = Auteurs.id

  2. Écrire une jointure permettant d’afficher le titre des livres ainsi que le nom et prénom de leur auteur.

  3. Écrire une jointure permettant d’afficher le titre des livres dont la note est supérieure à 8 ainsi que la langue d’écriture de leur auteur.

II.4. Modification

  1. Mettre à jour la note du livre dont le titre est « Blade Runner » pour la passer à 9.
  2. Supprimer de la table le livre dont le titre est « Fahrenheit 451 ».

II. La base de données de l’histoire de l’informatique

Reprenons la base de données sur l'histoire de l'informatique créée dans le premier TP.

Télécharger la version actualisée de la base de données > BDDInformatique.db <.

  1. Dans cette base de données, qui contient déjà la table Figures, on a créé les 2 tables Avancées (définie dans le cours) et Programme (définie en TD).

    Des enregistrements ont été ajoutés :

    Dans Avancées :

    id_a nom date id_f
    1 Premier compilateur 1951 9
    2 Développement de logiciels pour la NASA 1965 2
    3 Premier programme informatique 1843 3
    4 Langage TeX 1976 4
    5 Machine universelle 1936 7
    6 Déchiffrement d’Enigma 1941 7
    7 Principe de transmission du signal à la base du Wifi 1941 6

    Pour Programme :

    id_p contenu capacite theme id_f
    1 Calculabilité, décidabilité Comprendre que la calculabilité ne dépend pas du langage utilisé. Langages et programmation 7
    2 Calculabilité, décidabilité Montrer, sans formalisme théorique, que le problème de l’arrêt est indécidable. Langages et programmation 7

  2. Vérifier le contenu des tables, avec une sélection sur l'ensemble des enregistrements.

  3. Ajouter les enregistrements suivants :

    • A Avancées :

    Code

    (8, Le Web, 1989, 8 )
(9, Modèle d’architecture des ordinateurs, 1945, 10)
    • A Programme :

    Code

    (3, Modèle d’architecture séquentielle (von Neumann), Distinguer les rôles et les caractéristiques des différents constituants d’une machine, Architectures matérielles, 10)
(4, Constructions élémentaires, Mettre en évidence un corpus de constructions élémentaires, Langages et programmation, 3)

    Nous allons faire des jointures sur ces tables. Pensez à utiliser des alias pour rendre les requêtes plus lisibles.

  4. Écrire une jointure affichant les noms et prénoms des figures avec les dates de leurs avancées.

  5. Écrire une jointure affichant les noms des avancées découvertes par une figure britannique.

  6. Écrire une jointure affichant les contenus et capacités du programme que nous devons à des recherches d’Alan Turing.