Formation

Introduction: SQL

Qu’est-ce que SQL?

  • Structured Query Language
  • Un language de programmation
  • Créer pour gérer des Relational Database Management System (RDBMS)

Les sous-langages SQL

  • Data Definition Language (DDL): Structured Query Language
  • Data Manipulation Language (DML): Créer, Lire, Mettre à jour, Supprimer
  • Data Control Language (DCL): Controler l’acces aux données dans la database

Les sous-langages SQL

  • Data Definition Language (DDL): Structured Query Language
  • Data Manipulation Language (DML): Créer, Lire, Mettre à jour, Supprimer (CRUD)
  • Data Control Language (DCL): Controler l’acces aux données dans la database

Quel type d’opération peut-on réaliser en SQL

  • CRÉER
  • READ
  • UPDATE
  • DELETE

Quel type d’opération peut-on réaliser en SQL

  • CRÉER
  • READ
  • UPDATE
  • DELETE

ok ok ok

  • En tant que Data Analyste
  • Pourquoi avez-vous besoin de SQL

ok ok ok

La plupart des entreprises utilises des RDBMS

Installation du RDBMS

Interface de dbeaver

Ouvrir un script

Commencer avec SELECT

Exemple de Query

SELECT Title, ArtistID
FROM Album

Changer le nom des éléments

Exemple de Query

SELECT Title as "Titre", ArtistId as "nArtist"
FROM Album;

Changer le nom des éléments

Selon la RDBMS utilisée :

SELECT Title as "Titre", 
       ArtistId as 'nArtist',
       AlbumId as nAlbum
FROM Album;

Selectionner l’ensemble des colonnes

SELECT *
FROM Album;

Selectionner des observations distinctes

Lorsque l’on veut afficher des observations distinctes

SELECT DISTINCT ArtistId
FROM Album;

Limiter le nombre de lignes affichées

Parfois, un aperçu du résultat suffit.
LIMIT permet de limiter le nombre de ligne à afficher

SELECT * 
FROM Album
LIMIT 50;

Selectionner avec condition

WHERE permet de filtrer les observations

SELECT Title, AlbumId 
FROM Album
WHERE ArtistId =2;

Selectionner avec condition

Avec WHERE, plusieurs type de conditions peuvent être utilisées:

  • >, <, =, <>, >=, <=
  • LIKE, NOT, IN, BETWEEN
  • IS NULL, IS NOT NULL
SELECT SELECT *
From Invoice
WHERE BillingCountry ='USA'  
-- Les valeurs non numeriques sont mises entre guillemets

Selectionner avec condition

Avec WHERE, plusieurs type de conditions peuvent être utilisées:

  • >, <, =, <>, >=, <=
  • LIKE, NOT, IN, BETWEEN
  • IS NULL, IS NOT NULL
SELECT SELECT *
From Invoice
WHERE BillingCountry IN ('USA','Canada')  
-- Les valeurs non numeriques sont mises entre guillemets

Selectionner avec condition

Avec WHERE, plusieurs type de conditions peuvent être utilisées:

  • >, <, =, <>, >=, <=
  • LIKE, NOT, IN, BETWEEN
  • IS NULL, IS NOT NULL
SELECT SELECT *
From Invoice
WHERE BillingState IS NOT NULL 
-- Les valeurs non numeriques sont mises entre guillemets

Selectionner avec plusieurs conditions

Avec WHERE, plusieurs type de conditions peuvent être utilisées:

  • OR, AND
SELECT SELECT *
From Invoice
WHERE BillingState IS NOT NULL AND BillingCountry IN ('USA','Canada')  
-- Les valeurs non numeriques sont mises entre guillemets

1.1.1 Instructions

  • 1) Selectionner dans la table Artist, l’id: 2
  • 2) Selectionner le prénom, nom et l’adresse de la table Playlist
  • 3) Reprendre la question 2 et filtrer sur les individus de la company JetBrains s.r.o.
  • 4) Afficher les différents pays où il y a des achats dans la table Invoice

Créer une nouvelle colonne

Comment créer une nouvelle colonne? > * Concaténer/Opération > * CASE > * Aggregation: MIN(), MAX(), AVG(), SUM(), COUNT()

Concaténer

Le signe “+” permet de concaténer des colonnes

SELECT *, FirstName + " " + LastName AS Name
FROM Customer;

CASE

Permet de créer une nouvelle colonne basée sur une existante.

SELECT *, 
    CASE WHEN Milliseconds < 100 THEN "short" 
         WHEN Milliseconds >= 100 AND Milliseconds <300 THEN "medium"
         ELSE "long"
    END AS Music_length
FROM Track;

Aggregation: MIN(), MAX(), AVG(), SUM(), COUNT()

Sélection uniquement de la nouvelle colonne

SELECT SELECT SUM(Total)  as "Chiffre_Affaire"
FROM Invoice;

SELECT SELECT AVG(Total)  as "Depense_Moyenne"
FROM Invoice;

Aggregation par groupe

Opération par groupe


SELECT BillingCountry, SUM(Total)  as "Chiffre_Affaire"
FROM Invoice
GROUP BY BillingCountry;

1.1.2 Instructions

  • 1) Créer une nouvelle colonne Continent: Les valeurs possibles sont “Nth America”, “Sth America” (Brazil, Chile and Argentina) et “Others” (de la table Customers)
  • 2) La chanson la plus longue dans la table Track
  • 3) Chanson la plus longue par genre de la table Track
  • 4) Compter le nombre de chansons par genre

Filtrer les operations par groupe

Ajouter des filtres sur des aggrégats:


