DÉVELOPPEMENT D'APPLICATIONS AVEC ...






COURS 10 - REQUÊTES SQL






Supposons qu'on travaille avec la base de données SILC.db qui contient les tables suivantes :

Etudiants Inscriptions Cours Numetud Numetud Numcours Nometud Numcours Titre Sexe Dateinscr HeuresTheorie Datenaiss Frais HeuresLab Rue NoteFinale Secteur Ville Prov Programme



Nous utilisons une requête simple pour répondre à une interrogation simple :

Tous les cours de Technologie ?

SELECT * FROM cours
WHERE secteur = 'Technologie'
 ;


Le nom et l'âge de tous les étudiants masculins ?

SELECT nometud, DATEDIFF("yy", datenaiss, today()) AS "Age"
FROM etudiants
WHERE sexe = 'M' ;


Tous les étudiants âgés de plus de 20 ans ?

SELECT numetud, nometud, datenaiss
FROM etudiants
WHERE DATEDIFF(year, DATENAISS, today()) > 20 
;




JOINTURE

Pour répondre à une interrogation où il faut extraire des champs de 2 tables, on utilise un JOIN (Jointure) ; par définition, la jointure produit une multiplication des 2 tables ; donc, si une table contient 10 enregistrements et l'autre en contient 15, le produit de la jointure en contient 150. 

Le numéro et le nom de l'étudiant, la date d'inscription et le numéro de cours et de tous les cours où l'étudiant '1234' est inscrit ?

nometud est dans etudiants, dateinscr est dans inscriptions, numcours est dans inscriptions et NUMETUD est dans les deux; donc il faut faire un SELECT dans deux tables:

SELECT numetud, nometud, dateinscr, numcours
FROM etudiants, inscriptions ;


La première chose qu'on remarque c'est qu'on obtient une erreur: "Column 'NUMETUD' found in more than one table"
En effet, la colonne NUMETUD fait partie des deux tables et l'interpreteur ne sait pas laquelle utiliser.

En SQL lorsq'une référence est ambigüe, il faut donner le nom complet de la colonne et ceci veut dire d'y ajouter le nom de table devant comme: etudiants.NUMETUD ou inscriptions.NUMETUD

Mais puisqu'on se fatigue vite d'écrire les noms au complet, il est de mise d'assigner à chaque table un surnom, un alias qui facilite l'écriture (ISQL appelle l'alias correlation name); on nomme l'alias dans le FROM:

SELECT et.numetud, nometud, dateinscr, numcours
FROM etudiants et, inscriptions in

Mais on n'a pas fini; si on exécute ce SELECT avec 3000 étudiants et 3000 inscriptions, SQL nous retourne 9,000,000 de réponses!
Le truc dans un JOIN est de spécifier qu'on veut seulement ceux qui ont une colonne commune:

SELECT et.numetud, nometud, dateinscr, numcours
FROM etudiants et, inscriptions in
WHERE et.numetud = in.numetud

La condition ET.NUMETUD = IN.NUMETUD identifie la relation entre les 2 tables.

Et ce n'est pas tout; le SELECT précédent me donnera la liste de tous les cours suivis par tous les étudiants; même si chaque étudiant ne suit qu'un cours, le résultat contiendra 3000 lignes; je voulais seulement les cours suivis par l'étudiant '1234' donc je dois finir la condition:

SELECT et.numetud, nometud, dateinscr, numcours
FROM etudiants et, inscriptions in
WHERE et.numetud = in.numetud
      AND in.numetud = '1234';



En résumé: La première condition à inclure après le WHERE dans la requête est la condition qui spécifie la clé commune dans les 2 tables.

Autres exemples:

Le numéro d'étudiant, le numéro de cours, le titre et les frais de tous les cours suivis en Commerce ?

SELECT numetud, IN.numcours, titre, frais
FROM inscriptions IN, cours CO
WHERE IN.numcours = CO.numcours
AND secteur = ''Commerce'' ;



Le nom et le programme de tous les étudiants qui payent des frais de plus que 100$?

SELECT nometud, programme
FROM etudiants X, inscriptions Y
WHERE X.numetud = Y.numetud
AND frais > 100;



Si les colonnes doivent être tirées de 3 tables, il faudra qu'il y aie 2 relations entre les colonnes communes:

Le nom de tous les étudiants et le titre ainsi que les frais de tous les cours auxquels ils sont inscrits ?

SELECT nometud, titre, frais
FROM etudiants ET, inscriptions IN, cours CO
WHERE IN.numetud = ET.numetud
      AND IN.numcours = CO.numcours ;



Le titre ainsi que les frais de tous les cours de 'Jean Smith'?

SELECT nometud, titre, frais
FROM etudiants ET, inscriptions IN, cours CO
WHERE IN.numetud = ET.numetud
      AND IN.numcours = CO.numcours
          AND nometud = 'Jean Smith';






REQUÊTES EMBOITÉES

Tous les étudiants qui habitent la même ville que l'étudiant '1234' ?

SELECT * FROM etudiants
WHERE ville = (la ville où habite '1234') ? ? ?



On pourrait le faire en deux étapes :

SELECT ville FROM etudiants
WHERE numetud = '1234' ;



noter le résultat et, en supposant que c'est GLOUCESTER,

SELECT * FROM etudiants
WHERE ville = 'GLOUCESTER' ;



Mais, dans un programme, où tout doit être automatique, ceci n'est pas du tout pratique

Cependant, SQL nous permet de faire la même chose avec une requête emboitée, c'est à dire, une requête à l'intérieur d'une requête

SELECT * FROM etudiants
WHERE ville = (SELECT ville FROM etudiants
       WHERE numetud = '1234') ;



La sous-requête doit être entre ( ); lors de l'exécution c'est d'abord la sous-requête qui est évaluée et ensuite, la valeur retournée est utilisée dans la requête principale

Notez que la sous-requête doit retourner une valeur qui correspond à ce que la requête veut ; par exemple :

SELECT * .... dans la sous-requête n'a pas de sens car la condition devient :

... WHERE ville = numero, nom, sexe, datenaiss, etc.

N.B.   On utilise une requête emboitée lorsqu'il faut extraire des données en les comparant à une autre donnée de la même table :

Quels sont les cours dans le même secteur que le cours '13639' ?

SELECT numcours, TITRE, secteur FROM cours
WHERE secteur = (SELECT secteur FROM cours
         WHERE numcours = '13639') 
ORDER BY numcours ASC ;



Quels étudiants sont plus vieux que Jean Smith ?

SELECT numetud, nometud, datenaiss, (years(datenaiss, today()) AS age
FROM etudiants WHERE datenaiss <
          (SELECT datenaiss FROM etudiants
                WHERE nometud = 'Jean Smith') 
ORDER BY age ;


On peut aussi s'en servir avec les fonctions statistiques:

Quel cours a le plus grand nombre d'heures de théorie?

SELECT numcours, titre, heurestheorie FROM cours
      WHERE heurestheorie = (SELECT MAX(heurestheorie) FROM cours

Quels cours ont plus d'heures de lab que la moyenne?

SELECT numcours, titre, heureslab FROM cours
      WHERE heureslab > (SELECT AVG(heureslab) FROM cours)






[ Page TITRE ]      [ PRÉCÉDENTE ]      [ SUIVANTE ]