collab_lab3.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyPvHi/P+AMYoupxw/OsbT4w",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/nrflynn2/swe-molecular-sciences/blob/main/collab_lab3.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Lab 3: Becoming Dangerous with SQL"
      ],
      "metadata": {
        "id": "Z2a6XiKKRc-e"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Review the lab material and go through the entire notebook. The lab contains 5 exercises for you to solve. The entire lab is worth 2.5% of your final grade and each exercise is worth 0.4% of your final grade. Going through the full notebook is worth 0.5% of your final grade. Any extra credit or bonus exercises are worth an additional 0.4%.\n",
        "\n",
        "Labs are due by Friday at 11:59 PM PST and can be submitted on BCourses assignment page for the corresponding lab."
      ],
      "metadata": {
        "id": "VNQMyCVJRsx3"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Data Persistence Techniques\n",
        "\n",
        "Persisting data is an important task, and not just for data science\n",
        "applications. Programs may need to persist data to ensure state, to\n",
        "share information, and to improve performance. As a result, many\n",
        "different approaches exist for saving data, spanning everything from\n",
        "basic file input/output techniques to enterprise level database\n",
        "management software. In this lab, we explore some of these different\n",
        "techniques with the goal of leveraging them to facilitate data science\n",
        "investigations.\n",
        "\n",
        "One of the simplest persistence techniques is basic file input/output. In Python, you can open a file for reading and writing and even use\n",
        "binary mode to save storage space (or even directly use a compression\n",
        "technique by using the appropriate Python library like bzip2). To\n",
        "recall, the following code segment demonstrates how to write data to a\n",
        "file called _test.dat_."
      ],
      "metadata": {
        "id": "rQhsaPx9RzTy"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "data = \"\"\"Data to write to the file, which can easily include any Python datatype\n",
        "by using string formatting techniques.\"\"\"\n",
        "\n",
        "with open('test.dat', 'w') as fout:\n",
        "    fout.write(data)"
      ],
      "metadata": {
        "id": "zn1nLVPGSMin"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "and we can easily read data back into a Python program (and in this\n",
        "example, simply echo the text to _STDOUT_) in a similar manner:\n"
      ],
      "metadata": {
        "id": "lkpfY7ceSYZG"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "with open('test.dat', 'r') as fin:\n",
        "    for line in fin:\n",
        "        print(line)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "lCd4hc4kSb_Z",
        "outputId": "2622989c-9ec7-4797-b369-792c1989c178"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Data to write to the file, which can easily include any Python datatype \n",
            "\n",
            "by using string formatting techniques.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "While this works, it is not optimal for several reasons:\n",
        "\n",
        "1. All data is written and read as Python strings. Complex arrangements\n",
        "of heterogenous data thus require potentially complex (and costly in\n",
        "execution time) transformations.\n",
        "\n",
        "2. All _concurrency_ is provides by the file system, thus we are not\n",
        "guaranteed consistent results if multiple writers work at the same time.\n",
        "\n",
        "3. Without extra effort, for example, to write to a binary file or to\n",
        "employ compression, this approach is costly in terms of storage space.\n",
        "\n",
        "4. We rely completely on the underlying file system for _consistency_ and\n",
        "_durability_. Thus, persisted application state may have unintentional\n",
        "dependencies on the underlying file system.\n",
        "\n",
        "An alternative approach is available for more advanced data structures,\n",
        "like the NumPy array.\n",
        "\n",
        "```python\n",
        "data = np.genfromtxt('rppdm/data/airports.csv', names=True,\n",
        "    delimiter=',', dtype=None, invalid_raise=False)\n",
        "```\n",
        "\n",
        "And if we develop our own data types, we can create our own custom\n",
        "input/output routines to read/write any new objects we have created. But\n",
        "this can be a lot of extra work, especially to verify the routines work\n",
        "accurately as a program is continually developed or maintained.\n",
        "Furthermore, this doesn't solve all of the problems as we still rely\n",
        "entirely on the file system to maintain concurrency, consistency, and\n",
        "durability."
      ],
      "metadata": {
        "id": "cMLG4_8JSqhw"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Pickling\n",
        "\n",
        "Python provides a simple technique, called _pickling_, that\n",
        "we can use to easily save data to a file and to later reconstitute the\n",
        "data into a Python program. Pickling writes the _class_ information for\n",
        "any data being written to the file along with the data. When you\n",
        "_unpickle_ data, this class information is used to properly reconstitute\n",
        "the data in the pickled file. Pickling is easy to use and can often\n",
        "suffice for simple data persistence tasks. To pickle data to a file, you\n",
        " must import the pickle module and open a file in binary writing mode.\n",
        "After this, simply call the `pickle.dump()` method with the data to\n",
        "write and the file stream."
      ],
      "metadata": {
        "id": "cXWe6zbETmS_"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import numpy as np\n",
        "import pickle as pkl\n",
        "\n",
        "data = np.random.rand(100)\n",
        "\n",
        "with open('test.p', 'wb') as fout:\n",
        "    pkl.dump(data, fout)"
      ],
      "metadata": {
        "id": "v3iDoDppTvCO"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Unpickling data is also easy, simply open the appropriate file in binary\n",
        "read mode and call the `pickle.load()` method to retrieve the data from\n",
        "the file and assign to a variable."
      ],
      "metadata": {
        "id": "Rt-5Wc-YTvqs"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "with open('test.p', 'rb') as fin:\n",
        "    newData = pkl.load(fin)\n",
        "\n",
        "print(newData[0:20:4])"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "8_2RDCqDVGGP",
        "outputId": "cc84d380-ee36-47d0-b432-65268461cf03"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[0.66683345 0.79688476 0.56419327 0.61638109 0.37892829]\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "!ls -l ."
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "HagchfgsVHxu",
        "outputId": "650d9889-df06-434a-fbb5-185c1f453aa0"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "total 12\n",
            "drwxr-xr-x 1 root root 4096 Sep 25 18:24 sample_data\n",
            "-rw-r--r-- 1 root root  111 Sep 28 20:18 test.dat\n",
            "-rw-r--r-- 1 root root  950 Sep 28 20:30 test.p\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "While easier than custom read/write routines, pickling still requires\n",
        "the file system to provide support for concurrency, consistency, and\n",
        "durability. To go any further with data persistence, we need to move\n",
        "beyond Python language constructs and employ additional software tools."
      ],
      "metadata": {
        "id": "8exD-UlMVRVW"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Database Systems\n",
        "\n",
        "Whether you realize it or not, as you surf the Internet you're\n",
        "interacting with a variety of database-backed Web applications. This\n",
        "nomenclature may be unfamiliar, but it simply means that a website you\n",
        "visit is dynamically created using data saved in a database. To\n",
        "demonstrate, consider the following types of Web sites that you may\n",
        "visit:\n",
        "\n",
        "- An information portal, like Google\n",
        "\n",
        "- A newspaper Web site to catch up on the local news or sports\n",
        "\n",
        "- A financial Web site, like that of a bank or investment institution,\n",
        "to monitor your financial portfolio\n",
        "\n",
        "- A map website to find driving directions\n",
        "\n",
        "- A search engine where you can identify interesting Web sites for more\n",
        "detailed information on a subject\n",
        "\n",
        "Each of these examples use databases to store, locate, and retrieve\n",
        "information dynamically. In each of these applications, the website\n",
        "collects necessary information from the user (such as a street address),\n",
        "queries the application database, and collects the data that has been\n",
        "requested into a suitable visual result.\n",
        "\n",
        "Many of these database systems are large and complex-imagine holding all\n",
        "the map information needed to provide accurate driving directions with\n",
        "pictures! Clearly, storing data and making it available to applications\n",
        "is a big task, one that has been addressed by a number of commercial\n",
        "vendors, that provide different solutions that are optimized for\n",
        "different tasks. Many of these open-source or commercial database\n",
        "systems provide full, enterprise-class capabilities. As a result, they\n",
        "can hold enormous quantities of data, concurrently interact with a large\n",
        "number of users, and scale across large computational systems.\n",
        "\n",
        "We can broadly classify these systems into two categories:\n",
        "\n",
        "1. Relational Database Management Systems like the open-source [MySQL][2] and [PostgreSQL][3], and commercial systems like [IBM DB2][4], [Microsoft SQL Server][5], or [Oracle Database][6] that rely on a tabular data model.\n",
        "\n",
        "2. NoSQL (or _Not only SQL_) systems that abondon the tabular data model to achieve a simpler design, better scaling or higher availability than is traditionally possible with relational databases. NoSQL databases can be classifid based on their data model, and include key-store databases like Amazon's [Dynamo][7], Object Databases like [ZopeDB][8], Document Store databases like [MongoDB][9], and Column Databases like  [Cassandra][10] or [HBase][11], which is an open source implementation of Google's [BigTable][bt] model.\n",
        "\n",
        "While the NoSQL databases are extremely interesting, many of them have\n",
        "been developed to meet the **big data** challenges faced by companies\n",
        "like Google, Facebook, or Amazon. For the rest of this lab,\n",
        "we will focus on relational database systems.\n",
        "\n",
        "[rdb]: https://en.wikipedia.org/wiki/Relational_database\n",
        "[nosql]: https://en.wikipedia.org/wiki/NoSQL\n",
        "[1]: http://yahoo.com\n",
        "[2]: https://www.mysql.com\n",
        "[3]: http://www.postgresql.org\n",
        "[4]: http://www-01.ibm.com/software/data/db2/\n",
        "[5]: http://www.microsoft.com/en-us/server-cloud/products/sql-server/\n",
        "[6]: https://www.oracle.com/database/index.html\n",
        "[7]: https://aws.amazon.com/dynamodb/\n",
        "[8]: http://www.zodb.org/en/latest/\n",
        "[9]: https://www.mongodb.org\n",
        "[bt]: https://en.wikipedia.org/wiki/BigTable\n",
        "[10]: https://cassandra.apache.org\n",
        "[11]: https://hbase.apache.org"
      ],
      "metadata": {
        "id": "UrqR8aRDVT89"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Database Roles\n",
        "\n",
        "As you might expect, working with these systems isn't trivial, and they\n",
        "can be expensive to operate. Historically, the tasks involved in working\n",
        "with these databases have been divided into three categories. Although\n",
        "the roles sometimes overlap, their individual responsibilities are easy\n",
        "to comprehend:\n",
        "\n",
        "**Database administrator (DBA)**: Responsible for the overall operation\n",
        "of the database system, which includes the selection and layout of the\n",
        "underlying hardware, the installation and optimization of the database\n",
        "server (especially given the hardware being used), and the day-to-day\n",
        "operations of the database server, such as data backup and recovery.\n",
        "\n",
        "**Database developer**: Responsible for the actual databases in\n",
        "operation, including designing databases, schemas, tables, table\n",
        "relationships, and indexes as well as optimizing queries.\n",
        "\n",
        "**Database application developer**: Responsible for integrating\n",
        "application code with the underlying database by using database\n",
        "application programming interfaces (APIs) to store and retrieve data as\n",
        "necessary.\n",
        "\n",
        "If the previous discussion leaves you feeling intimidated, that's OK,\n",
        "working with databases has historically been difficult. To understand\n",
        "why, let's examine a specific example in more detail: online banking.\n",
        "When you connect to your bank's Web site, you provide your credentials\n",
        "(most likely a username and password) and thereby gain access to your\n",
        "financial accounts. You can view your data, pay bills, and transfer\n",
        "funds. The database your bank uses must quickly locate the relevant\n",
        "information, safely manage the transactions, securely interact with\n",
        "users, and *most important* not lose any data! And the bank must do\n",
        "this for a large number of users concurrently. To ensure these tasks are\n",
        "performed correctly, relational database systems are given a special\n",
        "test, known as the **ACID Test**."
      ],
      "metadata": {
        "id": "4PcJIEKeV2Ss"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### The ACID Test\n",
        "\n",
        "Diamonds are obviously a valuable commodity, so valuable that\n",
        "counterfeits are a serious concern. One simple and (at least, in the\n",
        "movies) popular test to determine whether a diamond is real is to run it\n",
        "across a piece of glass. Because diamonds are one of the hardest\n",
        "materials known, a real diamond easily cuts the glass surface; a fake,\n",
        "especially if it's made of glass itself, won't. Similarly, we might check whether a Persian rug is counterfit by trying to light it on fire (sorry Aunt Suzie, but at least you now know it's a fake!).\n",
        "\n",
        "To a software developer, databases are equally valuable. If you use a\n",
        "database, you want to be sure it will safely store your data and let you\n",
        "easily retrieve the data later. You also want your database to allow\n",
        "multiple programs (or people) to work with the database without\n",
        "interfering with each other. To demonstrate, imagine you own a bank. The\n",
        "database for your bank must do the following, among other things:\n",
        "\n",
        "- Safely store the appropriate data\n",
        "- Quickly retrieve the appropriate data\n",
        "- Support multiple, concurrent user sessions\n",
        "\n",
        "These tasks can be collectively referred to as the ACID test; ACID is an\n",
        "acronym for Atomicity, Consistency, Isolation, and Durability.\n",
        "\n",
        "**Atomicity** means that operations with the database can be grouped\n",
        "together and treated as a single unit.\n",
        "\n",
        "**Consistency** guarantees that either all the operations in this single\n",
        "unit (or transaction) are performed successfully, or none of them is\n",
        "performed. In other words, a database can't be in an unfinished state.\n",
        "To understand why these characteristics are important, think about a\n",
        "bank transaction during which money is transferred from a savings\n",
        "account into a checking account. If the transfer process fails after\n",
        "subtracting the money from your savings account and before it was added\n",
        "to your checking account, you would become poorer, and the bank would\n",
        "have an angry (ex)customer! Atomicity enables the two operations -- the\n",
        "subtraction from the savings account and the addition to the checking\n",
        "account -- to be treated as a single transaction. Consistency guarantees\n",
        "that both operations in the transaction either succeed or fail. That\n",
        "way, your money isn't lost.\n",
        "\n",
        "**Isolation** means that independent sets of database transactions are\n",
        "performed in such a way that they don't conflict with each other.\n",
        "Continuing the bank analogy, consider two customers who transfer funds\n",
        "between accounts at the same time. The database must track both\n",
        "transfers separately; otherwise, the funds could go into the wrong\n",
        "accounts, and the bank might be left with two angry (ex)customers.\n",
        "\n",
        "**Durability** guarantees that the database is safe against unexpected\n",
        "terminations. It may be a minor inconvenience if your television or\n",
        "computer won't work when the power goes out, but the same can't be said\n",
        "for a database. If the bank's computers lose power when transferring\n",
        "your funds, you won't be a happy customer if the transaction is lost.\n",
        "Durability guarantees that if the database terminates abnormally during\n",
        "a funds transfer, then when the database is brought back up, it will be\n",
        "able to recover the transaction and continue with normal operations.\n",
        "\n",
        "Passing the ACID test is nontrivial, and many simple databases fall\n",
        "short. For critical e-business or Web-based applications, passing the\n",
        "ACID test is a must. This is one of the reasons so many companies and\n",
        "individuals utilize enterprise-level database systems, such as IBM DB2,\n",
        "Oracle Database, or Microsoft SQL Server. These databases are fully\n",
        "compliant with the ACID test, and can meet many of the data persistence\n",
        "needs of large corporations or organizations. To do so, however, often\n",
        "requires a large team that includes database administrators, database\n",
        "developers, and database application developers to ensure that data is\n",
        "effectively persisted and available as necessary for business\n",
        "applications."
      ],
      "metadata": {
        "id": "7h3ZrUjqWCP-"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### SQLite\n",
        "\n",
        "But not all applications are this demanding, especially when you're\n",
        "starting out and trying to learn the basic relational database concepts.\n",
        "If you're just learning to work with databases, or if you want to\n",
        "quickly prototype a database application, most commercial database\n",
        "systems can be cumbersome. Fortunately, open-source, ACID compliant\n",
        "database systems exist, including the zero-configuration, serverless\n",
        "relational database system known as [SQLite][1]. By using SQLite, you\n",
        "can learn to work with a relational database by using SQL as well as the\n",
        "Python programming language. If you later find your application needs\n",
        "require a more powerful database system, you can always migrate your\n",
        "efforts to a more powerful database system; however, many systems\n",
        "continue to embed SQLite within their own applications, as demonstrated\n",
        "in the following webpage.\n",
        "\n",
        "[1]: https://www.sqlite.org"
      ],
      "metadata": {
        "id": "RxluM--vWoBn"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from IPython.display import HTML\n",
        "HTML('<iframe src=https://www.sqlite.org/famous.html width=750 height=300></iframe>')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 360
        },
        "id": "hc47-sM3V_4D",
        "outputId": "4421a4b5-aca2-45b3-dabd-96a5196bccec"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.10/dist-packages/IPython/core/display.py:724: UserWarning: Consider using IPython.display.IFrame instead\n",
            "  warnings.warn(\"Consider using IPython.display.IFrame instead\")\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ],
            "text/html": [
              "<iframe src=https://www.sqlite.org/famous.html width=750 height=300></iframe>"
            ]
          },
          "metadata": {},
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### What is SQLite?\n",
        "\n",
        "SQLite is quite different than traditional relational database\n",
        "systems. SQLite does not have a separate server process, instead SQLite\n",
        "is a software library that, as the website states:\n",
        "\n",
        "> implements a self-contained, serverless, zero-configuration,\n",
        "> transactional SQL database engine.\n",
        "\n",
        "Before progressing, lets examine each of these concepts in turn:\n",
        "\n",
        "- *self-contained*: Nothing else is needed to use SQLite but the\n",
        "software library. Since, by default,  this comes with Python, we can use\n",
        "SQLite without any additional software downloads or installs. in\n",
        "addition, if you want to embed SQLite in your own application, you can\n",
        "obtain a single ANSI-C file that contains the entire SQLite library.\n",
        "\n",
        "- *serverless*: We interact with the SQLite database by using the\n",
        "SQLite library. The database is stored in a single file that is platform\n",
        "independent (so you can simply copy it over to a new machine with no\n",
        "further effort).\n",
        "\n",
        "- *zero-configuration*: SQLite does not use a server process, so there\n",
        "is no configuration required. While you can customize sqlite to change\n",
        "[default limits][1], for most applications this is unnecessary. You can\n",
        "also pre-specify certain options for the `sqlite3` command line client\n",
        "in a separate configuration file (e.g., `.sqliterc`, which is located in\n",
        "the current user's home directory).\n",
        "\n",
        "- *transactional*: A transaction is a logical set of operations. SQLite\n",
        "is ACID-complaint by implementing [atomic commits][2], which means that\n",
        "either every operation within the transaction completes successfully or\n",
        "none of them do. No partial writes are persisted, so that the database\n",
        "is always in a consistent state.\n",
        "\n",
        "With this power, it is even more surprising that the SQLite library is\n",
        "quite small, and can be compacted to as small as **300 kb** if required.\n",
        "\n",
        "SQLite by default will store data in a single database file; however, it\n",
        "can also be used as an _in memory_ database. SQLite has been distributed\n",
        "as a component within the Python language for many years, but also has a\n",
        "stand-alone command line interface client, called `sqlite3` that we will\n",
        "use in this lesson use to create a database, create schema within that\n",
        "database, and to import data.\n",
        "\n",
        "[1]: https://www.sqlite.org/limits.html\n",
        "[2]: https://www.sqlite.org/atomiccommit.html\n",
        "\n",
        "### Additional References\n",
        "\n",
        "1. [SQLite Documentation][1]\n",
        "2. [Free SQLite][2] Tutorial\n",
        "\n",
        "[1]: http://www.sqlite.org/docs.html\n",
        "[2]: http://zetcode.com/db/sqlite/"
      ],
      "metadata": {
        "id": "vSBGOPsSW6zw"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Introduction to SQL\n",
        "\n",
        "We now focus on a basic component of relational database\n",
        "management systems, SQL. In this Notebook we will use the SQLite\n",
        "database to build a fictitious database. We will cover SQL data types and\n",
        "how to create SQL schemas before moving on to creating and executing\n",
        "queries and finishing with updating and deleting data.\n",
        "\n",
        "We can now start using the SQLite database. First we will test SQLite\n",
        "from within the IPython Notebook, before switching to the command line\n",
        "to actually create and populate a database. Note that if we run the\n",
        "`sqlite3` command line client from within an IPython Notebook cell, the\n",
        "process will continue to run _in the background_ since we can not\n",
        "directly enter commands. Thus, you should either redirect _STDIN_ for\n",
        "the `sqlite3` client to be a file of commands, or work directly with\n",
        "this tool at the command line.\n",
        "\n",
        "### Working with SQLite\n",
        "\n",
        "By default, the `sqlite3` command line client will operate in\n",
        "interactive mode. However, this tool will also read and execute commands\n",
        "either in from a separate file by redirecting _STDIN_ or by enclosing\n",
        "the commands in quotes. Since SQLite databases are files, unless\n",
        "explicitly created from within a program as in memory databases, we pass\n",
        "the name of the database as a command line argument. Thus, to connect to\n",
        "a database with the `sqlite3` command line client in\n",
        "interactive mode, we simply enter the following at a command prompt in\n",
        "our Docker container:\n",
        "\n",
        "```\n",
        "/home/database: $ sqlite3 <name of database>\n",
        "SQLite version 3.8.2 2013-12-06 14:53:30\n",
        "Enter \".help\" for instructions\n",
        "Enter SQL statements terminated with a \";\"\n",
        "sqlite>\n",
        "```\n",
        "\n",
        "To exit from the `sqlite3` client, simply enter either ctrl-d or use the\n",
        "`.quit` command. The `sqlite3` client can either accept SQL commands,\n",
        "which we will discus in more detail in the next lesson, or the client\n",
        "can accept dot commands, which are instructions to the SQLite database\n",
        "engine that begin with a `.` character. These commands can be explicitly\n",
        "listed by entering `.help` at the `sqlite3` client prompt. We can do\n",
        "this from within our Notebook by creating and using a file as shown below.\n"
      ],
      "metadata": {
        "id": "A_Dk2CPLXSu3"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!apt-get install sqlite3"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "jDw7nWqZZu2j",
        "outputId": "3a6a1c58-7d80-4c6a-a5e3-e50932f741db"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Reading package lists... Done\n",
            "Building dependency tree... Done\n",
            "Reading state information... Done\n",
            "Suggested packages:\n",
            "  sqlite3-doc\n",
            "The following NEW packages will be installed:\n",
            "  sqlite3\n",
            "0 upgraded, 1 newly installed, 0 to remove and 49 not upgraded.\n",
            "Need to get 768 kB of archives.\n",
            "After this operation, 1,873 kB of additional disk space will be used.\n",
            "Get:1 http://archive.ubuntu.com/ubuntu jammy-updates/main amd64 sqlite3 amd64 3.37.2-2ubuntu0.3 [768 kB]\n",
            "Fetched 768 kB in 1s (785 kB/s)\n",
            "Selecting previously unselected package sqlite3.\n",
            "(Reading database ... 123614 files and directories currently installed.)\n",
            "Preparing to unpack .../sqlite3_3.37.2-2ubuntu0.3_amd64.deb ...\n",
            "Unpacking sqlite3 (3.37.2-2ubuntu0.3) ...\n",
            "Setting up sqlite3 (3.37.2-2ubuntu0.3) ...\n",
            "Processing triggers for man-db (2.10.2-1) ...\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Run the SQLite command line client\n",
        "\n",
        "!sqlite3 -help"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "8DQbtoL4W2Oq",
        "outputId": "fc6b9cb1-a95d-4937-98ef-ce077163d450"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Usage: sqlite3 [OPTIONS] FILENAME [SQL]\n",
            "FILENAME is the name of an SQLite database. A new database is created\n",
            "if the file does not previously exist.\n",
            "OPTIONS include:\n",
            "   -A ARGS...           run \".archive ARGS\" and exit\n",
            "   -append              append the database to the end of the file\n",
            "   -ascii               set output mode to 'ascii'\n",
            "   -bail                stop after hitting an error\n",
            "   -batch               force batch I/O\n",
            "   -box                 set output mode to 'box'\n",
            "   -column              set output mode to 'column'\n",
            "   -cmd COMMAND         run \"COMMAND\" before reading stdin\n",
            "   -csv                 set output mode to 'csv'\n",
            "   -deserialize         open the database using sqlite3_deserialize()\n",
            "   -echo                print commands before execution\n",
            "   -init FILENAME       read/process named file\n",
            "   -[no]header          turn headers on or off\n",
            "   -help                show this message\n",
            "   -html                set output mode to HTML\n",
            "   -interactive         force interactive I/O\n",
            "   -json                set output mode to 'json'\n",
            "   -line                set output mode to 'line'\n",
            "   -list                set output mode to 'list'\n",
            "   -lookaside SIZE N    use N entries of SZ bytes for lookaside memory\n",
            "   -markdown            set output mode to 'markdown'\n",
            "   -maxsize N           maximum size for a --deserialize database\n",
            "   -memtrace            trace all memory allocations and deallocations\n",
            "   -mmap N              default mmap size set to N\n",
            "   -newline SEP         set output row separator. Default: '\\n'\n",
            "   -nofollow            refuse to open symbolic links to database files\n",
            "   -nonce STRING        set the safe-mode escape nonce\n",
            "   -nullvalue TEXT      set text string for NULL values. Default ''\n",
            "   -pagecache SIZE N    use N slots of SZ bytes each for page cache memory\n",
            "   -quote               set output mode to 'quote'\n",
            "   -readonly            open the database read-only\n",
            "   -safe                enable safe-mode\n",
            "   -separator SEP       set output column separator. Default: '|'\n",
            "   -stats               print memory stats before each finalize\n",
            "   -table               set output mode to 'table'\n",
            "   -tabs                set output mode to 'tabs'\n",
            "   -version             show SQLite version\n",
            "   -vfs NAME            use NAME as the default VFS\n",
            "   -zip                 open the file as a ZIP Archive\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%%writefile help.txt\n",
        ".help"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "3ilkL-l1a5yh",
        "outputId": "b7dc4d5b-76ab-436d-a4cc-b608ff0dfef6"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Writing help.txt\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# List 'dot' commands\n",
        "\n",
        "!sqlite3 < help.txt"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ofqxzshja6mh",
        "outputId": "e35614e9-ce1c-4b94-d10c-1fd92fbfcac6"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            ".archive ...             Manage SQL archives\n",
            ".auth ON|OFF             Show authorizer callbacks\n",
            ".backup ?DB? FILE        Backup DB (default \"main\") to FILE\n",
            ".bail on|off             Stop after hitting an error.  Default OFF\n",
            ".binary on|off           Turn binary output on or off.  Default OFF\n",
            ".cd DIRECTORY            Change the working directory to DIRECTORY\n",
            ".changes on|off          Show number of rows changed by SQL\n",
            ".check GLOB              Fail if output since .testcase does not match\n",
            ".clone NEWDB             Clone data into NEWDB from the existing database\n",
            ".connection [close] [#]  Open or close an auxiliary database connection\n",
            ".databases               List names and files of attached databases\n",
            ".dbconfig ?op? ?val?     List or change sqlite3_db_config() options\n",
            ".dbinfo ?DB?             Show status information about the database\n",
            ".dump ?OBJECTS?          Render database content as SQL\n",
            ".echo on|off             Turn command echo on or off\n",
            ".eqp on|off|full|...     Enable or disable automatic EXPLAIN QUERY PLAN\n",
            ".excel                   Display the output of next command in spreadsheet\n",
            ".exit ?CODE?             Exit this program with return-code CODE\n",
            ".expert                  EXPERIMENTAL. Suggest indexes for queries\n",
            ".explain ?on|off|auto?   Change the EXPLAIN formatting mode.  Default: auto\n",
            ".filectrl CMD ...        Run various sqlite3_file_control() operations\n",
            ".fullschema ?--indent?   Show schema and the content of sqlite_stat tables\n",
            ".headers on|off          Turn display of headers on or off\n",
            ".help ?-all? ?PATTERN?   Show help text for PATTERN\n",
            ".import FILE TABLE       Import data from FILE into TABLE\n",
            ".imposter INDEX TABLE    Create imposter table TABLE on index INDEX\n",
            ".indexes ?TABLE?         Show names of indexes\n",
            ".limit ?LIMIT? ?VAL?     Display or change the value of an SQLITE_LIMIT\n",
            ".lint OPTIONS            Report potential schema issues.\n",
            ".load FILE ?ENTRY?       Load an extension library\n",
            ".log FILE|off            Turn logging on or off.  FILE can be stderr/stdout\n",
            ".mode MODE ?TABLE?       Set output mode\n",
            ".nonce STRING            Disable safe mode for one command if the nonce matches\n",
            ".nullvalue STRING        Use STRING in place of NULL values\n",
            ".once ?OPTIONS? ?FILE?   Output for the next SQL command only to FILE\n",
            ".open ?OPTIONS? ?FILE?   Close existing database and reopen FILE\n",
            ".output ?FILE?           Send output to FILE or stdout if FILE is omitted\n",
            ".parameter CMD ...       Manage SQL parameter bindings\n",
            ".print STRING...         Print literal STRING\n",
            ".progress N              Invoke progress handler after every N opcodes\n",
            ".prompt MAIN CONTINUE    Replace the standard prompts\n",
            ".quit                    Exit this program\n",
            ".read FILE               Read input from FILE\n",
            ".recover                 Recover as much data as possible from corrupt db.\n",
            ".restore ?DB? FILE       Restore content of DB (default \"main\") from FILE\n",
            ".save FILE               Write in-memory database into FILE\n",
            ".scanstats on|off        Turn sqlite3_stmt_scanstatus() metrics on or off\n",
            ".schema ?PATTERN?        Show the CREATE statements matching PATTERN\n",
            ".selftest ?OPTIONS?      Run tests defined in the SELFTEST table\n",
            ".separator COL ?ROW?     Change the column and row separators\n",
            ".session ?NAME? CMD ...  Create or control sessions\n",
            ".sha3sum ...             Compute a SHA3 hash of database content\n",
            ".shell CMD ARGS...       Run CMD ARGS... in a system shell\n",
            ".show                    Show the current values for various settings\n",
            ".stats ?ARG?             Show stats or turn stats on or off\n",
            ".system CMD ARGS...      Run CMD ARGS... in a system shell\n",
            ".tables ?TABLE?          List names of tables matching LIKE pattern TABLE\n",
            ".testcase NAME           Begin redirecting output to 'testcase-out.txt'\n",
            ".testctrl CMD ...        Run various sqlite3_test_control() operations\n",
            ".timeout MS              Try opening locked tables for MS milliseconds\n",
            ".timer on|off            Turn SQL timer on or off\n",
            ".trace ?OPTIONS?         Output each SQL statement as it is run\n",
            ".vfsinfo ?AUX?           Information about the top-level VFS\n",
            ".vfslist                 List all available VFSes\n",
            ".vfsname ?AUX?           Print the name of the VFS stack\n",
            ".width NUM1 NUM2 ...     Set minimum column widths for columnar output\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### SQLite Cliet Tool Options\n",
        "\n",
        "The previous code block listed the available dot commands that we can\n",
        "use within the `sqlite3` client. We will use several of these, including\n",
        "\n",
        "- `.header`: If this is `on`, headers will be displayed. Generally this\n",
        "is used to see the names of columns in a table.\n",
        "\n",
        "- `.separator`: This specifies the separator character for fields, either\n",
        "in output displays or when importing data. By default, the separator\n",
        "character is the vertical bar, `|`. However, to import data from a\n",
        "comma-separated-value (CSV) file, you would change the separator to a\n",
        "comma, `.separator \",\"`.\n",
        "\n",
        "- `.import`: This command is used to read data from a file and insert\n",
        "this data into a specific table in the database.\n",
        "\n",
        "- `.schema`: This command will list the schema commands required to\n",
        "recreate the tables contained in the database.\n",
        "\n",
        "- `.stats`: This command lists statistics after each command entered at\n",
        "the sqlite prompt. This can be useful for profiling SQL commands.\n",
        "\n",
        "- `.width`: This command changes the default width for columns, which\n",
        "can improve the visual formatting of the results from database queries\n",
        "that are displayed to the screen.\n",
        "\n",
        "### Creating and Populating a Database\n",
        "\n",
        "We can easily create and populate a database by using the `sqlite3`\n",
        "client. While we could do this at the command line (and advanced users\n",
        "are encouraged to do so), we can also complete these tasks from within\n",
        "this IPython Notebook. The steps we must complete include\n",
        "\n",
        "1. Create the new database. We do this by simply passing the name of our\n",
        "new database to the `sqlite3` client. If the file does not exist, a new\n",
        "file will be created. This file will hold the entire contents of the new\n",
        "database.\n",
        "\n",
        "2. Create the schema for our new database. A relational database is\n",
        "built on a tabular data model. Thus our schema consists of the table\n",
        "definitions as well as the relationships that might exist between\n",
        "tables. To accomplish this, we must execute SQL `CREATE TABLE`\n",
        "statements. For now, we simply create the schema, the next lesson\n",
        "explores SQL statements in more detail. A schema file for the airline\n",
        "data is included in this lesson, under the `schema` directory.\n",
        "\n",
        "3. Populate the tables with data. For simplicity, we will use the\n",
        "`.import` command within the `sqlite3` client to import data from a file\n",
        "directly into the relevant table in our database."
      ],
      "metadata": {
        "id": "pz9ZPvzmbGDk"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!pwd"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "JuZgPLulbaMU",
        "outputId": "10902efa-c1aa-4c66-8573-4b17b0da7cf9"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "/content\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# First we make a new directory to hold our database\n",
        "\n",
        "!mkdir /content/database"
      ],
      "metadata": {
        "id": "Zd0tRBCObWlw"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "!ls -l /content/database"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "BpV__0hMbhss",
        "outputId": "65c54eac-642c-4c9a-e8b5-6bf90f610f79"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "total 0\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# We could create a schema SQL file, then use Bulk imort to load the databases."
      ],
      "metadata": {
        "id": "dypOwKnJbsLp"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### The basics of relational database systems\n",
        "\n",
        "Before you can begin to develop database applications, you need to\n",
        "understand the basic concepts. Relational databases hold data. This data\n",
        "can be of different types, such as numbers, characters, or dates. Within\n",
        "the database, the data are organized into logical units called tables. A\n",
        "table is like a spreadsheet, because it contains rows of data. Each row\n",
        "is made up of a number of columns. The columns hold data of a specific\n",
        "data type, like integer values or strings of characters. In most cases,\n",
        "a database has more than one table. To relate the tables together, a\n",
        "database designer takes advantage of natural (or artificial) links\n",
        "between the tables. In a spreadsheet, you can link rows in different\n",
        "sheets by cell values. The same idea holds in a relational database, and\n",
        "the column used to make the link is referred to as a key column.\n",
        "\n",
        "To make it easier to understand the purpose of a table or a particular\n",
        "column, you should select appropriate names. The naming conventions can\n",
        "vary between databases. For a SQLite database, individual names:\n",
        "\n",
        "- by default, case insensitive (although this can be changed),\n",
        "\n",
        "- are unlimited in length, but should be kept to a reasonable length\n",
        "(given readability constraints),\n",
        "\n",
        "- must begin with a letter or an underscore character, and\n",
        "\n",
        "- must only code alphanumeric characters or underscores.\n",
        "\n",
        "You can escape these rules by placing the name in double quotation\n",
        "marks, which allows names to be case sensitive and to include additional\n",
        "characters (including spaces). Doing this, however, is generally a bad\n",
        "practice: It requires the name to always be enclosed in double quotation\n",
        "marks and can easily confuse someone else who may be maintaining your\n",
        "code. Finally, a name can not be one of the reserved [keywords](https://www.sqlite.org/lang_keywords.html).\n",
        "\n",
        "#### Class Style\n",
        "\n",
        "For this lab, we will follow a specific style: All SQL commands are\n",
        "presented entirely in uppercase, and item names use camelCase. In\n",
        "camelCase style, words are joined together, and the first letter of each\n",
        "word-following the first one-is capitalized, such as aLongIdentifier.\n",
        "Combining these two styles together, these articles write SQL commands\n",
        "using the following style: SELECT aLongIdentifier FROM dataTable ;.\n",
        "\n",
        "#### Schema\n",
        "\n",
        "Related tables are often grouped together into a schema. You can think\n",
        "of a schema as a container for all the related structure definitions\n",
        "within a particular database. A table name must be unique within a given\n",
        "schema. Thus, by using schemas, you can have identically named objects\n",
        "(such as tables) enclosed within different schemas.\n",
        "\n",
        "You can use the schema name to qualify a name. By default, SQLite uses\n",
        "the database name as the schema, and you do not need to prefix names to\n",
        "indicate the correct schema. For other databases, however, this is not\n",
        "the case. For these databases, you specify the schema name followed by a\n",
        "period and then the table name. For example, bigdog.products references\n",
        "the products table in the bigdog schema. Without the relevant schema\n",
        "name, a table name is said to be unqualified, as in products. When the\n",
        "schema name and the table name are completely specified, as in\n",
        "bigdog.products, the name is said to be fully qualified.\n",
        "\n",
        "In an abstract sense, these database concepts may seem confusing, but in\n",
        "practice they're fairly straightforward. For example, imagine you own a\n",
        "store called Bigdog's Surf Shop that sells a variety of items like\n",
        "sunglasses, shirts, and so on. If you want to be profitable, you must\n",
        "keep a close eye on your inventory so you can easily order additional\n",
        "inventory or change vendors to keep your overhead to a minimum. One\n",
        "simple method for tracking this information is to write entries in a\n",
        "table-like format:\n",
        "\n",
        "**<DIV ALIGN=CENTER>Product Table </DIV>**\n",
        "\n",
        "| Item# | Price | Stock Date | Description |\n",
        "| ---- | ----- | ---------- | ----------- |\n",
        "| 1 | 29.95 | 1/15/15 | Basic Sunglasses |\n",
        "| 2 | 9.95 | 12/14/14 | Generic Shirt |\n",
        "| 3 | 99.95 | 8/04/14 | Boogie Board |\n",
        "\n",
        "**<DIV ALIGN=CENTER>Vendors Table </DIV>**\n",
        "\n",
        "| Item# | Vendor# | Vendor Name |\n",
        "| ---- | ----- | ---------- |\n",
        "| 1 | 101 | Mikal Arroyo |\n",
        "| 2 | 102 | Quiet Beach Industries |\n",
        "| 3 | 103 | Vista Luna |\n",
        "\n",
        "\n",
        "From this simple visual design you can easily map the business logic\n",
        "straight into database tables. You have two database tables, Products\n",
        "and Vendors, which are naturally linked by the item number. The data\n",
        "types for the columns in each table are easy to determine. Later in this\n",
        "lesson we will actually create this sample schema for Bigdog's Surf\n",
        "Shop, which consists of these two tables, in a SQLite database. But\n",
        "first, we need to address how data is stored in a relational database\n",
        "table."
      ],
      "metadata": {
        "id": "dNS88WqnbtVR"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### SQL: Structured Query Language\n",
        "\n",
        "Database systems can be complex pieces of software, especially when they\n",
        "scale to support enterprise-level applications. As a result, you may\n",
        "expect that every database has its own application programming interface\n",
        "(API) and that these APIs may be different from one system to the next.\n",
        "When relational databases were first developed, this was the case; but,\n",
        "fortunately, a number of vendors agreed to develop a standard language\n",
        "for accessing and manipulating relational databases. This language is\n",
        "officially called Structured Query Language (or SQL, pronounced\n",
        "sea-quill). Several official standard versions have been produced,\n",
        "including one in 1992 that is referred to as SQL-92, and one in 1999\n",
        "that is referred to as SQL-99. The Apache Derby database provides a\n",
        "nearly complete implementation of the SQL-92 standard, so applications\n",
        "developed with Derby can be easily transported to other database systems.\n",
        "\n",
        "SQL has two main components: a Data Definition Language (DDL) and a Data\n",
        "Manipulation Language (DML). DDL commands are used to create, modify, or\n",
        "delete items (such as tables) in a database. DML commands are used to\n",
        "add, modify, delete, or select data from a table in the database. The\n",
        "rest of this article provides a basic introduction to the DDL components\n",
        "of SQL. Future articles will focus on the DML commands and more advanced\n",
        "DDL commands.\n",
        "\n",
        "### SQL data types\n",
        "\n",
        "SQL, being a programming language in its own right, defines a rich\n",
        "data-type hierarchy. Persisting these data types is one of the most\n",
        "important responsibilities of the database. As databases have become\n",
        "more powerful, this type hierarchy has grown more complex. But most\n",
        "simple databases don't require the full range of allowed types, and\n",
        "often they need to store only numerical, character, and date or time\n",
        "data.\n",
        "\n",
        "While the SQL standard defines basic [data types][1], different database\n",
        "systems can support the standard to varying degrees. While this might\n",
        "seem odd, doing so provides more flexibility in allowing a particular\n",
        "implementation to achieve a market niche. In the case of SQLite, the\n",
        "design decisions support a compact, zero-configuration database file\n",
        "that is platform-independent. As a result, [SQLite does not support][2]\n",
        "a rich data type hierarchy, and instead focuses on ease-of-use.\n",
        "\n",
        "SQLite supports five storage classes:\n",
        "\n",
        "- **NULL**: A null value.\n",
        "\n",
        "- **INTEGER**: A signed integer, the number of bytes (1, 2, 3, 4, 6, or\n",
        "8) used depends on the magnitude of the value.\n",
        "\n",
        "- **REAL**: A floating-point value stored as an 8 byte IEEE\n",
        "floating-point value.\n",
        "\n",
        "- **TEXT**: A string of character values stored in the default database\n",
        "encoding (e.g., UTF-8).\n",
        "\n",
        "- **BLOB**: A blob of data stored *exactly* as is in the database.\n",
        "\n",
        "Note that SQLite does not support Boolean or Date/Time valus directly.\n",
        "Instead, Boolean values are encoded as INTEGERs (0 = False, 1 = True).\n",
        "Likewise Date/Time values can be encoded either as TEXT, REAL, or\n",
        "INTEGER values. For full details, see the SQLite documentation. In\n",
        "addition, SQLite supports the concept of _Type Affinity_, whereby\n",
        "different data types can be easily mapped into each other. This\n",
        "simplifies moving schemas from other database systems to SQLite.\n",
        "\n",
        "[1]: https://en.wikipedia.org/wiki/SQL#Data_types\n",
        "[2]: https://www.sqlite.org/datatype3.html"
      ],
      "metadata": {
        "id": "Oym7Ksj2cnul"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### The SQL NULL type\n",
        "\n",
        "Before you begin creating database tables, you must know what to do when no value is specified for a column. To illustrate this point, imagine that you've been asked to fill out a Web form. If you leave a particular column blank, what is inserted into the database? As you can imagine, this problem could be cumbersome if you had to track no value markers. Fortunately, SQL defines a special value, NULL, to indicate that a column has no value."
      ],
      "metadata": {
        "id": "HnROeTUZcx23"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### CREATE TABLE\n",
        "\n",
        "So far, you've learned how to design a table, including mapping out the\n",
        "table columns and defining the data type for each column. After you've\n",
        "properly designed a table, the method for creating a table in SQL is\n",
        "straightforward. Listing 1 shows the formal syntax for creating a table\n",
        "in Derby.\n",
        "\n",
        "```\n",
        "-- Comment describing the purpose and layout of the table\n",
        "\n",
        "CREATE TABLE tableName ( { <columnDefinition> | <tableLevelConstraint> }\n",
        "    [, { <columnDefinition> | <tableLevelConstraint> } ]* ) ;\n",
        "```\n",
        "\n",
        "You may feel bewildered after looking at this syntax for the first time.\n",
        "But it's easy to follow once you have the basics down. The square\n",
        "brackets ([ and ]) enclose optional parameters. As you can see from the\n",
        "formal syntax, any column definitions or table-level constraints after\n",
        "the required initial one (it wouldn't make sense to create a table with\n",
        "no columns!) are optional.\n",
        "\n",
        "You probably understand what is meant by a column definition, but you\n",
        "might not understand the idea of a constraint. Constraints come in two\n",
        "types: table-level constraints and column constraints. A constraint\n",
        "limits either a column or a table in some manner. For example, you can\n",
        "use a constraint to require that a column always be assigned an actual\n",
        "value (no NULL values), or that every entry in a column must be unique,\n",
        "or that a column is automatically assigned a default value.\n",
        "\n",
        "The asterisk (\\*) after the last closing square bracket indicates that\n",
        "more than one of the enclosing items can be included. This implies that\n",
        "the table must have one or more columns or table-level constraints. The\n",
        "vertical line (|) indicates an either/or condition. In this syntax\n",
        "example, you must either define a new column or define a new table-level\n",
        "constraint. The curly brackets ({ and }) group related items together,\n",
        "and the parentheses (( and )) are required elements. Finally, the\n",
        "semicolon (;) indicates the end of a SQL statement.\n",
        "\n",
        "#### SQLite Schema Creation\n",
        "\n",
        "In the following code block, we create our schema for Bigdog's Surf\n",
        "Shop, which includes two new tables: `myProducts` and `myVendors`. The\n",
        "myProducts table has four columns: itemNumber, price, stockDate, and\n",
        "description. The itemNumber column provides a unique identity for each\n",
        "item (or row) and has an attached column-level constraint that enforces\n",
        "a valid value to always be supplied (NOT NULL). Without this\n",
        "requirement, the itemNumber column isn't guaranteed to be unique because\n",
        "multiple columns could be assigned a NULL value. The price column is\n",
        "created as a REAL data type. The last two columns are simple: The\n",
        "stockDate column is stored as a TEXT (we can use application logic to\n",
        "transform the date/time information into and out of the appropriate\n",
        "YYYY-MM-DD format), and description is also stored in a TEXT field.\n",
        "\n",
        "The myVendors table has three columns: itemNumber, vendorNumber, and\n",
        "vendorName. In this case, both the itemNumber and vendorNumber columns\n",
        "have attached column-level constraints (NOT NULL). In addition, the\n",
        "vendorName column is stored as a TEXT field."
      ],
      "metadata": {
        "id": "BrZFX3fYc-j-"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Drop Table\n",
        "\n",
        "No one is perfect. What do you do when you incorrectly create a table or\n",
        "a table is no longer needed? The simple answer is to delete the table\n",
        "from the database and, if necessary, create a replacement table.\n",
        "Deleting a table is easy, which means, of course, that you should\n",
        "exercise great care when doing so -- no dialog box pops up and asks if\n",
        "you're sure you want to proceed!\n",
        "\n",
        "The full syntax for deleting-or, more formally, dropping-a table from a\n",
        "database in SQLite is\n",
        "\n",
        "DROP TABLE tableName ;\n",
        "\n",
        "The syntax is simple: You append the fully qualified name and a\n",
        "semicolon to the DROP TABLE SQL command, and you're finished."
      ],
      "metadata": {
        "id": "bgJWcv5NdCUD"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "In the next few code cells, we first create our schema file, before\n",
        "executing the SQL commands on our new database by using the `sqlite3`\n",
        "client tool."
      ],
      "metadata": {
        "id": "sze5I_OhdFj2"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%writefile create.sql\n",
        "\n",
        "-- First we drop any tables if they exist\n",
        "-- Ignore the no such Table error if present\n",
        "\n",
        "DROP TABLE myVendors ;\n",
        "DROP TABLE myProducts ;\n",
        "\n",
        "-- Vendor Table: Could contain full vendor contact information.\n",
        "\n",
        "CREATE TABLE myVendors (\n",
        "    itemNumber INT NOT NULL,\n",
        "    vendornumber INT NOT NULL,\n",
        "    vendorName TEXT\n",
        ") ;\n",
        "\n",
        "-- Product Table: Could include additional data like quantity\n",
        "\n",
        "CREATE TABLE myProducts (\n",
        "    itemNumber INT NOT NULL,\n",
        "    price REAL,\n",
        "    stockDate TEXT,\n",
        "    description TEXT\n",
        ") ;"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "5yjtcul7Xmnt",
        "outputId": "2bf253bc-eec1-42f6-d23e-9ab82e96b16a"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Writing create.sql\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Now create the schema in a new test database\n",
        "# The following two error lines are fine to ignore\n",
        "# Error: near line 5: in prepare, no such table: myVendors (1)\n",
        "# Error: near line 6: in prepare, no such table: myProducts (1)\n",
        "\n",
        "!sqlite3 test < create.sql"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "VmLVWmppX1SY",
        "outputId": "b355ba3f-fa11-41aa-ff07-01e3afc44254"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Error: near line 5: in prepare, no such table: myVendors (1)\n",
            "Error: near line 6: in prepare, no such table: myProducts (1)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# We can test the results\n",
        "\n",
        "!sqlite3 test \".schema\""
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "yCV3qnl3dJ_z",
        "outputId": "1bd3a211-581c-42d0-9a05-0f666da1793c"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "CREATE TABLE myVendors (\n",
            "    itemNumber INT NOT NULL,\n",
            "    vendornumber INT NOT NULL,\n",
            "    vendorName TEXT\n",
            ");\n",
            "CREATE TABLE myProducts (\n",
            "    itemNumber INT NOT NULL,\n",
            "    price REAL,\n",
            "    stockDate TEXT,\n",
            "    description TEXT\n",
            ");\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### SQL scripts\n",
        "\n",
        "As the previous code blocks demonstrated, we can write SQL commands into\n",
        "a script file that can be easily executed by the `sqlite3` client. While\n",
        "this might seem like overkill given the simplicity of our current\n",
        "schema, it is actually a useful technique. Often, you'll need to execute\n",
        "multiple, complex commands. To simplify debugging a set of complex SQL\n",
        "commands,  it's generally easier to write them in a text file and then\n",
        "execute the commands in the text file all at once. By placing SQL\n",
        "commands in a script file, you gain the additional benefit of being able\n",
        "to execute the commands as many times as necessary.\n",
        "\n",
        "A script file is just a plain text file that contains a combination of\n",
        "SQL commands and SQLite commands that can be run directly from the\n",
        "`sqlite3` tool. A script file simplifies the development and maintenance\n",
        "of relational databases and provides a self-documenting technique for\n",
        "building databases. You should store these files as ASCII text files,\n",
        "not as RTF files (or any other format), to prevent text-encoding errors.\n",
        "Some text applications may try to automatically save your file as a rich\n",
        "text file. Be careful to avoid doing so, or you may have problems when\n",
        "you try to execute your script file. For this reason, you probably will\n",
        "want to always create and edit your SQL scripts at the Unix command\n",
        "prompt.\n",
        "\n",
        "Our previous script file, `create.sql`, includes several lines that\n",
        "start with two dashes (--). These lines are SQL comments; you should use\n",
        "them to provide a basic description of the purpose of each major\n",
        "component within the script file. The first actual commands ion the\n",
        "script file are SQL DROP statements that delete the myProducts and\n",
        "myVendors tables from the database. If the tables don't exist (which is\n",
        "the case if the database was just created), an error message is\n",
        "displayed; but as the preceding SQL comments indicate, you can safely\n",
        "ignore those messages.\n",
        "\n",
        "You first drop the tables, if they exist, so that you can cleanly create\n",
        "new tables with the exact column definitions you need. The next two SQL\n",
        "statements do just that, creating the myProducts and myVendors tables.\n",
        "To run this script file, we can either have the `sqlite3` client tool\n",
        "read the file form _STDIN_, as shown above, or we can start `sqlite3`\n",
        "and use the `.read` command to read and execute SQL commands from our\n",
        "`create.sql` file.\n",
        "\n",
        "#### What if something goes wrong?\n",
        "\n",
        "Sometimes, no matter how hard you try, things don't work out quite\n",
        "right. If you can't safely execute the create.sql script, there are a\n",
        "number of possibilities to check:\n",
        "\n",
        "- Be sure the SQLite client tool starts up properly. You can do this at\n",
        "the Unix command prompt.\n",
        "\n",
        "- Be sure you have free disk space in which to create a new database.\n",
        "\n",
        "- Be sure you have proper permissions (to read the script file and to\n",
        "create the new database) in the directory where you try to execute the\n",
        "script file.\n",
        "\n",
        "- Be sure your script file is a simple ASCII text file (and not an RTF\n",
        "file).\n",
        "\n",
        "If the output of the `!sqlite3 test \".schema\"` matches the data in the\n",
        "`create.sql` file, congratulations are in order. You now have a new test\n",
        "database with two new tables ready to hold data.\n",
        "\n",
        "Now we'll focus on the process of inserting data into a table by using\n",
        "SQLite. To follow along, you will need a SQLite database with the\n",
        "myProducts table available. If you haven't already done so, you should\n",
        "execute the create.sql script file."
      ],
      "metadata": {
        "id": "pbJ4Cyn3dbeM"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Additional References\n",
        "\n",
        "Several sites exist that allow you to try out SQL commands online.\n",
        "\n",
        "1. [W3 Schools SQL][1], a general SQL demo site.\n",
        "2. [SQLZoo][2], allows you to specify the Relational Database to target.\n",
        "\n",
        "[1]: http://www.w3schools.com/SQL/\n",
        "[2]: http://sqlzoo.net/wiki/SELECT_basics"
      ],
      "metadata": {
        "id": "DF0g3mRQdn3w"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Introduction to SQL Data Manipulation Language\n",
        "\n",
        "From here, we focus on the basic task of manipulating data in relational database management systems by using SQL DML. In this Notebook we will expand on our use of the SQLite database to build and query a fictitious database. We will cover inserting data into tables, creating and executing queries, and finishing with updating and deleting data."
      ],
      "metadata": {
        "id": "SrleGc1ihx2V"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### INSERT\n",
        "\n",
        "One of the most important tasks when you're building a database\n",
        "application is inserting data into the database. It doesn't matter how\n",
        "good the database software is-if you put bad data in a database, nothing\n",
        "else matters. There are several different ways to insert data into a\n",
        "database, but the rest of this lesson focuses on inserting data into a\n",
        "SQLite database by using the SQL INSERT statement.\n",
        "\n",
        "Before you can insert data into a SQLite database using the SQL INSERT\n",
        "statement, you must know how to properly use this statement. The full\n",
        "syntax for the SQL INSERT statement is\n",
        "\n",
        "```\n",
        "INSERT INTO table-Name\n",
        "    [ (Simple-column-Name [ , Simple-column-Name]* ) ]\n",
        "\t  Expression\n",
        "```\n",
        "\n",
        "which should seem familiar. As discussed previously, the square brackets\n",
        "([]) enclose optional parameters. The only component whose purpose isn't\n",
        "immediately clear is _Expression_; but how complex can that simple\n",
        "phrase be? Of course, appearances can be deceiving; the Expression term\n",
        "can expand to one of four different structures:\n",
        "\n",
        "- a single-row VALUES list\n",
        "- a multiple-row VALUES list\n",
        "- a SELECT expression\n",
        "- a UNION expression\n",
        "\n",
        "Of these, the last two are beyond the scope of this lesson. The first\n",
        "two are similar; the only difference is that the first form inserts one\n",
        "row into a table, whereas the latter form inserts multiple rows into a\n",
        "table.\n",
        "\n",
        "You can use the optional part of the SQL INSERT statement to specify the\n",
        "column order of the values being inserted into the table. By default,\n",
        "data is inserted into a table's columns in the same order that the\n",
        "columns were listed when the table was created. Sometimes you may want\n",
        "to change this order or perhaps only specify values for columns that\n",
        "have NOT NULL constraints. By explicitly listing the columns in your SQL\n",
        "INSERT statement, you gain more control of the operation and can more\n",
        "easily handle these specific use cases.\n",
        "\n",
        "The syntax for the SQL VALUES expression is fairly simple,\n",
        "\n",
        "```\n",
        "{\n",
        "    VALUES ( Value {, Value }* )\n",
        "        [ , ( Value {, Value }* ) ]* |\n",
        "    VALUES Value [ , Value ]*\n",
        " }\n",
        "```\n",
        "\n",
        "This syntax displays the multiple-row format first, followed by the\n",
        "single-row format (remember that the vertical line character, |, means\n",
        "or and that the asterisk character, \\*, means one or more). The value\n",
        "term stands for a value that you want to insert into a specific column.\n",
        "To insert data into multiple columns, you must enclose the data for a\n",
        "row in parentheses separated by commas.\n",
        "\n",
        "As shown below, to insert data into a table, you first need to to make\n",
        "sure that the table exists. If you haven't already done so, execute the\n",
        "table creation scripts discussed earlier.\n",
        "\n",
        "```\n",
        "INSERT INTO myProducts\n",
        "    VALUES(1, 19.95, '2015-03-31', 'Hooded sweatshirt') ;\n",
        "\n",
        "INSERT INTO myProducts(itemNumber, price, stockDate, description)\n",
        "    VALUES(2, 99.99, '2015-03-29', 'Beach umbrella') ;\n",
        "\n",
        "INSERT INTO myProducts(itemNumber, price, stockDate)\n",
        "    VALUES(3, 0.99, '2015-02-28') ;\n",
        "```\n",
        "\n",
        "This example presents three single-row inserts into the myProducts\n",
        "table. The first SQL INSERT statement doesn't provide a list of columns;\n",
        "it inserts an itemNumber, a price, a stockDate, and a description.\n",
        "Notice that the values inserted into both the stockDate and description\n",
        "columns are enclosed in single quote characters. The description column\n",
        "is a TEXT field, so it expects a string (which you indicate by enclosing\n",
        "the character data within single quotes). The stockDate column is also a\n",
        "TEXT field; as part of our application logic, we could pass in dates in\n",
        "the correct day, month, and year format. (For more guidance on the\n",
        "format of data types during a SQL INSERT operation, read the SQLite\n",
        "documentation).\n",
        "\n",
        "The second SQL INSERT statement explicitly lists all four columns and\n",
        "inserts new values appropriately. The final SQL INSERT statement lists\n",
        "only three columns and inserts only three values. The description column\n",
        "is left empty, which means it will have a NULL value.\n",
        "\n",
        "Although single-row SQL INSERT statements can be useful, when you need\n",
        "to insert multiple rows, it's more efficient to do so directly, as shown\n",
        "below:\n",
        "\n",
        "    INSERT INTO myProducts(itemNumber, price, stockDate, description)\n",
        "    VALUES (4, 29.95, '2015-02-10', 'Male bathing suit, blue'),\n",
        "           (5, 49.95, '2015-02-20', 'Female bathing suit, one piece, aqua'),\n",
        "           (6, 9.95, '2015-01-15', 'Child sand toy set'),\n",
        "           (7, 24.95, '2014-12-20', 'White beach towel'),\n",
        "           (8, 32.95, '2014-12-22', 'Blue-striped beach towel'),\n",
        "           (9, 12.95, '2015-03-12', 'Flip-flop'),\n",
        "           (10, 34.95, '2015-01-24', 'Open-toed sandal') ;\n",
        "\n",
        "In this example, we insert seven rows into the database by explicitly\n",
        "listing all four columns and providing new values for each row. As\n",
        "discussed earlier, multiple-row inserts enclose the values for each new\n",
        "row within parentheses, and these values are separated by commas.\n",
        "\n",
        "To actually execute these statements, we can place the SQL INSERT\n",
        "statements in a script file and run the script to insert the data. This\n",
        "approach lets you more easily fix errors or reinsert the data if\n",
        "necessary without recreating the requisite SQL INSERT statements. In the\n",
        "following code cells, we first create a SQL INSERT script file, before\n",
        "executing this script by using the `sqlite3` client tool. After this, we\n",
        "use the `.dump` command in the `sqlite3` client tool to display the full\n",
        "schema and contents of this SQLite database."
      ],
      "metadata": {
        "id": "IINPG2bdiTeu"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%writefile insert.sql\n",
        "\n",
        "-- Single unnamed INSERT\n",
        "\n",
        "INSERT INTO myProducts\n",
        "VALUES(1, 19.95, '2015-03-31', 'Hooded sweatshirt') ;\n",
        "\n",
        "-- Single named INSERT\n",
        "\n",
        "INSERT INTO myProducts (itemNumber, price, stockDate, description)\n",
        "VALUES(2, 99.99, '2015-03-29', 'Beach umbrella') ;\n",
        "\n",
        "-- Single named INSERT with missing data\n",
        "\n",
        "INSERT INTO myProducts (itemNumber, price, stockDate)\n",
        "VALUES(3, 0.99, '2015-02-28') ;\n",
        "\n",
        "-- Multiple named INSERT\n",
        "\n",
        "INSERT INTO myProducts (itemNumber, price, stockDate, description)\n",
        "VALUES (4, 29.95, '2015-02-10', 'Male bathing suit, blue'),\n",
        "       (5, 49.95, '2015-02-20', 'Female bathing suit, one piece, aqua'),\n",
        "       (6, 9.95, '2015-01-15', 'Child sand toy set'),\n",
        "       (7, 24.95, '2014-12-20', 'White beach towel'),\n",
        "       (8, 32.95, '2014-12-22', 'Blue-striped beach towel'),\n",
        "       (9, 12.95, '2015-03-12', 'Flip-flop'),\n",
        "       (10, 34.95, '2015-01-24', 'Open-toed sandal') ;\n",
        "\n",
        "-- Insert into myVendors\n",
        "\n",
        "INSERT INTO myVendors(itemNumber, vendorNumber, vendorName)\n",
        "VALUES (1, 1, 'Luna Vista Limited'),\n",
        "       (2, 1, 'Luna Vista Limited'),\n",
        "       (3, 1, 'Luna Vista Limited'),\n",
        "       (4, 2, 'Mikal Arroyo Incorporated'),\n",
        "       (5, 2, 'Mikal Arroyo Incorporated'),\n",
        "       (6, 1, 'Luna Vista Limited'),\n",
        "       (7, 1, 'Luna Vista Limited'),\n",
        "       (8, 1, 'Luna Vista Limited'),\n",
        "       (9, 3, 'Quiet Beach Industries'),\n",
        "       (10, 3, 'Quiet Beach Industries') ;"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "_T5ro8FidT-H",
        "outputId": "a06dac2f-2bcf-435a-be7d-fe0f36e1496f"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Writing insert.sql\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "!head -10 insert.sql"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ELvglrCeiaOB",
        "outputId": "521f2df5-2e3d-43e5-ebe8-6c835e4f69b1"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "-- Single unnamed INSERT\n",
            "\n",
            "INSERT INTO myProducts \n",
            "VALUES(1, 19.95, '2015-03-31', 'Hooded sweatshirt') ;\n",
            "\n",
            "-- Single named INSERT\n",
            "\n",
            "INSERT INTO myProducts (itemNumber, price, stockDate, description) \n",
            "VALUES(2, 99.99, '2015-03-29', 'Beach umbrella') ;\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "!sqlite3 test < insert.sql"
      ],
      "metadata": {
        "id": "KwxxwHa2icg5"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "!sqlite3 test \".dump\""
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "HXQwRBrqifdh",
        "outputId": "bc529acb-922c-4b6d-981b-7a83b8caabc8"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "PRAGMA foreign_keys=OFF;\n",
            "BEGIN TRANSACTION;\n",
            "CREATE TABLE myVendors (\n",
            "    itemNumber INT NOT NULL,\n",
            "    vendornumber INT NOT NULL,\n",
            "    vendorName TEXT\n",
            ");\n",
            "INSERT INTO myVendors VALUES(1,1,'Luna Vista Limited');\n",
            "INSERT INTO myVendors VALUES(2,1,'Luna Vista Limited');\n",
            "INSERT INTO myVendors VALUES(3,1,'Luna Vista Limited');\n",
            "INSERT INTO myVendors VALUES(4,2,'Mikal Arroyo Incorporated');\n",
            "INSERT INTO myVendors VALUES(5,2,'Mikal Arroyo Incorporated');\n",
            "INSERT INTO myVendors VALUES(6,1,'Luna Vista Limited');\n",
            "INSERT INTO myVendors VALUES(7,1,'Luna Vista Limited');\n",
            "INSERT INTO myVendors VALUES(8,1,'Luna Vista Limited');\n",
            "INSERT INTO myVendors VALUES(9,3,'Quiet Beach Industries');\n",
            "INSERT INTO myVendors VALUES(10,3,'Quiet Beach Industries');\n",
            "CREATE TABLE myProducts (\n",
            "    itemNumber INT NOT NULL,\n",
            "    price REAL,\n",
            "    stockDate TEXT,\n",
            "    description TEXT\n",
            ");\n",
            "INSERT INTO myProducts VALUES(1,19.949999999999999289,'2015-03-31','Hooded sweatshirt');\n",
            "INSERT INTO myProducts VALUES(2,99.989999999999994882,'2015-03-29','Beach umbrella');\n",
            "INSERT INTO myProducts VALUES(3,0.98999999999999999111,'2015-02-28',NULL);\n",
            "INSERT INTO myProducts VALUES(4,29.949999999999999289,'2015-02-10','Male bathing suit, blue');\n",
            "INSERT INTO myProducts VALUES(5,49.950000000000002843,'2015-02-20','Female bathing suit, one piece, aqua');\n",
            "INSERT INTO myProducts VALUES(6,9.9499999999999992894,'2015-01-15','Child sand toy set');\n",
            "INSERT INTO myProducts VALUES(7,24.949999999999999289,'2014-12-20','White beach towel');\n",
            "INSERT INTO myProducts VALUES(8,32.950000000000002841,'2014-12-22','Blue-striped beach towel');\n",
            "INSERT INTO myProducts VALUES(9,12.949999999999999289,'2015-03-12','Flip-flop');\n",
            "INSERT INTO myProducts VALUES(10,34.950000000000002841,'2015-01-24','Open-toed sandal');\n",
            "COMMIT;\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Transactions\n",
        "\n",
        "If you look carefully at the output of the `.dump` command, you see that\n",
        "near the top of the output is a `BEGIN TRANSACTION;` statement and at\n",
        "the end of the output is a `COMMIT;` statement. These two statements are\n",
        "explicit instructions, inserted by SQLite, to start a transaction, which\n",
        "is a logical unit of work, and save all operations in the transaction to\n",
        "the database. If a set of operations is not completed successfully, the\n",
        "transaction model requires that the commit does not occur, and instead a\n",
        "rollback is issued to return the database to the state that existed\n",
        "prior to the transaction commencing."
      ],
      "metadata": {
        "id": "N4OnX_J9inS4"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### SELECT\n",
        "\n",
        "In the SQL programming language, the task of performing a query falls to\n",
        "the SELECT statement. To provide all the query functionality required by\n",
        "database applications, the SELECT statement's capabilities are\n",
        "extensive. Before looking at example SELECT statements, lets first look\n",
        "at the  formal syntax of SELECT, which, as shown below is actually\n",
        "simple. The basic format is `SELECT ... FROM ... WHERE;`, you select the\n",
        "columns of interest from rows in a table or tables where certain\n",
        "conditions are satisfied. Of course, things can become considerably more\n",
        "complex. This article covers the basic features of SELECT and defers the\n",
        "more advanced issues to subsequent articles.\n",
        "\n",
        "```\n",
        "SELECT [ DISTINCT | ALL ] SelectItem [ , SelectItem ]*\n",
        "FROM clause\n",
        "[ WHERE clause ]\n",
        "[ GROUP BY clause ]\n",
        "[ HAVING clause ]\n",
        "```\n",
        "\n",
        "From this you can see that a basic SELECT statement requires only a\n",
        "SELECT and a FROM; you must specify what data to select and indicate the\n",
        "location of the data of interest. Everything else is optional (as\n",
        "indicated by the square brackets). The DISTINCT and ALL keywords are\n",
        "optional qualifiers to indicate that either rows with unique values or\n",
        "all rows should be selected, respectively. By default, ALL is implicitly\n",
        "assumed, and you can use only one DISTINCT qualifier per SELECT\n",
        "statement.\n",
        "\n",
        "A SELECT statement can have multiple columns listed following the SELECT\n",
        "keyword. Multiple elements (or, more generally, column names) are\n",
        "separated by commas. For example, `SELECT a, b, c` selects the three\n",
        "columns a, b, and c. To select all columns from a table, you can use the\n",
        "asterisk character (\\*) as a shorthand for all columns. An important\n",
        "point to remember is that the result of any SELECT statement is a\n",
        "transient SQLite table, and you can use it in many of the same ways you\n",
        "use a more permanent table.\n",
        "\n",
        "The FROM component of a SELECT statement indicates from which table (or\n",
        "multiple tables) the data will be extracted. For now, we will focus on\n",
        "selecting data from a single table; latter we will cover table joins and\n",
        "selecting data from multiple tables. In this case, the fully qualified\n",
        "name of the table to query must follow the FROM keyword.\n",
        "\n",
        "The rest of the SELECT statement is optional. Before you build your\n",
        "first query, however, lets review the order in which the SELECT\n",
        "statement components are evaluated:\n",
        "\n",
        "1. FROM clause\n",
        "2. WHERE clause\n",
        "3. GROUP BY clause\n",
        "4. HAVING clause\n",
        "5. SELECT clause\n",
        "\n",
        "When you break down the process SQLite follows when processing a query,\n",
        "this order is intuitive. First you must locate the data to be analyzed,\n",
        "after which you filter out the rows of interest. The next steps are to\n",
        "group related rows and, finally, to select the actual columns of\n",
        "interest.\n",
        "\n",
        "### SQLite\n",
        "\n",
        "To demonstrate a SELECT statement, we can extract all the columns from\n",
        "the myProducts table by using the `sqlite3` tool and passing the SQL\n",
        "statement in as a command line argument."
      ],
      "metadata": {
        "id": "kyqsPG9miv7K"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!sqlite3 test \"SELECT * FROM myProducts ;\""
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "2_6i7EMligyV",
        "outputId": "f0a6b466-002e-4cbf-c4bd-94e998d2809d"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "1|19.95|2015-03-31|Hooded sweatshirt\n",
            "2|99.99|2015-03-29|Beach umbrella\n",
            "3|0.99|2015-02-28|\n",
            "4|29.95|2015-02-10|Male bathing suit, blue\n",
            "5|49.95|2015-02-20|Female bathing suit, one piece, aqua\n",
            "6|9.95|2015-01-15|Child sand toy set\n",
            "7|24.95|2014-12-20|White beach towel\n",
            "8|32.95|2014-12-22|Blue-striped beach towel\n",
            "9|12.95|2015-03-12|Flip-flop\n",
            "10|34.95|2015-01-24|Open-toed sandal\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "In the previous code cell, we used the asterisk character to select all\n",
        "columns from the myProducts table without listing them explicitly. This\n",
        "can be a useful shortcut, especially when you're developing database\n",
        "applications, but it isn't a recommended practice. By using the\n",
        "shortcut, you don't explicitly specify the database column names or\n",
        "their order. In a database application, if you always assume that the\n",
        "column names and their order in a table are fixed, you may end up with\n",
        "subtle bugs if someone else modifies the database tables on which your\n",
        "application depends. You should always explicitly name the database\n",
        "columns in your SELECT statements and list the order you require.\n",
        "\n",
        "As a result, lets look at explicitly listing the columns to extract.\n",
        "This is a recommended practice that also allows us to control the order\n",
        "in which the columns are listed in the query output."
      ],
      "metadata": {
        "id": "9eTCMvlLi13X"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!sqlite3 test \"SELECT price, itemNumber, description FROM myProducts ;\""
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "YZ7OJJMviyXY",
        "outputId": "b96d33d5-7f96-4334-9ef6-6229cc23a0cb"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "19.95|1|Hooded sweatshirt\n",
            "99.99|2|Beach umbrella\n",
            "0.99|3|\n",
            "29.95|4|Male bathing suit, blue\n",
            "49.95|5|Female bathing suit, one piece, aqua\n",
            "9.95|6|Child sand toy set\n",
            "24.95|7|White beach towel\n",
            "32.95|8|Blue-striped beach towel\n",
            "12.95|9|Flip-flop\n",
            "34.95|10|Open-toed sandal\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### WHERE clause\n",
        "\n",
        "Up to this point, you have only selected columns for all rows in a\n",
        "single table. This can be expensive in terms of query performance,\n",
        "especially if you only want a subset of the rows from a large table. A\n",
        "more efficient technique is to filter database rows by placing\n",
        "conditions in the WHERE clause, which is evaluated immediately after the\n",
        "tables are specified within the FROM clause. The rest of this section\n",
        "discusses some of the basic features that are enabled by using the WHERE\n",
        "clause, including the ability to select rows that satisfy Boolean\n",
        "conditions as well as join multiple tables to perform more complex\n",
        "queries.\n",
        "\n",
        "The simplest and most common use of the WHERE clause is to filter the\n",
        "rows from a table before selecting any columns, as shown in the next two\n",
        "code cells."
      ],
      "metadata": {
        "id": "bzKSq9Svi7IL"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!sqlite3 test \"SELECT p.itemNumber, p.price FROM myProducts AS p WHERE p.price > 30.00 ;\""
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "HrF0bQWDi4GR",
        "outputId": "cf6def67-b76f-4551-eb13-8bca2f4941d3"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "2|99.99\n",
            "5|49.95\n",
            "8|32.95\n",
            "10|34.95\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "!sqlite3 test \"SELECT * FROM myProducts WHERE price > 30.00 AND stockDate < '2015-01-01' ;\""
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "4YTyOkHEi9aI",
        "outputId": "7d602195-3a43-42d5-e878-3a5223036a23"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "8|32.95|2014-12-22|Blue-striped beach towel\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "The first query shown in this example selects the `itemNumber` and\n",
        "`price` columns from the myProducts table for all rows where the price\n",
        "column has a value greater than `$30.00`. The second query extends this\n",
        "same query to select only those columns whose price column has a value\n",
        "more than `$30.00` and whose stockDate column has a value less than\n",
        "January 1, 2006. These two query restrictions are combined in this query\n",
        "by using the Boolean AND operator.\n",
        "\n",
        "You can perform a number of different Boolean operations within a WHERE\n",
        "clause. The following table lists and provides examples of the basic SQL Boolean\n",
        "operations that you can use in a query.\n",
        "\n",
        "| Operator | Example | Description |\n",
        "| ---- | ---- | ---- |\n",
        "| =\t| `p.price = 29.95` | Test if any built-in type is equal to a specified value. |\n",
        "|<\t| `p.price < 29.95` | Test if any built-in type is less than a specified value.|\n",
        "|>\t| `p.price > 29.95` | Test if any built-in type is greater than a specified value.|\n",
        "|<=\t| `p.price <= 29.95` | Test if any built-in type is less than or equal to a specified value.|\n",
        "|>=\t| `p.price >= 29.95` | Test if any built-in type is greater than or equal to a specified value.|\n",
        "|<>\t| `p.price <> 29.95` |Test if any built-in type is not equal to a specified value. |\n",
        "| IS NULL | `p.description IS NULL` | Test if an expression or value is null. |\n",
        "| IS NOT NULL | `p.description IS NOT NULL` | Test if an expression or value is not null.|\n",
        "| AND | `(p.price > 29.92) AND (p.itemNumber > 5)` | Test if two expressions are both true or evaluate as nonzero.\n",
        "| OR | `(p.price > 29.92) OR (p.itemNumber > 5)` | Test if one or both of two expressions are true or evaluate as nonzero.\n",
        "| NOT | `NOT v.vendorNumber = 1` | Test if an expression is false or evaluates as zero.\n",
        "| BETWEEN | `p.price BETWEEN 29.95 AND 39.95` | Test if a value lies inclusively between two other values (example is equivalent to `29.95 <= p.price <= 39.95`).\n",
        "| LIKE | `v.vendorName LIKE 'Lun%'` | Test if a character expression matches a pattern, with the percent character (%) matching zero or more arbitrary characters and the underscore character (\\_) matching exactly one arbitrary character.\n",
        "\n",
        "The first query above also introduces the AS clause, which you can use\n",
        "to create a table synonym. In these examples, you define a synonym p for\n",
        "the fully qualified table name myProducts. By defining a synonym,\n",
        "you can refer to table quantities by using a shorter notation. This may\n",
        "not seem important when only one table is being referenced in a query,\n",
        "but the next section shows how to join multiple tables together within a\n",
        "query; in that case, providing table synonyms is very useful. You can\n",
        "also use an AS clause to name the selected columns in a query. Doing so\n",
        "lets you control how the results are displayed, which is demonstrated in\n",
        "the next section."
      ],
      "metadata": {
        "id": "ALLw65AtjCVy"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Joins\n",
        "\n",
        "The second major function performed by a WHERE clause is to join\n",
        "multiple tables together into a single table that can be queried more\n",
        "easily. Joining multiple tables is a powerful technique, and it can be\n",
        "complex when you're dealing with several large tables. Tables can be\n",
        "joined either explicitly, by using the JOIN keyword, or implicitly, by\n",
        "using a WHERE clause.\n",
        "\n",
        "You join two tables by using an inner join or an outer join. An inner\n",
        "join is essentially the intersection of two tables, where the tables are\n",
        "matched by comparing the values of a key column, such as itemNumber. The\n",
        "resulting table is composed of only rows that were matched between the\n",
        "two tables. An outer join is more like a union of two tables, where the\n",
        "tables are matched by comparing the values of a key column, but\n",
        "non-matching rows are still included in the resulting table and filled\n",
        "with NULL values as appropriate. Writing SQL queries that use these more\n",
        "advanced table joins will be addressed in future articles.\n",
        "\n",
        "In the current simple scheme, the process is simple; In the next code\n",
        "cell, we perform an implicit inner join of the myProducts table and the\n",
        "myVendors table."
      ],
      "metadata": {
        "id": "RPF1hnxejFti"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%writefile select.sql\n",
        "\n",
        "SELECT p.price, p.description AS 'Item', v.vendorName AS 'Vendor'\n",
        "FROM myProducts AS p, myVendors AS v\n",
        "  WHERE p.itemNumber = v.itemNumber ;"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "1OP9MLbhi_bR",
        "outputId": "ad28beee-6c53-4fa0-eb1d-71dc55ad6975"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Writing select.sql\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Execute SQL Script\n",
        "\n",
        "!sqlite3 test < select.sql"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "CQ1LrhbnjJWB",
        "outputId": "13cc03d3-9e62-448a-e6a6-ce1029b96d59"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "19.95|Hooded sweatshirt|Luna Vista Limited\n",
            "99.99|Beach umbrella|Luna Vista Limited\n",
            "0.99||Luna Vista Limited\n",
            "29.95|Male bathing suit, blue|Mikal Arroyo Incorporated\n",
            "49.95|Female bathing suit, one piece, aqua|Mikal Arroyo Incorporated\n",
            "9.95|Child sand toy set|Luna Vista Limited\n",
            "24.95|White beach towel|Luna Vista Limited\n",
            "32.95|Blue-striped beach towel|Luna Vista Limited\n",
            "12.95|Flip-flop|Quiet Beach Industries\n",
            "34.95|Open-toed sandal|Quiet Beach Industries\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "This query may seem complex, due primarily to its length. But by\n",
        "breaking it down line by line you can easily follow what's happening.\n",
        "First, you select two columns from the myProducts table and one column\n",
        "from the myVendors table and use an AS clause to name these columns for\n",
        "subsequent usage (in this case they are displayed by the `sqlite3`\n",
        "client tool. Because the query joins these two tables (by using an\n",
        "implicit inner join), you can select columns from both tables. In the\n",
        "FROM clause, you list both tables and provide aliases for them to\n",
        "simplify the full SQL statement. In the WHERE clause, you provide the\n",
        "logic for joining the two tables, by explicitly instructing the SQLite\n",
        "database to only select rows from the two tables that have matching\n",
        "values in their respective itemNumber columns. In processing this query,\n",
        "SQLite first pulls all rows out of the first (left) table in the query\n",
        "(myProducts) and finds the row with a matching value in the itemNumber\n",
        "column in the second (right) table in the query (myVendors)."
      ],
      "metadata": {
        "id": "i-juu2UCjOME"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### DISTINCT\n",
        "\n",
        "By default, when data is selected by using an SQL query, all rows that\n",
        "satisfy the WHERE clause are extracted from the database. In some cases,\n",
        "this may result in rows that have identical column values being\n",
        "returned. If you need to restrict your query so that only unique row\n",
        "values are returned, you can use the DISTINCT qualifier, as shown in the\n",
        "following two code cells."
      ],
      "metadata": {
        "id": "f-bJKQvPjScM"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!sqlite3 test \"SELECT DISTINCT vendorNumber AS 'Vendor #' FROM myVendors ;\""
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "HJ3xO0aPjK15",
        "outputId": "ec4409a0-0e48-4c0e-ace8-383d083c1075"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "1\n",
            "2\n",
            "3\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "!sqlite3 test \"SELECT DISTINCT vendorNumber AS 'Vendor #', itemNumber as 'Item #' FROM myVendors WHERE itemNumber > 5 ;\""
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "IHHGXoo3jUnB",
        "outputId": "365e16c5-7889-458a-d1e1-bb8d575f8ef7"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "1|6\n",
            "1|7\n",
            "1|8\n",
            "3|9\n",
            "3|10\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "If you want to use the DISTINCT qualifier, it must be the first item\n",
        "listed in the SELECT clause, as shown in Listing 1, and you can have\n",
        "only one DISTINCT qualifier per SELECT clause. If the selected rows\n",
        "contain a column with NULL values, multiple NULL values are considered\n",
        "duplicates when identifying unique rows.\n",
        "\n",
        "The first query in this listing uses the DISTINCT qualifier to restrict\n",
        "the output of the query to only distinct, or unique, values of the\n",
        "vendorNumber column, which is the only column listed in the SELECT\n",
        "clause. In the example schema that these articles use, there are only\n",
        "three vendors (with vendorNumber being restricted to 1, 2, or 3). Thus,\n",
        "when the DISTINCT qualifier is used in the query, only three rows are\n",
        "selected.\n",
        "\n",
        "The DISTINCT qualifier, however, applies to the entire list of selected\n",
        "columns, so if multiple columns are listed following a DISTINCT keyword,\n",
        "only unique combinations of all the columns are selected. This is\n",
        "demonstrated in the second example, where both vendorNumber and\n",
        "itemNumber are listed in the SELECT clause. Because every item has a\n",
        "unique itemNumber, every combination of these two columns is unique, and\n",
        "all rows that satisfy the WHERE clause are selected; in other words, the\n",
        "DISTINCT qualifier has no effect on the results.\n",
        "\n",
        "One remaining point that you may have noticed from the two previous\n",
        "examples is that the selected rows were not in the same order. If the\n",
        "order of selected rows is important, you can easily control it by using\n",
        "an ORDER BY clause in your query.\n",
        "\n",
        "### ORDER BY\n",
        "\n",
        "In general, you can't assume that SQLite, or any database, will return\n",
        "rows from a query in a specific order. If the order is important, you\n",
        "can use the ORDER BY clause to have SQLite order the data that are\n",
        "returned by your query in a particular manner. Generally, you do so by\n",
        "specifying a column that should be used to provide the ordinal values\n",
        "for comparison as shown in the next two code cells."
      ],
      "metadata": {
        "id": "4f9wx77MjaOr"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%writefile orderby.sql\n",
        "\n",
        "SELECT v.vendorNumber AS \"Vendor #\", vendorName as \"Vendor\",\n",
        "    p.price AS \"Price\", p.itemNumber AS \"Item #\"\n",
        "    FROM myProducts AS p, myVendors AS v\n",
        "    WHERE p.itemNumber = v.itemNumber AND p.price > 20.0\n",
        "    ORDER by v.vendorNumber ;\n",
        "\n",
        "SELECT v.vendorNumber AS \"Vendor #\", vendorName as \"Vendor\",\n",
        "    p.price AS \"Price\", p.itemNumber AS \"Item #\"\n",
        "    FROM myProducts AS p, myVendors AS v\n",
        "    WHERE p.itemNumber = v.itemNumber AND p.price > 20.0\n",
        "    ORDER BY v.vendorNumber ASC, p.price DESC ;"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "891riJyOjWo4",
        "outputId": "5e288813-7772-45a7-86c0-51f96e47c009"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Writing orderby.sql\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "!sqlite3 test < orderby.sql"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "CJK02rByjej_",
        "outputId": "e2372b05-e9a4-44a6-d382-4e20152b8c7f"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "1|Luna Vista Limited|99.99|2\n",
            "1|Luna Vista Limited|24.95|7\n",
            "1|Luna Vista Limited|32.95|8\n",
            "2|Mikal Arroyo Incorporated|29.95|4\n",
            "2|Mikal Arroyo Incorporated|49.95|5\n",
            "3|Quiet Beach Industries|34.95|10\n",
            "1|Luna Vista Limited|99.99|2\n",
            "1|Luna Vista Limited|32.95|8\n",
            "1|Luna Vista Limited|24.95|7\n",
            "2|Mikal Arroyo Incorporated|49.95|5\n",
            "2|Mikal Arroyo Incorporated|29.95|4\n",
            "3|Quiet Beach Industries|34.95|10\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "In the previous example, the first query uses the ORDER BY clause to\n",
        "list a subset of all the rows in the table that results from joining the\n",
        "myVendors table to the myProducts table. The rows are ordered by\n",
        "vendorNumber (the subset is constructed by applying the WHERE clause).\n",
        "An ORDER BY clause can take either a column name, as in this example, or\n",
        "a column number, which is taken from the order in which the columns are\n",
        "listed after the SELECT keyword.\n",
        "\n",
        "You can also specify multiple columns to use during the sorting process\n",
        "and even specify ASC for ascending order, which is the default, or DESC\n",
        "for descending order. For example, if you used the ORDER BY 1 DESC, 4\n",
        "DESC clause in the first query, the query would return the same rows,\n",
        "but they would be ordered by using the vendorNumber column as the\n",
        "primary sort column in descending order followed by the itemNumber\n",
        "column as the secondary sort column in descending order.\n",
        "\n",
        "Although using column numbers may seem like a handy shortcut, it\n",
        "generally isn't a good idea. To see why, consider what happens if you\n",
        "modify the columns listed in a SELECT clause or just modify their order.\n",
        "If you forget to modify the numbers used in the ORDER BY clause, the\n",
        "query will break-or worse, return bad data. In general, it's a _best\n",
        "practice_ to always be explicit and specify the column names directly,\n",
        "even if doing so means more typing.\n",
        "\n",
        "### Query Math\n",
        "\n",
        "Selecting columns from a database provides a number of useful benefits,\n",
        "but being able to compute and select quantities based on data in a table\n",
        "opens up even more possibilities. SQLite,as does any SQL database,\n",
        "provides several mathematical operators, the most common of which are\n",
        "listed in the following table, that you can use in either a SELECT clause\n",
        "or a WHERE clause.\n",
        "\n",
        "|Operator | Example | Description |\n",
        "| --- | --- | ---- |\n",
        "| unary + | +1.0 | A noop, or no operation, as +4 = 4 |\n",
        "| unary - | -p.price | Changes the sign of the value to which it's applied |\n",
        "|+ | p.itemNumber + 10 | Adds the second value to the first value |\n",
        "| - | p.itemNumber - 10 | Subtracts the second value from the first value |\n",
        "| * | p.price * 1.0825 | Multiplies the first value by the second value |\n",
        "| / | p.price / 100.0 | Divides the first value by the second value |\n",
        "\n",
        "Using these operators is straightforward because they generally behave\n",
        "exactly as you expect. For example, if the sales tax is 8.25%, you can\n",
        "return the price for an item both before and after sales tax has been\n",
        "applied by using `SELECT price, price * 1.0825 FROM myProducts ;`. As\n",
        "another example, if you have a column called `numberItems` that tracks\n",
        "the number of items purchased and another column called `price` that\n",
        "contains the price at which they're purchased, you can return the total\n",
        "amount paid for those items at a given price by using\n",
        "`numberItems * price`. Several of the queries shown in this\n",
        "IPython Notebook provide additional examples of how to use these\n",
        "operators.\n",
        "\n",
        "The only concern when using these operators arises from complications\n",
        "that result from using different data types, such as integer or\n",
        "floating-point, in a mathematical operation. If both operands are the\n",
        "same data type, the result type will be the same. If you're performing\n",
        "division, this can result in truncation (for example, if you're using\n",
        "two integer values), which might cause unexpected problems. On the other\n",
        "hand, if the two operands are different data types, the result type is\n",
        "promoted to the more complex type.\n",
        "\n",
        "### SQL Functions\n",
        "\n",
        "SQL is a powerful and expressive language that can be used to perform a\n",
        "wide range of actions. Part of the SQL language's power comes from its\n",
        "ability to directly interact with a variety of data types. Some of the\n",
        "greatest power of a relational database arises from the inherent\n",
        "functions that it provides and from the extensibility enabled by\n",
        "allowing users to create new functions. SQLite provides functions (see\n",
        "the following [SQLite tutorial][1] for more information) in three\n",
        "different categories:\n",
        "\n",
        "1. [Core functions](https://www.sqlite.org/lang_corefunc.html)\n",
        "2. [Aggregate functions](https://www.sqlite.org/lang_aggfunc.html)\n",
        "3. [Date and Time functions](https://www.sqlite.org/lang_datefunc.html)\n",
        "\n",
        "Of these built-in functions, we will most likely use the aggregate\n",
        "functions, which operate on multiple rows. Aggregate functions-also\n",
        "known as set functions in SQL-92 or, more informally, as column\n",
        "functions-return a computed quantity from a column over a number of\n",
        "rows. SQLite supports the following five aggregate functions (a sixth\n",
        "function, `group_concat`, is not listed).\n",
        "\n",
        "| Function | Example | Description |\n",
        "| --- | --- | --- |\n",
        "| AVG | AVG(p.price) | Returns the average value of a column from all rows that satisfy an expression. Can only be used with built-in numeric data types. The precision of the returned value is defined by the precision of the column being evaluated. |\n",
        "| COUNT | COUNT(p.price) | Returns the number of rows that satisfy an expression, such as a query. Can be used with any data type. |\n",
        "| MAX | MAX(p.price) | Returns the maximum value of a column from all rows that satisfy an expression. Can only be used with built-in data types. |\n",
        "| MIN | MIN(p.price) | Returns the minimum value of a column from all rows that satisfy an expression. Can only be used with built-in data types. |\n",
        "| SUM | SUM(p.price) | Returns the sum of a column over all rows that satisfy an expression. Can only be used with built-in numeric data types. |\n",
        "\n",
        "These aggregate functions can often be used to quickly find useful\n",
        "information that might otherwise be difficult to identify, as shown in\n",
        "Listing 4.\n",
        "```\n",
        "SELECT COUNT(p.itemNumber) AS Number,\n",
        "        AVG(p.price) AS Average,\n",
        "        MIN(p.stockDate) AS \"First Date\", MAX(p.stockDate) AS \"Last Date\"\n",
        "    FROM myProducts AS p ;\n",
        "```\n",
        "Listing 4 uses four of the five aggregate functions to get summary\n",
        "information about the data in the myProducts table. The COUNT function\n",
        "indicates that the table includes ten rows (because the query didn't use\n",
        "a WHERE clause to restrict the rows selected from the table). The AVG\n",
        "function calculates the average price of all items in the myProducts\n",
        "table. Finally, the MIN and MAX functions extract the minimum and\n",
        "maximum dates from the myProducts table.\n",
        "\n",
        "[1]: http://zetcode.com/db/sqlite/sqlitefunctions/"
      ],
      "metadata": {
        "id": "TOB8GRHQjnrG"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### DELETE\n",
        "\n",
        "To delete data in a SQLite database, you use the SQL DELETE statement,\n",
        "which can delete either all rows in a table or a specific subset of\n",
        "rows. The formal syntax for the SQL DELETE statement is remarkably\n",
        "simple:\n",
        "\n",
        "```\n",
        "DELETE FROM tableName\n",
        "    [WHERE clause]\n",
        "```\n",
        "\n",
        "The DELETE statement deletes all rows from the specified table that\n",
        "satisfy an optional WHERE clause. If no WHERE clause is included, all\n",
        "rows in the table are deleted. To demonstrate this use of the DELETE\n",
        "statement, we can create a temporary table, insert several rows, and\n",
        "delete them all."
      ],
      "metadata": {
        "id": "5DCjzTi4jsLb"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%writefile delete.sql\n",
        "\n",
        "-- First create the temporary table\n",
        "CREATE TABLE temp (aValue INT) ;\n",
        "\n",
        "-- Insert fake data\n",
        "INSERT INTO temp VALUES(0), (1), (2), (3) ;\n",
        "\n",
        "-- Count rows in the table\n",
        "SELECT COUNT(*) AS COUNT FROM temp ;\n",
        "\n",
        "-- Delete all rows\n",
        "DELETE FROM temp ;\n",
        "\n",
        "-- Count all rows in the table\n",
        "SELECT COUNT(*) AS COUNT FROM temp ;\n",
        "\n",
        "-- Now drop the temporary table\n",
        "\n",
        "DROP TABLE temp ;"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "rALGzn0ljhp8",
        "outputId": "24e22eec-d88e-4530-e7cd-872daad3df69"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Writing delete.sql\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "!sqlite3 test < delete.sql"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "5HF1BJTDjv05",
        "outputId": "1063172a-f892-46c9-dabf-5876432037ab"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "4\n",
            "0\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "The previous example created a single-column temporary table to hold a\n",
        "single integer value. Next we inserted four rows into the database and\n",
        "issued a SELECT statement to verify that the new table contained four\n",
        "rows. By using an unconstrained DELETE statement, we delete all four\n",
        "rows from the temporary table, which is verified by the second SELECT\n",
        "statement, which indicates that the temporary table contains zero rows.\n",
        "Finally, the DROP TABLE statement deletes the empty table from the\n",
        "schema.\n",
        "\n",
        "In general, however, you don't want to delete all rows from a table;\n",
        "instead, you'll selectively delete rows. To do this, you create an\n",
        "appropriate WHERE clause that identifies all rows of interest. The\n",
        "syntax for the WHERE clause that you can use with a DELETE statement is\n",
        "identical to that discussed previously when we presented the full SQL\n",
        "SELECT statement syntax. The basic building blocks for constructing a\n",
        "Boolean expression within a WHERE clause were presented in an earlier\n",
        "table. The following example demonstrates using a WHERE clause in a\n",
        "DELETE statement, where we delete all rows that satisfy at least one of\n",
        "two conditions."
      ],
      "metadata": {
        "id": "pQ20tLJUj0vl"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%writefile delete2.sql\n",
        "\n",
        "-- First display data\n",
        "SELECT itemNumber, description FROM myProducts ;\n",
        "\n",
        "-- Selectively delete rows\n",
        "DELETE FROM myProducts\n",
        "    WHERE description LIKE '%towel%' OR itemNumber <= 3 ;\n",
        "\n",
        "-- Confirm the proper deletion\n",
        "SELECT itemNumber, description FROM myProducts ;"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0hynn8Jmjx6h",
        "outputId": "5f7324c1-863a-4ef6-f9cb-8020447d4b77"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Writing delete2.sql\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "!sqlite3 test < delete2.sql"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "dp5Lf-zIj486",
        "outputId": "f00d397b-e8a1-4c66-97e3-faa87d41b069"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "1|Hooded sweatshirt\n",
            "2|Beach umbrella\n",
            "3|\n",
            "4|Male bathing suit, blue\n",
            "5|Female bathing suit, one piece, aqua\n",
            "6|Child sand toy set\n",
            "7|White beach towel\n",
            "8|Blue-striped beach towel\n",
            "9|Flip-flop\n",
            "10|Open-toed sandal\n",
            "4|Male bathing suit, blue\n",
            "5|Female bathing suit, one piece, aqua\n",
            "6|Child sand toy set\n",
            "9|Flip-flop\n",
            "10|Open-toed sandal\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "In this example, the DELETE statement includes a WHERE clause that\n",
        "identifies five rows. The WHERE clause contains two expressions that are\n",
        "joined by the OR operator, which means that if either expression\n",
        "evaluates as TRUE for a specific row, that row will be deleted.\n",
        "\n",
        "The first expression finds all rows that contain the word \"towel\" in the\n",
        "product description. If you recall, there are two towels in the\n",
        "myProducts table, with itemNumber column values of 7 and 8. The other\n",
        "expression selects all rows with an itemNumber column value less than or\n",
        "equal to 3. The contents of the myProducts table are finally displayed\n",
        "with a simple SELECT statement, demonstrating that only five of the\n",
        "original ten rows remain in the table.\n",
        "\n",
        "Although this example doesn't explicitly demonstrate their use, you can\n",
        "also include the SQL functions to gain more control over the selection\n",
        "of rows for deletion. These same functions and other operators that can\n",
        "be used in the WHERE clause of the DELETE statement also can be used\n",
        "with the UPDATE statement to selectively modify the values of rows in a\n",
        "table, as described in the next section.\n",
        "\n",
        "### UPDATE\n",
        "\n",
        "The last SQL task for dealing with data that you need to address is\n",
        "updating specific column values for selected rows in a table. At some\n",
        "level, the SQL UPDATE statement is the union of the SQL INSERT and\n",
        "DELETE statements, because you must select rows to modify as well as\n",
        "specify how to modify them. Formally, the UPDATE statement syntax is\n",
        "straightforward, because you must specify the new column values for the\n",
        "set of rows to be updated:\n",
        "\n",
        "```\n",
        "UPDATE tableName\n",
        "    SET columnName = Value\n",
        "    [ , columnName = Value} ]*\n",
        "    [WHERE clause]\n",
        "```\n",
        "\n",
        "As shown in this SQL syntax, an SQL UPDATE statement must have, at a\n",
        "minimum, one SET component to update one column, along with one or more\n",
        "SET components and a WHERE clause, both of which are optional. If the\n",
        "WHERE clause isn't included, the UPDATE statement modifies the indicated\n",
        "columns for all rows in the table.\n",
        "\n",
        "Issuing an UPDATE statement is fairly easy, as shown in the following\n",
        "code example, where we modify two columns of a single row."
      ],
      "metadata": {
        "id": "3_TfxEzTj_pl"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%writefile update.sql\n",
        "\n",
        "-- Extract the test row\n",
        "SELECT itemNumber, price, stockDate FROM myProducts WHERE itemNumber = 6 ;\n",
        "\n",
        "-- Update the row\n",
        "UPDATE myProducts SET price = price * 1.25, stockDate = date('now')  WHERE itemNumber = 6 ;\n",
        "\n",
        "-- Show the new result\n",
        "SELECT itemNumber, price, stockDate FROM myProducts WHERE itemNumber = 6 ;"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "pT0ZUWdCj6lb",
        "outputId": "deb4b083-d07d-4dc9-8b87-08d335ecd61c"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Writing update.sql\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "!sqlite3 test < update.sql"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "AR6Sjox8kCmK",
        "outputId": "60149c46-d423-4cfd-ba47-eb34f88f4f66"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "6|9.95|2015-01-15\n",
            "6|12.4375|2024-09-28\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "This example wraps a single UPDATE statement with SELECT statements to\n",
        "demonstrate the change to the target row. The SELECT statements both\n",
        "select three columns from the myProducts table for a single row\n",
        "(the row with the value 6 in the itemNumber column). The UPDATE\n",
        "statement modifies both the price and the stockDate columns for this\n",
        "specific row. The value in the price column is increased by 25% (for\n",
        "example, perhaps due to the item's popularity), and the stockDate column\n",
        "is modified to hold the current date, which can be obtained easily with\n",
        "SQLite by using the built-in `date` function with an argument of `now`\n",
        "in an SQL query.\n",
        "\n",
        "The previous example demonstrated how to modify multiple column values\n",
        "for a specific row in a single table. However, sometimes the logic to\n",
        "select rows to update is more complex. For example, suppose you need to\n",
        "modify the price of all objects in the myProducts table that you obtain\n",
        "from Quiet Beach Industries, which has a value of 3 in the vendorNumber\n",
        "column in the myVendors table. To do this, you need to use an embedded\n",
        "query:"
      ],
      "metadata": {
        "id": "vo-ocQVmkHER"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%writefile update2.sql\n",
        "\n",
        "-- Update the table\n",
        "UPDATE myProducts\n",
        "    SET price = price * 1.10, description = 'NEW: ' || description\n",
        "    WHERE itemNumber IN\n",
        "        ( SELECT v.itemNumber\n",
        "          FROM myProducts as p, myVendors as v\n",
        "          WHERE p.itemNumber = v.itemNumber AND v.vendorNumber = 3 ) ;\n",
        "\n",
        "-- Show new results\n",
        "SELECT * FROM myProducts ;"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "OTUSIQChkEQh",
        "outputId": "979227f6-4b87-40c5-d3ec-52f24424f605"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Writing update2.sql\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "!sqlite3 test < update2.sql"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "4GMU8dPjkJ9B",
        "outputId": "9dd2afd9-5d39-4fca-c409-2201e568b413"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "4|29.95|2015-02-10|Male bathing suit, blue\n",
            "5|49.95|2015-02-20|Female bathing suit, one piece, aqua\n",
            "6|12.4375|2024-09-28|Child sand toy set\n",
            "9|14.245|2015-03-12|NEW: Flip-flop\n",
            "10|38.445|2015-01-24|NEW: Open-toed sandal\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "In this example, the UPDATE statement modifies the price and description\n",
        "columns for all products that are obtained from the vendor with a value\n",
        "of 3 in the vendorNumber column in the myVendors table. Because you\n",
        "can't do a simple join within an UPDATE statement, you must include a\n",
        "subquery in the WHERE clause to extract the itemNumber rows that\n",
        "correspond to products from Quiet Beach Industries. The WHERE clause in\n",
        "the UPDATE statement uses the IN operator to select those rows that have\n",
        "an itemNumber column in the set of values selected by the embedded query.\n",
        "\n",
        "Two types of queries can be used in the WHERE clause of an UPDATE\n",
        "statement: a scalar subquery and a table subquery. A scalar subquery is\n",
        "an embedded query that returns a single row that contains a single\n",
        "column-essentially, a single value, which is known as a scalar. You can\n",
        "use a scalar subquery to select a specific value that will be used in\n",
        "the expression of the WHERE clause. For example,\n",
        "`itemNumber = (Scalar Subquery)` updates any rows that have a\n",
        "value in the itemNumber column that matches the result of the\n",
        "scalar subquery.\n",
        "\n",
        "A table subquery, on the other hand, can return multiple rows that\n",
        "generally only have one column. In certain instances, a table subquery\n",
        "can contain multiple columns. To use a table subquery, you need to use\n",
        "an SQL operator to combine the embedded query with a Boolean expression.\n",
        "For example, this was shown in the previous code listing, where the IN\n",
        "operator selected all rows from the myProducts table that were produced\n",
        "by Quiet Beach Industries.\n",
        "\n",
        "### ALTER TABLE\n",
        "\n",
        "The previous section discussed modifying the data that already exists in\n",
        "a table. The other possibility is modifying the structure, or schema, of\n",
        "a database table. This can take the form of adding a column, changing\n",
        "the data type for a column, adding a constraint, or even deleting a\n",
        "column. This process isn't easy, which is one reason to be careful when\n",
        "you initially design your schema. If you do need to modify the structure\n",
        "of a table, you should use a temporary table."
      ],
      "metadata": {
        "id": "T9nqN6-HkPtE"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%writefile rename.sql\n",
        "\n",
        "-- Create New table with extra column\n",
        "\n",
        "CREATE TABLE newProducts (\n",
        "    itemNumber INT NOT NULL,\n",
        "    price REAL,\n",
        "    stockDate TEXT,\n",
        "    count INT NOT NULL DEFAULT 0,\n",
        "    description TEXT\n",
        ") ;\n",
        "\n",
        "-- New copy old table into new table\n",
        "\n",
        "INSERT INTO newProducts(itemNumber, price, stockDate, description)\n",
        "    SELECT itemNumber, price, stockDate, description FROM myProducts ;\n",
        "\n",
        "-- Drop old table\n",
        "DROP TABLE myProducts ;\n",
        "\n",
        "-- Now Rename new table to old table name\n",
        "ALTER TABLE newProducts RENAME TO myProducts ;\n",
        "\n",
        "-- Show the results\n",
        "SELECT * FROM myProducts ;"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "jEin5lJykL4l",
        "outputId": "8709b543-6f6b-4ce3-a016-e008e2b12571"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Writing rename.sql\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Execute SQL Script\n",
        "\n",
        "!sqlite3 test < rename.sql"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "QgUrzPaMkSmj",
        "outputId": "c1e90ea0-6b28-49e1-861d-8483b2c1e1cd"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "4|29.95|2015-02-10|0|Male bathing suit, blue\n",
            "5|49.95|2015-02-20|0|Female bathing suit, one piece, aqua\n",
            "6|12.4375|2024-09-28|0|Child sand toy set\n",
            "9|14.245|2015-03-12|0|NEW: Flip-flop\n",
            "10|38.445|2015-01-24|0|NEW: Open-toed sandal\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "As this example shows, to modify a table-in this case, to add a new\n",
        "`count` column to the `myProducts` table-you first create a table that\n",
        "has the exact schema you require. This example requires that it always\n",
        "have a valid value by including the column constraint NOT NULL and\n",
        "assigns a default value of 0 to the count column by using the column\n",
        "constraint DEFAULT 0. Notice how you can combine multiple column\n",
        "constraints by listing them sequentially.\n",
        "\n",
        "The next step is to copy the existing data from the original table to\n",
        "the new table. You can do so by using an SQL INSERT statement that uses\n",
        "a subquery to get the values to insert. This is a powerful technique\n",
        "that lets you easily copy all or part of an existing table into a second\n",
        "table.\n",
        "\n",
        "After you've created the new table and copied the appropriate data, you\n",
        "drop the old table by using an SQL DROP TABLE statement and rename the\n",
        "new table to the original name by using an SQL RENAME TABLE statement.\n",
        "The rename operation is straightforward: Rename the oldTableName to the\n",
        "newTableName, but don't supply a schema name for the new table name\n",
        "because the RENAME operation can't move a table between different\n",
        "database schemas. This example concludes by issuing a SELECT statement\n",
        "to display the schema and contents of the new myProducts table. As\n",
        "you can see, the new table has five columns, and the count column is\n",
        "always zero. At this point, a real application would modify the count\n",
        "column appropriately by issuing the necessary SQL UPDATE statements."
      ],
      "metadata": {
        "id": "z0C6ybm-kaDb"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### CREATE INDEX\n",
        "\n",
        "Relational databases support the concept of an index to speed up\n",
        "queries. An index is often used when a particular column (or columns) is\n",
        "frequently involved in either a WHERE clause or a table join. The syntax\n",
        "for creating an index is rather simple:\n",
        "\n",
        "```\n",
        "CREATE [UNIQUE] INDEX idx_name ON table_name(column [column]*)\n",
        "[WHERE]\n",
        "```\n",
        "\n",
        "In this format, we create an index on one or more columns in a given\n",
        "table. If UNIQUE is present in the index creation, only non-duplicate\n",
        "entries are allowed in the index. The columns can also be followed by\n",
        "either ASC or DESC to indicate the column sort order for the index.\n",
        "Finally, if a WHERE clause is included the index is only a partial index\n",
        "since it does not cover the entire table.\n",
        "\n",
        "As an example, we can create an index on the myProducts table by using\n",
        "the itemNumber column:\n",
        "\n",
        "    CREATE INDEX itn ON myProducts(itemNumber) ;"
      ],
      "metadata": {
        "id": "OMQLfg9HkcpL"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Advanced Features\n",
        "\n",
        "Of course we have only scratched the surface of SQL, despite the length\n",
        "of this IPython Notebook. We have not discussed\n",
        "[views](https://www.sqlite.org/lang_createview.html), which effectively\n",
        "turns a SQL query into a new, read-only table, or\n",
        "[triggers](https://www.sqlite.org/lang_createtrigger.html), which are\n",
        "database operations that are automatically performed when a specific\n",
        "event occurs.\n",
        "\n",
        "Another useful feature is the LIMIT clause. We can use this to restrict the number of rows returned by a particular query. For example, `SELECT * FROM myProducts LIMIT 5 ;` will only return five rows.\n"
      ],
      "metadata": {
        "id": "1hLh-kZFkihI"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Additional References\n",
        "\n",
        "Several sites exist that allow you to try out SQL commands online.\n",
        "\n",
        "1. [W3 Schools SQL][1], a general SQL demo site.\n",
        "2. [SQLZoo][2], allows you to specify the Relational Database to target.\n",
        "\n",
        "[1]: http://www.w3schools.com/SQL/\n",
        "[2]: http://sqlzoo.net/wiki/SELECT_basics"
      ],
      "metadata": {
        "id": "1MGGttcpkqWa"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Exercises\n",
        "\n",
        "The following 5 lab exercises will work through the same flight-based use case.\n",
        "\n",
        "You should understand the next couple of cells, which prepare the database used in the exercises to come."
      ],
      "metadata": {
        "id": "N4NJEYqQqxAd"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "CSV_PATH = \"2001.csv\""
      ],
      "metadata": {
        "id": "UesKN6YHq7Ra"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "In the following code cell, we use the writefile magic function to write a schema that imports 2001.csv and creates a new table named flights."
      ],
      "metadata": {
        "id": "9Mw7e0MTrKRg"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%writefile airport.sql\n",
        "\n",
        "DROP TABLE IF EXISTS flights;\n",
        "\n",
        "CREATE TABLE flights (\n",
        "    year INT,\n",
        "    month INT,\n",
        "    dayOfMonth INT,\n",
        "    dayOfWeek INT,\n",
        "    actualDepartureTime INT,\n",
        "    scheduledDepartureTime INT,\n",
        "    arrivalArrivalTime INT,\n",
        "    scheduledArrivalTime INT,\n",
        "    uniqueCarrierCode TEXT,\n",
        "    flightNumber INT,\n",
        "    tailNumber TEXT,\n",
        "    actualElapsedTime INT,\n",
        "    scheduledElapsedTime INT,\n",
        "    airTime INT,\n",
        "    arrivalDelay INT,\n",
        "    departureDelay INT,\n",
        "    originCode TEXT,\n",
        "    destinationCode TEXT,\n",
        "    distance INT,\n",
        "    taxiIn INT,\n",
        "    taxiOut INT,\n",
        "    cancelled INT,\n",
        "    cancellationCode TEXT,\n",
        "    diverted INT,\n",
        "    carrierDelay INT,\n",
        "    weatherDelay INT,\n",
        "    nasDelay INT,\n",
        "    securityDelay INT,\n",
        "    lateAircraftDelay INT\n",
        ");\n",
        "\n",
        ".separator ,\n",
        ".import 2001.csv flights\n",
        "\n",
        "-- Our file has a header. The following line deletes the header.\n",
        "DELETE FROM flights WHERE Year='Year';"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "HNNQCH6aq-Hx",
        "outputId": "2b718c4a-a807-4507-d5fe-bda7a154506b"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Writing airport.sql\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "We will name our database assignment.db and use IPython's ! magic to redirect the airport.sql code to sqlite3."
      ],
      "metadata": {
        "id": "IsPPQ5JArsKp"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!sqlite3 assignment.db < airport.sql"
      ],
      "metadata": {
        "id": "cfDXNbwQrpcy"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Exercise 1: Flight Count\n",
        "\n",
        "- In the following code cell, write an SQL statement that counts the number of rows in the flights table.\n",
        "\n",
        "Note that comments in SQL begin with -- (not #)."
      ],
      "metadata": {
        "id": "akO2_88Fr-f4"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%writefile count_lines_flights.sql\n",
        "\n",
        "-- YOUR CODE HERE"
      ],
      "metadata": {
        "id": "6LrBkaVGrtjK"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Run the following code cell to check the output of count_lines_flights.sql.  Make sure that this cell passes."
      ],
      "metadata": {
        "id": "jzMB2y8csG4Z"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "nlines_flights = !sqlite3 assignment.db < count_lines_flights.sql\n",
        "print(nlines_flights.s)\n",
        "assert nlines_flights.s == \"49\""
      ],
      "metadata": {
        "id": "xoCGGFVksKnp"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Exercise 2: Creating Another Table\n",
        "\n",
        "We will use another CSV file iata.csv to create a new table.\n",
        "\n",
        "- In the following code cell, write your own schema and SQL script to import iata.csv and create a new table named iata. The names of the columns should be\n",
        "\n",
        "    airportID, name, city, country, iata, icao, latitude, longitude, altitude, timeZone, dst, tzDatabaseTimeZone\n",
        "    \n",
        "Also make sure that your data types are all correct. Use head or otherwise to check the CSV file. If a value is enclosed by quotation marks, it should be a TEXT. If a field has a decimal point, it should be a REAL. If a field is a number with no decimal point, it should be an INT."
      ],
      "metadata": {
        "id": "Jb2lKIzGselY"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%writefile import_iata.sql\n",
        "\n",
        "-- YOUR CODE HERE"
      ],
      "metadata": {
        "id": "_oA3HWctvDnG"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "The following code cell should create a new table named iata in the database assignment.db."
      ],
      "metadata": {
        "id": "M3m0EcmAvGQ6"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!sqlite3 assignment.db < import_iata.sql"
      ],
      "metadata": {
        "id": "MvDy48Q4vHmj"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "I think it's a good idea to print out a few lines and see if the table looks okay."
      ],
      "metadata": {
        "id": "8KGPom-1vKu1"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!sqlite3 assignment.db \"SELECT * FROM iata LIMIT 10\""
      ],
      "metadata": {
        "id": "FHbY77bovLST"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Make sure that the following code cell doesn't raise any errors. You don't have to understand what the SQL statement means. It simply checks if there exists a table named iata in the database and prints out iata if it exists."
      ],
      "metadata": {
        "id": "vHCeHW6zvMsC"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "iata_exists = !sqlite3 assignment.db \"SELECT name FROM sqlite_master WHERE type='table' and name='iata'\"\n",
        "assert iata_exists.s == \"iata\""
      ],
      "metadata": {
        "id": "D_5kKijmvPL6"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "You don't have to understand the code in the following tests, but make sure that your SQL script passes the tests."
      ],
      "metadata": {
        "id": "lJxmkFNBvZoR"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "iata_info = !sqlite3 assignment.db \"PRAGMA table_info(iata)\"\n",
        "iata_names = [i.split(\"|\")[1] for i in iata_info]\n",
        "iata_names_answer = [\n",
        "    \"airportID\",\n",
        "    \"name\",\n",
        "    \"city\",\n",
        "    \"country\",\n",
        "    \"iata\",\n",
        "    \"icao\",\n",
        "    \"latitude\",\n",
        "    \"longitude\",\n",
        "    \"altitude\",\n",
        "    \"timeZone\",\n",
        "    \"dst\",\n",
        "    \"tzDatabaseTimeZone\"\n",
        "]\n",
        "assert len(iata_names) == len(iata_names_answer)\n",
        "assert set(iata_names) == set(iata_names_answer)"
      ],
      "metadata": {
        "id": "d7qc7ptIvaH5"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Exercise 3: Joining Tables\n",
        "\n",
        "-Join flights and iata tables by matching the IATA codes of the destinationCode column in flights to the IATA codes of the iata column in iata. Combine them into a new table named myTable, which should have the following columns:\n",
        "    month, dayOfMonth, uniqueCarrierCode, flightNumber, scheduledDepartureTime, diverted, city\n",
        "\n",
        "All columns excpet city come from the flights table. The city column comes from the iata table, and it's the full city name of the airport that corresponds to destinationCode.\n",
        "\n",
        "In other words, if we did\n",
        "\n",
        "    SELECT\n",
        "    month,\n",
        "    dayOfMonth,\n",
        "    uniqueCarrierCode,\n",
        "    flightNumber,\n",
        "    scheduledDepartureTime,\n",
        "    diverted,\n",
        "    destinationCode\n",
        "    FROM flights\n",
        "    LIMIT 10;\n",
        "    \n",
        "we would have\n",
        "\n",
        "    1|17|US|375|1810|0|CLT\n",
        "    1|18|US|375|1810|0|CLT\n",
        "    1|19|US|375|1810|0|CLT\n",
        "    1|20|US|375|1810|0|CLT\n",
        "    1|21|US|375|1810|0|CLT\n",
        "    1|22|US|375|1810|0|CLT\n",
        "    1|23|US|375|1810|0|CLT\n",
        "    1|24|US|375|1810|0|CLT\n",
        "    1|25|US|375|1810|0|CLT\n",
        "    1|26|US|375|1810|0|CLT\n",
        "    \n",
        "Translate all the IATA codes (CLTs in this example) to actual city names so that when you do\n",
        "\n",
        "    SELECT * FROM myTable LIMIT 10;\n",
        "\n",
        "you get\n",
        "\n",
        "    1|17|US|375|1810|0|Charlotte\n",
        "    1|18|US|375|1810|0|Charlotte\n",
        "    1|19|US|375|1810|0|Charlotte\n",
        "    1|20|US|375|1810|0|Charlotte\n",
        "    1|21|US|375|1810|0|Charlotte\n",
        "    1|22|US|375|1810|0|Charlotte\n",
        "    1|23|US|375|1810|0|Charlotte\n",
        "    1|24|US|375|1810|0|Charlotte\n",
        "    1|25|US|375|1810|0|Charlotte\n",
        "    1|26|US|375|1810|0|Charlotte"
      ],
      "metadata": {
        "id": "L4vwtxN2vmdI"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%writefile join.sql\n",
        "\n",
        "-- YOUR CODE HERE"
      ],
      "metadata": {
        "id": "yJk6WITrvwAh"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "!sqlite3 assignment.db < join.sql"
      ],
      "metadata": {
        "id": "ff8Bd6SFvwyg"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "!sqlite3 assignment.db \"SELECT * FROM myTable LIMIT 10;\""
      ],
      "metadata": {
        "id": "ifJeFNWjvyKR"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Exercise 4: Inserting\n",
        "\n",
        "Insert a new row into myTable. This flight\n",
        "- took place on September 9, 2001,\n",
        "- its uniqueCarrierCode was INFO,\n",
        "- its flightNumber was 490,\n",
        "- its scheduledDepartureTime was 0800,\n",
        "- was diverted (i.e. diverted == 1), and\n",
        "- left from San Francisco."
      ],
      "metadata": {
        "id": "KlZZGDuEwQuJ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%writefile insert.sql\n",
        "\n",
        "-- YOUR CODE HERE"
      ],
      "metadata": {
        "id": "l_AaeI5KwdCR"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "!sqlite3 assignment.db < insert.sql"
      ],
      "metadata": {
        "id": "xKjy-aJOwe-N"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "info_month = !sqlite3 assignment.db \"SELECT month FROM myTable WHERE uniqueCarrierCode='INFO';\"\n",
        "info_day = !sqlite3 assignment.db \"SELECT dayOfMonth FROM myTable WHERE uniqueCarrierCode='INFO';\"\n",
        "info_flight_no = !sqlite3 assignment.db \"SELECT flightNumber FROM myTable WHERE uniqueCarrierCode='INFO';\"\n",
        "info_crs_dep = !sqlite3 assignment.db \"SELECT scheduledDepartureTime FROM myTable WHERE uniqueCarrierCode='INFO';\"\n",
        "info_diverted = !sqlite3 assignment.db \"SELECT diverted FROM myTable WHERE uniqueCarrierCode='INFO';\"\n",
        "info_dest = !sqlite3 assignment.db \"SELECT city FROM myTable WHERE uniqueCarrierCode='INFO';\"\n",
        "\n",
        "print('''\n",
        "UniqueCarrierCode: {0}\n",
        "Month: {1}\n",
        "Day: {2}\n",
        "Flight Number: {3}\n",
        "Scheduled Departure Time: {4}\n",
        "Diverted: {5}\n",
        "Origin City: {6}\n",
        "'''.format(\n",
        "    \"INFO\",\n",
        "    info_month.s,\n",
        "    info_day.s,\n",
        "    info_flight_no.s,\n",
        "    info_crs_dep.s,\n",
        "    info_diverted.s,\n",
        "    info_dest.s\n",
        "    )\n",
        ")"
      ],
      "metadata": {
        "id": "pgBTLyOXwgN_"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "assert \"9\" == info_month.s\n",
        "assert \"9\" == info_day.s\n",
        "assert \"490\" == info_flight_no.s\n",
        "assert \"800\" == info_crs_dep.s\n",
        "assert \"1\" == info_diverted.s\n",
        "assert \"San Francisco\" == info_dest.s"
      ],
      "metadata": {
        "id": "3QhXiz5SwjhI"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Exercise 5: Query Maximum\n",
        "- Compute the maximum of the departureDelay column in the flights table.\n"
      ],
      "metadata": {
        "id": "LEHlFCfNw6Lu"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%writefile get_maximum_depdelay.sql\n",
        "\n",
        "-- YOUR CODE HERE"
      ],
      "metadata": {
        "id": "a7zHFxiYxMFS"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "maximum_depdelay = !sqlite3 assignment.db < get_maximum_depdelay.sql\n",
        "print(maximum_depdelay)\n",
        "assert maximum_depdelay.s == '100'"
      ],
      "metadata": {
        "id": "eeHN8ceSxOBr"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}