1. Introduction & SQLite Queries.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e4ae7e6e",
   "metadata": {},
   "source": [
    "# Benefits of SQL for Data Analysis\n",
    "* SQL for Data Analysis is a powerful programming language that helps data analysts interact with data stored in Relational databases. By using SQL several companies have built their proprietary tools to fetch information from databases quickly.\n",
    "* This data-driven approach has enabled the industry to channel its growth by analyzing meaningful information to make critical business decisions. Data Analysis has played a vital role in identifying trends and patterns as organizations forecast their business goals by extracting their historical data in databases."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f70436bb",
   "metadata": {},
   "source": [
    "### Importing required libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "091d0a0d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlite3 as sql"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37f69275",
   "metadata": {},
   "source": [
    "### Connecting to the Database\n",
    "* Connecting to the SQLite Database can be established using the connect() method, passing the name of the database to be accessed as a parameter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "136d7264",
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = sql.connect('jobs.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4f5135b7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Connection at 0x1afa3635b70>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connection"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2824a87d",
   "metadata": {},
   "source": [
    "# SQLite Queries"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c804fee8",
   "metadata": {},
   "source": [
    "### Defining a function to read queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f0e1689b",
   "metadata": {},
   "outputs": [],
   "source": [
    "def read(query):\n",
    "    return pd.read_sql_query(query, connection)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "952778ff",
   "metadata": {},
   "source": [
    "### The Schema Table\n",
    "* Every SQLite database contains a single \"schema table\" that stores the schema for that database. The schema for a database is a description of all of the other tables, indexes, triggers, and views that are contained within the database\n",
    "* It contains one row for each table, index, view, and trigger (collectively \"objects\") in the schema, except there is no entry for the sqlite_schema table itself.\n",
    "* The schema table can always be referenced using the name \"sqlite_schema\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21e5c573",
   "metadata": {},
   "source": [
    "### Select Data from Table\n",
    "* Select statement is used to retrieve data from an SQLite table and this returns the data contained in the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "279125d7",
   "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>type</th>\n",
       "      <th>name</th>\n",
       "      <th>tbl_name</th>\n",
       "      <th>rootpage</th>\n",
       "      <th>sql</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>table</td>\n",
       "      <td>recent_grads</td>\n",
       "      <td>recent_grads</td>\n",
       "      <td>2</td>\n",
       "      <td>CREATE TABLE \"recent_grads\" (\\n\"index\" INTEGER...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>index</td>\n",
       "      <td>ix_recent_grads_index</td>\n",
       "      <td>recent_grads</td>\n",
       "      <td>3</td>\n",
       "      <td>CREATE INDEX \"ix_recent_grads_index\"ON \"recent...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    type                   name      tbl_name  rootpage  \\\n",
       "0  table           recent_grads  recent_grads         2   \n",
       "1  index  ix_recent_grads_index  recent_grads         3   \n",
       "\n",
       "                                                 sql  \n",
       "0  CREATE TABLE \"recent_grads\" (\\n\"index\" INTEGER...  \n",
       "1  CREATE INDEX \"ix_recent_grads_index\"ON \"recent...  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read(\"\"\"\n",
    "SELECT * FROM sqlite_master\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab4dc980",
   "metadata": {},
   "source": [
    "### Selecting single column from table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "4e1a6af4",
   "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>Major_category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168</th>\n",
       "      <td>Biology &amp; Life Science</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>Psychology &amp; Social Work</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170</th>\n",
       "      <td>Psychology &amp; Social Work</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>Psychology &amp; Social Work</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>Education</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>173 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Major_category\n",
       "0                 Engineering\n",
       "1                 Engineering\n",
       "2                 Engineering\n",
       "3                 Engineering\n",
       "4                 Engineering\n",
       "..                        ...\n",
       "168    Biology & Life Science\n",
       "169  Psychology & Social Work\n",
       "170  Psychology & Social Work\n",
       "171  Psychology & Social Work\n",
       "172                 Education\n",
       "\n",
       "[173 rows x 1 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read('SELECT Major_category FROM recent_grads')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "718f2672",
   "metadata": {},
   "source": [
    "### Selecting multiple columns from table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "9c496063",
   "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>Major_category</th>\n",
       "      <th>Major</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Engineering</td>\n",
       "      <td>PETROLEUM ENGINEERING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Engineering</td>\n",
       "      <td>MINING AND MINERAL ENGINEERING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Engineering</td>\n",
       "      <td>METALLURGICAL ENGINEERING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Engineering</td>\n",
       "      <td>NAVAL ARCHITECTURE AND MARINE ENGINEERING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Engineering</td>\n",
       "      <td>CHEMICAL ENGINEERING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168</th>\n",
       "      <td>Biology &amp; Life Science</td>\n",
       "      <td>ZOOLOGY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>Psychology &amp; Social Work</td>\n",
       "      <td>EDUCATIONAL PSYCHOLOGY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170</th>\n",
       "      <td>Psychology &amp; Social Work</td>\n",
       "      <td>CLINICAL PSYCHOLOGY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>Psychology &amp; Social Work</td>\n",
       "      <td>COUNSELING PSYCHOLOGY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>Education</td>\n",
       "      <td>LIBRARY SCIENCE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>173 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Major_category                                      Major\n",
       "0                 Engineering                      PETROLEUM ENGINEERING\n",
       "1                 Engineering             MINING AND MINERAL ENGINEERING\n",
       "2                 Engineering                  METALLURGICAL ENGINEERING\n",
       "3                 Engineering  NAVAL ARCHITECTURE AND MARINE ENGINEERING\n",
       "4                 Engineering                       CHEMICAL ENGINEERING\n",
       "..                        ...                                        ...\n",
       "168    Biology & Life Science                                    ZOOLOGY\n",
       "169  Psychology & Social Work                     EDUCATIONAL PSYCHOLOGY\n",
       "170  Psychology & Social Work                        CLINICAL PSYCHOLOGY\n",
       "171  Psychology & Social Work                      COUNSELING PSYCHOLOGY\n",
       "172                 Education                            LIBRARY SCIENCE\n",
       "\n",
       "[173 rows x 2 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read('SELECT Major_category,Major FROM recent_grads')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3fb52d1",
   "metadata": {},
   "source": [
    "### Selecting all the columns from table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "3ad7b5e7",
   "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>index</th>\n",
       "      <th>Rank</th>\n",
       "      <th>Major_code</th>\n",
       "      <th>Major</th>\n",
       "      <th>Major_category</th>\n",
       "      <th>Total</th>\n",
       "      <th>Sample_size</th>\n",
       "      <th>Men</th>\n",
       "      <th>Women</th>\n",
       "      <th>ShareWomen</th>\n",
       "      <th>...</th>\n",
       "      <th>Part_time</th>\n",
       "      <th>Full_time_year_round</th>\n",
       "      <th>Unemployed</th>\n",
       "      <th>Unemployment_rate</th>\n",
       "      <th>Median</th>\n",
       "      <th>P25th</th>\n",
       "      <th>P75th</th>\n",
       "      <th>College_jobs</th>\n",
       "      <th>Non_college_jobs</th>\n",
       "      <th>Low_wage_jobs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2419</td>\n",
       "      <td>PETROLEUM ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>2339</td>\n",
       "      <td>36</td>\n",
       "      <td>2057</td>\n",
       "      <td>282</td>\n",
       "      <td>0.120564</td>\n",
       "      <td>...</td>\n",
       "      <td>270</td>\n",
       "      <td>1207</td>\n",
       "      <td>37</td>\n",
       "      <td>0.018381</td>\n",
       "      <td>110000</td>\n",
       "      <td>95000</td>\n",
       "      <td>125000</td>\n",
       "      <td>1534</td>\n",
       "      <td>364</td>\n",
       "      <td>193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2416</td>\n",
       "      <td>MINING AND MINERAL ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>756</td>\n",
       "      <td>7</td>\n",
       "      <td>679</td>\n",
       "      <td>77</td>\n",
       "      <td>0.101852</td>\n",
       "      <td>...</td>\n",
       "      <td>170</td>\n",
       "      <td>388</td>\n",
       "      <td>85</td>\n",
       "      <td>0.117241</td>\n",
       "      <td>75000</td>\n",
       "      <td>55000</td>\n",
       "      <td>90000</td>\n",
       "      <td>350</td>\n",
       "      <td>257</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2415</td>\n",
       "      <td>METALLURGICAL ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>856</td>\n",
       "      <td>3</td>\n",
       "      <td>725</td>\n",
       "      <td>131</td>\n",
       "      <td>0.153037</td>\n",
       "      <td>...</td>\n",
       "      <td>133</td>\n",
       "      <td>340</td>\n",
       "      <td>16</td>\n",
       "      <td>0.024096</td>\n",
       "      <td>73000</td>\n",
       "      <td>50000</td>\n",
       "      <td>105000</td>\n",
       "      <td>456</td>\n",
       "      <td>176</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>2417</td>\n",
       "      <td>NAVAL ARCHITECTURE AND MARINE ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>1258</td>\n",
       "      <td>16</td>\n",
       "      <td>1123</td>\n",
       "      <td>135</td>\n",
       "      <td>0.107313</td>\n",
       "      <td>...</td>\n",
       "      <td>150</td>\n",
       "      <td>692</td>\n",
       "      <td>40</td>\n",
       "      <td>0.050125</td>\n",
       "      <td>70000</td>\n",
       "      <td>43000</td>\n",
       "      <td>80000</td>\n",
       "      <td>529</td>\n",
       "      <td>102</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>2405</td>\n",
       "      <td>CHEMICAL ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>32260</td>\n",
       "      <td>289</td>\n",
       "      <td>21239</td>\n",
       "      <td>11021</td>\n",
       "      <td>0.341631</td>\n",
       "      <td>...</td>\n",
       "      <td>5180</td>\n",
       "      <td>16697</td>\n",
       "      <td>1672</td>\n",
       "      <td>0.061098</td>\n",
       "      <td>65000</td>\n",
       "      <td>50000</td>\n",
       "      <td>75000</td>\n",
       "      <td>18314</td>\n",
       "      <td>4440</td>\n",
       "      <td>972</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  Rank  Major_code                                      Major  \\\n",
       "0      0     1        2419                      PETROLEUM ENGINEERING   \n",
       "1      1     2        2416             MINING AND MINERAL ENGINEERING   \n",
       "2      2     3        2415                  METALLURGICAL ENGINEERING   \n",
       "3      3     4        2417  NAVAL ARCHITECTURE AND MARINE ENGINEERING   \n",
       "4      4     5        2405                       CHEMICAL ENGINEERING   \n",
       "\n",
       "  Major_category  Total  Sample_size    Men  Women  ShareWomen  ...  \\\n",
       "0    Engineering   2339           36   2057    282    0.120564  ...   \n",
       "1    Engineering    756            7    679     77    0.101852  ...   \n",
       "2    Engineering    856            3    725    131    0.153037  ...   \n",
       "3    Engineering   1258           16   1123    135    0.107313  ...   \n",
       "4    Engineering  32260          289  21239  11021    0.341631  ...   \n",
       "\n",
       "   Part_time  Full_time_year_round  Unemployed  Unemployment_rate  Median  \\\n",
       "0        270                  1207          37           0.018381  110000   \n",
       "1        170                   388          85           0.117241   75000   \n",
       "2        133                   340          16           0.024096   73000   \n",
       "3        150                   692          40           0.050125   70000   \n",
       "4       5180                 16697        1672           0.061098   65000   \n",
       "\n",
       "   P25th   P75th  College_jobs  Non_college_jobs  Low_wage_jobs  \n",
       "0  95000  125000          1534               364            193  \n",
       "1  55000   90000           350               257             50  \n",
       "2  50000  105000           456               176              0  \n",
       "3  43000   80000           529               102              0  \n",
       "4  50000   75000         18314              4440            972  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read('SELECT * FROM recent_grads').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4ca31e52",
   "metadata": {},
   "source": [
    "### Column Alias\n",
    "* You can rename a table or a column temporarily by giving another name, which is known as ALIAS\n",
    "* The use of table aliases means to rename a table in a particular SQLite statement\n",
    "* Renaming is a temporary change and the actual table name does not change in the database\n",
    "* The column aliases are used to rename a table's columns for the purpose of a particular SQLite query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "deb8a091",
   "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>MAJOR CATAGORY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168</th>\n",
       "      <td>Biology &amp; Life Science</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>Psychology &amp; Social Work</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170</th>\n",
       "      <td>Psychology &amp; Social Work</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>Psychology &amp; Social Work</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>Education</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>173 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               MAJOR CATAGORY\n",
       "0                 Engineering\n",
       "1                 Engineering\n",
       "2                 Engineering\n",
       "3                 Engineering\n",
       "4                 Engineering\n",
       "..                        ...\n",
       "168    Biology & Life Science\n",
       "169  Psychology & Social Work\n",
       "170  Psychology & Social Work\n",
       "171  Psychology & Social Work\n",
       "172                 Education\n",
       "\n",
       "[173 rows x 1 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read('SELECT Major_category AS \"MAJOR CATAGORY\" FROM recent_grads')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48817a2c",
   "metadata": {},
   "source": [
    "### WHERE Clause\n",
    "* Where clause is used in order to make our search results more specific, using the where clause in SQL/SQLite we can go ahead and specify specific conditions that have to be met when retrieving data from the database.\n",
    "* If we want to retrieve, update or delete a particular set of data we can use the where clause. If we don’t have condition matching values in your database tables we probably didn’t get anything returned."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "4cb064f5",
   "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>ShareWomen</th>\n",
       "      <th>Major</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.535714</td>\n",
       "      <td>ACTUARIAL SCIENCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.578766</td>\n",
       "      <td>COMPUTER SCIENCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.558548</td>\n",
       "      <td>ENVIRONMENTAL ENGINEERING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.896019</td>\n",
       "      <td>NURSING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.750473</td>\n",
       "      <td>INDUSTRIAL PRODUCTION TECHNOLOGIES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>0.637293</td>\n",
       "      <td>ZOOLOGY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>0.817099</td>\n",
       "      <td>EDUCATIONAL PSYCHOLOGY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>0.799859</td>\n",
       "      <td>CLINICAL PSYCHOLOGY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>0.798746</td>\n",
       "      <td>COUNSELING PSYCHOLOGY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>0.877960</td>\n",
       "      <td>LIBRARY SCIENCE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>97 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    ShareWomen                               Major\n",
       "0     0.535714                   ACTUARIAL SCIENCE\n",
       "1     0.578766                    COMPUTER SCIENCE\n",
       "2     0.558548           ENVIRONMENTAL ENGINEERING\n",
       "3     0.896019                             NURSING\n",
       "4     0.750473  INDUSTRIAL PRODUCTION TECHNOLOGIES\n",
       "..         ...                                 ...\n",
       "92    0.637293                             ZOOLOGY\n",
       "93    0.817099              EDUCATIONAL PSYCHOLOGY\n",
       "94    0.799859                 CLINICAL PSYCHOLOGY\n",
       "95    0.798746               COUNSELING PSYCHOLOGY\n",
       "96    0.877960                     LIBRARY SCIENCE\n",
       "\n",
       "[97 rows x 2 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read('SELECT ShareWOmen, major FROM recent_grads WHERE ShareWOmen > 0.5')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9316f112",
   "metadata": {},
   "source": [
    "### ORDER BY Clause\n",
    "* The ORDER BY statement is a SQL statement that is used to sort the data in either ascending or descending according to one or more columns. By default, ORDER BY sorts the data in ascending order.\n",
    "\n",
    "* DESC is used to sort the data in descending order.\n",
    "* ASC to sort in ascending order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "405d277e",
   "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>ShareWomen</th>\n",
       "      <th>Major</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.799859</td>\n",
       "      <td>CLINICAL PSYCHOLOGY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.798920</td>\n",
       "      <td>TEACHER EDUCATION: MULTIPLE LEVELS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.798746</td>\n",
       "      <td>COUNSELING PSYCHOLOGY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.792095</td>\n",
       "      <td>MATHEMATICS TEACHER EDUCATION</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.779933</td>\n",
       "      <td>PSYCHOLOGY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>0.515406</td>\n",
       "      <td>BIOCHEMICAL SCIENCES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>0.507377</td>\n",
       "      <td>INTERCULTURAL AND INTERNATIONAL STUDIES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>0.506721</td>\n",
       "      <td>PHYSICAL AND HEALTH EDUCATION TEACHING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>0.505141</td>\n",
       "      <td>CHEMISTRY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>0.502846</td>\n",
       "      <td>GENERAL SOCIAL SCIENCES</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>79 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    ShareWomen                                    Major\n",
       "0     0.799859                      CLINICAL PSYCHOLOGY\n",
       "1     0.798920       TEACHER EDUCATION: MULTIPLE LEVELS\n",
       "2     0.798746                    COUNSELING PSYCHOLOGY\n",
       "3     0.792095            MATHEMATICS TEACHER EDUCATION\n",
       "4     0.779933                               PSYCHOLOGY\n",
       "..         ...                                      ...\n",
       "74    0.515406                     BIOCHEMICAL SCIENCES\n",
       "75    0.507377  INTERCULTURAL AND INTERNATIONAL STUDIES\n",
       "76    0.506721   PHYSICAL AND HEALTH EDUCATION TEACHING\n",
       "77    0.505141                                CHEMISTRY\n",
       "78    0.502846                  GENERAL SOCIAL SCIENCES\n",
       "\n",
       "[79 rows x 2 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read(\"\"\"\n",
    "SELECT ShareWOmen, major FROM recent_grads \n",
    "WHERE ShareWOmen > 0.5 and ShareWOmen < 0.8 \n",
    "ORDER BY ShareWOmen DESC\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d1c0c3d",
   "metadata": {},
   "source": [
    "### LIMIT Clause\n",
    "* LIMIT keyword is used to limit the data given by the SELECT statement\n",
    "* If there are many tuples satisfying the query conditions, it might be resourceful to view only a handful of them at a time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "d37203a8",
   "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>ShareWomen</th>\n",
       "      <th>Major</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.799859</td>\n",
       "      <td>CLINICAL PSYCHOLOGY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.798920</td>\n",
       "      <td>TEACHER EDUCATION: MULTIPLE LEVELS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.798746</td>\n",
       "      <td>COUNSELING PSYCHOLOGY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.792095</td>\n",
       "      <td>MATHEMATICS TEACHER EDUCATION</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.779933</td>\n",
       "      <td>PSYCHOLOGY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.774577</td>\n",
       "      <td>GENERAL MEDICAL AND HEALTH SERVICES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.770901</td>\n",
       "      <td>HEALTH AND MEDICAL ADMINISTRATIVE SERVICES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0.764427</td>\n",
       "      <td>SOIL SCIENCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.764320</td>\n",
       "      <td>LINGUISTICS AND COMPARATIVE LANGUAGE AND LITER...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0.758060</td>\n",
       "      <td>AREA ETHNIC AND CIVILIZATION STUDIES</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ShareWomen                                              Major\n",
       "0    0.799859                                CLINICAL PSYCHOLOGY\n",
       "1    0.798920                 TEACHER EDUCATION: MULTIPLE LEVELS\n",
       "2    0.798746                              COUNSELING PSYCHOLOGY\n",
       "3    0.792095                      MATHEMATICS TEACHER EDUCATION\n",
       "4    0.779933                                         PSYCHOLOGY\n",
       "5    0.774577                GENERAL MEDICAL AND HEALTH SERVICES\n",
       "6    0.770901         HEALTH AND MEDICAL ADMINISTRATIVE SERVICES\n",
       "7    0.764427                                       SOIL SCIENCE\n",
       "8    0.764320  LINGUISTICS AND COMPARATIVE LANGUAGE AND LITER...\n",
       "9    0.758060               AREA ETHNIC AND CIVILIZATION STUDIES"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read(\"\"\"\n",
    "SELECT ShareWOmen, major FROM recent_grads \n",
    "WHERE ShareWOmen > 0.5 and ShareWOmen < 0.8 \n",
    "ORDER BY ShareWOmen DESC\n",
    "LIMIT 10\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c4940c1",
   "metadata": {},
   "source": [
    "### GROUP BY clause\n",
    "* The GROUP BY clause is an optional clause of the SELECT statement. The GROUP BY clause a selected group of rows into summary rows by values of one or more columns\n",
    "* The GROUP BY clause returns one row for each group. For each group, you can apply an aggregate function such as MIN, MAX, SUM, COUNT, or AVG to provide more information about each group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "238a5fb2",
   "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>Avg_Share_Women</th>\n",
       "      <th>Major_category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.617938</td>\n",
       "      <td>Agriculture &amp; Natural Resources</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.561851</td>\n",
       "      <td>Arts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.584518</td>\n",
       "      <td>Biology &amp; Life Science</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.405063</td>\n",
       "      <td>Business</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.643835</td>\n",
       "      <td>Communications &amp; Journalism</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.512752</td>\n",
       "      <td>Computers &amp; Mathematics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.674986</td>\n",
       "      <td>Education</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0.257158</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.616857</td>\n",
       "      <td>Health</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0.676193</td>\n",
       "      <td>Humanities &amp; Liberal Arts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0.449351</td>\n",
       "      <td>Industrial Arts &amp; Consumer Services</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>0.495397</td>\n",
       "      <td>Interdisciplinary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>0.335990</td>\n",
       "      <td>Law &amp; Public Policy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>0.508749</td>\n",
       "      <td>Physical Sciences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>0.777763</td>\n",
       "      <td>Psychology &amp; Social Work</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>0.539067</td>\n",
       "      <td>Social Science</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Avg_Share_Women                       Major_category\n",
       "0          0.617938      Agriculture & Natural Resources\n",
       "1          0.561851                                 Arts\n",
       "2          0.584518               Biology & Life Science\n",
       "3          0.405063                             Business\n",
       "4          0.643835          Communications & Journalism\n",
       "5          0.512752              Computers & Mathematics\n",
       "6          0.674986                            Education\n",
       "7          0.257158                          Engineering\n",
       "8          0.616857                               Health\n",
       "9          0.676193            Humanities & Liberal Arts\n",
       "10         0.449351  Industrial Arts & Consumer Services\n",
       "11         0.495397                    Interdisciplinary\n",
       "12         0.335990                  Law & Public Policy\n",
       "13         0.508749                    Physical Sciences\n",
       "14         0.777763             Psychology & Social Work\n",
       "15         0.539067                       Social Science"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read(\"\"\"\n",
    "SELECT AVG(ShareWOmen) AS Avg_Share_Women ,Major_category FROM recent_grads \n",
    "GROUP BY Major_category \n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2d109bfe",
   "metadata": {},
   "source": [
    "### HAVING clause\n",
    "* SQLite HAVING clause is an optional clause of the SELECT statement. The HAVING clause specifies a search condition for a group\n",
    "* You often use the HAVING clause with the GROUP BY clause. The GROUP BY clause groups a set of rows into a set of summary rows or groups. Then the HAVING clause filters groups based on a specified condition\n",
    "* If you use the HAVING clause, you must include the GROUP BY clause; otherwise, you will get the error"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "910d9f6e",
   "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>Total ShareWomen</th>\n",
       "      <th>Major_category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6.179384</td>\n",
       "      <td>Agriculture &amp; Natural Resources</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8.183259</td>\n",
       "      <td>Biology &amp; Life Science</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5.265821</td>\n",
       "      <td>Business</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5.640272</td>\n",
       "      <td>Computers &amp; Mathematics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10.799768</td>\n",
       "      <td>Education</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7.457579</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7.402279</td>\n",
       "      <td>Health</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>10.142901</td>\n",
       "      <td>Humanities &amp; Liberal Arts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5.087494</td>\n",
       "      <td>Physical Sciences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>6.999868</td>\n",
       "      <td>Psychology &amp; Social Work</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Total ShareWomen                   Major_category\n",
       "0          6.179384  Agriculture & Natural Resources\n",
       "1          8.183259           Biology & Life Science\n",
       "2          5.265821                         Business\n",
       "3          5.640272          Computers & Mathematics\n",
       "4         10.799768                        Education\n",
       "5          7.457579                      Engineering\n",
       "6          7.402279                           Health\n",
       "7         10.142901        Humanities & Liberal Arts\n",
       "8          5.087494                Physical Sciences\n",
       "9          6.999868         Psychology & Social Work"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read(\"\"\"\n",
    "SELECT SUM(ShareWOmen) AS \"Total ShareWomen\" , Major_category FROM recent_grads \n",
    "GROUP BY Major_category\n",
    "HAVING \"Total ShareWomen\" > 5\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb265f99",
   "metadata": {},
   "source": [
    "### Comparison Operators\n",
    "* ==\tChecks if the values of two operands are equal or not, if yes then the condition becomes true\n",
    "* !=\tChecks if the values of two operands are equal or not, if the values are not equal, then the condition becomes true\n",
    "* <>\tChecks if the values of two operands are equal or not, if the values are not equal, then the condition becomes true.\n",
    "* etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "f4406d1f",
   "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>ShareWomen</th>\n",
       "      <th>Major</th>\n",
       "      <th>Major_category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.120564</td>\n",
       "      <td>PETROLEUM ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.101852</td>\n",
       "      <td>MINING AND MINERAL ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.153037</td>\n",
       "      <td>METALLURGICAL ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.107313</td>\n",
       "      <td>NAVAL ARCHITECTURE AND MARINE ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.341631</td>\n",
       "      <td>CHEMICAL ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.144967</td>\n",
       "      <td>NUCLEAR ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.441356</td>\n",
       "      <td>ASTRONOMY AND ASTROPHYSICS</td>\n",
       "      <td>Physical Sciences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0.139793</td>\n",
       "      <td>MECHANICAL ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.437847</td>\n",
       "      <td>ELECTRICAL ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0.199413</td>\n",
       "      <td>COMPUTER ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ShareWomen                                      Major     Major_category\n",
       "0    0.120564                      PETROLEUM ENGINEERING        Engineering\n",
       "1    0.101852             MINING AND MINERAL ENGINEERING        Engineering\n",
       "2    0.153037                  METALLURGICAL ENGINEERING        Engineering\n",
       "3    0.107313  NAVAL ARCHITECTURE AND MARINE ENGINEERING        Engineering\n",
       "4    0.341631                       CHEMICAL ENGINEERING        Engineering\n",
       "5    0.144967                        NUCLEAR ENGINEERING        Engineering\n",
       "6    0.441356                 ASTRONOMY AND ASTROPHYSICS  Physical Sciences\n",
       "7    0.139793                     MECHANICAL ENGINEERING        Engineering\n",
       "8    0.437847                     ELECTRICAL ENGINEERING        Engineering\n",
       "9    0.199413                       COMPUTER ENGINEERING        Engineering"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read(\"\"\"\n",
    "SELECT ShareWOmen, major, Major_category FROM recent_grads \n",
    "WHERE ShareWOmen < 0.5\n",
    "LIMIT 10\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "ea19f1e7",
   "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>ShareWomen</th>\n",
       "      <th>Major</th>\n",
       "      <th>Major_category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.120564</td>\n",
       "      <td>PETROLEUM ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.101852</td>\n",
       "      <td>MINING AND MINERAL ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.153037</td>\n",
       "      <td>METALLURGICAL ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.107313</td>\n",
       "      <td>NAVAL ARCHITECTURE AND MARINE ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.341631</td>\n",
       "      <td>CHEMICAL ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.144967</td>\n",
       "      <td>NUCLEAR ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.139793</td>\n",
       "      <td>MECHANICAL ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0.437847</td>\n",
       "      <td>ELECTRICAL ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.199413</td>\n",
       "      <td>COMPUTER ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0.196450</td>\n",
       "      <td>AEROSPACE ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ShareWomen                                      Major Major_category\n",
       "0    0.120564                      PETROLEUM ENGINEERING    Engineering\n",
       "1    0.101852             MINING AND MINERAL ENGINEERING    Engineering\n",
       "2    0.153037                  METALLURGICAL ENGINEERING    Engineering\n",
       "3    0.107313  NAVAL ARCHITECTURE AND MARINE ENGINEERING    Engineering\n",
       "4    0.341631                       CHEMICAL ENGINEERING    Engineering\n",
       "5    0.144967                        NUCLEAR ENGINEERING    Engineering\n",
       "6    0.139793                     MECHANICAL ENGINEERING    Engineering\n",
       "7    0.437847                     ELECTRICAL ENGINEERING    Engineering\n",
       "8    0.199413                       COMPUTER ENGINEERING    Engineering\n",
       "9    0.196450                      AEROSPACE ENGINEERING    Engineering"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read(\"\"\"\n",
    "SELECT ShareWOmen, major, Major_category FROM recent_grads \n",
    "WHERE Major_category == 'Engineering'\n",
    "LIMIT 10\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "00f80b92",
   "metadata": {},
   "source": [
    "### Logical Operators\n",
    "* AND - The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause\n",
    "* BETWEEN - The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value\n",
    "* IN - The IN operator is used to compare a value to a list of literal values that have been specified\n",
    "* OR - The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause\n",
    "* UNIQUE - The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates)\n",
    "* etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "4367b65f",
   "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>ShareWomen</th>\n",
       "      <th>Major</th>\n",
       "      <th>Major_category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.120564</td>\n",
       "      <td>PETROLEUM ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.101852</td>\n",
       "      <td>MINING AND MINERAL ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.153037</td>\n",
       "      <td>METALLURGICAL ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.107313</td>\n",
       "      <td>NAVAL ARCHITECTURE AND MARINE ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.341631</td>\n",
       "      <td>CHEMICAL ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>0.637293</td>\n",
       "      <td>ZOOLOGY</td>\n",
       "      <td>Biology &amp; Life Science</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>0.817099</td>\n",
       "      <td>EDUCATIONAL PSYCHOLOGY</td>\n",
       "      <td>Psychology &amp; Social Work</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>0.799859</td>\n",
       "      <td>CLINICAL PSYCHOLOGY</td>\n",
       "      <td>Psychology &amp; Social Work</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>0.798746</td>\n",
       "      <td>COUNSELING PSYCHOLOGY</td>\n",
       "      <td>Psychology &amp; Social Work</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>0.877960</td>\n",
       "      <td>LIBRARY SCIENCE</td>\n",
       "      <td>Education</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>124 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     ShareWomen                                      Major  \\\n",
       "0      0.120564                      PETROLEUM ENGINEERING   \n",
       "1      0.101852             MINING AND MINERAL ENGINEERING   \n",
       "2      0.153037                  METALLURGICAL ENGINEERING   \n",
       "3      0.107313  NAVAL ARCHITECTURE AND MARINE ENGINEERING   \n",
       "4      0.341631                       CHEMICAL ENGINEERING   \n",
       "..          ...                                        ...   \n",
       "119    0.637293                                    ZOOLOGY   \n",
       "120    0.817099                     EDUCATIONAL PSYCHOLOGY   \n",
       "121    0.799859                        CLINICAL PSYCHOLOGY   \n",
       "122    0.798746                      COUNSELING PSYCHOLOGY   \n",
       "123    0.877960                            LIBRARY SCIENCE   \n",
       "\n",
       "               Major_category  \n",
       "0                 Engineering  \n",
       "1                 Engineering  \n",
       "2                 Engineering  \n",
       "3                 Engineering  \n",
       "4                 Engineering  \n",
       "..                        ...  \n",
       "119    Biology & Life Science  \n",
       "120  Psychology & Social Work  \n",
       "121  Psychology & Social Work  \n",
       "122  Psychology & Social Work  \n",
       "123                 Education  \n",
       "\n",
       "[124 rows x 3 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read(\"\"\"\n",
    "SELECT ShareWOmen, major, Major_category FROM recent_grads \n",
    "WHERE ShareWOmen > 0.5 OR (ShareWOmen <= 0.5 AND Major_category == 'Engineering')\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a9bae703",
   "metadata": {},
   "source": [
    "### Arithmetic Operators\n",
    "* \"+\" - Adds the values present on both sides of the operator\n",
    "* \"–\" - Subtracts the right hand operand from left hand operand.\n",
    "* \"*\" - Multiplies the values of both sides.\n",
    "* \"/\" - Divides the left hand operand by right hand operand.\n",
    "* \"%\" - Divides the left hand operand by right hand operand and returns the remainder."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "3525e498",
   "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>ShareWomen</th>\n",
       "      <th>Men</th>\n",
       "      <th>Women</th>\n",
       "      <th>Total</th>\n",
       "      <th>wo_perc</th>\n",
       "      <th>men_perc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.120564</td>\n",
       "      <td>2057</td>\n",
       "      <td>282</td>\n",
       "      <td>2339</td>\n",
       "      <td>12.06</td>\n",
       "      <td>87.944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.101852</td>\n",
       "      <td>679</td>\n",
       "      <td>77</td>\n",
       "      <td>756</td>\n",
       "      <td>10.19</td>\n",
       "      <td>89.815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.153037</td>\n",
       "      <td>725</td>\n",
       "      <td>131</td>\n",
       "      <td>856</td>\n",
       "      <td>15.30</td>\n",
       "      <td>84.696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.107313</td>\n",
       "      <td>1123</td>\n",
       "      <td>135</td>\n",
       "      <td>1258</td>\n",
       "      <td>10.73</td>\n",
       "      <td>89.269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.341631</td>\n",
       "      <td>21239</td>\n",
       "      <td>11021</td>\n",
       "      <td>32260</td>\n",
       "      <td>34.16</td>\n",
       "      <td>65.837</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168</th>\n",
       "      <td>0.637293</td>\n",
       "      <td>3050</td>\n",
       "      <td>5359</td>\n",
       "      <td>8409</td>\n",
       "      <td>63.73</td>\n",
       "      <td>36.271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>0.817099</td>\n",
       "      <td>522</td>\n",
       "      <td>2332</td>\n",
       "      <td>2854</td>\n",
       "      <td>81.71</td>\n",
       "      <td>18.290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170</th>\n",
       "      <td>0.799859</td>\n",
       "      <td>568</td>\n",
       "      <td>2270</td>\n",
       "      <td>2838</td>\n",
       "      <td>79.99</td>\n",
       "      <td>20.014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>0.798746</td>\n",
       "      <td>931</td>\n",
       "      <td>3695</td>\n",
       "      <td>4626</td>\n",
       "      <td>79.87</td>\n",
       "      <td>20.125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>0.877960</td>\n",
       "      <td>134</td>\n",
       "      <td>964</td>\n",
       "      <td>1098</td>\n",
       "      <td>87.80</td>\n",
       "      <td>12.204</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>173 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     ShareWomen    Men  Women  Total  wo_perc  men_perc\n",
       "0      0.120564   2057    282   2339    12.06    87.944\n",
       "1      0.101852    679     77    756    10.19    89.815\n",
       "2      0.153037    725    131    856    15.30    84.696\n",
       "3      0.107313   1123    135   1258    10.73    89.269\n",
       "4      0.341631  21239  11021  32260    34.16    65.837\n",
       "..          ...    ...    ...    ...      ...       ...\n",
       "168    0.637293   3050   5359   8409    63.73    36.271\n",
       "169    0.817099    522   2332   2854    81.71    18.290\n",
       "170    0.799859    568   2270   2838    79.99    20.014\n",
       "171    0.798746    931   3695   4626    79.87    20.125\n",
       "172    0.877960    134    964   1098    87.80    12.204\n",
       "\n",
       "[173 rows x 6 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read(\"\"\"\n",
    "SELECT sharewomen, men, women, total, round((cast(women as float)/total)*100,2) wo_perc, \n",
    "round((cast(men as float)/total)*100, 3) men_perc FROM recent_grads\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0cb72334",
   "metadata": {},
   "source": [
    "### Aggregate functions\n",
    "* An aggregate function is a function that groups the values of numerous rows into a single summary value\n",
    "* SQLite provides us with many aggregate functions used for statistical analysis. \n",
    "* AVG (i.e., arithmetic mean), SUM, MAX, MIN, COUNT are common aggregation functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "335d8c1b",
   "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>AVG(sharewomen)</th>\n",
       "      <th>Major</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.968954</td>\n",
       "      <td>ANTHROPOLOGY AND ARCHEOLOGY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.967998</td>\n",
       "      <td>EARLY CHILDHOOD EDUCATION</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.927807</td>\n",
       "      <td>MATHEMATICS AND COMPUTER SCIENCE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.923745</td>\n",
       "      <td>ELEMENTARY EDUCATION</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.910933</td>\n",
       "      <td>ANIMAL SCIENCES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.906677</td>\n",
       "      <td>PHYSIOLOGY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.905590</td>\n",
       "      <td>MISCELLANEOUS PSYCHOLOGY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0.904075</td>\n",
       "      <td>HUMAN SERVICES AND COMMUNITY ORGANIZATION</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.896019</td>\n",
       "      <td>NURSING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0.881294</td>\n",
       "      <td>GEOSCIENCES</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   AVG(sharewomen)                                      Major\n",
       "0         0.968954                ANTHROPOLOGY AND ARCHEOLOGY\n",
       "1         0.967998                  EARLY CHILDHOOD EDUCATION\n",
       "2         0.927807           MATHEMATICS AND COMPUTER SCIENCE\n",
       "3         0.923745                       ELEMENTARY EDUCATION\n",
       "4         0.910933                            ANIMAL SCIENCES\n",
       "5         0.906677                                 PHYSIOLOGY\n",
       "6         0.905590                   MISCELLANEOUS PSYCHOLOGY\n",
       "7         0.904075  HUMAN SERVICES AND COMMUNITY ORGANIZATION\n",
       "8         0.896019                                    NURSING\n",
       "9         0.881294                                GEOSCIENCES"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read(\"\"\"\n",
    "SELECT AVG(sharewomen),major FROM recent_grads\n",
    "GROUP BY major\n",
    "HAVING AVG(sharewomen) > 0.5\n",
    "ORDER BY AVG(sharewomen) DESC \n",
    "LIMIT 10\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0ead50d",
   "metadata": {},
   "source": [
    "### Subquery\n",
    "* A subquery is a SELECT statement nested in another statement\n",
    "* You must use a pair of parentheses to enclose a subquery\n",
    "* Typically, a subquery returns a single row as an atomic value, though it may return multiple rows for comparing values with the IN operator\n",
    "* You can use a subquery in the SELECT, FROM, WHERE, and JOIN clauses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "2ebdd08e",
   "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>Major</th>\n",
       "      <th>Major_category</th>\n",
       "      <th>ShareWomen</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ACTUARIAL SCIENCE</td>\n",
       "      <td>Business</td>\n",
       "      <td>0.535714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>COMPUTER SCIENCE</td>\n",
       "      <td>Computers &amp; Mathematics</td>\n",
       "      <td>0.578766</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ENVIRONMENTAL ENGINEERING</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>0.558548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NURSING</td>\n",
       "      <td>Health</td>\n",
       "      <td>0.896019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>INDUSTRIAL PRODUCTION TECHNOLOGIES</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>0.750473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>ZOOLOGY</td>\n",
       "      <td>Biology &amp; Life Science</td>\n",
       "      <td>0.637293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>EDUCATIONAL PSYCHOLOGY</td>\n",
       "      <td>Psychology &amp; Social Work</td>\n",
       "      <td>0.817099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>CLINICAL PSYCHOLOGY</td>\n",
       "      <td>Psychology &amp; Social Work</td>\n",
       "      <td>0.799859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>COUNSELING PSYCHOLOGY</td>\n",
       "      <td>Psychology &amp; Social Work</td>\n",
       "      <td>0.798746</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>LIBRARY SCIENCE</td>\n",
       "      <td>Education</td>\n",
       "      <td>0.877960</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>91 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 Major            Major_category  ShareWomen\n",
       "0                    ACTUARIAL SCIENCE                  Business    0.535714\n",
       "1                     COMPUTER SCIENCE   Computers & Mathematics    0.578766\n",
       "2            ENVIRONMENTAL ENGINEERING               Engineering    0.558548\n",
       "3                              NURSING                    Health    0.896019\n",
       "4   INDUSTRIAL PRODUCTION TECHNOLOGIES               Engineering    0.750473\n",
       "..                                 ...                       ...         ...\n",
       "86                             ZOOLOGY    Biology & Life Science    0.637293\n",
       "87              EDUCATIONAL PSYCHOLOGY  Psychology & Social Work    0.817099\n",
       "88                 CLINICAL PSYCHOLOGY  Psychology & Social Work    0.799859\n",
       "89               COUNSELING PSYCHOLOGY  Psychology & Social Work    0.798746\n",
       "90                     LIBRARY SCIENCE                 Education    0.877960\n",
       "\n",
       "[91 rows x 3 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read(\"\"\"\n",
    "    SELECT major, major_category, sharewomen FROM recent_grads\n",
    "    WHERE sharewomen > (SELECT avg(sharewomen) FROM recent_grads)\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "eefb7e4e",
   "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>COUNT(sharewomen)</th>\n",
       "      <th>(SELECT COUNT(*) FROM recent_grads)</th>\n",
       "      <th>COUNT(sharewomen)/CAST((SELECT COUNT(*) FROM recent_grads) AS FLOAT)*100</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>91</td>\n",
       "      <td>173</td>\n",
       "      <td>52.601156</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   COUNT(sharewomen)  (SELECT COUNT(*) FROM recent_grads)  \\\n",
       "0                 91                                  173   \n",
       "\n",
       "   COUNT(sharewomen)/CAST((SELECT COUNT(*) FROM recent_grads) AS FLOAT)*100  \n",
       "0                                          52.601156                         "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read(\"\"\"\n",
    "    SELECT \n",
    "        COUNT(sharewomen), (SELECT COUNT(*) FROM recent_grads),\n",
    "        COUNT(sharewomen)/CAST((SELECT COUNT(*) FROM recent_grads) AS FLOAT)*100 FROM recent_grads\n",
    "    WHERE \n",
    "        sharewomen > (SELECT AVG(sharewomen) FROM recent_grads)\n",
    "\"\"\")"
   ]
  }
 ],
 "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}