Archives de catégorie : SQL cours

SQL cours

Instruction SQL ALIAS

L’instruction CREATE ALIAS définit un alias pour un module, un surnom, une séquence, une table, une vue ou un autre alias. Les alias sont également appelés synonymes.

Voici la syntaxe de création d’alias :

CREATE[REPLACE] ALIAS nom-alias
         FOR TABLE nom-table
         [FOR MODULE nom-module]
         [FOR SEQUENCE nom-séquence]

Exemple:

L’instruction SQL suivante crée un alias pour une table TB_TABLE.

CREATE ALIAS SP_TAB
   FOR TB_TABLE;
SQL cours

Instruction SQL VIEW

Une vue offre une manière différente d’examiner les données dans une ou plusieurs tables. L’instruction VIEW (vue) est une table virtuelle constituée d’une instruction SQL SELECT qui accède aux données d’une ou plusieurs tables ou vues.

Une vue contient des lignes et des colonnes, tout comme une vraie table. Les champs d’une vue sont des champs d’une ou plusieurs tables réelles de la base de données.

Voici la syntaxe de création de vue :

CREATE VIEW nom-view
       AS requete

Où, requete signifie n’importe quelle instruction SQL SELECT.

Exemple:

Créez une vue nommée VW_PROJET sur la table TB_PROJET qui contient uniquement les lignes avec un numéro de projet (PROJET_NO) commençant par les lettres “SP”.

CREATE VIEW VW_PROJET
    AS SELECT *
      FROM TB_PROJET
        WHERE SUBSTR(PROJET_NO, 1, 2) = 'SP'

Nous pouvons interroger la vue ci-dessus comme suit :

SELECT * FROM [VW_PROJET];
SQL cours

Instruction SQL ALTER INDEX

L’instruction SQL ALTER INDEX est utilisée pour modifier la définition d’un index.

Voici la syntaxe de modification d’index :

ALTER INDEX nom-index
      [REGENERATE]
      [ADD COLUMN (nom-colonne ASC/DESC)]
      [CLUSTER | NOT CLUSTER]
      [PADDED | NOT PADDED]
      [using-specification]
      [free-specification]
      [COMPRESS YES | NO]
      [ALTER partition-element
            using-specification
            free-specification]
      [BUFFERPOOL nom-bp]
      [CLOSE YES | NO]
      [PIECESIZE integer]
      [COPY YES | NO]

Explication:

  • REGENERATE : Spécifie que l’index sera régénéré.
  • ADD COLUMN : ajoute nom-colonne à l’index.
  • ALTER PARTITION : identifie la partition de l’index à modifier.

Exemple 1:

Modifiez l’index DSN8910.IX_EMP. Indiquez que DB2 ne doit pas fermer les ensembles de données qui prennent en charge l’index lorsqu’il n’y a aucun utilisateur actuel de l’index.

ALTER INDEX DSN8910.IX_EMP
      CLOSE NO;

Modifier l’index partitionné DSN8910.IX_DEPT. Pour la partition 3, laissez une page d’espace libre pour 13 pages et 13 % d’espace libre par page. Pour la partition 5, laissez une page pour 25 pages et 25 % d’espace libre. Pour toutes les autres partitions, laissez une page d’espace libre pour 6 pages et 11 % d’espace libre.

ALTER INDEX DSN8910.IX_DEPT
USING
    VCAT CATLGG
FREEPAGE 6
PCTFREE 11
ALTER PARTITION 3
    USING VCAT CATLGG
    FREEPAGE 13
    PCTFREE 13,
ALTER PARTITION 5
    USING VCAT CATLGG
    FREEPAGE 25
    PCTFREE 25;

Exemple 2 :

Modifiez l’index DSN8910.IX_PROJ. Utilisez BP1 comme pool de mémoire tampon à associer à l’index, indiquez que l’image complète ou les copies simultanées sur l’index sont autorisées et modifiez la taille maximale de chaque ensemble de données à 8 mégaoctets.

ALTER INDEX DSN8910.IX_PROJ
        BUFFERPOOL BP1
        COPY YES
        PIECESIZE 8M;

Exemple 3 :

Supposons que l’index IX_X1 contient au moins une colonne de longueur variable et est un index rembourré. Remplacez l’index par un index qui n’est pas rempli.

ALTER INDEX IX_X1 NOT PADDED;
SQL cours

Opérateur SQL BETWEEN 

L’opérateur BETWEEN sélectionne des valeurs dans une plage donnée. Les valeurs peuvent être des nombres, du texte ou des dates.

L’opérateur BETWEEN est inclusif : les valeurs de début et de fin sont incluses.

Syntaxe:

SELECT nom_colonne(s) 
  FROM nom_table 
 WHERE nom_colonne BETWEEN valeur1 AND valeur2 ;

Base de données DB2 :

Vous trouverez ci-dessous une sélection de la table “Product” de la base de données DB2.

ID-ProduitNom-ProduitCategoriePrixStockValeur-Totale
7001SourisAccessoires75,00
7002Disque dur65,00201 300
7003ClavierAccessoires36.00331 118,00
7004RAMComposants23.5016376,00
7005VTTvélos1 200
7006STYLO7.451074,50
7007Lecteur CDAccessoires75,00
7008MicroAccessoires75,00
7009AmplificateurComposants25.00
7010casque de musiqueAccessoires100,00

Exemple 1:

L’instruction SQL suivante sélectionne tous les produits dont le prix est ENTRE 10 et 50 :

SELECT * FROM Produit 
 WHERE Prix BETWEEN 10 ET 50 ;
Résultat:
ID-ProduitNom-ProduitCategoriePrixStockValeur-Totale
7003ClavierAccessoires36.00331 118,00
7004RAMComposants23.5016376,00
7006STYLO7.451074,50
7009AmplificateurComposants25.00

Exemple 2 :

L’instruction SQL suivante sélectionne tous les produits en dehors de la plage de l’exemple précédent, utilisez NOT BETWEEN ::

SELECT *
  FROM Produits 
 WHERE Prix NOT BETWEEN 10 ET 50 ;
Résultat:
ID-ProduitNom-ProduitCategoriePrixStockValeur-Totale
7001SourisAccessoires75,00
7002Disque dur65,00201 300
7005VTTvélos1 200
7007Lecteur CDAccessoires75,00
7008MicroAccessoires75,00
7010casque de musiqueAccessoires100,00
SQL cours

SQL AS

Un alias est juste cela, un nom alternatif pour un champ ou une valeur.

Les alias sont attribués avec le mot-clé AS.

Les alias SQL sont utilisés pour donner à une table, ou à une colonne d’une table, un nom temporaire. Les alias sont souvent utilisés pour rendre les noms de colonnes plus lisibles.

Syntaxe : alias de colonne

SELECT nom_colonne AS nom_alias 
  FROM nom_table ;

Syntaxe : alias de table

SELECT nom_colonne(s) 
  FROM nom_table AS nom_alias ;

Base de données DB2 :

Vous trouverez ci-dessous une sélection de la table “Product” de la base de données DB2.

ID-ProduitNom-ProduitCategoriePrixStockValeur-Totale
7001SourisAccessoires75,00
7002Disque dur65,00201 300
7003ClavierAccessoires36.00331 118,00
7004RAMComposants23.5016376,00
7005VTTvélos1 200
7006STYLO7.45dix74,50
7007Lecteur CDAccessoires75,00
7008MicroAccessoires75,00
7009AmplificateurComposants25.00
7010casque de musiqueAccessoires100,00

Exemple 1:

L’instruction SQL suivante crée deux alias, un pour la colonne ProductID et un pour la colonne ProductName :

SELECT ID_Produit AS ID, Nom_Produit AS Produit 
  FROM Produit ;

Le résultat de cette requête est le suivant.

IDProduit
7001Souris
7002Disque dur
7003Clavier
7004RAM
7005VTT
7006STYLO
7007Lecteur CD
7008Micro
7009Amplificateur
7010casque de musique

Exemple 2 :

Si le nom d’alias contient des espaces, il nécessite des guillemets simples ou des crochets.

SELECT CONCAT('LaCommunaute','DuCobol') AS 'Valeur combinée' 
  FROM SYSIBM.SYSDUMMY1;

Voici la sortie :

Valeur combinée 
------------- 
LaCommunauteDuCobol

Exemple 3 :

L’instruction SQL suivante vend tous les produits de la table product. Nous utilisons la table “Product” et donnons à la table des alias comme “P” (ici, nous utilisons des alias pour raccourcir le SQL) :

Ceci est un exemple simple d’alias de table. nous n’utilisons généralement pas d’alias pour une requête de table unique. Juste à des fins de compréhension, nous avons utilisé des alias pour une requête de table unique.

SELECT P.ID_Produit, P.Nom_Produit
  FROM Produit AS P ;

Le résultat de cette requête est le suivant.

ID-ProduitNom-Produit
7001Souris
7002Disque dur
7003Clavier
7004RAM
7005VTT
7006STYLO
7007Lecteur CD
7008Micro
7009Amplificateur
7010casque de musique

