03-basic-sql.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7f38fc3f",
   "metadata": {},
   "source": [
    "* Initially developed at IBM by Donald D. Chamberlin and Raymond F. Boyce after learning about the relational model from Edgar F. Codd  \n",
    "* First appeared 1974  \n",
    "* Became ANSI Standard 1986 and ISO Standard 1987  \n",
    "* First available definition article is ANSI-SQL92. The latest is ANSI-SQL2023  \n",
    "* Declarative language  \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "857e8662",
   "metadata": {},
   "source": [
    "**[CRUD Operations](https://en.wikipedia.org/wiki/Create,_read,_update_and_delete)**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8cbff38e",
   "metadata": {},
   "source": [
    "## Using the _sakila_ Database"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83d78f13",
   "metadata": {},
   "source": [
    "#Check existing databases  \n",
    "**`mysql> SHOW DATABASES;`**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e309d718",
   "metadata": {},
   "source": [
    "#Change context  \n",
    "**`mysql> USE sakila;`**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32c89f8c",
   "metadata": {},
   "source": [
    "#Check the current active  \n",
    "**`mysql> SELECT DATABASE();`**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d982a801",
   "metadata": {},
   "source": [
    "#Explore existing tables in the active database  \n",
    "**`mysql> SHOW TABLES;`**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "791e5d1e",
   "metadata": {},
   "source": [
    "#Show table schema  \n",
    "**`mysql> SHOW COLUMNS FROM actor;`**  \n",
    "**`mysql> DESCRIBE actor;`**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8baf79a9",
   "metadata": {},
   "source": [
    "## Data Manipulation Language (DML)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "344fd5e3",
   "metadata": {},
   "source": [
    "### SELECT Statement  \n",
    "[SELECT Doc](https://dev.mysql.com/doc/refman/8.0/en/select.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b07c3f51",
   "metadata": {},
   "source": [
    "`mysql> SELECT 'Hello World!';`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4cb27330",
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT\n",
    "    [ALL | DISTINCT | DISTINCTROW ]\n",
    "    [HIGH_PRIORITY]\n",
    "    [STRAIGHT_JOIN]\n",
    "    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]\n",
    "    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]\n",
    "    select_expr [, select_expr] ...\n",
    "    [into_option]\n",
    "    [FROM table_references\n",
    "      [PARTITION partition_list]]\n",
    "    [WHERE where_condition]\n",
    "    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]\n",
    "    [HAVING where_condition]\n",
    "    [WINDOW window_name AS (window_spec)\n",
    "        [, window_name AS (window_spec)] ...]\n",
    "    [ORDER BY {col_name | expr | position}\n",
    "      [ASC | DESC], ... [WITH ROLLUP]]\n",
    "    [LIMIT {[offset,] row_count | row_count OFFSET offset}]\n",
    "    [into_option]\n",
    "    [FOR {UPDATE | SHARE}\n",
    "        [OF tbl_name [, tbl_name] ...]\n",
    "        [NOWAIT | SKIP LOCKED]\n",
    "      | LOCK IN SHARE MODE]\n",
    "    [into_option]\n",
    "\n",
    "into_option: {\n",
    "    INTO OUTFILE 'file_name'\n",
    "        [CHARACTER SET charset_name]\n",
    "        export_options\n",
    "  | INTO DUMPFILE 'file_name'\n",
    "  | INTO var_name [, var_name] ...\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4bfcfab9",
   "metadata": {},
   "source": [
    "### WHERE Clause"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "088cb176",
   "metadata": {},
   "source": [
    "`mysql> SELECT * FROM sakila.language WHERE name = 'English';`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b828c32e",
   "metadata": {},
   "source": [
    "### ORDER BY Clause"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57a85bc5",
   "metadata": {},
   "source": [
    "`mysql> SELECT name FROM customer_list`  \n",
    "    `-> ORDER BY name`  \n",
    "    `-> LIMIT 10;` "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "542b9c87",
   "metadata": {},
   "source": [
    "`mysql> SELECT address, district FROM address`  \n",
    "    `-> ORDER BY district, address;`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ec3b579",
   "metadata": {},
   "source": [
    "`mysql> SELECT id, name FROM customer_list`  \n",
    "    `-> ORDER BY id LIMIT 10 OFFSET 5;`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2fd53a29",
   "metadata": {},
   "source": [
    "## Joining Two Tables"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc2128eb",
   "metadata": {},
   "source": [
    "`LEFT JOIN, RIGHT JOIN,, INNER JOIN`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07b23e12",
   "metadata": {},
   "source": [
    "`mysql> SELECT city, country FROM city INNER JOIN country`  \n",
    "    `-> ON city.country_id = country.country_id`  \n",
    "    `-> WHERE country.country_id < 5`  \n",
    "    `-> ORDER BY country, city;`  \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e63e4876",
   "metadata": {},
   "source": [
    "#Equals"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5e34cb6b",
   "metadata": {},
   "source": [
    "`mysql> SELECT city, country FROM city INNER JOIN country`  \n",
    "    `-> using (country_id)`**  \n",
    "    `-> WHERE country.country_id < 5`  \n",
    "    `-> ORDER BY country, city;`  \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "85330906",
   "metadata": {},
   "source": [
    "![Joins](./images/join-types.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48776640",
   "metadata": {},
   "source": [
    "## The INSERT Statement"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "19f4914c",
   "metadata": {},
   "source": [
    "`mysql> SHOW COLUMNS FROM language;`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aad2cabf",
   "metadata": {},
   "source": [
    "`mysql> INSERT INTO language VALUES (NULL, 'Portuguese', NOW());`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07c7f06a",
   "metadata": {},
   "source": [
    "`mysql> INSERT INTO language VALUES (8, 'Russian', '2020-09-26 10:35:00');`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a3b7ef04",
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]\n",
    "    [INTO] tbl_name\n",
    "    [PARTITION (partition_name [, partition_name] ...)]\n",
    "    [(col_name [, col_name] ...)]\n",
    "    { {VALUES | VALUE} (value_list) [, (value_list)] ... }\n",
    "    [AS row_alias[(col_alias [, col_alias] ...)]]\n",
    "    [ON DUPLICATE KEY UPDATE assignment_list]\n",
    "\n",
    "INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]\n",
    "    [INTO] tbl_name\n",
    "    [PARTITION (partition_name [, partition_name] ...)]\n",
    "    SET assignment_list\n",
    "    [AS row_alias[(col_alias [, col_alias] ...)]]\n",
    "    [ON DUPLICATE KEY UPDATE assignment_list]\n",
    "\n",
    "INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]\n",
    "    [INTO] tbl_name\n",
    "    [PARTITION (partition_name [, partition_name] ...)]\n",
    "    [(col_name [, col_name] ...)]\n",
    "    { SELECT ... \n",
    "      | TABLE table_name \n",
    "      | VALUES row_constructor_list\n",
    "    }\n",
    "    [ON DUPLICATE KEY UPDATE assignment_list]\n",
    "\n",
    "value:\n",
    "    {expr | DEFAULT}\n",
    "\n",
    "value_list:\n",
    "    value [, value] ...\n",
    "\n",
    "row_constructor_list:\n",
    "    ROW(value_list)[, ROW(value_list)][, ...]\n",
    "\n",
    "assignment:\n",
    "    col_name = \n",
    "          value\n",
    "        | [row_alias.]col_name\n",
    "        | [tbl_name.]col_name\n",
    "        | [row_alias.]col_alias\n",
    "\n",
    "assignment_list:\n",
    "    assignment [, assignment] ..."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a9dd810c",
   "metadata": {},
   "source": [
    "## DELETE"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57a659a4",
   "metadata": {},
   "source": [
    "`mysql> DELETE FROM rental;`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2cae010d",
   "metadata": {},
   "source": [
    "`mysql> TRUNCATE TABLE payment;`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "777134c5",
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]\n",
    "    [PARTITION (partition_name [, partition_name] ...)]\n",
    "    [WHERE where_condition]\n",
    "    [ORDER BY ...]\n",
    "    [LIMIT row_count]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a865776",
   "metadata": {},
   "source": [
    "## UPDATE"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf1d854b",
   "metadata": {},
   "source": [
    "`mysql> UPDATE payment SET amount=amount*1.1;`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5132bcfc",
   "metadata": {},
   "source": [
    "`mysql> UPDATE payment SET last_update='2021-02-28 17:53:00';`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a32dfc85",
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "UPDATE [LOW_PRIORITY] [IGNORE] table_reference\n",
    "    SET assignment_list\n",
    "    [WHERE where_condition]\n",
    "    [ORDER BY ...]\n",
    "    [LIMIT row_count]\n",
    "\n",
    "value:\n",
    "    {expr | DEFAULT}\n",
    "\n",
    "assignment:\n",
    "    col_name = value\n",
    "\n",
    "assignment_list:\n",
    "    assignment [, assignment] ..."
   ]
  }
 ],
 "metadata": {
  "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.10.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}