ex00-Introduction.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction\n",
    "\n",
    "[SQL](https://en.wikipedia.org/wiki/SQL) (Structured Query Language, pronounced \"sequel\" or ess-cue-ell) is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data where there are relations between different entities/variables of the data. SQL is a very important tool for data scientists to have in their repertoire. \n",
    "\n",
    "A relational database is, simply, a database that stores related information across multiple tables and allows you to query information in more than one table at the same time. Within a table, the data to be stored is organized in a tabular format with rows and columns. Each row inside a table represents a distinct record with the column headings specifying the corresponding type of data stored. Software that is used to manage a digital database is called a Database Management System (DBMS). There are quite a few database systems available (such as MySQL, PostgreSQL, IBM Db2 and Oracle Database, etc.). The Web site [DB-Engines](http://dbengines.com/en/ranking/relational+dbms) collects and presents information on DBMSs and provides a monthly listing of them, ranked by their current popularity. You’ll likely find yourself using many different types of databases throughout your education and work. However, all of them speak SQL, so once you've got the hang of the basic SQL syntax you'll be able to work with any of them.\n",
    "\n",
    "To keep things simple, we will be using a database system called [SQLite](https://www.sqlite.org/about.html) to practice SQL syntax. SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. It is the most widely deployed database in the world. But every technology has a trade-off. Because it has no server managing access to it, it fails in multiuser environments where multiple people can simultaneously edit the SQLite file.\n",
    "\n",
    "There are several ways to query a SQLite database with python such as sqlite3 and SQLAlchemy. However, we use **[ipython-sql](https://github.com/catherinedevlin/ipython-sql)** Magic functions to query a sqllite database as it is more straightforward and concise. ipython-sql introduces a %sql (or %%sql) magic to notebooks allowing you to connect to a database, using SQLAlchemy connect strings, then issue SQL commands within IPython or [Jupyter](http://jupyter.org/)/IPython Notebook. However, if you do not use the %%sql magic in your Jupyter notebook, the output of your SQL queries will be just a plain list of tuples. A better way to work with the result sets returned is to draw them as a table with the headers. This is where the IPython SQL magic gets very handy.\n",
    "\n",
    "You can install it using ***pip install ipython-sql*** or ***conda install ipython-sql***. The ipython-sql library is loaded using the %load_ext just like other iPython extension syntax. \n",
    "\n",
    "#### Advantages of Using SQL (Rahul, 2018)\n",
    "\n",
    "- It is standardized\n",
    ">no matter which relational database you choose, it will have an SQL query interpreter built in. The sheer popularity of SQL makes it worth everyone’s time who interacts with a data system.\n",
    "\n",
    "- It has a reasonable English-like syntax. \n",
    ">None of the painstaking detail of programming languages like C or Java have to be specified when using SQL. It is concise, easy to understand, and easy to write database queries with. It is declarative in nature, meaning you only have to declare what you want to achieve rather than going over the steps to achieve the results.\n",
    "- It allows a uniform way to query and administer a relational database. \n",
    ">Many of the database administration commands are standard SQL commands making the transfer of skills much easier.\n",
    "- It is mature – SQL has been around for over 35 years.\n",
    ">While many new features have been added to it, the core of SQL has largely been unchanged. You can derive\n",
    "a lot of utility knowing a few basic SQL concepts and commands, and they will serve you well into the future.\n",
    "\n",
    "However, please keep it mind that while it is similar across DBMS, there are subtle differences. These differences result from different interpretations of the standard, different development styles, or different underlying architectures. In practice, refer to their techinical documents for details.\n",
    "\n",
    "\n",
    "### References\n",
    "\n",
    "SQL Primer: An Accelerated Introduction to SQL Basics by Rahul Batra. Rahul Batra (2018). https://doi.org/10.1007/978-1-4842-3576-8_7\n",
    "\n",
    "Effective SQL: 61 Specific Ways to Write Better SQL by by John L. Viescas, Douglas J. Steele, Ben G. Clothier (2016). ISBN-13: 978-0134578897.\n",
    "\n",
    "https://www.sqlite.org/about.html\n",
    "\n",
    "https://github.com/catherinedevlin/ipython-sql\n",
    "\n",
    "https://en.wikipedia.org/wiki/SQL\n",
    "\n",
    "http://jupyter.org/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [default]",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}