{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "source": [
    "# SQL Kernel\n",
    "SQL (Structured Query Language) is one of the oldest and most popular languages for database access.\n",
    "Beaker has first-class support for SQL, including syntax highlighting, autocompletion, and autotranslation.\n",
    "Most of the languages in Beaker have their own SQL APIs, and you can use them as well.\n",
    "\n",
    "In order to be completely self-contained, this demo notebook uses an in-memory H2 database, \n",
    "and it’s built from scratch in the first cell.\n",
    "Normally you would connect to an existing database by putting its JDBC configuration\n",
    "in the notebook metadata.\n",
    "\n",
    "For example, if you want to connect to a MySQL running locally then use:\n",
    "jdbc:mysql://localhost/dbName, where dbName would be replaced as appropriate.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "drop table if exists color;\n",
    "CREATE TABLE color (\n",
    "  id int(11) NOT NULL,\n",
    "  name varchar(45) NOT NULL,\n",
    "  code varchar(10),\n",
    "  PRIMARY KEY (id)\n",
    ");\n",
    "\n",
    "INSERT INTO color (id, name, code) VALUES (1001,'AliceBlue','#F0F8FF');\n",
    "INSERT INTO color (id, name, code) VALUES (1002,'AntiqueWhite','#FAEBD7');\n",
    "INSERT INTO color (id, name, code) VALUES (1003,'Aqua','#00FFFF');\n",
    "INSERT INTO color (id, name, code) VALUES (1004,'Aquamarine','#7FFFD4');\n",
    "INSERT INTO color (id, name, code) VALUES (1005,'Azure','#F0FFFF');\n",
    "INSERT INTO color (id, name, code) VALUES (1006,'Beige','#F5F5DC');\n",
    "INSERT INTO color (id, name, code) VALUES (1007,'Bisque','#FFE4C4');\n",
    "INSERT INTO color (id, name, code) VALUES (1008,'Black','#000000');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "SELECT * FROM color WHERE name LIKE 'A%';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Autotranslation of results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "null"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select * into ${colorTable} from color;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "\n",
       "beaker.displayHTML(this, beaker.colorTable.values+\"\")"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%javascript\n",
    "beaker.displayHTML(this, beaker.colorTable.values+\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "Autocomplete"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "SELECT * FROM color WHE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "INSERT INTO color (id, name, code) VA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "%%beakerDB "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%"
   ]
  }
 ],
 "metadata": {
  "beakerx_kernel_parameters": {
   "datasources": "chemistry=jdbc:h2:mem:chemistry",
   "defaultDatasource": "jdbc:h2:mem:db1"
  },
  "kernelspec": {
   "display_name": "SQL",
   "language": "SQL",
   "name": "sql"
  },
  "language_info": {
   "codemirror_mode": "sql",
   "file_extension": ".sql",
   "mimetype": "",
   "name": "SQL",
   "nbconverter_exporter": "",
   "version": ""
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}