select연습문제.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](pic/mysql2.jpg) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\PC-14\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\pymysql\\cursors.py:170: Warning: (1366, \"Incorrect string value: '\\\\xB4\\\\xEB\\\\xC7\\\\xD1\\\\xB9\\\\xCE...' for column 'VARIABLE_VALUE' at row 518\")\n",
      "  result = self._query(query)\n",
      "C:\\Users\\PC-14\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\pymysql\\cursors.py:170: Warning: (3719, \"'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.\")\n",
      "  result = self._query(query)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Connected: root@galaxy'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pymysql\n",
    "pymysql.install_as_MySQLdb()\n",
    "%load_ext sql\n",
    "%sql mysql://root:hannam@localhost/galaxy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## <span style=\"color:red\">** 연습문제 **</span>  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "19 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>employee_id</th>\n",
       "        <th>first_name</th>\n",
       "        <th>last_name</th>\n",
       "        <th>email</th>\n",
       "        <th>phone_number</th>\n",
       "        <th>hire_date</th>\n",
       "        <th>job_id</th>\n",
       "        <th>salary</th>\n",
       "        <th>commission_pct</th>\n",
       "        <th>manager_id</th>\n",
       "        <th>department_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>100</td>\n",
       "        <td>Steven</td>\n",
       "        <td>King</td>\n",
       "        <td>SKING</td>\n",
       "        <td>515.123.4567</td>\n",
       "        <td>1987-06-17</td>\n",
       "        <td>AD_PRES</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>101</td>\n",
       "        <td>Neena</td>\n",
       "        <td>Kochhar</td>\n",
       "        <td>NKOCHHAR</td>\n",
       "        <td>515.123.4568</td>\n",
       "        <td>1989-09-21</td>\n",
       "        <td>AD_VP</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>None</td>\n",
       "        <td>100</td>\n",
       "        <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>102</td>\n",
       "        <td>Lex</td>\n",
       "        <td>De Haan</td>\n",
       "        <td>LDEHAAN</td>\n",
       "        <td>515.123.4569</td>\n",
       "        <td>1993-01-13</td>\n",
       "        <td>AD_VP</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>None</td>\n",
       "        <td>100</td>\n",
       "        <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>103</td>\n",
       "        <td>Alexander</td>\n",
       "        <td>Hunold</td>\n",
       "        <td>AHUNOLD</td>\n",
       "        <td>590.423.4567</td>\n",
       "        <td>1990-01-03</td>\n",
       "        <td>IT_PROG</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>None</td>\n",
       "        <td>102</td>\n",
       "        <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>104</td>\n",
       "        <td>Bruce</td>\n",
       "        <td>Ernst</td>\n",
       "        <td>BERNST</td>\n",
       "        <td>590.423.4568</td>\n",
       "        <td>1991-05-21</td>\n",
       "        <td>IT_PROG</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>None</td>\n",
       "        <td>103</td>\n",
       "        <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>107</td>\n",
       "        <td>Diana</td>\n",
       "        <td>Lorentz</td>\n",
       "        <td>DLORENTZ</td>\n",
       "        <td>590.423.5567</td>\n",
       "        <td>1999-02-07</td>\n",
       "        <td>IT_PROG</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>None</td>\n",
       "        <td>103</td>\n",
       "        <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>124</td>\n",
       "        <td>Kevin</td>\n",
       "        <td>Mourgos</td>\n",
       "        <td>KMOURGOS</td>\n",
       "        <td>650.123.5234</td>\n",
       "        <td>1999-11-16</td>\n",
       "        <td>ST_MAN</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>None</td>\n",
       "        <td>100</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>141</td>\n",
       "        <td>Trenna</td>\n",
       "        <td>Rajs</td>\n",
       "        <td>TRAJS</td>\n",
       "        <td>650.121.8009</td>\n",
       "        <td>1995-10-17</td>\n",
       "        <td>ST_CLERK</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>None</td>\n",
       "        <td>124</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>142</td>\n",
       "        <td>Curtis</td>\n",
       "        <td>Davies</td>\n",
       "        <td>CDAVIES</td>\n",
       "        <td>650.121.2994</td>\n",
       "        <td>1997-01-29</td>\n",
       "        <td>ST_CLERK</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>None</td>\n",
       "        <td>124</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>143</td>\n",
       "        <td>Randall</td>\n",
       "        <td>Matos</td>\n",
       "        <td>RMATOS</td>\n",
       "        <td>650.121.2874</td>\n",
       "        <td>1998-03-15</td>\n",
       "        <td>ST_CLERK</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>None</td>\n",
       "        <td>124</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>144</td>\n",
       "        <td>Peter</td>\n",
       "        <td>Vargas</td>\n",
       "        <td>PVARGAS</td>\n",
       "        <td>650.121.2004</td>\n",
       "        <td>1998-07-09</td>\n",
       "        <td>ST_CLERK</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>None</td>\n",
       "        <td>124</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>149</td>\n",
       "        <td>Eleni</td>\n",
       "        <td>Zlotkey</td>\n",
       "        <td>EZLOTKEY</td>\n",
       "        <td>011.44.1344.429018</td>\n",
       "        <td>2000-01-29</td>\n",
       "        <td>SA_MAN</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>0.20</td>\n",
       "        <td>100</td>\n",
       "        <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>176</td>\n",
       "        <td>Jonathon</td>\n",
       "        <td>Taylor</td>\n",
       "        <td>JTAYLOR</td>\n",
       "        <td>011.44.1644.429265</td>\n",
       "        <td>1998-03-24</td>\n",
       "        <td>SA_REP</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>0.20</td>\n",
       "        <td>149</td>\n",
       "        <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>178</td>\n",
       "        <td>Kimberely</td>\n",
       "        <td>Grant</td>\n",
       "        <td>KGRANT</td>\n",
       "        <td>011.44.1644.429263</td>\n",
       "        <td>1999-05-24</td>\n",
       "        <td>SA_REP</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>0.15</td>\n",
       "        <td>149</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>200</td>\n",
       "        <td>Jennifer</td>\n",
       "        <td>Whalen</td>\n",
       "        <td>JWHALEN</td>\n",
       "        <td>515.123.4444</td>\n",
       "        <td>1987-09-17</td>\n",
       "        <td>AD_ASST</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>None</td>\n",
       "        <td>101</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>201</td>\n",
       "        <td>Michael</td>\n",
       "        <td>Hartstein</td>\n",
       "        <td>MHARTSTE</td>\n",
       "        <td>515.123.5555</td>\n",
       "        <td>1996-02-17</td>\n",
       "        <td>MK_MAN</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>None</td>\n",
       "        <td>100</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>202</td>\n",
       "        <td>Pat</td>\n",
       "        <td>Fay</td>\n",
       "        <td>PFAY</td>\n",
       "        <td>603.123.6666</td>\n",
       "        <td>1997-08-17</td>\n",
       "        <td>MK_REP</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>None</td>\n",
       "        <td>201</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>205</td>\n",
       "        <td>Shelley</td>\n",
       "        <td>Higgins</td>\n",
       "        <td>SHIGGINS</td>\n",
       "        <td>515.123.8080</td>\n",
       "        <td>1994-06-07</td>\n",
       "        <td>AC_MGR</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>None</td>\n",
       "        <td>101</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>206</td>\n",
       "        <td>William</td>\n",
       "        <td>Gietz</td>\n",
       "        <td>WGIETZ</td>\n",
       "        <td>515.123.8181</td>\n",
       "        <td>1994-06-07</td>\n",
       "        <td>AC_ACCOUNT</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>None</td>\n",
       "        <td>205</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(100, 'Steven', 'King', 'SKING', '515.123.4567', datetime.date(1987, 6, 17), 'AD_PRES', Decimal('100000.00'), None, None, 90),\n",
       " (101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', datetime.date(1989, 9, 21), 'AD_VP', Decimal('100000.00'), None, 100, 90),\n",
       " (102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', datetime.date(1993, 1, 13), 'AD_VP', Decimal('100000.00'), None, 100, 90),\n",
       " (103, 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', datetime.date(1990, 1, 3), 'IT_PROG', Decimal('100000.00'), None, 102, 90),\n",
       " (104, 'Bruce', 'Ernst', 'BERNST', '590.423.4568', datetime.date(1991, 5, 21), 'IT_PROG', Decimal('100000.00'), None, 103, 90),\n",
       " (107, 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567', datetime.date(1999, 2, 7), 'IT_PROG', Decimal('100000.00'), None, 103, 90),\n",
       " (124, 'Kevin', 'Mourgos', 'KMOURGOS', '650.123.5234', datetime.date(1999, 11, 16), 'ST_MAN', Decimal('100000.00'), None, 100, 50),\n",
       " (141, 'Trenna', 'Rajs', 'TRAJS', '650.121.8009', datetime.date(1995, 10, 17), 'ST_CLERK', Decimal('100000.00'), None, 124, 50),\n",
       " (142, 'Curtis', 'Davies', 'CDAVIES', '650.121.2994', datetime.date(1997, 1, 29), 'ST_CLERK', Decimal('100000.00'), None, 124, 50),\n",
       " (143, 'Randall', 'Matos', 'RMATOS', '650.121.2874', datetime.date(1998, 3, 15), 'ST_CLERK', Decimal('100000.00'), None, 124, 50),\n",
       " (144, 'Peter', 'Vargas', 'PVARGAS', '650.121.2004', datetime.date(1998, 7, 9), 'ST_CLERK', Decimal('100000.00'), None, 124, 50),\n",
       " (149, 'Eleni', 'Zlotkey', 'EZLOTKEY', '011.44.1344.429018', datetime.date(2000, 1, 29), 'SA_MAN', Decimal('100000.00'), Decimal('0.20'), 100, 80),\n",
       " (176, 'Jonathon', 'Taylor', 'JTAYLOR', '011.44.1644.429265', datetime.date(1998, 3, 24), 'SA_REP', Decimal('100000.00'), Decimal('0.20'), 149, 80),\n",
       " (178, 'Kimberely', 'Grant', 'KGRANT', '011.44.1644.429263', datetime.date(1999, 5, 24), 'SA_REP', Decimal('100000.00'), Decimal('0.15'), 149, None),\n",
       " (200, 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', datetime.date(1987, 9, 17), 'AD_ASST', Decimal('100000.00'), None, 101, 10),\n",
       " (201, 'Michael', 'Hartstein', 'MHARTSTE', '515.123.5555', datetime.date(1996, 2, 17), 'MK_MAN', Decimal('100000.00'), None, 100, 20),\n",
       " (202, 'Pat', 'Fay', 'PFAY', '603.123.6666', datetime.date(1997, 8, 17), 'MK_REP', Decimal('100000.00'), None, 201, 20),\n",
       " (205, 'Shelley', 'Higgins', 'SHIGGINS', '515.123.8080', datetime.date(1994, 6, 7), 'AC_MGR', Decimal('100000.00'), None, 101, 50),\n",
       " (206, 'William', 'Gietz', 'WGIETZ', '515.123.8181', datetime.date(1994, 6, 7), 'AC_ACCOUNT', Decimal('100000.00'), None, 205, 50)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select * from employees;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br><br>\n",
    "<span style=\"color:red\">[연습 10]</span>   \n",
    "사원번호가 176번인 종업원의 사원이름과 사원번호를 나타내는 질의어를 작성하라."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>first_name</th>\n",
       "        <th>employee_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jonathon</td>\n",
       "        <td>176</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Jonathon', 176)]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select first_name, employee_id\n",
    "from employees\n",
    "where employee_id = 176;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br><br>\n",
    "<span style=\"color:red\">[연습 11]</span>   \n",
    "급여가 5000에서 12000사이에 포함되지 않는 종업원의 사원이름과 급여를 나타내는 질의어를 작성하라."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "19 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>first_name</th>\n",
       "        <th>salary</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Steven</td>\n",
       "        <td>100000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Neena</td>\n",
       "        <td>100000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lex</td>\n",
       "        <td>100000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Alexander</td>\n",
       "        <td>100000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bruce</td>\n",
       "        <td>100000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Diana</td>\n",
       "        <td>100000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kevin</td>\n",
       "        <td>100000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Trenna</td>\n",
       "        <td>100000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Curtis</td>\n",
       "        <td>100000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Randall</td>\n",
       "        <td>100000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Peter</td>\n",
       "        <td>100000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Eleni</td>\n",
       "        <td>100000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jonathon</td>\n",
       "        <td>100000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kimberely</td>\n",
       "        <td>100000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jennifer</td>\n",
       "        <td>100000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Michael</td>\n",
       "        <td>100000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Pat</td>\n",
       "        <td>100000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Shelley</td>\n",
       "        <td>100000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>William</td>\n",
       "        <td>100000.00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Steven', Decimal('100000.00')),\n",
       " ('Neena', Decimal('100000.00')),\n",
       " ('Lex', Decimal('100000.00')),\n",
       " ('Alexander', Decimal('100000.00')),\n",
       " ('Bruce', Decimal('100000.00')),\n",
       " ('Diana', Decimal('100000.00')),\n",
       " ('Kevin', Decimal('100000.00')),\n",
       " ('Trenna', Decimal('100000.00')),\n",
       " ('Curtis', Decimal('100000.00')),\n",
       " ('Randall', Decimal('100000.00')),\n",
       " ('Peter', Decimal('100000.00')),\n",
       " ('Eleni', Decimal('100000.00')),\n",
       " ('Jonathon', Decimal('100000.00')),\n",
       " ('Kimberely', Decimal('100000.00')),\n",
       " ('Jennifer', Decimal('100000.00')),\n",
       " ('Michael', Decimal('100000.00')),\n",
       " ('Pat', Decimal('100000.00')),\n",
       " ('Shelley', Decimal('100000.00')),\n",
       " ('William', Decimal('100000.00'))]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select first_name, salary\n",
    "from employees\n",
    "where salary not between 5000 and 12000;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br><br>\n",
    "<span style=\"color:red\">[연습 12]</span>   \n",
    "2011년 2월 20일과 1998년 5월 1일 사이에 입사한 사원이름과 업무 ID 및 입사일을 나타내되 입사일을 기준으로 오름차순으로 정렬하라."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>first_name</th>\n",
       "        <th>manager_id</th>\n",
       "        <th>hire_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Peter</td>\n",
       "        <td>124</td>\n",
       "        <td>1998-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Diana</td>\n",
       "        <td>103</td>\n",
       "        <td>1999-02-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kimberely</td>\n",
       "        <td>149</td>\n",
       "        <td>1999-05-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kevin</td>\n",
       "        <td>100</td>\n",
       "        <td>1999-11-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Eleni</td>\n",
       "        <td>100</td>\n",
       "        <td>2000-01-29</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Peter', 124, datetime.date(1998, 7, 9)),\n",
       " ('Diana', 103, datetime.date(1999, 2, 7)),\n",
       " ('Kimberely', 149, datetime.date(1999, 5, 24)),\n",
       " ('Kevin', 100, datetime.date(1999, 11, 16)),\n",
       " ('Eleni', 100, datetime.date(2000, 1, 29))]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select first_name, manager_id, hire_date\n",
    "from employees\n",
    "where hire_date between '1998-5-1' and '2011-2-20'\n",
    "order by hire_date asc;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br><br>\n",
    "<span style=\"color:red\">[연습 13]</span>   \n",
    "부서 20 또는 부서 50에 소속된 사원이름과 부서이름을 나타내되 사원이름을 기준으로 알파벳순으로 정렬하라."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "9 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>first_name</th>\n",
       "        <th>department_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Curtis</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kevin</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Michael</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Pat</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Peter</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Randall</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Shelley</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Trenna</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>William</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Curtis', 50),\n",
       " ('Kevin', 50),\n",
       " ('Michael', 20),\n",
       " ('Pat', 20),\n",
       " ('Peter', 50),\n",
       " ('Randall', 50),\n",
       " ('Shelley', 50),\n",
       " ('Trenna', 50),\n",
       " ('William', 50)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select first_name, department_id\n",
    "from employees\n",
    "where department_id = 20 or department_id = 50\n",
    "order by first_name asc;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br><br>\n",
    "<span style=\"color:red\">[연습 14]</span>   \n",
    "급여가 5000 에서 12000 사이 이고, 부서 20 또는 부서 50에 소속된 사원의 이름과 급여를 나타내라."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "7 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>first_name</th>\n",
       "        <th>salary</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kevin</td>\n",
       "        <td>100000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Trenna</td>\n",
       "        <td>100000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Curtis</td>\n",
       "        <td>100000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Randall</td>\n",
       "        <td>100000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Peter</td>\n",
       "        <td>100000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Shelley</td>\n",
       "        <td>100000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>William</td>\n",
       "        <td>100000.00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Kevin', Decimal('100000.00')),\n",
       " ('Trenna', Decimal('100000.00')),\n",
       " ('Curtis', Decimal('100000.00')),\n",
       " ('Randall', Decimal('100000.00')),\n",
       " ('Peter', Decimal('100000.00')),\n",
       " ('Shelley', Decimal('100000.00')),\n",
       " ('William', Decimal('100000.00'))]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select first_name, salary\n",
    "from employees\n",
    "where salary between 5000 and 12000\n",
    "and department_id = 20 or department_id = 50;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br><br>\n",
    "<span style=\"color:red\">[연습 15]</span>   \n",
    "2000년에 입사한 사원의 이름과 입사일을 나타내라."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>first_name</th>\n",
       "        <th>hire_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Eleni</td>\n",
       "        <td>2000-01-29</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Eleni', datetime.date(2000, 1, 29))]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select first_name, hire_date\n",
    "from employees\n",
    "where hire_date like '2000%'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br><br>\n",
    "<span style=\"color:red\">[연습 16]</span>   \n",
    "관리자가 없는 모든 사원의 이름과 업무를 표시하라."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>first_name</th>\n",
       "        <th>department_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Steven</td>\n",
       "        <td>90</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Steven', 90)]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select first_name, department_id\n",
    "from employees\n",
    "where manager_id is null;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br><br>\n",
    "<span style=\"color:red\">[연습 17]</span>   \n",
    "커미션을 받는 모든 사원의 이름, 급여 및 커미션을 급여 및 커미션을 기준으로 내림차순으로 정렬하라."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>first_name</th>\n",
       "        <th>salary</th>\n",
       "        <th>commission_pct</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Eleni</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>0.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jonathon</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>0.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kimberely</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>0.15</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Eleni', Decimal('100000.00'), Decimal('0.20')),\n",
       " ('Jonathon', Decimal('100000.00'), Decimal('0.20')),\n",
       " ('Kimberely', Decimal('100000.00'), Decimal('0.15'))]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select first_name, salary, commission_pct\n",
    "from employees\n",
    "where not commission_pct is null\n",
    "order by commission_pct desc;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br><br>\n",
    "<span style=\"color:red\">[연습 18]</span>   \n",
    "이름의 세번째 문자가 a인  모든 사원의 이름을 표시하라."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>first_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Diana</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Diana',)]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select first_name\n",
    "from employees\n",
    "where first_name like '__a%'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br><br>\n",
    "<span style=\"color:red\">[연습 19]</span>   \n",
    "이름에 a가 있는 모든 사원의 이름을 표시하라."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "9 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>first_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Pat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>William</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Michael</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Alexander</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Neena</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Diana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Randall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Trenna</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jonathon</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Pat',),\n",
       " ('William',),\n",
       " ('Michael',),\n",
       " ('Alexander',),\n",
       " ('Neena',),\n",
       " ('Diana',),\n",
       " ('Randall',),\n",
       " ('Trenna',),\n",
       " ('Jonathon',)]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select first_name\n",
    "from employees\n",
    "where first_name like '%a%'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br><br>\n",
    "<span style=\"color:red\">[연습 20]</span>   \n",
    "이름에 a가 먼저 나오고 e가 나중에 나오는 모든 사원의 이름을 표시하라."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>first_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Alexander</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Alexander',)]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select first_name\n",
    "from employees\n",
    "where first_name like 'a%e%'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br><br>\n",
    "<span style=\"color:red\">[연습 21]</span>   \n",
    "이름에 a와 e가 모두 나오는 모든 사원의 이름을 표시하라."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>first_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Michael</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Alexander</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Michael',), ('Alexander',)]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select first_name\n",
    "from employees\n",
    "where first_name like '%a%e%'"
   ]
  }
 ],
 "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}