{"cells":[{"metadata":{},"cell_type":"markdown","source":"# TP 1 : Bases de données en SQL"},{"metadata":{},"cell_type":"markdown","source":"## A - Bases de données"},{"metadata":{},"cell_type":"markdown","source":"\n Définition : Base de données
\nUne base de données regroupe, au sein d’un stockage informatique un ensemble de données organisé de manière cohérente.
\nCes données sont organisées de manière à être facilement consultées, enrichies, supprimées, comparées.\n\n|idclient |nom |prenom | ville |\n|:------:|:----------:|:----------:|:----------:|\n|1|Ewing|Grant|San Gregorio nelle Alpi |\n|2|Buck|Violet|Landenne|\n|3|Hood|Isabelle|Sahiwal |\n|4|Small|Germane|Fontanafredda |\n|5|Hancock|Vincent|Potsdam |\n \n
\n\n"},{"metadata":{},"cell_type":"markdown","source":"\n Définition : SQL
\nLe SQL (Structured Query Language) est un langage informatique servant à exploiter des bases de données. C'est à dire qu'il permet notamment de rechercher, d'ajouter, de modifier, de supprimer, d'analyser des données, ou encore, de créer et de modifier l'organisation des données dans les bases de données.\n
\n\n"},{"metadata":{},"cell_type":"markdown","source":"\n Définition : SGBD
\nUn système de gestion de base de données (SGBD) est un logiciel permettant d’interagir avec une base de donnée.
\n Dans notre cas, Capytale est notre SGBD et la base de donnée que l'on va utiliser dans ce TP est un fichier .sql déjà attaché à cette activité Capytale.
\n Il est chargé automatiquement lors de l'ouverture de la page et, on peut ainsi directement effectuer des requêtes sur la base de données. \n
"},{"metadata":{},"cell_type":"markdown","source":"## B - Modèle relationnel"},{"metadata":{},"cell_type":"markdown","source":"\n
Définition : Relation (ou table) \nUne relation (ou table) est une structure de données comprenant différentes entités, caractérisées chacune par différents attributs.\n
\n - Chaque colonne d’une relation s’appelle un attribut.
\n Le nom donné à la colonne est appelé un descripteur.
\nL’ensemble des valeurs que peut prendre un même attribut s’appelle le domaine de l’attribut.
\n On va s'intéréser à deux type de données pour les attributs malgré qu'il en existe d'autres : les entiers, notés \"INT\", et les chaînes de caractères, notées \"TEXT\", .
\n - Chaque ligne de la relation correspond aux données d’une entité.
\n Pour une entité, la donnée des valeurs de chaque attribut s’appelle un enregistrement.\n - Un champ est l'information élémentaire d'une base de données. C'est l'intersection d'une ligne et d'une colonne.
\n
\n
\nExemple : En reprenant la base de donnée précédente.\n\n - Cette base de données est une table/relation.
\n - Cette table contient cinq enregistrements.
\n - Cette table possède quatre attributs/colonnes dont les descripteurs sont : idclient, nom, prenom, ville.
\n - Le domaine de l'attribut nom est l'ensemble des noms possibles et son type de données est \"TEXT. Celui de l'attribut idclient est l'ensemble des entiers et son type de données est \"INT\".
\n - Un enregistrement de la table est : $\\hspace{5mm}$2 $\\hspace{5mm}$ Buck $\\hspace{5mm}$ Violet $\\hspace{5mm}$ Landenne.
\n
"},{"metadata":{"trusted":true},"cell_type":"markdown","source":"\n Question 1 : Lister toutes les tables de la base de données chargée dans le notebook en exécutant ci-dessous la commande .tables.\n
"},{"metadata":{"trusted":false},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\nQuestion 2 : Afficher la table de votre choix en complétant les instructions ci-dessous\n
"},{"metadata":{"trusted":false},"cell_type":"code","source":"SELECT * \nFROM .................. /*nom de la table à afficher */","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"On comprend qu'on a à disposition une base de données bancaires (évidemment factice) qui recense les clients, leurs comptes et les opérations effectuées sur les comptes."},{"metadata":{},"cell_type":"markdown","source":"\n
Définition : PRIMARY KEY et FOREIGN KEY \n
\n - La clé primaire d’une relation/table est un attribut qui permet d’identifier de manière unique et sans ambiguité chaque entité de cette relation.
\n - On appelle clé étrangère d’une relation/table tout attribut qui fait référence à une clé primaire d’une autre relation/table.
\n
\nQuestion 3 : Dans la table de votre choix, Y-a-t-il des clés primaires ? Y-a-t-il des clés étrangères ?\n
"},{"metadata":{},"cell_type":"raw","source":""},{"metadata":{"trusted":false},"cell_type":"markdown","source":"\n Définition : Schéma relationnel
\nOn appelle schéma relationnel la liste des noms des relations suivis de la liste de leurs attributs, de leur domaine et des clés primaires et étrangères. \n
"},{"metadata":{"trusted":true},"cell_type":"markdown","source":"\n Question 4 : Afficher le schéma relationnel de la base de données en saisisant l'instruction .schema.\n
"},{"metadata":{"trusted":false},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\n Question 5 : Exécuter la requête ci-dessous.
\n A quoi sert elle ?\n
"},{"metadata":{"trusted":false},"cell_type":"code","source":"SELECT * \nFROM client \nWHERE idclient=42 ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"raw","source":""},{"metadata":{},"cell_type":"markdown","source":"\n Question 6 : En vous inspirant des instructions précédentes, lister tous les livrets A ouvert à la banque dont on a les données.\n
"},{"metadata":{"trusted":false},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"On traduit cette requête en français par : «sélectionner tous les attributs des lignes de la table `compte` dont le `type` est égal à Livret A»"},{"metadata":{},"cell_type":"markdown","source":"## C - Recherche de données dans une table"},{"metadata":{},"cell_type":"markdown","source":"\n Format d'une requête pour rechercher des informations dans une table
\n \n```SQL\nSELECT les attributs à afficher ou * pour tous les afficher\nFROM la relation/table considérée\nWHERE les critères de recherche, séparés par OR ou AND si nécessaire et NOT pour une négation\n```\n
"},{"metadata":{},"cell_type":"markdown","source":"\n Question 7 : A quoi sert la requête suivante ?\n \n```SQL\nSELECT nom, prenom \nFROM client \nWHERE idclient <15\n```\n
"},{"metadata":{"trusted":false},"cell_type":"raw","source":""},{"metadata":{},"cell_type":"markdown","source":"\nQuestion 8 : Ecrire ci-dessous une requête afin d'afficher les nom, prenom et ville des clients dont le prénom est \"Jordan\".\n
"},{"metadata":{"trusted":false},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\nQuestion 9 : Écrire une requête permettant d'afficher « les montant et informations des opérations réalisées sur les comptes d'idenficateur $1$ ou $13$ ». Vous devriez obtenir les résultats suivants :\n\n\n|montant |informations|\n|:------:|:----------:|\n|526.86 |Virement|\n|7.95 | Cheque|\n|131.47 |Cheque|\n|20.17 |Virement|\n|26.19 |Guichet|\n|307.93 |Cheque|\n|-360.29 |Guichet|\n
"},{"metadata":{"trusted":false},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\nQuestion 10 : Écrire une requête fournissant les identifiants de tous les comptes de type Livret A.\n
"},{"metadata":{"trusted":false},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\nQuestion 11 : Écrire une requête fournissant les identifiants d'opération et montants de toutes les opérations effectuées au guichet pour un montant compris entre $0$ et $100$.\n
"},{"metadata":{"trusted":false},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## D - Jointure de différentes tables d'une même base de données"},{"metadata":{},"cell_type":"markdown","source":"\n
Jointure de tables \nSQL permet d'afficher des tables recoupant des données contenues dans différentes tables. Pour ce faire, on fait des
jointures sur des attributs qu'elles ont en commun.
\nUne requête avec jointure de tables s'écrit sous la forme suivante :\n \n```SQL\nSELECT table_1.attribut_1, table_2.attribut_2, ...\nFROM table_1 \nINNER JOIN table_2 ON table_1.nom_attribut_commun=table_2.nom_attribut_commun\nWHERE condition s il y en a\n```\n \n
\n - la ligne
SELECT permet de choisir quels attributs afficher pour chacune des tables ; \n - la ligne
FROM ... définit la première table à laquelle on va s'intéresser ; \n - la ligne
INNER JOIN ... ON ... permet de faire la jointure entre notre première table et une seconde selon un attribut commun aux deux ; \n - la ligne
WHERE permet de limiter l'affichage de la table jointée aux enregistrements vérifiant une condition donnée. \n
\n
\nQuestion 12 : Que va afficher la requête ci-dessous ? Vérifier votre raisonnement en l'exécutant.\n \n```SQL\nSELECT client.nom,client.prenom,compte.idcompte \nFROM client \nINNER JOIN compte ON client.idclient=compte.idclient \nWHERE compte.type='Livret A' \n````\n
"},{"metadata":{},"cell_type":"raw","source":""},{"metadata":{"trusted":false},"cell_type":"code","source":" ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\nFaire des jointures peut être délicat. Pour bien les faire, on peut suivre le protocole suivant :\n
\n - Faire le bilan des attributs à afficher.
\n - Faire le bilan des tables à utiliser pour obtenir ces arguments.
\n - Faire le bilan des jointures (Quel est l'attribut qui va permettre de relier une base à une autre ?).
\n - Faire le bilan des contraintes
\n - Ecrire la requête en tenant compte des éléments précédents.
\n
\nSi nécessaire, on peut faire plusieurs jointures entre plusieurs tables les unes à la suite des autres.\n
\nQuestion 13 : Ecrire une requète permettant d'afficher le numéro client, le type de livret et le montant de toutes les opérations du client ayant l'identifiant 33.\n
"},{"metadata":{"trusted":false},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\nQuestion 14 : Compléter le code suivant pour afficher tous les montants des opérations de plus de 700 euros ainsi que les noms et prénoms des titulaires des comptes concernés par ces opérations.\n
"},{"metadata":{"trusted":false},"cell_type":"code","source":"SELECT client.nom, ...........\nFROM client \nINNER JOIN compte ON client.idclient=compte.idclient \nINNER JOIN .......... ON ..........\nWHERE .......... ","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"markdown","source":"\nQuestion 15 : Afficher la table des opérations effectuées sur chaque compte pour un montant compris entre 0 et 100 euros. On demande à avoir l'identificateur du propriétaire, l'identificateur du compte, le type de compte, le montant et les informations de l'opération.\n
"},{"metadata":{"trusted":false},"cell_type":"code","source":" ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## E - Modification d'une table"},{"metadata":{},"cell_type":"markdown","source":"\n \n
\n- Pour rajouter un enregistrement à une table, on utilise la requête :
\n \n```SQL\nINSERT INTO nom_de_la_table\nVALUES (élément 1, élément 2, ...)\n``` \n \n \n- Pour supprimer un enregistrement d'une table, on utilise la requête :
\n \n```SQL\nDELETE FROM nom_de_la_table\nWHERE condition\n``` \n \n \n- Pour mettre à jour un enregistrement dans une table, on utilise la requête :
\n \n```SQL\nUPDATE nom_de_la_table\nSET nom_colonne_1 = \"nouvelle valeur 1\", nom_colonne_2 = \"nouvelle valeur 2\", ...\nWHERE condition\n``` \n \n \n- Pour créer une table, on utilise la requête :
\n \n```SQL\nCREATE TABLE nom_de_la_table\n(\n colonne1 type_donnees,\n colonne2 type_donnees,\n colonne3 type_donnees,\n colonne4 type_donnees\n)\n``` \n \n
\n\n
\nQuestion 16 : Ecrire une requête permettant de rajouter une nouvelle opération numérotée 1001, à la table operation, représentant un dépot par virement de 150 euros sur le compte d'identifiant 269.
\n Vous pouvez vérifier votre requête en allant chercher l'opération dans la tableoperation.\n
"},{"metadata":{"trusted":false},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\nQuestion 17 : Le client Ewing Grant vient de fermer ses comptes et on veut le suprimer de la table client. Ecrire une requête permettant de faire cela.
\n Vous pouvez vérifier votre requête en allant chercher le client dans la tableclient.\n
"},{"metadata":{"trusted":false},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\nQuestion 18 : Le banquier à l'accueil de la banque a fait une erreur lors de l'ouverture du compte de la cliente dont l'indentifiant est le numéro 9. Son prénom est Jessie et non pas Jesse.
\nEcrire une requête permettant de corriger cette erreur.
\n Vous pouvez vérifier votre requête en allant chercher le client dans la tableclient.\n
"},{"metadata":{"trusted":false},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\nQuestion 19 : Créer une nouvelle table, appelé bilan, contenant les noms des clients, les prénoms des clients, les identifiants de ses comptes et pour chaque compte son type.
\n Vous pouvez vérifier votre résultat en utilisant la requète .schema.\n
"},{"metadata":{"trusted":false},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\nQuestion 20 : Insérer dans cette nouvelle table toutes les données correspondantes. On triera la table par ordre croissant de noms puis prénoms.
\n Vous pouvez vérifier en affichant la table bilan.\n
"},{"metadata":{"trusted":false},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## E - Commandes non exigibles mais intéressantes pour le traitement de données"},{"metadata":{},"cell_type":"markdown","source":"### Fonctions d'agrégation, traitement statistique"},{"metadata":{"trusted":true},"cell_type":"markdown","source":"\nSQL permet de faire un traitement statistique basique grace à des fonctions dites d'agrégation.\n
\n \n- Pour déterminer le nombre d'enregistrements dans une table, on utilise la requête :\n \n```SQL\nSELECT COUNT(*)\nFROM table_choisie\nWHERE conditions\n```\n
\n \n- Pour déterminer la valeur minimale d'un attribut, on utilise la requête :\n \n```SQL\nSELECT MIN(attribut)\nFROM table_choisie\nWHERE conditions\n```\n
\n \n- Pour déterminer la valeur maximale d'un attribut, on utilise la requête :\n \n```SQL\nSELECT MAX(attribut)\nFROM table_choisie\nWHERE conditions\n```\n
\n \n- Pour déterminer la valeur moyenne d'un attribut, on utilise la requête :\n \n```SQL\nSELECT AVG(attribut)\nFROM table_choisie\nWHERE conditions\n```\n
\n \n- Pour déterminer la somme des valeurs d'un attribut on utilise la requête :\n \n```SQL\nSELECT SUM(attribut)\nFROM table_choisie\nWHERE conditions\n```\n
\n\n
\n\n\n
\nQuestion 21 : Ecrire une requête permettant de compter le nombre de Livrets A.\n
"},{"metadata":{"trusted":false},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\nQuestion 22 : Ecrire une requête permettant d'obtenir la somme des montants des opérations positives (dépôts).\n
"},{"metadata":{"trusted":false},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\nQuestion 23 : Ecrire une requête permettant d'obtenir le montant du plus grand virement de notre base de données.\n
"},{"metadata":{"trusted":false},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\nQuestion 24 : Ecrire une requête permettant d'obtenir le montant du plus petit dépot de notre base de données.\n
"},{"metadata":{"trusted":false},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\nQuestion 25 : Ecrire une requête permettant d'obtenir la moyenne des retraits.\n
"},{"metadata":{"trusted":false},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\nQuestion 26 : A quoi sert la requête suivante ?\n \n```SQL\nSELECT COUNT(montant)\nFROM operation\nWHERE montant>0\n```\n
"},{"metadata":{"trusted":false},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\nQuestion 27 : Ecrire une requête permettant d'obtenir la moyenne des dépots sur les assurances vies.\n
"},{"metadata":{"trusted":false},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"name":"sql","display_name":"SQL","language":"sql"},"celltoolbar":"Éditer les Méta-Données"},"nbformat":4,"nbformat_minor":2}