COURS SQL.pdf

COURS SQL Types de chaînes de caractères sous Oracle VARCHAR2 : Principal type pour les chaînes de caractères, avec une longueur maximale définie obligatoirement. 1. Concaténation de chaînes Fonction CONCAT : Permet de joindre deux chaînes de caractères. Sy...

COURS SQL Types de chaînes de caractères sous Oracle VARCHAR2 : Principal type pour les chaînes de caractères, avec une longueur maximale définie obligatoirement. 1. Concaténation de chaînes Fonction CONCAT : Permet de joindre deux chaînes de caractères. Syntaxe : `CONCAT(char1, char2)` Exemple : SELECT CONCAT('Edouard', ' Martin') "Nom complet" FROM DUAL; Opérateur `||`: Plus flexible, permet de concaténer plusieurs chaînes. Exemple : SELECT 'Edouard' || ' ' || 'Martin' "Nom complet" FROM DUAL; ### 2. **Extraction et modification de sous-chaînes** - **Extraction de sous-chaîne : `SUBSTR`** - Syntaxe : `SUBSTR(string, position, [length])` - Exemple : ```sql SELECT SUBSTR('ABCDEFGHI', 4, 2) FROM DUAL; -- Résultat : DE ``` - **Modification de sous-chaîne : `REPLACE`** - Remplace une sous-chaîne dans une chaîne par une autre. - Syntaxe : `REPLACE(char, search_string, [replacement_string])` - Exemple : ```sql  SELECT REPLACE('JACK & BLUE', 'BLUE', 'RED') FROM DUAL; -- Résultat : JACK & RED ``` - **Transformation de caractères : `TRANSLATE`** - Remplace chaque caractère selon un mappage. - Syntaxe : `TRANSLATE(string, charset_to_replace, replacing_charset)` - Exemple : ```sql SELECT TRANSLATE('1.234.567,89', '.,', ',.') FROM DUAL; -- Résultat : 1,234,567.89 ``` ### 3. **Suppression de caractères indésirables** - **Fonction `TRIM`** - Enlève les caractères indésirables en début, fin ou les deux d'une chaîne. - Syntaxe : `TRIM([LEADING | TRAILING | BOTH] trim_char FROM string)` - Exemple : ```sql SELECT TRIM(BOTH '-' FROM '---SQL---') FROM DUAL; -- Résultat : SQL ``` - **Fonctions `LTRIM` et `RTRIM`** - Suppriment respectivement les caractères en début ou en fin de chaîne. - Exemples : ```sql SELECT LTRIM('---SQL---', '-') FROM DUAL; -- Résultat : SQL--- SELECT RTRIM('---SQL---', '-') FROM DUAL; -- Résultat : ---SQL ``` ### 4. **Longueur d'une chaîne : `LENGTH`** - Renvoie la longueur d'une chaîne de caractères. - Syntaxe : `LENGTH(string)` - Exemple : ```sql SELECT LENGTH('I love SQL!') FROM DUAL; -- Résultat : 10 ``` ### 5. **Recherche d'une sous-chaîne : `INSTR`** - Trouve la position d'une sous-chaîne dans une chaîne. - Syntaxe : `INSTR(string, substring, [start], [occurrence])` - Exemple : ```sql SELECT INSTR('SQL et NoSQL, c''est du SQL', 'SQL') FROM DUAL; -- Résultat : 1 ``` ### 6. **Manipulation des majuscules/minuscules** - **`LOWER`** : Convertit une chaîne en minuscules. - **`UPPER`** : Convertit une chaîne en majuscules. - **`INITCAP`** : Met en majuscule la première lettre de chaque mot. - Exemple : ```sql SELECT LOWER('I love SQL!'), UPPER('I love SQL!'), INITCAP('i love sql!') FROM DUAL; ``` --- ### **Manipulation des dates sous Oracle** #### 1. **Obtenir la date/heure actuelle** - **`SYSDATE`** : Retourne la date/heure actuelle sans la précision des fractions de seconde. - **`SYSTIMESTAMP`** : Retourne la date/heure actuelle avec les fractions de seconde. - Exemple : ```sql SELECT SYSDATE, SYSTIMESTAMP FROM DUAL; ``` #### 2. **Formatage de dates : `TO_CHAR`** - Convertit une date en chaîne selon un format spécifique. - Syntaxe : `TO_CHAR(date, format)` - Exemple : ```sql SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL; ``` #### 3. **Création de dates : `TO_DATE`** - Convertit une chaîne en date selon un format spécifié. - Syntaxe : `TO_DATE(string, format)` - Exemple : ```sql SELECT TO_DATE('20240821', 'YYYYMMDD') FROM DUAL; ``` #### 4. **Addition de mois à une date : `ADD_MONTHS`** - Ajoute un nombre de mois à une date.  - Syntaxe : `ADD_MONTHS(date, nb_months)` - Exemple : ```sql SELECT ADD_MONTHS(SYSDATE, 2) FROM DUAL; ``` #### 5. **Modification de dates par addition de jours ou fractions de jours** - Additionner ou soustraire des jours, heures, minutes ou secondes à une date. - Exemple : ```sql SELECT SYSDATE + 1 "Demain", SYSDATE + 1/24 "Dans une heure" FROM DUAL; ``` #### 6. **Arrondi et tronquage de dates : `ROUND` et `TRUNC`** - **`TRUNC`** : Tronque la date à une période (jour, mois, année). - **`ROUND`** : Arrondit la date à la période la plus proche. - Exemple : ```sql SELECT TRUNC(SYSDATE, 'MM') "Début du mois", ROUND(SYSDATE, 'WW') "Début de la semaine" FROM DUAL; ``` --- ### **Jointures internes et externes** #### 1. **Jointure interne : `JOIN... ON`** - Permet de lier deux tables en fonction d'une condition de jointure. - Syntaxe : ```sql SELECT * FROM Table1 JOIN Table2 ON (Table1.id = Table2.id); ``` #### 2. **Jointure externe gauche : `LEFT JOIN`** - Récupère toutes les lignes de la table à gauche, même si aucune correspondance n'est trouvée dans la table à droite. - Syntaxe : ```sql SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1.id = Table2.id); ``` #### 3. **Jointure externe droite : `RIGHT JOIN`** - Inverse de la jointure gauche, récupère toutes les lignes de la table à droite. - Syntaxe : ```sql SELECT * FROM Table1 RIGHT JOIN Table2 ON (Table1.id = Table2.id); ``` #### 4. **Jointure externe complète : `FULL JOIN`** - Combine les résultats des jointures gauche et droite. - Syntaxe : ```sql SELECT * FROM Table1 FULL JOIN Table2 ON (Table1.id = Table2.id); ``` ### Fiche de cours : **Les jointures externes en SQL** Les **jointures externes** permettent de combiner des enregistrements provenant de plusieurs tables, en incluant tous les enregistrements d’au moins l’une des tables, même s'il n'y a pas de correspondance dans les autres tables. Les champs sans correspondance sont remplis par des valeurs `NULL`. --- ### 1. **Types de jointures externes** 1. **Jointure externe gauche (`LEFT JOIN` ou `LEFT OUTER JOIN`)** : - **Description** : Récupère toutes les lignes de la table située à gauche de l’opérateur, et les lignes correspondantes de la table à droite. Si aucune correspondance n’est trouvée, les colonnes de la table à droite sont remplies de `NULL`. - **Utilisation** : Idéal pour les rapports où tous les enregistrements d'une table doivent apparaître, même sans correspondance. **Syntaxe** : ```sql SELECT * FROM Table1 LEFT JOIN Table2 ON (Table1.att1 = Table2.att2); ``` - **Exemple** : Trouver tous les morceaux, même ceux qui n’ont jamais été achetés. ```sql SELECT t.* FROM TRACK t LEFT JOIN INVOICELINE i ON (t.TRACKID = i.TRACKID); ```  ➔ Si vous voulez uniquement les morceaux qui n'ont jamais été achetés : ```sql SELECT t.* FROM TRACK t LEFT JOIN INVOICELINE i ON (t.TRACKID = i.TRACKID) WHERE i.INVOICELINEID IS NULL; ``` 2. **Jointure externe droite (`RIGHT JOIN` ou `RIGHT OUTER JOIN`)** : - **Description** : Fonctionne comme la jointure externe gauche, mais récupère toutes les lignes de la table située à droite de l’opérateur, même s’il n’y a pas de correspondance avec la table de gauche. Les colonnes de la table de gauche seront remplies de `NULL` si aucune correspondance n’est trouvée. **Syntaxe** : ```sql SELECT * FROM Table1 RIGHT JOIN Table2 ON (Table1.att1 = Table2.att2); ``` - **Exemple** : Trouver toutes les lignes de factures, même si elles n'ont pas de correspondance avec un morceau. ```sql SELECT t.* FROM INVOICELINE i RIGHT JOIN TRACK t ON (i.TRACKID = t.TRACKID); ``` 3. **Jointure externe complète (`FULL JOIN` ou `FULL OUTER JOIN`)** : - **Description** : Combine les résultats d'une jointure externe gauche et d'une jointure externe droite. Elle retourne toutes les lignes des deux tables, qu’il y ait ou non correspondance. Les lignes sans correspondance dans l’une des tables auront des valeurs `NULL` dans les colonnes de l’autre. - **Utilisation** : Pour obtenir un ensemble complet de données, en consolidant les informations de plusieurs sources. **Syntaxe** : ```sql SELECT * FROM Table1 FULL JOIN Table2 ON (Table1.att1 = Table2.att2); ``` - **Exemple** : Liste des employés et des clients, y compris ceux sans correspondance. ```sql SELECT * FROM CUSTOMER c FULL JOIN EMPLOYEE e ON (c.SUPPORTREPID = e.EMPLOYEEID); ``` ➔ Résultat : - Les employés et leurs clients : 59 lignes - Les employés sans clients (ex. : managers) : 5 lignes - Les clients sans employés associés (nouveaux clients) : n lignes --- ### 2. **Cas d’usage des jointures externes** - **Rapports complets** : Obtenir la liste de tous les clients avec leurs commandes, même pour ceux qui n’ont pas encore passé de commande. Exemple : ```sql SELECT c.*, o.ORDERID FROM CUSTOMER c LEFT JOIN ORDERS o ON c.CUSTOMERID = o.CUSTOMERID; ``` - **Analyse des données manquantes** : Identifier les produits qui n’ont pas encore été vendus. Exemple : ```sql SELECT p.* FROM PRODUCT p LEFT JOIN INVOICELINE i ON p.PRODUCTID = i.PRODUCTID WHERE i.INVOICELINEID IS NULL; ``` - **Consolidation des données** : Consolider les informations des employés et de leurs départements, même pour les employés qui ne sont pas affectés à un département. Exemple : ```sql SELECT e.*, d.DEPARTMENTNAME  FROM EMPLOYEE e LEFT JOIN DEPARTMENT d ON e.DEPARTMENTID = d.DEPARTMENTID; ``` - **Gestion des relations optionnelles** : Voir toutes les commandes et leur livraison, y compris les commandes qui n’ont pas encore été livrées. Exemple : ```sql SELECT o.*, l.DELIVERYDATE FROM ORDERS o LEFT JOIN DELIVERY l ON o.ORDERID = l.ORDERID; ``` - **Rapports de couverture** : Générer un rapport montrant tous les produits et leurs catégories, même si certains produits ne sont pas encore catégorisés. Exemple : ```sql SELECT p.*, c.CATEGORYNAME FROM PRODUCT p LEFT JOIN CATEGORY c ON p.CATEGORYID = c.CATEGORYID; ``` --- ### 3. **Comparaison des jointures externes** | **Type de jointure** | **Résultat** | **Correspondances manquantes** | |----------------------|--------------------------------------------------------------------------------------- ---------|--------------------------------| | **LEFT JOIN** | Toutes les lignes de la table de gauche, avec les correspondances de la table de droite. | Colonnes de droite `NULL`. | | **RIGHT JOIN** | Toutes les lignes de la table de droite, avec les correspondances de la table de gauche. | Colonnes de gauche `NULL`. | | **FULL JOIN** | Toutes les lignes des deux tables, qu’il y ait correspondance ou non. | Colonnes de la table opposée `NULL` pour chaque ligne sans correspondance. | --- ### Conclusion Les jointures externes sont un outil puissant pour manipuler et analyser des données provenant de plusieurs sources en garantissant qu’aucune information importante ne soit perdue lors de l’assemblage des ensembles de données. Elles sont particulièrement utiles dans des situations où des relations partielles ou des données manquantes doivent être incluses dans les résultats. ### Fiche de cours : Opérations ensemblistes en SQL Les opérations ensemblistes permettent de combiner les résultats de plusieurs requêtes `SELECT` en un seul ensemble de résultats. Elles sont inspirées des opérations sur les ensembles en mathématiques, comme l’union, l’intersection ou la différence. --- #### 1. **Union (`UNION`)** - **Description** : Combine les résultats de deux ou plusieurs requêtes `SELECT` en un seul ensemble sans doublon. Les requêtes doivent avoir le même nombre de colonnes et des types de données compatibles. - **Utilisation** : Pour fusionner des résultats de requêtes similaires. - **Exemple** : Obtenir la liste des noms et prénoms des clients et des employés, sans doublon. ```sql SELECT LASTNAME, FIRSTNAME FROM CUSTOMER c UNION SELECT LASTNAME, FIRSTNAME FROM EMPLOYEE e; ``` - **Résultat** : Si un employé est aussi un client, ou si deux clients ont le même nom et prénom, ces résultats n’apparaîtront qu’une fois dans le résultat. --- #### 2. **Union intégrale (`UNION ALL`)** - **Description** : Comme `UNION`, mais conserve les doublons. - **Utilisation** : Pour fusionner des ensembles tout en gardant les doublons. - **Exemple** : Liste des noms et prénoms des clients et des employés, avec doublons. ```sql SELECT LASTNAME, FIRSTNAME FROM CUSTOMER c UNION ALL SELECT LASTNAME, FIRSTNAME  FROM EMPLOYEE e; ``` - **Résultat** : Si plusieurs clients ou employés ont le même nom et prénom, ils apparaîtront autant de fois que nécessaire. - **Cas pratique** : Ces opérations sont essentielles pour l’alimentation des systèmes décisionnels, par exemple pour rassembler les informations sur les clients provenant de plusieurs bases de données dans une seule table. --- #### 3. **Intersection (`INTERSECT`)** - **Description** : Retourne les enregistrements communs aux résultats de deux requêtes `SELECT`. - **Utilisation** : Pour trouver les enregistrements qui existent dans les deux ensembles de données. - **Exemple** : Trouver les clients qui sont aussi des employés. ```sql SELECT LASTNAME, FIRSTNAME FROM CUSTOMER c INTERSECT SELECT LASTNAME, FIRSTNAME FROM EMPLOYEE e; ``` - **Remarque** : Cet exemple repose sur l’hypothèse que deux personnes ayant le même nom et prénom sont les mêmes. Dans la pratique, d’autres identifiants comme l’adresse, l’e-mail ou la date de naissance devraient être utilisés pour garantir l’unicité des personnes. --- #### 4. **Différence (`MINUS`)** - **Description** : Retourne les enregistrements présents dans le résultat de la première requête, mais absents dans le résultat de la deuxième requête. - **Utilisation** : Pour identifier les différences entre deux ensembles de données. - **Exemple** : Trouver les clients qui ne sont pas des employés. ```sql SELECT LASTNAME, FIRSTNAME FROM CUSTOMER c MINUS SELECT LASTNAME, FIRSTNAME FROM EMPLOYEE e; ``` - **Remarque** : Comme pour l'intersection, il faut veiller à utiliser des identifiants uniques pour garantir la précision des résultats. --- ### Récapitulatif des opérations ensemblistes | Opération | Description | Doublons | Exemple d'utilisation | |------------------|-------------------------------------------------------------|----------|------------------ -----------------------------| | **UNION** | Combine deux ensembles, sans doublons. | Non | Fusionner des données similaires sans doublon.| | **UNION ALL** | Combine deux ensembles, avec doublons. | Oui | Fusionner des données avec conservation des doublons.| | **INTERSECT** | Retourne les enregistrements communs aux deux ensembles. | Non | Trouver les enregistrements communs. | | **MINUS** | Retourne les enregistrements présents dans le premier ensemble mais pas dans le second. | Non | Identifier les différences entre deux ensembles.| --- Ces opérations sont très utiles pour manipuler et comparer des ensembles de données provenant de différentes sources, tout en appliquant les concepts mathématiques d’union, intersection et différence.