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

Solution – Daily Product Revenue Creating Tables and Indexes