Aller au contenu
Sup et Spé
Télécharger l'application

Accueil › ECG › Maths appliquées

ECG2 · Maths appliquées · semaine 29

Bases de données et langage SQL

Modèle relationnel avec relation, attribut, domaine, clef primaire, clef étrangère et schéma relationnel. Vocabulaire des tables, champs, colonnes et enregistrements, opérateurs arithmétiques, de comparaison et logiques. Commandes SQL exigibles SELECT, INSERT INTO, DELETE FROM, UPDATE, INNER JOIN et CREATE TABLE, sur des cas d'usage comme un fichier clients ou une gestion de stocks.

Sommaire

Le cours

Résumé en cinq points

  1. Une base de données relationnelle est un ensemble de tables :
    • une table est une relation ;
    • une colonne est un attribut ;
    • une ligne est un enregistrement.
  2. La clef primaire identifie de façon unique chaque ligne, la clef étrangère pointe vers la clef primaire d'une autre table et permet les jointures.
  3. Les commandes exigibles sont :
    • SELECT avec FROM et WHERE ;
    • INSERT INTO ;
    • UPDATE ;
    • DELETE FROM ;
    • INNER JOIN avec ON ;
    • CREATE TABLE.
  4. Le test d'égalité s'écrit avec un seul signe égal, la différence avec deux chevrons, et les conditions se combinent avec AND, OR et NOT.
  5. UNION, EXCEPT, les fonctions d'agrégation, DISTINCT et ORDER BY existent mais ne sont pas exigibles, le programme le dit explicitement.
Pourquoi ce chapitre

Remarque

L'administration, les banques, les assurances et la finance stockent leurs données dans des bases de données relationnelles.

Ce chapitre est le seul du programme qui ne soit pas mathématique au sens strict. On donne un schéma de tables et on demande d'écrire une requête. L'objectif du programme est une description applicative, pas une théorie des bases de données.

DéfinitionSGBD et SQL

Un logiciel appelé système de gestion de bases de données, en abrégé SGBD, se charge de la lecture, de l'écriture et de la cohérence de ces données.

On l'interroge dans un langage dédié, le SQL, pour Structured Query Language.

Fin de l'aperçu gratuit

Encore 12 parties de cours et 9 corrigés.

Essayer 7 jours gratuitement Sans engagement · cours, exercices et fiches complets

Méthodes et exercices

Exercices classiques corrigés

Les exercices se suivent : garde leur ordre. Difficulté et durée indicatives.

Exercice 1. Lire un schéma ★

Application du cours · 10 min · Niveau khôlle · Modèle relationnel

On gère le personnel d'une entreprise avec deux tables.

La table Services a pour champs id_service, libelle et ville.

La table Employes a pour champs id_employe, nom, prenom, id_service et salaire.

Identifier les clefs primaires et étrangères, donner un domaine pour chaque champ, et dire si un service peut n'avoir aucun employé.

Voir l'indice

Technique situer le vocabulaire du modèle relationnel.

Voir le corrigé offert
  1. id_service est clef primaire de Services, id_employe est clef primaire de Employes. 2. id_service dans Employes est clef étrangère, elle référence Services. 3. Les identifiants et le salaire sont INTEGER, les autres champs sont TEXT. 4. Plusieurs employés peuvent avoir le même id_service, c'est pourquoi ce champ n'est pas clef primaire de Employes. 5. Rien n'oblige un service à apparaître dans Employes, un service peut donc être vide. Point où les élèves se trompent : proposer nom comme clef primaire parce que l'extrait ne contient pas d'homonymes.

Exercice 2. Créer et remplir ★

Application du cours · 15 min · Niveau khôlle · Création et insertion

Écrire la création d'une table Fournisseurs (id_fournisseur, raison_sociale, pays) et d'une table Produits (id_produit, designation, stock, id_fournisseur).

Insérer le fournisseur 1, « L'Outilleur », France, puis le produit 100, « Vis inox », 500 unités, fourni par le fournisseur 1.

Voir le début du corrigé
  1. La table référencée d'abord.

La suite du corrigé dans Sup et Spé+ : essai gratuit 7 jours.

Exercice 3. Conditions composées ★

Application du cours · 10 min · Niveau khôlle · Filtres et conditions

Dans Employes, enrichie d'un champ anciennete, donner les noms des employés : a) du service 3 gagnant plus de 3000 euros, b) des services 3 ou 4 ayant au moins dix ans d'ancienneté, c) qui ne sont ni au service 3 ni au service 4, d) dont le salaire est hors de l'intervalle de 2000 à 4000.

Voir le début du corrigé
  1. a) WHERE id_service = 3 AND salaire > 3000. …

La suite du corrigé dans Sup et Spé+ : essai gratuit 7 jours.

Exercice 4. Mettre à jour et supprimer ★

Application du cours · 10 min · Niveau khôlle · Mise à jour et suppression

