{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "22482673",
   "metadata": {},
   "outputs": [],
   "source": [
    "import agh_db_lectures\n",
    "agh_db_lectures.prepare_notebook_for_sql()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83f4fffc",
   "metadata": {},
   "source": [
    "# The \"NULL\" value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8ec0bb1a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Open the diagram in an image viewer for more convenience.\n",
    "agh_db_lectures.nw_diagram.download_open()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d4c3ba6c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect to the (locally-hosted) database.\n",
    "%sql postgresql://demo_user:demo_pwd@localhost:25432/agh_it_northwind"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b594cc93",
   "metadata": {},
   "outputs": [],
   "source": [
    "agh_db_lectures.download_restore_nw_postgres_dump()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fc14542d",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT company_name, phone, fax\n",
    "FROM customers"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e1266e1",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "- Theodore, what would an employee do if an customer to record in the db had no phone number?\n",
    "  - Give many ideas.\n",
    "  - What would be their pros and cons be?\n",
    "- `SELECT company_name, phone, fax`\n",
    "- Jupyter (well, Python) happens to have `NULL` values converted to `None`s and presented to us this way.\n",
    "- Do not work:\n",
    "  - `WHERE fax = NULL`, and\n",
    "  - `WHERE fax <> NULL`.\n",
    "- Do work\n",
    "  - `WHERE fax IS NULL`, and\n",
    "  - `WHERE fax IS NOT NULL`.\n",
    "  - Also, `WHERE fax <> '011-4988261' OR fax = '011-4988261'` won't return all table rows."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad4700a9",
   "metadata": {},
   "source": [
    "## \"NULL\" in arithmetic and comparison operations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c08e4ed5",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT NULL <> 'unknown fax number'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b2619658",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "- Try\n",
    "  - `SELECT NULL`,\n",
    "  - `SELECT NULL > 5`,\n",
    "  - `SELECT NULL <> 'unknown fax number'`,\n",
    "  - `SELECT NULL = NULL`, and\n",
    "  - `SELECT NULL IN (TRUE, FALSE, NULL)`,\n",
    "    - but note that a test for presence of `NULL` (or any other value) in an empty tuple (e.g., `SELECT NULL IN (SELECT 'whatever' LIMIT 0)`) would still give `FALSE`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0613f611",
   "metadata": {},
   "source": [
    "## \"NULL\" with logical operators"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3cf39c08",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT NOT NULL"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "66b696f3",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "- `TRUE OR NULL`\n",
    "- `FALSE AND NULL`\n",
    "- `NOT`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "502f27dc",
   "metadata": {},
   "source": [
    "## The \"COALESCE\" function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5af26eab",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT company_name,\n",
    "       CASE\n",
    "           WHEN fax IS NULL THEN\n",
    "               'unknown'\n",
    "           ELSE\n",
    "               fax\n",
    "       END\n",
    "FROM customers"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff370664",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "- Replace the `CASE` expression with `COALESCE(fax, 'unknown') AS fax`."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "/gnu/store/q35bxk1i5blx0rcgxphhrq9xqmgha9bz-python-3.11.11/bin/python3",
   "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.11.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}