Afin de travailler avec des exemples concrets que vous pouvez suivre, nous allons d'abord créer deux tables temporaires nommées "personnes" et "salaire", puis ces deux tables temporaires seront utilisées pour le reste de l'exercice. L'avantage de cette approche est que vous pouvez copier et coller la partie de création de tableau, puis vous entraîner le long des étapes suivantes pour maximiser l'opportunité d'apprentissage. Veuillez noter que vous n'avez pas besoin de comprendre la partie création de table du travail à ce stade. La partie que vous devez comprendre commence après cela.

Alors, allez-y, copiez et collez ce qui suit dans votre outil SQL préféré et appuyez sur Exécuter.

DROP TABLE IF EXISTS salary;

CREATE TEMPORARY TABLE salary (city VARCHAR(30), average_salary int);

INSERT INTO
    salary
VALUES
    ('san_francisco', '54500'),
    ('seattle', '54100'),
    ('new_york', '34400'),
    ('phoenix', '31800');

DROP TABLE IF EXISTS people;

CREATE TEMPORARY TABLE people (
    person_id int,
    name VARCHAR(30),
    gender VARCHAR(30),
    location VARCHAR(30),
    birth_year int,
    birth_month VARCHAR(30),
    birth_day int,
    job_title VARCHAR(30),
    salary int
);

INSERT INTO
    people
VALUES
    (
        '1',
        'james',
        'male',
        'seattle',
        '1984',
        '9',
        '15',
        'software_developer',
        '115000'
    ),
    (
        '2',
        'mary',
        'female',
        'new_york',
        '1992',
        '1',
        '13',
        'financial_analyst',
        '183000'
    ),
    (
        '3',
        'john',
        'male',
        'san_francisco',
        '1971',
        '4',
        '22',
        'data_scientist',
        '165000'
    ),
    (
        '4',
        'patricia',
        'female',
        'phoenix',
        '1971',
        '8',
        '15',
        'physician',
        '215000'
    ),
    (
        '5',
        'michael',
        'male',
        'new_york',
        '1966',
        '1',
        '13',
        'retired',
        '25000'
    ),
    (
        '6',
        'jennifer',
        'female',
        'phoenix',
        '1994',
        '12',
        '12',
        'data_scientist',
        '165000'
    );

1. Bases

Voyons maintenant ce que nous avons créé dans les deux tables temporaires ci-dessus. Je vais les présenter sous forme de questions et de réponses afin que vous puissiez également essayer de résoudre chaque question en premier, avant de regarder la réponse que j'ai fournie. Ce que j'ai fourni n'est qu'une façon de résoudre ces questions - généralement, il y a plus d'une bonne façon d'arriver à la même réponse.

Q1 : Récupérez toutes les données disponibles dans le tableau people.

A1 :

En général, je vais d'abord fournir les réponses, puis expliquer ce qui s'est passé et comment fonctionnent différents concepts SQL, soit sous la même question, soit sous la suivante. Par exemple, Q2 expliquera ce qui s'est passé en Q1.

SELECT 
* 
FROM 
personnes ;

Résultats:

salaryEt pour référence future, voici à quoi ressemble le tableau , en utilisant ce qui suit :

SELECT 
* 
FROM 
salaire ;

Conseil de pro : à ce stade, je vous suggère de prendre un instantané de ces deux tableaux. J'ai ajouté des instructions ci-dessous sur la façon de prendre un instantané. Vous voudrez vous référer aux tableaux pour écrire des requêtes à l'avenir et faire défiler de haut en bas dans cet article pour trouver que ces deux tableaux ne seront pas amusants. Pour plus de commodité, j'ai inclus des instructions sur la façon de prendre un instantané sur Mac et Windows :

• Utilisateurs Mac — Appuyez sur ces trois touches et maintenez-les enfoncées : Maj, Commande et 4, puis sélectionnez la zone pour l'instantané. Si vous ne souhaitez pas sélectionner manuellement la zone d'instantané, appuyez simultanément sur Maj, Commande et 3 pour créer un écran d'impression.
• Utilisateurs Windows - Appuyez sur ces trois touches ensemble : Touche Windows + Maj + S, puis créez un instantané à l'aide de l'outil Snip and Sketch (je suis sur un Mac, donc cela est basé sur une recherche Google - j'espère que cela fonctionnera).

Q2 : Expliquez ce qui s'est passé dans la requête précédente.

A2 : SELECTl'instruction est utilisée pour sélectionner des données à partir d'une base de données donnée (c'est-à-dire une table). FROMest utilisé pour indiquer dans quelle base de données les données doivent être sélectionnées ("personnes" dans cet exemple). Et enfin *dit simplement tout sélectionner dans cette base de données.

Q3 : Afficher uniquement les données des colonnes suivantes : ['name', 'gender', 'job_title'].

A3 :

SELECT 
nom, 
sexe, 
job_title 
FROM 
personnes ;

Résultats:

Celui-ci était simple - nous pouvons indiquer explicitement si les colonnes doivent être sélectionnées.

Q4 : Quelles personnes travaillent à New York ?

A4 :

SELECT 
nom 
FROM 
personnes 
WHERE 
emplacement = 'new_york';

Résultats:

Comme le montre cet exemple, nous pouvons filtrer les résultats à l'aide de WHERE.

3. Agrégations

Les agrégations nous aident à effectuer des analyses de base sur les données. Les exemples incluent COUNT(), MAX(), MIN(), AVG()et SUM().

Q5 : Combien de personnes vivent à Seattle ou à Phonenix ?

A5 :

SELECT 
count(person_id) 
FROM 
people 
WHERE 
location IN ('seattle', 'phoenix');

Résultats:

Q6 : Combien d'individus vivent dans chacune des villes ?

A6 :

SELECT 
location, 
count(person_id) 
FROM 
personnes 
GROUP BY 
location ;

Résultats:

Nous savions déjà ce que COUNT()c'était à partir de la question précédente, mais maintenant il y a aussi un nouveau concept à la fin de la requête. GROUP BYL'instruction regroupe des lignes de données avec les mêmes valeurs et est utilisée avec des fonctions d'agrégation, telles que COUNT(), MAX(), MIN(), AVG()et SUM().

Q7 : Quel est le total des salaires à Phoenix et à New York ?

A7 :

SELECT 
location, 
SUM(salary) AS total_salary 
FROM 
people 
WHERE 
location = 'phoenix' 
OR location = 'new_york' 
GROUP BY 
location;

Résultats:

Il y a deux nouveaux apprentissages dans cet exemple. La première est que dans cet exemple, nous avons attribué un alias à la SUM()colonne et la colonne résultante est maintenant nommée total_salary. Si nous ne l'avions pas fait, la colonne aurait ressemblé à ceci :

Le deuxième apprentissage est une approche alternative. Notez que dans le WHERE, nous utilisons OR, qui détermine locationpeut être phoenixou new_york. Alternativement, nous aurions pu utiliser location IN ('phoenix', 'new_york'), similaire à l'approche que nous avons utilisée dans Q5. Cela ne change pas les résultats et est simplement une approche alternative.

4. Jointures

C'est l'une des fonctionnalités les plus utiles. Ce. aide à visualiser les données lorsque vous essayez de comprendre les requêtes. Regardons d'abord un exemple, puis j'expliquerai ce qui se passe.

Q8 : Montrez-moi les noms des personnes, leurs emplacements et leurs salaires, ainsi que le salaire moyen dans chaque emplacement.

R8 : Notez qu'il s'agit d'un nouveau type de question que nous n'avions pas abordé auparavant. Les noms individuels, les emplacements et leurs salaires sont disponibles dans le tableau peopletandis que le salaire moyen par emplacement est disponible dans le tableau salary. Ce dont nous avons besoin, c'est de JOINces deux tables ensemble, ce qui est possible car les deux tables ont le nom des villes en commun (bien que les colonnes soient nommées locationdans peopleet citydans salary). La façon dont nous structurons cela est la suivante :

SELECT 
p.name, 
p.location, 
p.salary, 
s.average_salary 
FROM 
personnes p 
LEFT JOIN salaire s ON p.location = s.city ;

Résultats:

Alors que s'est-il passé exactement ? Regardons la jointure de plus près :

FROM 
personnes p 
LEFT JOIN salaire s ON p.lieu = s.ville

Ce qui précède dit que regarder table peoplepuis le joindre à table salary. Ensuite, nous devons indiquer les colonnes de chaque table qui partagent les mêmes valeurs (imaginez assembler ces deux tables uniquement là où il y a des valeurs communes dans ces deux colonnes particulières). Les colonnes avec des valeurs mutuelles sont locationfrom peopleet cityfrom salary. Enfin, nous utilisons ici le concept d'alias. Par exemple, table peoplea maintenant un alias de p, tandis que table salarya un alias de s. Lorsque nous voulons indiquer la colonne locationde people, nous pouvons soit indiquer cela comme people.locationou p.location. Il en va de même pour la façon dont nous indiquons la colonne cityà partir salaryde s.city. Notez que la structure est toujours aussitable_name.column_name. Enfin, nous avons utilisé a LEFT JOINdans cet exemple. Ci-dessous, je vais discuter de ce que cela signifie et des autres moyens de rejoindre des tables.

Comme mentionné, nous avons utilisé a LEFT JOINdans cet exemple. Il est utile d'avoir l'image ci-dessous à l'esprit lorsque l'on réfléchit à son JOINfonctionnement.

Voici quelques-uns des JOINtypes les plus courants :

• JOINor INNER JOIN: Renvoie les données qui ont des valeurs correspondantes dans les tables de gauche et de droite.
• LEFT JOINor LEFT OUTER JOIN: Renvoie les données de la table de gauche et toutes les données correspondantes de la table de droite.
• RIGHT JOINou RIGHT OUTER JOIN: C'est le revers du LEFT JOIN. Il renvoie les données de la table de droite et toutes les données correspondantes de la table de gauche.
• FULL JOINor FULL OUTER JOIN: Renvoie toutes les données de la table de gauche ou de droite lorsqu'il y a une correspondance entre les deux

5. Conditionnels

Q9 : Créez une nouvelle colonne dans peoplequi décompose les titres de poste en technologies et non technologies. Renvoie uniquement les noms, les intitulés de poste, la nouvelle catégorie nommée as job_groupet les salaires.

A9 : Afin de répondre à cette question, nous devons d'abord voir quels titres de poste uniques sont disponibles en peopleutilisant la requête suivante :

SELECT 
DISTINCT job_title 
FROM 
personnes ;

Notez qu'il DISTINCTs'agit de l'instruction qui a été ajoutée pour SELECTne renvoyer que des valeurs uniques (ou distinctes) dans cette colonne, comme le montrent les résultats ci-dessous :

Maintenant que nous savons quels titres de poste existent, nous pouvons les décomposer en technologies et non technologies, comme indiqué. Pour cet exercice, utilisez votre meilleur jugement pour déterminer quel rôle est technique et ce qui n'est pas technique - une partie importante est d'apprendre à implémenter cette logique dans la requête. Par exemple, financial_analystest un rôle non technique, tandis que data_scientistest un rôle technique. La requête suivante fait exactement cela :

SELECT 
name, 
job_title, 
CASE 
  WHEN job_title IN ('software_developer', 'data_scientist') THEN 'tech' 
  WHEN job_title IN ('financial_analyst', 'physician') THEN 'non-tech' 
  ELSE job_title 
END AS job_group, 
salaire 
FROM 
people ;

Résultats:

Parlons du fonctionnement de l' CASEexpression en regardant de plus près ce que nous avons fait pour cette question :

CASE 
  WHEN job_title IN ('software_developer', 'data_scientist') THEN 'tech' 
  WHEN job_title IN ('financial_analyst', 'physician') THEN 'non-tech' 
  ELSE job_title 
END AS job_group,

Afin de mettre en œuvre cette logique, nous commençons d'abord par l' CASEexpression puis identifions les conditions, en utilisant WHEN, telles que :

WHEN job_title IN ('software_developer', 'data_scientist') THEN 'tech'

Le script ci-dessus examine d'abord la colonne job_title, et si la valeur de cette colonne pour une ligne est soit software_developerou data_scientist, il affiche ensuite tech. La même logique s'applique pour la non-techcatégorie.

Alors il y a ELSE job_title. Cette clause indique que si une valeur est rencontrée dans la colonne qui n'était pas couverte par les WHENconditions ci-dessus, renvoie la valeur qui existe dans la colonne job_title. Par exemple, si nous avions une ligne dans la colonne job_titleavec la valeur de chef, car chefne faisait pas partie de celles que nous avions incluses dans les WHENconditions (c'est-à-dire software_developer, data_scientist, financial_analystet physician), alors cette clause renverrait la valeur d'origine dans la colonne job_title, qui était chef.

La dernière partie du script est END AS job_group. CASEL' expression se termine par un ENDet AS job_groupest l'alias donné à la colonne résultante. C'est pourquoi la colonne est nommée job_groupdans le tableau des résultats.

Conseil de pro : l'ordre des instructions conditionnelles, en commençant parWHEN, est important. Une fois qu'une condition est vraie, il arrête de lire le reste des conditions et renvoie le résultat. Si aucune des conditions n'est remplie, elle renvoie la valeur de laELSEclause.

Q10 : Qu'est-ce qui job_grouprapporte le plus d'argent en moyenne ? Ordonnez les résultats du plus élevé au plus bas job_group.

A10 : J'inclurai deux approches pour répondre à cette question, chacune introduira de nouveaux concepts. Je vais d'abord couvrir une approche pour montrer comment utiliser FROMdirectement sur une sortie d'une requête, puis je couvrirai une approche différente pour démontrer l'application de WITHla clause.

Approche 1 :

SELECT 
job_group, 
AVG(salary) AS average_salary 
FROM 
( 
  SELECT 
   person_id, 
   CASE 
    WHEN job_title IN ('software_developer', 'data_scientist') THEN 'tech' 
    WHEN job_title IN ('financial_analyst', 'physician') THEN 'non-tech' 
    ELSE titre_emploi 
   END AS groupe_emploi, 
   salaire 
  FROM 
   personnes 
) 
GROUP BY 
groupe_emploi 
ORDER BY 
salaire_moyen DESC ;

Résultats:

Alors que s'est-il passé exactement ? Dans le passé, nous incluions toujours le nom d'une table après FROM, mais le nouveau concept ici est que nous pouvons également inclure une requête interne au lieu du nom de la table. Ce qui se passe, c'est que la requête interne après FROMest d'abord exécutée, puis les résultats sont utilisés comme table temporaire pour sélectionner des données dans la requête externe.

Approche 2 :

Je commencerai par inclure la requête utilisée dans cette approche, puis j'expliquerai chacune des étapes de la requête. Commençons d'abord par la requête :

WITH count_table AS( 
SELECT 
  CASE 
   WHEN job_title IN ('software_developer', 'data_scientist') THEN 'tech' 
   WHEN job_title IN ('financial_analyst', 'physician') THEN 'non-tech' 
   ELSE job_title 
  END AS job_group, 
  count(person_id) as total_count 
FROM 
  people 
GROUP BY 
  1 
), 
total_salary_table AS( 
SELECT 
  CASE 
   WHEN job_title IN ('software_developer', 'data_scientist') THEN 'tech' 
   WHEN job_title IN ('financial_analyst', 'physician') THEN 'non-tech' 
   ELSE job_title 
  END AS job_group,
  SUM(salaire) as total_salary 
FROM 
  people 
GROUP BY
  1 
) 
SELECT 
ct.job_group, 
tst.total_salary / ct.total_count as average_salary 
FROM 
count_table ct 
INNER JOIN total_salary_table tst ON ct.job_group = tst.job_group 
ORDER BY 
average_salary DESC ;

Regardons maintenant de plus près ce qui s'est passé.

Nous allons d'abord créer une table temporaire à l'aide de la WITHclause, nommée count_table. Ce tableau temporaire indique le nombre d'individus dans chaque job_group, comme indiqué ci-dessous :

Ensuite, nous allons créer une deuxième table temporaire nommée total_salary_tablequi affiche le salaire total de chaque job_group, comme indiqué ci-dessous :

Maintenant que nous avons ces deux tableaux, nous pouvons les joindre pour trouver le salaire moyen de chacun job_group, comme suit :

Les résultats ici correspondent aux résultats de l'approche 1 comme prévu.

6. Fonctions de la fenêtre

Ces fonctions sont plus difficiles à visualiser au départ, alors ne soyez pas déçu si vous ne l'obtenez pas tout de suite. Passez en revue quelques exemples et vous commencerez à mieux les comprendre. Nous les incluons généralement dans nos entretiens, je vous recommande donc de les comprendre et de les mettre en pratique.

Les fonctions de fenêtre effectuent généralement un calcul sur un ensemble de lignes de table qui sont liées d'une manière ou d'une autre. En d'autres termes, ils ressemblent en quelque sorte à des agrégations avec quelques mises en garde. Contrairement aux agrégations, les fonctions de fenêtre n'entraînent pas le regroupement des lignes. Cela deviendra plus clair avec des exemples.

Voici quelques exemples de fonctions de fenêtre : COUNT(), AVG(), SUM(), ROW_NUMBER(), RANK(), DENSE_RANK(), LAG, et LEADet elles sont structurées comme suit :

fonction_fenêtre(nom_colonne) OVER ( 
PARTITION BY 
  nom_colonne 
ORDER BY 
  nom_colonne ROWS BETWEEN UNBOUNDED PRECEDING 
  AND UNBOUNDED FOLLOWING 
) AS output_alias;

L'exemple ci-dessus montre les éléments les plus courants de la structure des fonctions de fenêtre, mais ils ne sont pas tous nécessaires. Je sais que jusqu'à présent, cela n'a pas de sens, alors examinons certains d'entre eux dans la pratique pour mieux les comprendre.

Q11 : Créez un classement des salaires dans l'ensemble et également par sexe, du salaire le plus élevé au salaire le plus bas.

A11 :

SELECT 
nom, 
sexe, 
salaire, 
RANK() OVER( 
  ORDER BY 
   salaire DESC 
) AS salaire_rang_overall, 
RANK() OVER( 
  PARTITION BY sexe 
  ORDER BY 
   salaire DESC 
) AS salaire_rang_by_sexe 
FROM 
personnes 
ORDER BY 
salaire_rang_overall, 
salaire_rang_by_sexe ;

Résultats:

Comme vous pouvez le voir dans les résultats, en utilisant RANK()la fonction de fenêtre, nous avons pu classer les salaires à la fois dans une perspective globale et également dans chacune des catégories masculines ou féminines. À l'intérieur du OVER(), nous avons indiqué que nous voulions que le classement soit décomposé (ou «partitionné») par genderet également trié par salaire du plus élevé au plus bas en ajoutant ORDER BY salary DESC.

Q12 : Créer une somme cumulée de chaque salaire à la fois globalement (c'est-à-dire que la somme à la ligne 2 doit être la ligne 1 + la ligne 2, la somme à la ligne 3 doit être la somme de la ligne 1 + la ligne 2 + la ligne 3, et ainsi de suite) et aussi par sexe , classés par âge (du plus vieux au plus jeune). Incluez également une colonne indiquant le salaire total à chaque ligne.

A12 :

SELECT 
nom, 
sexe, 
année_de_naissance, 
salaire, 
SUM(salaire) OVER( 
  ORDER BY 
   rows_année_de_naissance entre UNBOUNDED PRECEDING 
   And CURRENT ROW 
) AS running_total_salary_overall, 
SUM(salary) OVER( 
  PARTITION BY gender 
  ORDER BY 
   birth_year ROWS between UNBOUNDED PRECEDING And CURRENT 
   ROW 
) AS running_total_salary_by_gender, 
SUM(salary) OVER( 
  ORDER BY 
   birth_year ROWS between UNBOUNDED PRECEDING 
   And UNBOUNDED FOLLOWING 
) AS total_salary 
FROM 
people 
ORDER BY 
running_total_salary_overall, 
running_total_salary_by_gender ;

Résultats:

Une fois que vous aurez parcouru les explications ci-dessous et que vous les aurez comparées aux résultats, je suis sûr que vous apprécierez également la puissance des fonctions de fenêtre.

Celui-ci a utilisé de nouvelles déclarations, alors regardons-les de plus près. Regardons d'abord ce qui suit :

SUM(salary) OVER( 
  ORDER BY 
   birth_year ROWS between UNBOUNDED PRECEDING 
   And CURRENT ROW 
) AS running_total_salary_overall,

SUM(salary)additionne simplement les salaires, mais la façon dont les salaires doivent être additionnés est la partie la plus intéressante et est indiquée dans la OVER()déclaration. Tout d'abord, les salaires sont triés par année de naissance des individus du plus bas au plus élevé, comme indiqué dans ORDER BY birth_year, (l'absence de DESCnous indique que c'est dans un ordre croissant). Indique ensuite ROWScomment le SUM()doit être appliqué à ces lignes de données. Dans ce cas, la sommation est appliquée à toutes les lignes avant chaque ligne ( UNBOUNDED PRECEDING) jusqu'à et y compris la ligne elle-même ( CURRENT ROW).

Et si nous voulions que la somme de chaque ligne inclue tout ce qui précède et aussi tout ce qui suit cette ligne spécifique ? Cela peut être accompli par ce qui suit :

LIGNES entre UNBOUNDED PRECEDING 
   et UNBOUNDED FOLLOWING

Cette structure a été utilisée pour calculer le total_salarydans la partie suivante de la requête et vous pouvez voir dans la section des résultats :

SUM(salaire) OVER( 
  ORDER BY 
   birth_year ROWS between UNBOUNDED PRECEDING 
   And UNBOUNDED FOLLOWING 
) AS total_salary

Étant donné que pour chaque ligne, nous voulions avoir la somme de toutes les lignes avant et après une ligne donnée, nous avons essentiellement demandé à voir la somme totale de cette colonne affichée à chaque ligne sous total_salary, qui était la même valeur de 868,000pour chaque ligne.

8. Divers - Opérateur UNION, gestion des valeurs nulles et gestion des dates

Jusqu'à présent, nous avons couvert certains des concepts les plus courants qui peuvent vous aider à rédiger vos propres requêtes. Dans cette partie, je vais couvrir quelques sujets supplémentaires qui peuvent également vous aider dans les entretiens.

Comme la dernière fois, créons d'abord deux nouvelles tables nommées misc_part1, misc_part2puis passons en revue les concepts. Pour l'instant, copiez, collez et exécutez le script ci-dessous pour créer les tables temporaires pour cette partie de l'exercice.

Point bonus (facultatif) : Maintenant que vous êtes plus familiarisé avec les différents concepts SQL, jetez un œil à ce que vous copiez et collez et voyez si vous pouvez suivre la logique. Vous pouvez voir que nous définissons d'abord une table, puis spécifions les colonnes dans la table et le type de données associé à chaque colonne, puis ajoutons des valeurs pour chaque colonne (sous le format d'une ligne de valeurs). C'est ça! Vous pouvez maintenant créer vos propres tables temporaires et commencer à en récupérer les données !

DROP TABLE IF EXISTS misc_part1;

CREATE TEMPORARY TABLE misc_part1 (
name VARCHAR(30),
last_contacted DATE,
contact_type VARCHAR(30)
);

INSERT INTO
misc_part1
VALUES
('michael', '2022-09-09', 'email'),
('elizabeth', '2022-01-23', 'sms'),
('david', NULL, 'sms'),
('linda', NULL, 'phone_call'),
('william', '2002-01-28', 'postal_mail'),
('barbara', '2019-12-01', 'email'),
('richard', '2022-08-29', NULL),
('susan', '2020-09-20', NULL);

DROP TABLE IF EXISTS misc_part2;

CREATE TEMPORARY TABLE misc_part2 (
name VARCHAR(30),
last_contacted DATE,
contact_type VARCHAR(30)
);

INSERT INTO
misc_part2
VALUES
('joseph', '2016-08-01', 'phone_call'),
('elizabeth', '2022-01-23', 'sms'),
('david', NULL, 'sms'),
('sarah', '2021-09-13', NULL),
('thomas', '2008-01-18', 'sms'),
('jessica', '2013-04-17', 'email'),
('charles', '2022-08-24', NULL),
('lisa', NULL, 'postal_mail');

Voyons maintenant à quoi ressemblent les tables. Je vais utiliser ce qui suit pour regarder mist_part1:

SELECT 
* 
FROM 
misc_part1 :

Résultats:

puis utilisera ce qui suit pour voir misc_part2:

SELECT 
* 
FROM 
misc_part2 ;

Résultats:

Nous pouvons considérer ces deux tableaux comme des données marketing sur la dernière fois que les clients ont été contactés et également sur la manière dont ils ont été contactés, par exemple par e-mail, appel téléphonique, etc. Il y a trois observations rien qu'en regardant les données :

1. Les deux tables ont les mêmes noms de colonne, il est donc peut-être possible de les combiner dans le cadre de notre exercice, que nous explorerons plus en détail sous UNION.
2. Certaines valeurs sont manquantes, ce qui n'était pas le cas dans les tables peopleet . salaryPar exemple, en regardant misc_part2, les cellules B5et B6sont vides. Il y a aussi des valeurs manquantes dans misc_part1. Nous discuterons de la gestion des nulls.
3. Les deux tables incluent une colonne de format de date, que nous n'avions pas dans les tables peopleet . salaryNous utiliserons ces valeurs pour certaines manipulations de date.

Comme la dernière fois, n'hésitez pas à prendre un instantané de ces deux tableaux pour votre référence, puis continuons avec le même format de questions et réponses pour couvrir de nouveaux concepts.

8.1. Opérateur UNION

Q13 : Je vois que les deux tables incluent les mêmes colonnes. Pouvez-vous les combiner en un seul tableau ? Triez les résultats par nom et identifiez également quelle ligne appartient à quelle table.

R13 : N'oubliez pas que nous avons commencé avec deux tableaux, chacun d'eux comprenant 8 lignes de données (à l'exclusion des en-têtes). Nous nous attendons donc à ce que le tableau combiné inclue 16 lignes de données (à l'exclusion des en-têtes).

Cela peut être fait UNION ALLen utilisant le format suivant :

SELECT 
*, 
1 AS misc_table_number 
FROM 
misc_part1 
UNION ALL 
SELECT 
*, 
2 AS misc_table_number 
FROM 
misc_part2 
ORDER BY 
nom ;

Résultats:

Les résultats incluent 16 lignes de données (à l'exclusion des en-têtes) comme prévu. Parlons de ce qui s'est passé.

UNION ALLL'opérateur place les données de chacune des requêtes et les empile les unes sur les autres. Quelques conditions doivent être remplies pour que cela fonctionne correctement :

1. Chaque SELECTélément de l' UNION ALLopérateur doit avoir le même nombre de colonnes. Par exemple, dans notre exercice chaque tableau comprend 4 colonnes.
2. Les colonnes correspondantes de chaque SELECTinstruction doivent avoir le même type de données. Par exemple, namedans les deux tables sont dans VARCHAR(30)le type de données, ou last_contacteddans les deux tables sont dans DATEle type de données.
3. Les colonnes de chacune des SELECTdéclarations doivent être dans le même ordre. En d'autres termes, l'ordre dans les deux tables de nos exemples de tables doit être name, last_contacted, contact_type, misc_table_number. Cette condition était également remplie dans notre exemple et c'est pourquoi nous avons pu utiliser UNION ALL.

Q14 : En examinant les résultats de la Q13, il semble que les deux tableaux contiennent les mêmes données pour davidet elizabeth. Pouvez-vous créer le même tableau mais n'inclure que des lignes uniques (c'est-à-dire dédoubler les résultats) ? Il n'est pas nécessaire d'indiquer à quelles lignes du tableau appartiennent.

A14 : Cela peut facilement être fait en utilisant UNION, au lieu de UNION ALL. En d'autres termes, UNIONne sélectionne que des valeurs distinctes, tandis que UNION ALLsélectionne toutes les valeurs. La requête est la suivante :

SELECT 
* 
FROM 
misc_part1 
UNION 
SELECT 
* 
FROM 
misc_part2 
ORDER BY 
nom ;

Résultats:

Comme prévu, les résultats n'incluent désormais que des lignes distinctes et le nombre total de lignes est désormais de 14 (hors en-têtes), au lieu de 16 au Q13.

8.2. Traitement nul

Q15 : Créez une table temporaire nommée combined_tablequi inclut des lignes distinctes des tables combinées (similaire à Q14). Lorsque last_contactedla valeur est manquante, entrez la valeur sous la forme 1901-01-01. Nous savons aussi que ce contact_typequi manque est phone_calldonc remplissez-les également.

A15 :

WITH combination_table as ( 
SELECT 
  * 
FROM 
  misc_part1 
UNION 
SELECT 
  * 
FROM 
  misc_part2 
ORDER BY 
  name 
) 
SELECT 
name, 
NVL(last_contacted, '1901-01-01') as last_contacted, 
COALESCE(contact_type, 'phone_call') AS contact_type 
FROM combination_table 
;

Résultats:

Les résultats sont conformes à nos attentes, alors parlons de ce qui s'est passé.

Il y a deux fonctions nulles que nous avons utilisées dans cet exercice et pour nos besoins, elles sont toutes les deux similaires. Ils renvoient tous les deux une valeur alternative lorsqu'une expression est NULL. Je voulais utiliser les deux pour les présenter tous les deux, mais vous pouvez choisir d'utiliser l'un ou l'autre. Regardons-les de plus près :

NVL(last_contacted, '1901-01-01') as last_contacted, 
COALESCE(contact_type, 'phone_call') AS contact_type

Le premier dit que lorsque vous rencontrez NULL dans la colonne last_contacted, remplacez-le par 1901-01-01. De même, le second dit que lorsque vous rencontrez NULL dans la colonne contact_type, remplacez-le par phone_call, comme indiqué dans la question.

Conseil de pro : selon l'environnement SQL, les instructions peuvent légèrement varier, mais les concepts restent les mêmes. Par exemple,COALESCE()est utilisé sur MySQL, SQL Server, Oracle et MS Access, alors qu'ilNVL()fonctionne principalement avec Oracle.

8.3. Gestion des dates

Q16 : En commençant par combined_table, créez des colonnes séparées pour l'année, le trimestre, le mois et la date de la dernière fois que les personnes ont été contactées, lorsqu'une telle date est disponible.

R16 : Examinons d'abord la structure et les résultats, puis discutons-en.

WITH combination_table as ( 
SELECT 
  * 
FROM 
  misc_part1 
UNION 
SELECT 
  * 
FROM 
  misc_part2 
ORDER BY 
  name 
) 
SELECT 
name, 
last_contacted, 
DATE_PART(year, last_contacted) AS year_contacted, 
DATE_PART(trimestre, last_contacted) AS quarter_contacted, 
DATE_PART(month, last_contacted) AS month_contacted, 
DATE_PART (jour, dernier_contacté) AS jour_contacté, type_contact 
FROM 
tableau_combiné 
WHERE 
dernier_contacté 
IS NOT NULL ;

Résultats:

Il y a deux nouveaux concepts à couvrir dans cet exercice. La première consiste à extraire une partie spécifique (par exemple, année, trimestre, mois ou jour) d'une date, qui peut être implémentée comme suit :

DATE_PART(year, last_contacted) AS year_contacted,

L'idée est simple. yearidentifie quelle partie de la date doit être extraite, puis elle est suivie du nom de la colonne où se trouve la date d'origine, qui est last_contacteddans cet exemple.

Le deuxième concept consiste à filtrer les lignes avec des valeurs NULL, ce qui a été accompli en utilisant ce qui suit :

WHERE 
last_contacted IS NOT NULL

Conseil de pro : dans SQL Server, au lieu deDATE_PART(), on utiliseDATEPART().

Section 3 — Aide-mémoire

J'ai inclus la feuille de triche que j'avais développée pour moi-même ici. Avant de commencer à l'utiliser, j'ai deux recommandations à ce sujet et sur d'autres aide-mémoire :

1. Dans la mesure du possible, créez votre propre feuille de triche au fil du temps, au lieu de vous fier à une feuille de triche préparée. Vous en apprendrez beaucoup plus lorsque vous le créerez et le mettrez à jour par vous-même, par rapport à celui qui est partagé avec vous.
2. Si vous n'avez pas le temps de créer votre propre feuille de triche, prenez une feuille de triche existante et personnalisez-la. Ce que je veux dire, c'est que commencez par la feuille de triche existante, mais ajoutez-y, révisez-la, modifiez-la et à un moment donné, elle deviendra "votre" feuille de triche et vous continuerez à apprendre en cours de route.

-- Introduction
 
SELECT column_1, column_2… [Returns entries per specified columns]
SELECT * [Returns all entries]
SELECT DISTINCT column_1, column_2… [Returns unique entries per specified columns]
SELECT DISTINCT * [Returns all unique entries]
 
FROM schema_name.table_name [Specifies table to return entries from]
 
WHERE column_1 = ‘value’ [Specifies condition per column]
May use =, >, <, >=, <=, <>
 
WHERE column_1 = ‘value’ AND column_2 = ‘value’ [Specifies both conditions must be met]
 
WHERE column_1 = ‘value’ OR column_2 = ‘value’ [Specifies one condition must be met]
 
WHERE column_1 = ‘value’ AND (column_2 = ‘value’ OR column_3 = ‘value’)
 
-- Aggregations

SUM(column_1) [Returns summation of column]
 
AVG(column_1) [Returns average of column]
 
MIN(column_1) [Returns minimum of column]
 
MAX(column_1) [Returns maximum of column]
 
COUNT(column_1) [Returns count of entries of column]
 
COUNT(DISTINCT column_1) [Returns unique count of entries of column]
 
Aggregations require a GROUP BY clause, i.e. GROUP BY 1, 2 or GROUP BY column_1, column_2
All non-aggregated columns are required to be in the GROUP BY clause
 
-- Null Handling

IS NULL [Evaluates if an entry is null]
 
IS NOT NULL [Evaluates if an entry is not null]
 
NVL(column_1, ‘alternative value’) [Replaces entry with an alternative value if found to be null]
 
NULLIF(column_1,’value’) [Replaces entry with null if entry meets value]
 
-- Aliases
 
column_1 AS ‘value’ [Renames column]
 
schema_name.table_name AS ‘value’ [Renames table]
 
-- Joins (added in From and then join on primary keys)
 
INNER JOIN schema_name.table_name ON table1.value1 = table2.value1 [Merges tables on matching values, contains only records with matching values found in both table 1 and table 2] 
 
LEFT JOIN schema_name.table_name ON table1.value1 = table2.value1 [Merges tables on matching values, contains all records from table 1] 
 
RIGHT JOIN schema_name.table_name ON table1.value1 = table2.value1 [Merges tables on matching values, contains all records from table 2] 
 
FULL OUTER JOIN schema_name.table_name ON table1.value1 = table2.value1 [Merges tables on matching values, contains all records from both table 1 and table 2] 
 
-- UNIONs

UNION [Merges two tables whose structures are the same, removes duplicative records] 
UNION ALL [Merges two tables whose structures are the same]
 
-- Miscellaneous
 
LIMIT x [Limits returned records to a specified number]
 
ORDER BY 1, 2 or ORDER BY column_1, column_2 [Orders returned records by specified columns]
 
LIKE ‘%value%’ [Used to find near matches when evaluating conditions]
 
IN (‘value 1’,’value 2’) [Used to find matches within a list of values when evaluating conditions]
 
BETWEEN ‘value 1’ AND ‘value 2’ [Used to find matches where an entry falls between two values when evaluating conditions] (inclusive)
 
HAVING [Used in place of WHERE for aggregate clauses]
(WHERE filters before data is grouped. HAVING filters after data is grouped.) 
 
CASE WHEN column1 = ‘value’ THEN ‘value’ ELSE ‘value’ END AS ‘value’ [Returns entries by evaluating when/then statements]
CASE WHEN A THEN X
WHEN B THEN Y
WHEN C THEN Z
ELSE W
END
 

NOT [Used to limit to where a condition is not met]

-- Date Management
 
CURRENT_DATE [Returns current date]
 
TO_CHAR (column name, 'date format') [converts the date to a character string in a specified format]
Date formats:
'YYYY/MM/DD'
'YYYY:MM:DD'
'Q' = Quarter number
'MM' = Month number
'Month' = Month name
'W' = Week of the month (1-5; first week starts on the first day of the month)
'WW' = Week number of year (1-53; first week starts on the first day of the year)
'Day' = Day name
'DDD' = Day of year (1-366)
'DD' = Day of month (1-31)
'D' = Day of week (1-7; Sunday is 1)
'YYYY/MM/DD HH24:MI:SS' = Year, month, day, hour, minutes and seconds
 
TO_DATE ('string', 'date format') [opposite of TO_CHAR]
Wildcard: TO_DATE ('{RUN_DATE_YYYY-MM-DD}', 'YYYY-MM-DD')
 
DATE_TRUNC ('datepart', column) [truncates a time stamps expression to a specified date part, such as hour, week or month]
Datepart formats:
'year' = truncates to the first second of the first hour of the first day of the year
'quarter' = truncates to the first second/hour/day/quarter
'month' = truncates to the first second/hour/day/month
'week' = truncates to the first second/hour/day/week (Monday)
'day' = truncates to the first second/hour/day

Source :