MODULE BDDMODULE BDDMathieu GOULIN :

mathieu.goulin@gadz.org

ASRALL

2021

Le Langage SQL

SOMMAIRESOMMAIREPartie 1: Prérequis

Comprendre ce qu'est une base de donnéesAlgèbre relationnelleLe langage SQL

SOMMAIRESOMMAIREPartie 2: Administration

Motivations?Sécurité/intégritéSauvegarde/restaurationPerformancesGestion du changementMise en œuvre

LE LANGAGE SQLLE LANGAGE SQLStructured Query Language

Langage Informatique normalisé qui sert àeffectuer des opérations sur des bases de données.C’est un langage interprété

4 grandes parties:

DML: Data Manipulation Langage ou Langage demanipulation de données (LMD)

Peupler ou interroger les relationsDDL: Data De�nition Langage ou Langage dedé�nition de données (LDD)

Créer, supprimer, modi�er les relationsLe contrôle des transactions

Application des changements ou points de reprisesLangage de contrôle de données (LCD)

Attribuer ou retirer des droits

Types de données

Type dedonnées

Description

VARCHAR(size) Données de type caractère delongueur variable

CHAR(size) Données de type caractère delongueur �xe

INT / BIGINT Nombre entier

DATE Valeurs de date et d'heure

REAL Nombre réel

Type dedonnées

Description

FLOAT Nombre réel (précision doublé : 25à 53 bits)

D’AUTRES TYPES EXISTENTD’AUTRES TYPES EXISTENT

LES SCHEMASLES SCHEMASUne base de données contient un ou plusieurs schémasnommés qui, eux, contiennent des tables. Les schémas

contiennent aussi d'autres types d'objets nommés(types de données, fonctions et opérateurs, par

exemple).

LES TABLESLES TABLESUne table est un ensemble d'enregistrementsUne table n’est pas ordonnéeOn représente une table par son nom et ses attributs

Employees (employee_id, nom, prenom,

job_id, dept_id)

CLÉCLÉUne clé est un attribut pour lequel les valeurs sont

uniques.

Employees (employee_id, nom, prenom, job_id, dept_id

Les instructions SQL :Ne distinguent pas les majuscules des minuscules.Peuvent être écrites sur une ou plusieurs lignes.Doivent être terminées par un point-virgule (;).

Les mots-clés ne peuvent pas être abrégés ourépartis sur plusieurs lignes.Les clauses sont généralement placées sur des lignesdistinctes.Des indentations sont utilisées pour améliorer lalisibilité.

DMLDMLExtraire des données à l'aide de l'instruction SELECT

L’utilisation la plus courante de SQL consiste à lire desdonnées issues de la base de données. Cela s’effectue

grâce à la commande SELECT, qui retourne desenregistrements dans un tableau de résultat. Cette

commande peut sélectionner une ou plusieurs colonnesd’une table.

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] * | expression [ AS nom_d_affichage ] [, ...] [ FROM éléments_from [, ...] ] [ WHERE condition ] [ GROUP BY expression [, ...] ] [ HAVING condition [, ...] ] [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ] [ ORDER BY expression [ ASC | DESC | USING opérateur ] [, ... [ LIMIT { nombre | ALL } ] [ OFFSET début ] [ FOR { UPDATE | SHARE } [ OF nom_table [, ...] ] [ NOWAIT ]

Toutes les colonnes

SELECT * FROM departements;

Restrictions des colonnes

SELECT department_id, manager_id FROM departements;

Expressions arithmétiques

Créez les expressions avec des données de typenombre et date à l'aide d'opérateurs arithmétiques.

Opérateur Description

+ Ajouter

- Soustraire

* Multiplier

/ Diviser

SELECT last_name, salary, salary + 300 FROM employees;

Priorité des opérateurs

SELECT last_name, salary, 12*salary + 300 FROM employees;

SELECT last_name, salary, 12*(salary + 300) FROM employees;

ALIAS DE COLONNESALIAS DE COLONNESUn alias de colonne :

Renomme un en-tête de colonneEst utile avec les calculsSuit immédiatement le nom d'une colonne (le mot-clé facultatif AS peut également être utilisé entrele nom de la colonne et l'alias)Nécessite des guillemets s'il contient des espacesou des caractères spéciaux, ou s'il distingue lesmajuscules des minuscules

SELECT last_name AS name, commission_pct com FROM employees;

SELECT last_name "Name", salary*12 "Annual Salary" FROM employees;

Opérateur de concatenation

Un opérateur de concaténation :Lie des colonnes ou des chaînes de caractères àd'autres colonnesEst représenté par deux barres verticales (||)

SELECT last_name||job_id AS "Employees" FROM employees;

SELECT last_name ||' is a '||job_id AS "Employee Details" FROM employees;

LIMITER LES LIGNESLIMITER LES LIGNESSÉLECTIONNÉESSÉLECTIONNÉES

Restreindre les lignes renvoyées à l'aide de la clauseWHERE :

La clause WHERE suit la clause FROM.

SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)];

SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ;

CHAÎNES DE CARACTÈRES ET DATESCHAÎNES DE CARACTÈRES ET DATESLes chaînes de caractères et les dates sont inclusesentre apostrophes.Les valeurs de type caractère distinguent lesmajuscules des minuscules et les valeurs de date sontsensibles au format.

SELECT last_name, job_id, department_id FROM employees WHERE last_name = 'Whalen' ;

CONDITIONS DE COMPARAISONCONDITIONS DE COMPARAISONOpérateur Signi�cation

= Egal à

> Supérieur à

>= Supérieur ou égal à

< Inférieur à

<= Inférieur ou égal à

<> Non égal à

CONDITIONS DE COMPARAISONCONDITIONS DE COMPARAISONOpérateur Signi�cation

BETWEEN...AND...

Entre deux valeurs (incluses)

IN(set) Correspond à une valeurquelconque d'une liste

LIKE Correspond à un modèle decaractère

IS NULL Est une valeur NULL

SELECT last_name, salary FROM employees WHERE salary <= 3000 ;

UTILISER LA CONDITION BETWEENUTILISER LA CONDITION BETWEENSELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500 ;

UTILISER LA CONDITION INUTILISER LA CONDITION INSELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201) ;

UTILISER LA CONDITION LIKEUTILISER LA CONDITION LIKEUtilisez la condition LIKE pour effectuer desrecherches de chaînes de caractères valides vial'utilisation de caractères génériquesLes conditions de recherche peuvent contenir soitdes caractères littéraux, soit des nombres :

'%' indique un nombre quelconque de caractères(zéro ou plus).'_' indique un caractère unique.

SELECT first_name FROM employees WHERE first_name LIKE 'S%' ;

UTILISER LA CONDITION LIKEUTILISER LA CONDITION LIKEVous pouvez combiner des caractères de mise encorrespondance de modèle :

SELECT last_name FROM employees WHERE last_name LIKE '_o%' ;

UTILISER LA CONDITION NULLUTILISER LA CONDITION NULLTestez la présence de valeurs NULL avec l'opérateurIS NULL

SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL ;

CODITIONS LOGIQUESCODITIONS LOGIQUESOpérateur Signi�cation

AND Renvoie TRUE si les deux conditionssont vraies.

OR Renvoie TRUE si l'une des deuxconditions est vraie.

NOT Renvoie TRUE si la condition qui suit estfausse.

L'opérateur AND nécessite que les deux conditionssoient vraies :

SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >=10000Utiliser l'opérateur AND AND job_id LIKE '%MAN%' ;

L'opérateur OR nécessite que l'une des deux conditionssoit vraie :

SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >= 10000 Utiliser l'opérateur OR OR job_id LIKE '%MAN%' ;

Utiliser l'opérateur NOT :

SELECT last_name, job_id FROM employees WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP') ;

Règles de priorité 1/2

Ordre Opérateurs

1 Opérateurs arithmétiques

2 Opérateurs de concatenation

3 Conditions de comparaison

4 IS [NOT] NULL, LIKE, [NOT] IN

5 [NOT] BETWEEN

6 Non égal à

Règles de priorité 2/2

Ordre Opérateurs

7 Condition logique NOT

8 Condition logique AND

9 Condition logique OR

RÈGLES DE PRIORITÉRÈGLES DE PRIORITÉSELECT last_name, job_id, salary FROM employees WHERE job_id = 'SA_REP' OR job_id = 'AD_PRES' AND salary > 15000;

SELECT last_name, job_id, salary FROM employees WHERE (job_id = 'SA_REP' OR job_id = 'AD_PRES') AND salary > 15000;

UTILISER LA CLAUSE ORDER BYUTILISER LA CLAUSE ORDER BYTrier les lignes extraites à l'aide de la clause ORDERBY :

ASC : ordre croissant (par défaut)DESC : ordre décroissant

La clause ORDER BY vient en dernier dansl'instruction SELECT :

SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date ;

TRIERTRIERTrier par ordre décroissant :

Trier par alias de colonne :

Trier selon plusieurs colonnes :

