Durga Gadiraju

Preparing Tables in PostgreSQL

Preparing Tables¶

Let us prepare retail tables to come up with the solution for the problem statement.

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 as part of self support lab which is setup using Docker.
Launch Terminal. File -> New -> Terminal (using Jupyter Notebook)
```
psql -U postgres -h pg.itversity.com -p 5432 -W
```

CREATE DATABASE itversity_retail_db;
CREATE USER itversity_retail_user WITH ENCRYPTED PASSWORD 'retail_password';
GRANT ALL ON DATABASE itversity_retail_db TO itversity_retail_user;

Create Tables using the script provided. You can either use psql or SQL Alchemy.

psql -U itversity_retail_user \
  -h pg.itversity.com \
  -p 5432 \
  -d itversity_retail_db \
  -W

\i /data/retail_db/create_db_tables_pg.sql

Data shall be loaded using the script provided.

\i /data/retail_db/load_db_tables_pg.sql

Run queries to validate we have data in all the 3 tables.

In [11]:

%reload_ext sql

In [10]:

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db

In [12]:

%sql SELECT current_database()

1 rows affected.

Out[12]:

current_database
itversity_retail_db

In [13]:

%%sql

SELECT * FROM information_schema.tables 
WHERE table_catalog = 'itversity_retail_db' 
    AND table_schema = 'public' 
LIMIT 10

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
6 rows affected.

Out[13]:

table_catalog	table_schema	table_name	table_type	self_referencing_column_name	reference_generation	user_defined_type_catalog	user_defined_type_schema	user_defined_type_name	is_insertable_into	is_typed	commit_action
itversity_retail_db	public	departments	BASE TABLE	None	None	None	None	None	YES	NO	None
itversity_retail_db	public	categories	BASE TABLE	None	None	None	None	None	YES	NO	None
itversity_retail_db	public	products	BASE TABLE	None	None	None	None	None	YES	NO	None
itversity_retail_db	public	customers	BASE TABLE	None	None	None	None	None	YES	NO	None
itversity_retail_db	public	orders	BASE TABLE	None	None	None	None	None	YES	NO	None
itversity_retail_db	public	order_items	BASE TABLE	None	None	None	None	None	YES	NO	None

In [14]:

%sql SELECT * FROM orders LIMIT 10

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
10 rows affected.

Out[14]:

order_id	order_date	order_customer_id	order_status
1	2013-07-25 00:00:00	11599	CLOSED
2	2013-07-25 00:00:00	256	PENDING_PAYMENT
3	2013-07-25 00:00:00	12111	COMPLETE
4	2013-07-25 00:00:00	8827	CLOSED
5	2013-07-25 00:00:00	11318	COMPLETE
6	2013-07-25 00:00:00	7130	COMPLETE
7	2013-07-25 00:00:00	4530	COMPLETE
8	2013-07-25 00:00:00	2911	PROCESSING
9	2013-07-25 00:00:00	5657	PENDING_PAYMENT
10	2013-07-25 00:00:00	5648	PENDING_PAYMENT

In [15]:

%sql SELECT * FROM order_items LIMIT 10

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
10 rows affected.

Out[15]:

order_item_id	order_item_order_id	order_item_product_id	order_item_quantity	order_item_subtotal	order_item_product_price
1	1	957	1	299.98	299.98
2	2	1073	1	199.99	199.99
3	2	502	5	250.0	50.0
4	2	403	1	129.99	129.99
5	4	897	2	49.98	24.99
6	4	365	5	299.95	59.99
7	4	502	3	150.0	50.0
8	4	1014	4	199.92	49.98
9	5	957	1	299.98	299.98
10	5	365	5	299.95	59.99

In [16]:

%sql SELECT * FROM products LIMIT 10

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
10 rows affected.

Out[16]:

product_id	product_category_id	product_name	product_price	product_image
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
2	2	Under Armour Men’s Highlight MC Football Clea	129.99	http://images.acmesports.sports/Under+Armour+Men%27s+Highlight+MC+Football+Cleat
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
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
5	2	Riddell Youth Revolution Speed Custom Footbal	199.99	http://images.acmesports.sports/Riddell+Youth+Revolution+Speed+Custom+Football+Helmet
6	2	Jordan Men’s VI Retro TD Football Cleat	134.99	http://images.acmesports.sports/Jordan+Men%27s+VI+Retro+TD+Football+Cleat
7	2	Schutt Youth Recruit Hybrid Custom Football H	99.99	http://images.acmesports.sports/Schutt+Youth+Recruit+Hybrid+Custom+Football+Helmet+2014
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
9	2	Nike Adult Vapor Jet 3.0 Receiver Gloves	50.0	http://images.acmesports.sports/Nike+Adult+Vapor+Jet+3.0+Receiver+Gloves
10	2	Under Armour Men’s Highlight MC Football Clea	129.99	http://images.acmesports.sports/Under+Armour+Men%27s+Highlight+MC+Football+Cleat

In [17]:

%sql SELECT count(1) FROM orders

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[17]:

count
68883

In [18]:

%sql SELECT count(1) FROM order_items

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[18]:

count
172198

In [19]:

%sql SELECT count(1) FROM products

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[19]:

count
1345

In [ ]:

Durga Gadiraju

Preparing Tables in PostgreSQL

Preparing Tables¶

Share this post

Join Our Community

Follow Us

Links

Contact Info

Address

Phone

Email