SQL jointure

1. SQL jointure PHILIPPE BANCQUART

2. 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.

3. jointure

4. Quelles tables utiliser • Choisir les colonnes • Utiliser le schema BD • Suivre les lignes de relation. • Sales.stor_id = stores.stor_id

5. 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

6. Opérations de jointure

7. 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.

8. exercices • Liste des livres vendus par magasinAfficher : pub_name, pub_id , title table : publishers , titles • 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

9. exercices • select pub_name, publishers.pub_id , title_id from publishers , titles where publishers.pub_id = titles.pub_id • select stor_name, stores.stor_id, qty ,title_id from sales , stores where sales.stor_id = stores.stor_id

10. 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

11. 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

12. 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

13. 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

14. Jointure de plus de 2 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

15. Jointure de plus de 2 tables • 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

16. Sous requêtes • Exp : quels sont les livres oubliés par ‘'New Moon Books' ? • Trouvez la pub_id associé à ‘new age books’ select pub_id , pub_name from publishers where pub_name = 'New Moon Books'  0736 • 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

17. 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' )

18. 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.

19. 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

20. 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' ))

21. 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

22. 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

23. 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)

24. 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')