SELECT last_name, job_id, department_id, hire_FROM employees ORDER BY hire_date DESC ;

SELECT employee_id, last_name, salary*12 annsaFROM employees ORDER BY annsal ;

SELECT last_name, department_id, salary FROM employees ORDER BY department_id, salary DESC;

EXERCICE 1EXERCICE 1

1. La table UN

2. La table DEUX

SELECT * FROM un ;

SELECT a FROM un ;

SELECT a FROM un WHERE c=1 ;

SELECT a FROM un WHERE c=1 OR c=2 ;

SELECT DISTINCT a FROM un WHERE c=1 OR c=2 ;

SELECT a FROM un ORDER BY b ;

SELECT a,e FROM un,deux ;

SELECT a,e FROM un,deux WHERE c=e ;

EXERCICE 2EXERCICE 2Af�cher le nom, prénom, mail et téléphone desemployés du département 25Af�cher par ordre alphabétique le nom et prénomdes employés qui gagnent entre 1500 et 2900.Af�cher une colonne « Je suis » qui af�che autant defois que nécessaire la phrase « est dans le service

EXERCICES 2EXERCICES 2SELECT last_name, first_name, email, phone_number FROM employees WHERE department_id = 25;

SELECT last_name, first_name FROM employees WHERE salary BETWEEN 1500 AND 2900 ORDER BY last_name;

SELECT first_name || ‘ ‘ || last_name || ‘ est dans le service ‘ || department_id AS ‘Je suis’ FROM employees;

LES FONCTIONSLES FONCTIONSExemples de fonctions monoligne

Ces fonctions convertissent la casse de chaînes decaractères :

Fonction Résultat

LOWER('SQL Course') sql course

UPPER('SQL Course') SQL COURSE

INITCAP('SQL Course') Sql Course

Af�cher le numéro, le nom et le numéro dedépartement de l'employé Higgins :

SELECT employee_id, last_name, department_id FROM employees WHERE last_name = 'higgins'; no rows selected

SELECT employee_id, last_name, department_id FROM employees WHERE LOWER(last_name) = 'higgins';

FONCTIONS NUMÉRIQUESFONCTIONS NUMÉRIQUESROUND : arrondit la valeur à une décimale donnéeTRUNC : tronque la valeur à une décimale donnéeMOD : renvoie le reste d'une division

Fonction Résultat

ROUND(45.926, 2) 45.93

TRUNC(45.926, 2) 45.92

MOD(1600, 300) 100

LES FONCTIONSLES FONCTIONSFonctions de groupe et agrégation

Les fonctions de groupe opèrent sur des ensemblesde lignes a�n de renvoyer un seul résultat par groupe

Types de fonction de groupe

AVG()COUNT()MAX()MIN()SUM()

Avec le mot-clé DISTINCT, la fonction ignore les valeursen double

SYTAXE :SYTAXE :SELECT [column,] group_function(column), ... FROM table [WHERE condition] [GROUP BY column] [ORDER BY column];

Vous pouvez utiliser les fonctions AVG et SUM pour lesdonnées numériques:

SELECT AVG(salary), MAX(salary), FROM employees WHERE job_id LIKE '%REP%';

Vous pouvez utiliser MIN et MAX pour les valeursnumériques, les valeurs de type caractère et les valeurs

de type date.

SELECT MIN(hire_date), MAX(hire_date) FROM employees;

UTILISER LA FONCTION COUNTUTILISER LA FONCTION COUNTCOUNT(*) renvoie le nombre de lignes d'une table :

SELECT COUNT(*) FROM employees WHERE department_id = 50;

UTILISER LA FONCTION COUNTUTILISER LA FONCTION COUNTCOUNT(expr) renvoie le nombre de lignes avec desvaleurs non NULL pour expr :

SELECT COUNT(commission_pct) FROM employees WHERE department_id = 80;

COUNT(DISTINCT expr) renvoie le nombre devaleurs non NULL distinctes de expr.Pour af�cher le nombre de départements distincts dela table EMPLOYEES :

SELECT COUNT(DISTINCT department_id) FROM employees;

GROUPES DE DONNÉESGROUPES DE DONNÉESUtilisation de GROUP BY et HAVING

CRÉER DES GROUPES DE DONNÉESCRÉER DES GROUPES DE DONNÉESVous pouvez diviser les lignes d'une table en groupes

plus petits à l'aide de la clause GROUP BY.

SELECT [column,] group_function(column), ... FROM table [WHERE condition] [GROUP BY column] [ORDER BY column];

