lab3.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 289,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Connected: @lab3-1.db3'"
      ]
     },
     "execution_count": 289,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%load_ext sql\n",
    "%sql sqlite:///lab3-1.db3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Q1: In the score table, find the student number that has all the grades in between 90 and 70."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 290,
   "metadata": {},
   "outputs": [],
   "source": [
    "# RETURNS SNO WHERE GRADES ARE BETWEEN 90 AND 70, INCLUSIVE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 291,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///lab3-1.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>sno</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>103</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>105</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>105</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>109</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>108</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>101</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>107</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>108</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('103',),\n",
       " ('105',),\n",
       " ('105',),\n",
       " ('109',),\n",
       " ('108',),\n",
       " ('101',),\n",
       " ('107',),\n",
       " ('108',)]"
      ]
     },
     "execution_count": 291,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT sno\n",
    "FROM scores\n",
    "WHERE (grade <= 90) AND (grade >= 70)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Q2: For all the courses that took by class 15033, calculate the average grade."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 292,
   "metadata": {},
   "outputs": [],
   "source": [
    "# RETURNS THE AVERAGE GRADE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 293,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///lab3-1.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>avg(grade)</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>79.66666666666667</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(79.66666666666667,)]"
      ]
     },
     "execution_count": 293,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT avg(grade)\n",
    "FROM scores, students\n",
    "WHERE students.class = 15033 AND students.sno = scores.sno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Q3: Find the class number that has at least two male students."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 294,
   "metadata": {},
   "outputs": [],
   "source": [
    "# RETURN THE CLASS NUMBER"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 295,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///lab3-1.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>class</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>15031</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>15033</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('15031',), ('15033',)]"
      ]
     },
     "execution_count": 295,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT s.class\n",
    "FROM students s \n",
    "WHERE s.sgender = 'M'\n",
    "GROUP BY s.class HAVING COUNT(*) >= 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Q4: Find the teacher's name in CS and EE department, where they have different title. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 296,
   "metadata": {},
   "outputs": [],
   "source": [
    "# RETURN BOTH NAME AND TITLE\n",
    "# FROM THE DATA PROVIDED, THERE IS NO TEACHER THAT FITS THE REQ."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 297,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///lab3-1.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>tname</th>\n",
       "            <th>title</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 297,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT tname, title\n",
    "FROM ( \n",
    "        SELECT *\n",
    "        FROM teachers\n",
    "        WHERE department = 'CS') NATURAL JOIN ( \n",
    "            SELECT tno, title as title2\n",
    "            FROM teachers\n",
    "            WHERE department = 'EE')\n",
    "WHERE title != title2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Q5: Find the students, who took the course number \"3-105\" and have earned a grade, at least, higher than the students who took \"3-245\" course."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 298,
   "metadata": {},
   "outputs": [],
   "source": [
    "# RETURNS SNO (PRIMARY KEY), STUDENT NAME, AND GRADE IN DESCENDING ORDER OF GRADE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 299,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///lab3-1.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>sno</th>\n",
       "            <th>sname</th>\n",
       "            <th>grade</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>103</td>\n",
       "            <td>Quincy</td>\n",
       "            <td>92</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>107</td>\n",
       "            <td>Lily</td>\n",
       "            <td>91</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>105</td>\n",
       "            <td>Gary</td>\n",
       "            <td>88</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('103', 'Quincy', 92), ('107', 'Lily', 91), ('105', 'Gary', 88)]"
      ]
     },
     "execution_count": 299,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT sno, sname, grade\n",
    "FROM scores natural join students\n",
    "WHERE  cno = '3-105' AND grade > (\n",
    "                                     SELECT max(grade)\n",
    "                                     FROM scores\n",
    "                                     WHERE cno = '3-245' )\n",
    "ORDER BY grade DESC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Q6: Find the students, who took more than 1 course, and return the students' names that is not the one with highest grade."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 300,
   "metadata": {},
   "outputs": [],
   "source": [
    "# RETURNS STUDENT NAMES WHO TOOK MORE THAN 1 COURSE\n",
    "# EXCEPT THE STUDENT WHO HAD THE HIGHEST GRADE\n",
    "# IN THIS DB, SNO = 103 HAD THE HIGHEST SCORE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 301,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///lab3-1.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>sno</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>101</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>105</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>107</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>108</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>109</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('101',), ('105',), ('107',), ('108',), ('109',)]"
      ]
     },
     "execution_count": 301,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT s.sno\n",
    "FROM scores s, students t\n",
    "WHERE t.sno = s.sno\n",
    "GROUP BY s.sno HAVING count(*)>1 AND s.grade <>( SELECT max(grade) \n",
    "                                                 FROM scores )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Q7: For each course, find the students who earned a grade less than the average grade of this course."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 302,
   "metadata": {},
   "outputs": [],
   "source": [
    "# RETURNS SNO OF STUDENT\n",
    "# CREATE NESTED LIST TO FIND GRADE LESS THAN AVERAGE\n",
    "# THEN GROUP BY CNO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 303,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///lab3-1.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>sno</th>\n",
       "            <th>cno</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>109</td>\n",
       "            <td>3-105</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>105</td>\n",
       "            <td>3-245</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>107</td>\n",
       "            <td>6-106</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>108</td>\n",
       "            <td>6-166</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('109', '3-105'), ('105', '3-245'), ('107', '6-106'), ('108', '6-166')]"
      ]
     },
     "execution_count": 303,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT sno, cno\n",
    "FROM scores\n",
    "WHERE grade < ( SELECT avg(grade)\n",
    "                FROM scores\n",
    "                GROUP BY cno )\n",
    "GROUP BY cno\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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}