Skip to content

Instantly share code, notes, and snippets.

@jsmorph

jsmorph/q7p4.ipynb

Created November 30, 2022 03:24
Show Gist options
  • Save jsmorph/7f5c0d3078dd1fbd8413c276a1fbde4d to your computer and use it in GitHub Desktop.
Save jsmorph/7f5c0d3078dd1fbd8413c276a1fbde4d to your computer and use it in GitHub Desktop.
Download ZIP
Example notebook
Display the source blob
Display the rendered blob
Raw
q7p4.ipynb
{
"nbformat": 4,
"nbformat_minor": 0,
"metadata": {
"colab": {
"provenance": []
},
"kernelspec": {
"name": "python3",
"display_name": "Python 3"
},
"language_info": {
"name": "python"
}
},
"cells": [
{
"cell_type": "markdown",
"source": [
"The old answer marked as correct on (practice) Quiz 7, Problem 4 was incorrect. `11` is not the correct answer. \"Can't tell without knowning more\" is the correct answer, and _I think_ the quiz has been updated accordingly."
],
"metadata": {
"id": "LaVPZY0Lfa6C"
}
},
{
"cell_type": "markdown",
"source": [
"This notebook is a very simple example."
],
"metadata": {
"id": "Rdee6DUChB1D"
}
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"id": "xw4v9wZqxH6j"
},
"outputs": [],
"source": [
"%load_ext sql"
]
},
{
"cell_type": "code",
"source": [
"%sql sqlite://"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/",
"height": 35
},
"id": "Yi0fTKSKxN5F",
"outputId": "3bdcb84e-0352-4116-9996-474ede9835f9"
},
"execution_count": null,
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": [
"'Connected: @None'"
],
"application/vnd.google.colaboratory.intrinsic+json": {
"type": "string"
}
},
"metadata": {},
"execution_count": 3
}
]
},
{
"cell_type": "markdown",
"source": [
"We'll make an example table manually."
],
"metadata": {
"id": "VPHwe5eIhE17"
}
},
{
"cell_type": "code",
"source": [
"%%sql\n",
"CREATE TABLE p4 (n INTEGER, Belts INTEGER, Siblings INTEGER, metric INTEGER)"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "ezyth1LPeGsr",
"outputId": "c0fddae8-e45c-42e8-9f1d-dd69939ebfab"
},
"execution_count": null,
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": [
" * sqlite://\n",
"Done.\n"
]
},
{
"output_type": "execute_result",
"data": {
"text/plain": [
"[]"
]
},
"metadata": {},
"execution_count": 9
}
]
},
{
"cell_type": "markdown",
"source": [
"We'll ignore `Undergrad` and just use all the records. Doesn't really change what's going on."
],
"metadata": {
"id": "4H9onyHlhJuc"
}
},
{
"cell_type": "code",
"source": [
"%%sql\n",
"INSERT INTO p4 (n, Belts, Siblings) VALUES\n",
" (1,9,0),\n",
" (2,5,6)"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "q6_OGuEneYUF",
"outputId": "3670b5bf-8c3e-4eeb-f42f-74ca1b953843"
},
"execution_count": null,
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": [
" * sqlite://\n",
"2 rows affected.\n"
]
},
{
"output_type": "execute_result",
"data": {
"text/plain": [
"[]"
]
},
"metadata": {},
"execution_count": 10
}
]
},
{
"cell_type": "markdown",
"source": [
"Now let's compute `metric`."
],
"metadata": {
"id": "KG3pb0JqhOq9"
}
},
{
"cell_type": "code",
"source": [
"%sql UPDATE p4 SET metric = Belts + Siblings;"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "jF8v9llmejFJ",
"outputId": "6570c083-0bba-4e02-b42f-e1951c3b7a1b"
},
"execution_count": null,
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": [
" * sqlite://\n",
"2 rows affected.\n"
]
},
{
"output_type": "execute_result",
"data": {
"text/plain": [
"[]"
]
},
"metadata": {},
"execution_count": 11
}
]
},
{
"cell_type": "markdown",
"source": [
"Check our work so far:"
],
"metadata": {
"id": "IIEeMq-bhRUn"
}
},
{
"cell_type": "code",
"source": [
"%sql SELECT * FROM p4"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/",
"height": 118
},
"id": "9BfB3FNOenbJ",
"outputId": "7d52d3d4-c333-4511-c8a7-b2581027272b"
},
"execution_count": null,
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": [
" * sqlite://\n",
"Done.\n"
]
},
{
"output_type": "execute_result",
"data": {
"text/plain": [
"[(1, 9, 0, 9), (2, 5, 6, 11)]"
],
"text/html": [
"<table>\n",
" <thead>\n",
" <tr>\n",
" <th>n</th>\n",
" <th>Belts</th>\n",
" <th>Siblings</th>\n",
" <th>metric</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>9</td>\n",
" <td>0</td>\n",
" <td>9</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>5</td>\n",
" <td>6</td>\n",
" <td>11</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
]
},
"metadata": {},
"execution_count": 12
}
]
},
{
"cell_type": "markdown",
"source": [
"Compute the sum of `MAX(Belts)` and `MAX(Siblings)`."
],
"metadata": {
"id": "fc9Swn-OhS_z"
}
},
{
"cell_type": "code",
"source": [
"%sql SELECT MAX(Belts) + MAX(Siblings) FROM p4"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/",
"height": 96
},
"id": "IZtdPLJnepbh",
"outputId": "ffbd1a21-e84f-452f-f897-a711c282f23b"
},
"execution_count": null,
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": [
" * sqlite://\n",
"Done.\n"
]
},
{
"output_type": "execute_result",
"data": {
"text/plain": [
"[(15,)]"
],
"text/html": [
"<table>\n",
" <thead>\n",
" <tr>\n",
" <th>MAX(Belts) + MAX(Siblings)</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <td>15</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
]
},
"metadata": {},
"execution_count": 13
}
]
},
{
"cell_type": "markdown",
"source": [
"That result is _not_ the same as computing `MAX(metric)`."
],
"metadata": {
"id": "tCiaUI_phYjJ"
}
},
{
"cell_type": "code",
"source": [
"%sql SELECT MAX(metric) FROM p4"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/",
"height": 96
},
"id": "eWRG37p4e2g9",
"outputId": "533c5f15-4208-4b46-e735-9d85837aef14"
},
"execution_count": null,
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": [
" * sqlite://\n",
"Done.\n"
]
},
{
"output_type": "execute_result",
"data": {
"text/plain": [
"[(11,)]"
],
"text/html": [
"<table>\n",
" <thead>\n",
" <tr>\n",
" <th>MAX(metric)</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <td>11</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
]
},
"metadata": {},
"execution_count": 14
}
]
},
{
"cell_type": "markdown",
"source": [
"Each `MAX(Siblings)` and `MAX(Belts)` is computed across all records independently. In contrast, `MAX(Siblings + Belts)`, which is the same as `MAX(metric)` is computed based on `Silbings` and `Belts` for _each record_."
],
"metadata": {
"id": "cSNg4cMehd14"
}
},
{
"cell_type": "markdown",
"source": [
"Knowing `MAX(metric)` doesn't help (much) in determining `MAX(Belts) + MAX(Siblings)`. That was the point of this question."
],
"metadata": {
"id": "IyLC7skKh2nu"
}
},
{
"cell_type": "markdown",
"source": [
"In my defense (!), I have three versions of this question. I got them mixed up!"
],
"metadata": {
"id": "lkejXqkDiB0f"
}
},
{
"cell_type": "markdown",
"source": [
"Note that this query returns the same result as the one for `MAX(metric)`, which should not be surprising."
],
"metadata": {
"id": "1IgZmB7qjutG"
}
},
{
"cell_type": "code",
"source": [
"%sql SELECT MAX(Belts + Siblings) FROM p4"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/",
"height": 96
},
"id": "ERR9oQT7jqrm",
"outputId": "bb9fe2b6-399e-4e32-ac2c-c98665f855e1"
},
"execution_count": 15,
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": [
" * sqlite://\n",
"Done.\n"
]
},
{
"output_type": "execute_result",
"data": {
"text/plain": [
"[(11,)]"
],
"text/html": [
"<table>\n",
" <thead>\n",
" <tr>\n",
" <th>MAX(Belts + Siblings)</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <td>11</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
]
},
"metadata": {},
"execution_count": 15
}
]
}
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment