Exercises - Basic SQL Queries

Here are some of the exercises for which you can write SQL queries to self evaluate.

  • 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 database (if required) and tables.

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

psql -U itversity_retail_user \
  -h localhost \
  -p 5432 \
  -d itversity_retail_db \
  -W
  • You can drop the existing tables.

DROP TABLE order_items;
DROP TABLE orders;
DROP TABLE customers;
DROP TABLE products;
DROP TABLE categories;
DROP TABLE departments;
  • Once the tables are dropped you can run below script to create the tables for the purpose of exercises.

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

Exercise 1 - Customer order count

Get order count per customer for the month of 2014 January.

  • Tables - orders and customers

  • Data should be sorted in descending order by count and ascending order by customer id.

  • Output should contain customer_id, customer_first_name, customer_last_name and customer_order_count.

Exercise 2 - Dormant Customers

Get the customer details who have not placed any order for the month of 2014 January.

  • Tables - orders and customers

  • Data should be sorted in ascending order by customer_id

  • Output should contain all the fields from customers

Exercise 3 - Revenue Per Customer

Get the revenue generated by each customer for the month of 2014 January

  • Tables - orders, order_items and customers

  • Data should be sorted in descending order by revenue and then ascending order by customer_id

  • Output should contain customer_id, customer_first_name, customer_last_name, customer_revenue.

  • If there are no orders placed by customer, then the corresponding revenue for a give customer should be 0.

  • Consider only COMPLETE and CLOSED orders

Exercise 4 - Revenue Per Category

Get the revenue generated for each category for the month of 2014 January

  • Tables - orders, order_items, products and categories

  • Data should be sorted in ascending order by category_id.

  • Output should contain all the fields from category along with the revenue as category_revenue.

  • Consider only COMPLETE and CLOSED orders

Exercise 5 - Product Count Per Department

Get the products for each department.

  • Tables - departments, categories, products

  • Data should be sorted in ascending order by department_id

  • Output should contain all the fields from department and the product count as product_count