04_Workshop_Feb_19_2024.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/ua-datalab/DataEngineering/blob/main/04_Workshop_Feb_19_2024.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# **Workshop 4: Introduction to SQL and duckDB Part 2 -**\n",
        "\n",
        "# Agenda:\n",
        "- **SQL Recap and Functions, Sub-queries**\n",
        "- **Creating Tables and Inserting Data**\n",
        "- **Exporting data to CSV**\n",
        "- **Importing data from CSV to duckdb**"
      ],
      "metadata": {
        "id": "gVveuG2hP7Xn"
      },
      "id": "gVveuG2hP7Xn"
    },
    {
      "cell_type": "code",
      "source": [
        "import duckdb"
      ],
      "metadata": {
        "id": "esUqpB0zP5qI"
      },
      "id": "esUqpB0zP5qI",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "id": "5d87bc22-901f-4d69-bde6-a0fa9a86f13c",
      "metadata": {
        "id": "5d87bc22-901f-4d69-bde6-a0fa9a86f13c"
      },
      "source": [
        "# Working with an available dataset (Covid19-patients 100k synthetic dataset)\n",
        "## Dataset Link [Link](https://mitre.box.com/shared/static/wk3560f962ozlg7sd2oj1zxk73ayqvm0.zip)"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Let's download the database file"
      ],
      "metadata": {
        "id": "WO6F0xlskxQc"
      },
      "id": "WO6F0xlskxQc"
    },
    {
      "cell_type": "code",
      "source": [
        "!wget --content-disposition https://arizona.box.com/shared/static/uozg0z86rtdjupwpc7i971xwzuzhp42o.duckdb"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "YRmtiIivkwjc",
        "outputId": "bee86677-ffa7-443b-ff4d-f4e11485a4f7"
      },
      "id": "YRmtiIivkwjc",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "--2024-02-19 00:07:51--  https://arizona.box.com/shared/static/uozg0z86rtdjupwpc7i971xwzuzhp42o.duckdb\n",
            "Resolving arizona.box.com (arizona.box.com)... 74.112.186.144\n",
            "Connecting to arizona.box.com (arizona.box.com)|74.112.186.144|:443... connected.\n",
            "HTTP request sent, awaiting response... 301 Moved Permanently\n",
            "Location: /public/static/uozg0z86rtdjupwpc7i971xwzuzhp42o.duckdb [following]\n",
            "--2024-02-19 00:07:51--  https://arizona.box.com/public/static/uozg0z86rtdjupwpc7i971xwzuzhp42o.duckdb\n",
            "Reusing existing connection to arizona.box.com:443.\n",
            "HTTP request sent, awaiting response... 301 Moved Permanently\n",
            "Location: https://arizona.app.box.com/public/static/uozg0z86rtdjupwpc7i971xwzuzhp42o.duckdb [following]\n",
            "--2024-02-19 00:07:51--  https://arizona.app.box.com/public/static/uozg0z86rtdjupwpc7i971xwzuzhp42o.duckdb\n",
            "Resolving arizona.app.box.com (arizona.app.box.com)... 74.112.186.144\n",
            "Connecting to arizona.app.box.com (arizona.app.box.com)|74.112.186.144|:443... connected.\n",
            "HTTP request sent, awaiting response... 302 Found\n",
            "Location: https://public.boxcloud.com/d/1/b1!bsiUPxL7hmATNKcUNDHrJbqIs61NoBjYB5x-o3vtyas3Zzv6AtJDnk001KnoSWRvVzQArYIBQNxP32VLN1NOj0dsUZK92Tc3d5Ymzd6bt_n5HF2gJPdJ65ULQ7imrA0epK2exNTFNGFLqji5Q_9v6NMaUkMbVdkEpup7FSr5d7p5sGLq_AD-J0RKeAHr2ktLwRSEjIyNOiWM54b1rrQZL2BCJ1tR2oSODHFNBfLUrnmbEZci79ZHy-Rz1JpuBnTtps494cQ7MU4Qi-LLmj-M1PrrMvV2IDGu0AxWDfn3za8cRcwvImyJkWy6vWz_iYx41_mW07cOmrzmzCFwdP_s8ioJbffSFWKVX-TarQzVWcZwDPbqA7uiI26CTzhGxI6TSMCKp0_GBfSXZ84dBaWkiB60ko8KCyTguBjH3a7kKX9Jr9z2CJ9ZRHK4opfJi4CwlDGYNKxI-J4tvxU1-EIAhIxcPKlAzq7UeNopvMdDCof4OYDSoCzDJxwHLSW-A1O7T7GDnoETDzoqspB1a0-GPXsQ9MP0un63RaYGkssulYKpGUpoblPGGixvO6Y4De0Lcm9eHsrP8NQLxpEGhYo4OepZV5kH-0BM0nzOAggxxMzs1HgepCITQKBCEppS3lsx7ThSbghMV39GSoO2-jLMq1-XUBWIkyPRHCHFSNpzUiWQNWWFZeIoC_loKGNgephs_UhbX_79c9fS9m6GKkjLAuLcqNkBxW3CqYfjDmrCy-71m48czMJbp3WM2zZ19QdERVGHVnummr83-K9gFDERQ84CDVt1GKVcEh2xz9OgpbfHbm2X58NgcEboAUFx6H5EG9ES91Nn2jUaCRaKxxy56I-JVriFycM3v7kRzvEX09WfA4pmgM3QqhicOd8KfLnY8iQSrECaDO8kOQar7ps9J7Ou7R5FUdeqMEFVBVfyq9jbYFYNTm-lqN1DrJMJfJjTu5oEfPTj1CzWwlToO2GcSlamS_F2ZZSCkWcEXaC59wAmdP8RjP4GYW48SpWctNvMQIASjsY5ktz-HWc5z21zdpCLpw55jVSIaUeiC-5Km-coad-gPrZLuUkRlsCS4BOEboygfTdv93tDttZiaqrWx3U7aH71t_5yFWeBss8XTqNog_72ED3wM6iNvSdTuBGiawpK6Rb5WeuTTBeYw5WcCjMD0xb9pQ4fWWfd2nRkEIYBC-WzQk39QR03_pYYYKHsqPd8StGhY59t_C8AbX4VoBL0_kDOsf1sk104_E4UIveeKbr29opE20XOvLvtKO7enJlqqYsU9UfSQid7hdHtBiLOFchXDgTqlCl8_bUHe5QACXBfcHiXVtyHm2XYZR-WKUpLijxKtad33vsz31FYtUbk4p4zyUPOOCgfJP7zF9jGaurlMSh54NjldHDPFPaL/download [following]\n",
            "--2024-02-19 00:07:51--  https://public.boxcloud.com/d/1/b1!bsiUPxL7hmATNKcUNDHrJbqIs61NoBjYB5x-o3vtyas3Zzv6AtJDnk001KnoSWRvVzQArYIBQNxP32VLN1NOj0dsUZK92Tc3d5Ymzd6bt_n5HF2gJPdJ65ULQ7imrA0epK2exNTFNGFLqji5Q_9v6NMaUkMbVdkEpup7FSr5d7p5sGLq_AD-J0RKeAHr2ktLwRSEjIyNOiWM54b1rrQZL2BCJ1tR2oSODHFNBfLUrnmbEZci79ZHy-Rz1JpuBnTtps494cQ7MU4Qi-LLmj-M1PrrMvV2IDGu0AxWDfn3za8cRcwvImyJkWy6vWz_iYx41_mW07cOmrzmzCFwdP_s8ioJbffSFWKVX-TarQzVWcZwDPbqA7uiI26CTzhGxI6TSMCKp0_GBfSXZ84dBaWkiB60ko8KCyTguBjH3a7kKX9Jr9z2CJ9ZRHK4opfJi4CwlDGYNKxI-J4tvxU1-EIAhIxcPKlAzq7UeNopvMdDCof4OYDSoCzDJxwHLSW-A1O7T7GDnoETDzoqspB1a0-GPXsQ9MP0un63RaYGkssulYKpGUpoblPGGixvO6Y4De0Lcm9eHsrP8NQLxpEGhYo4OepZV5kH-0BM0nzOAggxxMzs1HgepCITQKBCEppS3lsx7ThSbghMV39GSoO2-jLMq1-XUBWIkyPRHCHFSNpzUiWQNWWFZeIoC_loKGNgephs_UhbX_79c9fS9m6GKkjLAuLcqNkBxW3CqYfjDmrCy-71m48czMJbp3WM2zZ19QdERVGHVnummr83-K9gFDERQ84CDVt1GKVcEh2xz9OgpbfHbm2X58NgcEboAUFx6H5EG9ES91Nn2jUaCRaKxxy56I-JVriFycM3v7kRzvEX09WfA4pmgM3QqhicOd8KfLnY8iQSrECaDO8kOQar7ps9J7Ou7R5FUdeqMEFVBVfyq9jbYFYNTm-lqN1DrJMJfJjTu5oEfPTj1CzWwlToO2GcSlamS_F2ZZSCkWcEXaC59wAmdP8RjP4GYW48SpWctNvMQIASjsY5ktz-HWc5z21zdpCLpw55jVSIaUeiC-5Km-coad-gPrZLuUkRlsCS4BOEboygfTdv93tDttZiaqrWx3U7aH71t_5yFWeBss8XTqNog_72ED3wM6iNvSdTuBGiawpK6Rb5WeuTTBeYw5WcCjMD0xb9pQ4fWWfd2nRkEIYBC-WzQk39QR03_pYYYKHsqPd8StGhY59t_C8AbX4VoBL0_kDOsf1sk104_E4UIveeKbr29opE20XOvLvtKO7enJlqqYsU9UfSQid7hdHtBiLOFchXDgTqlCl8_bUHe5QACXBfcHiXVtyHm2XYZR-WKUpLijxKtad33vsz31FYtUbk4p4zyUPOOCgfJP7zF9jGaurlMSh54NjldHDPFPaL/download\n",
            "Resolving public.boxcloud.com (public.boxcloud.com)... 74.112.186.128\n",
            "Connecting to public.boxcloud.com (public.boxcloud.com)|74.112.186.128|:443... connected.\n",
            "HTTP request sent, awaiting response... 200 OK\n",
            "Length: 770453504 (735M) [application/octet-stream]\n",
            "Saving to: ‘my_database.duckdb’\n",
            "\n",
            "my_database.duckdb  100%[===================>] 734.76M  64.3MB/s    in 11s     \n",
            "\n",
            "2024-02-19 00:08:03 (66.8 MB/s) - ‘my_database.duckdb’ saved [770453504/770453504]\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "ea032f0d-53a1-4885-875d-b8b2ca73a86a",
      "metadata": {
        "tags": [],
        "id": "ea032f0d-53a1-4885-875d-b8b2ca73a86a"
      },
      "outputs": [],
      "source": [
        "conn = duckdb.connect(database='/content/my_database.duckdb', read_only=True)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "24ee20a7-bf91-452c-aa4a-e0010d83cbf2",
      "metadata": {
        "id": "24ee20a7-bf91-452c-aa4a-e0010d83cbf2"
      },
      "source": [
        "## Viewing what tables are available inside the database"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "ddcaffbd-d845-43a8-8572-12d24c4685e4",
      "metadata": {
        "tags": [],
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ddcaffbd-d845-43a8-8572-12d24c4685e4",
        "outputId": "b5b8622a-e5a0-42c9-af74-3a54af5e7f47"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌───────────────────┐\n",
              "│       name        │\n",
              "│      varchar      │\n",
              "├───────────────────┤\n",
              "│ allergies         │\n",
              "│ careplans         │\n",
              "│ conditions        │\n",
              "│ devices           │\n",
              "│ encounters        │\n",
              "│ imaging_studies   │\n",
              "│ immunizations     │\n",
              "│ medications       │\n",
              "│ observations      │\n",
              "│ organizations     │\n",
              "│ patients          │\n",
              "│ payer_transitions │\n",
              "│ payers            │\n",
              "│ procedures        │\n",
              "│ providers         │\n",
              "│ supplies          │\n",
              "├───────────────────┤\n",
              "│      16 rows      │\n",
              "└───────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 4
        }
      ],
      "source": [
        "conn.sql(\"SHOW TABLES;\")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "f65ef4d0-5988-42c6-b903-3b00c0f9ef0c",
      "metadata": {
        "tags": [],
        "id": "f65ef4d0-5988-42c6-b903-3b00c0f9ef0c"
      },
      "source": [
        "## Before running any query we need to know the column names of particular tables"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "1afbbe30-050a-4999-be5a-14ee29f57cca",
      "metadata": {
        "tags": [],
        "id": "1afbbe30-050a-4999-be5a-14ee29f57cca",
        "outputId": "b827869c-c7d7-4d30-db27-e6da14a487df",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌─────────────────────┬─────────────┬─────────┬─────────┬─────────┬───────┐\n",
              "│     column_name     │ column_type │  null   │   key   │ default │ extra │\n",
              "│       varchar       │   varchar   │ varchar │ varchar │ varchar │ int32 │\n",
              "├─────────────────────┼─────────────┼─────────┼─────────┼─────────┼───────┤\n",
              "│ Id                  │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ BIRTHDATE           │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ DEATHDATE           │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ SSN                 │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ DRIVERS             │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ PASSPORT            │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ PREFIX              │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ FIRST               │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ LAST                │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ SUFFIX              │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│   ·                 │    ·        │  ·      │  ·      │  ·      │    ·  │\n",
              "│   ·                 │    ·        │  ·      │  ·      │  ·      │    ·  │\n",
              "│   ·                 │    ·        │  ·      │  ·      │  ·      │    ·  │\n",
              "│ BIRTHPLACE          │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ ADDRESS             │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ CITY                │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ STATE               │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ COUNTY              │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ ZIP                 │ DOUBLE      │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ LAT                 │ DOUBLE      │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ LON                 │ DOUBLE      │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ HEALTHCARE_EXPENSES │ DOUBLE      │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ HEALTHCARE_COVERAGE │ DOUBLE      │ YES     │ NULL    │ NULL    │  NULL │\n",
              "├─────────────────────┴─────────────┴─────────┴─────────┴─────────┴───────┤\n",
              "│ 25 rows (20 shown)                                            6 columns │\n",
              "└─────────────────────────────────────────────────────────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 5
        }
      ],
      "source": [
        "conn.sql(\"DESCRIBE patients;\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "750d7686-931a-4ede-9e5a-33ec814182fa",
      "metadata": {
        "tags": [],
        "id": "750d7686-931a-4ede-9e5a-33ec814182fa",
        "outputId": "e414de80-1f09-41a2-9857-5eea5c89f54f",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌───────────────────┬─────────────┬─────────┬─────────┬─────────┬───────┐\n",
              "│    column_name    │ column_type │  null   │   key   │ default │ extra │\n",
              "│      varchar      │   varchar   │ varchar │ varchar │ varchar │ int32 │\n",
              "├───────────────────┼─────────────┼─────────┼─────────┼─────────┼───────┤\n",
              "│ START             │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ STOP              │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ PATIENT           │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ PAYER             │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ ENCOUNTER         │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ CODE              │ BIGINT      │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ DESCRIPTION       │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ BASE_COST         │ DOUBLE      │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ PAYER_COVERAGE    │ DOUBLE      │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ DISPENSES         │ BIGINT      │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ TOTALCOST         │ DOUBLE      │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ REASONCODE        │ DOUBLE      │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ REASONDESCRIPTION │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "├───────────────────┴─────────────┴─────────┴─────────┴─────────┴───────┤\n",
              "│ 13 rows                                                     6 columns │\n",
              "└───────────────────────────────────────────────────────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 6
        }
      ],
      "source": [
        "conn.sql(\"DESCRIBE medications;\")"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "conn.sql(\"DESCRIBE immunizations;\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "qPg9wDDk11Jj",
        "outputId": "01c2dcd6-a45b-4099-cc9a-bdf305ff9811"
      },
      "id": "qPg9wDDk11Jj",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌─────────────┬─────────────┬─────────┬─────────┬─────────┬───────┐\n",
              "│ column_name │ column_type │  null   │   key   │ default │ extra │\n",
              "│   varchar   │   varchar   │ varchar │ varchar │ varchar │ int32 │\n",
              "├─────────────┼─────────────┼─────────┼─────────┼─────────┼───────┤\n",
              "│ DATE        │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ PATIENT     │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ ENCOUNTER   │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ CODE        │ BIGINT      │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ DESCRIPTION │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ BASE_COST   │ DOUBLE      │ YES     │ NULL    │ NULL    │  NULL │\n",
              "└─────────────┴─────────────┴─────────┴─────────┴─────────┴───────┘"
            ]
          },
          "metadata": {},
          "execution_count": 7
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "<hr>\n",
        "<hr>\n",
        "<hr>"
      ],
      "metadata": {
        "id": "lZaBtO8V3jyT"
      },
      "id": "lZaBtO8V3jyT"
    },
    {
      "cell_type": "markdown",
      "source": [
        "# **Workshop 04 Starts here**"
      ],
      "metadata": {
        "id": "JL9w8_IYsP1Q"
      },
      "id": "JL9w8_IYsP1Q"
    },
    {
      "cell_type": "markdown",
      "source": [
        " ## Introduction to Subqueries and Nested Selects"
      ],
      "metadata": {
        "id": "Nrs6EKwGHV3d"
      },
      "id": "Nrs6EKwGHV3d"
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Subqueries, also known as inner queries or nested queries, are SQL queries nested inside a larger query. They allow you to perform operations that usually require multiple steps in a single query, making your data retrieval process more efficient and concise. Nested selects are a type of subquery used specifically within the SELECT, FROM, or WHERE clauses to provide a dataset for the outer query to process."
      ],
      "metadata": {
        "id": "v8lH_xsPHb8l"
      },
      "id": "v8lH_xsPHb8l"
    },
    {
      "cell_type": "markdown",
      "source": [
        " ### Subqueries can return a single value, a single row, multiple rows, or a table. They are used for comparison, as a condition, or to provide a list of values for the outer query. The main distinction between correlated and non-correlated subqueries is that correlated subqueries reference column(s) from the outer query, thus running once for each row selected by the outer query, while non-correlated subqueries run independently of the outer query and can be run as standalone queries."
      ],
      "metadata": {
        "id": "rq2VhCrcPoRd"
      },
      "id": "rq2VhCrcPoRd"
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Types of subqueries\n",
        "\n",
        "- **Single-Row Subqueries**: Single-row subqueries return only one row and are used with single row comparison operators like =, >, <, >=, <=. They are often used in the WHERE clause to compare a column value against the result of the subquery.\n",
        "- **Multi-Row Subqueries**: Multi-row subqueries return more than one row and are used with operators like IN, ANY, ALL, which allow comparison against multiple values. They're useful for filtering based on a set of criteria returned by the subquery.\n",
        "- **Correlated Subqueries**: Correlated subqueries reference column(s) from the outer query, making them dependent on the outer query. They are executed once for each row processed by the outer query, often leading to performance considerations.\n"
      ],
      "metadata": {
        "id": "EqLXO-E3MvP5"
      },
      "id": "EqLXO-E3MvP5"
    },
    {
      "cell_type": "markdown",
      "source": [
        "Use subqueries to find patients based on specific criteria.\n",
        "\n",
        "Find all patients who have been prescribed medication with a base cost higher than the average base cost of all medications."
      ],
      "metadata": {
        "id": "Y5skkKqFuPHy"
      },
      "id": "Y5skkKqFuPHy"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT p.FIRST, p.LAST\n",
        "FROM patients p\n",
        "WHERE p.Id IN (\n",
        "    SELECT m.PATIENT\n",
        "    FROM medications m\n",
        "    WHERE m.BASE_COST > (SELECT AVG(BASE_COST) FROM medications)\n",
        ");\n",
        "\"\"\"\n",
        "\n",
        "conn.sql(query)"
      ],
      "metadata": {
        "id": "2vK4ewvgVxlo",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "2c237417-70e1-46f4-c165-a950e767c372"
      },
      "id": "2vK4ewvgVxlo",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌─────────────┬─────────────┐\n",
              "│    FIRST    │    LAST     │\n",
              "│   varchar   │   varchar   │\n",
              "├─────────────┼─────────────┤\n",
              "│ Dewitt635   │ Reichel38   │\n",
              "│ Anisa442    │ Purdy2      │\n",
              "│ Ross213     │ Mayert710   │\n",
              "│ Jim478      │ Mueller846  │\n",
              "│ Maria750    │ Schimmel440 │\n",
              "│ Ignacio928  │ Gorczany269 │\n",
              "│ Kip442      │ Zboncak558  │\n",
              "│ Jean712     │ Kuhlman484  │\n",
              "│ Mac103      │ Moen819     │\n",
              "│ Dianna917   │ Goldner995  │\n",
              "│     ·       │    ·        │\n",
              "│     ·       │    ·        │\n",
              "│     ·       │    ·        │\n",
              "│ Cameron381  │ Bogan287    │\n",
              "│ Andrew29    │ Donnelly343 │\n",
              "│ Charis952   │ Littel644   │\n",
              "│ Lyndon118   │ Swift555    │\n",
              "│ Edward499   │ Zieme486    │\n",
              "│ Myron933    │ Ritchie586  │\n",
              "│ Yolonda722  │ Champlin946 │\n",
              "│ Alayna598   │ Kozey370    │\n",
              "│ Kristian973 │ Ledner144   │\n",
              "│ Sydney660   │ Zulauf375   │\n",
              "├─────────────┴─────────────┤\n",
              "│ ? rows          2 columns │\n",
              "└───────────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 9
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Correlated Subqueries\n",
        "\n",
        "### Learn how to use correlated subqueries to perform row-specific comparisons.\n",
        "\n",
        "### Find patients whose healthcare expenses are higher than the average expenses in their county."
      ],
      "metadata": {
        "id": "kwdoW5Egvbo9"
      },
      "id": "kwdoW5Egvbo9"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT\n",
        "    p.FIRST,\n",
        "    p.LAST,\n",
        "    p.COUNTY,\n",
        "    p.HEALTHCARE_EXPENSES,\n",
        "    ROUND(AVG_EXPENSES.COUNTY_AVG_EXPENSES, 2) AS ROUNDED_AVG_HEALTHCARE_EXPENSES\n",
        "FROM\n",
        "    patients p\n",
        "INNER JOIN (\n",
        "    SELECT\n",
        "        COUNTY,\n",
        "        AVG(CAST(HEALTHCARE_EXPENSES AS DECIMAL(20,6))) AS COUNTY_AVG_EXPENSES\n",
        "    FROM\n",
        "        patients\n",
        "    GROUP BY\n",
        "        COUNTY\n",
        ") AS AVG_EXPENSES ON p.COUNTY = AVG_EXPENSES.COUNTY\n",
        "WHERE\n",
        "    p.HEALTHCARE_EXPENSES > (\n",
        "        SELECT AVG(p2.HEALTHCARE_EXPENSES)\n",
        "        FROM patients p2\n",
        "        WHERE p2.COUNTY = p.COUNTY\n",
        "    );\n",
        "\"\"\"\n",
        "conn.sql(query)"
      ],
      "metadata": {
        "id": "v5h40qReVxtu",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "e9d20801-92d9-42db-a163-2072a983314f"
      },
      "id": "v5h40qReVxtu",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌────────────┬───────────────┬───────────────────┬─────────────────────┬─────────────────────────────────┐\n",
              "│   FIRST    │     LAST      │      COUNTY       │ HEALTHCARE_EXPENSES │ ROUNDED_AVG_HEALTHCARE_EXPENSES │\n",
              "│  varchar   │    varchar    │      varchar      │       double        │             double              │\n",
              "├────────────┼───────────────┼───────────────────┼─────────────────────┼─────────────────────────────────┤\n",
              "│ Tammy740   │ Ernser583     │ Bristol County    │          1546025.67 │                       793296.73 │\n",
              "│ Iliana226  │ Schmeler639   │ Barnstable County │          1407960.93 │                       949155.23 │\n",
              "│ Anthony633 │ Yundt842      │ Essex County      │          1575731.48 │                        814437.7 │\n",
              "│ Jim478     │ Mueller846    │ Bristol County    │          1112473.78 │                       793296.73 │\n",
              "│ Sina65     │ Howell947     │ Middlesex County  │          1479425.58 │                       841491.73 │\n",
              "│ Maria750   │ Schimmel440   │ Plymouth County   │           952814.55 │                        856778.1 │\n",
              "│ Lorenzo669 │ Vandervort697 │ Middlesex County  │           873001.47 │                       841491.73 │\n",
              "│ David908   │ Carter549     │ Worcester County  │           834576.02 │                       804752.74 │\n",
              "│ Noah480    │ O'Reilly797   │ Essex County      │            915110.6 │                        814437.7 │\n",
              "│ Kip442     │ Zboncak558    │ Middlesex County  │  1704103.7200000002 │                       841491.73 │\n",
              "│   ·        │     ·         │       ·           │               ·     │                           ·     │\n",
              "│   ·        │     ·         │       ·           │               ·     │                           ·     │\n",
              "│   ·        │     ·         │       ·           │               ·     │                           ·     │\n",
              "│ Tinisha932 │ Zulauf375     │ Bristol County    │          1035244.36 │                       793296.73 │\n",
              "│ Mario764   │ Estrada938    │ Suffolk County    │           1380401.5 │                       664340.79 │\n",
              "│ Glenn0     │ Schulist381   │ Barnstable County │           1357123.8 │                       949155.23 │\n",
              "│ Porter490  │ Schulist381   │ Bristol County    │          1534216.57 │                       793296.73 │\n",
              "│ Darius626  │ Hackett68     │ Essex County      │          1715140.52 │                        814437.7 │\n",
              "│ Leonel449  │ Kunze215      │ Essex County      │          1492352.86 │                        814437.7 │\n",
              "│ Tyrell880  │ Kassulke119   │ Hampden County    │          1053520.21 │                       725636.71 │\n",
              "│ Arlen68    │ Gusikowski974 │ Worcester County  │           1363289.9 │                       804752.74 │\n",
              "│ Ashli227   │ Huel628       │ Worcester County  │           813730.02 │                       804752.74 │\n",
              "│ Loriann967 │ Mertz280      │ Norfolk County    │          1618397.57 │                       874351.83 │\n",
              "├────────────┴───────────────┴───────────────────┴─────────────────────┴─────────────────────────────────┤\n",
              "│ ? rows (>9999 rows, 20 shown)                                                                5 columns │\n",
              "└────────────────────────────────────────────────────────────────────────────────────────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 32
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### More examples of subqueries\n",
        "\n",
        "### Identify patients who have not been prescribed any medication."
      ],
      "metadata": {
        "id": "sDiKnkvk8lKh"
      },
      "id": "sDiKnkvk8lKh"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"\n",
        "SELECT p.FIRST, p.LAST\n",
        "FROM patients p\n",
        "WHERE NOT EXISTS (\n",
        "    SELECT 1 FROM medications m WHERE m.PATIENT = p.Id\n",
        ");\n",
        "\"\"\"\n",
        "\n",
        "conn.sql(query)"
      ],
      "metadata": {
        "id": "ML1Ksk9zVx05",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "fdf23ca5-2ffb-41dd-9dc1-57dc2b6332a5"
      },
      "id": "ML1Ksk9zVx05",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌────────────┬───────────────┐\n",
              "│   FIRST    │     LAST      │\n",
              "│  varchar   │    varchar    │\n",
              "├────────────┼───────────────┤\n",
              "│ Iliana226  │ Schmeler639   │\n",
              "│ Toshiko149 │ Swaniawski813 │\n",
              "│ Heath320   │ Streich926    │\n",
              "│ Rana586    │ Langworth352  │\n",
              "│ Dorathy429 │ Yost751       │\n",
              "│ Deon400    │ Littel644     │\n",
              "│ Emerson869 │ D'Amore443    │\n",
              "│ Alec433    │ Sanford861    │\n",
              "│ Roselyn270 │ McLaughlin530 │\n",
              "│ Claudia969 │ Smith67       │\n",
              "│   ·        │    ·          │\n",
              "│   ·        │    ·          │\n",
              "│   ·        │    ·          │\n",
              "│ Lou594     │ Gleason633    │\n",
              "│ Hank686    │ Russel238     │\n",
              "│ Earl438    │ Block661      │\n",
              "│ Mariano761 │ Heller342     │\n",
              "│ Sandra485  │ Fisher429     │\n",
              "│ Claire652  │ Murray856     │\n",
              "│ Doreen575  │ Graham902     │\n",
              "│ Fausto876  │ Kuhn96        │\n",
              "│ Vernon254  │ Gutmann970    │\n",
              "│ Ernest565  │ Block661      │\n",
              "├────────────┴───────────────┤\n",
              "│ ? rows           2 columns │\n",
              "└────────────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 27
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Find patients whose healthcare coverage is above the average of their respective city."
      ],
      "metadata": {
        "id": "7rAXcWPH9mQ5"
      },
      "id": "7rAXcWPH9mQ5"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"\n",
        "SELECT\n",
        "    p.FIRST,\n",
        "    p.LAST,\n",
        "    p.CITY,\n",
        "    ROUND(p.HEALTHCARE_COVERAGE,2) AS INDIVIDUAL_COVERAGE,\n",
        "    ROUND(c.AVG_COVERAGE,2) AS CITY_AVERAGE_COVERAGE\n",
        "FROM\n",
        "    patients p\n",
        "INNER JOIN (\n",
        "    SELECT\n",
        "        CITY,\n",
        "        AVG(HEALTHCARE_COVERAGE) AS AVG_COVERAGE\n",
        "    FROM\n",
        "        patients\n",
        "    GROUP BY\n",
        "        CITY\n",
        ") AS c ON p.CITY = c.CITY\n",
        "WHERE\n",
        "    p.HEALTHCARE_COVERAGE > (\n",
        "        SELECT AVG(HEALTHCARE_COVERAGE)\n",
        "        FROM patients\n",
        "        WHERE CITY = p.CITY\n",
        "    );\n",
        "\"\"\"\n",
        "conn.sql(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Z36ZM1Hn9lt7",
        "outputId": "eb1e4835-74b6-443d-c01f-2ee89bfc9c11"
      },
      "id": "Z36ZM1Hn9lt7",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌──────────────┬────────────────┬───────────────┬─────────────────────┬───────────────────────┐\n",
              "│    FIRST     │      LAST      │     CITY      │ INDIVIDUAL_COVERAGE │ CITY_AVERAGE_COVERAGE │\n",
              "│   varchar    │    varchar     │    varchar    │       double        │        double         │\n",
              "├──────────────┼────────────────┼───────────────┼─────────────────────┼───────────────────────┤\n",
              "│ Anisa442     │ Purdy2         │ Methuen       │            34393.57 │              12949.31 │\n",
              "│ Sina65       │ Howell947      │ Sudbury       │            15481.43 │              15385.85 │\n",
              "│ Maria750     │ Schimmel440    │ Plymouth      │            14231.22 │              10826.53 │\n",
              "│ Kip442       │ Zboncak558     │ Hopkinton     │           155478.44 │              15439.17 │\n",
              "│ Hans694      │ Wilkinson796   │ Framingham    │            13729.73 │              11988.99 │\n",
              "│ Jean712      │ Kuhlman484     │ Boxford       │            11421.48 │               8660.53 │\n",
              "│ Lorette239   │ Abbott774      │ Dennis        │            78740.23 │              14918.28 │\n",
              "│ Tobi258      │ Bernier607     │ Wilmington    │             13889.6 │               9411.52 │\n",
              "│ Rosia390     │ Reichel38      │ Rochester     │            16234.29 │               9736.74 │\n",
              "│ Florencia449 │ Mercado213     │ Wilbraham     │            11427.25 │              10468.26 │\n",
              "│   ·          │    ·           │    ·          │                ·    │                  ·    │\n",
              "│   ·          │    ·           │    ·          │                ·    │                  ·    │\n",
              "│   ·          │    ·           │    ·          │                ·    │                  ·    │\n",
              "│ Jc393        │ Marks830       │ Brockton      │            16284.35 │              12287.77 │\n",
              "│ Cedric746    │ Marvin195      │ Dedham        │            19806.72 │              13840.99 │\n",
              "│ Milford485   │ Schmitt836     │ Hubbardston   │            10592.34 │               8172.48 │\n",
              "│ Tegan755     │ Ruecker817     │ Waltham       │            30493.65 │              14495.07 │\n",
              "│ Lindsay928   │ Lang846        │ Boston        │            11837.05 │               10725.8 │\n",
              "│ Claudie965   │ Hermann103     │ Medford       │            15420.54 │              12610.05 │\n",
              "│ Julie245     │ Nolan344       │ Essex         │            31903.53 │              31787.13 │\n",
              "│ Mui729       │ Stoltenberg489 │ Wakefield     │            21528.91 │              13699.32 │\n",
              "│ Oliver401    │ Lesch175       │ Yarmouth      │            27442.87 │              19907.83 │\n",
              "│ Mi492        │ Gusikowski974  │ North Andover │            18481.09 │              17677.13 │\n",
              "├──────────────┴────────────────┴───────────────┴─────────────────────┴───────────────────────┤\n",
              "│ ? rows (>9999 rows, 20 shown)                                                     5 columns │\n",
              "└─────────────────────────────────────────────────────────────────────────────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 31
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### For patients with records spanning multiple years, analyze the change in healthcare expenses."
      ],
      "metadata": {
        "id": "qCeP34HwHBCm"
      },
      "id": "qCeP34HwHBCm"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT CORR(p.HEALTHCARE_EXPENSES, p.HEALTHCARE_COVERAGE) AS CorrelationCoefficient\n",
        "FROM patients p;\n",
        "\"\"\"\n",
        "\n",
        "conn.sql(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "fMQv43hQHN_l",
        "outputId": "496ce832-519d-4b83-ac6d-aebfe868e241"
      },
      "id": "fMQv43hQHN_l",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌────────────────────────┐\n",
              "│ CorrelationCoefficient │\n",
              "│         double         │\n",
              "├────────────────────────┤\n",
              "│    0.18868010271196137 │\n",
              "└────────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 38
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# FUNCTIONS IN SQL"
      ],
      "metadata": {
        "id": "OJmbRh3dUnO3"
      },
      "id": "OJmbRh3dUnO3"
    },
    {
      "cell_type": "markdown",
      "source": [
        "## String Functions"
      ],
      "metadata": {
        "id": "DBjr9TazUv8R"
      },
      "id": "DBjr9TazUv8R"
    },
    {
      "cell_type": "markdown",
      "source": [
        "## CONCAT: Concatenates two or more strings."
      ],
      "metadata": {
        "id": "Xj4q8jBVVqww"
      },
      "id": "Xj4q8jBVVqww"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT CONCAT(FIRST, ' ', LAST) AS FullName FROM patients LIMIT 10;\n",
        ";\"\"\"\n",
        "\n",
        "\n",
        "conn.sql(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "BXh7RvITHOJT",
        "outputId": "8dba0065-8b54-4d74-9716-97f8849e6a01"
      },
      "id": "BXh7RvITHOJT",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌───────────────────────────┐\n",
              "│         FullName          │\n",
              "│          varchar          │\n",
              "├───────────────────────────┤\n",
              "│ Jacinto644 Kris249        │\n",
              "│ Alva958 Krajcik437        │\n",
              "│ Jimmie93 Harris789        │\n",
              "│ Gregorio366 Auer97        │\n",
              "│ Karyn217 Mueller846       │\n",
              "│ Jayson808 Fadel536        │\n",
              "│ José Eduardo181 Gómez206  │\n",
              "│ Milo271 Feil794           │\n",
              "│ Karyn217 Metz686          │\n",
              "│ Jeffrey461 Greenfelder433 │\n",
              "├───────────────────────────┤\n",
              "│          10 rows          │\n",
              "└───────────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 49
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## DATALENGTH: Returns the number of bytes used to represent any expression."
      ],
      "metadata": {
        "id": "2FJdfZ96XQC8"
      },
      "id": "2FJdfZ96XQC8"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"\n",
        "SELECT FIRST, LENGTH(FIRST) AS LengthInBytes FROM patients LIMIT 10;\n",
        "\"\"\"\n",
        "\n",
        "conn.sql(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Sioae_88XQLF",
        "outputId": "f2873a11-b4e6-4fa9-ea65-4f0a3efa9d8c"
      },
      "id": "Sioae_88XQLF",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌─────────────────┬───────────────┐\n",
              "│      FIRST      │ LengthInBytes │\n",
              "│     varchar     │     int64     │\n",
              "├─────────────────┼───────────────┤\n",
              "│ Jacinto644      │            10 │\n",
              "│ Alva958         │             7 │\n",
              "│ Jimmie93        │             8 │\n",
              "│ Gregorio366     │            11 │\n",
              "│ Karyn217        │             8 │\n",
              "│ Jayson808       │             9 │\n",
              "│ José Eduardo181 │            15 │\n",
              "│ Milo271         │             7 │\n",
              "│ Karyn217        │             8 │\n",
              "│ Jeffrey461      │            10 │\n",
              "├─────────────────┴───────────────┤\n",
              "│ 10 rows               2 columns │\n",
              "└─────────────────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 50
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## LEFT: Returns the left part of a character string with the specified number of characters."
      ],
      "metadata": {
        "id": "x7mN5ee4XQZk"
      },
      "id": "x7mN5ee4XQZk"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT LEFT(FIRST, 5) AS Initial FROM patients LIMIT 10;\"\"\"\n",
        "conn.sql(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "M0-q5mLcXQf1",
        "outputId": "b25fc594-6c2d-4230-9088-eda974147d1b"
      },
      "id": "M0-q5mLcXQf1",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌─────────┐\n",
              "│ Initial │\n",
              "│ varchar │\n",
              "├─────────┤\n",
              "│ Jacin   │\n",
              "│ Alva9   │\n",
              "│ Jimmi   │\n",
              "│ Grego   │\n",
              "│ Karyn   │\n",
              "│ Jayso   │\n",
              "│ José    │\n",
              "│ Milo2   │\n",
              "│ Karyn   │\n",
              "│ Jeffr   │\n",
              "├─────────┤\n",
              "│ 10 rows │\n",
              "└─────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 51
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## LOWER: Converts all characters in the specified string to lowercase.\n",
        "\n",
        "## UPPER: Convert to uppercase"
      ],
      "metadata": {
        "id": "yqk2h2nIXQnc"
      },
      "id": "yqk2h2nIXQnc"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"\n",
        "SELECT LOWER(FIRST) AS LowercaseFirstName, UPPER(LAST) AS UppercaseLastName FROM patients LIMIT 10;\n",
        "\"\"\"\n",
        "conn.sql(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "P5pAtu_yXQtr",
        "outputId": "3b29ab8f-4425-4e51-fcb8-9a67ae439f8f"
      },
      "id": "P5pAtu_yXQtr",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌────────────────────┬───────────────────┐\n",
              "│ LowercaseFirstName │ UppercaseLastName │\n",
              "│      varchar       │      varchar      │\n",
              "├────────────────────┼───────────────────┤\n",
              "│ jacinto644         │ KRIS249           │\n",
              "│ alva958            │ KRAJCIK437        │\n",
              "│ jimmie93           │ HARRIS789         │\n",
              "│ gregorio366        │ AUER97            │\n",
              "│ karyn217           │ MUELLER846        │\n",
              "│ jayson808          │ FADEL536          │\n",
              "│ josé eduardo181    │ GÓMEZ206          │\n",
              "│ milo271            │ FEIL794           │\n",
              "│ karyn217           │ METZ686           │\n",
              "│ jeffrey461         │ GREENFELDER433    │\n",
              "├────────────────────┴───────────────────┤\n",
              "│ 10 rows                      2 columns │\n",
              "└────────────────────────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 54
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## LTRIM: Removes leading spaces from a string."
      ],
      "metadata": {
        "id": "NxkMdQhqXQz_"
      },
      "id": "NxkMdQhqXQz_"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT LTRIM(ADDRESS) AS TrimmedAddress FROM patients LIMIT 10;\"\"\"\n",
        "\n",
        "conn.sql(query)\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "-2c8J8fqXQ6W",
        "outputId": "8b0b4ef1-16da-4474-9937-92bac7148ef7"
      },
      "id": "-2c8J8fqXQ6W",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌────────────────────────────────┐\n",
              "│         TrimmedAddress         │\n",
              "│            varchar             │\n",
              "├────────────────────────────────┤\n",
              "│ 888 Hickle Ferry Suite 38      │\n",
              "│ 1048 Skiles Trailer            │\n",
              "│ 201 Mitchell Lodge Unit 67     │\n",
              "│ 1050 Lindgren Extension Apt 38 │\n",
              "│ 570 Abshire Forge Suite 32     │\n",
              "│ 1056 Harris Lane Suite 70      │\n",
              "│ 427 Balistreri Way Unit 19     │\n",
              "│ 422 Farrell Path Unit 69       │\n",
              "│ 181 Feest Passage Suite 64     │\n",
              "│ 428 Wiza Glen Unit 91          │\n",
              "├────────────────────────────────┤\n",
              "│            10 rows             │\n",
              "└────────────────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 56
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## REPLACE: Replaces all occurrences of a specified string value with another string value.\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "1S4VdJPiXRN4"
      },
      "id": "1S4VdJPiXRN4"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT FIRST, LAST,\n",
        "    ADDRESS AS LongAddress,\n",
        "    REPLACE(ADDRESS, 'Street', 'St') AS ShortAddress\n",
        "FROM\n",
        "    patients\n",
        "WHERE\n",
        "    ADDRESS LIKE '%Street%' LIMIT 10;\n",
        "\"\"\"\n",
        "\n",
        "conn.sql(query)"
      ],
      "metadata": {
        "id": "8vNol7hqXRVJ",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "bb05bdca-1966-44c6-ad05-d2278c49de3a"
      },
      "id": "8vNol7hqXRVJ",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌────────────┬───────────────┬───────────────────────────┬───────────────────────┐\n",
              "│   FIRST    │     LAST      │        LongAddress        │     ShortAddress      │\n",
              "│  varchar   │    varchar    │          varchar          │        varchar        │\n",
              "├────────────┼───────────────┼───────────────────────────┼───────────────────────┤\n",
              "│ Lorrie905  │ Leannon79     │ 813 Casper Street         │ 813 Casper St         │\n",
              "│ Asa127     │ Block661      │ 140 Rohan Street Suite 50 │ 140 Rohan St Suite 50 │\n",
              "│ Logan497   │ Brekke496     │ 1081 Orn Street           │ 1081 Orn St           │\n",
              "│ Cletus494  │ Strosin214    │ 1019 Haley Street         │ 1019 Haley St         │\n",
              "│ Cortney940 │ Stehr398      │ 458 Streich Street        │ 458 Streich St        │\n",
              "│ Latoyia537 │ Gaylord332    │ 200 Heaney Street         │ 200 Heaney St         │\n",
              "│ Clark193   │ Hilll811      │ 202 Tromp Street Suite 0  │ 202 Tromp St Suite 0  │\n",
              "│ Jc393      │ Bosco882      │ 792 Walsh Street          │ 792 Walsh St          │\n",
              "│ Noel608    │ Swaniawski813 │ 155 Walter Street         │ 155 Walter St         │\n",
              "│ Sari509    │ Hoppe518      │ 550 Lang Street Suite 65  │ 550 Lang St Suite 65  │\n",
              "├────────────┴───────────────┴───────────────────────────┴───────────────────────┤\n",
              "│ 10 rows                                                              4 columns │\n",
              "└────────────────────────────────────────────────────────────────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 64
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## RIGHT: Returns the right part of a character string with the specified number of characters."
      ],
      "metadata": {
        "id": "ZLf-r-XMp1yJ"
      },
      "id": "ZLf-r-XMp1yJ"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT FIRST, RIGHT(FIRST, 6) AS LastSixChars FROM patients LIMIT 10;\n",
        "\"\"\"\n",
        "\n",
        "conn.sql(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "q7p8oW5_p17U",
        "outputId": "6ebb9ef7-56d3-437d-8133-c1eb66956bfe"
      },
      "id": "q7p8oW5_p17U",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌─────────────────┬──────────────┐\n",
              "│      FIRST      │ LastSixChars │\n",
              "│     varchar     │   varchar    │\n",
              "├─────────────────┼──────────────┤\n",
              "│ Jacinto644      │ nto644       │\n",
              "│ Alva958         │ lva958       │\n",
              "│ Jimmie93        │ mmie93       │\n",
              "│ Gregorio366     │ rio366       │\n",
              "│ Karyn217        │ ryn217       │\n",
              "│ Jayson808       │ son808       │\n",
              "│ José Eduardo181 │ rdo181       │\n",
              "│ Milo271         │ ilo271       │\n",
              "│ Karyn217        │ ryn217       │\n",
              "│ Jeffrey461      │ rey461       │\n",
              "├─────────────────┴──────────────┤\n",
              "│ 10 rows              2 columns │\n",
              "└────────────────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 66
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## SUBSTRING: Returns part of a character, binary, text, or image expression in SQL Server.\n",
        "\n"
      ],
      "metadata": {
        "id": "j9VUzGNyrWMt"
      },
      "id": "j9VUzGNyrWMt"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT FIRST, SUBSTRING(FIRST, 1, 4) AS FirstFourChars FROM patients LIMIT 5;\n",
        "\"\"\"\n",
        "\n",
        "conn.sql(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "rPUxVPnMrWVG",
        "outputId": "8377fab4-6756-4cbd-cfb7-5f8fb66324fa"
      },
      "id": "rPUxVPnMrWVG",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌─────────────┬────────────────┐\n",
              "│    FIRST    │ FirstFourChars │\n",
              "│   varchar   │    varchar     │\n",
              "├─────────────┼────────────────┤\n",
              "│ Jacinto644  │ Jaci           │\n",
              "│ Alva958     │ Alva           │\n",
              "│ Jimmie93    │ Jimm           │\n",
              "│ Gregorio366 │ Greg           │\n",
              "│ Karyn217    │ Kary           │\n",
              "└─────────────┴────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 71
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## REGEXP_REPLACE can use regular expressions to strip numeric characters:"
      ],
      "metadata": {
        "id": "j8jFp86Mp2Cd"
      },
      "id": "j8jFp86Mp2Cd"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT\n",
        "FIRST, REGEXP_REPLACE(FIRST, '[0-9]', '', 'g') AS FirstNameStripped, LAST, REGEXP_REPLACE(LAST, '[0-9]', '', 'g') AS LastNameStripped\n",
        "FROM patients;\n",
        "\"\"\"\n",
        "\n",
        "conn.sql(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "rxyCGFLAp2IT",
        "outputId": "0735c6d4-745e-4e36-f9cd-7c1ae29b9736"
      },
      "id": "rxyCGFLAp2IT",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌─────────────────┬───────────────────┬────────────────┬──────────────────┐\n",
              "│      FIRST      │ FirstNameStripped │      LAST      │ LastNameStripped │\n",
              "│     varchar     │      varchar      │    varchar     │     varchar      │\n",
              "├─────────────────┼───────────────────┼────────────────┼──────────────────┤\n",
              "│ Jacinto644      │ Jacinto           │ Kris249        │ Kris             │\n",
              "│ Alva958         │ Alva              │ Krajcik437     │ Krajcik          │\n",
              "│ Jimmie93        │ Jimmie            │ Harris789      │ Harris           │\n",
              "│ Gregorio366     │ Gregorio          │ Auer97         │ Auer             │\n",
              "│ Karyn217        │ Karyn             │ Mueller846     │ Mueller          │\n",
              "│ Jayson808       │ Jayson            │ Fadel536       │ Fadel            │\n",
              "│ José Eduardo181 │ José Eduardo      │ Gómez206       │ Gómez            │\n",
              "│ Milo271         │ Milo              │ Feil794        │ Feil             │\n",
              "│ Karyn217        │ Karyn             │ Metz686        │ Metz             │\n",
              "│ Jeffrey461      │ Jeffrey           │ Greenfelder433 │ Greenfelder      │\n",
              "│     ·           │    ·              │     ·          │    ·             │\n",
              "│     ·           │    ·              │     ·          │    ·             │\n",
              "│     ·           │    ·              │     ·          │    ·             │\n",
              "│ Raymond398      │ Raymond           │ Kuvalis369     │ Kuvalis          │\n",
              "│ Gearldine455    │ Gearldine         │ Boyer713       │ Boyer            │\n",
              "│ Nichol11        │ Nichol            │ Gleichner915   │ Gleichner        │\n",
              "│ Louvenia131     │ Louvenia          │ Marks830       │ Marks            │\n",
              "│ Raymon366       │ Raymon            │ Beer512        │ Beer             │\n",
              "│ Camelia346      │ Camelia           │ Stamm704       │ Stamm            │\n",
              "│ William805      │ William           │ Pacocha935     │ Pacocha          │\n",
              "│ Guillermo498    │ Guillermo         │ Téllez750      │ Téllez           │\n",
              "│ Milton509       │ Milton            │ Bailey598      │ Bailey           │\n",
              "│ Cecilia788      │ Cecilia           │ Wisozk929      │ Wisozk           │\n",
              "├─────────────────┴───────────────────┴────────────────┴──────────────────┤\n",
              "│ ? rows (>9999 rows, 20 shown)                                 4 columns │\n",
              "└─────────────────────────────────────────────────────────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 68
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Date/Time Functions"
      ],
      "metadata": {
        "id": "tkOf_no9p2O_"
      },
      "id": "tkOf_no9p2O_"
    },
    {
      "cell_type": "markdown",
      "source": [
        "## CURRENT_TIMESTAMP: Returns the current database system timestamp as a datetime value."
      ],
      "metadata": {
        "id": "ZwNtwfbNsZg-"
      },
      "id": "ZwNtwfbNsZg-"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT CURRENT_TIMESTAMP AS CurrentDateTime;\n",
        "\"\"\"\n",
        "\n",
        "conn.sql(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "SwnswuL4p2VC",
        "outputId": "142bb79e-7e9e-4794-dd52-4a4bf36bcc6d"
      },
      "id": "SwnswuL4p2VC",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌───────────────────────────┐\n",
              "│      CurrentDateTime      │\n",
              "│ timestamp with time zone  │\n",
              "├───────────────────────────┤\n",
              "│ 2024-02-19 04:56:28.46+00 │\n",
              "└───────────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 72
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### DATE_DIFF Calculates the difference between two dates."
      ],
      "metadata": {
        "id": "eYW0qKIxp2bs"
      },
      "id": "eYW0qKIxp2bs"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT DATE_DIFF('day', DATE '2024-01-01', DATE '2025-01-01') AS DaysDifference;\n",
        "\n",
        "\n",
        "\"\"\"\n",
        "\n",
        "conn.sql(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "dZ77Gv40p2hc",
        "outputId": "37e18954-9ea5-4aa7-9397-c5112c20cc66"
      },
      "id": "dZ77Gv40p2hc",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌────────────────┐\n",
              "│ DaysDifference │\n",
              "│     int64      │\n",
              "├────────────────┤\n",
              "│            366 │\n",
              "└────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 87
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## DATEPART: Returns an integer representing the specified part of a date."
      ],
      "metadata": {
        "id": "eoOCAqUNp2nZ"
      },
      "id": "eoOCAqUNp2nZ"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT FIRST, LAST,BIRTHDATE,  DATE_PART('year', CAST(BIRTHDATE AS DATE)) AS YearOfBirth FROM patients LIMIT 10;\n",
        "\"\"\"\n",
        "\n",
        "conn.sql(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "bvL42v9tp2tg",
        "outputId": "7e82492b-5e84-4680-9d74-0a1cc31bf83a"
      },
      "id": "bvL42v9tp2tg",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌─────────────────┬────────────────┬────────────┬─────────────┐\n",
              "│      FIRST      │      LAST      │ BIRTHDATE  │ YearOfBirth │\n",
              "│     varchar     │    varchar     │  varchar   │    int64    │\n",
              "├─────────────────┼────────────────┼────────────┼─────────────┤\n",
              "│ Jacinto644      │ Kris249        │ 2017-08-24 │        2017 │\n",
              "│ Alva958         │ Krajcik437     │ 2016-08-01 │        2016 │\n",
              "│ Jimmie93        │ Harris789      │ 2004-01-09 │        2004 │\n",
              "│ Gregorio366     │ Auer97         │ 1996-11-15 │        1996 │\n",
              "│ Karyn217        │ Mueller846     │ 2019-06-12 │        2019 │\n",
              "│ Jayson808       │ Fadel536       │ 1992-06-30 │        1992 │\n",
              "│ José Eduardo181 │ Gómez206       │ 1989-06-22 │        1989 │\n",
              "│ Milo271         │ Feil794        │ 1983-12-12 │        1983 │\n",
              "│ Karyn217        │ Metz686        │ 1991-07-31 │        1991 │\n",
              "│ Jeffrey461      │ Greenfelder433 │ 2005-01-16 │        2005 │\n",
              "├─────────────────┴────────────────┴────────────┴─────────────┤\n",
              "│ 10 rows                                           4 columns │\n",
              "└─────────────────────────────────────────────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 93
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### CAST: Converts a value from one data type to another."
      ],
      "metadata": {
        "id": "hu_lEnFUp2zh"
      },
      "id": "hu_lEnFUp2zh"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT FIRST, LAST,HEALTHCARE_EXPENSES, CAST(HEALTHCARE_EXPENSES AS VARCHAR(20)) AS ExpensesText FROM patients LIMIT 10;\n",
        "\"\"\"\n",
        "\n",
        "conn.sql(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Fz_FNT_0p26L",
        "outputId": "bbe74a4b-47b9-4875-ece5-e9186b146e98"
      },
      "id": "Fz_FNT_0p26L",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌─────────────────┬────────────────┬─────────────────────┬──────────────┐\n",
              "│      FIRST      │      LAST      │ HEALTHCARE_EXPENSES │ ExpensesText │\n",
              "│     varchar     │    varchar     │       double        │   varchar    │\n",
              "├─────────────────┼────────────────┼─────────────────────┼──────────────┤\n",
              "│ Jacinto644      │ Kris249        │             8446.49 │ 8446.49      │\n",
              "│ Alva958         │ Krajcik437     │             94568.4 │ 94568.4      │\n",
              "│ Jimmie93        │ Harris789      │           375754.62 │ 375754.62    │\n",
              "│ Gregorio366     │ Auer97         │           484758.46 │ 484758.46    │\n",
              "│ Karyn217        │ Mueller846     │             24130.0 │ 24130.0      │\n",
              "│ Jayson808       │ Fadel536       │           607481.92 │ 607481.92    │\n",
              "│ José Eduardo181 │ Gómez206       │            33411.93 │ 33411.93     │\n",
              "│ Milo271         │ Feil794        │           880850.51 │ 880850.51    │\n",
              "│ Karyn217        │ Metz686        │           767029.01 │ 767029.01    │\n",
              "│ Jeffrey461      │ Greenfelder433 │           352288.38 │ 352288.38    │\n",
              "├─────────────────┴────────────────┴─────────────────────┴──────────────┤\n",
              "│ 10 rows                                                     4 columns │\n",
              "└───────────────────────────────────────────────────────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 96
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Advanced Functions"
      ],
      "metadata": {
        "id": "XLpjqajRp3Mg"
      },
      "id": "XLpjqajRp3Mg"
    },
    {
      "cell_type": "markdown",
      "source": [
        "### VERSION returns current duckDB version"
      ],
      "metadata": {
        "id": "EiLVU43WzTvS"
      },
      "id": "EiLVU43WzTvS"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT VERSION();\n",
        "\"\"\"\n",
        "\n",
        "conn.sql(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "HQvNkwJ0p3SD",
        "outputId": "7ce3475a-44a5-49d8-c88d-a3d43065341a"
      },
      "id": "HQvNkwJ0p3SD",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌───────────┐\n",
              "│ version() │\n",
              "│  varchar  │\n",
              "├───────────┤\n",
              "│ v0.9.2    │\n",
              "└───────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 101
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### CASE: Allows for conditional logic in SQL queries."
      ],
      "metadata": {
        "id": "buxy01vYzcwk"
      },
      "id": "buxy01vYzcwk"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT FIRST, LAST,\n",
        "       CASE\n",
        "           WHEN HEALTHCARE_EXPENSES > 10000 THEN 'High'\n",
        "           ELSE 'Low'\n",
        "       END AS ExpenseCategory\n",
        "FROM patients;\n",
        "\n",
        "\"\"\"\n",
        "\n",
        "conn.sql(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "QMYRpucnzc5O",
        "outputId": "62b54a76-db52-48c5-ca40-f0698713d2ea"
      },
      "id": "QMYRpucnzc5O",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌─────────────────┬────────────────┬─────────────────┐\n",
              "│      FIRST      │      LAST      │ ExpenseCategory │\n",
              "│     varchar     │    varchar     │     varchar     │\n",
              "├─────────────────┼────────────────┼─────────────────┤\n",
              "│ Jacinto644      │ Kris249        │ Low             │\n",
              "│ Alva958         │ Krajcik437     │ High            │\n",
              "│ Jimmie93        │ Harris789      │ High            │\n",
              "│ Gregorio366     │ Auer97         │ High            │\n",
              "│ Karyn217        │ Mueller846     │ High            │\n",
              "│ Jayson808       │ Fadel536       │ High            │\n",
              "│ José Eduardo181 │ Gómez206       │ High            │\n",
              "│ Milo271         │ Feil794        │ High            │\n",
              "│ Karyn217        │ Metz686        │ High            │\n",
              "│ Jeffrey461      │ Greenfelder433 │ High            │\n",
              "│     ·           │     ·          │  ·              │\n",
              "│     ·           │     ·          │  ·              │\n",
              "│     ·           │     ·          │  ·              │\n",
              "│ Raymond398      │ Kuvalis369     │ High            │\n",
              "│ Gearldine455    │ Boyer713       │ High            │\n",
              "│ Nichol11        │ Gleichner915   │ High            │\n",
              "│ Louvenia131     │ Marks830       │ High            │\n",
              "│ Raymon366       │ Beer512        │ High            │\n",
              "│ Camelia346      │ Stamm704       │ High            │\n",
              "│ William805      │ Pacocha935     │ High            │\n",
              "│ Guillermo498    │ Téllez750      │ High            │\n",
              "│ Milton509       │ Bailey598      │ High            │\n",
              "│ Cecilia788      │ Wisozk929      │ High            │\n",
              "├─────────────────┴────────────────┴─────────────────┤\n",
              "│ ? rows (>9999 rows, 20 shown)            3 columns │\n",
              "└────────────────────────────────────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 104
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## COALESCE: Returns the first non-null value in a list of arguments."
      ],
      "metadata": {
        "id": "-JxtALTXzdAg"
      },
      "id": "-JxtALTXzdAg"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT FIRST, COALESCE(DEATHDATE, 'N/A') AS DeathDate\n",
        "FROM patients;\n",
        "\n",
        "\"\"\"\n",
        "\n",
        "conn.sql(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "M_bqFQmJzdJV",
        "outputId": "d8daadda-82c7-4d13-ce46-4f773420fdc5"
      },
      "id": "M_bqFQmJzdJV",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌─────────────────┬────────────┐\n",
              "│      FIRST      │ DeathDate  │\n",
              "│     varchar     │  varchar   │\n",
              "├─────────────────┼────────────┤\n",
              "│ Jacinto644      │ N/A        │\n",
              "│ Alva958         │ N/A        │\n",
              "│ Jimmie93        │ N/A        │\n",
              "│ Gregorio366     │ N/A        │\n",
              "│ Karyn217        │ N/A        │\n",
              "│ Jayson808       │ N/A        │\n",
              "│ José Eduardo181 │ N/A        │\n",
              "│ Milo271         │ N/A        │\n",
              "│ Karyn217        │ N/A        │\n",
              "│ Jeffrey461      │ N/A        │\n",
              "│     ·           │  ·         │\n",
              "│     ·           │  ·         │\n",
              "│     ·           │  ·         │\n",
              "│ Raymond398      │ N/A        │\n",
              "│ Gearldine455    │ N/A        │\n",
              "│ Nichol11        │ N/A        │\n",
              "│ Louvenia131     │ N/A        │\n",
              "│ Raymon366       │ 2020-03-23 │\n",
              "│ Camelia346      │ N/A        │\n",
              "│ William805      │ N/A        │\n",
              "│ Guillermo498    │ N/A        │\n",
              "│ Milton509       │ 2020-03-22 │\n",
              "│ Cecilia788      │ N/A        │\n",
              "├─────────────────┴────────────┤\n",
              "│ ? rows             2 columns │\n",
              "└──────────────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 105
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### CURRENT_USER: Returns the name of the current user."
      ],
      "metadata": {
        "id": "nvnz0YW0zdSX"
      },
      "id": "nvnz0YW0zdSX"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT CURRENT_USER AS CurrentUser;\n",
        "\"\"\"\n",
        "\n",
        "conn.sql(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "-ucJ6E2vzdZ9",
        "outputId": "0361db3c-4303-43b0-a70f-0ba1213cd2a3"
      },
      "id": "-ucJ6E2vzdZ9",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌─────────────┐\n",
              "│ CurrentUser │\n",
              "│   varchar   │\n",
              "├─────────────┤\n",
              "│ duckdb      │\n",
              "└─────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 106
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Creating and Adding Data to tables"
      ],
      "metadata": {
        "id": "M8n4rz3J_KjR"
      },
      "id": "M8n4rz3J_KjR"
    },
    {
      "cell_type": "markdown",
      "source": [
        "CREATE, ALTER, DROP, INSERT commands"
      ],
      "metadata": {
        "id": "4FQdu3iNt46w"
      },
      "id": "4FQdu3iNt46w"
    },
    {
      "cell_type": "markdown",
      "source": [
        "## First lets have a inmemory database"
      ],
      "metadata": {
        "id": "61-693q_-6pJ"
      },
      "id": "61-693q_-6pJ"
    },
    {
      "cell_type": "code",
      "source": [
        "newdb = duckdb.connect()"
      ],
      "metadata": {
        "id": "ckLHg2_4-55h"
      },
      "id": "ckLHg2_4-55h",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## CREATE TABLE: Create a books table to mimic a library catalog system."
      ],
      "metadata": {
        "id": "r7Uq1WGr9JEe"
      },
      "id": "r7Uq1WGr9JEe"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"CREATE TABLE books (\n",
        "    book_id INTEGER PRIMARY KEY,\n",
        "    title VARCHAR,\n",
        "    author VARCHAR,\n",
        "    published_year INTEGER\n",
        ");\n",
        "\"\"\"\n",
        "\n",
        "newdb.sql(query)"
      ],
      "metadata": {
        "id": "XlAF8blqsVQT"
      },
      "id": "XlAF8blqsVQT",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### The CREATE TABLE statement defines the schema for this table:\n",
        "\n",
        "### This statement creates a table with columns for the book ID, title, author, and the year of publication. The book_id column is designated as the primary key, ensuring that each record in the table is unique."
      ],
      "metadata": {
        "id": "rjnZt2kpCeqq"
      },
      "id": "rjnZt2kpCeqq"
    },
    {
      "cell_type": "markdown",
      "source": [
        "## check"
      ],
      "metadata": {
        "id": "dCywCkAJBgXN"
      },
      "id": "dCywCkAJBgXN"
    },
    {
      "cell_type": "code",
      "source": [
        "newdb.sql(\"SHOW TABLES;\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "T7psCH7tBf1l",
        "outputId": "bf862eed-2848-4dd0-a000-2f68305893d4"
      },
      "id": "T7psCH7tBf1l",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌─────────┐\n",
              "│  name   │\n",
              "│ varchar │\n",
              "├─────────┤\n",
              "│ books   │\n",
              "└─────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 111
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## check data"
      ],
      "metadata": {
        "id": "IvYoFUcBBpav"
      },
      "id": "IvYoFUcBBpav"
    },
    {
      "cell_type": "code",
      "source": [
        "newdb.sql(\"DESCRIBE books;\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "6qJoGbblBh6t",
        "outputId": "e27e27ac-a294-4eb2-e250-9b305d684d68"
      },
      "id": "6qJoGbblBh6t",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌────────────────┬─────────────┬─────────┬─────────┬─────────┬───────┐\n",
              "│  column_name   │ column_type │  null   │   key   │ default │ extra │\n",
              "│    varchar     │   varchar   │ varchar │ varchar │ varchar │ int32 │\n",
              "├────────────────┼─────────────┼─────────┼─────────┼─────────┼───────┤\n",
              "│ book_id        │ INTEGER     │ NO      │ PRI     │ NULL    │  NULL │\n",
              "│ title          │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ author         │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ published_year │ INTEGER     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "└────────────────┴─────────────┴─────────┴─────────┴─────────┴───────┘"
            ]
          },
          "metadata": {},
          "execution_count": 112
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Alter Table: After creating the table, you might need to add more information. Use the ALTER TABLE statement to add a new column:"
      ],
      "metadata": {
        "id": "yskaI-T09Fww"
      },
      "id": "yskaI-T09Fww"
    },
    {
      "cell_type": "markdown",
      "source": [],
      "metadata": {
        "id": "lvTtCGZcCYOm"
      },
      "id": "lvTtCGZcCYOm"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"ALTER TABLE books ADD COLUMN genre VARCHAR;\n",
        "\"\"\"\n",
        "newdb.sql(query)\n",
        "# This adds a genre column to the books table, allowing you to categorize books."
      ],
      "metadata": {
        "id": "hCZXjjKFCYW2"
      },
      "id": "hCZXjjKFCYW2",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## check"
      ],
      "metadata": {
        "id": "OlSMuqnGCYdz"
      },
      "id": "OlSMuqnGCYdz"
    },
    {
      "cell_type": "code",
      "source": [
        "newdb.sql(\"DESCRIBE books;\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "rRSv6GptCYl2",
        "outputId": "b405cc49-76b5-4962-dd06-bca97b545985"
      },
      "id": "rRSv6GptCYl2",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌────────────────┬─────────────┬─────────┬─────────┬─────────┬───────┐\n",
              "│  column_name   │ column_type │  null   │   key   │ default │ extra │\n",
              "│    varchar     │   varchar   │ varchar │ varchar │ varchar │ int32 │\n",
              "├────────────────┼─────────────┼─────────┼─────────┼─────────┼───────┤\n",
              "│ book_id        │ INTEGER     │ NO      │ PRI     │ NULL    │  NULL │\n",
              "│ title          │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ author         │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ published_year │ INTEGER     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "│ genre          │ VARCHAR     │ YES     │ NULL    │ NULL    │  NULL │\n",
              "└────────────────┴─────────────┴─────────┴─────────┴─────────┴───────┘"
            ]
          },
          "metadata": {},
          "execution_count": 114
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### let's add another which we will DROP later;"
      ],
      "metadata": {
        "id": "Si3RN2l9CYrp"
      },
      "id": "Si3RN2l9CYrp"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"CREATE TABLE authors (\n",
        "    author_id INTEGER PRIMARY KEY,\n",
        "    firstname VARCHAR,\n",
        "    lastname VARCHAR,\n",
        "    birth_year INTEGER\n",
        ");\n",
        "\"\"\"\n",
        "\n",
        "newdb.sql(query)"
      ],
      "metadata": {
        "id": "exYT1IkqCYx1"
      },
      "id": "exYT1IkqCYx1",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## check"
      ],
      "metadata": {
        "id": "9PQKJOkTCY5q"
      },
      "id": "9PQKJOkTCY5q"
    },
    {
      "cell_type": "code",
      "source": [
        "newdb.sql(\"SHOW TABLES;\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "jSlr7Px2CY_u",
        "outputId": "eaf35842-6493-49ac-8d9d-da8533e96015"
      },
      "id": "jSlr7Px2CY_u",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌─────────┐\n",
              "│  name   │\n",
              "│ varchar │\n",
              "├─────────┤\n",
              "│ authors │\n",
              "│ books   │\n",
              "└─────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 117
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Now let's **DROP** the author table"
      ],
      "metadata": {
        "id": "b-xKN6WMCZF0"
      },
      "id": "b-xKN6WMCZF0"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"DROP TABLE IF EXISTS authors;\n",
        "\"\"\"\n",
        "\n",
        "newdb.sql(query)"
      ],
      "metadata": {
        "id": "eIRbqVxOCZLh"
      },
      "id": "eIRbqVxOCZLh",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## check again"
      ],
      "metadata": {
        "id": "3Wr-8eajCZSN"
      },
      "id": "3Wr-8eajCZSN"
    },
    {
      "cell_type": "code",
      "source": [
        "newdb.sql(\"SHOW TABLES;\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "7wKSJNiTCZYJ",
        "outputId": "e6a32461-9eb0-469f-9c5c-f009c3ae51d2"
      },
      "id": "7wKSJNiTCZYJ",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌─────────┐\n",
              "│  name   │\n",
              "│ varchar │\n",
              "├─────────┤\n",
              "│ books   │\n",
              "└─────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 119
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### Let's add some data first"
      ],
      "metadata": {
        "id": "EKQ-G7OIFpou"
      },
      "id": "EKQ-G7OIFpou"
    },
    {
      "cell_type": "markdown",
      "source": [
        "### **INSERT** To populate the books table with data. This inserts 10 records into the books table, providing a diverse set of examples."
      ],
      "metadata": {
        "id": "wZeY-FobFvpJ"
      },
      "id": "wZeY-FobFvpJ"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"INSERT INTO books (book_id, title, author, published_year, genre) VALUES\n",
        "(1, '1984', 'George Orwell', 1949, 'Dystopian'),\n",
        "(2, 'The Great Gatsby', 'F. Scott Fitzgerald', 1925, 'Classic'),\n",
        "(3, 'To Kill a Mockingbird', 'Harper Lee', 1960, 'Classic'),\n",
        "(4, 'Pride and Prejudice', 'Jane Austen', 1813, 'Romance'),\n",
        "(5, 'The Hobbit', 'J.R.R. Tolkien', 1937, 'Fantasy'),\n",
        "(6, 'The Catcher in the Rye', 'J.D. Salinger', 1951, 'Literary Fiction'),\n",
        "(7, 'Harry Potter and the Sorcerer’s Stone', 'J.K. Rowling', 1997, 'Fantasy'),\n",
        "(8, 'The Da Vinci Code', 'Dan Brown', 2003, 'Thriller'),\n",
        "(9, 'Sapiens: A Brief History of Humankind', 'Yuval Noah Harari', 2011, 'Non-fiction'),\n",
        "(10, 'Beloved', 'Toni Morrison', 1987, 'Historical Fiction');\"\"\"\n",
        "\n",
        "newdb.sql(query)\n"
      ],
      "metadata": {
        "id": "S_bOz7QlFvy9"
      },
      "id": "S_bOz7QlFvy9",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## check"
      ],
      "metadata": {
        "id": "yybiuI8TGKGN"
      },
      "id": "yybiuI8TGKGN"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT title AS \"Book Title\", author AS \"Author Name\", Genre FROM books;\n",
        "\"\"\"\n",
        "\n",
        "newdb.sql(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "FRV9cxVnGJi8",
        "outputId": "594812f0-51fe-4a57-ae16-ef2998393c7b"
      },
      "id": "FRV9cxVnGJi8",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌───────────────────────────────────────┬─────────────────────┬────────────────────┐\n",
              "│              Book Title               │     Author Name     │       genre        │\n",
              "│                varchar                │       varchar       │      varchar       │\n",
              "├───────────────────────────────────────┼─────────────────────┼────────────────────┤\n",
              "│ 1984                                  │ George Orwell       │ Dystopian          │\n",
              "│ The Great Gatsby                      │ F. Scott Fitzgerald │ Classic            │\n",
              "│ To Kill a Mockingbird                 │ Harper Lee          │ Classic            │\n",
              "│ Pride and Prejudice                   │ Jane Austen         │ Romance            │\n",
              "│ The Hobbit                            │ J.R.R. Tolkien      │ Fantasy            │\n",
              "│ The Catcher in the Rye                │ J.D. Salinger       │ Literary Fiction   │\n",
              "│ Harry Potter and the Sorcerer’s Stone │ J.K. Rowling        │ Fantasy            │\n",
              "│ The Da Vinci Code                     │ Dan Brown           │ Thriller           │\n",
              "│ Sapiens: A Brief History of Humankind │ Yuval Noah Harari   │ Non-fiction        │\n",
              "│ Beloved                               │ Toni Morrison       │ Historical Fiction │\n",
              "├───────────────────────────────────────┴─────────────────────┴────────────────────┤\n",
              "│ 10 rows                                                                3 columns │\n",
              "└──────────────────────────────────────────────────────────────────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 123
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Updating Data: **UPDATE**\n",
        "### This changes the genre of the book titled '1984' to 'Science Fiction'."
      ],
      "metadata": {
        "id": "PxT8drG3H2IX"
      },
      "id": "PxT8drG3H2IX"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"UPDATE books SET genre = 'Science Fiction' WHERE title = '1984';\n",
        "\"\"\"\n",
        "\n",
        "newdb.sql(query)"
      ],
      "metadata": {
        "id": "0kdseOChHz6_"
      },
      "id": "0kdseOChHz6_",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### check"
      ],
      "metadata": {
        "id": "7S7dbYCQIjCM"
      },
      "id": "7S7dbYCQIjCM"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT title AS \"Book Title\", author AS \"Author Name\", Genre FROM books;\n",
        "\"\"\"\n",
        "\n",
        "newdb.sql(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "T7mHJtI9IjIb",
        "outputId": "7baadfce-3d32-47ba-eed1-931aac38b2af"
      },
      "id": "T7mHJtI9IjIb",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌───────────────────────────────────────┬─────────────────────┬────────────────────┐\n",
              "│              Book Title               │     Author Name     │       genre        │\n",
              "│                varchar                │       varchar       │      varchar       │\n",
              "├───────────────────────────────────────┼─────────────────────┼────────────────────┤\n",
              "│ 1984                                  │ George Orwell       │ Science Fiction    │\n",
              "│ The Great Gatsby                      │ F. Scott Fitzgerald │ Classic            │\n",
              "│ To Kill a Mockingbird                 │ Harper Lee          │ Classic            │\n",
              "│ Pride and Prejudice                   │ Jane Austen         │ Romance            │\n",
              "│ The Hobbit                            │ J.R.R. Tolkien      │ Fantasy            │\n",
              "│ The Catcher in the Rye                │ J.D. Salinger       │ Literary Fiction   │\n",
              "│ Harry Potter and the Sorcerer’s Stone │ J.K. Rowling        │ Fantasy            │\n",
              "│ The Da Vinci Code                     │ Dan Brown           │ Thriller           │\n",
              "│ Sapiens: A Brief History of Humankind │ Yuval Noah Harari   │ Non-fiction        │\n",
              "│ Beloved                               │ Toni Morrison       │ Historical Fiction │\n",
              "├───────────────────────────────────────┴─────────────────────┴────────────────────┤\n",
              "│ 10 rows                                                                3 columns │\n",
              "└──────────────────────────────────────────────────────────────────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 125
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## **DELETE** To remove a book from the catalog"
      ],
      "metadata": {
        "id": "hwls__C-InvF"
      },
      "id": "hwls__C-InvF"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"DELETE FROM books WHERE title = 'The Great Gatsby';\"\"\"\n",
        "newdb.sql(query)"
      ],
      "metadata": {
        "id": "ij9BeOemIn59"
      },
      "id": "ij9BeOemIn59",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## check"
      ],
      "metadata": {
        "id": "jcOUZBxrIyNJ"
      },
      "id": "jcOUZBxrIyNJ"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT title AS \"Book Title\", author AS \"Author Name\", Genre FROM books;\n",
        "\"\"\"\n",
        "\n",
        "newdb.sql(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "it1cPrgEIyUD",
        "outputId": "3275c191-ecd1-41d5-b811-6335cd4a195f"
      },
      "id": "it1cPrgEIyUD",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌───────────────────────────────────────┬───────────────────┬────────────────────┐\n",
              "│              Book Title               │    Author Name    │       genre        │\n",
              "│                varchar                │      varchar      │      varchar       │\n",
              "├───────────────────────────────────────┼───────────────────┼────────────────────┤\n",
              "│ 1984                                  │ George Orwell     │ Science Fiction    │\n",
              "│ To Kill a Mockingbird                 │ Harper Lee        │ Classic            │\n",
              "│ Pride and Prejudice                   │ Jane Austen       │ Romance            │\n",
              "│ The Hobbit                            │ J.R.R. Tolkien    │ Fantasy            │\n",
              "│ The Catcher in the Rye                │ J.D. Salinger     │ Literary Fiction   │\n",
              "│ Harry Potter and the Sorcerer’s Stone │ J.K. Rowling      │ Fantasy            │\n",
              "│ The Da Vinci Code                     │ Dan Brown         │ Thriller           │\n",
              "│ Sapiens: A Brief History of Humankind │ Yuval Noah Harari │ Non-fiction        │\n",
              "│ Beloved                               │ Toni Morrison     │ Historical Fiction │\n",
              "└───────────────────────────────────────┴───────────────────┴────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 127
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### **TRUNCATE**\n",
        "\n",
        "#### The TRUNCATE TABLE statement is used to delete all rows in a table without logging the individual row deletions. This is faster than using DELETE without a WHERE clause because TRUNCATE immediately deallocates data pages used by the table. Use TRUNCATE when you want to quickly remove all records from a table but keep the table structure for future use. Unlike DELETE, TRUNCATE does not generate a large number of transaction logs, making it more efficient for completely clearing a table."
      ],
      "metadata": {
        "id": "EMeL-ZjyCZdp"
      },
      "id": "EMeL-ZjyCZdp"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"TRUNCATE TABLE books;\"\"\"\n",
        "\n",
        "newdb.sql(query)"
      ],
      "metadata": {
        "id": "hqzUbQQ9CZkH"
      },
      "id": "hqzUbQQ9CZkH",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### check"
      ],
      "metadata": {
        "id": "2knNnnlFEQxh"
      },
      "id": "2knNnnlFEQxh"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT * FROM books;\n",
        "\"\"\"\n",
        "\n",
        "newdb.sql(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "hE2whPEAEQ77",
        "outputId": "4b2160ae-66aa-4872-8d8a-19b216564693"
      },
      "id": "hE2whPEAEQ77",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌─────────┬───────────────────────────────────────┬─────────────────────┬────────────────┬────────────────────┐\n",
              "│ book_id │                 title                 │       author        │ published_year │       genre        │\n",
              "│  int32  │                varchar                │       varchar       │     int32      │      varchar       │\n",
              "├─────────┼───────────────────────────────────────┼─────────────────────┼────────────────┼────────────────────┤\n",
              "│       1 │ 1984                                  │ George Orwell       │           1949 │ Dystopian          │\n",
              "│       2 │ The Great Gatsby                      │ F. Scott Fitzgerald │           1925 │ Classic            │\n",
              "│       3 │ To Kill a Mockingbird                 │ Harper Lee          │           1960 │ Classic            │\n",
              "│       4 │ Pride and Prejudice                   │ Jane Austen         │           1813 │ Romance            │\n",
              "│       5 │ The Hobbit                            │ J.R.R. Tolkien      │           1937 │ Fantasy            │\n",
              "│       6 │ The Catcher in the Rye                │ J.D. Salinger       │           1951 │ Literary Fiction   │\n",
              "│       7 │ Harry Potter and the Sorcerer’s Stone │ J.K. Rowling        │           1997 │ Fantasy            │\n",
              "│       8 │ The Da Vinci Code                     │ Dan Brown           │           2003 │ Thriller           │\n",
              "│       9 │ Sapiens: A Brief History of Humankind │ Yuval Noah Harari   │           2011 │ Non-fiction        │\n",
              "│      10 │ Beloved                               │ Toni Morrison       │           1987 │ Historical Fiction │\n",
              "├─────────┴───────────────────────────────────────┴─────────────────────┴────────────────┴────────────────────┤\n",
              "│ 10 rows                                                                                           5 columns │\n",
              "└─────────────────────────────────────────────────────────────────────────────────────────────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 131
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Adding one more table; named 'authors'"
      ],
      "metadata": {
        "id": "fC6tXTEiERDb"
      },
      "id": "fC6tXTEiERDb"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"CREATE TABLE authors (\n",
        "    author_id INTEGER PRIMARY KEY,\n",
        "    name VARCHAR,\n",
        "    birth_year INTEGER,\n",
        "    nationality VARCHAR\n",
        ");\n",
        "\"\"\"\n",
        "\n",
        "newdb.sql(query)"
      ],
      "metadata": {
        "id": "64nBekVRMDQ1"
      },
      "id": "64nBekVRMDQ1",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Add information the authors table"
      ],
      "metadata": {
        "id": "9x0eejDtMM5W"
      },
      "id": "9x0eejDtMM5W"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"INSERT INTO authors (author_id, name, birth_year, nationality) VALUES\n",
        "(1, 'George Orwell', 1903, 'British'),\n",
        "(2, 'F. Scott Fitzgerald', 1896, 'American'),\n",
        "(3, 'Harper Lee', 1926, 'American'),\n",
        "(4, 'Jane Austen', 1775, 'British'),\n",
        "(5, 'J.R.R. Tolkien', 1892, 'British'),\n",
        "(6, 'J.D. Salinger', 1919, 'American'),\n",
        "(7, 'J.K. Rowling', 1965, 'British'),\n",
        "(8, 'Dan Brown', 1964, 'American'),\n",
        "(9, 'Yuval Noah Harari', 1976, 'Israeli'),\n",
        "(10, 'Toni Morrison', 1931, 'American');\n",
        "\"\"\"\n",
        "\n",
        "\n",
        "newdb.sql(query)"
      ],
      "metadata": {
        "id": "IA6YLfg2ERKu"
      },
      "id": "IA6YLfg2ERKu",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT * FROM books;\n",
        "\"\"\"\n",
        "\n",
        "newdb.sql(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "W22cJ3ODERWe",
        "outputId": "2b680330-371a-406f-d6b7-fcab0a32ec8e"
      },
      "id": "W22cJ3ODERWe",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌─────────┬───────────────────────────────────────┬─────────────────────┬────────────────┬────────────────────┐\n",
              "│ book_id │                 title                 │       author        │ published_year │       genre        │\n",
              "│  int32  │                varchar                │       varchar       │     int32      │      varchar       │\n",
              "├─────────┼───────────────────────────────────────┼─────────────────────┼────────────────┼────────────────────┤\n",
              "│       1 │ 1984                                  │ George Orwell       │           1949 │ Dystopian          │\n",
              "│       2 │ The Great Gatsby                      │ F. Scott Fitzgerald │           1925 │ Classic            │\n",
              "│       3 │ To Kill a Mockingbird                 │ Harper Lee          │           1960 │ Classic            │\n",
              "│       4 │ Pride and Prejudice                   │ Jane Austen         │           1813 │ Romance            │\n",
              "│       5 │ The Hobbit                            │ J.R.R. Tolkien      │           1937 │ Fantasy            │\n",
              "│       6 │ The Catcher in the Rye                │ J.D. Salinger       │           1951 │ Literary Fiction   │\n",
              "│       7 │ Harry Potter and the Sorcerer’s Stone │ J.K. Rowling        │           1997 │ Fantasy            │\n",
              "│       8 │ The Da Vinci Code                     │ Dan Brown           │           2003 │ Thriller           │\n",
              "│       9 │ Sapiens: A Brief History of Humankind │ Yuval Noah Harari   │           2011 │ Non-fiction        │\n",
              "│      10 │ Beloved                               │ Toni Morrison       │           1987 │ Historical Fiction │\n",
              "├─────────┴───────────────────────────────────────┴─────────────────────┴────────────────┴────────────────────┤\n",
              "│ 10 rows                                                                                           5 columns │\n",
              "└─────────────────────────────────────────────────────────────────────────────────────────────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 135
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT * FROM authors;\n",
        "\"\"\"\n",
        "\n",
        "newdb.sql(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "snIM6QL6MTXg",
        "outputId": "5110c9f5-ab4b-43f9-eb6e-4e16d70be248"
      },
      "id": "snIM6QL6MTXg",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌───────────┬─────────────────────┬────────────┬─────────────┐\n",
              "│ author_id │        name         │ birth_year │ nationality │\n",
              "│   int32   │       varchar       │   int32    │   varchar   │\n",
              "├───────────┼─────────────────────┼────────────┼─────────────┤\n",
              "│         1 │ George Orwell       │       1903 │ British     │\n",
              "│         2 │ F. Scott Fitzgerald │       1896 │ American    │\n",
              "│         3 │ Harper Lee          │       1926 │ American    │\n",
              "│         4 │ Jane Austen         │       1775 │ British     │\n",
              "│         5 │ J.R.R. Tolkien      │       1892 │ British     │\n",
              "│         6 │ J.D. Salinger       │       1919 │ American    │\n",
              "│         7 │ J.K. Rowling        │       1965 │ British     │\n",
              "│         8 │ Dan Brown           │       1964 │ American    │\n",
              "│         9 │ Yuval Noah Harari   │       1976 │ Israeli     │\n",
              "│        10 │ Toni Morrison       │       1931 │ American    │\n",
              "├───────────┴─────────────────────┴────────────┴─────────────┤\n",
              "│ 10 rows                                          4 columns │\n",
              "└────────────────────────────────────────────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 136
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## **EXPORTING** Data to csv from SQL"
      ],
      "metadata": {
        "id": "Mvdrz-byERsa"
      },
      "id": "Mvdrz-byERsa"
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"EXPORT DATABASE './' (FORMAT CSV, DELIMITER ',');\"\"\"\n",
        "\n",
        "newdb.sql(query)"
      ],
      "metadata": {
        "id": "uaLxm5SyER0c"
      },
      "id": "uaLxm5SyER0c",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Reading csv files and importing data"
      ],
      "metadata": {
        "id": "_vhLRqgaNadg"
      },
      "id": "_vhLRqgaNadg"
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd"
      ],
      "metadata": {
        "id": "UtX09pVzNHvf"
      },
      "id": "UtX09pVzNHvf",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "import glob"
      ],
      "metadata": {
        "id": "UI6MXQ6XOl-7"
      },
      "id": "UI6MXQ6XOl-7",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "import glob\n",
        "csv_files = glob.glob('*.csv')"
      ],
      "metadata": {
        "id": "oxPrGT9ZOmDb"
      },
      "id": "oxPrGT9ZOmDb",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "csv_files"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "xax6CbXXOmH_",
        "outputId": "dcd29594-29fd-4910-86e8-2e217d5b8bf3"
      },
      "id": "xax6CbXXOmH_",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['books.csv', 'authors.csv']"
            ]
          },
          "metadata": {},
          "execution_count": 148
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Step 1: Connect to DuckDB (this creates an empty database in memory; specify a filename for a persistent database)"
      ],
      "metadata": {
        "id": "zK8i9E18PGs3"
      },
      "id": "zK8i9E18PGs3"
    },
    {
      "cell_type": "code",
      "source": [
        "dbfromcsv = duckdb.connect('library.duckdb')"
      ],
      "metadata": {
        "id": "ypmYz2DuPG6N"
      },
      "id": "ypmYz2DuPG6N",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Step 3: Process each CSV file\n",
        "for csv_file in csv_files:\n",
        "    # Read the CSV file into a pandas DataFrame\n",
        "    df = pd.read_csv(csv_file)\n",
        "\n",
        "    # Derive table name from CSV file name (optional)\n",
        "    table_name = csv_file.split('.')[0]  # This splits the filename from its extension and uses the name as the table name\n",
        "\n",
        "    # Create table and insert data\n",
        "    # DuckDB can directly execute pandas DataFrames\n",
        "    dbfromcsv.register('temp_df', df)\n",
        "    dbfromcsv.sql(f\"CREATE TABLE IF NOT EXISTS {table_name} AS SELECT * FROM temp_df WHERE false;\")  # Create table with schema\n",
        "    dbfromcsv.sql(f\"INSERT INTO {table_name} SELECT * FROM temp_df;\")\n",
        "    dbfromcsv.unregister('temp_df')"
      ],
      "metadata": {
        "id": "TuE4VQbWPHI4"
      },
      "id": "TuE4VQbWPHI4",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Step 4: Close the connection\n",
        "dbfromcsv.close()"
      ],
      "metadata": {
        "id": "1i7TdqwTPHVt"
      },
      "id": "1i7TdqwTPHVt",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "libdb = duckdb.connect('/content/library.duckdb')"
      ],
      "metadata": {
        "id": "_Q6QTAbQQQ_s"
      },
      "id": "_Q6QTAbQQQ_s",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "libdb.sql(\"SHOW TABLES;\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "DM24m4SQQREP",
        "outputId": "b4574489-5971-4fd0-fcaa-19082754d327"
      },
      "id": "DM24m4SQQREP",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌─────────┐\n",
              "│  name   │\n",
              "│ varchar │\n",
              "├─────────┤\n",
              "│ authors │\n",
              "│ books   │\n",
              "└─────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 153
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT * FROM books;\n",
        "\"\"\"\n",
        "\n",
        "libdb.sql(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "wBqJ5XF6QRHi",
        "outputId": "95316b03-28a9-4663-d4ed-f38cc7726ce2"
      },
      "id": "wBqJ5XF6QRHi",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌─────────┬───────────────────────────────────────┬─────────────────────┬────────────────┬────────────────────┐\n",
              "│ book_id │                 title                 │       author        │ published_year │       genre        │\n",
              "│  int64  │                varchar                │       varchar       │     int64      │      varchar       │\n",
              "├─────────┼───────────────────────────────────────┼─────────────────────┼────────────────┼────────────────────┤\n",
              "│       1 │ 1984                                  │ George Orwell       │           1949 │ Dystopian          │\n",
              "│       2 │ The Great Gatsby                      │ F. Scott Fitzgerald │           1925 │ Classic            │\n",
              "│       3 │ To Kill a Mockingbird                 │ Harper Lee          │           1960 │ Classic            │\n",
              "│       4 │ Pride and Prejudice                   │ Jane Austen         │           1813 │ Romance            │\n",
              "│       5 │ The Hobbit                            │ J.R.R. Tolkien      │           1937 │ Fantasy            │\n",
              "│       6 │ The Catcher in the Rye                │ J.D. Salinger       │           1951 │ Literary Fiction   │\n",
              "│       7 │ Harry Potter and the Sorcerer’s Stone │ J.K. Rowling        │           1997 │ Fantasy            │\n",
              "│       8 │ The Da Vinci Code                     │ Dan Brown           │           2003 │ Thriller           │\n",
              "│       9 │ Sapiens: A Brief History of Humankind │ Yuval Noah Harari   │           2011 │ Non-fiction        │\n",
              "│      10 │ Beloved                               │ Toni Morrison       │           1987 │ Historical Fiction │\n",
              "├─────────┴───────────────────────────────────────┴─────────────────────┴────────────────┴────────────────────┤\n",
              "│ 10 rows                                                                                           5 columns │\n",
              "└─────────────────────────────────────────────────────────────────────────────────────────────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 155
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "query = \"\"\"SELECT * FROM authors;\n",
        "\"\"\"\n",
        "\n",
        "libdb.sql(query)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "8FyjeoMtQdG7",
        "outputId": "9689d399-8c6f-4b3b-ab4e-114c82c835de"
      },
      "id": "8FyjeoMtQdG7",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "┌───────────┬─────────────────────┬────────────┬─────────────┐\n",
              "│ author_id │        name         │ birth_year │ nationality │\n",
              "│   int64   │       varchar       │   int64    │   varchar   │\n",
              "├───────────┼─────────────────────┼────────────┼─────────────┤\n",
              "│         1 │ George Orwell       │       1903 │ British     │\n",
              "│         2 │ F. Scott Fitzgerald │       1896 │ American    │\n",
              "│         3 │ Harper Lee          │       1926 │ American    │\n",
              "│         4 │ Jane Austen         │       1775 │ British     │\n",
              "│         5 │ J.R.R. Tolkien      │       1892 │ British     │\n",
              "│         6 │ J.D. Salinger       │       1919 │ American    │\n",
              "│         7 │ J.K. Rowling        │       1965 │ British     │\n",
              "│         8 │ Dan Brown           │       1964 │ American    │\n",
              "│         9 │ Yuval Noah Harari   │       1976 │ Israeli     │\n",
              "│        10 │ Toni Morrison       │       1931 │ American    │\n",
              "├───────────┴─────────────────────┴────────────┴─────────────┤\n",
              "│ 10 rows                                          4 columns │\n",
              "└────────────────────────────────────────────────────────────┘"
            ]
          },
          "metadata": {},
          "execution_count": 156
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# 🙏 **Thank You**"
      ],
      "metadata": {
        "id": "3cAx4BqnQdak"
      },
      "id": "3cAx4BqnQdak"
    }
  ],
  "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.11.5"
    },
    "colab": {
      "provenance": [],
      "include_colab_link": true
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}