SQL/UPDATE_INSERT_DELETE.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "5182ccd5",
   "metadata": {},
   "source": [
    "# SQL modification statements"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db2128e6",
   "metadata": {},
   "source": [
    "## Packages and database files needed for this project"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e07a946",
   "metadata": {},
   "source": [
    "*Note:* This section is repeated in several notebooks in order to make them functional as independent lectures."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6612d770",
   "metadata": {},
   "source": [
    "Import needed packages:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9e9677de",
   "metadata": {},
   "outputs": [],
   "source": [
    "import urllib.request    # needed for download of the example database\n",
    "import shutil            # needed for unziping of the example database\n",
    "\n",
    "from sqlalchemy import * # package providing unified access to various databases"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "589c6b99",
   "metadata": {},
   "source": [
    "If necessary, download the database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1e63454c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import urllib.request    # needed for download of the example database\n",
    "import shutil            # needed for unziping of the example database\n",
    "import os                # for checking existence/removing of a file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "95d9cdda",
   "metadata": {},
   "outputs": [],
   "source": [
    "if not os.path.isfile(\"chinook.db\"):\n",
    "    urllib.request.urlretrieve(\"https://www.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip\", \"chinook.zip\")\n",
    "    shutil.unpack_archive(\"chinook.zip\")\n",
    "    os.remove(\"chinook.zip\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e2e426d0",
   "metadata": {},
   "source": [
    "In this session we use the magic database connector. Install `ipython-sql` when necessary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "521a990f",
   "metadata": {},
   "outputs": [],
   "source": [
    "#!pip install ipython-sql #In case the SQLite not working in Jupyter\n",
    "%load_ext sql\n",
    "%sql sqlite:///chinook.db"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c3224b88",
   "metadata": {},
   "source": [
    "## UPDATE - change existing rows"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8ff49df",
   "metadata": {},
   "source": [
    "### Overview"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e69e0cab",
   "metadata": {},
   "source": [
    "To update existing data in a table, you use the `UPDATE` statement."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a5779034",
   "metadata": {},
   "source": [
    "```sql\n",
    "UPDATE table\n",
    "   SET column_1 = new_value_1,\n",
    "       column_2 = new_value_2\n",
    "   WHERE\n",
    "       search_condition \n",
    "   ORDER column_or_expression\n",
    "   LIMIT row_count OFFSET offset\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e224cd57",
   "metadata": {},
   "source": [
    "You need/may specify:\n",
    "- Name of the `table` where you want to make changes.\n",
    "- Names of the columns and their new value(s) in the `SET` clause.\n",
    "- Which rows to update using a condition in the `WHERE` clause. The `WHERE` clause is optional. If you skip it, the `UPDATE` statement will update data in all rows of the table.\n",
    "- Finally, use the `ORDER BY` and `LIMIT` clauses in the `UPDATE` statement to specify the number of rows to update.\n",
    "\n",
    "*Notes:*\n",
    "- If you use a negative value in the `LIMIT` clause, SQL updates all rows that meet the condition in the preceding `WHERE ` clause.\n",
    "- The `ORDER BY` clause should always goes with the `LIMIT` clause to specify exactly which rows to be updated. Otherwise, you will never know which row will be actually updated; because without the `ORDER BY` clause, the order of rows in the table is unspecified."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88202333",
   "metadata": {},
   "source": [
    "### Example"
   ]
  },
  {
   "attachments": {
    "image.png": {
     "image/png": "iVBORw0KGgoAAAANSUhEUgAAAMEAAAFYCAIAAABlPg/0AAAK2WlDQ1BJQ0MgUHJvZmlsZQAASImVlwdUU9kWhs+96Y2WgICU0HsXCCAl9NClg6iEJJBQQkwIKnZlcATHgogIKCM6VAVHhyJjAUSxDYoN+4AMCso4WLChMhd4hJl567233r5r3/OtnX322eese9b6AwAliC0SpcMKAGQIs8Thfp702Lh4Ou4pgJCHDBhAmc2RiJhhYUEAsdnx7/buDpKL2E2LqVr//vt/NSUuT8IBAEpAOIkr4WQg3I74GEckzgIAdQyJ663IEk3xLYRpYqRBhIenOGWGP09x0jSjFaZzIsO9ENYHAE9ms8UpAJCtkTg9m5OC1CGHIWwt5AqECG9A2I3DZ3MRRtYF5hkZmVM8irAxki8CgEJDmJH0l5opf6ufJKvPZqfIeGZf04b3FkhE6exV/+fR/G/LSJfOrmGIOJkv9g9HRlXk/O6mZQbKWJgUEjrLAu50/jTzpf5Rs8yReMXPMpftHSibmx4SNMvJAl+WrE4WK3KWeRKfiFkWZ4bL1koWezFnmS2eW1eaFiWL83ksWf0cfmTMLGcLokNmWZIWETiX4yWLi6Xhsv55Qj/PuXV9ZXvPkPxlvwKWbG4WP9Jftnf2XP88IXOupiRW1huX5+0zlxMlyxdlecrWEqWHyfJ56X6yuCQ7QjY3C/k45+aGyc4wlR0QNsuACSJAFOJ0EA3igS1wBE7AH/hk8VZmTW3GK1O0SixI4WfRmciN49FZQo6lOd3W2tYWgKn7O/NJvLk7fS8hFfxcjL8bAPulCHyciyUj96oF6VsRnosZZQJARc6hw5YjFWfPxNBTLwwgAnlAA2pAC+gBY2CBdOcAXIAH8AEBIBREgjiwFHAAH2QAMVgB1oCNIA8UgJ1gDygFFeAQqAFHwXHQAk6BDnABXAHXwW3wAPSDIfACjIF3YAKCIBxEgaiQGqQNGUBmkC3EgNwgHygICofioEQoBRJCUmgNtBkqgAqhUuggVAv9CJ2EOqBLUC90DxqARqDX0CcYBZNhGqwJG8JWMANmwoFwJLwEToGXwzlwLrwdLoEr4SNwM9wBX4Fvw/3wC3gcBVAklApKB2WBYqC8UKGoeFQySoxah8pHFaMqUQ2oNlQ36iaqHzWK+ojGoqloOtoC7YL2R0ehOejl6HXobehSdA26Gd2FvokeQI+hv2IoGA2MGcYZw8LEYlIwKzB5mGJMFaYJcx5zGzOEeYfFYlWwRlhHrD82DpuKXY3dht2PbcS2Y3uxg9hxHA6nhjPDueJCcWxcFi4Ptw93BHcWdwM3hPuAJ+G18bZ4X3w8XojfhC/G1+HP4G/gn+EnCAoEA4IzIZTAJawi7CAcJrQRrhGGCBNERaIR0ZUYSUwlbiSWEBuI54kPiW9IJJIuyYm0iCQgbSCVkI6RLpIGSB/JSmRTshc5gSwlbydXk9vJ98hvKBSKIcWDEk/Jomyn1FLOUR5TPshR5SzlWHJcufVyZXLNcjfkXsoT5A3kmfJL5XPki+VPyF+TH1UgKBgqeCmwFdYplCmcVOhTGFekKtoohipmKG5TrFO8pDishFMyVPJR4irlKh1SOqc0SEVR9aheVA51M/Uw9Tx1iIalGdFYtFRaAe0orYc2pqykvEA5WnmlcpnyaeV+FZSKoQpLJV1lh8pxlTsqn+ZpzmPO483bOq9h3o1571Xnq3qo8lTzVRtVb6t+UqOr+ailqe1Sa1F7pI5WN1VfpL5C/YD6efXR+bT5LvM58/PnH59/XwPWMNUI11itcUjjqsa4ppamn6ZIc5/mOc1RLRUtD61UrSKtM1oj2lRtN22BdpH2We3ndGU6k55OL6F30cd0NHT8daQ6B3V6dCZ0jXSjdDfpNuo+0iPqMfSS9Yr0OvXG9LX1g/XX6Nfr3zcgGDAM+AZ7DboN3hsaGcYYbjFsMRw2UjViGeUY1Rs9NKYYuxsvN640vmWCNWGYpJnsN7luCpvam/JNy0yvmcFmDmYCs/1mveYYcydzoXmleZ8F2YJpkW1RbzFgqWIZZLnJssXypZW+VbzVLqtuq6/W9tbp1oetH9go2QTYbLJps3lta2rLsS2zvWVHsfO1W2/XavdqgdkC3oIDC+7aU+2D7bfYd9p/cXB0EDs0OIw46jsmOpY79jFojDDGNsZFJ4yTp9N6p1NOH50dnLOcjzv/4WLhkuZS5zK80Gghb+HhhYOuuq5s14Ou/W50t0S379363XXc2e6V7k889Dy4HlUez5gmzFTmEeZLT2tPsWeT53svZ6+1Xu3eKG8/73zvHh8lnyifUp/Hvrq+Kb71vmN+9n6r/dr9Mf6B/rv8+1iaLA6rljUW4BiwNqArkBwYEVga+CTINEgc1BYMBwcE7w5+GGIQIgxpCQWhrNDdoY/CjMKWh/28CLsobFHZoqfhNuFrwrsjqBHLIuoi3kV6Ru6IfBBlHCWN6oyWj06Iro1+H+MdUxjTH2sVuzb2Spx6nCCuNR4XHx1fFT++2GfxnsVDCfYJeQl3lhgtWbnk0lL1pelLTy+TX8ZediIRkxiTWJf4mR3KrmSPJ7GSypPGOF6cvZwXXA9uEXeE58or5D1Ldk0uTB5OcU3ZnTLCd+cX80cFXoJSwatU/9SK1PdpoWnVaZPpMemNGfiMxIyTQiVhmrArUytzZWavyEyUJ+pf7rx8z/IxcaC4SgJJlkhas2iIULoqNZZ+Ix3Idssuy/6wInrFiZWKK4Urr64yXbV11bMc35wfVqNXc1Z3rtFZs3HNwFrm2oProHVJ6zrX663PXT+0wW9DzUbixrSNv2yy3lS46e3mmM1tuZq5G3IHv/H7pj5PLk+c17fFZUvFt+hvBd/2bLXbum/r13xu/uUC64Ligs/bONsuf2fzXcl3k9uTt/fscNhxYCd2p3DnnV3uu2oKFQtzCgd3B+9uLqIX5Re93bNsz6XiBcUVe4l7pXv7S4JKWvfp79u573Mpv/R2mWdZY7lG+dby9/u5+28c8DjQUKFZUVDx6XvB93cP+h1srjSsLD6EPZR96Onh6MPdPzB+qK1Sryqo+lItrO6vCa/pqnWsra3TqNtRD9dL60eOJBy5ftT7aGuDRcPBRpXGgmPgmPTY8x8Tf7xzPPB45wnGiYafDH4qb6I25TdDzauax1r4Lf2tca29JwNOdra5tDX9bPlz9SmdU2WnlU/vOEM8k3tm8mzO2fF2UftoR0rHYOeyzgfnYs/d6lrU1XM+8PzFC74XznUzu89edL146pLzpZOXGZdbrjhcab5qf7XpF/tfmnocepqvOV5rve50va13Ye+ZG+43Om5637xwi3Xryu2Q2713ou7c7Uvo67/LvTt8L/3eq/vZ9ycebHiIeZj/SOFR8WONx5W/mvza2O/Qf3rAe+Dqk4gnDwY5gy9+k/z2eSj3KeVp8TPtZ7XDtsOnRnxHrj9f/HzohejFxGje74q/l780fvnTHx5/XB2LHRt6JX41+XrbG7U31W8XvO0cDxt//C7j3cT7/A9qH2o+Mj52f4r59GxixWfc55IvJl/avgZ+fTiZMTkpYovZ01IAhTicnAzA62pEH8chWuE6AMTFM/p62qCZ/wTTBP4Tz2jwaXMAoNoDgGjEp+RReTsiaTcgmgTxMCQW6QFgOzuZ/8skyXa2M7VILYg0KZ6cfIPoR5wJAF/6JicnWiYnv1Qhzd4HoP3djK6fMuwJRN90AED4/V759lzwD5vR/H/Z4z9HIOvgb+OfTAoafkXnCMQAAAA4ZVhJZk1NACoAAAAIAAGHaQAEAAAAAQAAABoAAAAAAAKgAgAEAAAAAQAAAMGgAwAEAAAAAQAAAVgAAAAAw39vxwAAHE1JREFUeAHtnb/PFdXWx+9zc/8Bf5DXWJD4i4JY+iPcN1pgMBEaGo3Bgs5bQiHRgpKoxAYTGiggoCGG6kmMSgNWaEKNFigFFmjU+9ppZbwfnu9zF/udM2fO7D0/zp496xSHNWvWXnvt7/qevfczs2bY+Jt/HIEtBP766680JP6e1sxbOQKGwD8kJXPQHLkwXQQ2Nja6BO/zUBf0vO09BJxDzoOuCDiHuiLo7Z1DzoGuCDiHuiLo7Z1DzoGuCDiHuiLo7efIof/d+nju+0JgjhzqCzv3IwScQ86ErghkzaEPP/yQy/D6fP/99xorh//a+kiPyKlto40NM2O94lToYRlUoQ1NrBc8hE04RS/ShE2sR04t01t45iH0XIjM/bLcPqdOnQJcRSX5u+++41CIf/7558hvvvmmDmX2z62PyZyioQ6ROWmnTJYHeeYsZmgQwh7Ng7zplJSh2TK9+ZRbNczqmwj5JIek5untkzte2ZDIlDMsybEdIhgDQj1mYUa36LRNmmWnKs0rZnQkPpkegY9Fghx6aNBbwFsOsvsicj7JYWW6ln3xxReM6ujRo1oFnnrqqa1hbn89/fTT4eEyOTTbtWsXZkq52etQp6QMzSDQ2bNnpb98+bIIuiywZXqaM2V+9dVXGki48FkYUxcy5ZBg1YJlv48jR46MCfexY8foji0O/IAEBw4csN6XBVarf+WVVxgCp2jOj6E8GmXKIU08t27dsrQlCDdv3rRWclWZzxZ7Cc2efPJJpiJmoM3NTQQORQK+FwNbdGVdS4BJolFlLqyYTfjQfuv5CKQNQEFcIXEoAaXJyodtmyr7ISx1SmbWKtwqVXoJndOd5dvCQFlpYm5r9TS08IxDGkg+34yaT3I8ap7ePrnjNg2VFYVoqeLQZOXYklThEGbQpdKcfkMOcahWMjNXFh7G1p0p0cg+DIazi3pjoexDLpq3tQuKLTmM7QsetDdQyhC4usOe+syZM12Gw96FRYr5g5Woi5/M2+qqVTIHMt0PrR107XwPHz7M1FI2gbpD7RyqxxD26NfZcSar916Wtti1rKw0DTsaX8uGxde9r0TA17KVELnBCgScQysA8tMrEXAOrYTIDVYg4BxaAZCfXolApwetV3p3gwkh4NcYJ5Ss0kL1936UltGE8ej60I0bN5577rmE5r4fSgCtzCavv/76s88+e/78+T/++KMywmvXrr300kuPbH0Qvvzyy4rBvcPke7besAAERIg///zz008/5ebgww8//NZbb3HHUEM7ffr0zp07L1269O+tDwKHKG3g23yy4/GF8RlMAQYVHeOPNNseRQILD/bAIZgEny5evLhjx447d+7YWQQOUd69e1fKFWtZ5fkYdaanZFhE+dQabBMzg3+IMIMoJhbCE0888cEHH/zwww+vvvrq+++/z/z0ySef/PrrrzYM5qF9+/ZduXLFNPeEkGWhvFiTxVnKISipCc26yA29d3GrtrXOfR6qAHuPAamfBx54YOk8RCE6P2I9WUFdemoX3m4yCFSI9fvvv587d2737t0PPvjgyZMnf/nll9Dg0KFD7L7R/Pbbb9sjDE+bzGTDlLM4FdXOQzhSvbAEk605W5CwMNRmMuytx+1otv6REjGsHzVjszT/2C8qzT48i9L3Q4a5IWOacD/08ccfP/TQQ7dv37azCBig/Omnn6ztPeRDC5OlX4QbDt1rs/WxFHKEnrYiSijLIX6wkSwbyaZEMGKJghjg37pAKdmaYCBqIjQrQ+fEtjgoBTPPb8Dhs+zvMqrwHn30UTbX+rvswoULHPLknbBS23vfUdiRA0u2NQydWF45a7IJamJO1BCHop05NHvzLI3ohdI+EKtWiSts+K44h8G4so5cEJKPPfbYM888w/rFKlbBhMfr9u7dq+tDCByaAW23r1NbPkYTePjr4MGDbbqDImzIeAKVGnse8oIBsK1SosrubVHZxrnbGAL88bXsOvWePXuuXr1qlvWC0aqNYFNIaIxfO7QpBI3JCDbTaNqQvTVEQC8lgk0VmjZoi2BNzNJ8hs1D5WITPJtznZ35N9DxSQOBhonz0P79+7f63e7b5GaBTb5dsCHiijEaO0uOr1+/LgPmHmYg5i09aYoSMtkTq8xSMqtVWhfhWfh34sQJO+VCRwTGq8nnaiTbMeNBVNxc1WTh82d0okBrb6yf7uKvuo0H2i69PtSm/Tg2/CXJJOQEGgfthF4S17KEntKaMHvxVwArUVpzbzUCAuOtZSMMxrtIQ6D8tSwNF281GgIT2A+NhoV3lIaAcygNN291HwHn0H0sXEpDwDmUhpu3uo+Ac+g+Fi6lIeAcSsPNW91HII5DXPHjqvH91vESHuymmFrjEGW8J2+RCwJxHEqLusI8bqrbf4uR5tBbZYXAGByqDJi7p9z/0ovlK6f8cIoIdOUQKxFrkz5GCy1YKLVOccOLUo1w7uEGflg9YsDVesOPHhCQYHL4pIAC4DtUmlsXBkWgK4c+++wz1S5xW1S0IIuU+0hJ6JQBUQzE2bDykAoQ6n5CVmmQi96k5031OFQXJvPMic5CHXXHN6+1h4jS+/eoCFgOmgWxIbSp3FHnlDRhoWDYCtlKEKXH3owXveEQIKzHiivs8VZBCnaavQttEBCAbSwXbWjbaR7iF88iZX4VCnMMGlYrpgdb3Spp1iE2vMLXTtV6s7MNglW+KpKR/2uYhsBmcqoTh5gGyJ+Qso2IBK1W9n+jVCYYNcHmtdde47FcHdZ6W5kGatN4TsXIurg+rvTgBh0RiOYQEw8TDB82zuSPv7B0aHEwDUjDfkVTAkRhq1SbXQzwoLa13sxtgwD58K9OKdlusPRTQyDgNWhDoDoxn/z8iJidQELctI2ehxK68SZlI+AcKju/Y4zOOTQGymX34RwqO79jjM45NAbKZffhHCo7v2OMzjk0Bspl9+EcKju/Y4zOOTQGymX34RwqO79jjC6OQ1ZcxhVu7n+pZCyqXkcewpHRHGWocXlaCMRxiLFxg1MlFtSUqcyD7+YxQ5GQZ15P3QzX5M5Gc6j7CL2eujuGWXnoyiHd8tV6ROWQDm3Jk97rqbNKee/BRHPI6oes6CyMiWUOvddTh5jMRdYWZ+V3WM4sYwBCUJmiNJKtRBpl2ArZ66kFVD7fYnlaPLSNnoeW/aogh05po+311MuAKk/fG4cMGq+nNihmIvTPIa+nngl1bJheT21QzFfQX9PshxIgoG3/81BCHN5k0gg4hyadviyCdw5lkYZJB+EcmnT6sgjeOZRFGiYdhHNo0unLInjnUBZpmHQQzqFJpy+L4J1DWaRh0kEMxSFd+pw0NB58SwQiOEQBNcwIP2kl1S0jc7OpIJByvwwa1d5boXyRkg+VVy+zmQous4qTZDHe2pyuxIG2EfPQSnduME8EeuCQWMwktFg3LUwx0Ke2fHaeuBc46qg6SMYf2tthWPNqShNoEhqEHlxeLwLkKExTVDA07GEe2gqg/kuva/3vNLTBRMVbzOtNXTtZBAb/v8l5IjF8Q/5kgfLAlyLQ8zykhzqsN395tEFRsNAnh2rfQw2r/OXRBROIoaVcHyobkRmOjg0ro2YrnTB22vY5DyVE4E0KQMA5VEAS1zwE59CaE1BA986hApK45iE4h9acgAK6dw4VkMQ1D8E5tOYEFNC9c6iAJK55CM6hNSeggO6dQwUkcc1DiOOQvayTK9zI48deW9M9fhjeY4hAHIdoaW9T5A4rGQ199ShD0PCV1uaZMhJVSKGR4IUlBs66hGgOWaAHDhyw/xLalC7MEIF0DvH/0jMVGWRWrGhF05pLTG//BT1NTIkgD+EbrmkYlmZzKPvamWkxAPNpp1wYFIFoDlkxEK+7139fT3ykzYpwL1++bMnmZdbSq4pII8HYFkSESsqxv379uiqvWacqb7tehkWzz2WtXN8nAsaAZoHUKv2nTp1CljGaSiic5VSlAp+iWCz5IIS9yEwFkKa3ttJbX2ZAjyYv82kGLjQjoPQ12yw7S9voeUj9MQPxMnxbtiq0sPlJxvpeuXmCKKG95PZvu15s65ouCLzzzjtxzZexrKInzfzoTUkfzBMcIpje+ISxyZzFRg1DY/SaY/AjQTbWkaY0lAgmq0dZ6rvWZ2jgcgMCoMenYmCaq1ev7t2793+2PgjXrl0LLbeabn2F2gbZUisbY4ZWHLmzTGOMbH2Ebk1pvKlwSA1FQTOueAgPkc3MfFYM/HAZAoKuchYlmtOnT+/cufPSpUv/3vogcIjSjA32KgfNootALjVLdXHibUdAYBmH7t69u2PHjjt37oQxcIiSU1LSNnE/dJ99LpWLAJdjXn75ZSaecIgc7tu378qVK6Yc8LkOruvYa0CsPxcyRKByeSUqQuakAechLvOs/G86o8J140ERCBcsZPo6d+7cG2+8UdFzeOjQofPnz0v/888/DzgPDTpgd94jApqH4EToE+WPP/7IFZwbN248/vjjdur27dvPP//8N998wx9qUg44D1mvLkwUgUceeeTdd9994YUXPvroo//b+ly8ePHFF1987733jEAMzeehiea3z7CXzUOamb7++uvjx49/++23dLl79+4TJ07s2bMn7N45FKIxU7mWQydPnnz77bfbIOIcaoNS4Ta1HGo/Zt8PtcfKLesRGINDonl9/66dPgJxHLJyMGihIiG+TY5CI3SFh7BCLcqPG68dgTgOEa5ugXG39fDhwxyqNmPxWiIUsUq0ZYMM76ZR2maVJLX2bRzWNnTl0AhEc0gB8ZK8fiPjz8ijR4+upF2/nbq3XhBI5BBzhhVTa7tD+pkq0HOIEBZEEyhLFXo+DZMNlR7MTBhrfZS91rhFhzrb7LAXgNzJSgSi3wtLibScVi6NW0/Sk/Xwhuvm5iZ6yEFzHghZXPusOQLvH5YT2SNz6y10CHVkgDH6lQ5D5y73jkD0PGSbGBJJjsOAmHtqq2Cx0VNgUIeiorBJrQwnNM0YX0MzzUwy4NvfeR2CsxY5mkMWpS09pmnDDzOuCNDx7NmzbLNs7mGmscmmYmwltrJZRtxKKz8cCIF0DpHy2nnCAtXmxg4bBPFGlbW0giIyrmye5NDfed2A5FpORXMI3mgdIeUN25rad1VXRmiuEJhR9Lce3zwBoi5C+9AhZLLH3LgLGJq5PD4Cfr9sfMyz65FfLDEt2zmsDDd6Hlrp0Q3mhoBzaG4Z73+8zqH+MZ2bR+fQ3DLe/3idQ/1jOjePzqG5Zbz/8TqH+sd0bh6dQ3PLeP/jdQ71j+ncPDqH5pbx/scbxyGKdcJ6D2Q0BIXA9fLwVEOkNNHtMH17JXUDVpM4FcehZUPi5it3WxpuwVYactNUZRt8eyV1BZzJHUbXMfY+QmjEhOS1iL0DO5rDfuYhwoUHfGt1U1W1xmDLVqUYKByhlbNpTQzXOBa+Sml2G4ehc5eHRiCaQ1b0Qy6Rl8XH7MIpbGzNCt9bvayVKqlpogohzMJ3VSc4XNaR63tEIJpD4VYGeTEUq6pOKHz2SupFPPPXRHNo5ZDCquo2hc+sX15JvRLVnA3655CNtk3hMwRiQfRKagNtisKAHAIObWu0Cw4Ln21ThcDuxyupp0gdi9nrqQ2K+Qr8yBm8/gxKQGHYeSghIG8yOQScQ5NLWXYBO4eyS8nkAnIOTS5l2QXsHMouJZMLyDk0uZRlF7BzKLuUTC4g59DkUpZdwM6h7FIyuYD655BKiBaBWKZftHTNtBBI4dC/tj7TGqdHOxwC0RxiOuEdU1RrDBeTe54WAtEcotSQV5JRGNRQ2zotCDzajghEc4g3kVNtePDgQWpbw75V4MF3pUB2Ua+NUXPNtT0/hDG9VA7Dfl1eOwJxHKK8lTJFngGi4oeaV3s0DKJQR6bSaXsjJ2NbptewVWyAjRryrZpr6MV/ASkllpXDtUPmAdQjYFlsFuDHqVOnZIPMBxn2SJCeujN41qBXFbaMVcEYxoR/GcgJZpVDNfTvHhEQ/skOI54vY1nRVprlzLJ+7Ngxk9sLlZprvQE9bM546E7TGHNe5TC0dDkXBNpwkBkinG9oAhU0LTEMW8tQ8pHDWr1NVIs28m9THYI+sgxlafy7FwTEwmRX2yRu0x5T0h9aaiVCo+VGvlAah2r1FQ6FNrBE/rfD2irQRFM5DGNwuTsCgjfZj9dTGz/nK7BnYPBwKA2CuL/L0vrwVmUj4BwqO79jjM45NAbKZffhHCo7v2OMzjk0Bspl9+EcKju/Y4zOOTQGymX34RwqO79jjM45NAbKZffhHCo7v2OMLoVDXBrXh7pqYtQNdhWLjRGy95EZAnEcougM9nCXVPfnqGZEE76cmoJDJ1NmKR48nIj6IWI5ceIEBLJ3mev9ZYPH6B3kjUDEPKTKVyNQOC4mJw7Dd0mjCSckGYRNXC4GgQgOMWbKnBtGHr5LmkogngCRMeTjsKGhn5o0AnEcaj/UI0eOWMksKyCPgrRv65bTQiCCQ+x+qKcOV6jmoTL38EgGkxCzV+0K2Nzcz04FgQgOMSRoET4+JopUhmq1rcw9POuzubmZVrdfceuH2SIQxyFWKMql2SDrw6gqf5rxCCz/l5SuGzH3MAPxYLVPQtmmv5fAhq2nhkxcQ6rwrJe43UmPCDAj4C3Hemp2TkxCTqAek52nq7hrjO3HoGtFtjdq39AtJ4fAsGvZ5OCYZ8D5rmXzzMcMRx33d9kMAfIhr0TAObQSIjdYgYBzaAVAfnolAs6hlRC5wQoEnEMrAPLTKxFwDq2EyA1WIBDNoe1bZRsbKklb4d5PzwCBOA5BIHvfGTfkhc/KGuqVBjPAueQhRnCI+1+84Mzufy2+RLFknHxsKxFo+R41/NhDHWoCq+Rcr1IMb5BpxqoYhDeHqUZq2a+bDYqAMpjcxTa7WrYXRRbf7GnEMlrIUm6hkRnQn/UV6k3pwvgIdORQxFpGT1STMcLdu3ezMaKIcZuAwT/ULmrTHZY72nltw21XzlvSrW7fbFyYHAJxHNLwqGaESXqnfThgNkxQx35G4SmTK3MYruyUCxNFIIJDUERFrotD1crFNxTR2cosJQP241T120WBZd4W/btmAgjY5NEssE22wdgf+eyBUGqCsR20lPIWGohMcmKbp+ZO/ezQCCgdyb14DZr9KOYrsENl8HAoDYKItSytA29VPALOoeJTPPgAnUODQ1x8B86h4lM8+ACdQ4NDXHwHzqHiUzz4AJ1Dg0NcfAfOoeJTPPgAnUODQ1x8B86h4lM8+ABTOGTFG37TdPD8TKGDOA5xyx0Ccd9U9+f0fupehuk1173AuBYnce+O8fdTryVJmXcaMQ+p7mfZi/FsgdNNYIZNvRGzi43fZhoMNJ8hqMyIU9Q0Ur/G4qhW6OWQQ/Ngnk3jQg4IRHCIcJe9n5rsWjkRwspk81wRqyFrIu8fhiXhi60NFAwoMLJiWWinOiQzcCEvBLS/af6GHJVKVtkv6lVsD0UQzKdV4DPyRaWdVZGaGZixGdgpF/pCQFxM9hYxD6mSNVxcon4HrFYt7aGLWTL3sK4xCflLrg2T3IQIDhE6GQ0f2FB2K1XStm1i5wRvxDkpmwevGahi4y+5rgCS4WEch5a9n5ppkNdSaxfM327sbzRUcQ49G6BwdlkEInyxdXgWIvpLrkNAMpQnUE/NH2v+kutBqaO/gZgI0nqJm4fS+ujSiqXQX3LdBcAR2sZdYxwhoLALXTeq3SeFZi6vF4EJrGXrBWgOvRe+ls0hhVMfY+77oanjO4f4nUNzyPKwY3QODYvvHLw7h+aQ5WHH6BwaFt85eM+IQ/oLsyPoXJMMi5Y6evPmbRCI4xDpIdP6qHysTR9pNv/tZ8OrttMAHK1VHIcIi6vGKjShfKzN3XgbCfxrWTeCWwhkHfVYtW3BuNAjAtEcsr65J3/r1i077FFYrNq2l2L32Iu76guBdA6FEdi6g2B6W/i0R7GKaQzQWJPKZKbD9lXb6s68heVNnDL90CuvjXqGQiKHIAFrGQViytNiMTU5o+5Hqx42lYppqqR1igWLwqMK7rFV2xDFArCXiiow9cL34ltsK536YVcEDOtmIawj036loZiamLA3h8i2xUEII5YNGoRFhzq7qJfDih7P6hR92AUyi68F40KIgIAKNVFy9DxkPFi23CggzhLHhQsXmCQqqxVzWMNrrDtWbYe8qTxB4O/CDsHpX27Ju3AusSZEw49ehwiaBuxHjyAZvcz4tuxyiuZqa0Ko5BSHaljbEQahnl74mEM15NB61Cn/DhEAQD6hJkpW87btSY/NQ2E3217+/+JlSlmKGcolfnQ2pAsa80nurTk2pjelEYVT4cpIQzsV6kMn5s0FISBUk9HwGjSj5XwF9hsMHg6lQRC9H0rrxlsVjIBzqODkjjQ059BIQBfcjXOo4OSONDTn0EhAF9yNc6jg5I40NOfQSEAX3I1zqODkjjQ059BIQBfcTQ8c0lXOgjHyoTUjEMchKyuDN17V1YzsfM5Gv/eDG5mq+oBGu3bt8irV+XBl2Ujj5qHQC3fCB6qnDntxOX8E0jkUjk1PYlQWOA7tY8Zomo19iTSsJia0rB2x+iGV5vCtggFVBVWUVv+lYiB1AS61xhaAdWEaF4ZGQGRN7mWb6y3bk2D7cYhANERjzcUASFOpGzRm1BqbTwleL2Z4jiMI9uS+0vfUlcR3OYRwZ86c6eLB264RgX72Q5UBLHtjdcVMhxVjfzK6FqWcldHzUMvBMDGyfZYxC5m9sbq2OcuiPVvIQlZr48psEfB66mxTM15g+rXzs0/rcpC1LC0UbzVRBJxDE01cRmE7hzJKxkRDcQ5NNHEZhe0cyigZEw3FOTTRxGUUtnMoo2RMNBTn0EQTl1HYzqGMkjHRUJxDE01cRmHHcSisp/aboxmlca2hRN9z5f5o81v01joc73wNCMTNQ2sI0LvMHoGuHFp80zQF0bbMhXL2UHiAiQhEc4hCH1Xaq3h+8U3Tev0qhffQi7dCe4FiYmYm16xlLa2VRZu96vBtvKbHEqUdupAzAkpfcoTRe2qjC4K9aVpKK1wMbVwuHoHotSxEhEmIcnppwufC2A8dP36cpztsYxS2crkwBDpxiHL6mzdvantkuIg3nOKD0mlkyJQqeD11qZmNGJfXU0eA5aZDINBpLRsiIPc5OQScQ5NLWXYBO4eyS8nkAnIOTS5l2QXsHMouJZMLyDk0uZRlF7BzKLuUTC4g59DkUpZdwOvnkN+pzY4UkQHFcYibX7o7xje11ZF93TdX5Rrf91UuTRaB6NoP7sbrZmqXIet/Lu/iwdvmg0DcPJRP3B5JPgj0wCHWNXvlNILJVlGklUuLIGc1eA7zQcEj6YJANIf2798vNhhF6H5zc5NKSkrSOGvy0aNHFdlizXWXiL1tpgi0rKWlapH9UMWYIZkmLLg2ubbm2lqZYE5cGBkBkTK50+g9tfpr/+011+2xmqhl9FoWO85lNdexftw+WwSiOWT7oZabYi4ELNZcZwuHB5aAgNdTJ4BWWhNNB+yH0gYWPQ+ldeOtCkbAOVRwckcamnNoJKAL7sY5VHByRxqac2gkoAvuxjlUcHJHGppzaCSgC+7GOVRwckcamnNoJKAL7sY5VHByRxqac2gkoAvuxjlUcHJHGppzaCSgC+7GOVRwckcamnNoJKAL7sY5VHByRxqaP6AzEtD5d+M1aPnnqNgI/wP4so6kefBiKgAAAABJRU5ErkJggg=="
    }
   },
   "cell_type": "markdown",
   "id": "698f3b99",
   "metadata": {},
   "source": [
    "We use the `employees` table in the sample database to demonstrate the `UPDATE` statement.\n",
    "\n",
    "![image.png](attachment:image.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "069555f3",
   "metadata": {},
   "source": [
    "The following `SELECT` statement gets partial data from the `employees` table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45608821",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT EmployeeId, FirstName, LastName, Title, Email\n",
    "  FROM employees\n",
    "  ORDER BY EmployeeId LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d691d4f1",
   "metadata": {},
   "source": [
    "Let's suppose that *Jane* changed her last name to *Smith*. You can perform such update as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "727e50fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "UPDATE employees\n",
    "  SET LastName = 'Smith'\n",
    "  WHERE EmployeeId = 3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd6833b1",
   "metadata": {},
   "source": [
    "To verify the `UPDATE` let's show the same few rows again:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b30ae4b",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT EmployeeId, FirstName, LastName, Title, Email\n",
    "  FROM employees\n",
    "  ORDER BY EmployeeId LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a2e480b6",
   "metadata": {},
   "source": [
    "## INSERT - add new rows"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a837c31d",
   "metadata": {},
   "source": [
    "### Overview"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d7ac85b",
   "metadata": {},
   "source": [
    "To insert data into a table, you use the `INSERT` statement:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05a2a325",
   "metadata": {},
   "source": [
    "```sql\n",
    "INSERT INTO table (column_1, column_2, column_3)\n",
    "  VALUES (new_value_1, new_value_2, new_value_3) \n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "761c8f33",
   "metadata": {},
   "source": [
    "The columns of a table which are not listed will get their default values."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fbb860ee",
   "metadata": {},
   "source": [
    "### Example (insert a provided value)"
   ]
  },
  {
   "attachments": {
    "image.png": {
     "image/png": "iVBORw0KGgoAAAANSUhEUgAAAK4AAABUCAIAAACUZ5vGAAAK2WlDQ1BJQ0MgUHJvZmlsZQAASImVlwdUU9kWhs+96Y2WgICU0HsXCCAl9NClg6iEJJBQQkwIKnZlcATHgogIKCM6VAVHhyJjAUSxDYoN+4AMCso4WLChMhd4hJl567233r5r3/OtnX322eese9b6AwAliC0SpcMKAGQIs8Thfp702Lh4Ou4pgJCHDBhAmc2RiJhhYUEAsdnx7/buDpKL2E2LqVr//vt/NSUuT8IBAEpAOIkr4WQg3I74GEckzgIAdQyJ663IEk3xLYRpYqRBhIenOGWGP09x0jSjFaZzIsO9ENYHAE9ms8UpAJCtkTg9m5OC1CGHIWwt5AqECG9A2I3DZ3MRRtYF5hkZmVM8irAxki8CgEJDmJH0l5opf6ufJKvPZqfIeGZf04b3FkhE6exV/+fR/G/LSJfOrmGIOJkv9g9HRlXk/O6mZQbKWJgUEjrLAu50/jTzpf5Rs8yReMXPMpftHSibmx4SNMvJAl+WrE4WK3KWeRKfiFkWZ4bL1koWezFnmS2eW1eaFiWL83ksWf0cfmTMLGcLokNmWZIWETiX4yWLi6Xhsv55Qj/PuXV9ZXvPkPxlvwKWbG4WP9Jftnf2XP88IXOupiRW1huX5+0zlxMlyxdlecrWEqWHyfJ56X6yuCQ7QjY3C/k45+aGyc4wlR0QNsuACSJAFOJ0EA3igS1wBE7AH/hk8VZmTW3GK1O0SixI4WfRmciN49FZQo6lOd3W2tYWgKn7O/NJvLk7fS8hFfxcjL8bAPulCHyciyUj96oF6VsRnosZZQJARc6hw5YjFWfPxNBTLwwgAnlAA2pAC+gBY2CBdOcAXIAH8AEBIBREgjiwFHAAH2QAMVgB1oCNIA8UgJ1gDygFFeAQqAFHwXHQAk6BDnABXAHXwW3wAPSDIfACjIF3YAKCIBxEgaiQGqQNGUBmkC3EgNwgHygICofioEQoBRJCUmgNtBkqgAqhUuggVAv9CJ2EOqBLUC90DxqARqDX0CcYBZNhGqwJG8JWMANmwoFwJLwEToGXwzlwLrwdLoEr4SNwM9wBX4Fvw/3wC3gcBVAklApKB2WBYqC8UKGoeFQySoxah8pHFaMqUQ2oNlQ36iaqHzWK+ojGoqloOtoC7YL2R0ehOejl6HXobehSdA26Gd2FvokeQI+hv2IoGA2MGcYZw8LEYlIwKzB5mGJMFaYJcx5zGzOEeYfFYlWwRlhHrD82DpuKXY3dht2PbcS2Y3uxg9hxHA6nhjPDueJCcWxcFi4Ptw93BHcWdwM3hPuAJ+G18bZ4X3w8XojfhC/G1+HP4G/gn+EnCAoEA4IzIZTAJawi7CAcJrQRrhGGCBNERaIR0ZUYSUwlbiSWEBuI54kPiW9IJJIuyYm0iCQgbSCVkI6RLpIGSB/JSmRTshc5gSwlbydXk9vJ98hvKBSKIcWDEk/Jomyn1FLOUR5TPshR5SzlWHJcufVyZXLNcjfkXsoT5A3kmfJL5XPki+VPyF+TH1UgKBgqeCmwFdYplCmcVOhTGFekKtoohipmKG5TrFO8pDishFMyVPJR4irlKh1SOqc0SEVR9aheVA51M/Uw9Tx1iIalGdFYtFRaAe0orYc2pqykvEA5WnmlcpnyaeV+FZSKoQpLJV1lh8pxlTsqn+ZpzmPO483bOq9h3o1571Xnq3qo8lTzVRtVb6t+UqOr+ailqe1Sa1F7pI5WN1VfpL5C/YD6efXR+bT5LvM58/PnH59/XwPWMNUI11itcUjjqsa4ppamn6ZIc5/mOc1RLRUtD61UrSKtM1oj2lRtN22BdpH2We3ndGU6k55OL6F30cd0NHT8daQ6B3V6dCZ0jXSjdDfpNuo+0iPqMfSS9Yr0OvXG9LX1g/XX6Nfr3zcgGDAM+AZ7DboN3hsaGcYYbjFsMRw2UjViGeUY1Rs9NKYYuxsvN640vmWCNWGYpJnsN7luCpvam/JNy0yvmcFmDmYCs/1mveYYcydzoXmleZ8F2YJpkW1RbzFgqWIZZLnJssXypZW+VbzVLqtuq6/W9tbp1oetH9go2QTYbLJps3lta2rLsS2zvWVHsfO1W2/XavdqgdkC3oIDC+7aU+2D7bfYd9p/cXB0EDs0OIw46jsmOpY79jFojDDGNsZFJ4yTp9N6p1NOH50dnLOcjzv/4WLhkuZS5zK80Gghb+HhhYOuuq5s14Ou/W50t0S379363XXc2e6V7k889Dy4HlUez5gmzFTmEeZLT2tPsWeT53svZ6+1Xu3eKG8/73zvHh8lnyifUp/Hvrq+Kb71vmN+9n6r/dr9Mf6B/rv8+1iaLA6rljUW4BiwNqArkBwYEVga+CTINEgc1BYMBwcE7w5+GGIQIgxpCQWhrNDdoY/CjMKWh/28CLsobFHZoqfhNuFrwrsjqBHLIuoi3kV6Ru6IfBBlHCWN6oyWj06Iro1+H+MdUxjTH2sVuzb2Spx6nCCuNR4XHx1fFT++2GfxnsVDCfYJeQl3lhgtWbnk0lL1pelLTy+TX8ZediIRkxiTWJf4mR3KrmSPJ7GSypPGOF6cvZwXXA9uEXeE58or5D1Ldk0uTB5OcU3ZnTLCd+cX80cFXoJSwatU/9SK1PdpoWnVaZPpMemNGfiMxIyTQiVhmrArUytzZWavyEyUJ+pf7rx8z/IxcaC4SgJJlkhas2iIULoqNZZ+Ix3Idssuy/6wInrFiZWKK4Urr64yXbV11bMc35wfVqNXc1Z3rtFZs3HNwFrm2oProHVJ6zrX663PXT+0wW9DzUbixrSNv2yy3lS46e3mmM1tuZq5G3IHv/H7pj5PLk+c17fFZUvFt+hvBd/2bLXbum/r13xu/uUC64Ligs/bONsuf2fzXcl3k9uTt/fscNhxYCd2p3DnnV3uu2oKFQtzCgd3B+9uLqIX5Re93bNsz6XiBcUVe4l7pXv7S4JKWvfp79u573Mpv/R2mWdZY7lG+dby9/u5+28c8DjQUKFZUVDx6XvB93cP+h1srjSsLD6EPZR96Onh6MPdPzB+qK1Sryqo+lItrO6vCa/pqnWsra3TqNtRD9dL60eOJBy5ftT7aGuDRcPBRpXGgmPgmPTY8x8Tf7xzPPB45wnGiYafDH4qb6I25TdDzauax1r4Lf2tca29JwNOdra5tDX9bPlz9SmdU2WnlU/vOEM8k3tm8mzO2fF2UftoR0rHYOeyzgfnYs/d6lrU1XM+8PzFC74XznUzu89edL146pLzpZOXGZdbrjhcab5qf7XpF/tfmnocepqvOV5rve50va13Ye+ZG+43Om5637xwi3Xryu2Q2713ou7c7Uvo67/LvTt8L/3eq/vZ9ycebHiIeZj/SOFR8WONx5W/mvza2O/Qf3rAe+Dqk4gnDwY5gy9+k/z2eSj3KeVp8TPtZ7XDtsOnRnxHrj9f/HzohejFxGje74q/l780fvnTHx5/XB2LHRt6JX41+XrbG7U31W8XvO0cDxt//C7j3cT7/A9qH2o+Mj52f4r59GxixWfc55IvJl/avgZ+fTiZMTkpYovZ01IAhTicnAzA62pEH8chWuE6AMTFM/p62qCZ/wTTBP4Tz2jwaXMAoNoDgGjEp+RReTsiaTcgmgTxMCQW6QFgOzuZ/8skyXa2M7VILYg0KZ6cfIPoR5wJAF/6JicnWiYnv1Qhzd4HoP3djK6fMuwJRN90AED4/V759lzwD5vR/H/Z4z9HIOvgb+OfTAoafkXnCMQAAAA4ZVhJZk1NACoAAAAIAAGHaQAEAAAAAQAAABoAAAAAAAKgAgAEAAAAAQAAAK6gAwAEAAAAAQAAAFQAAAAAeLQ1uAAABXNJREFUeAHtnTFS5DAQRWFrjwIJR4ATAAkRKRmEkHAKEgghIyUiAU4AJ6AIgLuwj/1VjcrWeOxhBsbSd+Bqt1pt9e9vSTPThpUVH0bACBiBFIFVXXx8fKRay1UhsLr6SYM/VcXsYDsQMBU6wKmryVSoK98d0ZoKHeDU1WQq1JXvjmhNhQ5w6moyFerKd0e0psLK1taWPlh3wFRDU41UuLi4GJT7ofYj5c3fkY57jsN+fHyco7fRu+KL57Ef9/f3aRoinMPDQ/Tn5+eckTc3N8MMGTMpwz5aEVC27VE2bKLvSAWFU84CcXt7G5kgtqOjozRhLy8vtF5eXjIHiBZctucDFg6YIT8ya9u3bdIbjVcuhwqkOdJAFq+uruIS4fT0NL3skJ+fn9V6fHw8yayPzaS+S6svhwoPDw88rzpOTk4aiK+trTU02UtWmaenJzl5f3+f2SbbccmVhVCBtO3s7DAZpHP7DNBvb2/jQduO9fX1LBv62Mxw61/vUggV3t7egHJ3d1eAsjP4DrIkW2yQ26yrPjbZjkurLIQKwvfu7g6BlaKxUciij1lDzxzAVwipkokhLmXfbRPGIxY0r476rA+N5ICPf/EZgYikb4Smj4iND5ONOYDL6BX2HTZhPDpBxHVB24gf4HkNnW0yropaIOYFTZ1+TIU6856J2lTIgFKnylSoM++ZqE2FDCh1qkyFOvOeidpUyIBSp8pUqDPvjtoIGAEjMBUBf/E8FaLyDfzFc/k5HhSht42D4CrZeKmpQHEA76uUDP8yxTaFCh2ZoKS4UVWcjYt0shRxzraGUstVXFr4eQSmUGF/fz87JlJL1W9HsRAcUvqpL6WUI1tlGjbZW1j5wwhMpIIKiCkd5nltlHkxRErHYAkFQu2mHw7At5szAtkaLEo9SXaUEac23J7SLgxUE0YTl8iqJFP5FzZ0pwlBfUMvY8XQsJG9mjiHf3nweREIBNqfQvYG0reTkTIAGy7pHlSQK3qhkSw/sERZlzHntg3KcIiMffvu6H3MFwEw/zoGuSZDMVUgc9CdxOMu/LTTLIM0tW0bzUPhRPSKSwsLQkA8GPwmNZtB7RbTN5D0Ghqp/SJXS9L+ke5sPkg57xG0TKz4TQQmbhsnDYoNo6aBYCgM0AsIjS6aBkKpDSaEYEZ5fX2VvmEDP+BZvKFwcHAQ3S38EAKR16kCAyJ/qRmPuJTp5K/9o0hDq+wjGF1mbVJy4Dn1md7U8hwRUF78c1Tws15B3+8NXiDqBaz0yE2F0jPcOz5ToTdUpRuaCqVnuHd8pkJvqEo3NBVKz3Dv+EyF3lCVbmgqlJ7h3vGZCr2hKt3QVCg9w73jG0YFStAaRYj80thR/9h7GDb8fQSGUYHx8gtTn+rW34/MIxiIwGAq7O3tUeAavyMPvJ3NlxeBwVQglOvra/50ajsmlaWwgnAEVyT/130qOSSn9bHScE6Vbf/WLBSBWaig8pP2MkEBi35Ep+Yg5Yr+JruUIUcRFAyIn95vbm7g00IDtvMpCEQyugUKSSgnkQ0y2eWI6hLk9DYyQxM+1SXtrrKXtFdUTUYvC4tGQPjPMiuoJ8tEWnDG08yfwY1Bp9ntlhvlcR1/er/bj1u/icDsVGCZ4K2Ys7MzjYApgaRK7r/kN4oZ24vON8Nz9/4IzE4F7sETHP8kg6QiawPY//ZYwiE2Fuq4sbExqK+N54iAaxvnCOZYXfEcMvRvzQpjDd3jziFgKuRQqVJnKlSZ9lzQpkIOlSp1pkKVac8FbSrkUKlSZypUmfZc0KZCDpUqdaZClWnPBW0q5FCpUmcqVJn2XNCmQg6VKnWmQpVpzwVtKuRQqVJnKlSZ9lzQpkIOFeuMgBEwAkZg5R/2w/xmvwp1aQAAAABJRU5ErkJggg=="
    }
   },
   "cell_type": "markdown",
   "id": "9e97f80d",
   "metadata": {},
   "source": [
    "We will use the `artists` table in the sample database for the demonstration.\n",
    "\n",
    "![image.png](attachment:image.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5768a96e",
   "metadata": {},
   "source": [
    "Let's first check the number of records in the `artists` table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b457e75",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT COUNT(*) FROM artists"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a4070de8",
   "metadata": {},
   "source": [
    "The following statement inserts a new row into the `artists` table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "77fbbbfd",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "INSERT INTO artists (Name)\n",
    "   VALUES('Bud Powell')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dde721b4",
   "metadata": {},
   "source": [
    "Let's check the count of rows after the insert:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c9f3837a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT COUNT(*) FROM artists"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9bef78ed",
   "metadata": {},
   "source": [
    "Because the `ArtistId` column is an auto-increment column, you can ignore it in the statement. SQL will automatically geneate a sequential integer number to insert into the `ArtistId` column."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d43b7deb",
   "metadata": {},
   "source": [
    "You can verify the insert operation by using the following `SELECT` statement:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7c7d800d",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "  FROM artists\n",
    "  ORDER BY ArtistId DESC LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43649560",
   "metadata": {},
   "source": [
    "### Example (insert default values)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e06e7f06",
   "metadata": {},
   "source": [
    "`INSERT DEFAULT VALUES` adds a new row into a table using the default values specified in the column definition or `NULL` if the default value is not available and the column does not have a `NOT NULL` constraint. For autoincrement columns, the default value is the next integer.\n",
    "\n",
    "For example, the following statement inserts a new row into the `artists` table using `INSERT DEFAULT VALUES`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "da7cbcd1",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "INSERT INTO artists \n",
    "  DEFAULT VALUES "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f4d77bf",
   "metadata": {},
   "source": [
    "Let's verify the recent insert:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "09ff13d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT *\n",
    "  FROM artists\n",
    "  ORDER BY ArtistId DESC LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5632c30e",
   "metadata": {},
   "source": [
    "### Example (insert many rows from another `SELECT` statement)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cfd0cd15",
   "metadata": {},
   "source": [
    "Suppose you want to backup the `artists` table. You can follow these steps:\n",
    "- Create a new table named `artists_backup`.\n",
    "- Insert data from `artists` into the `artists_backup`.\n",
    "- (To clean up) Drop the new table."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5eccf41",
   "metadata": {},
   "source": [
    "Let's create the new table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bb58052f",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE TABLE artists_backup(\n",
    "   ArtistId INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "   Name NVARCHAR\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7261cefb",
   "metadata": {},
   "source": [
    "There should be no content in the new table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5cc377ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT * FROM artists_backup LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47baf064",
   "metadata": {},
   "source": [
    "Let's insert new data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6e832890",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "INSERT INTO artists_backup \n",
    "  SELECT ArtistId, Name\n",
    "  FROM artists;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c2db6c1",
   "metadata": {},
   "source": [
    "Has the data been copied?:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "73627752",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT * FROM artists_backup LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f5d7baa",
   "metadata": {},
   "source": [
    "Let's clean up:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "468ae8a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql DROP TABLE artists_backup"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf0b50fb",
   "metadata": {},
   "source": [
    "## DELETE - remove rows"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f428a4a5",
   "metadata": {},
   "source": [
    "### Overview"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7278e7e",
   "metadata": {},
   "source": [
    "The `DELETE` statement allows you to delete one row, multiple rows, or all rows in a table. The syntax of the `DELETE` statement is as follows:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ecf488d",
   "metadata": {},
   "source": [
    "```sql\n",
    "DELETE FROM table\n",
    "  WHERE condition\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f92d8c8c",
   "metadata": {},
   "source": [
    "### Example (removing rows matching a condition)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22b43606",
   "metadata": {},
   "source": [
    "Let's create a playground by recreating the same table `artists_backup` as used in the `INSERT` exercise  \n",
    "(*Note:* You need semicolons at line ends to have multiple SQL statements in one Jupyter cell):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0138675c",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "DROP TABLE IF EXISTS artists_backup;\n",
    "CREATE TABLE artists_backup(\n",
    "   ArtistId INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "   Name NVARCHAR\n",
    ");\n",
    "INSERT INTO artists_backup \n",
    "  SELECT ArtistId, Name\n",
    "  FROM artists;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d1c8f1ae",
   "metadata": {},
   "source": [
    "Let's list all rows with artists' `Name`s starting with `E`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de712cc9",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT * FROM artists_backup WHERE Name LIKE \"E%\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75e28a6b",
   "metadata": {},
   "source": [
    "All these artists can be removed as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "86b599b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql \n",
    "DELETE FROM artists_backup \n",
    "  WHERE Name LIKE \"E%\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "189cd77f",
   "metadata": {},
   "source": [
    "Let's check:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "afc57276",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT * FROM artists_backup WHERE Name LIKE \"E%\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36317206",
   "metadata": {},
   "source": [
    "### Example (removing all rows, but keeping the table)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "99d5ae22",
   "metadata": {},
   "source": [
    "To remove all rows in the `artists_backup` table, you just need to omit the `WHERE` clause as the following statement:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "72d99467",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "DELETE FROM artists_backup"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe7dc157",
   "metadata": {},
   "source": [
    "Let's check:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7c96c685",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT * FROM artists_backup"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:sqlcourse]",
   "language": "python",
   "name": "conda-env-sqlcourse-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}