====== TP introduction SQL ======

Vous pouvez utiliser le serveur [[http://bdd.imst.os.univ-lyon1.fr]] avec les login et mot de passe fourni par votre enseignant.


===== Requêtes simples =====

Faire des requêtes sur la table des catégories

  * Listez l'ensemble de la table
  * Listez uniquement le titre de toutes les catégories
  * Listez les catégories qui sont des sujets ? Combien y en a-t-il ?
  * Listez les sujets qui contiennent le mot science.


<hidden>
<code>
# Listez le titre de toutes les catégories
SELECT categories.title FROM categories WHERE 1;
# Listez les catégories qui sont des sujets ? Combien y en a-t-il ?
SELECT categories.title AS titre FROM categories WHERE genre='subjects'; 
# Listez les sujets qui contiennent le mot science (On ne tiens plus compte de la casse).
SELECT categories.title AS titre FROM categories WHERE genre='subjects' AND title LIKE '%science%'; 
</code>
</hidden>
===== Requêtes avec jointure =====

vous devez faire une jointure entre les tables ''courses'' et ''courses_runs''

  * Trouvez la date de début de tous les cours.
  * Trouvez les cours qui ont lieu après octobre 2022
  * Trouvez les cours qui ont lieu après octobre 2022 et parlent de données ou de mathématiques

<hidden>
<code>
###############################################
# Trouvez la date de début de tous les cours.
###############################################
SELECT courses.title, courses_runs.start
  FROM courses, courses_runs
 WHERE courses.id=courses_runs.id_course;
# meme chose avec un join
SELECT courses.title, courses_runs.start
  FROM courses JOIN courses_runs ON courses.id=courses_runs.id_course
 WHERE 1;

########################################################
# Trouvez les cours qui ont lieu après octobre 2022
########################################################

########################################################
# Trouvez les cours qui ont lieu après octobre 2022 et parlent de données 
########################################################
</code>
</hidden>

===== Requête plus complexes =====

  * Trouvez tous les mots clefs utilisés par chacun des cours de science en n'affichant que le mot clef et le titre du cours
  * On cherche à obtenir une liste avec chaque mots clef utilisé par toutes les organisations avec l'affichage uniquement du nom de l'organisation et de la valeur du mot clef. Faites la requête 

Pour vous aider:
  - Un mot clef est une catégorie de genre ''subject''.
  - Les catégories sont liées aux cours qui sont liées au organisations.
  - On peut considérer qu'un cours est un cours de science si son titre ou son introduction contiennent le mot ''science''.

<hidden>
<code>
##################################################
#   * Trouvez tous les mots clefs utilisés par chacun des cours de science en #   n'affichant que le mot clef et le titre du cours
##################################################
SELECT categories.title AS motClef, courses.title AS titrecours 
  FROM courses JOIN courses_categories
                    ON courses_categories.id_course=courses.id
               JOIN categories
                    ON courses_categories.id_categorie=categories.id
WHERE courses.title LIKE '%science%' 
   OR courses.introduction LIKE '%science%';
   
</code>
</hidden>

  * Faites une requète pour voirs les cours d'une école des mines (école avec mines dans le nom)

<hidden>
<code>
SELECT courses.title AS NomCours, organizations.title AS NomOrga FROM courses
	JOIN courses_organizations ON courses.id=courses_organizations.id_course
	JOIN organizations ON courses_organizations.id_organization=organizations.id
	WHERE organizations.title REGEXP ".*mines.*";
</code>
</hidden>

===== En python =====

Pour python, vous pouvez utiliser le notebook https://jupyter.imst.os.univ-lyon1.fr

  * Faire un programme python qui affiche pour chaque mot clef le nombre de fois qu'il est utilisé dans un cours.

<hidden>
<code>
#!/usr/bin/env python
# coding: utf-8

# ## Connexion à la base et curseur
# 

# In[1]:


import mysql.connector
import json

conn = mysql.connector.connect(
    host="bdd.imst.os.univ-lyon1.fr",
    user="etudiant",
    password="sqlavance",
    database="funmooc")
cursor = conn.cursor()


# ## Éxécution de la requète

# In[2]:


cursor.execute("""
SELECT categories.title AS motClef, courses.title AS titrecours 
  FROM courses JOIN courses_categories
                    ON courses_categories.id_course=courses.id
               JOIN categories
                    ON courses_categories.id_categorie=categories.id
  WHERE categories.genre="subjects"
  ORDER BY courses.id
""")


# ## Comptage des mots clef
# 
# Pour chaque mot clef trouvé, j'ajoute 1 dans le tableau qui contient le nombre de fois qu'on l'a déjà vu.
# Si le mot n'a jamais été vu, j'utilise le fait qu'il est possible d'avoir une valeur par défaut dans un dictionnaire via `get`
# 
# Au passage j'affiche le mot et le nombre de fois qu'on l'a vu.
# 

# In[3]:


tab = {}
while True:
    res = cursor.fetchone()
    if res is None:
        break
    # Le mot clef est la première colonne demandée dans le SELECT alors il sera
    # dans la première case du résultat    
    mot = res[0]
    # On récupère le nmbre de fois où il a été vu ou 0 s'il n'a jamais été vu
    nbdeja = tab.get(mot, 0)
    # on affiche
    print(f"On trouve le mot clef '{mot}' déjà vu {nbdeja} fois")
    # on ajoute un et on change la valeur du tableau
    tab[mot] = nbdeja+1

# Affichage
print(tab)
# Affichage plus joli
print(json.dumps(tab, indent=2))


# ## on fait un nuage de mot
# 
# Ce n'est pas demander mais à partir d'un dictionnaire qui compte le nombre d'ocurence des mots on peut facilement faire un nuage de mot avec la librairie `wordcloud`

# In[5]:


# génération du nuage
import wordcloud
import matplotlib.pyplot as plt
wordcloud = wordcloud.WordCloud().generate_from_frequencies(tab)
# affichage
plt.imshow(wordcloud, interpolation='bilinear')
plt.axis("off")
plt.show()
</code>
</hidden>

  * Faire un programme python qui obtient la liste des mots clef de chaque cours de chaque organisation et affiche le tableau suivant:

^ Nom organisation ^ liste mot clef ^
| MINES ParisTech                                      | Economie et gestion, Environnement et dévelop... |
| Inria | Informatique et programmation, Numérique et technologie, Droit, Numérique et technologie, ... |
| ... | ... |