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 
  -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 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