#!/usr/bin/env python
# coding: utf-8

# # SQL 3/3
# > Cours NSI Terminale - Thème 2.
# - toc: true 
# - badges: true
# - comments: false
# - categories: [NSI, Terminale, Bases de données, SQL, TP]
# - image: /images/nsi2.png

# # Manipuler les données avec SQL
# 
# Dans ce dernier TP, nous allons voir comment insérer, mettre à jour ou supprimer des enregistrements dans des tables.
# 
# Commencez par valider les cellules suivantes afin d'activer la fonctionnalité SQL de Jupyter et de charger la base du TP précédent.

# In[ ]:


get_ipython().run_line_magic('load_ext', 'sql')

get_ipython().run_line_magic('sql', 'sqlite:///livres_db')


# ## Insérer un enregistrement dans une table
# 
# Nous avons déjà rencontré cerre requète **INSERT**. Elle s'applique que la table soit vide ou nom. On peut très bien ajouter par exemple une troisième langue dans notre table **Langues**

# In[ ]:


get_ipython().run_line_magic('sql', "INSERT INTO Langues (Langue) VALUES('Klingon');")


# In[ ]:


# Vérifions :
get_ipython().run_line_magic('sql', 'SELECT * FROM Langues;')


# ## Mise a jour, effacement : UPDATE et DELETE
# 
# Les requêtes **UPDATE** et **DELETE** fonctionnent sur le même modèle que les requêtes **SELECT**.  Attention, on a vite fait d'effacer toutes ses données si on ne configure pas bien sa requête. Une bonne habitude à prendre est de tester d'abord ses critères à l'aide d'un **SELECT**.
# 
# Observez les exemples ci-dessous :

# In[ ]:


get_ipython().run_line_magic('sql', 'SELECT NomAuteur FROM Auteurs WHERE IdAuteur = 10;')


# C'est bien lui !!
# 
# Modifions le nom de l'auteur grâce à une requête de mise à jour : 
# 
# **UPDATE** *table* <br>
# **SET**  *attribut1* = *valeur1*, *attribut2* = *valeur2*, ...<br>
# **WHERE** *critère*;

# In[ ]:


get_ipython().run_cell_magic('sql', '', '\nUPDATE Auteurs \nSET NomAuteur = "Ze Djloule", PrenomAuteur = "Juju"\nWHERE IdAuteur=10;\n\nSELECT * FROM Auteurs ;\n')


# supprimons vite cette entrée ! Nous utiliseerons une requête
# **DELETE FROM** *table* **WHERE** *critere*
# 
# **Attention** : soyez bien sûr de votre critère sous peine de perdre des données importantes !

# In[ ]:


get_ipython().run_cell_magic('sql', '', '\nDELETE  FROM Auteurs  WHERE IdAuteur=10;\n\nSELECT * FROM Auteurs;\n')


# ### Attention à la cohérence des données
# 
# Dans une base de données relationnelle il faut être vigilant lors de la suppression d'enregistrements : en effet la suppression d'un enregistrement entraîne la suppression de sa clé primaire qui peut être utilisée en tant que clé externe dans une autre table. Cela entraîne la corruption des données. Observez l'exemple ci-dessous dans lequel je me suis attribué un livre célèbre sans toucher à la table **Livres**
# 
# Il existe en SQL des moyens pour se prémunir de ce type de problèmes mais cela dépasse le cadre de ce cours.

# In[ ]:


get_ipython().run_cell_magic('sql', '', 'INSERT INTO Auteurs \n    (NomAuteur, PrenomAuteur, IdLangue, AnneeNaissance) \nVALUES\n    ("Lecluse", "Olivier", 2, 1850);\n    \nSELECT Titre, NomAuteur from Livres JOIN Auteurs USING (IdAuteur);\n')


# ### A vous de jouer
# 
# 1. Réinsérez l'auteur Jules Verne à sa place !
# 1. Supprimez tous les livres écrits au 19e sciecle

# In[ ]:


get_ipython().run_cell_magic('sql', '', '\n/* \n# YOUR CODE HERE\nraise NotImplementedError()\n*/\n')


# In[ ]:


result = get_ipython().run_line_magic('sql', 'SELECT Titre, NomAuteur AnneePubli from Livres JOIN Auteurs USING (IdAuteur);')


# In[ ]:


assert len(result) == 15


# In[ ]: