PostgreSQL Tasks

Task-1

This project demonstrates database creation, table setup, data insertion, and SQL querying using PostgreSQL with psql.

---

Deliverables

• schema.sql : SQL script to create 4 tables:

  • customers
  • products
  • orders
  • order_items

• insert_data.sql : SQL script to insert 10 rows into each table.

• query_screenshots/ : Folder containing screenshots of query results.

---

SQL Queries Executed

1. Get all customers from the East region
2. List products over $100, sorted by price (descending)
3. Show top 3 products by price
4. Show customers in alphabetical order

Screenshots of each query's output are saved inside the query_screenshots/ folder.

---

Task-2

This task focuses on applying SQL filters and conditions to retrieve meaningful business data from the database.

---

Deliverables

• filters_queries.sql : SQL script containing 5 business queries using filters and conditions.
• query_screenshots/ : Folder containing screenshots of query results.

---

SQL Queries Covered

1. Identify customers who haven't placed any orders
2. Find top 5 most expensive products
3. List all distinct product categories
4. Find all orders placed in the last 30 days
5. Show customers whose names start with 'A' or 'B'

---

Task-3

This task focuses on using SQL aggregation functions and grouping techniques to generate business reports from database tables.

---

Task-3 Deliverables

• aggregations.sql : SQL script containing 5 grouped reports using aggregation functions.
• query_screenshots/ : Folder containing screenshots of query results.

---

SQL Reports Generated

1. Total sales (SUM of total_amount) by region
2. Count of orders placed per customer
3. Average product price per product category
4. Top 3 customers ranked by total spend
5. List of products never sold (zero quantity in order_items)

---

Business Use Case

Identify the top 3 regions generating the highest total revenue in the past month to guide targeted marketing strategies

Task-4

This task demonstrates the use of SQL JOINs to fetch combined data from multiple tables and analyze relational dependencies between entities.

---

Deliverables

• joins_queries.sql : SQL script containing multi-table JOIN queries.
• query_screenshots/ : Folder containing screenshots of each query output.
• Brief explanations of JOIN behaviors included as SQL comments inside the SQL script.

---

SQL Queries Covered

1. Fetch Order Details:

   • Display Order ID, Customer Name, Product Name, and Quantity.

2. Identify Data Issues:

   • Find orders linked to missing products or invalid customers using OUTER JOIN techniques.

3. Customer Order Summary:

   • List all customers with their total number of orders.

4. Product Order Counts:

   • Show each product with the number of times it has been ordered (aggregate JOIN report).

5. JOIN Comparison:

   • Compare results of INNER JOIN vs LEFT JOIN between Customers and Orders to illustrate missing data handling.

Task-5

This task focuses on using subqueries and Common Table Expressions (CTEs) to perform complex multi-step data analysis using PostgreSQL.

---

📦 Task-5 Deliverables

• subqueries.sql : SQL script containing 5 analytical queries using subqueries and/or CTEs.
• query_screenshots/ : Folder containing screenshots of each query output.

---

📋 SQL Queries Covered

1. Customers Who Ordered Premium Products

   • Find customers who have ordered products priced above the average product price.

2. Unsold Products Report

   • Show products that were never ordered.

3. Repeat Customers

   • Identify customers who have placed more than one order.

4. Large Orders Analysis

   • List orders that contain more than 3 products.

5. High-Spending North Region Customers

   • From customers in the "North" region, list those whose total spend is above the regional average.

---

📚 SQL Concepts Applied

• Scalar Subqueries
• IN and EXISTS
• Correlated Subqueries
• Common Table Expressions (CTEs) using WITH
• Nested filtering and modular SQL design

---

📊 Why Subqueries & CTEs?

• Subqueries (inline & correlated) help in step-wise data filtering inside WHERE, SELECT, and FROM clauses.
• CTEs (WITH clause) simplify complex multi-stage queries and improve modularity.
• Encourages reusable and readable SQL code.