Simple-SQLite-Lesson.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "64ac031f",
   "metadata": {},
   "source": [
    "# Simple SQLite Query Notebook\n",
    "This notebook contains SQL queries derived from the exercises taken from:\n",
    "\n",
    "**[SQLite Data Starter Packs](http://2016.padjo.org/tutorials/sqlite-data-starterpacks/#toc-simplefolks-for-simple-sql) | Public Affairs Data Journalism at Stanford | Fall 2016**\n",
    "\n",
    "> This is a collection of public datasets conveniently packaged as SQLite databases to practice on.\n",
    "> \n",
    "> You don’t have to worry about the data cleaning/import process, just download the SQLite database files and query them from your favorite SQLite client.\n",
    "\n",
    "[See this simple database as a Google Spredsheet](https://docs.google.com/spreadsheets/d/1OzM6Sp92cDG_jkO4_WGEacuD0rwYF_g2kHOIuq6NgZM/view)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5d70df13-eb79-4111-b465-5036d41e10a2",
   "metadata": {},
   "source": [
    "SQLite3 comes with Python's standard library, so no extra install is needed if you already have pandas.\n",
    "\n",
    "If you need to install any missing libraries, uncomment and run the following commands:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a720bb4c-6d42-45a6-a903-3fa10f4fc9bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27c167cc-117d-4d34-be89-f7c00f232954",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql sqlite:///data/simplefolks.db"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f03c171",
   "metadata": {},
   "source": [
    "## Viewing: Select everything from the `people` table.\n",
    "\n",
    "**Details**: Include all the columns and rows, order does not matter.\n",
    "\n",
    "**Hint 1**: You can use `*` to include all columns of a given table.\n",
    "\n",
    "**Hint 2**: By default, all rows in a specified table are selected."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "42e53a8a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- # Query: Select everything from the `people` table.\n",
    "SELECT * FROM people;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cc1834a3",
   "metadata": {},
   "source": [
    "## Viewing: Select all the rows from `people, but list the columns in reverse sequence.\n",
    "\n",
    "**Details**: Include all of the columns, but just in reverse order from how they're listed in the `people` table. \n",
    "\n",
    "**Hint 1**: Can't use `*`, as that will include all the columns in their default order.\n",
    "\n",
    "**Hint 2**: Use commas to separate the field names in the SELECT clause."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a860951e",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- # Query: Select all the rows from `people, but list the columns in reverse sequence.\n",
    "SELECT age, sex, name\n",
    "FROM people;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e7e90ae",
   "metadata": {},
   "source": [
    "## Limiting: Select the first row from `pets`\n",
    "\n",
    "**Details**: Include all the columns, but list only the first row.\n",
    "\n",
    "**Hint 1**: LIMIT is pretty much the last clause in any SELECT query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "29d2de61",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- # Query: Select the first row from `pets`\n",
    "SELECT *\n",
    "FROM pets\n",
    "LIMIT 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dacbcb30",
   "metadata": {},
   "source": [
    "## Limiting: Select just the value of the first 3 `homes`\n",
    "\n",
    "**Details**: Include only the `value` column for the first 3 rows\n",
    "\n",
    "**Hint 1**: Select only the column that is required\n",
    "\n",
    "**Hint 2**: LIMIT 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4901491d",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- # Query: Select just the value of the first 3 `homes`\n",
    "SELECT value\n",
    "FROM homes\n",
    "LIMIT 3;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eed8f98d",
   "metadata": {},
   "source": [
    "## Sorting: List pet and owner names, from `pets`, in alphabetical order of the pet's name\n",
    "\n",
    "**Details**: The result should only have 2 columns, with the original headers of `name` and `owner_name`, in that order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b654a1ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- # Query: List pet and owner names, from `pets`, in alphabetical order of the pet's name\n",
    "SELECT name, owner_name\n",
    "FROM pets\n",
    "ORDER BY name;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f90d755d",
   "metadata": {},
   "source": [
    "## Sorting: List `homes` in order of area by alphabetical order, then by highest value.\n",
    "\n",
    "**Details**: Include all columns. Interpret \"highest\" value to mean, list biggest numbers first, per area."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "691de7e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- # Query: List `homes` in order of area by alphabetical order, then by highest value.\n",
    "SELECT *\n",
    "FROM homes\n",
    "ORDER BY area ASC, value DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c377b010",
   "metadata": {},
   "source": [
    "## Sorting: List the 3 oldest `people`\n",
    "\n",
    "**Details**: Include every column. Interpret \"oldest\" as: descending order of the age number.\n",
    "\n",
    "**Hint 1**: Biggest first means \"descending order\"\n",
    "\n",
    "**Hint 2**: LIMIT comes after ORDER BY"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "927556b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- # Query: List the 3 oldest `people`\n",
    "SELECT *\n",
    "FROM people\n",
    "ORDER BY age DESC\n",
    "LIMIT 3;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cc9338ce",
   "metadata": {},
   "source": [
    "## Filtering: List all the `people` younger than 30\n",
    "\n",
    "**Details**: Include all columns. Order rows by ascending order of age.\n",
    "\n",
    "**Hint 1**: WHERE goes after FROM\n",
    "\n",
    "**Hint 2**: age < 30"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c2f10f98",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- # Query: List all the `people` younger than 30\n",
    "SELECT *\n",
    "FROM people\n",
    "WHERE age < 30\n",
    "ORDER BY age ASC;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "735a7155",
   "metadata": {},
   "source": [
    "## Filtering: List the 3 cheapest `homes` in the urban area.\n",
    "\n",
    "**Details**: Include all columns\n",
    "\n",
    "**Hint 1**: Use single-quotes to denote the literal value of 'urban'\n",
    "\n",
    "**Hint 2**: WHERE homes = 'urban'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "08af522f",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- # Query: List the 3 cheapest `homes` in the urban area.\n",
    "SELECT *\n",
    "FROM homes\n",
    "WHERE area = 'urban'\n",
    "ORDER BY value ASC;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1e590a7",
   "metadata": {},
   "source": [
    "## Filtering: List every person between the ages of 30 and 50 (inclusive)\n",
    "\n",
    "**Details**: Include all columns. You can use the BETWEEN keyword if you know it. Order the rows by age, ascending\n",
    "\n",
    "**Hint 1**: Use `>=` and `<=` for \"greater than or equal to\" and \"less than or equal to\", respectively\n",
    "\n",
    "**Hint 2**: Think of the age constraint as requring the age number to meet a lower bound AND an upper bound."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dfed3013",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- # Query: List every person between the ages of 30 and 50 (inclusive)\n",
    "SELECT *\n",
    "FROM people\n",
    "WHERE age >= 30 AND age <= 60;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7fd41f9b",
   "metadata": {},
   "source": [
    "## Filtering: List every person younger than 35 or older than 65\n",
    "\n",
    "**Details**: Include all columns. Order the rows by age, ascending.\n",
    "\n",
    "**Hint 1**: Someone who is younger than 35 AND older than 65 is logically impossible.\n",
    "\n",
    "**Hint 2**: A person can either have an age less than 35, OR, greater than 65."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9c50cfae",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- # Query: List every person younger than 35 or older than 65\n",
    "SELECT *\n",
    "FROM people\n",
    "WHERE age < 35 OR age > 65"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "49fa4c9b",
   "metadata": {},
   "source": [
    "### Key Points:\n",
    "\n",
    "- Include all columns. Order the rows by age, ascending."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "605d7f35",
   "metadata": {},
   "source": [
    "## Filtering: List the 3 most expensive homes that are neither in the country or owned by Donald nor Hillary.\n",
    "\n",
    "**Details**: This isn't a particularly sophisticated (or useful) query. But there are so many ways to express this conditional statement that you could tie yourself up in **nots** `!=` (yes, silly pun with \"knots\").\n",
    "\n",
    "**Hint 1**: You could get by with 3 uses of the `!=` operator.\n",
    "\n",
    "**Hint 2**: Or, you could wrap up OR conditions usingparentheses, and negate the result of those conditions:\n",
    "\n",
    "```sql\n",
    "WHERE NOT(area = 'country' \n",
    "        OR owner_name IN('Donald', 'Hillary') \n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8075abe",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- # Query: List the 3 most expensive homes that are neither in the country or owned by Donald nor Hillary.\n",
    "SELECT *\n",
    "FROM homes\n",
    "WHERE area != 'country'\n",
    "AND owner_name != 'Donald'\n",
    "AND owner_name != 'Hillary'\n",
    "ORDER BY value DESC\n",
    "LIMIT 3;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "384ce73b",
   "metadata": {},
   "source": [
    "## Filtering: List all the 40-years-and-older men in `people`\n",
    "\n",
    "**Details**: List all columns, order the rows by oldest age.\n",
    "\n",
    "**Hint 1**: Use `=` to match literal string values, such as 'M' or 'F'\n",
    "\n",
    "**Hint 2**: If we want both conditions in a boolean expression to be met, we don't use `OR`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7127640f",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- # Query: List all the 40-years-and-older men in `people`\n",
    "SELECT *\n",
    "FROM people\n",
    "WHERE sex = 'M'\n",
    "AND age >= 40;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "993b5ab1",
   "metadata": {},
   "source": [
    "## Filtering: List the names of the pets that are owned by Sherry, Dani, and Zed.\n",
    "\n",
    "**Details**: List the owner's name, then the pet's name, and order the rows alphabetically by the owner's name, then the pet's name.\n",
    "\n",
    "**Hint 1**: Kind of a trick question: How we describe wanting the pets of this owner AND that owner in a normal sentence is NOT the same as we do in SQL.\n",
    "\n",
    "**Hint 2**: Another way to say, \"Pers owned by Sherry, Dani, and Zed\" is: \"Pets owned by either Sherry, Dani, OR Zed\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "50f60a02",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- # Query: List the names of the pets that are owned by Sherry, Dani, and Zed.\n",
    "SELECT owner_name, name\n",
    "FROM pets\n",
    "WHERE owner_name IN ('Sherry', 'Dani', 'Zed')\n",
    "ORDER BY owner_name ASC, name ASC;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f6f6c53",
   "metadata": {},
   "source": [
    "## Filtering: List the names of pets that are not cats.\n",
    "\n",
    "**Details**: List only the name column in alphabetical order\n",
    "\n",
    "**Hint 1**: Use `!=` to express \"not equal to\"\n",
    "\n",
    "**Hint 2**: type != 'cat'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f88fe20a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- # Query: List the names of pets that are not cats.\n",
    "SELECT name\n",
    "FROM pets\n",
    "WHERE type != 'cat';"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a82a3e59",
   "metadata": {},
   "source": [
    "## Filtering: List the pets, name and type, that are neither dogs nor cats\n",
    "\n",
    "**Details**: List the name and type of pet, ordered by type and name alphabetically.\n",
    "\n",
    "**Hint 1**: You can use `!=` with `AND`\n",
    "\n",
    "**Hint 2**: IN('dog', 'cat'), with NOT in front to negate it, will also work."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1cc3cab1",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- # Query: List the pets, name and type, that are neither dogs nor cats\n",
    "SELECT name, type\n",
    "FROM pets\n",
    "WHERE type != 'cat' AND type != 'dog'\n",
    "ORDER BY type, name;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7731143d",
   "metadata": {},
   "source": [
    "## Transforming: List the first letter of each person's name in `people`\n",
    "\n",
    "**Details**: The result set should have one column. No need to specify order of rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "172bbb70",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- # Query: List the first letter of each person's name in `people`\n",
    "SELECT SUBSTR(name, 1, 1)\n",
    "FROM people;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "26532bc0",
   "metadata": {},
   "source": [
    "## Transforming: List the combined length of owner name and pet name in `pets`\n",
    "\n",
    "**Details**: Just list a single column, any order.\n",
    "\n",
    "**Hint 1**: You can add (or subtract, etc) the result of function calls to each other"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2051e0d5",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- # Query: List the combined length of owner name and pet name in `pets`\n",
    "SELECT LENGTH(name) + LENGTH(owner_name)\n",
    "FROM pets;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f120ecc",
   "metadata": {},
   "source": [
    "## Transforming: List the uppercased first 3 letters of a politician's first name and their uppercased last name\n",
    "\n",
    "**Details**: 2 columns named \"first_letters\" and \"last_name\", in alphabetical order of last name, then first_letters\n",
    "\n",
    "**Hint 1**: Use the UPPER function to uppercase a value.\n",
    "\n",
    "**Hint 2**: Use SUBSTR to excerpt a string value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a5a38c43",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- # Query: List the uppercased first 3 letters of a politician's first name and their uppercased last name\n",
    "SELECT\n",
    "SUBSTR(UPPER(first_name), 1, 3) AS first_letters,\n",
    "UPPER(last_name) AS last_name\n",
    "FROM politicians\n",
    "ORDER BY last_name, first_letters;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f945974",
   "metadata": {},
   "source": [
    "## Transforming: List the 5 pets whose names are longer than their owners', in order of greatest difference.\n",
    "\n",
    "**Details**: List 3 columns: name, owner_name, and name_diff\n",
    "\n",
    "**Hint 1**: Subtract the LENGTH of one name against the other to get the difference.\n",
    "\n",
    "**Hint 2**: Use a WHERE clause to filter the selection for pets whose names are longer than their owners."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6324de06",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- # Query: List the 5 pets whose names are longer than their owners', in order of greatest difference.\n",
    "SELECT\n",
    "name, owner_name,\n",
    "LENGTH(name) - LENGTH(owner_name) AS name_diff\n",
    "FROM pets\n",
    "ORDER BY\n",
    "name_diff DESC\n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "61ee15ca",
   "metadata": {},
   "source": [
    "## Transforming: List the names, and also the ages of each politician derived from `birth_year` (assuming the current year is 2016)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "da447b7c",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- # Query: List the names, and also the ages of each politician derived from `birth_year` (assuming the current year is 2016)\n",
    "SELECT first_name, last_name,\n",
    "(2016 - birth_year) AS age\n",
    "FROM politicians\n",
    "ORDER BY age DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46ad0340",
   "metadata": {},
   "source": [
    "## Transforming: Get the decade of birth of each politician."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c39a309e",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- # Query: Get the decade of birth of each politician.\n",
    "SELECT\n",
    "first_name, last_name,\n",
    "(birth_year / 10) * 10 AS birth_decade\n",
    "FROM politicians\n",
    "ORDER BY birth_decade ASC, last_name, first_name;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0211fb95",
   "metadata": {},
   "source": [
    "## Transforming: List the value of the `homes` in thousands of dollars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df53a097",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- # Query: List the value of the `homes` in thousands of dollars\n",
    "SELECT (value / 1000)\n",
    "AS value_k\n",
    "FROM homes\n",
    "ORDER BY value_k;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2ac59ba",
   "metadata": {},
   "source": [
    "## Aggregating: Count the number of politicians.\n",
    "\n",
    "**Details**: List one column named pcount."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "accbd29c",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- # Query: Count the number of politicians.\n",
    "SELECT COUNT(*) AS pcount FROM politicians;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba94ae69",
   "metadata": {},
   "source": [
    "## Aggregating: List the values of the most expensive and cheapest `homes`.\n",
    "\n",
    "**Details**: List 2 columns, `most_expensive` and `cheapest`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03ffc48a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- # Query: List the values of the most expensive and cheapest `homes`.\n",
    "SELECT MAX(value) AS most_expensive,\n",
    "MIN(value) AS cheapest\n",
    "FROM homes;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83b46a50",
   "metadata": {},
   "source": [
    "## Aggregating: Fromn `people`, List the rounded, average age of `people`, and also the difference between the oldest and youngest.\n",
    "\n",
    "**Details**: List 2 columns, `rounded` and `diff_age`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d36ecfa2",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- # Query: Fromn `people`, List the rounded, average age of `people`, and also the difference between the oldest and youngest.\n",
    "SELECT ROUND(AVG(age)) AS rounded_age,\n",
    "MAX(age) - MIN(age)\n",
    "FROM people;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b2b175c-725d-4f79-b04f-951086541841",
   "metadata": {},
   "source": [
    "<a id='resources'></a>\n",
    "## Additional Resources\n",
    "\n",
    "- **SQLite Documentation:** [https://www.sqlite.org/docs.html](https://www.sqlite.org/docs.html)\n",
    "- **SQLite Command Line Reference:** [https://www.sqlite.org/cli.html](https://www.sqlite.org/cli.html)\n",
    "- **SQL Tutorial Cheatsheet:** [https://www.sqltutorial.org/sql-cheat-sheet/](https://www.sqltutorial.org/sql-cheat-sheet/)\n",
    "- **SQLite Browser Wiki:** [SQLite Browser GitHub Wiki](https://github.com/sqlitebrowser/sqlitebrowser/wiki)\n",
    "- **Northwind SQLite Repo:** [Github Repo](https://github.com/jpwhite3/northwind-SQLite3/)\n",
    "- **Northwind Database Schema:** [Schema Diagram](https://github.com/jpwhite3/northwind-SQLite3/blob/main/docs/Northwind_ERD.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "164ab25c-9975-4cb6-aac4-639b57d2afd8",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}