{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "## Preparing Tables\n", "\n", "Let us prepare retail tables to come up with the solution for the problem statement." ] }, { "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": [ "* Ensure that we have required database and user for retail data. We might provide the database as part of our labs. Here are the instructions to use `psql` for setting up the required tables.\n", "\n", "```shell\n", "psql -U postgres -h localhost -p 5432 -W\n", "```\n", "\n", "```sql\n", "CREATE DATABASE itversity_retail_db;\n", "CREATE USER itversity_retail_user WITH ENCRYPTED PASSWORD 'retail_password';\n", "GRANT ALL ON DATABASE itversity_retail_db TO itversity_retail_user;\n", "```\n", "\n", "* Create Tables using the script provided. You can either use `psql` or **SQL Alchemy**.\n", "\n", "```shell\n", "psql -U itversity_retail_user \\\n", " -h localhost \\\n", " -p 5432 \\\n", " -d itversity_retail_db \\\n", " -W\n", "\n", "\\i /data/retail_db/create_db_tables_pg.sql\n", "```\n", "\n", "* Data shall be loaded using the script provided.\n", "\n", "```shell\n", "\\i /data/retail_db/load_db_tables_pg.sql\n", "```\n", "\n", "* Run queries to validate we have data in all the 3 tables." ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "%load_ext sql" ] }, { "cell_type": "code", "execution_count": 5, "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": 6, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
current_database
itversity_retail_db
" ], "text/plain": [ "[('itversity_retail_db',)]" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT current_database()" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "6 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", " \n", " \n", " \n", " \n", " \n", "
table_catalogtable_schematable_nametable_typeself_referencing_column_namereference_generationuser_defined_type_cataloguser_defined_type_schemauser_defined_type_nameis_insertable_intois_typedcommit_action
itversity_retail_dbpubliccategoriesBASE TABLENoneNoneNoneNoneNoneYESNONone
itversity_retail_dbpublicdepartmentsBASE TABLENoneNoneNoneNoneNoneYESNONone
itversity_retail_dbpublicproductsBASE TABLENoneNoneNoneNoneNoneYESNONone
itversity_retail_dbpubliccustomersBASE TABLENoneNoneNoneNoneNoneYESNONone
itversity_retail_dbpublicordersBASE TABLENoneNoneNoneNoneNoneYESNONone
itversity_retail_dbpublicorder_itemsBASE TABLENoneNoneNoneNoneNoneYESNONone
" ], "text/plain": [ "[('itversity_retail_db', 'public', 'categories', 'BASE TABLE', None, None, None, None, None, 'YES', 'NO', None),\n", " ('itversity_retail_db', 'public', 'departments', 'BASE TABLE', None, None, None, None, None, 'YES', 'NO', None),\n", " ('itversity_retail_db', 'public', 'products', 'BASE TABLE', None, None, None, None, None, 'YES', 'NO', None),\n", " ('itversity_retail_db', 'public', 'customers', 'BASE TABLE', None, None, None, None, None, 'YES', 'NO', None),\n", " ('itversity_retail_db', 'public', 'orders', 'BASE TABLE', None, None, None, None, None, 'YES', 'NO', None),\n", " ('itversity_retail_db', 'public', 'order_items', 'BASE TABLE', None, None, None, None, None, 'YES', 'NO', None)]" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM information_schema.tables \n", "WHERE table_catalog = 'itversity_retail_db' \n", " AND table_schema = 'public' \n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 8, "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
22013-07-25 00:00:00256PENDING_PAYMENT
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
82013-07-25 00:00:002911PROCESSING
92013-07-25 00:00:005657PENDING_PAYMENT
102013-07-25 00:00:005648PENDING_PAYMENT
" ], "text/plain": [ "[(1, datetime.datetime(2013, 7, 25, 0, 0), 11599, 'CLOSED'),\n", " (2, datetime.datetime(2013, 7, 25, 0, 0), 256, 'PENDING_PAYMENT'),\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", " (8, datetime.datetime(2013, 7, 25, 0, 0), 2911, 'PROCESSING'),\n", " (9, datetime.datetime(2013, 7, 25, 0, 0), 5657, 'PENDING_PAYMENT'),\n", " (10, datetime.datetime(2013, 7, 25, 0, 0), 5648, 'PENDING_PAYMENT')]" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT * FROM orders LIMIT 10" ] }, { "cell_type": "code", "execution_count": 9, "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
order_item_idorder_item_order_idorder_item_product_idorder_item_quantityorder_item_subtotalorder_item_product_price
119571299.98299.98
2210731199.99199.99
325025250.050.0
424031129.99129.99
54897249.9824.99
643655299.9559.99
745023150.050.0
8410144199.9249.98
959571299.98299.98
1053655299.9559.99
" ], "text/plain": [ "[(1, 1, 957, 1, 299.98, 299.98),\n", " (2, 2, 1073, 1, 199.99, 199.99),\n", " (3, 2, 502, 5, 250.0, 50.0),\n", " (4, 2, 403, 1, 129.99, 129.99),\n", " (5, 4, 897, 2, 49.98, 24.99),\n", " (6, 4, 365, 5, 299.95, 59.99),\n", " (7, 4, 502, 3, 150.0, 50.0),\n", " (8, 4, 1014, 4, 199.92, 49.98),\n", " (9, 5, 957, 1, 299.98, 299.98),\n", " (10, 5, 365, 5, 299.95, 59.99)]" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT * FROM order_items LIMIT 10" ] }, { "cell_type": "code", "execution_count": 10, "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
product_idproduct_category_idproduct_nameproduct_descriptionproduct_priceproduct_image
12Quest Q64 10 FT. x 10 FT. Slant Leg Instant U59.98http://images.acmesports.sports/Quest+Q64+10+FT.+x+10+FT.+Slant+Leg+Instant+Up+Canopy
22Under Armour Men's Highlight MC Football Clea129.99http://images.acmesports.sports/Under+Armour+Men%27s+Highlight+MC+Football+Cleat
32Under Armour Men's Renegade D Mid Football Cl89.99http://images.acmesports.sports/Under+Armour+Men%27s+Renegade+D+Mid+Football+Cleat
42Under Armour Men's Renegade D Mid Football Cl89.99http://images.acmesports.sports/Under+Armour+Men%27s+Renegade+D+Mid+Football+Cleat
52Riddell Youth Revolution Speed Custom Footbal199.99http://images.acmesports.sports/Riddell+Youth+Revolution+Speed+Custom+Football+Helmet
62Jordan Men's VI Retro TD Football Cleat134.99http://images.acmesports.sports/Jordan+Men%27s+VI+Retro+TD+Football+Cleat
72Schutt Youth Recruit Hybrid Custom Football H99.99http://images.acmesports.sports/Schutt+Youth+Recruit+Hybrid+Custom+Football+Helmet+2014
82Nike Men's Vapor Carbon Elite TD Football Cle129.99http://images.acmesports.sports/Nike+Men%27s+Vapor+Carbon+Elite+TD+Football+Cleat
92Nike Adult Vapor Jet 3.0 Receiver Gloves50.0http://images.acmesports.sports/Nike+Adult+Vapor+Jet+3.0+Receiver+Gloves
102Under Armour Men's Highlight MC Football Clea129.99http://images.acmesports.sports/Under+Armour+Men%27s+Highlight+MC+Football+Cleat
" ], "text/plain": [ "[(1, 2, 'Quest Q64 10 FT. x 10 FT. Slant Leg Instant U', '', 59.98, 'http://images.acmesports.sports/Quest+Q64+10+FT.+x+10+FT.+Slant+Leg+Instant+Up+Canopy'),\n", " (2, 2, \"Under Armour Men's Highlight MC Football Clea\", '', 129.99, 'http://images.acmesports.sports/Under+Armour+Men%27s+Highlight+MC+Football+Cleat'),\n", " (3, 2, \"Under Armour Men's Renegade D Mid Football Cl\", '', 89.99, 'http://images.acmesports.sports/Under+Armour+Men%27s+Renegade+D+Mid+Football+Cleat'),\n", " (4, 2, \"Under Armour Men's Renegade D Mid Football Cl\", '', 89.99, 'http://images.acmesports.sports/Under+Armour+Men%27s+Renegade+D+Mid+Football+Cleat'),\n", " (5, 2, 'Riddell Youth Revolution Speed Custom Footbal', '', 199.99, 'http://images.acmesports.sports/Riddell+Youth+Revolution+Speed+Custom+Football+Helmet'),\n", " (6, 2, \"Jordan Men's VI Retro TD Football Cleat\", '', 134.99, 'http://images.acmesports.sports/Jordan+Men%27s+VI+Retro+TD+Football+Cleat'),\n", " (7, 2, 'Schutt Youth Recruit Hybrid Custom Football H', '', 99.99, 'http://images.acmesports.sports/Schutt+Youth+Recruit+Hybrid+Custom+Football+Helmet+2014'),\n", " (8, 2, \"Nike Men's Vapor Carbon Elite TD Football Cle\", '', 129.99, 'http://images.acmesports.sports/Nike+Men%27s+Vapor+Carbon+Elite+TD+Football+Cleat'),\n", " (9, 2, 'Nike Adult Vapor Jet 3.0 Receiver Gloves', '', 50.0, 'http://images.acmesports.sports/Nike+Adult+Vapor+Jet+3.0+Receiver+Gloves'),\n", " (10, 2, \"Under Armour Men's Highlight MC Football Clea\", '', 129.99, 'http://images.acmesports.sports/Under+Armour+Men%27s+Highlight+MC+Football+Cleat')]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT * FROM products LIMIT 10" ] }, { "cell_type": "code", "execution_count": 11, "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": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT count(1) FROM orders" ] }, { "cell_type": "code", "execution_count": 12, "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
172198
" ], "text/plain": [ "[(172198,)]" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT count(1) FROM order_items" ] }, { "cell_type": "code", "execution_count": 13, "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
1345
" ], "text/plain": [ "[(1345,)]" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT count(1) FROM products" ] } ], "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 }