Blog ENI : Toute la veille numérique !
-25€ dès 75€ sur les livres en ligne, vidéos... avec le code FUSEE25. J'en profite !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
  1. Livres et vidéos
  2. SQL
  3. Approfondissement
Extrait - SQL Les fondamentaux du langage (avec exercices et corrigés) - (5e édition)
Extraits du livre
SQL Les fondamentaux du langage (avec exercices et corrigés) - (5e édition)
1 avis
Revenir à la page d'achat du livre

Approfondissement

Les sous-requêtes

Il est possible d’insérer une requête dans une autre. Celle-ci peut se trouver après la clause WHERE ou remplacer une constante derrière un ordre IN ou EXISTS, par exemple.

Il existe deux types de sous-requêtes : imbriquées ou corrélées.

1. Les sous-requêtes imbriquées

En fonction de ce que peut ramener le sous-SELECT, celui-ci ne pourra pas être positionné n’importe où.

Si le résultat de la requête placé dans un sous-SELECT ne ramène qu’une seule ligne, on pourra utiliser la sous-requête à la place de n’importe quelle constante.

Par exemple, si on veut récupérer toutes les chambres qui ont 1 lit simple avec douche, il faut faire un sous-SELECT qui récupère l’identifiant de la table TYPESCHAMBRE qui correspond à la description "1 lit simple avec douche", puis vérifier que la colonne typechambre de la table CHAMBRES correspond à la valeur du sous-SELECT. 

Avant de tester la requête complète, il est préférable de tester la sous-requête en premier pour vérifier sa validité et que celle-ci ramène une seule ligne.

Exemple

SELECT idtypechambre FROM typeschambre 
where description = '1 lit simple avec douche';  

affiche :

IDTYPECHAMBRE   
-------------   
      1 

Ensuite, on peut inclure la sous-requête dans la requête principale ainsi :

SELECT hotels.libelle, numchambre FROM Chambres 
INNER JOIN hotels ON hotels.idhotel =chambres.hotel 
where typechambre = (SELECT idtypechambre FROM typeschambre 
where description = '1 lit simple avec douche'); 

Résultat

LIBELLE                                            NUMCHA 
-------------------------------------------------- ------ 
Ski Hotel                                          1      ...

Les imports et exports de données

Selon les bases de données, il existe des outils d’import et export de données comme SQL* Loader pour Oracle, décrit ci-dessous. Dans SQL Server, il est possible d’utiliser l’utilitaire bcp ou l’outil d’import/export à partir de SQL Server Management Studio. Nous ne décrirons pas ces outils, dont le dernier s’utilise de manière très intuitive. L’outil le plus adapté pour industrialiser l’import ou l’export de données est un ETL (Extract Transform and Load) comme SSIS de l’éditeur Microsoft, inclus dans la licence SQL Server (excepté la version Express), Talend en open source, Oracle Data Integrator, Pentaho, Stambia…

À partir du client Oracle SQL Developer, il suffit de faire un clic droit sur une table et de choisir Exporter… ou Copier dans Oracle et se laisser guider.

1. Charger des données en masse avec SQL*Loader

Après avoir créé les enveloppes des différentes tables d’une base de données, il faut maintenant les alimenter.

Lorsqu’un historique existe, il peut être intéressant de charger rapidement et en masse tout cet historique.

Avant toute chose, il faut mettre cet historique au format attendu pour que le chargement fonctionne avec l’outil choisi.

Reprenons par exemple la table FILM que l’on a remplie par des INSERT multiples lors du chapitre La manipulation des données (LMD) - Exercices d’application.

TABLE FILM

Requête de création de la table (syntaxe standard) :

