Day 31 - 50 Days of Data Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f3e5a6d3",
   "metadata": {},
   "source": [
    "## 1.\t\n",
    "\n",
    "#### Import the employees_record_data dataset and save it as a table in SQL. Write code to fetch the table, first using fetchall() and then read_sql_query()."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "99439189",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Job Title</th>\n",
       "      <th>Department</th>\n",
       "      <th>Salary</th>\n",
       "      <th>HireDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>John Smith</td>\n",
       "      <td>Manager</td>\n",
       "      <td>Management</td>\n",
       "      <td>100000</td>\n",
       "      <td>1 01 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jane Doe</td>\n",
       "      <td>Sales Associate</td>\n",
       "      <td>Sales</td>\n",
       "      <td>50000</td>\n",
       "      <td>1 06 2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bob Johnson</td>\n",
       "      <td>Assistant Manager</td>\n",
       "      <td>Management</td>\n",
       "      <td>75000</td>\n",
       "      <td>1 03 2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mary Johnson</td>\n",
       "      <td>Sales Associate</td>\n",
       "      <td>Sales</td>\n",
       "      <td>45000</td>\n",
       "      <td>1 02 2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Kevin Lee</td>\n",
       "      <td>Analyst</td>\n",
       "      <td>Finance</td>\n",
       "      <td>80000</td>\n",
       "      <td>1 07 2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Name          Job Title  Department  Salary   HireDate\n",
       "0    John Smith            Manager  Management  100000  1 01 2020\n",
       "1      Jane Doe    Sales Associate       Sales   50000  1 06 2019\n",
       "2   Bob Johnson  Assistant Manager  Management   75000  1 03 2018\n",
       "3  Mary Johnson    Sales Associate       Sales   45000  1 02 2021\n",
       "4     Kevin Lee            Analyst     Finance   80000  1 07 2020"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv(\"employees_record_data.csv\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "7e2a80a8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table already exists\n"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "\n",
    "# Connect to SQL and create a database\n",
    "engine = sqlite3.connect('Employees_Records.db')\n",
    "cur = engine.cursor()\n",
    "\n",
    "# Save to SQL\n",
    "try:\n",
    "    df.to_sql('MyEmployeesTable', con=engine, index = False)\n",
    "except ValueError:\n",
    "    print(\"Table already exists\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "3ce439eb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('John Smith', 'Manager', 'Management', 100000, '1 01 2020'),\n",
       " ('Jane Doe', 'Sales Associate', 'Sales', 50000, '1 06 2019'),\n",
       " ('Bob Johnson', 'Assistant Manager', 'Management', 75000, '1 03 2018'),\n",
       " ('Mary Johnson', 'Sales Associate', 'Sales', 45000, '1 02 2021'),\n",
       " ('Kevin Lee', 'Analyst', 'Finance', 80000, '1 07 2020'),\n",
       " ('Sophia Martinez', 'Technician', 'Customer Service', 71000, '12 09 2018'),\n",
       " ('Emma Thomas', 'Assistant', 'Research', 59000, '30 11 2019'),\n",
       " ('Liam Taylor', 'Consultant', 'Finance', 72000, '22 07 2015'),\n",
       " ('Ava White', 'Specialist', 'Engineering', 67000, '18 09 2020'),\n",
       " ('Noah Martin', 'Coordinator', 'HR', 55000, '3 03 2019'),\n",
       " ('Isabella Hall', 'Officer', 'IT', 78000, '29 05 2018'),\n",
       " ('Ethan Garcia', 'Programmer', 'Marketing', 65000, '12 10 2016'),\n",
       " ('Mia Lopez', 'Associate', 'Operations', 72000, '8 08 2017'),\n",
       " ('Alexander Hill', 'Administrator', 'Sales', 60000, '5 04 2022'),\n",
       " ('Charlotte Clark', 'Designer', 'Research', 73000, '2 09 2019'),\n",
       " ('Daniel Lewis', 'Assistant', 'IT', 69000, '14 01 2018'),\n",
       " ('Harper Adams', 'Manager', 'Marketing', 85000, '7 06 2016')]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_data = cur.execute(\"\"\"SELECT * FROM MyEmployeesTable\"\"\")\n",
    "df_data.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "813f0bc3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Job Title</th>\n",
       "      <th>Department</th>\n",
       "      <th>Salary</th>\n",
       "      <th>HireDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>John Smith</td>\n",
       "      <td>Manager</td>\n",
       "      <td>Management</td>\n",
       "      <td>100000</td>\n",
       "      <td>1 01 2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jane Doe</td>\n",
       "      <td>Sales Associate</td>\n",
       "      <td>Sales</td>\n",
       "      <td>50000</td>\n",
       "      <td>1 06 2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bob Johnson</td>\n",
       "      <td>Assistant Manager</td>\n",
       "      <td>Management</td>\n",
       "      <td>75000</td>\n",
       "      <td>1 03 2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mary Johnson</td>\n",
       "      <td>Sales Associate</td>\n",
       "      <td>Sales</td>\n",
       "      <td>45000</td>\n",
       "      <td>1 02 2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Kevin Lee</td>\n",
       "      <td>Analyst</td>\n",
       "      <td>Finance</td>\n",
       "      <td>80000</td>\n",
       "      <td>1 07 2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Name          Job Title  Department  Salary   HireDate\n",
       "0    John Smith            Manager  Management  100000  1 01 2020\n",
       "1      Jane Doe    Sales Associate       Sales   50000  1 06 2019\n",
       "2   Bob Johnson  Assistant Manager  Management   75000  1 03 2018\n",
       "3  Mary Johnson    Sales Associate       Sales   45000  1 02 2021\n",
       "4     Kevin Lee            Analyst     Finance   80000  1 07 2020"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_table = pd.read_sql_query(\"\"\"\n",
    "    SELECT * FROM MyEmployeesTable\n",
    "   \n",
    "     \"\"\", con=engine)\n",
    "\n",
    "data_table.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b26a687a",
   "metadata": {},
   "source": [
    "## 2.\t\n",
    "\n",
    "#### Using pandas sql_query, what is the names and salaries of employees that make over $60,000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "8e5ce00a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>John Smith</td>\n",
       "      <td>100000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bob Johnson</td>\n",
       "      <td>75000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Kevin Lee</td>\n",
       "      <td>80000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sophia Martinez</td>\n",
       "      <td>71000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Liam Taylor</td>\n",
       "      <td>72000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Ava White</td>\n",
       "      <td>67000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Isabella Hall</td>\n",
       "      <td>78000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Ethan Garcia</td>\n",
       "      <td>65000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Mia Lopez</td>\n",
       "      <td>72000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Charlotte Clark</td>\n",
       "      <td>73000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Daniel Lewis</td>\n",
       "      <td>69000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Harper Adams</td>\n",
       "      <td>85000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Name  Salary\n",
       "0        John Smith  100000\n",
       "1       Bob Johnson   75000\n",
       "2         Kevin Lee   80000\n",
       "3   Sophia Martinez   71000\n",
       "4       Liam Taylor   72000\n",
       "5         Ava White   67000\n",
       "6     Isabella Hall   78000\n",
       "7      Ethan Garcia   65000\n",
       "8         Mia Lopez   72000\n",
       "9   Charlotte Clark   73000\n",
       "10     Daniel Lewis   69000\n",
       "11     Harper Adams   85000"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary_over_60k = pd.read_sql_query(\"\"\"\n",
    "    SELECT Name, Salary\n",
    "    FROM MyEmployeesTable\n",
    "    WHERE Salary > 60000\n",
    "\"\"\", con=engine)\n",
    "\n",
    "salary_over_60k"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd2eea81",
   "metadata": {},
   "source": [
    "## 3.\t\n",
    "\n",
    "#### Write a code to return the average salary of each department. Group it by department"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e0388fba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Department</th>\n",
       "      <th>Average_salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Customer Service</td>\n",
       "      <td>71000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Engineering</td>\n",
       "      <td>67000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Finance</td>\n",
       "      <td>76000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>HR</td>\n",
       "      <td>55000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>IT</td>\n",
       "      <td>73500.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Management</td>\n",
       "      <td>87500.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Marketing</td>\n",
       "      <td>75000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Operations</td>\n",
       "      <td>72000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Research</td>\n",
       "      <td>66000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Sales</td>\n",
       "      <td>51666.666667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Department  Average_salary\n",
       "0  Customer Service    71000.000000\n",
       "1       Engineering    67000.000000\n",
       "2           Finance    76000.000000\n",
       "3                HR    55000.000000\n",
       "4                IT    73500.000000\n",
       "5        Management    87500.000000\n",
       "6         Marketing    75000.000000\n",
       "7        Operations    72000.000000\n",
       "8          Research    66000.000000\n",
       "9             Sales    51666.666667"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ave_salary = pd.read_sql_query(\"\"\"\n",
    "    SELECT Department, AVG(Salary) AS Average_salary\n",
    "    FROM MyEmployeesTable\n",
    "    GROUP BY Department\n",
    "\"\"\", con=engine)\n",
    "\n",
    "ave_salary"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "99d778d2",
   "metadata": {},
   "source": [
    "## 4.\t\n",
    "\n",
    "#### Which department has the highest-paid employee? What is their name?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "bff5ad44",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Department</th>\n",
       "      <th>Name</th>\n",
       "      <th>Highest_salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Management</td>\n",
       "      <td>John Smith</td>\n",
       "      <td>100000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Department        Name  Highest_salary\n",
       "0  Management  John Smith          100000"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "highest_salary = pd.read_sql_query(\"\"\"\n",
    "    SELECT Department, Name, MAX(Salary) AS Highest_salary\n",
    "    FROM MyEmployeesTable\n",
    "\"\"\", con=engine)\n",
    "\n",
    "highest_salary"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8f443d0a",
   "metadata": {},
   "source": [
    "## 5. \n",
    "\n",
    "#### Which employees where hired before 2020?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "79b2ba9b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>John Smith</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jane Doe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bob Johnson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mary Johnson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Kevin Lee</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Sophia Martinez</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Ava White</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Ethan Garcia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Charlotte Clark</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Daniel Lewis</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Name\n",
       "0       John Smith\n",
       "1         Jane Doe\n",
       "2      Bob Johnson\n",
       "3     Mary Johnson\n",
       "4        Kevin Lee\n",
       "5  Sophia Martinez\n",
       "6        Ava White\n",
       "7     Ethan Garcia\n",
       "8  Charlotte Clark\n",
       "9     Daniel Lewis"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hire_date_before_2020 = pd.read_sql_query(\"\"\"\n",
    "    SELECT Name\n",
    "    FROM MyEmployeesTable\n",
    "    WHERE HireDate < \"2020-01-01\"\n",
    "\"\"\", con=engine)\n",
    "\n",
    "hire_date_before_2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "788d4eac",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}