Kata SQL (aka Dojo Marketing)

Ceci est un exercice permettant de pratiquer des requêtes SQL sur des tables pré-définies.

À propos

ⓘ Ceci est la donnée d'un kata, un exercice de programmation qui se déroule généralement dans le cadre d'un coding dojo. Il est proposé aux membres du dojo de l'EPFL et fait partie d'une collection de différents katas identifiés par le tag epfl-dojo-kata sur GitHub.
Vous êtes plus que bienvenu·e d'essayer de le réaliser dans le langage de programmation de votre choix. Lorsque c'est terminé, ajoutez-vous à la liste de ceux qui l'ont fait dans ce document en proposant une Pull Request. Vous pouvez également partager votre intérêt pour ce dépôt en le «stargazant», c'est à dire en lui ajoutant une ⭐.
Bonne lecture et bon code !

Concept

Vous êtes responsable de la communication de la discothèque "HelloDojo" qui ouvrira prochainement ses portes. Vous avez récupéré des listes de personnes au format SQL et dans le but d'exercer vos talents de marketing, vous avez besoin d'en extraire des informations pertinentes.

Afin que d'autres personnes puissent faire de même, vous devez consigner les étapes pour reproduire votre travail le fichier HowTo.md.

Commencez par cloner le dépôt pour pouvoir utiliser Git et commiter vos avancements. Cela permet aussi de montrer que vous avez fait l'exercice. Vous pouvez également voir comment les autres ont procédé, et inversement !

Si vous êtes arrivé au bout de l'exercice, vous pouvez derechef vous ajouter au fichier IVEMADEIT.md en l'éditant en ligne.

La documentation officielle de MySQL se trouve ici, celle de MariaDB là et la page de StackOverflow peut également servir.

Étapes

Mise en place

Tout d'abord, vous devez vous débrouiller pour importer les données dans un système de gestion de base de données (SGBD) tel que MySQL. Toutes les options vous sont ouvertes. Vous fournissez une explication sur ce choix dans le fichier HowTo.md, et vous créez également un schema.jpg permettant de visualiser les différentes tables de la base de données.

Informations à récolter

Générales

Pour commencer, vous désirez connaître le nombre de personnes que vous avez dans votre base de données (people). ⓘ
Comment trouver l'email de la personne dont le nom de famille est "Warren" ? ⓘ
Comment trier les donnée de la table people par ordre alphabétique croissant sur le nom de famille ? ⓘ
Il y a-t-il un moyen de limiter le nombre de résultat, par exemple en affichant uniquement les 5 premiers, toujours triés par nom de famille ? ⓘ
Comment trouver les personnes qui ont un prénom ou un nom qui contient ojo ? ⓘ
Quelles sont les 5 personnes les plus jeunes ? Et les plus âgées ?
Comment trouver l'âge, en années, des personnes ?
Comment peut-on trouver la moyenne d'âge des personnes présentes dans la table ?
Votre designer travaille sur les cartes de membre et il a besoin de savoir quelle est la personne avec le plus long prénom et le plus long nom.
Ne sachant encore pas exactement la manière dont le layout des cartes de membres sera organisé, il aimerait également savoir qui sont les 3 personnes qui ont, mis ensemble, la paire nom + prénom la plus longue.
Il y a-t-il des doublons dans la table people ? ⓘ

Invitations

Pour l'ouverture, vous désirez lister tous les membres de plus de 18 ans,
- et de moins de 60 ans,
- qui ont une addresse email valide.
Pour faciliter la lecture vous ajoutez une colonne age dans le résultat de votre requête.
Avec ces membres, vous désirez faire une liste sous le format suivant Prénom Nom <email@provider.com>; afin de pouvoir la copier/coller dans votre client email.
Avec les informations contenues dans la table people (sans jointures), pourrait-on approximer le nombre de personnes habitant en Suisse ?

Countries

Pour un futur formulaire d'inscription sur un site Internet, vous voulez pré-macher votre travail en préparant les données des pays pour les options d'un <select>. Préparer la requête qui permet d'obtenir la liste d'options sous la forme : <option value="XXX">XXX</option>.
Quelle serait une solution pour avoir cette liste disponible en français et en anglais lorsque le site sera traduit ?

Jointure

En utilisant la table de jointure countries_people.sql, lister les personnes habitant en Suisse.
De la même manière, lister les personnes qui n'habitent pas en Suisse.
Comment lister les personnes (nom et prénom) qui habitent dans les pays limitrophe de la Suisse ? (i.e France, Allemagne, Italie, Autriche, Liechtenstein)
Vous souhaitez savoir combien il y a de personnes par pays, afin de savoir si votre table people a suffisament de personnes en suisse et combien de personnes sont étrangères.
Quels sont les pays qui ne possèdent pas de personnes ?
Il y a-t-il des personnes qui sont liées à plusieurs pays ?
Il y a-t-il des personnes liées à aucun pays ?
Comment pourrait-on afficher le pourcentage de personnes par pays ?

Procédures

Vous avez remarqué que la table countries.sql contient une colonne tld. Trouvez un moyen d'afficher le nom du pays en anglais en fonction du tld de l'adresse email de la personne. ⓘ
Pourrait-on afficher "Country Unkown" si l'email est vide ou que le tld ne match aucun pays ? ⓘ
Comment pourrait-on avoir accès à un méchanisme qui trouve automatiquement le tld des addresses emails ? ⓘ

Vue SQL

Pour faciliter vos futurs requêtes, vous créer une vue SQL HelloDojoⓘ qui contient les colonnes suivantes :
- Toutes les informations de la table people ;
- Une colonne age ;
- Une colonne formatée avec Prénom Nom (i.c. majuscules) ;
- Une colonne avec le nom du pays en Français.
Afin de partager les informations présentes dans cette vue, vous l'exporter au format CSV afin que vos collègues puissent la visualiser dans un tableur. ⓘ

Finances

En vue de l'ouverture, le directeur a déjà acheté des caisses enregistreuses. Vous savez également que des bons et les cartes de membres pourront-être achetés sur le site Internet. Modifiez la base de donnée existante en ajoutant une table expenses qui permettra d'ajouter les dépenses de chaque membre.
Modifier la vue HelloDojo pour ajouter le total des dépenses par membre.

Intégrité

(WIP) Les contraintes sont maintenant dans le SQL de base, cette question n'a plus de sense.

Comment devez-vous procéder pour ajouter des contraintes ⓘ dans votre schéma, en tant que clés étrangères de la table counties_people vers les tables countries et people ?
- CONSTRAINT countries_people_ibfk_1 FOREIGN KEY (idcountry) REFERENCES countries (id)
- CONSTRAINT countries_people_ibfk_2 FOREIGN KEY (idperson) REFERENCES people (id)
Pourquoi est-il nécessaire d'altérer les données ?

ponsfrilus/kata-sql