Avec les tables de l'exercice 2 : a) retirer 20 unités du stock du produit 100, b) supprimer les produits en rupture, c) supprimer le fournisseur 1.

Voir le début du corrigé
  1. a) UPDATE Produits SET stock = stock - 20 WHERE id_produit = 100. …

La suite du corrigé dans Sup et Spé+ : essai gratuit 7 jours.

Exercice 5. Jointure filtrée avec montant ★★

Classiques de khôlle et concours · 20 min · Niveau écrit Ecricome · Jointures

Sur le schéma Clients et Commandes de la fiche, donner le nom du client, le produit et le montant de chaque commande de plus de 500 euros passée par un client ayant au moins trois ans d'ancienneté.

Voir le début du corrigé
  1. Colonnes : Clients.nom, Commandes.produit et le produit quantite × prix_unitaire.

La suite du corrigé dans Sup et Spé+ : essai gratuit 7 jours.

Exercice 6. Exécuter une requête à la main ★★

Classiques de khôlle et concours · 15 min · Niveau écrit Ecricome · Jointures

Clients contient (1,Durand,Lyon,5),(1, \text{Durand}, \text{Lyon}, 5), (2,Martin,Lille,1)(2, \text{Martin}, \text{Lille}, 1) et (3,Petit,Lyon,2).(3, \text{Petit}, \text{Lyon}, 2).

Commandes contient (10,1,Clavier,2,30),(10, 1, \text{Clavier}, 2, 30), (11,1,Souris,1,15),(11, 1, \text{Souris}, 1, 15), (12,3,Eˊcran,1,200)(12, 3, \text{Écran}, 1, 200) et (13,4,Caˆble,5,3).(13, 4, \text{Câble}, 5, 3).

Combien de lignes a la jointure sur id_client ?

Que renvoie la requête de l'exercice 5 avec le seuil de montant remplacé par 20, sans condition d'ancienneté ?

Voir le début du corrigé
  1. La commande 10 va avec Durand, la 11 avec Durand, la 12 avec Petit. …

La suite du corrigé dans Sup et Spé+ : essai gratuit 7 jours.

Exercice 7. Une condition sur plusieurs lignes ★★

Classiques de khôlle et concours · 20 min · Niveau écrit BCE, EDHEC ou emlyon · Requêtes ensemblistes

Donner les identifiants des clients lyonnais qui n'ont jamais commandé de clavier. Le sujet rappelle la syntaxe de EXCEPT.

Voir le début du corrigé
  1. On forme les clients lyonnais, puis on retire ceux qui ont au moins une commande de clavier.

La suite du corrigé dans Sup et Spé+ : essai gratuit 7 jours.

Exercice 8. Jointure sur une clef composée ★★

Classiques de khôlle et concours · 20 min · Niveau écrit BCE, EDHEC ou emlyon · Jointures

Les tables Theorique et Comptage ont chacune pour champs id_produit, id_entrepot et quantite, avec la clef primaire (id_produit, id_entrepot).

Écrire la création de Theorique.

Lister ensuite, pour chaque couple produit et entrepôt, les deux quantités et l'écart, lorsque le comptage diffère du stock théorique.

Voir le début du corrigé
  1. La clef composée se déclare après les champs.

La suite du corrigé dans Sup et Spé+ : essai gratuit 7 jours.

Exercice 9. Trois tables ★★

Classiques de khôlle et concours · 25 min · Niveau écrit BCE, ESSEC ou HEC · Jointures

Etudiants (id_etudiant, nom, promo) et Cours (id_cours, intitule, coefficient) sont reliées par Inscriptions (id_etudiant, id_cours, note).

Justifier la clef primaire de Inscriptions.

Donner le nom et la note de chaque étudiant inscrit en Statistique.

Parmi ces inscrits en Statistique, donner ensuite ceux dont la note pondérée, note×coefficient\text{note} \times \text{coefficient}, atteint 30.

Voir le début du corrigé
  1. Un étudiant suit plusieurs cours et un cours a plusieurs étudiants. …

La suite du corrigé dans Sup et Spé+ : essai gratuit 7 jours.

Exercice 10. Fichier de données et descripteur ★★

Classiques de khôlle et concours · 20 min · Niveau écrit BCE, ESSEC ou HEC · Lecture de fichier CSV

Le fichier ventes.csv commence par la ligne d'en-tête produit,quantite,prix.

Chaque ligne suivante donne un produit, une quantité et un prix entiers.

Écrire une fonction Python qui renvoie le chiffre d'affaires total.

Expliquer le rôle du descripteur.

Corrigé détaillé dans Sup et Spé+ : essai gratuit 7 jours.

Fiche de cours rédigée à partir du programme officiel de la voie EC, enseignement annuel d'informatique et algorithmique de seconde année, partie I « Programme du troisième semestre », paragraphe 1 « Bases de données ». Elle présente le modèle relationnel, le vocabulaire des tables et les commandes SQL exigibles. Les commandes signalées comme non exigibles par le programme sont regroupées à la fin et clairement identifiées.