SELECT BillingCountry, SUM(Total)  as "Chiffre_Affaire"
FROM Invoice
GROUP BY BillingCountry
HAVING Chiffre_Affaire>100;

SQL : langage déclaratif

L’ordre d’exécution est différent de l’ordre d’écriture :

  • FROM
  • WHERE
  • GROUP BY
  • SELECT
  • HAVING

Attention: Différence entre WHERE et HAVING

WHERE: À appliquer avant l’opération par groupe
HAVING: Filtre appliqué apres l’opération par groupe


SELECT BillingCountry, SUM(Total)  as "Chiffre_Affaire"
FROM Invoice
WHERE BillingCountry NOT IN ("Chile", "Brazil", "Argentina")
GROUP BY BillingCountry;

Attention: Différence entre WHERE et HAVING

WHERE: À appliquer avant l’opération par groupe
HAVING: Filtre appliqué apres l’opération par groupe


SELECT BillingCountry, SUM(Total)  as "Chiffre_Affaire"
FROM Invoice
WHERE BillingCountry NOT IN ("Chile", "Brazil", "Argentina")
GROUP BY BillingCountry
HAVING Chiffre_Affaire>100;

ORDER BY

Parfois il est utile d’ordonner les sorties avec le mot clef ORDER BY.
Le mot-clef DESC peut être ajouté pour un ordre décroissant.


SELECT BillingCountry, Total
FROM Invoice
ORDER BY Total --DESC;

JOINTURE

Il est utile de joindre des tables pour répondre à certaines questions
Joindre des tables implique des informations communes.

JOINTURE

Il est utile de joindre des tables pour répondre à certaines questions
Joindre des tables implique des informations communes.

CLEF PRIMAIRE

Chaque table est composé d’une colonne correspondant à un identifiant unique par observation.
Cet identifiant est appelé clef primaire.

CLEF PRIMAIRE

Donnez un exemple de clef primaire pour :

  • la sécurité sociale
  • les grandes surfaces
  • les comptes réseaux sociaux

CLEF ETRANGERE

Une colonne qui correspond à une clef primaire dans une autre table mais qui n’est plus unique dans une nouvelle table.
La clef étrangère va permettre de faire le lien entre 2 tables

TYPE DE JOINTURE

INNER JOIN


SELECT Album.Title as Album, Artist.Name
FROM Album
INNER JOIN Artist ON Album.ArtistId = Artist.ArtistId;

  • Uniquement les observations ayant un même ArtistId dans les 2 tables

LEFT JOIN


SELECT Album.Title as Album, Artist.Name
FROM Album
LEFT JOIN Artist ON Album.ArtistId = Artist.ArtistId;

  • Toutes les observations ayant un ArtistId dans la table Album

  • Les observations ayant un ArtistId présent dans la table Artist mais absent dans Album ne sont pas reprises

FULL JOIN


SELECT Album.Title as Album, Artist.Name
FROM Album
FULL JOIN Artist ON Album.ArtistId = Artist.ArtistId;

  • Toutes les observations des 2 tables

  • Présence de case vide là où il n’y a pas de correspondance

1.1.3 Instructions

  • 1) Joindre toutes les observations des tables Track et InvoiceLine (full join)
  • 2) Selectionner Quantity dans InvoiceLine et selectionner Name, Composer et UnitPrice, TrackId dans Track
  • 3) Remplacer les quantités nules par 0
  • 4) Effectuer à la place un left join
  • 5) Est-ce qu’il y a encore des quantités nules ?

Comment joindre 3 tables ou plus?


  SELECT trk.AlbumId, trk.Name, inv.UnitPrice, 
         inv.Quantity, gen.Name  as Genre
    FROM InvoiceLine as inv
    LEFT JOIN Track as trk ON inv.TrackId = trk.TrackId 
    LEFT JOIN Genre as gen ON trk.GenreId = gen.GenreId

DATE

Travailler avec les dates

  • Lire une table à une date particulière
  • Effectuer des opérations entre 2 dates
  • Modifier une date

Selectionner une date particulière

  • Via les fonctions STRFTIME(), DATENAME()
  • STRFTIME('%Y %m %d', colonne)
  • Dans la clause SELECT

  SELECT *, STRFTIME('%Y', InvoiceDate) as Year
    FROM Invoice

Date dans la condition WHERE


  SELECT *, STRFTIME('%Y', InvoiceDate) as Year
    FROM Invoice
    WHERE STRFTIME('%m', InvoiceDate)="01"

Opérations entre 2 dates

  • Utilisation de la fonction JULIANDAY() qui compte le nombre de jours

  SELECT *, 
      ROUND((JULIANDAY(HireDate)-JULIANDAY(BirthDate))/365.25,0) as AgeHire
    FROM Employee

Modifier une date

  • Ajouter des jours ou des années à une date

 SELECT *, date(HireDate,'+14 day') as NewHireDate; 
    FROM Employee

Numéroter de lignes en fonction de groupe

  
 SELECT *, 
  ROW_NUMBER() OVER(PARTITION BY InvoiceID ORDER BY TrackId ASC) AS RowNumber
 FROM InvoiceLine
  • ROW_NUMBER() pour compter le numéro de la ligne
  • PARTITION BY pour déterminer la variable de groupe
  • ORDER BY pour déterminer l’ordre

DETAILS

CamelCase ou snake_case

Il y a 2 type de syntaxe lorsqu’il s’agit décrire du code et de nommer les variables:

  • CamelCase : Commencer chaque mot par une majuscule
  • snake_case : Pas de majuscules mais séparer les mots par un _
  • Les 2 sont utilisés

Merci pour votre attention

Questions?

Source

dplyr

Contact

Kevin Rosamont Prombo