SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SQL (Structured Query Language) is a standard language for storing, manipulating and retrieving data in databases. SQL statements are not case sensitive, and certain SQL programs require semi-colons at the end of the statement."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Relational Database Management System**\n",
    "\n",
    "RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.\n",
    "\n",
    "Data in a RDMS is stored in tables, which consists of columns (fields) and rows (records)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Major SQL Commands:**\n",
    "\n",
    "* Select\n",
    "* Update\n",
    "* Delete\n",
    "* Insert\n",
    "* Where\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Select"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Used to select and return data from a database. The returned data is stored in a result table, or a result-set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT * FROM table1 --select all fields from table\n",
    "SELECT column1, column2 FROM table1  --select certain fields from table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Distinct**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT DISTINCT column1 FROM table1  --returns all unique values in column1\n",
    "SELECT count(DISTINCT column1) FROM table1  --counts the number of unique values in column1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Top/Limit/Fetch**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* syntax varies based on database systems */\n",
    "\n",
    "SELECT TOP 10 * FROM table1 WHERE condition  --SQL Server/MS Access\n",
    "SELECT TOP 5 percent * FROM table1 WHERE condition  --SQL Server/MS Access\n",
    "\n",
    "SELECT * FROM table1 WHERE condition LIMIT 10  --MySQL\n",
    "\n",
    "SELECT * FROM table1 WHERE condition FETCH FIRST 10 ROWS only  --Oracle\n",
    "SELECT * FROM table1 WHERE condition FETCH FIRST 5 percent only  --Oracle"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Min Max**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT min(column1) FROM table1 WHERE condition\n",
    "\n",
    "SELECT max(column1) FROM table1 WHERE condition"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Count, Average, Sum**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT count(*) FROM table1 WHERE condition  --return number of records that meet a condition\n",
    "SELECT count(column1) FROM table1 WHERE condition  --return number of records in a specific column that meet a condition\n",
    "\n",
    "SELECT avg(column1) FROM table1 WHERE condition\n",
    "\n",
    "SELECT sum(column1) FROM table1 WHERE condition"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Alias**\n",
    "\n",
    "AS is used to give a table or a column name an alias, which exists only for the duration of the query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT column1 AS COLUMN_NAME FROM table1\n",
    "SELECT column1 AS COLUMN_NAME1, column2 AS COLUMN_NAME2 FROM table1\n",
    "\n",
    "SELECT column1 + column2 + column3 AS column4 FROM table1\n",
    "\n",
    "SELECT column1 FROM table1 AS table_name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Into**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Copy columns into a new table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT * \n",
    "INTO newtable\n",
    "FROM table1\n",
    "WHERE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT column1, column2, column3\n",
    "INTO newtable\n",
    "FROM table1\n",
    "WHERE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Copy schema of one table into another table that will be blank */\n",
    "\n",
    "SELECT *\n",
    "INTO newtable\n",
    "FROM table1\n",
    "WHERE 1 = 0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Insert Into Select**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Copy data from one table into a new table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "INSERT INTO newtable\n",
    "SELECT *\n",
    "FROM table1\n",
    "WHERE condition"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Where"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Used to filter records in a table that meet a certain condition. SQL requires single quotes around strings, but not around numeric values."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Where Operators**\n",
    "\n",
    "* =\n",
    "* '>'\n",
    "* <\n",
    "* '>='\n",
    "* <=\n",
    "* <> (not equal, may appear as != in some versions of SQL)\n",
    "* Between (between a range)\n",
    "* Like (search for a pattern)\n",
    "* In (multiple possible values in a column)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT * FROM table1 WHERE condition;\n",
    "SELECT * FROM table1 WHERE column1 = 'string';  --filter based on string\n",
    "SELECT * FROM table1 WHERE column2 = 10;  --filter based on numeric value\n",
    "SELECT * FROM table1 WHERE column3 IS NULL  --test for null values\n",
    "SELECT * FROM table1 WHERE column3 IS NOT NULL  --test for values that are not null"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**And, Or, Not**\n",
    "\n",
    "Filter records based on more than one condition. 'And' requires that all conditions be true, whereas 'or' requires only requires any condition to be true. Not returns a condition if none of the conditions are true."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT * FROM table1 WHERE condition1 AND condition2\n",
    "SELECT * FROM table1 WHERE NOT string = \"a\" AND NOT string = \"b\"\n",
    "SELECT * FROM table1 WHERE columnA = \"a\" AND (columnB = \"string\" OR columnC = \"string2\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Like**\n",
    "\n",
    "Used to search for a specific pattern in a column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT * FROM table1 Where column1 LIKE pattern\n",
    "SELECT * FROM table1 Where column1 LIKE 'a%'  --find values that start with 'a'\n",
    "SELECT * FROM table1 Where column1 LIKE '%a'  --find values that end with 'a'\n",
    "SELECT * FROM table1 Where column1 LIKE '%diamond%'  --find values that contain 'diamond'\n",
    "SELECT * FROM table1 Where column1 NOT LIKE '%diamond%'  --find values that do not contain 'diamond'\n",
    "SELECT * FROM table1 Where column1 LIKE '_r%'  --find values that contain the letter 'r' in the second position\n",
    "SELECT * FROM table1 Where column1 LIKE 'a_%'  --find values that start with 'a' and are at least 2 characters long\n",
    "SELECT * FROM table1 Where column1 LIKE 'a__%'  --find values that start with 'a' and are at least 3 characters long\n",
    "SELECT * FROM table1 Where column1 LIKE 'a%o'  --find values that start with 'a' and end with 'o'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Wildcard**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT * FROM table1 WHERE column1 LIKE 'h_t'  --returns values such as hot, hat, hit\n",
    "SELECT * FROM table1 WHERE column1 LIKE 'h[oa]t'  --returns values such as hot, hat, but not hit\n",
    "SELECT * FROM table1 WHERE column1 LIKE 'h[^oa]t'  --returns values such as hit, but not hot or hat\n",
    "SELECT * FROM table1 WHERE column1 LIKE 'h[a-b]t'  --returns values such as hat and hbt\n",
    "SELECT * FROM table1 WHERE column1 LIKE '[ac]%'  --returns values that start with 'a' or 'c'\n",
    "SELECT * FROM table1 WHERE column1 LIKE '[a-c]%'  --returns values that start with 'a', 'b', or 'c'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**In**\n",
    "\n",
    "In is shorthand for multiple OR statements."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT * FROM table1 WHERE column1 IN (value1, value2, value3)\n",
    "SELECT * FROM table1 WHERE column1 NOT IN (value1, value2, value3)\n",
    "SELECT * FROM table1 WHERE column1 IN (SELECT column1 FROM table2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Between**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT * FROM table1 WHERE column1 BETWEEN value1 AND value2\n",
    "SELECT * FROM table1 WHERE column1 BETWEEN 1 AND 10\n",
    "SELECT * FROM table1 WHERE column1 NOT BETWEEN 1 AND 10\n",
    "SELECT * FROM table1 WHERE column1 BETWEEN 1 AND 10 AND column2 NOT IN (1, 3, 5)\n",
    "SELECT * FROM table1 WHERE column1 BETWEEN 'apple' AND 'grape' ORDER BY column1\n",
    "SELECT * FROM table1 WHERE column1 BETWEEN '1996-07-01' AND '1996-07-31'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exists**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Tests for the existence of any record in a subquery and returns TRUE if the subquery returns one or more records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Returns TRUE and lists the suppliers with a product price less than 20 */\n",
    "\n",
    "SELECT SupplierName\n",
    "FROM Suppliers\n",
    "WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Any / All**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Perform a comparison between a single column value and a range of other values.\n",
    "\n",
    "ANY - returns true if ANY of the subquery values meet the condition.<br>\n",
    "ALL - returns true if ALL of the subquery values meet the condition."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Returns the ProductName if it finds any records in the OrderDetails table have Quantity equal to 10  */\n",
    "\n",
    "SELECT ProductName\n",
    "FROM Products\n",
    "WHERE ProductID = ANY \n",
    "    (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Return the ProductName if all the records in the OrderDetails table have Quantity equal to 10 */\n",
    "\n",
    "SELECT ProductName\n",
    "FROM Products\n",
    "WHERE ProductID = ALL\n",
    "  (SELECT ProductID\n",
    "  FROM OrderDetails\n",
    "  WHERE Quantity = 10);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Order By"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sort the result-set in ascending or descending order. Default is ascending."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT * FROM table1 ORDER BY column1 ASC  --sort ascending\n",
    "SELECT * FROM table1 ORDER BY column1 DESC  --sort descending\n",
    "SELECT * FROM table1 ORDER BY column1 ASC, column2 DESC  --sort by two fields"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Insert Into"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Adds new records into a table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "INSERT INTO table1 (column3) VALUES (value3)  --add values into specific columns\n",
    "INSERT INTO table1 VALUES (value1, value2, value3)  --add values into all columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Update"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Modify existing records in a table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Without the where statement, all records in the table will be updated */\n",
    "\n",
    "UPDATE table1 SET column1 = value1, column2 = value2 WHERE id = 1  --use where to specify which row/index to modify\n",
    "UPDATE table1 SET column1 = value1 WHERE condition  --change all rows where the condition is met\n",
    "UPDATE table1 SET column1 = value1  --update all records in column1 to value1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Delete"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Delete records from a table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* without a where statement all records in the table will be deleted */\n",
    "\n",
    "DELETE FROM table1 WHERE condition\n",
    "DELETE FROM table1  --deletes all records in a table, but retains the table structure, attributes, and indexes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Join"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "source": [
    "Used to combine records from two or more tables.\n",
    "\n",
    "Types of joins:\n",
    "* Inner Join\n",
    "* Left (Outer) Join\n",
    "* Right (Outer) Join\n",
    "* Full (Outer) Join"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Inner Join**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Returns records that have matching values in both tables. If there are records in one table but not the other, they won't be shown."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<img src=\"https://www.w3schools.com/sql/img_innerjoin.gif\"/>"
      ],
      "text/plain": [
       "<IPython.core.display.Image object>"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from IPython.display import Image\n",
    "Image(url= \"https://www.w3schools.com/sql/img_innerjoin.gif\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT column_name(s)\n",
    "FROM table1\n",
    "INNER JOIN table2\n",
    "ON table1.column_name = table2.column_name;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Select all orders with customer and shipper information */\n",
    "\n",
    "SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName\n",
    "FROM ((Orders\n",
    "INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)\n",
    "INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Left Join**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Returns all records from the left table, and the matched records from the right table. Left Join returns all records from the left table even if there are not matches in the right table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<img src=\"https://www.w3schools.com/sql/img_leftjoin.gif\"/>"
      ],
      "text/plain": [
       "<IPython.core.display.Image object>"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Image(url= \"https://www.w3schools.com/sql/img_leftjoin.gif\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT column_name(s)\n",
    "FROM table1\n",
    "LEFT JOIN table2\n",
    "ON table1.column_name = table2.column_name;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Right Join**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Returns all records from the right table, and the matched records from the left table. Right Join returns all records from the left table even if there are not matches in the right table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<img src=\"https://www.w3schools.com/sql/img_rightjoin.gif\"/>"
      ],
      "text/plain": [
       "<IPython.core.display.Image object>"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Image(url= \"https://www.w3schools.com/sql/img_rightjoin.gif\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT column_name(s)\n",
    "FROM table1\n",
    "RIGHT JOIN table2\n",
    "ON table1.column_name = table2.column_name;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Full Outer Join**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Returns all records in the left and right tables, whether there is a match or not, which can result in a a very large result-set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<img src=\"https://www.w3schools.com/sql/img_fulljoin.gif\"/>"
      ],
      "text/plain": [
       "<IPython.core.display.Image object>"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Image(url= \"https://www.w3schools.com/sql/img_fulljoin.gif\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT column_name(s)\n",
    "FROM table1\n",
    "FULL OUTER JOIN table2\n",
    "ON table1.column_name = table2.column_name\n",
    "WHERE condition;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Self Join**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Joins a table with itself. Requires using two different aliases for the same table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT column_name(s)\n",
    "FROM table1\n",
    "FULL OUTER JOIN table2\n",
    "ON table1.column_name = table2.column_name\n",
    "WHERE condition;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Matches customers that are from the same city */\n",
    "\n",
    "SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City\n",
    "FROM Customers A, Customers B\n",
    "WHERE A.CustomerID <> B.CustomerID\n",
    "AND A.City = B.City \n",
    "ORDER BY A.City;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Union"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Combine the result-set of two or more SELECT statements. The SELECT statements withing the UNION statement must have the same number and order of columns and date data types."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* UNION selects only distinct values by default even if a record appears in both tables */\n",
    "\n",
    "SELECT * FROM table1\n",
    "UNION \n",
    "SELECT * FROM table2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* To select duplicate values across tables, use UNION ALL */\n",
    "\n",
    "SELECT * FROM table1\n",
    "UNION ALL\n",
    "SELECT * FROM table2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT City, Country FROM Customers\n",
    "WHERE Country='Germany'\n",
    "UNION\n",
    "SELECT City, Country FROM Suppliers\n",
    "WHERE Country='Germany'\n",
    "ORDER BY City;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Group By"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Groups rows that have the same values into summary rows. Usually used with:\n",
    "* COUNT()\n",
    "* MAX()\n",
    "* MIN()\n",
    "* SUM()\n",
    "* AVG()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT *\n",
    "FROM table1\n",
    "WHERE condition\n",
    "GROUP BY column1\n",
    "ORDER BY column2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Returns number of customers per country */\n",
    "\n",
    "SELECT COUNT(CustomerID), Country\n",
    "FROM Customers\n",
    "GROUP BY Country\n",
    "ORDER BY COUNT(CustomerID) DESC;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Returns the number of orders by shipper */\n",
    "\n",
    "SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders\n",
    "LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID\n",
    "GROUP BY ShipperName;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Having"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Used with aggregate functions, since WHERE cannot."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Returns the number of customers per country, only for countries with more than 5 customers */\n",
    "\n",
    "SELECT COUNT(CustomerID), Country\n",
    "FROM Customers\n",
    "GROUP BY Country\n",
    "HAVING COUNT(CustomerID) > 5\n",
    "ORDER BY COUNT(CustomerID) DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Case"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "An if/then statement that returns a value when the first condition is met. If no conditions are true, returns the Else statement. If there is no Else statement, returns null."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "SELECT OrderID, Quantity,\n",
    "CASE WHEN Quantity > 30 THEN 'The quantity is greater than 30'\n",
    "WHEN Quantity = 30 THEN 'The quantity is 30'\n",
    "ELSE 'The quantity is under 30'\n",
    "END AS QuantityText  /*new column created that indicates if quantity is greater than, less than, or equal to 30*/\n",
    "FROM OrderDetails;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/*Sorts by City, but if City is null then sort by Country*/\n",
    "\n",
    "SELECT CustomerName, City, Country\n",
    "FROM Customers\n",
    "ORDER BY\n",
    "(CASE\n",
    "    WHEN City IS NULL THEN Country\n",
    "    ELSE City\n",
    "END);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Null Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Without IFNULL, if UnitsOnOrder column contains a null then the result of the operation will return null */\n",
    "/* Can also use COALESCE() instead */\n",
    "/* Function depends on SQL program used */\n",
    "\n",
    "SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))\n",
    "FROM Products;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Stored Procedure"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SQL code that you can save and use over and over. It just needs to be called to execute it. SQL version of a function in Python."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Store procedure */\n",
    "\n",
    "CREATE PROCEDURE procedure_name\n",
    "AS \n",
    "sql_statement \n",
    "GO;\n",
    "\n",
    "/* Execute procedure */\n",
    "EXEC procedure_name;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Example */\n",
    "\n",
    "CREATE PROCEDURE SelectAllCustomers\n",
    "AS\n",
    "SELECT * FROM Customers\n",
    "GO;\n",
    "\n",
    "/* Execute procedure */\n",
    "\n",
    "EXEC SelectAllCustomers;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Stored procedure with one parameter */\n",
    "\n",
    "CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)\n",
    "AS\n",
    "SELECT * FROM Customers WHERE City = @City\n",
    "GO;\n",
    "\n",
    "EXEC SelectAllCustomers @City = 'London';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Stored procedure with multiple parameters */\n",
    "\n",
    "CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)\n",
    "AS\n",
    "SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode\n",
    "GO;\n",
    "\n",
    "EXEC SelectAllCustomers @City = 'London', @PostalCode = 'WA1 1DP';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL Comments"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Comments can be used to either explain code or prevent the execution of SQL statements."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "--Single line comment\n",
    "--Can be used at the end of a SQL statement to ignore the end of a line\n",
    "\n",
    "SELECT * FROM Customers -- WHERE City='Berlin';\n",
    "\n",
    "--SELECT * FROM Customers;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Multi-line comment */\n",
    "\n",
    "SELECT CustomerName, /*City,*/ Country FROM Customers;\n",
    "\n",
    "SELECT * FROM Customers WHERE (CustomerName LIKE 'L%'\n",
    "OR CustomerName LIKE 'R%' /*OR CustomerName LIKE 'S%'\n",
    "OR CustomerName LIKE 'T%'*/ OR CustomerName LIKE 'W%')\n",
    "AND Country='USA'\n",
    "ORDER BY CustomerName;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL Operators"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Arithmetic */\n",
    "\n",
    "+ --Add\n",
    "- --Subtract\n",
    "* --Multiply\n",
    "/ --Divide\n",
    "% --Modulo (finds the remainder when one integer is divided by another)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Bitwise */\n",
    "\n",
    "& --And\n",
    "| --Or\n",
    "^ --Exclusive or"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Comparison */\n",
    "\n",
    "= --Equal to\n",
    "> --Greater than\n",
    "< --Less than\n",
    ">= --Greater than or equal to\n",
    "<= --Less than or equal to\n",
    "<> --Not equal to"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Compound */\n",
    "\n",
    "+= --Add equals\n",
    "-= --Subtract equals\n",
    "*= --Multiply equals\n",
    "/= --Divide equals\n",
    "%= --Modulo equals\n",
    "&= --Bitwise and equals\n",
    "^-= --Bitwise exclusive equals\n",
    "!*= --Bitwise or equals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Logical */\n",
    "\n",
    "ALL\n",
    "AND\n",
    "ANY\n",
    "BETWEEN\n",
    "EXISTS\n",
    "IN\n",
    "LIKE\n",
    "NOT\n",
    "OR\n",
    "SOME"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SQL Database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "CREATE DATABASE database_name;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Drop Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "DROP DATABASE database_name;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Backup Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Create a full backup of a database */\n",
    "\n",
    "BACKUP DATABASE database_name\n",
    "TO DISK = 'filepath\\filename.bak';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Create a new blank table */\n",
    "\n",
    "CREATE TABLE table_name(\n",
    "    column1 datatype  /*varchar(255), integer, date, etc.*/\n",
    "    column2 datatype\n",
    "    column3 datatype\n",
    ");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Create a copy of an existing table */\n",
    "\n",
    "CREATE TABLE new_table_name AS\n",
    "SELECT * \n",
    "FROM old_table_name\n",
    "WHERE condition;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Drop Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Drop and existing table in a database */\n",
    "\n",
    "DROP TABLE table_name;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Delete the data within a table, but not the table itself */\n",
    "\n",
    "TRUNCATE TABLE table_name;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Alter Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Add, delete, or modify columns in an existing table */\n",
    "\n",
    "/* Add column */\n",
    "ALTER TABLE table_name\n",
    "ADD column1 varchar(255);\n",
    "\n",
    "/* Drop column */\n",
    "ALTER TABLE table_name\n",
    "DROP COLUMN column1;\n",
    "\n",
    "/* Rename column */\n",
    "ALTER TABLE table_name\n",
    "RENAME COLUMN column1;\n",
    "\n",
    "/* Change datatype */\n",
    "ALTER TABLE table_name\n",
    "ALTER COLUMN column1 new_datatype;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create Constraint"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* Types of constraints */\n",
    "NOT NULL - Ensures that a column cannot have a NULL value\n",
    "UNIQUE - Ensures that all values in a column are different\n",
    "PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table\n",
    "FOREIGN KEY - Prevents actions that would destroy links between tables; a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.\n",
    "CHECK - Ensures that the values in a column satisfies a specific condition\n",
    "DEFAULT - Sets a default value for a column if no value is specified\n",
    "CREATE INDEX - Used to create and retrieve data from the database very quickly\n",
    "\n",
    "\n",
    "/* NOT NULL */\n",
    "\n",
    "CREATE TABLE table_name (\n",
    "    column1 datatype constraint\n",
    "    column2 datatype NOT NULL\n",
    ");\n",
    "\n",
    "ALTER TABLE table_name\n",
    "ALTER COLUMN column1 datatype NOT NULL;\n",
    "\n",
    "\n",
    "/* UNIQUE */\n",
    "\n",
    "ALTER TABLE table_name\n",
    "ADD UNIQUE (column1);\n",
    "\n",
    "\n",
    "/* PRIMARY KEY */\n",
    "\n",
    "CREATE TABLE table_name (\n",
    "    column1 datatype\n",
    "    column2 datatype\n",
    "    PRIMARY KEY (ID_column)\n",
    ");\n",
    "\n",
    "ALTER TABLE table_name\n",
    "ADD PRIMARY KEY (ID);\n",
    "\n",
    "ALTER TABLE table_name\n",
    "DROP PRIMARY KEY (ID);\n",
    "\n",
    "\n",
    "/* FOREIGN KEY */\n",
    "\n",
    "CREATE TABLE table_name (\n",
    "    column1 datatype\n",
    "    column2 datatype\n",
    "    FOREIGN KEY (ID_column)\n",
    ");\n",
    "\n",
    "ALTER TABLE table_name\n",
    "ADD FOREIGN KEY (ID) REFERENCES primary_table(primary_key);\n",
    "\n",
    "\n",
    "/* CHECK */\n",
    "\n",
    "\n",
    "/* DEFAULT */\n",
    "CREATE TABLE table_name (\n",
    "    column1 datatype\n",
    "    column2 datatype\n",
    "    column3 datatype DEFAULT 'default string or value'\n",
    ");\n",
    "\n",
    "ALTER TABLE table_name\n",
    "ALTER column1 SET DEFAULT 'default string or number';\n",
    "\n",
    "\n",
    "/* INDEX */\n",
    "CREATE INDEX index_name\n",
    "ON table_name (column1);\n",
    "\n",
    "DROP INDEX table_name.index_name;\n",
    "\n",
    "\n",
    "/* Increment */\n",
    "CREATE TABLE table_name (\n",
    "    column1 datatype IDENTITY(1,1) PRIMARY KEY\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Views"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "/* A virtual table or object used to view or or change data from a table, without changing the underlying table; a stored query; does not store data itself, so saves space */\n",
    "\n",
    "CREATE VIEW view_name AS\n",
    "SELECT column1, column2, column3\n",
    "FROM table_name\n",
    "WHERE condition;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "DATE - format YYYY-MM-DD\n",
    "DATETIME - format: YYYY-MM-DD HH:MI:SS\n",
    "SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS\n",
    "TIMESTAMP - format: a unique number"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "aee8b7b246df8f9039afb4144a1f6fd8d2ca17a180786b69acc140d282b71a49"
  },
  "kernelspec": {
   "display_name": "Python 3.9.3 64-bit",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.3"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}