create&insert&alter%drop.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](pic/mysql2.jpg) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 복습: SELECT 문"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br><br>\n",
    "<span style=\"color:blue; font-size: 1.2em;\" >\n",
    "SELECT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;column, group_function(column)   \n",
    "FROM&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;table  \n",
    "[WHERE &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;condition]  \n",
    "[GROUP BY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;column]    \n",
    "[HAVING &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;group_condition]  \n",
    "[ORDER BY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;column] ; </span> "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br><br><br><br><br><br>\n",
    "**<span style=\"color:red\">\n",
    "다음 셀은 mysql DB와 편집기를 연결하는 명령입니다.  \n",
    "수업을 시작할 때 마다 가장 먼저 실행하여야 합니다.** "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\PC-21\\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-21\\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": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "drop table DEPT"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br><br>\n",
    "<span style=\"color:red\">[문제 1]</span>   \n",
    "다음과 같은 열을 가진 테이블 DEPT를 만들고 잘 만들어졌는지 확인하라.\n",
    "![](pic/pic9_7.jpg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "create table DEPT(\n",
    "        ID int(7),\n",
    "        NAME varchar(25),\n",
    "        PRIMARY KEY(id));"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>ID</th>\n",
       "        <th>NAME</th>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select * from dept;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "14 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>department_id</th>\n",
       "        <th>department_name</th>\n",
       "        <th>manager_id</th>\n",
       "        <th>location_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>Administration</td>\n",
       "        <td>200</td>\n",
       "        <td>1700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "        <td>Marketing</td>\n",
       "        <td>201</td>\n",
       "        <td>1800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>Shipping</td>\n",
       "        <td>124</td>\n",
       "        <td>1500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>60</td>\n",
       "        <td>IT</td>\n",
       "        <td>103</td>\n",
       "        <td>1400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>80</td>\n",
       "        <td>Sales</td>\n",
       "        <td>149</td>\n",
       "        <td>2500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>90</td>\n",
       "        <td>Executive</td>\n",
       "        <td>100</td>\n",
       "        <td>1700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>110</td>\n",
       "        <td>Accounting</td>\n",
       "        <td>205</td>\n",
       "        <td>1700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>190</td>\n",
       "        <td>Contracting</td>\n",
       "        <td>None</td>\n",
       "        <td>1700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>270</td>\n",
       "        <td>Public Relations 270</td>\n",
       "        <td>100</td>\n",
       "        <td>1700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>370</td>\n",
       "        <td>Public Relations 370</td>\n",
       "        <td>100</td>\n",
       "        <td>1700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>470</td>\n",
       "        <td>Public Relations 470</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>480</td>\n",
       "        <td>Public Relations 480</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>570</td>\n",
       "        <td>Public Relations 570</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>900</td>\n",
       "        <td>Data Science</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(10, 'Administration', 200, 1700),\n",
       " (20, 'Marketing', 201, 1800),\n",
       " (50, 'Shipping', 124, 1500),\n",
       " (60, 'IT', 103, 1400),\n",
       " (80, 'Sales', 149, 2500),\n",
       " (90, 'Executive', 100, 1700),\n",
       " (110, 'Accounting', 205, 1700),\n",
       " (190, 'Contracting', None, 1700),\n",
       " (270, 'Public Relations 270', 100, 1700),\n",
       " (370, 'Public Relations 370', 100, 1700),\n",
       " (470, 'Public Relations 470', None, None),\n",
       " (480, 'Public Relations 480', None, None),\n",
       " (570, 'Public Relations 570', None, None),\n",
       " (900, 'Data Science', None, None)]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select * from departments;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br><br>\n",
    "<span style=\"color:red\">[문제 2]</span>   \n",
    "departments 테이블의 데이터를 DEPT 테이블에 추가하라. 필요한 열의 데이터만 추가하라. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "14 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "insert into dept(id,name) select department_id, department_name from departments;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "14 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>ID</th>\n",
       "        <th>NAME</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>Administration</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "        <td>Marketing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>Shipping</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>60</td>\n",
       "        <td>IT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>80</td>\n",
       "        <td>Sales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>90</td>\n",
       "        <td>Executive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>110</td>\n",
       "        <td>Accounting</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>190</td>\n",
       "        <td>Contracting</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>270</td>\n",
       "        <td>Public Relations 270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>370</td>\n",
       "        <td>Public Relations 370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>470</td>\n",
       "        <td>Public Relations 470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>480</td>\n",
       "        <td>Public Relations 480</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>570</td>\n",
       "        <td>Public Relations 570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>900</td>\n",
       "        <td>Data Science</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(10, 'Administration'),\n",
       " (20, 'Marketing'),\n",
       " (50, 'Shipping'),\n",
       " (60, 'IT'),\n",
       " (80, 'Sales'),\n",
       " (90, 'Executive'),\n",
       " (110, 'Accounting'),\n",
       " (190, 'Contracting'),\n",
       " (270, 'Public Relations 270'),\n",
       " (370, 'Public Relations 370'),\n",
       " (470, 'Public Relations 470'),\n",
       " (480, 'Public Relations 480'),\n",
       " (570, 'Public Relations 570'),\n",
       " (900, 'Data Science')]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select * from dept;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br><br>\n",
    "<span style=\"color:red\">[문제 3]</span>   \n",
    "다음과 같은 열을 가진 테이블 EMP를 만들고 잘 만들어졌는지 확인하라.\n",
    "![](pic/pic9_8.jpg)\n",
    "<span style=\"color:orange\">"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "create table EMP(\n",
    "        ID int(7),\n",
    "        LAST_NAME varchar(25),\n",
    "        FIRST_NAME varchar(25),\n",
    "        DEPT_ID int(7),\n",
    "        FOREIGN KEY (DEPT_ID) REFERENCES DEPT(ID)\n",
    "        );"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Field</th>\n",
       "        <th>Type</th>\n",
       "        <th>Null</th>\n",
       "        <th>Key</th>\n",
       "        <th>Default</th>\n",
       "        <th>Extra</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ID</td>\n",
       "        <td>int(7)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LAST_NAME</td>\n",
       "        <td>varchar(25)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>FIRST_NAME</td>\n",
       "        <td>varchar(25)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>DEPT_ID</td>\n",
       "        <td>int(7)</td>\n",
       "        <td>YES</td>\n",
       "        <td>MUL</td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('ID', 'int(7)', 'YES', '', None, ''),\n",
       " ('LAST_NAME', 'varchar(25)', 'YES', '', None, ''),\n",
       " ('FIRST_NAME', 'varchar(25)', 'YES', '', None, ''),\n",
       " ('DEPT_ID', 'int(7)', 'YES', 'MUL', None, '')]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "desc emp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br><br>\n",
    "<span style=\"color:red\">[문제 4]</span>   \n",
    "긴 성을 가진 사원의 성을 표시할 수 있도록, EMP 테이블의 LAST_NAME의 크기를 varchar(50)으로 고치고 확인하라."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "ALTER TABLE EMP\n",
    "MODIFY COLUMN LAST_NAME varchar(50);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Field</th>\n",
       "        <th>Type</th>\n",
       "        <th>Null</th>\n",
       "        <th>Key</th>\n",
       "        <th>Default</th>\n",
       "        <th>Extra</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ID</td>\n",
       "        <td>int(7)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>LAST_NAME</td>\n",
       "        <td>varchar(50)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>FIRST_NAME</td>\n",
       "        <td>varchar(25)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>DEPT_ID</td>\n",
       "        <td>int(7)</td>\n",
       "        <td>YES</td>\n",
       "        <td>MUL</td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('ID', 'int(7)', 'YES', '', None, ''),\n",
       " ('LAST_NAME', 'varchar(50)', 'YES', '', None, ''),\n",
       " ('FIRST_NAME', 'varchar(25)', 'YES', '', None, ''),\n",
       " ('DEPT_ID', 'int(7)', 'YES', 'MUL', None, '')]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "desc emp;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "11 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Field</th>\n",
       "        <th>Type</th>\n",
       "        <th>Null</th>\n",
       "        <th>Key</th>\n",
       "        <th>Default</th>\n",
       "        <th>Extra</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>employee_id</td>\n",
       "        <td>int(6)</td>\n",
       "        <td>NO</td>\n",
       "        <td>PRI</td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>first_name</td>\n",
       "        <td>varchar(20)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>last_name</td>\n",
       "        <td>varchar(25)</td>\n",
       "        <td>NO</td>\n",
       "        <td>MUL</td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>email</td>\n",
       "        <td>varchar(25)</td>\n",
       "        <td>NO</td>\n",
       "        <td>UNI</td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>phone_number</td>\n",
       "        <td>varchar(20)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>hire_date</td>\n",
       "        <td>date</td>\n",
       "        <td>NO</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>job_id</td>\n",
       "        <td>varchar(10)</td>\n",
       "        <td>NO</td>\n",
       "        <td>MUL</td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>salary</td>\n",
       "        <td>decimal(8,2)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>commission_pct</td>\n",
       "        <td>decimal(2,2)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>manager_id</td>\n",
       "        <td>int(6)</td>\n",
       "        <td>YES</td>\n",
       "        <td>MUL</td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>department_id</td>\n",
       "        <td>int(4)</td>\n",
       "        <td>YES</td>\n",
       "        <td>MUL</td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('employee_id', 'int(6)', 'NO', 'PRI', None, ''),\n",
       " ('first_name', 'varchar(20)', 'YES', '', None, ''),\n",
       " ('last_name', 'varchar(25)', 'NO', 'MUL', None, ''),\n",
       " ('email', 'varchar(25)', 'NO', 'UNI', None, ''),\n",
       " ('phone_number', 'varchar(20)', 'YES', '', None, ''),\n",
       " ('hire_date', 'date', 'NO', '', None, ''),\n",
       " ('job_id', 'varchar(10)', 'NO', 'MUL', None, ''),\n",
       " ('salary', 'decimal(8,2)', 'YES', '', None, ''),\n",
       " ('commission_pct', 'decimal(2,2)', 'YES', '', None, ''),\n",
       " ('manager_id', 'int(6)', 'YES', 'MUL', None, ''),\n",
       " ('department_id', 'int(4)', 'YES', 'MUL', None, '')]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "desc employees;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br><br>\n",
    "<span style=\"color:red\">[문제 5]</span>   \n",
    "employees 테이블 구조를 기반으로 employees99 테이블을 만들라. employee_id, first_name, last_name, salary 및 department_id 열만 포함시키고, 새 테이블의 열 이름을 각각 ID, first_name, last_name, salary 및 dept_id 로 지정하라.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "19 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "create table employees99 as select employee_id, first_name, last_name, salary, department_id from employees;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>Field</th>\n",
       "        <th>Type</th>\n",
       "        <th>Null</th>\n",
       "        <th>Key</th>\n",
       "        <th>Default</th>\n",
       "        <th>Extra</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>employee_id</td>\n",
       "        <td>int(6)</td>\n",
       "        <td>NO</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>first_name</td>\n",
       "        <td>varchar(20)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>last_name</td>\n",
       "        <td>varchar(25)</td>\n",
       "        <td>NO</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>salary</td>\n",
       "        <td>decimal(8,2)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>department_id</td>\n",
       "        <td>int(4)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('employee_id', 'int(6)', 'NO', '', None, ''),\n",
       " ('first_name', 'varchar(20)', 'YES', '', None, ''),\n",
       " ('last_name', 'varchar(25)', 'NO', '', None, ''),\n",
       " ('salary', 'decimal(8,2)', 'YES', '', None, ''),\n",
       " ('department_id', 'int(4)', 'YES', '', None, '')]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "desc employees99;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>salary</th>\n",
       "        <th>department_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>100</td>\n",
       "        <td>Steven</td>\n",
       "        <td>King</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>101</td>\n",
       "        <td>Neena</td>\n",
       "        <td>Kochhar</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>102</td>\n",
       "        <td>Lex</td>\n",
       "        <td>De Haan</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>103</td>\n",
       "        <td>Alexander</td>\n",
       "        <td>Hunold</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>104</td>\n",
       "        <td>Bruce</td>\n",
       "        <td>Ernst</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>107</td>\n",
       "        <td>Diana</td>\n",
       "        <td>Lorentz</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>124</td>\n",
       "        <td>Kevin</td>\n",
       "        <td>Mourgos</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>141</td>\n",
       "        <td>Trenna</td>\n",
       "        <td>Rajs</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>142</td>\n",
       "        <td>Curtis</td>\n",
       "        <td>Davies</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>143</td>\n",
       "        <td>Randall</td>\n",
       "        <td>Matos</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>144</td>\n",
       "        <td>Peter</td>\n",
       "        <td>Vargas</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>149</td>\n",
       "        <td>Eleni</td>\n",
       "        <td>Zlotkey</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>176</td>\n",
       "        <td>Jonathon</td>\n",
       "        <td>Taylor</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>178</td>\n",
       "        <td>Kimberely</td>\n",
       "        <td>Grant</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>200</td>\n",
       "        <td>Jennifer</td>\n",
       "        <td>Whalen</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>201</td>\n",
       "        <td>Michael</td>\n",
       "        <td>Hartstein</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>202</td>\n",
       "        <td>Pat</td>\n",
       "        <td>Fay</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>205</td>\n",
       "        <td>Shelley</td>\n",
       "        <td>Higgins</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>206</td>\n",
       "        <td>William</td>\n",
       "        <td>Gietz</td>\n",
       "        <td>100000.00</td>\n",
       "        <td>60</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(100, 'Steven', 'King', Decimal('100000.00'), 90),\n",
       " (101, 'Neena', 'Kochhar', Decimal('100000.00'), 90),\n",
       " (102, 'Lex', 'De Haan', Decimal('100000.00'), 90),\n",
       " (103, 'Alexander', 'Hunold', Decimal('100000.00'), 90),\n",
       " (104, 'Bruce', 'Ernst', Decimal('100000.00'), 90),\n",
       " (107, 'Diana', 'Lorentz', Decimal('100000.00'), 90),\n",
       " (124, 'Kevin', 'Mourgos', Decimal('100000.00'), 50),\n",
       " (141, 'Trenna', 'Rajs', Decimal('100000.00'), 50),\n",
       " (142, 'Curtis', 'Davies', Decimal('100000.00'), 50),\n",
       " (143, 'Randall', 'Matos', Decimal('100000.00'), 50),\n",
       " (144, 'Peter', 'Vargas', Decimal('100000.00'), 50),\n",
       " (149, 'Eleni', 'Zlotkey', Decimal('100000.00'), 80),\n",
       " (176, 'Jonathon', 'Taylor', Decimal('100000.00'), 80),\n",
       " (178, 'Kimberely', 'Grant', Decimal('100000.00'), None),\n",
       " (200, 'Jennifer', 'Whalen', Decimal('100000.00'), 10),\n",
       " (201, 'Michael', 'Hartstein', Decimal('100000.00'), 20),\n",
       " (202, 'Pat', 'Fay', Decimal('100000.00'), 20),\n",
       " (205, 'Shelley', 'Higgins', Decimal('100000.00'), 60),\n",
       " (206, 'William', 'Gietz', Decimal('100000.00'), 60)]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select * from employees99;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "0 rows affected.\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "alter table employees99 rename column employee_id to ID;\n",
    "alter table employees99 rename column department_id to dept_id;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>Field</th>\n",
       "        <th>Type</th>\n",
       "        <th>Null</th>\n",
       "        <th>Key</th>\n",
       "        <th>Default</th>\n",
       "        <th>Extra</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ID</td>\n",
       "        <td>int(6)</td>\n",
       "        <td>NO</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>first_name</td>\n",
       "        <td>varchar(20)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>last_name</td>\n",
       "        <td>varchar(25)</td>\n",
       "        <td>NO</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>salary</td>\n",
       "        <td>decimal(8,2)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dept_id</td>\n",
       "        <td>int(4)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('ID', 'int(6)', 'NO', '', None, ''),\n",
       " ('first_name', 'varchar(20)', 'YES', '', None, ''),\n",
       " ('last_name', 'varchar(25)', 'NO', '', None, ''),\n",
       " ('salary', 'decimal(8,2)', 'YES', '', None, ''),\n",
       " ('dept_id', 'int(4)', 'YES', '', None, '')]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "desc employees99;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br><br>\n",
    "<span style=\"color:red\">[문제 6]</span>   \n",
    "EMP 테이블을 삭제하라."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "drop table emp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "20 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Tables_in_galaxy</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>animals</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>animals2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>countries</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>departments</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dept</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dept2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dept_sum_vu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>emp_details_view</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>employees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>employees2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>employees22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>employees3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>employees99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>hire_dates</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>job_grades</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>job_history</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>jobs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>locations</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>regions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>salvu50</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('animals',),\n",
       " ('animals2',),\n",
       " ('countries',),\n",
       " ('departments',),\n",
       " ('dept',),\n",
       " ('dept2',),\n",
       " ('dept_sum_vu',),\n",
       " ('emp_details_view',),\n",
       " ('employees',),\n",
       " ('employees2',),\n",
       " ('employees22',),\n",
       " ('employees3',),\n",
       " ('employees99',),\n",
       " ('hire_dates',),\n",
       " ('job_grades',),\n",
       " ('job_history',),\n",
       " ('jobs',),\n",
       " ('locations',),\n",
       " ('regions',),\n",
       " ('salvu50',)]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "show tables;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<br><br>\n",
    "<span style=\"color:red\">[문제 7]</span>   \n",
    "employees99 테이블의 이름을 EMP로 변경하라.  \n",
    "(이 명령문은 배우지 않았다. 구글신에게 물어서 해결하라.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql://root:***@localhost/galaxy\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "alter table employees99 rename to emp;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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>Field</th>\n",
       "        <th>Type</th>\n",
       "        <th>Null</th>\n",
       "        <th>Key</th>\n",
       "        <th>Default</th>\n",
       "        <th>Extra</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ID</td>\n",
       "        <td>int(6)</td>\n",
       "        <td>NO</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>first_name</td>\n",
       "        <td>varchar(20)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>last_name</td>\n",
       "        <td>varchar(25)</td>\n",
       "        <td>NO</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>salary</td>\n",
       "        <td>decimal(8,2)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>dept_id</td>\n",
       "        <td>int(4)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('ID', 'int(6)', 'NO', '', None, ''),\n",
       " ('first_name', 'varchar(20)', 'YES', '', None, ''),\n",
       " ('last_name', 'varchar(25)', 'NO', '', None, ''),\n",
       " ('salary', 'decimal(8,2)', 'YES', '', None, ''),\n",
       " ('dept_id', 'int(4)', 'YES', '', None, '')]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "desc emp;"
   ]
  }
 ],
 "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
}