Preparing Database

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.

psql -U postgres -h localhost -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 localhost \
  -p 5432 \
  -d itversity_retail_db \

\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 6 tables.

%load_ext sql
%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db
%sql SELECT * FROM departments LIMIT 10
%sql SELECT * FROM categories LIMIT 10
%sql SELECT * FROM products LIMIT 10
%sql SELECT * FROM orders LIMIT 10
%sql SELECT * FROM order_items LIMIT 10
%sql SELECT * FROM customers LIMIT 10