server/notebooks/sql-practice-medium-practice.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f0554831",
   "metadata": {},
   "source": [
    "# Notes\n",
    "\n",
    "You might want to check https://julkaar9.github.io/walkalongs/orms/django/w3school/ to get  \n",
    "basic an idea on how to solve these questions.  \n",
    "\n",
    "Make sure to check the column names and foreign key relationships from server/customer_db/hospital_models.py.\n",
    "\n",
    "Use `sql_to_df` to print the sql query results.  \n",
    "\n",
    "Some insights on the equal function. \n",
    "1. Proper type casts might be required to get an equal result\n",
    "2. Exact ordering of columns is not required for query results to be equal\n",
    "3. Exact ordering of rows / tuples however are required."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "079dd83c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "os.environ[\"DJANGO_ALLOW_ASYNC_UNSAFE\"] = \"true\"\n",
    "from utils import *\n",
    "from sqlparse import format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "adb90216",
   "metadata": {},
   "outputs": [],
   "source": [
    "from django.db.models import (\n",
    "    Q,\n",
    "    F,\n",
    "    Case,\n",
    "    When,\n",
    "    Count,\n",
    "    Func,\n",
    "    Min,\n",
    "    Max,\n",
    "    Sum,\n",
    "    Avg,\n",
    "    Value,\n",
    "    OuterRef,\n",
    "    Subquery,\n",
    "    CharField,\n",
    ")\n",
    "from django.db.models.functions import Concat, Cast, Round, Length, Upper, Lower\n",
    "from django.db import connection\n",
    "from customer_db.models import Provinces, Patients, Doctors, Admissions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "47b65247",
   "metadata": {},
   "outputs": [],
   "source": [
    "def print_sql(queryset):\n",
    "    formatted = format(str(queryset.query), reindent=True)\n",
    "    print(formatted)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a979da30",
   "metadata": {},
   "outputs": [],
   "source": [
    "admission_fields = [\n",
    "    \"id\",\n",
    "    \"patient_id\",\n",
    "    \"admission_date\",\n",
    "    \"discharge_date\",\n",
    "    \"diagnosis\",\n",
    "    \"attending_doctor_id\",\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "ff151cc6",
   "metadata": {},
   "outputs": [],
   "source": [
    "class NonAggrCount(Count):\n",
    "    \"\"\"\n",
    "    This aggregation will not trigger a group by\n",
    "    \"\"\"\n",
    "\n",
    "    contains_aggregate = False\n",
    "\n",
    "\n",
    "class NonAggrSum(Sum):\n",
    "    contains_aggregate = False"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "290efc84",
   "metadata": {},
   "source": [
    "# Example \n",
    "Show first_name of patient and admission date from admission table. Order by first_name and admission_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1fb37e1d",
   "metadata": {},
   "outputs": [],
   "source": [
    "qstr = \"\"\"\n",
    "SELECT\n",
    "  p.first_name,\n",
    "  admission_date\n",
    "FROM admissions a\n",
    "JOIN patients p on p.patient_id = a.patient_id\n",
    "ORDER By p.first_name, admission_date\n",
    "\"\"\"\n",
    "sqlq = sql_raw(qstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "835ae553",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Equal ✔️\n",
      "SELECT \"admissions\".\"admission_date\",\n",
      "       \"patients\".\"first_name\" AS \"first_name\"\n",
      "FROM \"admissions\"\n",
      "INNER JOIN \"patients\" ON (\"admissions\".\"patient_id\" = \"patients\".\"patient_id\")\n",
      "ORDER BY 2 ASC,\n",
      "         \"admissions\".\"admission_date\" ASC\n"
     ]
    }
   ],
   "source": [
    "ormq = Admissions.objects.values(\n",
    "    \"admission_date\", first_name=F(\"patient__first_name\")\n",
    ").order_by(\"first_name\", \"admission_date\")\n",
    "equal(sqlq, ormq)\n",
    "print_sql(ormq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "2deb7fa7",
   "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>admission_date</th>\n",
       "      <th>first_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-02-18</td>\n",
       "      <td>Abigail</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-05-28</td>\n",
       "      <td>Abigail</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-01-08</td>\n",
       "      <td>Adam</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  admission_date first_name\n",
       "0     2022-02-18    Abigail\n",
       "1     2022-05-28    Abigail\n",
       "2     2022-01-08       Adam"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orm_to_df(ormq[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e25677c",
   "metadata": {},
   "source": [
    "## Medium 1\n",
    "Show unique birth years from patients and order them by ascending."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a26aaf08",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use YEAR(birth_date) for most databases\n",
    "qstr = \"\"\"\n",
    "SELECT\n",
    "  DISTINCT strftime('%Y', birth_date) birth_year\n",
    "FROM patients\n",
    "ORDER BY birth_year;\n",
    "\"\"\"\n",
    "sqlq = sql_raw(qstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "fa0745da",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unequal ❌\n",
      "SELECT \"patients\".\"patient_id\"\n",
      "FROM \"patients\"\n"
     ]
    }
   ],
   "source": [
    "ormq = Patients.objects.values(\"id\")\n",
    "equal(sqlq, ormq)\n",
    "print_sql(ormq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "1b640d15",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orm_to_df(ormq[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05167feb",
   "metadata": {},
   "source": [
    "## Medium 2\n",
    "Show unique first names from the patients table which only occurs once in the list.\n",
    "\n",
    "For example, if two or more people are named 'John' in the first_name column then don't include their name in the output list. If only 1 person is named 'Leo' then include them in the output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "f3d46825",
   "metadata": {},
   "outputs": [],
   "source": [
    "qstr = \"\"\"\n",
    "SELECT first_name\n",
    "FROM patients\n",
    "GROUP BY first_name\n",
    "HAVING COUNT(first_name) = 1\n",
    "\"\"\"\n",
    "sqlq = sql_raw(qstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "771ad168",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unequal ❌\n",
      "SELECT \"patients\".\"patient_id\"\n",
      "FROM \"patients\"\n"
     ]
    }
   ],
   "source": [
    "ormq = Patients.objects.values(\"id\")\n",
    "equal(sqlq, ormq)\n",
    "print_sql(ormq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "38223bf4",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orm_to_df(ormq[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "198bcd91",
   "metadata": {},
   "source": [
    "## Medium 3\n",
    "Show patient_id and first_name from patients where their first_name start and ends with 's' and is at least 6 characters long.\n",
    "We will do start and ends with 'n' and is at least 6 characters long."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "c8b3ece0",
   "metadata": {},
   "outputs": [],
   "source": [
    "qstr = \"\"\"\n",
    "SELECT\n",
    "  patient_id,\n",
    "  first_name\n",
    "FROM patients\n",
    "WHERE first_name LIKe \"n____%n\"\n",
    "\"\"\"\n",
    "sqlq = sql_raw(qstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "45d84a66",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unequal ❌\n",
      "SELECT \"patients\".\"patient_id\"\n",
      "FROM \"patients\"\n"
     ]
    }
   ],
   "source": [
    "ormq = Patients.objects.values(\"id\")\n",
    "equal(sqlq, ormq)\n",
    "print_sql(ormq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "f14125d8",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orm_to_df(ormq[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54667979",
   "metadata": {},
   "source": [
    "## Medium 4\n",
    "Show patient_id, first_name, last_name from patients whos diagnosis is 'Dementia'.\n",
    "We will do Diabetes.\n",
    "Primary diagnosis is stored in the admissions table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "ffcf38de",
   "metadata": {},
   "outputs": [],
   "source": [
    "qstr = \"\"\"\n",
    "SELECT\n",
    "  p.patient_id,\n",
    "  first_name,\n",
    "  last_name\n",
    "FROM patients p\n",
    "  INNER JOIN admissions a ON p.patient_id = a.patient_id\n",
    "WHERE diagnosis = 'Diabetes'\n",
    "\"\"\"\n",
    "sqlq = sql_raw(qstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "8891900d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unequal ❌\n",
      "SELECT \"patients\".\"patient_id\"\n",
      "FROM \"patients\"\n"
     ]
    }
   ],
   "source": [
    "ormq = Patients.objects.values(\"id\")\n",
    "equal(sqlq, ormq)\n",
    "print_sql(ormq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "9fcbee81",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orm_to_df(ormq[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e78bd24",
   "metadata": {},
   "source": [
    "## Medium 5\n",
    "Display every patient's first_name.\n",
    "Order the list by the length of each name and then by alphbetically"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "75fd0db8",
   "metadata": {},
   "outputs": [],
   "source": [
    "qstr = \"\"\"\n",
    "SELECT\n",
    "  first_name\n",
    "FROM patients p\n",
    "ORDER BY\n",
    "  LENGTH(first_name),\n",
    "  first_name;\n",
    "\"\"\"\n",
    "sqlq = sql_raw(qstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "737b0a09",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unequal ❌\n",
      "SELECT \"patients\".\"patient_id\"\n",
      "FROM \"patients\"\n"
     ]
    }
   ],
   "source": [
    "ormq = Patients.objects.values(\"id\")\n",
    "equal(sqlq, ormq)\n",
    "print_sql(ormq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "74a5ae47",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orm_to_df(ormq[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "152fd1d2",
   "metadata": {},
   "source": [
    "## Medium 6\n",
    "Show the total amount of male patients and the total amount of female patients in the patients table.\n",
    "Display the two results in the same row."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "8ac00c72",
   "metadata": {},
   "outputs": [],
   "source": [
    "qstr = \"\"\"\n",
    "SELECT (\n",
    "    SELECT COUNT(gender)\n",
    "    FROM patients\n",
    "    WHERE gender = 'M'\n",
    "  ) AS male, (\n",
    "    SELECT COUNT(gender)\n",
    "    FROM patients\n",
    "    WHERE gender = 'F'\n",
    "  ) AS female;\n",
    "\"\"\"\n",
    "sqlq = sql_raw(qstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "df843675",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unequal ❌\n",
      "SELECT \"patients\".\"patient_id\"\n",
      "FROM \"patients\"\n"
     ]
    }
   ],
   "source": [
    "ormq = Patients.objects.values(\"id\")\n",
    "equal(sqlq, ormq)\n",
    "print_sql(ormq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "192b2ae2",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orm_to_df(ormq[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0a262c83",
   "metadata": {},
   "source": [
    "## Medium 7\n",
    "Show first and last name, allergies from patients which have allergies to either 'Penicillin' or 'Morphine'. Show results ordered ascending by allergies then by first_name then by last_name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "843ceb1c",
   "metadata": {},
   "outputs": [],
   "source": [
    "qstr = \"\"\"\n",
    "SELECT\n",
    "  first_name,\n",
    "  last_name,\n",
    "  allergies\n",
    "FROM patients\n",
    "WHERE\n",
    "  allergies IN ('Penicillin', 'Morphine')\n",
    "ORDER BY\n",
    "  allergies,\n",
    "  first_name,\n",
    "  last_name;\n",
    "\"\"\"\n",
    "sqlq = sql_raw(qstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "b092986f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unequal ❌\n",
      "SELECT \"patients\".\"patient_id\"\n",
      "FROM \"patients\"\n"
     ]
    }
   ],
   "source": [
    "ormq = Patients.objects.values(\"id\")\n",
    "equal(sqlq, ormq)\n",
    "print_sql(ormq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "68972556",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orm_to_df(ormq[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93f3d23c",
   "metadata": {},
   "source": [
    "## Medium 8\n",
    "Show patient_id, diagnosis from admissions. Find patients admitted multiple times for the same diagnosis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "a54b2922",
   "metadata": {},
   "outputs": [],
   "source": [
    "qstr = \"\"\"\n",
    "SELECT\n",
    "  patient_id,\n",
    "  diagnosis\n",
    "FROM admissions\n",
    "GROUP BY\n",
    "  patient_id,\n",
    "  diagnosis\n",
    "HAVING COUNT(*) > 1\n",
    "\"\"\"\n",
    "sqlq = sql_raw(qstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "dca7fb3b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unequal ❌\n",
      "SELECT \"patients\".\"patient_id\"\n",
      "FROM \"patients\"\n"
     ]
    }
   ],
   "source": [
    "ormq = Patients.objects.values(\"id\")\n",
    "equal(sqlq, ormq)\n",
    "print_sql(ormq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "e748f9be",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orm_to_df(ormq[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "89b0d469",
   "metadata": {},
   "source": [
    "## Medium 9\n",
    "Show the city and the total number of patients in the city.\n",
    "Order from most to least patients and then by city name ascending."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "e13fa348",
   "metadata": {},
   "outputs": [],
   "source": [
    "qstr = \"\"\"\n",
    "SELECT\n",
    "  city,\n",
    "  COUNT(*) population\n",
    "FROM patients\n",
    "GROUP BY city \n",
    "ORDER BY population DESC, city\n",
    "\"\"\"\n",
    "sqlq = sql_raw(qstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "5ee71798",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unequal ❌\n",
      "SELECT \"patients\".\"patient_id\"\n",
      "FROM \"patients\"\n"
     ]
    }
   ],
   "source": [
    "ormq = Patients.objects.values(\"id\")\n",
    "equal(sqlq, ormq)\n",
    "print_sql(ormq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "66e7bf57",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orm_to_df(ormq[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8f4b8597",
   "metadata": {},
   "source": [
    "## Medium 10\n",
    "Show first name, last name and role of every person that is either patient or doctor.\n",
    "The roles are either \"Patient\" or \"Doctor\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "d58c01cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "qstr = \"\"\"\n",
    "SELECT\n",
    "  first_name,\n",
    "  last_name,\n",
    "  'patient' AS Role\n",
    "FROM patients\n",
    "UNION ALL\n",
    "SELECT\n",
    "  first_name,\n",
    "  last_name,\n",
    "  'doctor' AS Role\n",
    "FROM doctors;\n",
    "\"\"\"\n",
    "sqlq = sql_raw(qstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "9f8d606f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unequal ❌\n",
      "SELECT \"patients\".\"patient_id\"\n",
      "FROM \"patients\"\n"
     ]
    }
   ],
   "source": [
    "ormq = Patients.objects.values(\"id\")\n",
    "equal(sqlq, ormq)\n",
    "print_sql(ormq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "868f4e55",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orm_to_df(ormq[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "938cf813",
   "metadata": {},
   "source": [
    "## Medium 11\n",
    "Show all allergies and their occurance ordered by occurance. Remove NULL values from query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "121e4b3c",
   "metadata": {},
   "outputs": [],
   "source": [
    "qstr = \"\"\"\n",
    "SELECT\n",
    "  allergies,\n",
    "  COUNT (*) AS total_occurance\n",
    "FROM patients\n",
    "WHERE allergies IS NOT null\n",
    "GROUP BY allergies\n",
    "ORDER BY total_occurance DESC\n",
    "\"\"\"\n",
    "sqlq = sql_raw(qstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "186f50c6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unequal ❌\n",
      "SELECT \"patients\".\"patient_id\"\n",
      "FROM \"patients\"\n"
     ]
    }
   ],
   "source": [
    "ormq = Patients.objects.values(\"id\")\n",
    "equal(sqlq, ormq)\n",
    "print_sql(ormq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "c7610449",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orm_to_df(ormq[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "90aab2c9",
   "metadata": {},
   "source": [
    "## Medium 12\n",
    "Show all patient's first_name, last_name, and birth_date who were born in the 1970s decade. Sort the list starting from the earliest birth_date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "c8ede5a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# use YEAR(birth_date) if year function is available\n",
    "qstr = \"\"\"\n",
    "SELECT\n",
    "  first_name,\n",
    "  last_name,\n",
    "  birth_date\n",
    "FROM patients\n",
    "WHERE CAST(strftime('%Y', birth_date) AS INT) BETWEEN 1970 AND 1979\n",
    "ORDER BY birth_date\n",
    "\"\"\"\n",
    "sqlq = sql_raw(qstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "ba06606d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unequal ❌\n",
      "SELECT \"patients\".\"patient_id\"\n",
      "FROM \"patients\"\n"
     ]
    }
   ],
   "source": [
    "ormq = Patients.objects.values(\"id\")\n",
    "equal(sqlq, ormq)\n",
    "print_sql(ormq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "00214390",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orm_to_df(ormq[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0a17a415",
   "metadata": {},
   "source": [
    "## Medium 13\n",
    "We want to display each patient's full name in a single column. Their last_name in all upper letters must appear first, then first_name in all lower case letters. Separate the last_name and first_name with a comma. Order the list by the first_name in decending order\n",
    "EX: SMITH,jane"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "580934ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "qstr = \"\"\"\n",
    "SELECT\n",
    "   UPPER(last_name) || ',' || LOWER(first_name) AS full_name\n",
    "FROM patients\n",
    "ORDER BY first_name DESC\n",
    "\"\"\"\n",
    "sqlq = sql_raw(qstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "01802e85",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unequal ❌\n",
      "SELECT \"patients\".\"patient_id\"\n",
      "FROM \"patients\"\n"
     ]
    }
   ],
   "source": [
    "ormq = Patients.objects.values(\"id\")\n",
    "equal(sqlq, ormq)\n",
    "print_sql(ormq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "bd311cbc",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orm_to_df(ormq[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bbd85fc9",
   "metadata": {},
   "source": [
    "## Medium 14\n",
    "Show the province_id(s), sum of height; where the total sum of its patient's height is greater than or equal to 7,000.\n",
    "We will do 3000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "e5983c5e",
   "metadata": {},
   "outputs": [],
   "source": [
    "qstr = \"\"\"\n",
    "SELECT\n",
    "  province_id,\n",
    "  SUM(height) AS sum_height\n",
    "FROM patients\n",
    "GROUP BY province_id\n",
    "HAVING SUM(height) > 3000;\n",
    "\"\"\"\n",
    "sqlq = sql_raw(qstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "0024cf3c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unequal ❌\n",
      "SELECT \"patients\".\"patient_id\"\n",
      "FROM \"patients\"\n"
     ]
    }
   ],
   "source": [
    "ormq = Patients.objects.values(\"id\")\n",
    "equal(sqlq, ormq)\n",
    "print_sql(ormq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "880d8f10",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orm_to_df(ormq[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "406a5aff",
   "metadata": {},
   "source": [
    "## Medium 15\n",
    "Show the difference between the largest weight and smallest weight for patients with the last name 'Maroni'.\n",
    "We will do Lee."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "9edf66ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "qstr = \"\"\"\n",
    "SELECT\n",
    "  (MAX(weight) - MIN(weight)) AS weight_dif\n",
    "FROM patients\n",
    "WHERE last_name = 'Lee';\n",
    "\"\"\"\n",
    "sqlq = sql_raw(qstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "9530aafb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unequal ❌\n"
     ]
    }
   ],
   "source": [
    "ormq = Patients.objects.values(\"id\")\n",
    "equal(sqlq, ormq)\n",
    "# print_sql(ormq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "cb316417",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orm_to_df(ormq[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b8af818e",
   "metadata": {},
   "source": [
    "## Medium 16\n",
    "Show all of the days of the month (1-31) and how many admission_dates occurred on that day. Sort by the day with most admissions to least admissions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "5fc4a254",
   "metadata": {},
   "outputs": [],
   "source": [
    "qstr = \"\"\"\n",
    "SELECT\n",
    "  CAST(strftime('%d', admission_date) as INT) AS month_day,\n",
    "  COUNT(patient_id) AS daily_admission\n",
    "FROM admissions\n",
    "GROUP BY strftime('%d', admission_date)\n",
    "ORDER BY daily_admission DESC;\n",
    "\"\"\"\n",
    "sqlq = sql_raw(qstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "413c2377",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unequal ❌\n",
      "SELECT \"patients\".\"patient_id\"\n",
      "FROM \"patients\"\n"
     ]
    }
   ],
   "source": [
    "ormq = Patients.objects.values(\"id\")\n",
    "equal(sqlq, ormq)\n",
    "print_sql(ormq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "fadb1189",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orm_to_df(ormq[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "537e6f3f",
   "metadata": {},
   "source": [
    "## Medium 17\n",
    "Show all columns for patient_id 54's most recent admission_date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "89479323",
   "metadata": {},
   "outputs": [],
   "source": [
    "qstr = \"\"\"\n",
    "SELECT *\n",
    "FROM admissions\n",
    "WHERE patient_id = 54 AND admission_date = (\n",
    "    SELECT max(admission_date)\n",
    "    FROM admissions\n",
    "    WHERE patient_id = 54\n",
    "  )\n",
    "\"\"\"\n",
    "sqlq = sql_raw(qstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "763f14cc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unequal ❌\n",
      "SELECT \"patients\".\"patient_id\"\n",
      "FROM \"patients\"\n"
     ]
    }
   ],
   "source": [
    "ormq = Patients.objects.values(\"id\")\n",
    "equal(sqlq, ormq)\n",
    "print_sql(ormq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "a9be8b75",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orm_to_df(ormq[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44afd0cc",
   "metadata": {},
   "source": [
    "## Medium 18\n",
    "Show patient_id, attending_doctor_id, and diagnosis for admissions that match one of the two criteria:\n",
    "1. patient_id is an odd number and attending_doctor_id is either 1, 5, or 19.\n",
    "2. attending_doctor_id contains a 2 and the length of patient_id is 3 characters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "19aa8ae6",
   "metadata": {},
   "outputs": [],
   "source": [
    "qstr = \"\"\"\n",
    "SELECT\n",
    "  patient_id,\n",
    "  attending_doctor_id,\n",
    "  diagnosis\n",
    "FROM admissions\n",
    "WHERE\n",
    "  (\n",
    "    patient_id % 2 = 1\n",
    "    and attending_doctor_id IN (1, 5, 19)\n",
    "  )\n",
    "  OR (\n",
    "    CAST(attending_doctor_id AS varchar) LIKE \"%2%\"\n",
    "    AND length(CAST(patient_id AS VARCHAR)) = 2\n",
    "  )\n",
    "\"\"\"\n",
    "sqlq = sql_raw(qstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "cf8fcaa7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unequal ❌\n",
      "SELECT \"patients\".\"patient_id\"\n",
      "FROM \"patients\"\n"
     ]
    }
   ],
   "source": [
    "ormq = Patients.objects.values(\"id\")\n",
    "equal(sqlq, ormq)\n",
    "print_sql(ormq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "f9a79ab2",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orm_to_df(ormq[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "810a8d57",
   "metadata": {},
   "source": [
    "## Medium 19\n",
    "Show first_name, last_name, and the total number of admissions attended for each doctor.\n",
    "\n",
    "Every admission has been attended by a doctor."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "19a13ae6",
   "metadata": {},
   "outputs": [],
   "source": [
    "qstr = \"\"\"\n",
    "SELECT\n",
    "  first_name,\n",
    "  last_name,\n",
    "  COUNT(*) AS patient_count\n",
    "FROM admissions\n",
    "  JOIN doctors ON attending_doctor_id = doctor_id\n",
    "GROUP BY attending_doctor_id\n",
    "\"\"\"\n",
    "sqlq = sql_raw(qstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "56ef8391",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unequal ❌\n",
      "SELECT \"patients\".\"patient_id\"\n",
      "FROM \"patients\"\n"
     ]
    }
   ],
   "source": [
    "ormq = Patients.objects.values(\"id\")\n",
    "equal(sqlq, ormq)\n",
    "print_sql(ormq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "5c5f3789",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orm_to_df(ormq[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "229aea03",
   "metadata": {},
   "source": [
    "## Medium 20\n",
    "For each doctor, display their id, full name, and the first and last admission date they attended."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "70ff9851",
   "metadata": {},
   "outputs": [],
   "source": [
    "qstr = \"\"\"\n",
    "SELECT\n",
    "  doctor_id,\n",
    "  first_name || ' ' || last_name AS full_name,\n",
    "  MIN(admission_date) AS min_admission,\n",
    "  MAX(admission_date) AS max_admission\n",
    "FROM doctors\n",
    "  JOIN admissions ON attending_doctor_id = doctor_id\n",
    "GROUP BY doctor_id\n",
    "\"\"\"\n",
    "sqlq = sql_raw(qstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "5a6664a5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unequal ❌\n",
      "SELECT \"patients\".\"patient_id\"\n",
      "FROM \"patients\"\n"
     ]
    }
   ],
   "source": [
    "ormq = Patients.objects.values(\"id\")\n",
    "equal(sqlq, ormq)\n",
    "print_sql(ormq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "b40c7991",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orm_to_df(ormq[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c2d35cf4",
   "metadata": {},
   "source": [
    "## Medium 21\n",
    "Display the total amount of patients for each province. Order by descending."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "ad0361e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "qstr = \"\"\"\n",
    "SELECT\n",
    "  pn.province_name,\n",
    "  count(patient_id) population\n",
    "FROM patients p\n",
    "  JOIN province_names pn ON p.province_id = pn.province_id\n",
    "GROUP BY pn.province_id\n",
    "ORDER BY population desc\n",
    "\"\"\"\n",
    "sqlq = sql_raw(qstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "9508b391",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unequal ❌\n",
      "SELECT \"patients\".\"patient_id\"\n",
      "FROM \"patients\"\n"
     ]
    }
   ],
   "source": [
    "ormq = Patients.objects.values(\"id\")\n",
    "equal(sqlq, ormq)\n",
    "print_sql(ormq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "b6dd0ba1",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orm_to_df(ormq[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18217c39",
   "metadata": {},
   "source": [
    "## Medium 22\n",
    "For every admission, display the patient's full name, their admission diagnosis, and their doctor's full name who diagnosed their problem."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "db427fff",
   "metadata": {},
   "outputs": [],
   "source": [
    "qstr = \"\"\"\n",
    "SELECT\n",
    "  p.first_name || ' ' || p.last_name AS patient_name,\n",
    "  diagnosis,\n",
    "  d.first_name || ' ' || d.last_name AS doctor_name\n",
    "FROM patients p\n",
    "  JOIN admissions a ON p.patient_id = a.patient_id\n",
    "  JOIN doctors d ON a.attending_doctor_id = d.doctor_id\n",
    "\"\"\"\n",
    "sqlq = sql_raw(qstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "33e81ad1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unequal ❌\n",
      "SELECT \"patients\".\"patient_id\"\n",
      "FROM \"patients\"\n"
     ]
    }
   ],
   "source": [
    "ormq = Patients.objects.values(\"id\")\n",
    "equal(sqlq, ormq)\n",
    "print_sql(ormq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "b00fef4c",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orm_to_df(ormq[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5fbdbe81",
   "metadata": {},
   "source": [
    "## Medium 23\n",
    "display the number of duplicate patients based on their first_name and last_name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "0486d55d",
   "metadata": {},
   "outputs": [],
   "source": [
    "qstr = \"\"\"\n",
    "SELECT\n",
    "  first_name,\n",
    "  last_name,\n",
    "  count(*) duplicates\n",
    "FROM patients\n",
    "GROUP BY\n",
    "  first_name,\n",
    "  last_name\n",
    "HAVING COUNT(*) > 1\n",
    "\"\"\"\n",
    "sqlq = sql_raw(qstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "06f11f1a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unequal ❌\n",
      "SELECT \"patients\".\"patient_id\"\n",
      "FROM \"patients\"\n"
     ]
    }
   ],
   "source": [
    "ormq = Patients.objects.values(\"id\")\n",
    "equal(sqlq, ormq)\n",
    "print_sql(ormq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "9deec97c",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orm_to_df(ormq[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "536c8927",
   "metadata": {},
   "source": [
    "## Medium 24\n",
    "Display patient's full name,\n",
    "height in the units feet rounded to 1 decimal,\n",
    "weight in the unit pounds rounded to 0 decimals,\n",
    "birth_date,\n",
    "gender non abbreviated.\n",
    "\n",
    "Convert CM to feet by dividing by 30.48.\n",
    "Convert KG to pounds by multiplying by 2.205."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "d8b4428c",
   "metadata": {},
   "outputs": [],
   "source": [
    "qstr = \"\"\"\n",
    "SELECT\n",
    "  first_name || ' ' || last_name AS full_name,\n",
    "  ROUND(height / 30.48, 1) AS height_inc,\n",
    "  ROUND(weight * 2.205, 0) AS weight_pound,\n",
    "  birth_date,\n",
    "  CASE\n",
    "    WHEN gender = 'M' THEN 'Male'\n",
    "    WHEN gender = 'F' THEN 'Female'\n",
    "  END AS gender_full\n",
    "FROM patients\n",
    "\"\"\"\n",
    "sqlq = sql_raw(qstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "40c0979b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unequal ❌\n",
      "SELECT \"patients\".\"patient_id\"\n",
      "FROM \"patients\"\n"
     ]
    }
   ],
   "source": [
    "ormq = Patients.objects.values(\"id\")\n",
    "equal(sqlq, ormq)\n",
    "print_sql(ormq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "9dc606b4",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orm_to_df(ormq[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f46c8e7",
   "metadata": {},
   "source": [
    "## Medium 25\n",
    "Show patient_id, first_name, last_name from patients whose does not have any records in the admissions table. (Their patient_id does not exist in any admissions.patient_id rows.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "cca87548",
   "metadata": {},
   "outputs": [],
   "source": [
    "qstr = \"\"\"\n",
    "SELECT\n",
    "  patient_id,\n",
    "  first_name,\n",
    "  last_name\n",
    "FROM patients\n",
    "WHERE patient_id NOT IN (\n",
    "    SELECT patient_id\n",
    "    FROM admissions\n",
    "  )\n",
    "\"\"\"\n",
    "sqlq = sql_raw(qstr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "8323af94",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unequal ❌\n",
      "SELECT \"patients\".\"patient_id\"\n",
      "FROM \"patients\"\n"
     ]
    }
   ],
   "source": [
    "ormq = Patients.objects.values(\"id\")\n",
    "equal(sqlq, ormq)\n",
    "print_sql(ormq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "e574a632",
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id\n",
       "0   1\n",
       "1   2\n",
       "2   3"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orm_to_df(ormq[:3])"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Django Shell-Plus",
   "language": "python",
   "name": "django_extensions"
  },
  "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.9.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}