# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     name: python3
# ---

# + id="PBRnYFAuOD24" colab_type="code" outputId="536cc1bb-bcad-47d2-97ae-213749e3623e" colab={"base_uri": "https://localhost:8080/", "height": 124}
# !pip install psycopg2-binary

# + id="zVbYVnn8O20p" colab_type="code" colab={}
import psycopg2

# + id="_5Bl-i0rO741" colab_type="code" colab={}
host = 'raja.db.elephantsql.com'
user = 'amdimexz'
database = 'amdimexz'
password = '<PASSWORD>'

# + id="5n4urI4EO_tn" colab_type="code" colab={}
pg_conn = psycopg2.connect(database=database, user=user, password=password, host=host)
pg_cur = pg_conn.cursor()


# + id="6P9Yq1kWPKFO" colab_type="code" colab={}
def execute(query, conn):
  with conn:
    with conn.cursor() as cur:
      cur.execute(query)
      result = cur.fetchall()
  return list(result)


# + id="vPbUmuKZPx9N" colab_type="code" colab={}
query1 = 'SELECT COUNT(survived) \
          FROM "public"."titanic" \
          WHERE survived = 1'

# + id="tnRWIcpsRRcF" colab_type="code" colab={}
query2 = 'SELECT COUNT(survived) \
          FROM "public"."titanic" \
          WHERE survived = 0'

# + id="KbvJQyxNUiE8" colab_type="code" colab={}
query3 = 'SELECT pclass, COUNT(survived) \
          FROM "public"."titanic" \
          WHERE survived = 1 \
          GROUP BY pclass \
          ORDER BY pclass'

# + id="ztsNolbhW0yT" colab_type="code" colab={}
query4 = 'SELECT pclass, COUNT(survived) \
          FROM "public"."titanic" \
          WHERE survived = 0 \
          GROUP BY pclass \
          ORDER BY pclass'

# + id="lVvqf78_XPCl" colab_type="code" colab={}
query5 = 'SELECT  survived, AVG(age) \
          FROM "public"."titanic" \
          GROUP BY survived \
          ORDER BY survived'

# + id="PIWfWu2tZeam" colab_type="code" colab={}
query6 = 'SELECT  pclass, AVG(age) \
          FROM "public"."titanic" \
          GROUP BY pclass \
          ORDER BY pclass'

# + id="dh_l6AnUaTtE" colab_type="code" colab={}
query7 = 'SELECT  pclass, AVG(fare) \
          FROM "public"."titanic" \
          GROUP BY pclass \
          ORDER BY pclass'

# + id="HDh5hRbxa0IV" colab_type="code" colab={}
query8 = 'SELECT  survived, AVG(fare) \
          FROM "public"."titanic" \
          GROUP BY survived \
          ORDER BY survived'

# + id="9BGgFAYeb-gr" colab_type="code" colab={}
query9 = 'SELECT pclass, AVG(siblings) \
          FROM "public"."titanic" \
          GROUP BY pclass \
          ORDER BY pclass'

# + id="UmSB7k6hc43q" colab_type="code" colab={}
query10 = 'SELECT survived, AVG(siblings) \
           FROM "public"."titanic" \
           GROUP BY survived \
           ORDER BY survived'

# + id="3JJ2WjAfdbhr" colab_type="code" colab={}
query11 = 'SELECT pclass, AVG(parents) \
           FROM "public"."titanic" \
           GROUP BY pclass \
           ORDER BY pclass'

# + id="qCK7ztJqd5Eb" colab_type="code" colab={}
query12 = 'SELECT survived, AVG(parents) \
           FROM "public"."titanic" \
           GROUP BY survived \
           ORDER BY survived'

# + id="Lz3jg-daeWS5" colab_type="code" colab={}
query13 = 'SELECT name, COUNT(*) \
           FROM "public"."titanic" \
           GROUP BY name \
           HAVING COUNT(*) > 1'

# + id="ovlqSA9bie2g" colab_type="code" outputId="09962313-45e0-4a91-d7d7-3e5a091b66f9" colab={"base_uri": "https://localhost:8080/", "height": 54}
sec1 = 'SELECT name \
           FROM "public"."titanic" \
           WHERE (name LIKE '
sec2 = "'Mr.%'"
sec3 = ' OR name LIKE '
sec4 = "'Mrs.%'"
sec5 = ') AND siblings > 0'
query14 = sec1 + sec2 + sec3 + sec4 + sec5
print(query14)

# + id="5pgwAFmIPt6y" colab_type="code" outputId="923a7337-0d03-42c5-9903-35d5776ce4cd" colab={"base_uri": "https://localhost:8080/", "height": 34}
answer = execute(query1, pg_conn)
print(answer[0][0], 'survivors on the Titanic')

# + id="uyRho9uyRe6G" colab_type="code" outputId="ea5275bd-6231-450f-8df6-2bbeb504dc8c" colab={"base_uri": "https://localhost:8080/", "height": 34}
answer = execute(query2, pg_conn)
print(answer[0][0], 'died on the Titanic')

# + id="dMEa1fNKU4EE" colab_type="code" outputId="f660f2c8-4f08-4d53-9ba1-fc6509779e3b" colab={"base_uri": "https://localhost:8080/", "height": 69}
answer = execute(query3, pg_conn)
print(answer[0][1], 'survivors from class', answer[0][0])
print(answer[1][1], 'survivors from class', answer[1][0])
print(answer[2][1], 'survivors from class', answer[2][0])

# + id="j5n5WBRoXAyc" colab_type="code" outputId="7b74dbcc-953b-41a9-d5e5-8bec9f41aaeb" colab={"base_uri": "https://localhost:8080/", "height": 69}
answer = execute(query4, pg_conn)
print(answer[0][1], 'died from class', answer[0][0])
print(answer[1][1], 'died from class', answer[1][0])
print(answer[2][1], 'died from class', answer[2][0])

# + id="fwdun7yvXxwc" colab_type="code" outputId="aa100c78-5caf-45df-d5f6-7263c92ed0d8" colab={"base_uri": "https://localhost:8080/", "height": 52}
answer = execute(query5, pg_conn)
print('average age on those who died was', answer[0][1])
print('average age on those who suvived was', answer[1][1])

# + id="bU_ZLvMCZqgW" colab_type="code" outputId="abfdaa82-1af9-4abf-b1d6-42d39aa84404" colab={"base_uri": "https://localhost:8080/", "height": 69}
answer = execute(query6, pg_conn)
print('class', answer[0][0], 'average age is', answer[0][1])
print('class', answer[1][0], 'average age is', answer[1][1])
print('class', answer[2][0], 'average age is', answer[2][1])

# + id="e_WIfJTIafVV" colab_type="code" outputId="69358b33-f9f3-4acc-d33c-e3616ca9f72e" colab={"base_uri": "https://localhost:8080/", "height": 69}
answer = execute(query7, pg_conn)
print('class', answer[0][0], 'average fare is', answer[0][1])
print('class', answer[1][0], 'average fare is', answer[1][1])
print('class', answer[2][0], 'average fare is', answer[2][1])

# + id="h8y6jncTbBz7" colab_type="code" outputId="915dbb0a-f618-4bf7-ce48-f78af8386255" colab={"base_uri": "https://localhost:8080/", "height": 52}
answer = execute(query8, pg_conn)
print('average fare of those who died is', answer[0][1])
print('average fare of those who survived is', answer[1][1])

# + id="-vSXDndScbNQ" colab_type="code" outputId="f25eb96d-a395-40d4-8f7d-f6bbe5013313" colab={"base_uri": "https://localhost:8080/", "height": 69}
answer = execute(query9, pg_conn)
print('class', answer[0][0], 'average # of siblings is', answer[0][1])
print('class', answer[1][0], 'average # of siblings is', answer[1][1])
print('class', answer[2][0], 'average # of siblings is', answer[2][1])

# + id="XgiV-lKMdF5k" colab_type="code" outputId="2b970b18-77d4-4df9-d88e-6f693c704cca" colab={"base_uri": "https://localhost:8080/", "height": 52}
answer = execute(query10, pg_conn)
print('average # siblings of those who died is', answer[0][1])
print('average # siblings of those who survived is', answer[1][1])

# + id="4wVqh1Fqdr8s" colab_type="code" outputId="7a13ee52-febd-4bc6-ae1b-8a99d2288bd3" colab={"base_uri": "https://localhost:8080/", "height": 69}
answer = execute(query11, pg_conn)
print('class', answer[0][0], 'average # of parents is', answer[0][1])
print('class', answer[1][0], 'average # of parents is', answer[1][1])
print('class', answer[2][0], 'average # of parents is', answer[2][1])

# + id="MX_oYjiAeDsL" colab_type="code" outputId="afa9da1f-d5d2-495d-91f8-a240344e6da8" colab={"base_uri": "https://localhost:8080/", "height": 52}
answer = execute(query12, pg_conn)
print('average # parents of those who died is', answer[0][1])
print('average # parents of those who survived is', answer[1][1])

# + id="DWwUzpDQevF3" colab_type="code" outputId="421a8d42-679a-4d6e-89bd-0b0ae0f5e666" colab={"base_uri": "https://localhost:8080/", "height": 52}
answer = execute(query13, pg_conn)
print(answer)
print('No duplicate names')

# + id="1VTNPamnhUAQ" colab_type="code" outputId="59a770f5-9271-4cd9-8fcc-00e4e4cd9a6c" colab={"base_uri": "https://localhost:8080/", "height": 54}
answer = execute(query14, pg_conn)
print(answer)

StarcoderdataJupyterScriptsDedupFiltered