CREATE TABLE FILM (IDENT_FILM        INTEGER, 
                   TITRE             VARCHAR(50), 
                   GENRE1            VARCHAR(20), 
                   GENRE2            VARCHAR(20), 
                   DATE_SORTIE       DATE, 
                   PAYS...

Quelques notions de performances

Dans l’utilisation d’une base de données, on rencontre souvent des problèmes de temps de réponse importants sur une requête ou sur une autre.

Les raisons sont multiples, il peut s’agir d’une requête qui n’utilise aucun index, d’une table très importante, de jointures multiples, de problèmes d’accès disque ou de capacité mémoire, etc.

Ce que l’on appelle le « tuning » d’une base de données est très complexe est nécessite beaucoup d’expériences et de connaissances multiples en bases de données et systèmes d’exploitation.

Les règles de base lorsque l’on écrit une requête est de :

  • Vérifier que les critères de recherche (WHERE) utilisent des index.

  • Vérifier que les jointures entre tables se font bien sur les clés des tables et que des index ont bien été posés sur ces tables.

  • Vérifier que la sélection ne ramène pas des millions de lignes.

  • Vérifier que les statistiques de la base de données ont été activées et mises à jour régulièrement (surtout avec Oracle).

  • Ne pas utiliser trop de fonctions dans un même SELECT.

Les statistiques sont des données qui servent à la base de données pour savoir quel chemin est le plus optimisé pour atteindre une donnée.

1. Utilisation de EXPLAIN PLAN

Il existe un moyen de connaître le chemin utilisé par le SGBDR pour atteindre un élément. Il faut utiliser la commande EXPLAIN PLAN qui analyse l’ordre et indique ensuite le chemin pris. Pour cela, il stocke des éléments dans une table : PLAN_table sous Oracle.

La syntaxe à utiliser est celle-ci :

EXPLAIN PLAN SET STATEMENT_ID='<identifiant>' INTO PLAN_TABLE FOR
SELECT ... ... ; 

On indique au SGBDR de stocker dans une table nommée « PLAN_TABLE » sous l’identifiant choisi (STATEMENT_ID) les analyses réalisées sur la requête que l’on indique après le SELECT.

Exemple avec un SELECT sur trois tables, identifiant choisi ’TEST-PERF’

DELETE FROM PLAN_TABLE WHERE STATEMENT_ID='TEST-PERF';  
 ...

Les tables système

Les SGDBR utilisent pour leurs besoins un ensemble de tables pour stocker tous les éléments créés par un utilisateur. Tous les objets sont stockés dans des tables dites système.

Celles-ci sont accessibles simplement par la commande :

SELECT * FROM <Nom table>; 

1. Tables système pour les tables et colonnes

a. Oracle

Table

Contenu

ALL_COL_COMMENTS

Liste tous les commentaires sur les colonnes des tables.

ALL_TABLES

Liste toutes les tables.

ALL_TAB_COLUMNS

Liste toutes les colonnes des tables.

b. MySQL

Table

Contenu

INFORMATION_SCHEMA.TABLES

Liste toutes les tables.

INFORMATION_SCHEMA.COLUMNS

Liste toutes les colonnes des tables.

c. SQL Server

SQL Server stocke ces tables dans une base de données système nommée master.

Table

Contenu

sys.tables

Liste toutes les tables.

Sys.all_columns

Liste toutes les colonnes des tables.

d. PostgreSQL

PostgreSQL stocke ces tables dans un schéma pour chaque base de données nommé pg_catalog.

Table

Contenu

Pg_catalog.pg_class

Liste toutes les tables.

Pg_catalog.pg_attribute

Liste toutes les colonnes des tables.

2. Tables système pour les index et les vues

a. Oracle

Table

Contenu

ALL_INDEXES

Liste tous les index.

ALL_IND_COLUMNS

Liste toutes les colonnes des index.

ALL_VIEWS

Liste toutes les vues.

b. MySQL

Table

Contenu

INFORMATION_SCHEMA. STATISTICS

Liste toutes les informations sur les index.

INFORMATION_SCHEMA. VIEWS

Liste toutes les vues.

c. SQL Server

Table

Contenu

Sys.indexes

Liste toutes les informations sur les index.

Sys.views

Liste toutes les vues utilisateurs.

Sys.all_views

Liste toutes les vues.

d. PostgreSQL

Table

Contenu

Pg_catalog.pg_index

Liste toutes les informations sur les index.

Pg_catalog_pg_class

Liste toutes les vues.

3. Les autres tables système

a. Oracle

Table

Contenu

ALL_CATALOG

Liste toutes les tables, vues, séquences et synonymes.

ALL_CONSTRAINTS

Liste les contraintes.

ALL_OBJECTS

Liste tous les objets accessibles par l’utilisateur.

ALL_SEQUENCES

Liste les séquences.

ALL_SYNONYMS

Liste les synonymes.

ALL_TRIGGERS

Liste tous les triggers.

ALL_TRIGGERS_COLS

Liste toutes les colonnes des triggers.

ALL_USERS

Liste les utilisateurs déclarés.

b. MySQL

Table

Contenu

INFORMATION_SCHEMA. SCHEMATA

Liste toutes les tables, vues, séquences et synonymes.

INFORMATION_SCHEMA. CONSTRAINTS

Liste les contraintes.

INFORMATION_SCHEMA.COLUMN_ PRIVILEGES...

Les métadonnées, fonctions et procédures système SQL Server

Procédures système de description complète :

  • exec sp_helpdb

  • exec sp_help ’Hotels’

  • exec sp_helpdb ’RESAHOTEL’

  • exec sp_linkedservers

Fonctions système :

  • select DB_NAME()

  • select DB_ID()

  • select DB_NAME(2)

  • select DB_ID(’RESAHOTEL’)

  • select SUSER_NAME()

  • select GETDATE()

  • select SYSDATETIME()

  • select HOST_NAME() --machine

Variables système :

  • select @@SERVERNAME --instance

  • select @@VERSION

Quelques scripts bien utiles

1. Connaître la taille réelle d’une colonne

Sur une colonne déclarée en VARCHAR, il peut être intéressant de connaître la taille réelle de chaque valeur.

Cette requête permet en plus de trier le résultat.

Syntaxe

SELECT <nom de colonne>, LENGTH (TRIM(<nom de colonne>)) 
FROM <nom table> WHERE    .. 
ORDER BY LENGTH (TRIM(<nom de la colonne>)),<nom de colonne> ; 

Exemple Oracle

SELECT LENGTH(TRIM(description)) as longueurdesc, description 
FROM typeschambre 
ORDER BY longueurdesc; 

Exemple SQL Server

SELECT LEN(TRIM(description)) as longueurdesc, description 
FROM typeschambre 
ORDER BY longueurdesc; 

Résultat

LONGUEURDESC DESCRIPTION  
------------ --------------------------------------------------- 
         24 1 lit simple avec douche  
         24 1 lit double avec douche  
         25 2 lits double avec douche  
         26 2 lits simples avec douche  
         34 1 lit XL et 1 lit simple avec bain  
         35 1 lit double avec bain et WC séparés  
         36 2 lits double avec bain et WC séparés  
         37 1 lit double avec douche et WC séparés  
         38 2 lits double avec douche et WC séparés    

2. Rechercher et supprimer des doublons dans une table

Souvent on se retrouve avec des lignes en double dans une table suite à une mauvaise manipulation ou suite à un bug dans l’applicatif qui ne contrôle pas les doublons.

Si l’on reprend la table TYPESCHAMBRE et que l’on ajoute la ligne n°13 avec 1 lit simple avec douche qui existe déjà en ligne 1.

INSERT INTO typeschambre VALUES (13, 1, 'lit simple' ,'1 lit simple 
avec douche'); 

Contenu de la table TYPESCHAMBRE

IDTYPECHAMBRE  NOMBRELIT TYPELIT            DESCRIPTION  
-------------...

Exercices

Premier exercice

Créer une requête qui récupère tous les films qui ont dans leur casting un acteur français.

Deuxième exercice

Ajouter l’acteur Jean DUJARDIN dans la base puis afficher les acteurs (toutes les informations) qui portent le même prénom qu’un autre acteur.

Troisième exercice

Renommer la table PAYS avec le libellé PAYSold. Recréer une table PAYS. Importer la liste des pays curiexplore-pays.csv dans la table PAYS.

Source : Liste des pays et territoires — Plateforme open data (données ouvertes) (enseignementsup-recherche.gouv.fr)

Mettre à jour la nationalité des acteurs avec la table PAYS.

Supprimer la table PAYSold et le trigger T_INS_ACTEUR.

Solutions des exercices

Premier exercice

SELECT TITRE, NOM, PRENOM 
FROM FILM INNER JOIN CASTING 
ON FILM.IDENT_FILM = CASTING.IDENT_FILM  
INNER JOIN ACTEUR ON CASTING.IDENT_ACTEUR = ACTEUR.IDENT_ACTEUR 
WHERE NATIONALITE = (SELECT PAYS.IDENT_PAYS FROM PAYS WHERE 
PAYS.LIBELLE = 'FRANCE')    
ORDER BY FILM.TITRE, NOM; 

Deuxième exercice

INSERT INTO ACTEUR VALUES (15, 'DUJARDIN', 'JEAN', '19/06/1972', 
57,1); (SQL Server 
INSERT INTO ACTEUR VALUES (15, 'DUJARDIN', 'JEAN', 
TO_DATE('19/06/1972','DD/MM/YYYY'),57,1); (Oracle) 
INSERT INTO ACTEUR VALUES (15, 'DUJARDIN', 'JEAN', 
'19720619',57,1); (MySQL et PostgreSQL) 
SELECT * FROM ACTEUR A1 
WHERE EXISTS( SELECT PRENOM, COUNT(1) FROM ACTEUR AS A2 WHERE 
A1.PRENOM = A2.PRENOM 
GROUP BY PRENOM 
HAVING COUNT(1) > 1); 

Troisième exercice

Solution avec SQL Server

Renommer la table :

EXEC sp_rename 'PAYS', 'PAYSold'; 

Créer une table PAYS avec une clé primaire et une colonne auto incrémentée :

CREATE TABLE PAYS 
    (IDENT_PAYS SMALLINT IDENTITY(1,1) NOT NULL PRIMARY KEY, 
    LIBELLE    VARCHAR(100));  

Import des données :

sp_configure 'show advanced options', 1 
GO  
RECONFIGURE  ...