Les alias peuvent être utiles lorsque :

  • Il y a plus d’une table impliquée dans une requête
  • Les fonctions sont utilisées dans la requête
  • Les noms de colonne sont grands ou peu lisibles
  • Deux ou plusieurs colonnes sont combinées ensemble
SQL cours

Opérateurs mathématiques SQL

Les opérateurs mathématiques SQL sont les suivants :

OpérateurLa description
+Ajout
–Soustraction
*Multiplication
/Division

Base de données DB2 :

Vous trouverez ci-dessous une sélection de la table “Etudiants” de la base de données DB2.

ID-EtudiantNom-EtudiantMarque1Marque 2Marque3
1001Mani1008090
1002Sini1008595
1003Thamaraï10090100
1004Kalaï908090
1005Pravin758060
1006Asaimani607090

Exemple 1:

L’instruction SQL suivante répertorie les détails des étudiants avec des notes totales.

SELECT Studentid, Studentname, Mark1, Mark2, Mark3, Mark1+Mark2+Mark3 AS "Notes totales" 
  FROM Etudiants ;
Résultat:
ID-EtudiantNom-EtudiantMarque1Marque2Marque3Notes Totals
1001Mani1008090270
1002Sini1008595280
1003Thamaraï10090100290
1004Kalaï908090260
1005Pravin758060215
1006Asaimani607090220
SQL cours

Sous-requêtes SQL

Les sous-requêtes SQL sont utilisées pour combiner différentes requêtes en une seule instruction.

Les sous-requêtes sont toujours traitées en commençant par l’instruction SELECT la plus interne et en allant vers l’extérieur.

Exemple:

SELECT ordre_conso_id
    FROM tb_ordre
    WHERE ordre_num IN
      (SELECT ordre_objet_num
         FROM tb_ordre_objet
      WHERE ordre_objet_prod_id = 'RGAN01');

Lorsque l’instruction SELECT précédente est traitée, le SGBD effectue en fait deux opérations.

Il exécute d’abord la sous-requête :

SELECT ordre_objet_num
    FROM tb_ordre_objet
   WHERE ordre_objet_prod_id = 'RGAN01'

Cette requête s’appelle “Inner Query”.

Cette requête renvoie deux numéros d’ordre 20007 et 20008. Ces deux valeurs sont ensuite transmises à la clause WHERE de la “requête externe” dans le format délimité par des virgules requis par l’opérateur IN. La requête externe devient alors la suivante :

SELECT ordre_conso_id
    FROM tb_ordre
    WHERE ordre_num IN (20007,20008)

Vous pouvez utiliser une sous-requête dans une comparaison simple :

  • IN
  • ANY
  • SOME
  • ALL
  • EXIST
  • Si une sous-requête renvoie une seule ligne, l’opérateur =, <, >, <=, >= ou <> peut être utilisé pour la comparaison avec la sous-requête. Si plusieurs enregistrements sont renvoyés, l’opérateur IN, ANY, ALL ou SOME doit être utilisé. Avec ANY ou SOME, la condition doit être vraie pour l’une des valeurs renvoyées par la sous-requête. Avec ALL, la condition doit être vraie pour toutes les valeurs renvoyées par la sous-requête.

Exemple:

La sous-requête qui utilise l’opérateur ANY est la suivante :

SELECT conso_no
  FROM tb_cons
  WHERE conso_no = ANY
    (SELECT inv_cons_no
       FROM tb_inv
       WHERE inv_total > 200);

Types de sous-requêtes

  1. Sous-requête non corrélée
  2. Sous-requête corrélée

Sous-requête non corrélée :

Dans la sous-requête, si la requête interne et la requête externe fonctionnent indépendamment, la sous-requête est appelée sous-requête non corrélée.

SQL cours

Sous-requête corrélée SQL

Dans une sous-requête corrélée, la requête interne ne fonctionne pas indépendamment de la requête externe.

Dans ce cas, la requête interne est effectuée une fois pour chaque ligne de la requête externe.

Pour corréler la table de la requête interne avec la table de la requête externe, vous devez définir un alias pour la requête externe et l’utiliser comme qualificatif dans la requête interne.

  1. Lorsque vous utilisez l’alias dans ce contexte, il est appelé “nom de corrélation” et la connexion qu’il établit est appelée “référence corrélée”.
  2. Une sous-requête corrélée avec le mot-clé EXISTS ne nomme aucune colonne car aucune donnée n’est transférée lorsque vous utilisez EXISTS.

Exemple:

