SQL - Create and populate databases (DDL - DML).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "87a85ab9",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Create and manage objects in database"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db257a7f",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "![](images/sqladam.jpg)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c6834fa0",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Data Definition Language"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7376a60c",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Data Definition Language in SQL is a way to implement the physical data model of DBMS Data Modeling"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e53a0f7",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- The \"schema\" contains the tables names, field name, keys and all database objects;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd970e7f",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Schema it's stored in the database itself, typically using a relational model"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9141fb92",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Some DBMS allow different schemas to partition data in different silos, others, like SQLite, the database and schema are 1:1 connected (even if it's possibile to query from different db) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a4bd727a",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Table"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "62c7bd40",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## CREATE TABLE"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "169a72e9",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Creates a new table (relation) in a database, a unique of the table needs to be specified."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5d575dfc",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- A new table can be defined in two ways"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07e6aee9",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "    1. Defining the columns (attributes) and relative constraints;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "148655b4",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "    2. Running a sub query (result set are tables)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f8452e4",
   "metadata": {
    "cell_style": "center",
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Generic CREATE TABLE in SQLite \n",
    "![](images/create-table.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "075070bc",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Create TABLE (column definition)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b744778",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Table Name"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ce10767",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "It's the name of the relation, i.e. the entity in the conceptual data model or a association table to define a relationship\n",
    "\n",
    "**SQLite** : in sqlite names starting with sqlite_ are reserved)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f4cae89",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "```sql\n",
    "CREATE TABLE students\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e536325",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Table Schema"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2122e951",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "> Named group of related objects contained within a database or instance of a database server. A schema is a collection of tables, views, and permissions granted to specific users or roles.\n",
    "SQL in a nutshell, 4th Edition\n",
    "\n",
    "**SQLite** : Not required, if schema present should be:\n",
    "- main (the actual db)\n",
    "- temp (for temporany tables) \n",
    "- an attached database, see [documentation](https://www.sqlite.org/lang_attach.html))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1df24c82",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "```sql\n",
    "-- Create Table students in schema mastersds\n",
    "CREATE TABLE mastersds.students\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cbb42282",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Column definition"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f0def6d",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "List of attributes separated by comma with data type and constraints in the standard form\n",
    "```sql \n",
    "column_name datatype attributes\n",
    "```\n",
    "\n",
    "**SQLite** :\n",
    "- column_name is the mame of the attribute. It's the only parameter required (due to [flexible typing](https://www.sqlite.org/flextypegood.html)\n",
    "- datatype can be (integer, real, text, blob),  standard datatype (int, varchar) are automatically mapped \n",
    "- attributes are [constraints](https://www.sqlite.org/syntax/column-constraint.html) like primary/foreign key, nullable, ... (we will see in practice)\n",
    "- primary keys create a rowid, no need to use [autoincrement](https://www.sqlite.org/autoinc.html)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec1dac2f",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "```sql\n",
    "CREATE TABLE IF NOT EXISTS students(\n",
    "id   INTEGER PRIMARY KEY NOT NULL,\n",
    "name TEXT    NOT NULL,\n",
    "age  INTEGER \n",
    ");\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "325dee22",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Table constraints "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "30e4a54b",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "Constraints can also be added after column definition and can refer to multiple columns\n",
    "> Table constraints can express almost any restrictions that a column constraint can, but can additionally express restrictions that involve more than one column.\n",
    "[Ref](https://www.prisma.io/dataguide/mysql/column-and-table-constraints)\n",
    "\n",
    "**SQLite** : See [table constraints](https://www.sqlite.org/syntax/table-constraint.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "370ea716",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "```sql\n",
    "CREATE TABLE IF NOT EXISTS students(\n",
    "    id     INTEGER NOT NULL,\n",
    "    name   TEXT    NOT NULL,\n",
    "    age    INTEGER,\n",
    "    PRIMARY KEY(id)\n",
    ")\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a23e2fc",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Create TABLE as"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aaa2e537",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- \"CREATE TABLE ... AS SELECT\" creates and populates a database table based on the results of a SELECT statement"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f84ba9e2",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Table has the same number of columns as the SELECT statement"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "150d717c",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- The name of each column is the same as the name of the corresponding column in the result set "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3dfb589b",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Type of the column is automatically matched "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ffa12bda",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## DROP TABLE "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32df5af9",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- DROP is the inverse operation of CREATE, destroy a table referenced by its name and schema\n",
    "- It's an effective operation, depending on the DB vendor can be a non recoverable action (remember, backup it's your friend)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ef700d2",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Generic DROP TABLE in SQLite\n",
    "![](images/drop-table.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba8af891",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Practice\n",
    "\n",
    "[![Open In Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/github/nicshub/sdsdbms/blob/master/Practice_SQLite_From_Scratch.ipynb)\n",
    "\n",
    "[![Open In Jupyter](images/jupyter-badge.png)](Practice_SQLite_From_Scratch.ipynb)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14cbc3d5",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## ALTER TABLE "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34b06630",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "ALTER TABLE performs operations on tables, including:\n",
    "\n",
    "| Operation           | Syntax                    |\n",
    "|---------------------|--------                   |\n",
    "| Rename a table      |```RENAME TO```            |\n",
    "| Rename a column     |```RENAME COLUMN TO```     |\n",
    "| Add a column        |```ADD COLUMN col-def```   |\n",
    "| Drop a column       |```DROP COLUMN col-name``` |\n",
    "| Modify a column     | Not supported in SQLite   |\n",
    "| Add table constraint| Not supported in SQLite   |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d6ce1de",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Generic ALTER TABLE SQLite\n",
    "![](images/alter-table.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20422194",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Other DDL statements"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21bf852f",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- VIEWS (CREATE/DROP): Views are table generated \"on the fly\" by queries"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e8f6f19e",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- INDEX (CREATE/DROP): Indexes are data structures to improve the speed of data retrieval (increase storage and may slow DML operations)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "45ab4faa",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- TRIGGER (CREATE/DROP): Triggers are code (SQL / other languages) connected to database object events (i.e. BEFORE insert, AFTER delete). Used to implement business logic and some integrity check"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad043585",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- STORED PROCEDURE(CREATE/DROP): Stored Procedures are subroutine available to the Database that extend functionalities, they are called using CALL procedure_name. Used to implement complex transactions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cab82949",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Data Manipulation Language"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "542f2a99",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Data Manipulation Language (DML) is the sub language of SQL that creates/updates/deletes rows in the database tables"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51b961a2",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Operations can involve a single row, a subset or the entire table"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53c17925",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Constraints checks are applied and may raise error in case of violation"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9fa8aa3b",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## INSERT"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c986f13",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- INSERT add one or more rows in a table, both specifying selected columns or adding all in order\n",
    "- Two main forms:\n",
    "    1. adding values: ```INSERT into table VALUES...```\n",
    "    2. selecting from a query ```INSERT into table select...```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a1d90bf3",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### INSERT into table VALUES"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f761b287",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "#### Syntax\n",
    "```sql\n",
    "INSERT into table(col1, col2, ..., coln) VALUES (a1, ...,an),(b1, ..., bn)\n",
    "```\n",
    "\n",
    "the list of columns can be omitted, in that case all the column need to be specified"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "28079836",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Back to practice"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47a8ace5",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### INSERT into table SELECT"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6ca2adf8",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "#### Syntax\n",
    "```sql\n",
    "INSERT into table(col1, col2, ..., coln) SELECT col1, col2, ..., coln FROM ...\n",
    "```\n",
    "\n",
    "the list of columns can be omitted, in that case the select should return the same columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f654bf80",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## DELETE"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1cf2d8c5",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- DELETE deletes one or more rows in a table;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eb40c8d1",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- If WHERE condition is not specified, the whole table is deleted (in SQLite is truncate);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75014722",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- If WHERE condition is specified, only rows that verify the expression are deleted;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e811af63",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- Take care, without a transaction, recovering it may possible but not easy "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e400fd9e",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Syntax\n",
    "```sql\n",
    "DELETE FROM table WHERE expr\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6693e06a",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## UPDATE"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c7ecfd87",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- UPDATE updates one or more rows in a table;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c87dcdd",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- If WHERE condition is not specified, the whole table is deleted (in SQLite is truncate);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0dc65a01",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- If WHERE condition is specified, only rows that verify the expression are deleted;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1041c43b",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Syntax\n",
    "```sql\n",
    "UPDATE table SET col1=expr1,...,coln=exprn  WHERE expr\n",
    "```"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "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.9.15"
  },
  "rise": {
   "autolaunch": true,
   "enable_chalkboard": "true",
   "footer": "<div class=\"sds-footer\"> *** Social Data Science (SDS) - Notions of Database Management Systems ***</div>",
   "header": "<div class=\"sds-header\"><div class=\"sds-header-image\"></div></div>",
   "scroll": true,
   "theme": "white"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}