{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "## Filtering Data\n", "\n", "Let us understand how we can filter the data as part of our queries." ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "tags": [ "remove-cell" ] }, "outputs": [ { "data": { "text/html": [ "\n" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%%HTML\n", "" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "* We use `WHERE` clause to filter the data.\n", "* All comparison operators such as `=`, `!=`, `>`, `<`, `<=`, `>=` etc can be used to compare a column or expression or literal with another column or expression or literal.\n", "* We can use operators such as `LIKE` with `%` or `~` with regular expressions for pattern matching.\n", "* Boolean `OR` and `AND` can be performed when we want to apply multiple conditions.\n", " * Get all orders with order_status equals to COMPLETE or CLOSED. We can also use IN operator.\n", " * Get all orders from month 2014 January with order_status equals to COMPLETE or CLOSED\n", "* We can also use `BETWEEN` along with `AND` to compare a column or expression against range of values.\n", "* We need to use `IS NULL` and `IS NOT NULL` to compare against null values." ] }, { "cell_type": "code", "execution_count": 24, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The sql extension is already loaded. To reload it, use:\n", " %reload_ext sql\n" ] } ], "source": [ "%load_ext sql" ] }, { "cell_type": "code", "execution_count": 25, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db\n" ] } ], "source": [ "%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db" ] }, { "cell_type": "code", "execution_count": 26, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "10 rows affected.\n" ] }, { "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", "
order_idorder_dateorder_customer_idorder_status
32013-07-25 00:00:0012111COMPLETE
52013-07-25 00:00:0011318COMPLETE
62013-07-25 00:00:007130COMPLETE
72013-07-25 00:00:004530COMPLETE
152013-07-25 00:00:002568COMPLETE
172013-07-25 00:00:002667COMPLETE
222013-07-25 00:00:00333COMPLETE
262013-07-25 00:00:007562COMPLETE
282013-07-25 00:00:00656COMPLETE
322013-07-25 00:00:003960COMPLETE
" ], "text/plain": [ "[(3, datetime.datetime(2013, 7, 25, 0, 0), 12111, 'COMPLETE'),\n", " (5, datetime.datetime(2013, 7, 25, 0, 0), 11318, 'COMPLETE'),\n", " (6, datetime.datetime(2013, 7, 25, 0, 0), 7130, 'COMPLETE'),\n", " (7, datetime.datetime(2013, 7, 25, 0, 0), 4530, 'COMPLETE'),\n", " (15, datetime.datetime(2013, 7, 25, 0, 0), 2568, 'COMPLETE'),\n", " (17, datetime.datetime(2013, 7, 25, 0, 0), 2667, 'COMPLETE'),\n", " (22, datetime.datetime(2013, 7, 25, 0, 0), 333, 'COMPLETE'),\n", " (26, datetime.datetime(2013, 7, 25, 0, 0), 7562, 'COMPLETE'),\n", " (28, datetime.datetime(2013, 7, 25, 0, 0), 656, 'COMPLETE'),\n", " (32, datetime.datetime(2013, 7, 25, 0, 0), 3960, 'COMPLETE')]" ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "SELECT * FROM orders \n", "WHERE order_status = 'COMPLETE' \n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 27, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
count
68883
" ], "text/plain": [ "[(68883,)]" ] }, "execution_count": 27, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT count(1) FROM orders" ] }, { "cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
count
22899
" ], "text/plain": [ "[(22899,)]" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "SELECT count(1) \n", "FROM orders\n", "WHERE order_status = 'COMPLETE'" ] }, { "cell_type": "code", "execution_count": 29, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
order_status
COMPLETE
" ], "text/plain": [ "[('COMPLETE',)]" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "SELECT DISTINCT order_status\n", "FROM orders\n", "WHERE order_status = 'COMPLETE'" ] }, { "cell_type": "code", "execution_count": 30, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "9 rows affected.\n" ] }, { "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", "
order_status
COMPLETE
ON_HOLD
PENDING_PAYMENT
PENDING
CLOSED
CANCELED
PROCESSING
PAYMENT_REVIEW
SUSPECTED_FRAUD
" ], "text/plain": [ "[('COMPLETE',),\n", " ('ON_HOLD',),\n", " ('PENDING_PAYMENT',),\n", " ('PENDING',),\n", " ('CLOSED',),\n", " ('CANCELED',),\n", " ('PROCESSING',),\n", " ('PAYMENT_REVIEW',),\n", " ('SUSPECTED_FRAUD',)]" ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT DISTINCT order_status\n", "FROM orders" ] }, { "cell_type": "code", "execution_count": 31, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "10 rows affected.\n" ] }, { "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", "
order_idorder_dateorder_customer_idorder_status
12013-07-25 00:00:0011599CLOSED
32013-07-25 00:00:0012111COMPLETE
42013-07-25 00:00:008827CLOSED
52013-07-25 00:00:0011318COMPLETE
62013-07-25 00:00:007130COMPLETE
72013-07-25 00:00:004530COMPLETE
122013-07-25 00:00:001837CLOSED
152013-07-25 00:00:002568COMPLETE
172013-07-25 00:00:002667COMPLETE
182013-07-25 00:00:001205CLOSED
" ], "text/plain": [ "[(1, datetime.datetime(2013, 7, 25, 0, 0), 11599, 'CLOSED'),\n", " (3, datetime.datetime(2013, 7, 25, 0, 0), 12111, 'COMPLETE'),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 8827, 'CLOSED'),\n", " (5, datetime.datetime(2013, 7, 25, 0, 0), 11318, 'COMPLETE'),\n", " (6, datetime.datetime(2013, 7, 25, 0, 0), 7130, 'COMPLETE'),\n", " (7, datetime.datetime(2013, 7, 25, 0, 0), 4530, 'COMPLETE'),\n", " (12, datetime.datetime(2013, 7, 25, 0, 0), 1837, 'CLOSED'),\n", " (15, datetime.datetime(2013, 7, 25, 0, 0), 2568, 'COMPLETE'),\n", " (17, datetime.datetime(2013, 7, 25, 0, 0), 2667, 'COMPLETE'),\n", " (18, datetime.datetime(2013, 7, 25, 0, 0), 1205, 'CLOSED')]" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "SELECT * FROM orders \n", "WHERE order_status IN ('COMPLETE', 'CLOSED') \n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
count
30455
" ], "text/plain": [ "[(30455,)]" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT count(1) FROM orders \n", "WHERE order_status IN ('COMPLETE', 'CLOSED')" ] }, { "cell_type": "code", "execution_count": 33, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
count
30455
" ], "text/plain": [ "[(30455,)]" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "SELECT count(1) FROM orders \n", "WHERE order_status = 'COMPLETE' OR order_status = 'CLOSED'" ] }, { "cell_type": "code", "execution_count": 34, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "3 rows affected.\n" ] }, { "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", "
order_idorder_dateorder_customer_idorder_status
258762014-01-01 00:00:003414PENDING_PAYMENT
258772014-01-01 00:00:005549PENDING_PAYMENT
258782014-01-01 00:00:009084PENDING
" ], "text/plain": [ "[(25876, datetime.datetime(2014, 1, 1, 0, 0), 3414, 'PENDING_PAYMENT'),\n", " (25877, datetime.datetime(2014, 1, 1, 0, 0), 5549, 'PENDING_PAYMENT'),\n", " (25878, datetime.datetime(2014, 1, 1, 0, 0), 9084, 'PENDING')]" ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM orders\n", "WHERE order_date = '2014-01-01'\n", "LIMIT 3" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```{note}\n", "This query will not work as LIKE cannot be used to compare against columns with date data type\n", "```" ] }, { "cell_type": "code", "execution_count": 35, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "(psycopg2.errors.UndefinedFunction) operator does not exist: timestamp without time zone ~~ unknown\n", "LINE 2: WHERE order_date LIKE '2014-01%'\n", " ^\n", "HINT: No operator matches the given name and argument types. You might need to add explicit type casts.\n", "\n", "[SQL: SELECT * FROM orders\n", "WHERE order_date LIKE '2014-01%%'\n", "LIMIT 3]\n", "(Background on this error at: http://sqlalche.me/e/13/f405)\n" ] } ], "source": [ "%%sql\n", "\n", "SELECT * FROM orders\n", "WHERE order_date LIKE '2014-01%'\n", "LIMIT 3" ] }, { "cell_type": "code", "execution_count": 36, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "10 rows affected.\n" ] }, { "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", "
order_idorder_dateorder_customer_idorder_status
258822014-01-01 00:00:004598COMPLETE
258882014-01-01 00:00:006735COMPLETE
258892014-01-01 00:00:0010045COMPLETE
258912014-01-01 00:00:003037CLOSED
258952014-01-01 00:00:001044COMPLETE
258972014-01-01 00:00:006405COMPLETE
258982014-01-01 00:00:003950COMPLETE
258992014-01-01 00:00:008068CLOSED
259002014-01-01 00:00:002382CLOSED
259012014-01-01 00:00:003099COMPLETE
" ], "text/plain": [ "[(25882, datetime.datetime(2014, 1, 1, 0, 0), 4598, 'COMPLETE'),\n", " (25888, datetime.datetime(2014, 1, 1, 0, 0), 6735, 'COMPLETE'),\n", " (25889, datetime.datetime(2014, 1, 1, 0, 0), 10045, 'COMPLETE'),\n", " (25891, datetime.datetime(2014, 1, 1, 0, 0), 3037, 'CLOSED'),\n", " (25895, datetime.datetime(2014, 1, 1, 0, 0), 1044, 'COMPLETE'),\n", " (25897, datetime.datetime(2014, 1, 1, 0, 0), 6405, 'COMPLETE'),\n", " (25898, datetime.datetime(2014, 1, 1, 0, 0), 3950, 'COMPLETE'),\n", " (25899, datetime.datetime(2014, 1, 1, 0, 0), 8068, 'CLOSED'),\n", " (25900, datetime.datetime(2014, 1, 1, 0, 0), 2382, 'CLOSED'),\n", " (25901, datetime.datetime(2014, 1, 1, 0, 0), 3099, 'COMPLETE')]" ] }, "execution_count": 36, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM orders \n", "WHERE order_status IN ('COMPLETE', 'CLOSED')\n", " AND to_char(order_date, 'yyyy-MM-dd') LIKE '2014-01%'\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 37, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
count
2544
" ], "text/plain": [ "[(2544,)]" ] }, "execution_count": 37, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT count(1) FROM orders \n", "WHERE order_status IN ('COMPLETE', 'CLOSED')\n", " AND to_char(order_date, 'yyyy-MM-dd') LIKE '2014-01%'" ] }, { "cell_type": "code", "execution_count": 38, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "10 rows affected.\n" ] }, { "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", "
order_idorder_dateorder_customer_idorder_status
258822014-01-01 00:00:004598COMPLETE
258882014-01-01 00:00:006735COMPLETE
258892014-01-01 00:00:0010045COMPLETE
258912014-01-01 00:00:003037CLOSED
258952014-01-01 00:00:001044COMPLETE
258972014-01-01 00:00:006405COMPLETE
258982014-01-01 00:00:003950COMPLETE
258992014-01-01 00:00:008068CLOSED
259002014-01-01 00:00:002382CLOSED
259012014-01-01 00:00:003099COMPLETE
" ], "text/plain": [ "[(25882, datetime.datetime(2014, 1, 1, 0, 0), 4598, 'COMPLETE'),\n", " (25888, datetime.datetime(2014, 1, 1, 0, 0), 6735, 'COMPLETE'),\n", " (25889, datetime.datetime(2014, 1, 1, 0, 0), 10045, 'COMPLETE'),\n", " (25891, datetime.datetime(2014, 1, 1, 0, 0), 3037, 'CLOSED'),\n", " (25895, datetime.datetime(2014, 1, 1, 0, 0), 1044, 'COMPLETE'),\n", " (25897, datetime.datetime(2014, 1, 1, 0, 0), 6405, 'COMPLETE'),\n", " (25898, datetime.datetime(2014, 1, 1, 0, 0), 3950, 'COMPLETE'),\n", " (25899, datetime.datetime(2014, 1, 1, 0, 0), 8068, 'CLOSED'),\n", " (25900, datetime.datetime(2014, 1, 1, 0, 0), 2382, 'CLOSED'),\n", " (25901, datetime.datetime(2014, 1, 1, 0, 0), 3099, 'COMPLETE')]" ] }, "execution_count": 38, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM orders \n", "WHERE order_status IN ('COMPLETE', 'CLOSED')\n", " AND to_char(order_date, 'yyyy-MM') = '2014-01'\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 39, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
count
2544
" ], "text/plain": [ "[(2544,)]" ] }, "execution_count": 39, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT count(1) FROM orders \n", "WHERE order_status IN ('COMPLETE', 'CLOSED')\n", " AND to_char(order_date, 'yyyy-MM') = '2014-01'" ] }, { "cell_type": "code", "execution_count": 40, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
count
2544
" ], "text/plain": [ "[(2544,)]" ] }, "execution_count": 40, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT count(1) FROM orders \n", "WHERE order_status IN ('COMPLETE', 'CLOSED')\n", " AND to_char(order_date, 'yyyy-MM-dd') ~ '2014-01'" ] }, { "cell_type": "code", "execution_count": 41, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
countminmaxcount_1
75942014-01-01 00:00:002014-03-31 00:00:0089
" ], "text/plain": [ "[(7594, datetime.datetime(2014, 1, 1, 0, 0), datetime.datetime(2014, 3, 31, 0, 0), 89)]" ] }, "execution_count": 41, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT count(1), min(order_date), max(order_date), count(DISTINCT order_date) \n", "FROM orders \n", "WHERE order_status IN ('COMPLETE', 'CLOSED')\n", " AND order_date BETWEEN '2014-01-01' AND '2014-03-31'" ] }, { "cell_type": "code", "execution_count": 42, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "30 rows affected.\n" ] }, { "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", "
order_date
2014-03-01 00:00:00
2014-03-02 00:00:00
2014-03-03 00:00:00
2014-03-04 00:00:00
2014-03-05 00:00:00
2014-03-06 00:00:00
2014-03-07 00:00:00
2014-03-08 00:00:00
2014-03-10 00:00:00
2014-03-11 00:00:00
2014-03-12 00:00:00
2014-03-13 00:00:00
2014-03-14 00:00:00
2014-03-15 00:00:00
2014-03-16 00:00:00
2014-03-17 00:00:00
2014-03-18 00:00:00
2014-03-19 00:00:00
2014-03-20 00:00:00
2014-03-21 00:00:00
2014-03-22 00:00:00
2014-03-23 00:00:00
2014-03-24 00:00:00
2014-03-25 00:00:00
2014-03-26 00:00:00
2014-03-27 00:00:00
2014-03-28 00:00:00
2014-03-29 00:00:00
2014-03-30 00:00:00
2014-03-31 00:00:00
" ], "text/plain": [ "[(datetime.datetime(2014, 3, 1, 0, 0),),\n", " (datetime.datetime(2014, 3, 2, 0, 0),),\n", " (datetime.datetime(2014, 3, 3, 0, 0),),\n", " (datetime.datetime(2014, 3, 4, 0, 0),),\n", " (datetime.datetime(2014, 3, 5, 0, 0),),\n", " (datetime.datetime(2014, 3, 6, 0, 0),),\n", " (datetime.datetime(2014, 3, 7, 0, 0),),\n", " (datetime.datetime(2014, 3, 8, 0, 0),),\n", " (datetime.datetime(2014, 3, 10, 0, 0),),\n", " (datetime.datetime(2014, 3, 11, 0, 0),),\n", " (datetime.datetime(2014, 3, 12, 0, 0),),\n", " (datetime.datetime(2014, 3, 13, 0, 0),),\n", " (datetime.datetime(2014, 3, 14, 0, 0),),\n", " (datetime.datetime(2014, 3, 15, 0, 0),),\n", " (datetime.datetime(2014, 3, 16, 0, 0),),\n", " (datetime.datetime(2014, 3, 17, 0, 0),),\n", " (datetime.datetime(2014, 3, 18, 0, 0),),\n", " (datetime.datetime(2014, 3, 19, 0, 0),),\n", " (datetime.datetime(2014, 3, 20, 0, 0),),\n", " (datetime.datetime(2014, 3, 21, 0, 0),),\n", " (datetime.datetime(2014, 3, 22, 0, 0),),\n", " (datetime.datetime(2014, 3, 23, 0, 0),),\n", " (datetime.datetime(2014, 3, 24, 0, 0),),\n", " (datetime.datetime(2014, 3, 25, 0, 0),),\n", " (datetime.datetime(2014, 3, 26, 0, 0),),\n", " (datetime.datetime(2014, 3, 27, 0, 0),),\n", " (datetime.datetime(2014, 3, 28, 0, 0),),\n", " (datetime.datetime(2014, 3, 29, 0, 0),),\n", " (datetime.datetime(2014, 3, 30, 0, 0),),\n", " (datetime.datetime(2014, 3, 31, 0, 0),)]" ] }, "execution_count": 42, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT DISTINCT order_date\n", "FROM orders\n", "WHERE to_char(order_date, 'yyyy-MM') LIKE '2014-03%'\n", "ORDER BY order_date" ] }, { "cell_type": "code", "execution_count": 43, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "Done.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 43, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "DROP TABLE IF EXISTS users" ] }, { "cell_type": "code", "execution_count": 44, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "Done.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 44, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "CREATE TABLE users (\n", " user_id SERIAL PRIMARY KEY,\n", " user_first_name VARCHAR(30) NOT NULL,\n", " user_last_name VARCHAR(30) NOT NULL,\n", " user_email_id VARCHAR(50) NOT NULL,\n", " user_email_validated BOOLEAN DEFAULT FALSE,\n", " user_password VARCHAR(200),\n", " user_role VARCHAR(1) NOT NULL DEFAULT 'U', --U and A\n", " is_active BOOLEAN DEFAULT FALSE,\n", " create_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n", " last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n", ");" ] }, { "cell_type": "code", "execution_count": 45, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 45, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "INSERT INTO users (user_first_name, user_last_name, user_email_id)\n", "VALUES ('Donald', 'Duck', 'donald@duck.com')" ] }, { "cell_type": "code", "execution_count": 46, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 46, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "INSERT INTO users (user_first_name, user_last_name, user_email_id, user_role, is_active)\n", "VALUES ('Mickey', 'Mouse', 'mickey@mouse.com', 'U', true)" ] }, { "cell_type": "code", "execution_count": 47, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "3 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 47, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "INSERT INTO users \n", " (user_first_name, user_last_name, user_email_id, user_password, user_role, is_active) \n", "VALUES \n", " ('Gordan', 'Bradock', 'gbradock0@barnesandnoble.com', 'h9LAz7p7ub', 'U', true),\n", " ('Tobe', 'Lyness', 'tlyness1@paginegialle.it', 'oEofndp', 'U', true),\n", " ('Addie', 'Mesias', 'amesias2@twitpic.com', 'ih7Y69u56', 'U', true)" ] }, { "cell_type": "code", "execution_count": 48, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "5 rows affected.\n" ] }, { "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", "
user_iduser_first_nameuser_last_nameuser_email_iduser_email_validateduser_passworduser_roleis_activecreate_tslast_updated_ts
1DonaldDuckdonald@duck.comFalseNoneUFalse2020-11-14 15:38:53.3529842020-11-14 15:38:53.352984
2MickeyMousemickey@mouse.comFalseNoneUTrue2020-11-14 15:38:54.3694022020-11-14 15:38:54.369402
3GordanBradockgbradock0@barnesandnoble.comFalseh9LAz7p7ubUTrue2020-11-14 15:38:55.2602502020-11-14 15:38:55.260250
4TobeLynesstlyness1@paginegialle.itFalseoEofndpUTrue2020-11-14 15:38:55.2602502020-11-14 15:38:55.260250
5AddieMesiasamesias2@twitpic.comFalseih7Y69u56UTrue2020-11-14 15:38:55.2602502020-11-14 15:38:55.260250
" ], "text/plain": [ "[(1, 'Donald', 'Duck', 'donald@duck.com', False, None, 'U', False, datetime.datetime(2020, 11, 14, 15, 38, 53, 352984), datetime.datetime(2020, 11, 14, 15, 38, 53, 352984)),\n", " (2, 'Mickey', 'Mouse', 'mickey@mouse.com', False, None, 'U', True, datetime.datetime(2020, 11, 14, 15, 38, 54, 369402), datetime.datetime(2020, 11, 14, 15, 38, 54, 369402)),\n", " (3, 'Gordan', 'Bradock', 'gbradock0@barnesandnoble.com', False, 'h9LAz7p7ub', 'U', True, datetime.datetime(2020, 11, 14, 15, 38, 55, 260250), datetime.datetime(2020, 11, 14, 15, 38, 55, 260250)),\n", " (4, 'Tobe', 'Lyness', 'tlyness1@paginegialle.it', False, 'oEofndp', 'U', True, datetime.datetime(2020, 11, 14, 15, 38, 55, 260250), datetime.datetime(2020, 11, 14, 15, 38, 55, 260250)),\n", " (5, 'Addie', 'Mesias', 'amesias2@twitpic.com', False, 'ih7Y69u56', 'U', True, datetime.datetime(2020, 11, 14, 15, 38, 55, 260250), datetime.datetime(2020, 11, 14, 15, 38, 55, 260250))]" ] }, "execution_count": 48, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM users" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```{note}\n", "This will not return any thing and not the correct way to compare against NULL.\n", "NULL is specially treated by databases and it is not same as empty string.\n", "```" ] }, { "cell_type": "code", "execution_count": 49, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "0 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
user_iduser_first_nameuser_last_nameuser_email_iduser_email_validateduser_passworduser_roleis_activecreate_tslast_updated_ts
" ], "text/plain": [ "[]" ] }, "execution_count": 49, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM users\n", "WHERE user_password = NULL" ] }, { "cell_type": "code", "execution_count": 50, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "2 rows affected.\n" ] }, { "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", "
user_iduser_first_nameuser_last_nameuser_email_iduser_email_validateduser_passworduser_roleis_activecreate_tslast_updated_ts
1DonaldDuckdonald@duck.comFalseNoneUFalse2020-11-14 15:38:53.3529842020-11-14 15:38:53.352984
2MickeyMousemickey@mouse.comFalseNoneUTrue2020-11-14 15:38:54.3694022020-11-14 15:38:54.369402
" ], "text/plain": [ "[(1, 'Donald', 'Duck', 'donald@duck.com', False, None, 'U', False, datetime.datetime(2020, 11, 14, 15, 38, 53, 352984), datetime.datetime(2020, 11, 14, 15, 38, 53, 352984)),\n", " (2, 'Mickey', 'Mouse', 'mickey@mouse.com', False, None, 'U', True, datetime.datetime(2020, 11, 14, 15, 38, 54, 369402), datetime.datetime(2020, 11, 14, 15, 38, 54, 369402))]" ] }, "execution_count": 50, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM users\n", "WHERE user_password IS NULL" ] }, { "cell_type": "code", "execution_count": 51, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "3 rows affected.\n" ] }, { "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", "
user_iduser_first_nameuser_last_nameuser_email_iduser_email_validateduser_passworduser_roleis_activecreate_tslast_updated_ts
3GordanBradockgbradock0@barnesandnoble.comFalseh9LAz7p7ubUTrue2020-11-14 15:38:55.2602502020-11-14 15:38:55.260250
4TobeLynesstlyness1@paginegialle.itFalseoEofndpUTrue2020-11-14 15:38:55.2602502020-11-14 15:38:55.260250
5AddieMesiasamesias2@twitpic.comFalseih7Y69u56UTrue2020-11-14 15:38:55.2602502020-11-14 15:38:55.260250
" ], "text/plain": [ "[(3, 'Gordan', 'Bradock', 'gbradock0@barnesandnoble.com', False, 'h9LAz7p7ub', 'U', True, datetime.datetime(2020, 11, 14, 15, 38, 55, 260250), datetime.datetime(2020, 11, 14, 15, 38, 55, 260250)),\n", " (4, 'Tobe', 'Lyness', 'tlyness1@paginegialle.it', False, 'oEofndp', 'U', True, datetime.datetime(2020, 11, 14, 15, 38, 55, 260250), datetime.datetime(2020, 11, 14, 15, 38, 55, 260250)),\n", " (5, 'Addie', 'Mesias', 'amesias2@twitpic.com', False, 'ih7Y69u56', 'U', True, datetime.datetime(2020, 11, 14, 15, 38, 55, 260250), datetime.datetime(2020, 11, 14, 15, 38, 55, 260250))]" ] }, "execution_count": 51, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM users\n", "WHERE user_password IS NOT NULL" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "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.6.12" } }, "nbformat": 4, "nbformat_minor": 4 }