ch6_homework_2023.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bLvM-ORq_s_w"
      },
      "source": [
        "# Homework - SQL2\n",
        "\n",
        "1. Use Google Colabortory and do your homework.\n",
        "3. (In Google colaboratory) Before you submit your homework, restart kernel and run every cell!\n",
        "4. Save (File->Save) the file\n",
        "5. Submit your homework (this file) in Google classroom\n",
        "6. **Don't forget to click \"제출\" button** (\"Submit\", \"완료로 표시\", 또는 \"제출\" 버턴을 누르지 않으면 제출된 것이 아님)\n",
        "7. No late homeworks will be accepted for any reason!"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "h0fEj42V_s_0"
      },
      "source": [
        "To edit this cell, double click here\n",
        "```\n",
        "이름:_______________________\n",
        "학번:_______________________\n",
        "학과:_______________________\n",
        "제출일:______________________\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VcgBV_sf_s_1"
      },
      "source": [
        "- 권고사항: 모든 SQL select 문에 tuple variable를 사용할 것.\n",
        "\n",
        "- SQL 작성시, 문제에 주어지지 않은 상수를 사용하는 경우 (cheating), **마이너스 점수**를 받는다.\n",
        "\n",
        "- **점수: 각 10점, 19번 20점, 총200점**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "zEEZmXKK_s_1"
      },
      "outputs": [],
      "source": [
        "%load_ext sql"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Z_7tQqKP_s_3"
      },
      "outputs": [],
      "source": [
        "!pip install mysqlclient"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PwawA27Y_s_3"
      },
      "source": [
        "### Use your host, id, password, and database"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "hIYb8WQd_s_3"
      },
      "outputs": [],
      "source": [
        "import getpass\n",
        "user = 's202300001'\n",
        "password = getpass.getpass()\n",
        "host='dm.hufs.ac.kr'\n",
        "database = 's202300001db'\n",
        "connection_string = f'mysql://{user}:{password}@{host}:3306/{database}'\n",
        "\n",
        "%sql $connection_string"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lebsMMxS_s_4"
      },
      "source": [
        "## Warning: Your companydb state MUST be clean as initial state\n",
        "\n",
        "- Refer to jupyter notebook on \"SQL Lab\""
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "D3RbeCSF_s_5"
      },
      "source": [
        "### When you list the name of employees, the name must be the following format:\n",
        "\n",
        "- Output Schema and tuple format:\n",
        "\n",
        "```\n",
        "+------------------+\n",
        "| Employee name    |\n",
        "+------------------+\n",
        "| John B. Smith    |\n",
        "...\n",
        "+------------------+\n",
        "```\n",
        "\n",
        "- Use MySQL concat 함수 사용, Refer to MySQL manual or  Googling"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lwKUsNY5_s_5"
      },
      "source": [
        "### 1-14번에 대해 SQL select 문을 작성하고 MySQL에서 실행한 결과물을 제출하시오."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ehk8L5u3_s_6"
      },
      "source": [
        "#### 1. Retrieve the names of employees in department 5 who work more than 10 hours per week on the 'ProductX' project. (single SELECT 사용, MySQL concat 함수 사용)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Ads-lMbu_s_6"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "/* YOUR CODE HERE */\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ExpWSc-W_s_6"
      },
      "source": [
        "\n",
        "#### 2.  List the names of employees who have a dependent with the same sex as themselves. (EXISTS 사용)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "aaU_CwoZ_s_6"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "/* YOUR CODE HERE */\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FWaCWtIA_s_7"
      },
      "source": [
        "#### 3.  Find the names of employees that are directly supervised by 'Franklin Wong'. (EXISTS 사용)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "4zU940_H_s_7"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "/* YOUR CODE HERE */\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dzwcSZHT_s_7"
      },
      "source": [
        "#### 4.  For each project, list the project name and the total hours per week (by all employees) spent on that project.\n",
        "Output Schema:\n",
        "```\n",
        "+-----------------+-------------+\n",
        "| Project name    | Total hours |\n",
        "+-----------------+-------------+\n",
        "```"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "_lzRt34V_s_7"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "/* YOUR CODE HERE */\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FZkQ2Gvs_s_7"
      },
      "source": [
        "#### 5.  Retrieve the names of employees who work on every project managed by 'Administration' department."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "A_qtcKuP_s_8"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "/* YOUR CODE HERE */\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qd93E2eK_s_8"
      },
      "source": [
        "#### 6.  Retrieve the names of employees who do not work on any project which is located in 'Houston'. (NOT EXISTS 사용)\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Qmef9CIq_s_8"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "/* YOUR CODE HERE */\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "eWsxR11g_s_8"
      },
      "source": [
        "#### 7.  For each department, retrieve the department name, and the average salary of employees working in that department.(소수점 이하 2자리까지만(버림) 출력, Refer to MySQL manual or  Googling)\n",
        "\n",
        "Output schema\n",
        "```\n",
        "+-----------------+----------------+\n",
        "| Department name | Average salary |\n",
        "+-----------------+----------------+\n",
        "```"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "jlgOOBuf_s_9"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "/* YOUR CODE HERE */\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Jb4bXMnU_s_9"
      },
      "source": [
        "#### 8.  Retrieve the average salary of all female employees. (소수점 이하 2자리까지만(버림) 출력, Refer to MySQL manual or  Googling)\n",
        "Output Schema:\n",
        "```\n",
        "+----------------+\n",
        "| Average salary |\n",
        "+----------------+\n",
        "```"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "BFFDXiB0_s_9"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "/* YOUR CODE HERE */\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "P_Mgekga_s_9"
      },
      "source": [
        "#### 9.  Find the names and addresses of employees who work on at least one project located in Houston but whose department has no location in Houston. (EXISTS, NOT EXISTS 사용)\n",
        "\n",
        "Output schema:\n",
        "\n",
        "```\n",
        "+---------------------+-------------------------+\n",
        "| Employee name       | Address                 |\n",
        "+---------------------+-------------------------+\n",
        "```"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "edE4jPgJ_s_9"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "/* YOUR CODE HERE */\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "MWOTKOAQ_s_9"
      },
      "source": [
        "#### 10.  List the names of department managers who have no dependents. (EXISTS, NOT EXISTS 사용)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "h8iFFOVy_s_-"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "/* YOUR CODE HERE */\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rdmEoPVa_s_-"
      },
      "source": [
        "#### 11. Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "M02cc6Xy_s_-"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "/* YOUR CODE HERE */\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_2WSJNKg_s_-"
      },
      "source": [
        "#### 12. Retrieve the names of all employees whose supervisor’s supervisor is James Borg. (EXISTS 안에 EXISTS 사용)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Cge59IT7_s_-"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "/* YOUR CODE HERE */\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KU8Xgq6M_s_-"
      },
      "source": [
        "#### 13. Retrieve the names of all employees whose supervisor’s supervisor is James Borg. (single SELECT 사용)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "R5-xAsJa_s_-"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "/* YOUR CODE HERE */\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BVKK0wmj_s_-"
      },
      "source": [
        "#### 14. Retrieve the names of employees who make at least 10,000 dollars more than the employee who is paid the least in the company."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ah8GnEXn_s_-"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "/* YOUR CODE HERE */\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9TBK4hhZ_s__"
      },
      "source": [
        "### 15-18번에 대해 SQL view문을 작성하고 MySQL에서 “select * from <view_name>”을 실행한 결과물을 제출하시오."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "p1iKeYf6_s__"
      },
      "source": [
        "#### 15. A view that has the department name, manager name, and manager salary for every department\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Iu3pnt_b_s__"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "/* YOUR CODE HERE */\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "8C2Qcw1g_s__"
      },
      "outputs": [],
      "source": [
        "%sql select * from DEPT_VIEW;"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wcLSDNnt_s__"
      },
      "source": [
        "#### 16. A view that has the employee name, supervisor name, and employee salary for each employee who works in the ‘Research’ department\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "0e7iHQJY_s__"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "/* YOUR CODE HERE */\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Ki6KguKt_s__"
      },
      "outputs": [],
      "source": [
        "%sql select * from RESEARCH_DEPT_VIEW;"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NFnrd795_s__"
      },
      "source": [
        "#### 17. A view that has the project name, controlling department name, number of employees, and total hours worked per week on the project for each project\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ERDMgg7S_s__"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "/* YOUR CODE HERE */\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "MrXNgEBu_s__"
      },
      "outputs": [],
      "source": [
        "%sql select * from PROJECT_VIEW;"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RjXARLMD_tAA"
      },
      "source": [
        "#### 18. A view that has the project name, controlling department name, number of employees, and total hours worked per week on the project for each project with more than two employees working on it"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "kAc8mxHS_tAA"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "/* YOUR CODE HERE */\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "d4qsimVd_tAA"
      },
      "outputs": [],
      "source": [
        "%sql select * from PROJECT_VIEW_GT2;"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WKKnXE1-_tAA"
      },
      "source": [
        "#### 19. EMPLOYEE 테이블의 salary의 변경사항이 있을 때 마다, 변경사항을 기록하는 테이블 salary_audit(ssn, before_salary, after_salary, u_datetime)을 만들고, update trigger를 사용하여 변경사항을 기록하시오. 5번 부서에 속하는 직원의 salary를 100% 상향 조정 후, “select * from salary_audit”를 통해 결과를 확인하시오. udatetime에는 수정이 실행된 DATETIME(SQL Datatype, 날짜와 시각)을 기록한다. 현재 시각값은 now() 함수를 통해 얻을 수 있다.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BpAn5mWC_tAA"
      },
      "source": [
        "## Warning\n",
        "- 19번 문제 Update 한 후에 1-18번 문제를 다시 풀면 다른 결과가 나올 수 있음\n",
        "- 1-18번 문제는 반드시 companydb의 초기 상태에서 풀어야 함"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "rKWedvV7_tAA"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "/* YOUR CODE HERE  for \"create table ...\" */\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Cja2xalh_tAA"
      },
      "outputs": [],
      "source": [
        "%%sql\n",
        "/* YOUR CODE HERE for \"create trigger ...\" */\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "FSb7Pp8b_tAA"
      },
      "outputs": [],
      "source": [
        "%sql select * from salary_audit;"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ruxXSS75_tAA"
      },
      "outputs": [],
      "source": [
        "%sql update EMPLOYEE set salary = 2 * salary where dno = 5;"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "suOVQN7m_tAA"
      },
      "outputs": [],
      "source": [
        "%sql select * from salary_audit;"
      ]
    }
  ],
  "metadata": {
    "celltoolbar": "Tags",
    "colab": {
      "provenance": []
    },
    "hide_input": false,
    "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.8.8"
    },
    "toc": {
      "base_numbering": 1,
      "nav_menu": {},
      "number_sections": false,
      "sideBar": true,
      "skip_h1_title": true,
      "title_cell": "Table of Contents",
      "title_sidebar": "Contents",
      "toc_cell": false,
      "toc_position": {},
      "toc_section_display": true,
      "toc_window_display": false
    },
    "varInspector": {
      "cols": {
        "lenName": 16,
        "lenType": 16,
        "lenVar": 40
      },
      "kernels_config": {
        "python": {
          "delete_cmd_postfix": "",
          "delete_cmd_prefix": "del ",
          "library": "var_list.py",
          "varRefreshCmd": "print(var_dic_list())"
        },
        "r": {
          "delete_cmd_postfix": ") ",
          "delete_cmd_prefix": "rm(",
          "library": "var_list.r",
          "varRefreshCmd": "cat(var_dic_list()) "
        }
      },
      "types_to_exclude": [
        "module",
        "function",
        "builtin_function_or_method",
        "instance",
        "_Feature"
      ],
      "window_display": false
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}