{ "cells": [ { "cell_type": "code", "execution_count": 1, "id": "4d2f6331-3b21-45d8-a1d4-0f31471463ce", "metadata": {}, "outputs": [], "source": [ "from DATA225utils import make_connection, dataframe_query" ] }, { "cell_type": "code", "execution_count": 2, "id": "295c34b0-4708-4eca-9e24-b281eedfb98d", "metadata": {}, "outputs": [], "source": [ "conn = make_connection('titanic.ini')\n", "cursor = conn.cursor()" ] }, { "cell_type": "markdown", "id": "829ae112-aacf-4ea2-8df4-bea2de1474df", "metadata": {}, "source": [ "# Create the young_men_survivors table." ] }, { "cell_type": "code", "execution_count": 3, "id": "05d30dd3-5bfe-47ce-8086-92334d8fdf4e", "metadata": {}, "outputs": [], "source": [ "cursor.execute('DROP TABLE IF EXISTS young_men_survivors')\n", "\n", "sql = \"\"\"\n", " CREATE TABLE young_men_survivors\n", " (\n", " name VARCHAR(32) NOT NULL UNIQUE,\n", " age DOUBLE NOT NULL,\n", " class INT NOT NULL,\n", " PRIMARY KEY(name)\n", " )\n", " \"\"\"\n", "\n", "cursor.execute(sql)" ] }, { "cell_type": "markdown", "id": "08eebdcd-1c6c-460e-a77d-243350c86fcf", "metadata": {}, "source": [ "# Load the young_men_survivors table." ] }, { "cell_type": "code", "execution_count": 4, "id": "53e3671d-b155-485d-a90c-e977cd7e2be4", "metadata": {}, "outputs": [], "source": [ "sql = \"\"\"\n", " INSERT INTO young_men_survivors\n", " SELECT name, age,\n", " CASE \n", " WHEN passenger_class = '1st' THEN 1\n", " WHEN passenger_class = '2nd' THEN 2\n", " WHEN passenger_class = '3rd' THEN 3\n", " ELSE 0\n", " END AS class\n", " FROM passengers\n", " WHERE sex = 'male'\n", " AND survived = 'yes'\n", " AND age BETWEEN 21 AND 24\n", " \"\"\"\n", "\n", "cursor.execute(sql)\n", "conn.commit()" ] }, { "cell_type": "markdown", "id": "7a071f1a-99b5-43f8-b625-19aa95bb6fbe", "metadata": {}, "source": [ "# Display the table." ] }, { "cell_type": "code", "execution_count": 5, "id": "77e3380d-2ee6-4769-a794-48a4a07bda31", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
nameageclass
0Williams, Mr. Richard Norris II21.01
1Greenfield, Mr. William Bertram23.01
2Snyder, Mr. John Pillsbury24.01
3Oxenham, Mr. Percy Thomas22.02
4Collett, Mr. Sidney C Stuart24.02
5Buckley, Mr. Daniel21.03
6Jansson, Mr. Carl Olof21.03
7Karlsson, Mr. Einar Gervasius21.03
8Midtsjo, Mr. Karl Albert21.03
9Leeni, Mr. Fahim (Philip Zenni22.03
10Vartanian, Mr. David22.03
11Asplund, Mr. Johan Charles23.03
12Duquemin, Mr. Joseph24.03
13Madsen, Mr. Fridtjof Arne24.03
\n", "
" ], "text/plain": [ " name age class\n", "0 Williams, Mr. Richard Norris II 21.0 1\n", "1 Greenfield, Mr. William Bertram 23.0 1\n", "2 Snyder, Mr. John Pillsbury 24.0 1\n", "3 Oxenham, Mr. Percy Thomas 22.0 2\n", "4 Collett, Mr. Sidney C Stuart 24.0 2\n", "5 Buckley, Mr. Daniel 21.0 3\n", "6 Jansson, Mr. Carl Olof 21.0 3\n", "7 Karlsson, Mr. Einar Gervasius 21.0 3\n", "8 Midtsjo, Mr. Karl Albert 21.0 3\n", "9 Leeni, Mr. Fahim (Philip Zenni 22.0 3\n", "10 Vartanian, Mr. David 22.0 3\n", "11 Asplund, Mr. Johan Charles 23.0 3\n", "12 Duquemin, Mr. Joseph 24.0 3\n", "13 Madsen, Mr. Fridtjof Arne 24.0 3" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "_, df = dataframe_query(conn, \n", " \"\"\"\n", " SELECT *\n", " FROM young_men_survivors\n", " ORDER BY class, age\n", " \"\"\"\n", " )\n", "display(df)" ] }, { "cell_type": "code", "execution_count": 6, "id": "3d50dd07-7174-47a2-aba2-999ae303c8ef", "metadata": {}, "outputs": [], "source": [ "conn.close()" ] }, { "cell_type": "code", "execution_count": null, "id": "70181bfa-f85a-4907-b169-18e4bc8b7410", "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.9.13" } }, "nbformat": 4, "nbformat_minor": 5 }