Langage SQL

Numérique et sciences informatiques

donnees

Le langage SQL

Un système de gestion de bases de données (SGBD) est un logiciel qui organise et gère les données de façon transparente pour l’utilisateur. Ce sont des logiciels dont la conception est bien trop complexe pour pouvoir être abordée dans ce cours ; nous nous contenterons d’interagir avec eux par l’intermédiaire de requêtes exprimées dans un langage devenu standard au fil des temps : le langage SQL (pour Structured Query Language).

SQL est un langage déclaratif, il n'est donc pas a proprement parlé un langage de programmation, mais plutôt une interface standard pour accéder aux bases de données.

Une base de données est un ensemble de "tables" appelés relations ayant des liens entre eux. Chaque table est composée de "lignes" appelées enregistrements correspondant à des n-uplets de même structure. On appelle cette structure le schéma relationnel de la table.

Présentation du logiciel DB Browser SQLite

Ce logiciel gratuit et libre permet de créer et manipuler des bases de données sans avoir besoin d’un serveur (contrairement à MySQL par exemple). Son interface est agréable et très intuitive.

Téléchargement et installation

Le logiciel se trouve ici. Sur Windows, on peut choisir sa version et il existe aussi une version portable pour une exécution sans installation.

Utilisation

Au lancement, la fenêtre devrait ressembler à cela :

DB browser SQLite

Nous utiliserons beaucoup l'onglet "Exécuter le sql" pour atteindre les objectifs du programmes.

DB browser SQLite

Pour découvrir le langage SQL, nous allons répondre à l’appel d’offre du collège Poudlard qui a besoin d’une base de données pour la gestion de son établissement.

poudlard sql poudlard sql
Relation eleves et maisons

Nous allons organiser la base de données poudlard avec une relation « élèves » et une relation « maisons ».

Entraînement 1 :

Identifier les clés primaires et secondaire des tables précédentes.

Créer une base de données

Pour créer la table eleves, il faut utiliser le mot clé CREATE TABLE.

CREATE TABLE IF NOT EXISTS maisons (
	"nom"	TEXT,
	"referent"	TEXT,
	"dortoir"	TEXT,
	"effectif"	INTEGER,
	PRIMARY KEY("nom")
);

Entraînement 2 :

Ajouter la table eleves à votre base.

Il faut maintenant ajouter du contenu à notre base. Pour ajouter des entrées, on utilise “INSERT INTO .... VALUES ....”.

INSERT INTO maisons (nom,referent,dortoir,effectif)
VALUES ("Gryffondor","MacGonagall","tour",28),
("Serdaigle","Flitwick","tour",35),
("Poufsouffle","Chourave","logis",31),
("Serpentard","Rogue","donjon",25);

Entraînement 3 :

Ajouter les enregistrements à la table eleves de votre base. Ne pas oublier les clefs

Pour modifier un enregistrement/ligne, il faut utiliser UPDATE

UPDATE eleves SET moyenne=20 WHERE nom="Granger" AND prenom="Hermione" ;

Entraînement 4 :

Modifier la note de Harry.

Opérations sur une table

Commençons par extraire de la table maisons les dortoirs de tous qu’elle contient :
SELECT  dortoir FROM maisons

Les mots-clés SELECT ... FROM réalisent l’interrogation de la table. Dans le cas de l’exemple ci-dessus on ne liste qu’un seul des attributs de la table, pour en avoir plusieurs on sépare les attributs par une virgule ; pour les avoir tous on les désigne par une étoile. Par exemple, les deux requêtes qui suivent donnent pour la première le nom de chacune des maisons ainsi que leurs dortoirs, pour la seconde l’intégralité des données de la table :
SELECT  nom , dortoir FROM maisons
SELECT * FROM maisons

Pour éliminer les doublons, on utilise SELECT DISTINCT.

SELECT DISTINCT dortoir FROM maisons ;

Pour renommer un attribut (ou une table), on effectue une projection en indiquant tous les attributs et en précisant avec le mot clef AS le nouveau nom.

SELECT nom , referent AS prof_principal , effectif FROM maison ;

Le mot-clé WHERE filtre les données qui répondent à un critère de sélection. Par exemple, pour connaître le nom des élèves de la maison du Poufsouffle on écrira :

SELECT nom,prenom
FROM eleves
WHERE maison = "Poufsouffle" 

Il se peut que certains attributs d’un enregistrement soient manquants ; dans ce cas la valeur de cet attribut est NULL.
Différentes clauses permettent de formuler des requêtes plus élaborées ;

base de donnees
Principales requêtes SQL