SELECT nom_conso
    FROM tb_conso A
      WHERE NOT EXISTS
      (SELECT * FROM tb_inv WHERE inv_conso = A.conso_no)

Opérateur EXISTS

  • L’opérateur EXISTS est utilisé pour les sous-requêtes corrélées.
  • Il teste si la sous-requête renvoie au moins une ligne.
  • L’opérateur EXISTS renvoie vrai ou faux, jamais inconnu.
  • Étant donné que EXISTS teste uniquement si une ligne existe, les colonnes affichées dans la liste SELECT de la sous-requête ne sont pas pertinentes. En règle générale, vous utilisez un littéral de texte à un seul caractère tel que ‘1’ ou ‘X’ ou le mot-clé NULL.

Exemple:

Il s’agit d’une sous-requête corrélée qui affiche les instructeurs où INSTRUCTEUR_ID a une ligne correspondante dans la table SECTION.

Le résultat affiche les valeurs des colonnes INSTRUCTEUR_ID , INSTRUCTEUR_PRENOM des enseignants affectés à au moins une section.

SELECT instructeur_id, instructeur_nom
    FROM tb_instructeur I
      WHERE EXISTS
      (SELECT 'X'
          FROM tb_section
            WHERE I.instructeur_id = instructeur_id)

Pour chaque ligne de la table INSTRUCTEUR, la requête externe évalue la requête interne. Il vérifie si la valeur INSTRUCTEUR_ID de la ligne actuelle existe pour la colonne INSTRUCTEUR_ID de la table SECTION. Seulement si une ligne avec la valeur appropriée est trouvée, la condition est vraie et la ligne externe est incluse dans le résultat.

Opérateur NOT EXISTS :

L’opérateur NOT EXISTS est l’opposé de l’opérateur EXISTS ; il teste si une ligne correspondante est introuvable.

SQL cours

Clause SQL JOIN

Une clause JOIN est utilisée pour combiner des lignes de deux tables ou plus, en fonction d’une colonne liée entre elles.

Une jointure est un mécanisme utilisé pour associer des tables dans une instruction SELECT.

Pour créer une jointure, vous devez spécifier toutes les tables à inclure et comment elles sont liées les unes aux autres.

Examinons les données du tableau “Commandes” ci-dessous :

Numéro-commandeNuméro-clientDate-commande
1001102020-09-08
1002992020-09-01
1003172020-08-25
1004762020-09-19

Examinons les données du tableau “Clients” ci-dessous :

Numéro-clientNom-clientPays
76JackAmérique
17JancyAllemagne
10robertInde
99BrianChine

Notez que la colonne « Numéro-client » dans la table « Commandes » fait référence au « Numéro-client » dans la table « Clients ». La relation entre les deux tables ci-dessus est la colonne “Numéro de client”.

Voyons comment créer une instruction SQL qui sélectionne les enregistrements dont les valeurs correspondent dans les deux tables.

Exemple:

SELECT Commandes.Numero_Commande, Clients.Nom_Client, Commandes.Date_Commande
  FROM Commandes
  INNER JOIN Clients ON Commandes.Num_Clients=Clents.Num_Client;

Résultat:

Numéro-CommandeNom-ClientDate-Commande
1001robert2020-09-08
1002Brian2020-09-01
1003Jancy2020-08-25
1004Jack2020-09-19

Explication:

L’instruction SELECT démarre de la même manière que toutes les instructions que vous avez vues jusqu’ici, en spécifiant les colonnes à récupérer. La grande différence ici est que deux des colonnes spécifiées (Orderid et Orderdate) se trouvent dans une table, tandis que l’autre (Customername) se trouve dans une autre table.

Contrairement à toutes les instructions SELECT précédentes, celle-ci comporte deux tables répertoriées dans la clause FROM, Orders et Customers.

Vous devez utiliser le nom de colonne complet (table et colonne séparées par un point) chaque fois qu’il existe une éventuelle ambiguïté quant à la colonne à laquelle vous faites référence. Dans ce cas, vous spécifiez Orders.Orderid et Customers.Customername.

Types de jointures :

Il existe deux types de jointures :

  • Inner Join – Récupère les valeurs correspondantes dans les deux tables.
  • Outer Join – Récupère tous les enregistrements de la table de gauche et les enregistrements correspondants de la table de droite.
    1. Left Outer Join – Récupère tous les enregistrements de la table de gauche et les enregistrements correspondants de la table de droite.
    2. Right Outer Join – Récupère tous les enregistrements de la table de droite et les enregistrements correspondants de la table de gauche.
    3. Full Outer Join – Récupère tous les enregistrements des deux tables.