epid-cpi-isaip philippe bancquart - mise à jour 24/02/2005 - page 1 sql jointure philippe bancquart

EPID-CPI-ISAIPPhilippe Bancquart - mise à jour 24/02/2005 - page 1

SQL jointure

PHILIPPE BANCQUART


Jointure L’opération de jointure recherche des données

provenant d’au moins de deux tables.

La jointure est le point de contrôle du modèle fonctionnel

Elle combine les tables en faisant correspondre des valeurs présentes dans chaque table.


jointure


Quelles tables utiliser Choisir les colonnes

Utiliser le schema BD

Suivre les lignes de relation.

Sales.stor_id = stores.stor_id


Opérations de jointure Les lignes de relation permettent de trouver la

jointure qui va permettre de connecter les tables.

Exemple relier les éditeurs avec les magasins de ventes.

4 tables dont 2 pour les relations.

Stores,sales,titles,publishers


Opérations de jointure


Opération de jointure

Select [Table.]nomColonne , […] from { table},{ table}[, …]where conditionRecherche

WHERE est impératif

Pas de jointure sur les valeurs nulles

Les colonnes qui portent le même nom dans plusieurs tables doivent être précédées d’un nom de table.


exercices

1.Liste des livres vendus par magasinAfficher : pub_name, pub_id , title table : publishers , titles

2.Lister les magasins et la quantité de livres qu’ils ont commandée (qty) par titre Afficher : stor_name, stor_id, qty ,titletable : sales , stores


exercices

1. select pub_name, publishers.pub_id , title_id from publishers , titles where publishers.pub_id = titles.pub_id

2. select stor_name, stores.stor_id, qty ,title_id from sales , stores where sales.stor_id = stores.stor_id


Jointures avec order by et group by

Affichage du revenu total par livre chaque fois qu’il est commandé.

select titles.title_id , qty , price , price * qty as 'prix total' from titles , sales where titles.title_id = sales.title_id order by price * qty

Affichage du nombre de livres commandé par chaque magasin

select stor_name, sales.stor_id , sum(qty) from sales , stores where sales.stor_id = stores.stor_id group by stor_name , sales.stor_id


ALIAS

Pour éviter de taper le nom de la table de façon repétée, on affecte un ALIAS

Afficher le nom de l’éditeur, code du titre lorsque la valeur pub_id du livre = pub_id editeur et le prix 19,99€

select pub_name, title_id from titles t , publishers p where t.pub_id = p.pub_id and price = 19.99


Alias

Liste des magasins ayant commandé moins de 35 exemplaires d’un livre.

select stor_name , qty , title_id from sales s , stores m where s.stor_id = m.stor_id and qty < 35


Jointure de plus de 2 tables

La clause FROM fournit la liste de toutes les tables impliquées dans la requêtes

La clause where doit comporter un nombre suffisant de condition de join pour établir une connexion avec toute les tables



Afficher le nom de l’auteur et celui de chacun de ses livres.

select au_fname , title from authors a, titleauthor ta , titles t where a.au_id = ta.au_id and ta.title_id = t.title_id



Afficher titre du livre et du nom du magasin où le livre est répertorié comme vendu.

select stor_name ,title from titles t, sales s, stores m where t.title_id = s.title_id and s.stor_id = m.stor_id


Sous requêtes

Exp : quels sont les livres oubliés par ‘'New Moon Books' ?

1. Trouvez la pub_id associé à ‘new age books’ select pub_id , pub_name from publishers where pub_name = 'New Moon Books' 0736

2. Trouvez les titres de livres associés aux pub_id trouvé.Select title from titles where pub_id = 0736

Utilisez une sous requête pour le même résultat


Sous requêtes

Exp : quels sont les livres oubliés par 'New Moon Books'

habituelSelect title from titles,publishers where titles.pub_id = publishers.pub_id and publishers.pub_name = 'New Moon Books'

Sous requêtesselect title from titles where pub_id = (select pub_id from publishers where pub_name = 'New Moon Books' )


Sous requêtes Une sous requêtes est une instruction select utilisé

en tant qu’expression au sein d’une autre instruction select, update, insert, delete.

La sous requête select imbriquée est résolue et il y a substitution du résultat au sein de la clause where.

Elles sont plus faciles à comprendre qu’une jointure

Utile pour les agregats impossible avec jointure.


Sous requêtes Si la clause where comporte un nom d e colonne, elle

doit impérativement être compatible avec la colonne spécifié.

Une sous requête ne peut pas comporter de clauses ORDER BY ou compute ainsi que INTO

Le DISTINCT ne peut pas être utilisé avec des sous requêtes comportant un group by


Sous requêtes Il n’y a pas de limite d’imbrication.

Afficher le titre du livre écrit par Blocher-halls

select title from titles where titles.title_id = ( select title_id from titleauthor where dbo.titleauthor.au_id = ( select au_id from dbo.authors where au_lname = 'Blotchet-Halls' ))


Sous requêtes plusieurs lignes

Si la sous requêtes retournent plusieurs lignes alors utiliser IN

Afficher les livres vendus en Californie

select distinct stor_id , sales.title_id from dbo.sales , titles where stor_id in ( select stor_id from stores where state = 'CA') and titles.title_id = sales.title_id


Sous requêtes avec comparaison

Des sous requêtes peuvent effectuer des tâches qui sont impossibles avec les clauses de jointure/

Une clause where dans une instruction select ne peut pas comporter d’agrégat

Une sous requête peut en contenir


Sous requêtes avec comparaison

Listes des livres dont le prix depasse le prix moyen de l’ensemble des livres.

select title_id , price from titles where price > avg(price) --Erreur

select title_id , price from titles where price > (select avg(price) from titles)


Sous requêtes opérateurs de comparaison

Afficher les livres qui ont reçu une avance supérieure à la l’avance minimum payée par Algodata Infosystems

select distinct title , advance from titles where advance > ( select min(advance) from titles , publishers where publishers.pub_id = titles.pub_id and pub_name = 'Algodata Infosystems')

epid-cpi-isaip philippe bancquart - mise à jour 24/02/2005 - page 1 sql jointure philippe bancquart

Documents