CRÉER DES GROUPES DE DONNÉESCRÉER DES GROUPES DE DONNÉES

Toutes les colonnes de la liste SELECT qui ne sont pasincluses dans des fonctions de groupe doivent �gurer

dans la clause GROUP BY.

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ;

La colonne GROUP BY ne doit pas nécessairement�gurer dans la liste SELECT.

SELECT AVG(salary) FROM employees GROUP BY department_id ;

REGROUPER EN FONCTION DE PLUSIEURSREGROUPER EN FONCTION DE PLUSIEURSCOLONNESCOLONNES

REGROUPER EN FONCTION DE PLUSIEURSREGROUPER EN FONCTION DE PLUSIEURSCOLONNESCOLONNES

SELECT department_id dept_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id ;

INTERROGATIONS ILLÉGALES AVEC DESINTERROGATIONS ILLÉGALES AVEC DESFONCTIONS DE GROUPEFONCTIONS DE GROUPE

Toute colonne ou expression de la liste SELECT quine constitue pas une fonction d'agrégation doit�gurer dans la clause GROUP BY :

SELECT department_id, COUNT(last_name) FROM employees;

ERROR: column ‘employees.department_id’ must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT department_id, COUNT(last_name)…

Vous ne pouvez pas utiliser des fonctions de groupedans la clause WHERE.

Utilisez la clause HAVING pour restreindre desgroupes.

SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 8000 GROUP BY department_id;

ERROR: aggregates not allowed in WHERE clause

RESTREINDRE LES RÉSULTATS DESRESTREINDRE LES RÉSULTATS DESGROUPESGROUPES

Lorsque vous utilisez la clause HAVING, le serveurrestreint les groupes de la façon suivante :

1. Les lignes sont regroupées.2. La fonction de groupe est appliquée.3. Les groupes qui correspondent à la clause HAVING

s'af�chent.

SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];

UTILISER LA CLAUSE HAVINGUTILISER LA CLAUSE HAVINGSELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary)>10000 ;

UTILISER LA CLAUSE HAVINGUTILISER LA CLAUSE HAVINGSELECT job_id, SUM(salary) PAYROLL FROM employees WHERE job_id NOT LIKE '%REP%' GROUP BY job_id HAVING SUM(salary) > 13000 ORDER BY SUM(salary);

EXERCICESEXERCICES1. Af�chez le salaire le plus élevé, le salaire le plus faible,

la somme des salaires et le salaire moyen de tous lesemployés. Nommez les colonnes respectivementmaximum, minimum, sum et average.

2. Même question, mais pour chaque poste.3. Af�chez l'ID de poste et le salaire mensuel total de

chaque poste (somme pour tous les salariés occupantce job) dont la rémunération totale dépasse 13 000

SELECT MAX(salary) as maximum, MIN(salary) as minimum, SUM(salary) as sum, AVG(salary) as average FROM employees;

SELECT job_id, MAX(salary) as maximum, MIN(salary) as minimum, SUM(salary) as sum, AVG(salary) as average FROM employees GROUP BY job_id;

SELECT job_id, SUM(salary) FROM employees GROUP BY job_id HAVING SUM(salary) > 13000;

SOUS REQUÊTESSOUS REQUÊTES

UTILISER UNE SOUS-INTERROGATION POURUTILISER UNE SOUS-INTERROGATION POURRÉSOUDRE UN PROBLÈMERÉSOUDRE UN PROBLÈME

Qui a un salaire plus élevé que celui d'Abel ?

La sous-interrogation s'exécute une fois avantl'interrogation principale.Le résultat de la sous-interrogation est utilisé parl'interrogation principale.

SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);

SELECT last_name FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel');

RÈGLES D'UTILISATION DESRÈGLES D'UTILISATION DESSOUSINTERROGATIONSSOUSINTERROGATIONS

Incluez les sous-interrogations entre parenthèses.Placez les sous-interrogations à droite de la conditionde comparaison.La clause ORDER BY de la sous-interrogation n'estpas nécessaire, sauf si vous effectuez une analyse detype n-premiers.Utilisez des opérateurs monoligne avec les sous-interrogations monoligne, et des opérateursmultiligne avec les sousinterrogations multiligne

TYPES DE SOUS-INTERROGATIONTYPES DE SOUS-INTERROGATIONSous-interrogation monoligne

Sous-interrogation multiligne

SOUS-INTERROGATIONS MONOLIGNESOUS-INTERROGATIONS MONOLIGNERenvoient une seule ligneUtilisent des opérateurs de comparaison monoligne