L’opérateur LIKE est utilisé dans la clause WHERE des requêtes SQL. Ce mot-clé permet d’effectuer une recherche sur un modèle particulier.

  • LIKE ‘%a’ : le caractère “%” est un caractère joker qui remplace tous les autres caractères. Ainsi, ce modèle permet de rechercher toutes les chaînes de caractère qui se termine par un “a”.
  • LIKE ‘a%’ : ce modèle permet de rechercher toutes les lignes de “colonne” qui commence par un “a”.
  • LIKE ‘%a%’ : ce modèle est utilisé pour rechercher tous les enregistrement qui utilisent le caractère “a”.

Entraînement 5 : Rédiger une requête SQL pour obtenir

  1. la liste des élèves de Gryfondor avec une moyenne supérieur à 17.
  2. Voir une solution

  3. la même liste triée par ordre alphabétique ;
  4. Voir une solution

  5. la liste des maisons de la table eleves sans doublon ;
  6. Voir une solution

  7. le nom et le prénom des élèves ayant moins de 17 de moyenne par moyenne décroissante.
  8. Voir une solution

  9. le nom des élèves ayant un "h" dans leur prénom.
  10. Voir une solution

Fonctions d’agrégation

Il est possible de regrouper certains enregistrements d’une table par agrégation pour leur appliquer une fonction. Par exemple, pour connaître le nombre d'élèves on écrira :

SELECT COUNT ( * ) FROM eleves
Fonctions statistiques
Fonctions statistiques

Pour afficher la moyenne des élèves, nous utiliserons la requète :

SELECT AVG(Moyenne) FROM eleves ;

Toutes ces fonctions prennent tout leur sens lorsqu’elles sont utilisée avec la commande GROUP BY qui permet de filtrer les données sur une ou plusieurs colonnes.

SELECT maison, AVG(moyenne) FROM eleves
GROUP BY maison ;

Entraînement 6 : Rédiger une requête SQL pour obtenir

  1. Donner la somme des effectifs de Poudlard.
  2. Voir une solution

  3. Donner le nom de l'élève ayant la plus petite moyenne
  4. Voir une solution

  5. Donner la moyenne des élèves de la maison Gryffondor.
  6. Voir une solution

  7. le nombre d'élève dormant dans une tour
  8. Voir une solution

Jointures

L’intérêt d’une base de données réside en particulier dans la possibilité de croiser des informations présentes dans plusieurs tables par l’intermédiaire d’une jointure. Dans la base de données qui nous occupe, le lien entre les 2 tables est la maison, la clé étrangère pour la relation eleves et clef primaire pour la relation maisons.
Les mots-clés JOIN ... ON créent une table intermédiaire formée du produit cartésien des deux tables et applique ensuite la requête sur la nouvelle relation.
Remarque : L’interrogation de plusieurs tables simultanément rend nécessaire le préfixage de l’attribut par le nom de la table pour le cas où certaines d’entres-elles auraient des noms d’attributs en commun.

SELECT referent FROM maisons
JOIN eleves ON eleves.maison = maisons.nom
where eleves.maison = "Serpentard"

On peut alléger cette syntaxe à l’aide d’alias pour la rendre plus compacte. Par exemple, la requête précédente peut s’écrire plus succinctement :
SELECT referent FROM maisons m
JOIN eleves e ON e.maison = m.nom
where e.maison = "Serpentard"

Entraînement 7 : Rédiger une requête SQL pour obtenir

  1. le nom et le prénom des élèves appartenant à une maison ayant un effectif plus grand que 30.
  2. Voir une solution


Bilan

Entraînement 8 :

  1. Combien d’enregistrements comptabiliserait le résultat de la requête :
    SELECT * FROM maisons,eleves;
  2. Donner une requête qui donne le nom du référent de Cedric Diggory.
  3. Donner une requête qui ajoute Luna Lovegood et Cho Chang de la maison Serdaigle avec 15 et 16 de moyenne respectivement.
  4. Donner une requête qui donne la moyenne des élèves de Serdaigle et de Serpentard.
  5. Donner une requête qui classe les élèves par ordre croissant de la moyenne
  6. Donner un requête qui donne le nom et le prénom des élèves ayant plus de 16 moyenne

Savoir faire

  • Savoir identifier les composants d’une requête
  • Savoir construire des requêtes d’interrogation à l’aide des clauses du langage SQL : SELECT, FROM, WHERE, JOIN
  • Savoir construire des requêtes d’insertion et de mise à jour à l’aide de : UPDATE, INSERT, DELETE
  • Savor effectuer une jointure entre 2 tables (utilisation de “JOIN”)

Fiche de cours