path: root/09-views-and-temporary-tables/views-and-temporary-tables.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4a890bcf",
   "metadata": {},
   "outputs": [],
   "source": [
    "import agh_db_lectures\n",
    "agh_db_lectures.prepare_notebook_for_sql()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3d18d1d0",
   "metadata": {},
   "source": [
    "# Views and Temporary Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2ec8a264",
   "metadata": {},
   "outputs": [],
   "source": [
    "agh_db_lectures.nw_diagram.download_open()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "856e8c73",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql postgresql://demo_user:demo_pwd@localhost:25432/agh_it_northwind"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8e2cb93d",
   "metadata": {},
   "outputs": [],
   "source": [
    "agh_db_lectures.download_restore_nw_postgres_dump()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e7e450b",
   "metadata": {},
   "source": [
    "## Simple Views"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1513ab4b",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT c.customer_id,\n",
    "       c.company_name,\n",
    "       CAST (\n",
    "           COALESCE(SUM(od.unit_price * od.quantity - od.discount),\n",
    "                    0)\n",
    "           AS DECIMAL(15, 2)\n",
    "       ) AS turnover\n",
    "FROM           customers c\n",
    "     LEFT JOIN orders o         USING (customer_id)\n",
    "     LEFT JOIN order_details od USING (order_id)\n",
    "GROUP BY c.customer_id, c.company_name;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "72f0c8f2",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "A data query that is to be used multiple times can be remembered as a **view**.  Prepend the following.\n",
    "\n",
    "```sql\n",
    "CREATE OR VIEW customers_turnover AS\n",
    "```\n",
    "\n",
    "The `customers_turnover` view has been created.  It can be queried as if it were a table.  Execute the following.\n",
    "\n",
    "```sql\n",
    "SELECT * FROM customers_turnover\n",
    "```\n",
    "\n",
    "We can use `CREATE VIEW` and `DROP VIEW` commands with `IF EXISTS` and `IF NOT EXISTS`, as with tables.  We can also use `CASCADE` with `DROP VIEW`.  Replace the beginning with the following.\n",
    "\n",
    "```sql\n",
    "DROP VIEW IF EXISTS customers_turnover;\n",
    "CREATE VIEW customers_turnover AS\n",
    "```\n",
    "\n",
    "We can specify view column names instead of having the derived from the `SELECT` clause.  Use the following.\n",
    "\n",
    "```sql\n",
    "CREATE VIEW customers_turnover (customer_id, customer_name, turnover) AS\n",
    "```\n",
    "\n",
    "A view is just like a named query.  We shall see up-to-date data in the view even after we modify the original, for example with the following.\n",
    "\n",
    "```sql\n",
    "UPDATE order_details SET discount = discount + 1;\n",
    "```\n",
    "\n",
    "It also means that a view **is not** an optimization tool.  The queries are re-executed each time.  Some RDBMSes support **nonstandard materialized views** that behave the other way and remember their data.\n",
    "\n",
    "Views are useful for\n",
    "\n",
    "- naming queries that are to be reused, and\n",
    "- granting limited access to data in the db (e.g., with permission only to read a view that omits some sensitive data from the original table)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d86a520",
   "metadata": {},
   "source": [
    "## Updates on Views"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a860d91",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "UPDATE customers_turnover SET turnover = 1 WHERE customer_id LIKE 'K%'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "94854f5f",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "What should the above command do?  Does it make sense?\n",
    "\n",
    "What if the command were to alter a field other than turnover?  E.g., `customer_name`?  Theodore, which customer name shall we try modifying and how?\n",
    "\n",
    "Try the following.\n",
    "\n",
    "```sql\n",
    "UPDATE customers_turnover SET customer_name = 'Koeniglich Essen'\n",
    "WHERE customer_id = 'KOENE'\n",
    "```\n",
    "\n",
    "What does the error say?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d5f43ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "DROP VIEW IF EXISTS customers_turnover;\n",
    "CREATE VIEW customers_turnover(customer_id, customer_name, turnover) AS\n",
    "    SELECT c.customer_id,\n",
    "           c.company_name,\n",
    "           (SELECT CAST (\n",
    "                       COALESCE(SUM(od.unit_price * od.quantity - od.discount),\n",
    "                                0)\n",
    "                       AS DECIMAL(15, 2)\n",
    "                   )\n",
    "            FROM orders o JOIN order_details od USING (order_id)\n",
    "            WHERE customer_id = c.customer_id)\n",
    "    FROM customers c;\n",
    "\n",
    "SELECT * FROM customers_turnover ORDER BY customer_id"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dd0e8ac6",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "This view does (hopefully) the same thing as the previous one.  But it does not use `JOIN`s nor aggregate functions in the top-level `SELECT`.  We can retry updating a customer name."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dd7cd695",
   "metadata": {},
   "source": [
    "A (Postgres) view is **automatically updatable** if\n",
    "\n",
    "- it has a single table (or updatable view) in the `FROM` clause,\n",
    "- uses neither of\n",
    "  - Common Table Expressions (`WITH`),\n",
    "  - `DISTINCT`, `LIMIT`, and `OFFSET`,\n",
    "  - set operations (`UNION`, `INTERSECT`, and `EXCEPT`), and\n",
    "  - aggregations (`GROUP BY` clause, functions like `SUM`…).\n",
    "\n",
    "https://www.postgresql.org/docs/18/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5ba659a4",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "We still cannot assign values to computed columns (like `turnover`).\n",
    "\n",
    "Triggers (to be covered at a later time) offer an alternative way to make arbitrary views updatable."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fd07e4bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "INSERT INTO customers_turnover (customer_id, customer_name)\n",
    "VALUES ('THVB', 'Theodore''s Vegabs')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "26c15862",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "`INSERT` and `DELETE` are also possible when existing constraints allow.  Use the following to see that `NULL` values were used for most fields of the inserted `customers` row.\n",
    "\n",
    "```sql\n",
    "SELECT * FROM customers where customer_id = 'THVB'\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2c60ae9d",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "DELETE FROM customers where customer_id = 'THVB';\n",
    "ALTER TABLE customers ALTER COLUMN phone SET NOT NULL "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a5002e07",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "Existig constraints must of course be met by data modifications performed through views.  If we add a `NOT NULL` constraint on a column not used by the view, our `INSERT` command shall fail even though the view is considered updatable."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9b6318ec",
   "metadata": {},
   "source": [
    "### View Check Options"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a826b074",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "DROP VIEW IF EXISTS shipped_orders;\n",
    "CREATE VIEW shipped_orders AS\n",
    "    SELECT * FROM orders\n",
    "    WHERE shipped_date IS NOT NULL;\n",
    "\n",
    "SELECT * FROM shipped_orders\n",
    "ORDER BY order_id DESC\n",
    "LIMIT 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "929e02f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "DROP VIEW IF EXISTS shipped_orders_values;\n",
    "CREATE VIEW shipped_orders_values AS\n",
    "    SELECT (SELECT CAST (\n",
    "                       SUM(unit_price * quantity - discount)\n",
    "                       AS DECIMAL(15, 2)\n",
    "                   )\n",
    "            FROM order_details\n",
    "            WHERE order_id = so.order_id) AS value,\n",
    "           *\n",
    "    FROM shipped_orders so\n",
    "    WHERE order_id IN (SELECT order_id FROM order_details);\n",
    "\n",
    "SELECT * FROM shipped_orders_values\n",
    "ORDER BY order_id DESC\n",
    "LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b49e61c6",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "This updateble view uses another updatable views as a base."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3bea40df",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT order_id,\n",
    "       customer_id,\n",
    "       employee_id,\n",
    "       order_date,\n",
    "       required_date,\n",
    "       shipped_date,\n",
    "       ship_via,\n",
    "       freight,\n",
    "       ship_name,\n",
    "       ship_address,\n",
    "       ship_city,\n",
    "       ship_region,\n",
    "       ship_postal_code,\n",
    "       ship_country\n",
    "FROM shipped_orders_values\n",
    "ORDER BY order_id DESC\n",
    "LIMIT 1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27ad7824",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "The selected row is the last order's row.  Note that `shipped_date` is `NULL`.  The row is not present in the view.\n",
    "\n",
    "Now, add `+ 100` after `order_id`, `order_date`, `require_date`, and `shipped_date` in the `SELECT` clause.  We get a modified clone of this row.  It is possible to insert such row into `shipped_orders_values` by  prepending the following.\n",
    "\n",
    "```sql\n",
    "INSERT INTO shipped_orders_values (\n",
    "    order_id,\n",
    "    customer_id,\n",
    "    employee_id,\n",
    "    order_date,\n",
    "    required_date,\n",
    "    shipped_date,\n",
    "    ship_via,\n",
    "    freight,\n",
    "    ship_name,\n",
    "    ship_address,\n",
    "    ship_city,\n",
    "    ship_region,\n",
    "    ship_postal_code,\n",
    "    ship_country\n",
    ")\n",
    "```\n",
    "\n",
    "The statement succeeds, but the new row is not visible in the view's result.  Such behaviors are counterintuitive, especially when a database user fails notice that the relation being modified is a view rather than a table.\n",
    "\n",
    "We can append the following to the view definition statement to forbid addition of rows that do not meet updatable view's condition.\n",
    "\n",
    "```sql\n",
    "WITH LOCAL CHECK OPTION\n",
    "```\n",
    "\n",
    "Afterwards, such `INSERT` commands fail."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "37476fdb",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "UPDATE shipped_orders_values\n",
    "SET shipped_date = NULL\n",
    "WHERE order_id = X"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc6cf0d1",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "Theodore, please pick `X` as an id of some order seen in `shipped_orders_values`.\n",
    "\n",
    "The update caused the row in `orders` to be updated and made it disappear from `shipped_orders_values`.\n",
    "\n",
    "Our `CHECK OPTION` did not prevent this operation, because it is concerned with the `WHERE` condition of the upper view.  It is the base view where not-yet-shipped orders are excluded.\n",
    "\n",
    "There two primary ways we can guard against creating rows that fail to meet the conditions of base views.\n",
    "\n",
    "1. Use `WITH LOCAL CHECK OPTION` on all base views in the hierarchy.\n",
    "2. Use `WITH CASCADE CHECK OPTION` on the top view."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4317a1d0",
   "metadata": {},
   "source": [
    "### Altering of Base Relations Structure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "59d22e15",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE OR REPLACE VIEW shipped_orders AS\n",
    "    SELECT *\n",
    "    FROM orders\n",
    "    WHERE shipped_date IS NOT NULL;\n",
    "    \n",
    "SELECT * FROM shipped_orders\n",
    "ORDER BY order_id DESC\n",
    "LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6295011",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "We can add extra columns (and change view conditions) with `CREATE OR REPLACE VIEW`.  Here, we add a column named `shipment_days` selected as follows.\n",
    "\n",
    "```sql\n",
    "           shipped_date - order_date AS shipment_days\n",
    "```\n",
    "\n",
    "Note that views only reflect changes to underlying relations **data**, and **not their structure**.  Until we re-create `shipped_orders_values`, the added column is not present in it, as can be verified with the following.\n",
    "\n",
    "```sql\n",
    "SELECT * FROM shipped_orders_values\n",
    "ORDER BY order_id DESC\n",
    "LIMIT 5\n",
    "```\n",
    "\n",
    "We also cannot use `CREATE OR REPLACE` to change the type of existing view's columns.  For example, if we use the following two lines in our view redefinition, we get an error.\n",
    "\n",
    "```sql\n",
    "    SELECT *,\n",
    "           CAST (shipped_date AS TIMESTAMP) - order_date AS shipment_days\n",
    "```\n",
    "\n",
    "Similarly, we cannot drop a column this way.  The order, naming and types of columns must match for view's `REPLACE` operation to succeed.\n",
    "\n",
    "The following also fails due to the dependency of `shipped_orders_values` on this view.\n",
    "\n",
    "```sql\n",
    "DROP VIEW IF EXISTS shipped_orders;\n",
    "```\n",
    "It works (and drops both views) if we append `CASCADE` to the `DROP` command."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d6921ba",
   "metadata": {},
   "source": [
    "## The \"CREATE TABLE AS\" command"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "66391235",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT * FROM customers_turnover WHERE turnover > 30_000"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aee5b177",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "We can wrap our query with the following to make a table from this query's result.\n",
    "\n",
    "```sql\n",
    "CREATE TABLE top_customers AS\n",
    "-- the query\n",
    "WITH DATA\n",
    "```\n",
    "\n",
    "There also exists a `WITH NO DATA` variant and Postgres even makes the `WITH [NO] DATA` suffix optional.\n",
    "\n",
    "Postgres also supports a `SELECT INTO` command that serves a similar purpose but diverges further from the SQL standard.\n",
    "\n",
    "```sql\n",
    "SELECT *\n",
    "INTO TABLE top_customers\n",
    "FROM customers_turnover\n",
    "WHERE turnover > 30_000\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8c9fc700",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT * FROM top_customers"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd17b601",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "We can work with this table and alter it.  It is a real table, just as those created with the usual `CREATE TABLE`.  We can add columns or constraints.\n",
    "\n",
    "```sql\n",
    "ALTER TABLE top_customers\n",
    "ADD PRIMARY KEY (customer_id)\n",
    "```\n",
    "\n",
    "We can modify data.\n",
    "\n",
    "```sql\n",
    "UPDATE top_customers\n",
    "SET turnover = ROUND(turnover);\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8f71efef",
   "metadata": {},
   "source": [
    "## Temporary tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8e65aff9",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- CREATE TEMPORARY TABLE most_frequent_customers AS\n",
    "SELECT customer_id, company_name, COUNT(order_id) AS order_count\n",
    "FROM customers LEFT JOIN orders USING(customer_id)\n",
    "GROUP BY customer_id, company_name\n",
    "ORDER BY COUNT(order_id) DESC\n",
    "LIMIT 10"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5fcc6739",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "We can add a keyword `TEMPORARY` to create a table that would only exist until the end of our database session (or transaction).\n",
    "\n",
    "A shorthand `TEMP` can be used instead.\n",
    "\n",
    "```sql\n",
    "DROP TABLE IF EXISTS most_frequent_customers;\n",
    "CREATE TEMP TABLE most_frequent_customers AS\n",
    "```\n",
    "\n",
    "Also note that Postgres allowed us to omit `WITH DATA`, even though the standard requires it.\n",
    "\n",
    "Btw, normal table creation, like `CREATE TABLE (n INT)`, also accepts the `TEMP[ORARY]` keyword."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a2b247c",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT * FROM most_frequent_customers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4ad4ce8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql postgresql://demo_user:demo_pwd@localhost:25432/agh_it_northwind"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b1545be",
   "metadata": {},
   "source": [
    "_notes_\n",
    "\n",
    "When we re-connect, we start a new SQL session.  Our earlier temporary table is gone (the last `SELECT` now gives an error), but the non-temporary `top_customers` table is still there.\n",
    "\n",
    "The SQL standard instead specifies that temporary tables are retained across sessions but without data.  Multiple RDBMSes diverge from this specification in the same way Postgres does."
   ]
  }
 ],
 "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
}