EXAMPLE OPÉRATEURS SOUS-EXAMPLE OPÉRATEURS SOUS-INTERROGATIONS MONOLIGNEINTERROGATIONS MONOLIGNE

Opérateur Signi�cation

= Egal à

> Supérieur à

>= Supérieur ou égal à

< Inférieur à

<= Inférieur ou égal à

<> Non égal à

Les employee qui ont un salaraire plus haut quel'employé 143 et qui font le même job que l'employé

143

SELECT last_name, job_id, salary FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141) AND salary > (SELECT salary FROM employees WHERE employee_id = 143);

LE PLUS BAS SALAIRE ?LE PLUS BAS SALAIRE ?SELECT last_name, job_id, salary FROM employees WHERE salary = (SELECT MIN(salary) FROM employees);

LA CLAUSE HAVING AVEC DESLA CLAUSE HAVING AVEC DESSOUSINTERROGATIONSSOUSINTERROGATIONS

Le serveur exécute d'abord les sousinterrogations.Le serveur renvoie les résultats dans la clauseHAVING de l'interrogation principale.

SELECT department_id, MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50);

QUEL EST LE PROBLÈME DE CETTEQUEL EST LE PROBLÈME DE CETTEINSTRUCTION ?INSTRUCTION ?

SELECT employee_id, last_name FROM employees WHERE salary = (SELECT MIN(salary) FROM employees GROUP BY department_id);

ERROR: more than one row returned by a subquery used as an expres

SOUS-INTERROGATIONS MULTILIGNESOUS-INTERROGATIONS MULTILIGNERenvoient plusieurs lignesUtilisent des opérateurs de comparaison multiligne

Opérateur Signi�cation

IN Egal à un membre quelconque de la liste

ANY Compare la valeur à chaque valeurrenvoyée par la sous-interrogation

ALL Compare la valeur à toutes les valeursrenvoyées par la sous-interrogation

UTILISER L'OPÉRATEUR ANYUTILISER L'OPÉRATEUR ANY

Résultat de la sous-interogation : 9000, 6000, 4200

SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';

UTILISER L'OPÉRATEUR ALLUTILISER L'OPÉRATEUR ALL

Résultat de la sous-interogation : 9000, 6000, 4200

SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';

EXERCICEEXERCICE1. Af�chez en une requête le salaire moyen de tous les

employés et le salaire moyen des employés pour lesdépartement 25 et 27.

SELECT avg(salary) as moyenne, (SELECT avg(salary) FROM employees WHERE department_id=25) as 'Moyenne dpt25', (SELECT avg(salary) FROM employees WHERE department_id=27) as 'Moyenne dpt27' FROM employees;

AFFICHER DES DONNÉESAFFICHER DES DONNÉESISSUES DE PLUSIEURSISSUES DE PLUSIEURS

TABLESTABLESUnion, Intersection, Différence

L'opérateur UNION renvoie les résultats des deuxinterrogations après avoir éliminé les doublons

Af�chez les informations relatives au poste actuel etau poste précédent de tous les employés. Af�chezchaque employé une seule fois.

SELECT employee_id, job_id FROM employees UNIONSELECT employee_id, job_id FROM job_history;

L'opérateur UNION ALL renvoie les résultats desdeux interrogations en incluant tous les doublons

UTILISER L'OPÉRATEUR UNION ALLUTILISER L'OPÉRATEUR UNION ALLAf�chez les informations relatives au poste actuel etau poste précédent de tous les employés.

SELECT employee_id, job_id FROM employees UNION ALL SELECT employee_id, job_id, department_id FROM job_history ORDER BY employee_id;

L'opérateur INTERSECT renvoie les lignes qui sontcommunes aux deux interrogations.

UTILISER L'OPÉRATEUR INTERSECTUTILISER L'OPÉRATEUR INTERSECTAf�chez l'ID d'employé et l'ID de poste des employésdont le poste actuel est le même que celui qu'ilsoccupaient lors de leur embauche (c'est-à-dire ceuxqui ont changé de poste, mais qui occupentaujourd'hui le même poste qu'à l'origine).

SELECT employee_id, job_id FROM employees INTERSECT SELECT employee_id, job_id FROM job_history;

L'opérateur MINUS renvoie les lignes de la premièreinterrogation qui ne sont pas présentes dans la

deuxième interrogation

UTILISER L'OPÉRATEUR MINUSUTILISER L'OPÉRATEUR MINUSAf�chez l'ID des employés qui ont changé de poste.

SELECT employee_id,job_id FROM employees MINUS SELECT employee_id,job_id FROM job_history;

