PostgreSQLMagic.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%reload_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: postgres@postgres'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "postgresql://postgres:K%40trina26@localhost/postgres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: postgres@mydb'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "postgresql://postgres:K%40trina26@localhost/mydb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/mydb\n",
      "   postgresql://postgres:***@localhost/postgres\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE TABLE weather(\n",
    "    city varchar(80),\n",
    "    temp_lo int,  --low temperature\n",
    "    temp_hi int, --high temperature\n",
    "    prcp real, --precipitation\n",
    "    date date\n",
    ");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/mydb\n",
      "   postgresql://postgres:***@localhost/postgres\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE TABLE cities(\n",
    "    name varchar(80),\n",
    "    location point\n",
    ");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/mydb\n",
      "   postgresql://postgres:***@localhost/postgres\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Schema</th>\n",
       "        <th>Name</th>\n",
       "        <th>Type</th>\n",
       "        <th>Owner</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>cities</td>\n",
       "        <td>table</td>\n",
       "        <td>postgres</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>weather</td>\n",
       "        <td>table</td>\n",
       "        <td>postgres</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('public', 'cities', 'table', 'postgres'),\n",
       " ('public', 'weather', 'table', 'postgres')]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\\dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/mydb\n",
      "   postgresql://postgres:***@localhost/postgres\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Schema</th>\n",
       "        <th>Name</th>\n",
       "        <th>Type</th>\n",
       "        <th>Owner</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>cities</td>\n",
       "        <td>table</td>\n",
       "        <td>postgres</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>weather</td>\n",
       "        <td>table</td>\n",
       "        <td>postgres</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('public', 'cities', 'table', 'postgres'),\n",
       " ('public', 'weather', 'table', 'postgres')]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\\d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/mydb\n",
      "   postgresql://postgres:***@localhost/postgres\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "INSERT INTO weather VALUES(\n",
    "    'San Francisco', 46, 50, 0.25, '1994-11-27'\n",
    ");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/mydb\n",
      "   postgresql://postgres:***@localhost/postgres\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>city</th>\n",
       "        <th>temp_lo</th>\n",
       "        <th>temp_hi</th>\n",
       "        <th>prcp</th>\n",
       "        <th>date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco</td>\n",
       "        <td>46</td>\n",
       "        <td>50</td>\n",
       "        <td>0.25</td>\n",
       "        <td>1994-11-27</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('San Francisco', 46, 50, 0.25, datetime.date(1994, 11, 27))]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM weather;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/mydb\n",
      "   postgresql://postgres:***@localhost/postgres\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "INSERT  INTO cities VALUES(\n",
    "    'San Francisco', '(-194.0, 53.0)'\n",
    ");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/mydb\n",
      "   postgresql://postgres:***@localhost/postgres\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>location</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco</td>\n",
       "        <td>(-194,53)</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('San Francisco', '(-194,53)')]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM cities;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/mydb\n",
      "   postgresql://postgres:***@localhost/postgres\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "INSERT INTO weather(city, temp_lo, temp_hi, prcp, date)\n",
    "    VALUES('San Francisco', 43, 57, 0.0, '1994-11-29')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/mydb\n",
      "   postgresql://postgres:***@localhost/postgres\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>city</th>\n",
       "        <th>temp_lo</th>\n",
       "        <th>temp_hi</th>\n",
       "        <th>prcp</th>\n",
       "        <th>date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco</td>\n",
       "        <td>46</td>\n",
       "        <td>50</td>\n",
       "        <td>0.25</td>\n",
       "        <td>1994-11-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco</td>\n",
       "        <td>43</td>\n",
       "        <td>57</td>\n",
       "        <td>0.0</td>\n",
       "        <td>1994-11-29</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('San Francisco', 46, 50, 0.25, datetime.date(1994, 11, 27)),\n",
       " ('San Francisco', 43, 57, 0.0, datetime.date(1994, 11, 29))]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM weather;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/mydb\n",
      "   postgresql://postgres:***@localhost/postgres\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "INSERT INTO weather(date, city, temp_hi, temp_lo)\n",
    "    VALUES('1994-11-29', 'Hayward', 54, 37);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/mydb\n",
      "   postgresql://postgres:***@localhost/postgres\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>city</th>\n",
       "        <th>temp_lo</th>\n",
       "        <th>temp_hi</th>\n",
       "        <th>prcp</th>\n",
       "        <th>date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco</td>\n",
       "        <td>46</td>\n",
       "        <td>50</td>\n",
       "        <td>0.25</td>\n",
       "        <td>1994-11-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco</td>\n",
       "        <td>43</td>\n",
       "        <td>57</td>\n",
       "        <td>0.0</td>\n",
       "        <td>1994-11-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hayward</td>\n",
       "        <td>37</td>\n",
       "        <td>54</td>\n",
       "        <td>None</td>\n",
       "        <td>1994-11-29</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('San Francisco', 46, 50, 0.25, datetime.date(1994, 11, 27)),\n",
       " ('San Francisco', 43, 57, 0.0, datetime.date(1994, 11, 29)),\n",
       " ('Hayward', 37, 54, None, datetime.date(1994, 11, 29))]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM weather;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/mydb\n",
      "   postgresql://postgres:***@localhost/postgres\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>city</th>\n",
       "        <th>temp_avg</th>\n",
       "        <th>date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco</td>\n",
       "        <td>48</td>\n",
       "        <td>1994-11-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco</td>\n",
       "        <td>50</td>\n",
       "        <td>1994-11-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hayward</td>\n",
       "        <td>45</td>\n",
       "        <td>1994-11-29</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('San Francisco', 48, datetime.date(1994, 11, 27)),\n",
       " ('San Francisco', 50, datetime.date(1994, 11, 29)),\n",
       " ('Hayward', 45, datetime.date(1994, 11, 29))]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT city, (temp_hi + temp_lo)/2 AS temp_avg, date FROM weather;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/mydb\n",
      "   postgresql://postgres:***@localhost/postgres\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>city</th>\n",
       "        <th>temp_lo</th>\n",
       "        <th>temp_hi</th>\n",
       "        <th>prcp</th>\n",
       "        <th>date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco</td>\n",
       "        <td>46</td>\n",
       "        <td>50</td>\n",
       "        <td>0.25</td>\n",
       "        <td>1994-11-27</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('San Francisco', 46, 50, 0.25, datetime.date(1994, 11, 27))]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM weather\n",
    "WHERE city = 'San Francisco' AND prcp > 0.0;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/mydb\n",
      "   postgresql://postgres:***@localhost/postgres\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>city</th>\n",
       "        <th>temp_lo</th>\n",
       "        <th>temp_hi</th>\n",
       "        <th>prcp</th>\n",
       "        <th>date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hayward</td>\n",
       "        <td>37</td>\n",
       "        <td>54</td>\n",
       "        <td>None</td>\n",
       "        <td>1994-11-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco</td>\n",
       "        <td>46</td>\n",
       "        <td>50</td>\n",
       "        <td>0.25</td>\n",
       "        <td>1994-11-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco</td>\n",
       "        <td>43</td>\n",
       "        <td>57</td>\n",
       "        <td>0.0</td>\n",
       "        <td>1994-11-29</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Hayward', 37, 54, None, datetime.date(1994, 11, 29)),\n",
       " ('San Francisco', 46, 50, 0.25, datetime.date(1994, 11, 27)),\n",
       " ('San Francisco', 43, 57, 0.0, datetime.date(1994, 11, 29))]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM weather\n",
    "ORDER BY city;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/mydb\n",
      "   postgresql://postgres:***@localhost/postgres\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>city</th>\n",
       "        <th>temp_lo</th>\n",
       "        <th>temp_hi</th>\n",
       "        <th>prcp</th>\n",
       "        <th>date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hayward</td>\n",
       "        <td>37</td>\n",
       "        <td>54</td>\n",
       "        <td>None</td>\n",
       "        <td>1994-11-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco</td>\n",
       "        <td>43</td>\n",
       "        <td>57</td>\n",
       "        <td>0.0</td>\n",
       "        <td>1994-11-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco</td>\n",
       "        <td>46</td>\n",
       "        <td>50</td>\n",
       "        <td>0.25</td>\n",
       "        <td>1994-11-27</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Hayward', 37, 54, None, datetime.date(1994, 11, 29)),\n",
       " ('San Francisco', 43, 57, 0.0, datetime.date(1994, 11, 29)),\n",
       " ('San Francisco', 46, 50, 0.25, datetime.date(1994, 11, 27))]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM weather\n",
    "ORDER BY city, temp_lo;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/mydb\n",
      "   postgresql://postgres:***@localhost/postgres\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>city</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hayward</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Hayward',), ('San Francisco',)]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT city\n",
    "FROM weather;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/mydb\n",
      "   postgresql://postgres:***@localhost/postgres\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>city</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hayward</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Hayward',), ('San Francisco',)]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT city\n",
    "FROM weather\n",
    "ORDER BY city;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/mydb\n",
      "   postgresql://postgres:***@localhost/postgres\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>city</th>\n",
       "        <th>temp_lo</th>\n",
       "        <th>temp_hi</th>\n",
       "        <th>prcp</th>\n",
       "        <th>date</th>\n",
       "        <th>name</th>\n",
       "        <th>location</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco</td>\n",
       "        <td>46</td>\n",
       "        <td>50</td>\n",
       "        <td>0.25</td>\n",
       "        <td>1994-11-27</td>\n",
       "        <td>San Francisco</td>\n",
       "        <td>(-194,53)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco</td>\n",
       "        <td>43</td>\n",
       "        <td>57</td>\n",
       "        <td>0.0</td>\n",
       "        <td>1994-11-29</td>\n",
       "        <td>San Francisco</td>\n",
       "        <td>(-194,53)</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('San Francisco', 46, 50, 0.25, datetime.date(1994, 11, 27), 'San Francisco', '(-194,53)'),\n",
       " ('San Francisco', 43, 57, 0.0, datetime.date(1994, 11, 29), 'San Francisco', '(-194,53)')]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT *FROM weather, cities\n",
    "WHERE city = name;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/mydb\n",
      "   postgresql://postgres:***@localhost/postgres\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>city</th>\n",
       "        <th>temp_lo</th>\n",
       "        <th>temp_hi</th>\n",
       "        <th>prcp</th>\n",
       "        <th>date</th>\n",
       "        <th>location</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco</td>\n",
       "        <td>46</td>\n",
       "        <td>50</td>\n",
       "        <td>0.25</td>\n",
       "        <td>1994-11-27</td>\n",
       "        <td>(-194,53)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco</td>\n",
       "        <td>43</td>\n",
       "        <td>57</td>\n",
       "        <td>0.0</td>\n",
       "        <td>1994-11-29</td>\n",
       "        <td>(-194,53)</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('San Francisco', 46, 50, 0.25, datetime.date(1994, 11, 27), '(-194,53)'),\n",
       " ('San Francisco', 43, 57, 0.0, datetime.date(1994, 11, 29), '(-194,53)')]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT city, temp_lo, temp_hi, prcp, date, location\n",
    "FROM weather, cities\n",
    "WHERE city = name;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/mydb\n",
      "   postgresql://postgres:***@localhost/postgres\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>city</th>\n",
       "        <th>temp_lo</th>\n",
       "        <th>temp_hi</th>\n",
       "        <th>prcp</th>\n",
       "        <th>date</th>\n",
       "        <th>location</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco</td>\n",
       "        <td>46</td>\n",
       "        <td>50</td>\n",
       "        <td>0.25</td>\n",
       "        <td>1994-11-27</td>\n",
       "        <td>(-194,53)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco</td>\n",
       "        <td>43</td>\n",
       "        <td>57</td>\n",
       "        <td>0.0</td>\n",
       "        <td>1994-11-29</td>\n",
       "        <td>(-194,53)</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('San Francisco', 46, 50, 0.25, datetime.date(1994, 11, 27), '(-194,53)'),\n",
       " ('San Francisco', 43, 57, 0.0, datetime.date(1994, 11, 29), '(-194,53)')]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT weather.city, weather.temp_lo, weather.temp_hi, weather.prcp, weather.date, cities.location\n",
    "FROM weather, cities\n",
    "WHERE cities.name = weather.city;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/mydb\n",
      "   postgresql://postgres:***@localhost/postgres\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>city</th>\n",
       "        <th>temp_lo</th>\n",
       "        <th>temp_hi</th>\n",
       "        <th>prcp</th>\n",
       "        <th>date</th>\n",
       "        <th>name</th>\n",
       "        <th>location</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco</td>\n",
       "        <td>46</td>\n",
       "        <td>50</td>\n",
       "        <td>0.25</td>\n",
       "        <td>1994-11-27</td>\n",
       "        <td>San Francisco</td>\n",
       "        <td>(-194,53)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>San Francisco</td>\n",
       "        <td>43</td>\n",
       "        <td>57</td>\n",
       "        <td>0.0</td>\n",
       "        <td>1994-11-29</td>\n",
       "        <td>San Francisco</td>\n",
       "        <td>(-194,53)</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('San Francisco', 46, 50, 0.25, datetime.date(1994, 11, 27), 'San Francisco', '(-194,53)'),\n",
       " ('San Francisco', 43, 57, 0.0, datetime.date(1994, 11, 29), 'San Francisco', '(-194,53)')]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM weather \n",
    "INNER JOIN cities \n",
    "ON (weather.city = cities.name);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}