{ "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", " | name | \n", "age | \n", "class | \n", "
---|---|---|---|
0 | \n", "Williams, Mr. Richard Norris II | \n", "21.0 | \n", "1 | \n", "
1 | \n", "Greenfield, Mr. William Bertram | \n", "23.0 | \n", "1 | \n", "
2 | \n", "Snyder, Mr. John Pillsbury | \n", "24.0 | \n", "1 | \n", "
3 | \n", "Oxenham, Mr. Percy Thomas | \n", "22.0 | \n", "2 | \n", "
4 | \n", "Collett, Mr. Sidney C Stuart | \n", "24.0 | \n", "2 | \n", "
5 | \n", "Buckley, Mr. Daniel | \n", "21.0 | \n", "3 | \n", "
6 | \n", "Jansson, Mr. Carl Olof | \n", "21.0 | \n", "3 | \n", "
7 | \n", "Karlsson, Mr. Einar Gervasius | \n", "21.0 | \n", "3 | \n", "
8 | \n", "Midtsjo, Mr. Karl Albert | \n", "21.0 | \n", "3 | \n", "
9 | \n", "Leeni, Mr. Fahim (Philip Zenni | \n", "22.0 | \n", "3 | \n", "
10 | \n", "Vartanian, Mr. David | \n", "22.0 | \n", "3 | \n", "
11 | \n", "Asplund, Mr. Johan Charles | \n", "23.0 | \n", "3 | \n", "
12 | \n", "Duquemin, Mr. Joseph | \n", "24.0 | \n", "3 | \n", "
13 | \n", "Madsen, Mr. Fridtjof Arne | \n", "24.0 | \n", "3 | \n", "