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
Query Performance Tuning Interpreting Explain Plans