AFFICHER DES DONNÉESAFFICHER DES DONNÉESISSUES DE PLUSIEURSISSUES DE PLUSIEURS

TABLESTABLESLes jointures

AFFICHER DES DONNÉES ISSUES DEAFFICHER DES DONNÉES ISSUES DEPLUSIEURS TABLESPLUSIEURS TABLES

JOINDRE DES TABLES À L'AIDE DE LAJOINDRE DES TABLES À L'AIDE DE LASYNTAXE SQLSYNTAXE SQL

Une jointure sert à interroger des données deplusieurs tables

Ecrivez la condition de jointure dans la clauseWHERE.

Placez le nom de la table avant le nom de la colonnelorsque celui-ci �gure dans plusieurs tables.

SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2;

DÉFINITION D'UNE ÉQUIJOINTUREDÉFINITION D'UNE ÉQUIJOINTURE

EXTRAIRE DES ENREGISTREMENTS À L'AIDEEXTRAIRE DES ENREGISTREMENTS À L'AIDED'ÉQUIJOINTURESD'ÉQUIJOINTURES

SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_id FROM employees, departments WHERE employees.department_id = departments.department_id;

DIFFÉRENCIER LES NOMS DE COLONNEDIFFÉRENCIER LES NOMS DE COLONNEUtilisez des pré�xes qui précisent le nom de la tablepour différencier les noms de colonne appartenant àplusieurs tables.L'utilisation de pré�xes désignant la table amélioreles performances.Différenciez des colonnes de même nom appartenantà plusieurs tables en utilisant des alias de colonne.

UTILISER DES ALIAS DE TABLEUTILISER DES ALIAS DE TABLESimpli�ez les interrogations à l'aide des alias de table.L'utilisation de pré�xes désignant la table amélioreles performances.

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e , departments d WHERE e.department_id = d.department_id;

JOINDRE PLUS DE DEUX TABLESJOINDRE PLUS DE DEUX TABLESPour joindre n tables entre elles, il faut au minimumn-1 conditions de jointure. Par exemple, deuxjointures au moins sont nécessaires pour joindre trois

tables.

NON-ÉQUIJOINTURESNON-ÉQUIJOINTURES

EXTRAIRE DES ENREGISTREMENTS À L'AIDEEXTRAIRE DES ENREGISTREMENTS À L'AIDEDE NON-ÉQUIJOINTURESDE NON-ÉQUIJOINTURES

SELECT e.last_name, e.salary, j.grade_level FROM employees e, job_grades j WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

JOINDRE UNE TABLE À ELLE-MÊMEJOINDRE UNE TABLE À ELLE-MÊME

JOINDRE UNE TABLE À ELLE-MÊMEJOINDRE UNE TABLE À ELLE-MÊMESELECT worker.last_name || ' works for ' || manager.last_name FROM employees worker, employees manager WHERE worker.manager_id = manager.employee_id ;

JOINDRE DES TABLES À L'AIDE DE LAJOINDRE DES TABLES À L'AIDE DE LASYNTAXE SQL1999SYNTAXE SQL1999

Une jointure permet d'interroger des données deplusieurs tables

SELECT table1.column, table2.column FROM table1 [CROSS JOIN table2] | [NATURAL JOIN table2] | [JOIN table2 USING (column_name)] | [JOIN table2 ON(table1.column_name = table2.column_name)] | [LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)];

La clause CROSS JOIN génère le produit cartésien dedeux tables.

SELECT last_name, department_name FROM employees CROSS JOIN departments ;

GÉNÉRER UN PRODUIT CARTÉSIENGÉNÉRER UN PRODUIT CARTÉSIEN

GÉNÉRER UN PRODUIT CARTÉSIENGÉNÉRER UN PRODUIT CARTÉSIENUn produit cartésien est généré :

lorsqu'une condition de jointure est omise,lorsqu'une condition de jointure est incorrecte,lorsque toutes les lignes de la première table sontjointes à toutes les lignes de la seconde.

Pour éviter tout produit cartésien, insérez unecondition de jointure correcte dans la clauseWHERE.

CRÉER DES JOINTURES NATURELLESCRÉER DES JOINTURES NATURELLESLa clause NATURAL JOIN utilise toutes les colonnesdes deux tables portant le même nom.Elle sélectionne les lignes des deux tables dont lesvaleurs sont identiques dans toutes les colonnescorrespondantes.Une erreur est renvoyée lorsque des colonnesportant le même nom présentent des types dedonnées différents.

EXTRAIRE DES ENREGISTREMENTS À L'AIDEEXTRAIRE DES ENREGISTREMENTS À L'AIDEDE JOINTURES NATURELLESDE JOINTURES NATURELLES

SELECT department_id, department_name, location_id, city FROM departments NATURAL JOIN locations ;

CRÉER DES JOINTURES À L'AIDE DE LACRÉER DES JOINTURES À L'AIDE DE LACLAUSE USINGCLAUSE USING

Si plusieurs colonnes portent le même nom, mais nepossèdent pas le même type de données, la clauseNATURAL JOIN ne peut être employée, et doit êtremodi�ée en JOIN à l'aide de la clause USING pourindiquer les colonnes à utiliser pour une équijointure.La clause USING vous permet de n'indiquer qu'uneseule colonne lorsque plusieurs colonnes secorrespondent.

N'utilisez pas de nom ou d'alias de table dans lesnoms des colonnes référencées.Les clauses NATURAL JOIN et USING s'excluentmutuellement.

EXTRAIRE DES ENREGISTREMENTS À L'AIDEEXTRAIRE DES ENREGISTREMENTS À L'AIDEDE LA CLAUSE USINGDE LA CLAUSE USING

SELECT e.employee_id, e.last_name, d.location_id FROM employees e JOIN departments d USING (department_id) ;

CRÉER DES JOINTURES À L'AIDE DE LACRÉER DES JOINTURES À L'AIDE DE LACLAUSE ONCLAUSE ON

La condition de la jointure naturelle est uneéquijointure de toutes les colonnes portant le mêmenom.La clause ON permet d'indiquer des conditionsarbitraires ou de préciser les colonnes à joindre.La condition de jointure est distincte des autresconditions de recherche.La clause ON simpli�e la compréhension du code.

EXTRAIRE DES ENREGISTREMENTS À L'AIDEEXTRAIRE DES ENREGISTREMENTS À L'AIDEDE LA CLAUSE ONDE LA CLAUSE ON

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idFROM employees e JOIN departments d ON (e.department_id = d.department_id);

CRÉER DES JOINTURES À TROIS LIENS ÀCRÉER DES JOINTURES À TROIS LIENS ÀL'AIDE DE LA CLAUSE ONL'AIDE DE LA CLAUSE ON

SELECT employee_id, city, department_name FROM employees e JOIN departments d ON d.department_id = e.department_id JOIN locations l ON d.location_id = l.location_id;

JOINTURES INNER ET OUTERJOINTURES INNER ET OUTERLa jointure de deux tables ne renvoyant que les lignescorrespondantes est une jointure interne.Une jointure entre deux tables renvoyant le résultatde la jointure interne ainsi que les lignes sanscorrespondance de la table de gauche (ou de droite)est une jointure externe gauche (ou droite).Une jointure entre deux tables renvoyant le résultatd'une jointure interne et d'une jointure gauche etdroite est une jointure externe complète.

JOINTURE LEFT OUTER JOINJOINTURE LEFT OUTER JOIN

Cette interrogation extrait toutes les lignes de la tableEMPLOYEES (table de gauche) même s'il n'existe

aucune correspondance dans la table DEPARTMENTS.

SELECT e.last_name, e.department_id, d.department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id) ;

JOINTURE RIGHT OUTER JOINJOINTURE RIGHT OUTER JOIN

Cette interrogation extrait toutes les lignes de la tableDEPARTMENTS (table de droite) même s'il n'existeaucune correspondance dans la table EMPLOYEES.

SELECT e.last_name, e.department_id, d.department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id) ;

JOINTURE FULL OUTER JOINJOINTURE FULL OUTER JOIN

Cette interrogation extrait toutes les lignes de la tableEMPLOYEES, même s'il n'existe aucune

correspondance dans la table DEPARTMENTS. Elle

SELECT e.last_name, e.department_id, d.department_name FROM employees e FULL OUTER JOIN departments d ON (e.department_id = d.department_id) ;

extrait également toutes les lignes de la tableDEPARTMENTS, même s'il n'existe aucunecorrespondance dans la table EMPLOYEES

DMLDMLAjouter des données à l'aide de de l'instruction INSERT

LANGAGE DE MANIPULATION DE DONNÉESLANGAGE DE MANIPULATION DE DONNÉESUne instruction DML est exécutée pour :

Ajouter de nouvelles lignes à une tableModi�er des lignes existantes d'une tableSupprimer des lignes existantes d'une table

Une transaction est constituée d'un ensembled'instructions DML qui constituent une unité detravail logique

SYNTAXE DE L'INSTRUCTION INSERTSYNTAXE DE L'INSTRUCTION INSERTAjouter de nouvelles lignes à une table à l'aide del'instruction INSERT :

Avec cette syntaxe, une seule ligne est insérée à lafois.

INSERT INTO table [(column [, column...])] VALUES (value [, value...]);

INSÉRER DE NOUVELLES LIGNESINSÉRER DE NOUVELLES LIGNESInsérez une nouvelle ligne contenant des valeurspour chaque colonne.Répertoriez les valeurs dans l'ordre par défaut descolonnes de la table.Vous pouvez également indiquer les colonnes dans laclause INSERT.

Incluez les valeurs de type caractère et de type dateentre apostrophes

INSERT INTO departments(department_id, department_name, manager_id, location_id) VALUES (70, 'Public Relations', 100, 1700);

INSÉRER DES LIGNES AVEC DES VALEURSINSÉRER DES LIGNES AVEC DES VALEURSNULLNULL

Méthode implicite : omettez la colonne dans la liste.

Méthode explicite : indiquez le mot-clé NULL dans laclause VALUES.

INSERT INTO departments (department_id, department_name ) VALUES (30, 'Purchasing');

INSERT INTO departments VALUES (100, 'Finance', NULL, NULL); VALUES (30, 'Purchasing');

COPIER DES LIGNES À PARTIR D'UNE AUTRECOPIER DES LIGNES À PARTIR D'UNE AUTRETABLETABLE

Ecrivez l'instruction INSERT avec unesousinterrogation :

N'utilisez pas la clause VALUES.Mettez en correspondance le nombre de colonnes dela clause INSERT avec celui de la sous-interrogation.

INSERT INTO sales_reps(id, name, salary, commission_pct) SELECT employee_id, last_name, salary, commission_pct FROM employees WHERE job_id LIKE '%REP%';

DMLDMLModi�er des données à l'aide de de l'instruction

UPDATE

MODIFIER DES DONNÉES DANS UNE TABLEMODIFIER DES DONNÉES DANS UNE TABLE

SYNTAXE DE L'INSTRUCTION UPDATESYNTAXE DE L'INSTRUCTION UPDATEModi�er des lignes existantes à l'aide de l'instruction

UPDATE

UPDATE table SET column = value [, column = value, ...] [WHERE condition];

METTRE À JOUR DES LIGNES DANS UNEMETTRE À JOUR DES LIGNES DANS UNETABLETABLE

Des lignes spéci�ques sont modi�ées si vous indiquezla clause WHERE

Toutes les lignes de la table sont modi�ées si vousomettez la clause WHERE :

UPDATE employees SET department_id = 70

UPDATE copy_emp SET department_id = 110; WHERE employee_id = 113;

MISE À JOUR AVEC UNEMISE À JOUR AVEC UNESOUSINTERROGATIONSOUSINTERROGATION

Mettez à jour le poste et le salaire de l'employé 114a�n qu'ils correspondent à ceux de l'employé 205.

UPDATE employees SET job_id = (SELECT job_id FROM employees WHERE employee_id = salary = (SELECT salary FROM employees WHERE employee_id = 20WHERE employee_id = 114;

DMLDMLSupprimer des données

INSTRUCTION DELETEINSTRUCTION DELETEVous pouvez supprimer des lignes existantes d'une

table à l'aide de l'instruction DELETE :

DELETE [FROM] table [WHERE condition];

SUPPRIMER DES LIGNES D'UNE TABLESUPPRIMER DES LIGNES D'UNE TABLEDes lignes spéci�ques sont supprimées si vousindiquez la clause WHERE :

Toutes les lignes de la table sont supprimées si vousomettez la clause WHERE :

DELETE FROM departments WHERE department_name = 'Finance';

DELETE FROM copy_emp;

SUPPRIMER DES LIGNES EN FONCTIONSUPPRIMER DES LIGNES EN FONCTIOND'UNE AUTRE TABLED'UNE AUTRE TABLE

Utilisez des sous-interrogations dans les instructionsDELETE a�n de supprimer des lignes d'une table en

fonction des valeurs d'une autre table :

DELETE FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE department_name LIKE '%Public%');

INSTRUCTION TRUNCATEINSTRUCTION TRUNCATESupprime toutes les lignes d'une table ; la table estalors vide et sa structure est intacteEst une instruction de langage de dé�nition dedonnées (DDL) et non une instruction DMLSyntaxe :

TRUNCATE TABLE table_name;

QUESTION ?QUESTION ?=> DDL (Manipuler les structures de données)