sql-server-basics.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQL Server - Les basiques"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Qu'est-ce qu'un Système de Gestion de Base de Données (SGBD)\n",
    "\n",
    "Un SGBD est un logiciel qui va vous permettre de **manipuler les données d'une base**. C'est lui qui commande les intéractions avec celle-ci (ajouter, modifier ou supprimer des données).\n",
    "\n",
    "Pour communiquer/interagir avec votre base, les SGBD (relationnels) vont utiliser un langage : **Le SQL (ou Structured Query Language)**. Ce langage a été normalisé en 1986 et chaque SGBD utilise donc une base commune. Cependant, certains systèmes implémentes des fonctionnalités spécifiques avec des donc des extensions du langage."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Les bases\n",
    "\n",
    "Pour avoir une base de données facilement, vous pouvez utiliser Docker et la commande suivante (en partant de ce dossier)\n",
    "\n",
    "```\n",
    "docker run -e ACCEPT_EULA=Y -e SA_PASSWORD=P@ssw0rd -p 1433:1433 -v $(pwd)/data:/var/opt/mssql/backup mcr.microsoft.com/mssql/server:2019-latest\n",
    "```\n",
    "\n",
    "### Les types de commandes SQL\n",
    "\n",
    "Les commandes SQL sont réparties en plusieurs types afin que l'on puisse facilement identifier leur impact sur la base de données.\n",
    "\n",
    "- **DDL (Data Definition Language)** : Le DDL va permettre de gérer la structure de la base (modification/création/suppression d'une table par exemple). Nous y retrouvons les commandes suivantes : CREATE, DROP, ALTER, TRUNCATE. </br></br>\n",
    "- **DML (Data Manipulation Language)**: Le DML va permettre de gérer les données au sein de notre base (modification/création/suppression de données). Nous y retrouvons les commandes suivantes : INSERT, UPDATE, DELETE, MERGE. </br></br>\n",
    "- **DCL (Data Control Language)**: Le DCL va permettre de controller l'accès aux données de notre base. Nous y retrouvons les commandes suivantes : GRANT, REVOKE. </br></br>\n",
    "- **DTL (Data Transaction Language)**: Le DTL va nous permettre de gérer la partie transactionnelle de notre base de données. Quand nous devons effectuer de multiples requêtes afin que les données de notre base soient cohérentes. Nous y retrouvons les commandes suivantes : COMMIT, ROLLBACK, SAVEPOINT. </br></br>\n",
    "- **DQL (Data Query Language)**: Le DQL va nous permettre de questionner notre base et de récupérer les données qui y sont stockées. Nous y retrouvons les commandes suivantes : SELECT, SHOW, HELP.\n",
    "\n",
    "### Les principaux types de données\n",
    "\n",
    "Une base de données peut gérer de nombreux types de données. Les plus communs sont les suivants\n",
    "\n",
    "- VARCHAR : chaîne de caractères\n",
    "- INT : Nombre sans virgule\n",
    "- Date : Date\n",
    "- Float : Nombre avec virgule\n",
    "- Boolean : Valeur binaire, TRUE ou FALSE\n",
    "\n",
    "### Une table\n",
    "\n",
    "Une table est le rassemblement de données d'un même format (avec les mêmes colonnes, les mêmes règles...) au sein de la base.\n",
    "\n",
    "### Le schema\n",
    "\n",
    "Le schéma permet d'avoir une séparation logique entre les tables d'une même base. Le principe est de pouvoir isoler certaines domaines et d'y appliquer une politique de sécurité différente par exemple. Une table se retrouve toujours préfixé par son schema.\n",
    "\n",
    "```\n",
    "ex: Security.Users\n",
    "```\n",
    "\n",
    "Le schéma ici est \"Security\" et la table est \"Users\"."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## LE DDL\n",
    "\n",
    "### Créer une table\n",
    "\n",
    "Créer une table fonctionne de la manière suivante\n",
    "\n",
    "```\n",
    "CREATE TABLE table_name (\n",
    "  column1 datatype,\n",
    "  column2 datatype,\n",
    "  column3 datatype\n",
    ");\n",
    "```\n",
    "\n",
    "Viendra s'ajouter un certains nombre de mots clés au niveau des colonnes afin de spécifié une contrainte, un comportement. Par exemple :\n",
    "\n",
    "```\n",
    "CREATE TABLE student (\n",
    "  id INTEGER PRIMARY KEY,\n",
    "  name TEXT UNIQUE,\n",
    "  grade INTEGER NOT NULL,\n",
    "  age INTEGER DEFAULT 10\n",
    ");\n",
    "```\n",
    "\n",
    "### Modifier une table\n",
    "\n",
    "Lorsqu'une colonne manque à une table, nous pouvons utiliser la notion d'ALTER de la manière suivante\n",
    "\n",
    "```\n",
    "ALTER TABLE table_name\n",
    "ADD column_name datatype;\n",
    "```\n",
    "\n",
    "### Supprimer une table\n",
    "\n",
    "Pour supprimer une table, nous utilisons la commande suivante :\n",
    "\n",
    "```\n",
    "DROP TABLE table_name\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Le DML\n",
    "\n",
    "### Insérer des données\n",
    "\n",
    "Pour insérer des données dans une table nous utilisons la clause INSERT INTO\n",
    "\n",
    "```\n",
    "-- Insert into columns in order:\n",
    "INSERT INTO table_name\n",
    "VALUES (value1, value2);\n",
    " \n",
    "-- Insert into columns by name:\n",
    "INSERT INTO table_name (column1, column2)\n",
    "VALUES (value1, value2);\n",
    "```\n",
    "\n",
    "### Supprimer des données\n",
    "\n",
    "Pour supprimer des données, nous utilisons la commande suivante. **Attention à bien mettre une clause where, sous peine de vider la table**.\n",
    "\n",
    "```\n",
    "DELETE FROM table_name\n",
    "WHERE some_column = some_value;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Queries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SELECT name AS 'movie_title'\n",
    "FROM movies;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SELECT model \n",
    "FROM cars \n",
    "WHERE color = 'blue' \n",
    "  AND year > 2014;\n",
    "\n",
    "SELECT name\n",
    "FROM customers \n",
    "WHERE state = 'CA' \n",
    "   OR state = 'NY';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SELECT name\n",
    "FROM movies\n",
    "WHERE name LIKE 'Star%';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SELECT *\n",
    "FROM contacts\n",
    "ORDER BY birth_date DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SELECT DISTINCT city\n",
    "FROM contact_details;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SELECT *\n",
    "FROM movies\n",
    "WHERE year BETWEEN 1980 AND 1990;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SELECT *\n",
    "FROM movies\n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SELECT address\n",
    "FROM records\n",
    "WHERE address IS NOT NULL;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SELECT title\n",
    "FROM library\n",
    "WHERE pub_year = 2017;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SELECT MAX(amount) \n",
    "FROM transactions;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SELECT SUM(salary)\n",
    "FROM salary_disbursement;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SELECT COUNT(*)\n",
    "FROM employees\n",
    "WHERE experience < 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SELECT rating, \n",
    "   COUNT(*) \n",
    "FROM movies \n",
    "GROUP BY rating;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SELECT MIN(amount) \n",
    "FROM transactions;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SELECT AVG(salary)\n",
    "FROM employees\n",
    "WHERE experience < 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SELECT year, \n",
    "   COUNT(*) \n",
    "FROM movies \n",
    "GROUP BY year\n",
    "HAVING COUNT(*) > 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SELECT * \n",
    "FROM books\n",
    "JOIN authors\n",
    "  ON books.author_id = authors.id;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercice\n",
    "\n",
    "1. Retrouver le prénom et l'adresse email du client qui a pour nom de compagnie, 'Bike World' \n",
    "<br/><br/>\n",
    "\n",
    "2. Retrouver les noms de compagnies qui ont pour ville 'Dallas'\n",
    "<br/><br/>\n",
    "\n",
    "3. Combien de produits avec un prix > 1000$ ont été vendues\n",
    "<br/><br/>\n",
    "\n",
    "4. Retrouver les companies dont les clients  ont commandés pour plus de 10.000$ (en incluant le sous-total, les taxes et les frais de transport)\n",
    "<br/><br/>\n",
    "\n",
    "5. Retrouver le nombre de chaussettes de courses restantes, commandées par la société 'Riding Cycles'\n",
    "<br/><br/>\n",
    "\n",
    "6. Une « commande d'article unique » est une commande client pour laquelle un seul article est commandé. Affichez le SalesOrderID et le UnitPrice pour chaque commande d'article unique.\n",
    "<br/><br/>\n",
    "\n",
    "7. Retrouvez le nom du produit et le nom de la compagnie pour les clients ayant commandés le produit 'Racing Socks'\n",
    "<br/><br/>\n",
    "\n",
    "8. Retrouver la description du produit avec l'identifiant 736 pour la langue française.\n",
    "<br/><br/>\n",
    "\n",
    "9. En utilisant le SubTotal value dans SaleOrderHeader, lister les commandes de la plus petite à la plus grande. Pour chaque commande, listez le nom \n",
    "de la compagnie, le sous total et le poids total de la commande\n",
    "<br/><br/>\n",
    "\n",
    "10. Retrouver combien de produits avec la catégorie 'Cranksets' ont été vendues à une adresse à Londres.\n",
    "<br/><br/>\n",
    "\n",
    "11. Pour chaque client avec un 'Main Office' à Dallas, retrouvez la première ligne de l'adresse de livraison. Si aucune adresse n'existe, laissez là vide. N'ayez qu'une seule ligne par client.\n",
    "<br/><br/>\n",
    "\n",
    "12. Pour chaque vente, retrouvez le SalesOrderID et le sous-total, calculé à partir des 3 façons suivantes : depuis le SalesOrderHeader, la somme des OrderQty*UnitPrice et la somme des OrderQty*ListPrice\n",
    "<br/><br/>\n",
    "\n",
    "13. Retrouvez l'article le plus vendu, par valeur.\n",
    "<br/><br/>\n",
    "\n",
    "14. Retrouvez combien de commandes sont dans les intervals suivants : 0-99, 100-999, 1000-9999, 10000\n",
    "<br/><br/>\n",
    "\n",
    "15. Retrouvez les trois villes les plus importantes. Affichez la répartition de la catégorie de produits de premier niveau par rapport à chaque ville.\n",
    "<br/><br/>\n",
    "\n",
    "\n",
    "## Examen\n",
    "\n",
    "1. Lister les SalesOrderNumber pour les clients \"Good Toys\" et \"Bike World\" <br/><br/>\n",
    "\n",
    "2. Lister le nom des produits et les quantités commandées par la société \"Futuristic Bikes\" <br/><br/>\n",
    "\n",
    "3. Lister les noms et adresses des entreprises qui contiennent le mot 'Bike' (en lettres majuscules ou non) ou le mot 'Cycle' (en lettres majuscules ou non). Lister d'abord les entreprises qui contiennent le mot 'Bike'. <br/><br/>\n",
    "\n",
    "4. Lister le nombre de commandes par région (en commencant par la région qui a fait le plus de commandes). <br/><br/>\n",
    "\n",
    "5. Lister le meilleur client par région.<br/><br/>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  }
 ],
 "metadata": {
  "language_info": {
   "name": "plaintext"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}