Learn SQL for Data Analysis

You will learn:

•Relevance of SQL for  Business Analysts and Reporting

•Why Learn SQL for Data Analysis?

•Analytical SQL vs Transactional SQL

•Data Cleaning Using SQL

•Aggregating Data for Reports

•Advanced Analytical SQL

•Subqueries & CTEs for Analysis

•Joins for Analysis

Data Cleaning Using SQL

--------------------------------------------------------------------------------
-- FILE: data_cleaning_demo.sql
-- PURPOSE: Demonstration of Data Cleaning using SQL (Oracle)
-- DATABASE: Oracle
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-- SECTION 1: REMOVE DUPLICATE RECORDS USING ROW_NUMBER()
--------------------------------------------------------------------------------

-- Step 1: Create sample table
DROP TABLE employees_raw PURGE;

CREATE TABLE employees_raw (
    emp_id NUMBER,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    email VARCHAR2(100),
    department_id NUMBER
);

-- Step 2: Insert sample data (with duplicates)
INSERT INTO employees_raw VALUES (101, 'John', 'Smith', 'john.smith@abc.com', 10);
INSERT INTO employees_raw VALUES (102, 'Emma', 'Jones', 'emma.jones@abc.com', 20);
INSERT INTO employees_raw VALUES (101, 'John', 'Smith', 'john.smith@abc.com', 10);
INSERT INTO employees_raw VALUES (103, 'Raj', 'Mehta', 'raj.mehta@abc.com', 30);
COMMIT;

select * from employees_raw;

-- Step 3: Identify duplicates using ROW_NUMBER()  
SELECT emp_id, first_name, last_name, email, department_id
FROM (
  SELECT e.*, ROW_NUMBER() OVER (PARTITION BY emp_id, email ORDER BY emp_id) AS rn
  FROM employees_raw e
)
WHERE rn = 1;

--------------------------------------------------------------------------------
-- SECTION 2: HANDLE MISSING (NULL) VALUES USING NVL()
--------------------------------------------------------------------------------

-- Step 1: Create new sample table
DROP TABLE emp_incentive PURGE;

CREATE TABLE emp_incentive (
    emp_id NUMBER,
    first_name VARCHAR2(50),
    salary NUMBER,
    commission_pct NUMBER
);

-- Step 2: Insert sample data (with NULLs)
INSERT INTO emp_incentive VALUES (201, 'Alice', 8000, 0.1);
INSERT INTO emp_incentive VALUES (202, 'Bob', 6000, NULL);
INSERT INTO emp_incentive VALUES (203, 'Carol', 9500, 0.15);
COMMIT;

select * from emp_incentive;

-- Step 3: Replace NULLs using NVL and calculate total pay

SELECT emp_id, first_name, salary, commission_pct, salary * commission_pct AS total_pay
FROM emp_incentive;


SELECT emp_id, first_name, salary,
       NVL(commission_pct, 0) AS commission_pct,
       salary + (salary * NVL(commission_pct, 0)) AS total_pay
FROM emp_incentive;

--------------------------------------------------------------------------------
-- SECTION 3: CLEANING STRING FIELDS USING TRIM, INITCAP, UPPER
--------------------------------------------------------------------------------

-- Step 1: Create sample table
DROP TABLE customers_raw PURGE;

CREATE TABLE customers_raw (
    customer_id VARCHAR2(10),
    customer_name VARCHAR2(100),
    region VARCHAR2(50)
);

-- Step 2: Insert inconsistent data
INSERT INTO customers_raw VALUES ('C01', '  John Smith  ', 'north');
INSERT INTO customers_raw VALUES ('C02', ' emma JONES  ', ' South ');
INSERT INTO customers_raw VALUES ('C03', 'raj Mehta', '  EAST');
COMMIT;

select * from customers_raw;

-- Step 3: Standardize text fields
SELECT customer_id,
       INITCAP(TRIM(customer_name)) AS clean_name,
       UPPER(TRIM(region)) AS region_std
FROM customers_raw;

--------------------------------------------------------------------------------
-- SECTION 4: CONVERT AND VALIDATE DATES USING REGEXP_LIKE + TO_DATE
--------------------------------------------------------------------------------

-- Step 1: Create sample table
DROP TABLE emp_dates PURGE;

CREATE TABLE emp_dates (
    emp_id NUMBER,
    name VARCHAR2(50),
    join_date VARCHAR2(20)
);

-- Step 2: Insert inconsistent date formats
INSERT INTO emp_dates VALUES (301, 'Priya', '2024-01-15');
INSERT INTO emp_dates VALUES (302, 'Arjun', '15/02/2024');
INSERT INTO emp_dates VALUES (303, 'Maria', 'FEB-25-24');
COMMIT;

select * from emp_dates;

-- Step 3: Convert various formats into a standard date
SELECT emp_id, name,
       CASE
         WHEN REGEXP_LIKE(join_date, '^\d{4}-\d{2}-\d{2}$')
           THEN TO_DATE(join_date, 'YYYY-MM-DD')
         WHEN REGEXP_LIKE(join_date, '^\d{2}/\d{2}/\d{4}$')
           THEN TO_DATE(join_date, 'DD/MM/YYYY')
         ELSE TO_DATE(join_date, 'MON-DD-YY')
       END AS valid_join_date
FROM emp_dates;

Aggregating Data for Reports

--------------------------------------------------------------------------------
-- FILE: data_aggregation_demo.sql
-- PURPOSE: Demonstration of Data Aggregation & Reporting using SQL (Oracle)
-- DATABASE: Oracle
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-- SECTION 1: SETUP - CREATE SAMPLE TABLE & DATA
--------------------------------------------------------------------------------

DROP TABLE employee_sales PURGE;

CREATE TABLE employee_sales (
    emp_id NUMBER,
    emp_name VARCHAR2(50),
    department VARCHAR2(30),
    region VARCHAR2(20),
    sales_amount NUMBER,
    target_amount NUMBER,
    joining_date DATE
);

INSERT INTO employee_sales VALUES (101, 'John Smith', 'Sales', 'NORTH', 120000, 100000, DATE '2022-01-10');
INSERT INTO employee_sales VALUES (102, 'Emma Jones', 'Sales', 'SOUTH', 90000, 95000, DATE '2021-12-01');
INSERT INTO employee_sales VALUES (103, 'Raj Mehta', 'Finance', 'EAST', 65000, 60000, DATE '2020-06-15');
INSERT INTO employee_sales VALUES (104, 'Priya Patel', 'Finance', 'WEST', 70000, 70000, DATE '2022-03-05');
INSERT INTO employee_sales VALUES (105, 'Arjun Nair', 'IT', 'NORTH', 110000, 100000, DATE '2019-08-20');
INSERT INTO employee_sales VALUES (106, 'Alice Brown', 'IT', 'SOUTH', 105000, 95000, DATE '2023-02-14');
INSERT INTO employee_sales VALUES (107, 'Bob Taylor', 'Sales', 'EAST', 98000, 85000, DATE '2022-10-12');
INSERT INTO employee_sales VALUES (108, 'Carol White', 'Finance', 'NORTH', 75000, 65000, DATE '2023-05-01');
COMMIT;

select * from employee_sales;

--------------------------------------------------------------------------------
-- SECTION 2: BASIC AGGREGATION FUNCTIONS
--------------------------------------------------------------------------------
-- Use SUM, AVG, MIN, MAX, COUNT for simple company-level reporting

SELECT
    COUNT(*) AS total_employees,
    SUM(sales_amount) AS total_sales,
    AVG(sales_amount) AS avg_sales,
    MIN(sales_amount) AS min_sales,
    MAX(sales_amount) AS max_sales
FROM employee_sales;

--------------------------------------------------------------------------------
-- SECTION 3: GROUP BY FOR DEPARTMENT-WISE ANALYSIS
--------------------------------------------------------------------------------

SELECT
    department,
    COUNT(*) AS emp_count,
    SUM(sales_amount) AS total_sales,
    ROUND(AVG(sales_amount), 2) AS avg_sales
FROM employee_sales
GROUP BY department
ORDER BY total_sales DESC;

-- "Group by department provides a quick overview of performance across business units."

--------------------------------------------------------------------------------
-- SECTION 4: GROUP BY MULTIPLE COLUMNS (DEPARTMENT + REGION)
--------------------------------------------------------------------------------

SELECT
    department,
    region,
    SUM(sales_amount) AS total_sales,
    ROUND(AVG(sales_amount), 2) AS avg_sales
FROM employee_sales
GROUP BY department, region
ORDER BY department, region;

-- "Combining multiple dimensions in GROUP BY supports multi-level reporting, similar to pivot tables."

--------------------------------------------------------------------------------
-- SECTION 5: FILTERING GROUP RESULTS WITH HAVING CLAUSE
--------------------------------------------------------------------------------

-- Show only departments with average sales above 90,000
SELECT
    department,
    ROUND(AVG(sales_amount), 2) AS avg_sales
FROM employee_sales
GROUP BY department
HAVING AVG(sales_amount) > 90000
ORDER BY avg_sales DESC;

-- "HAVING is like WHERE for grouped data—it filters after aggregation."

--------------------------------------------------------------------------------
-- SECTION 6: CONDITIONAL AGGREGATION USING CASE WHEN
--------------------------------------------------------------------------------

-- Count number of employees meeting or missing targets by department
SELECT
    department,
    SUM(CASE WHEN sales_amount >= target_amount THEN 1 ELSE 0 END) AS target_achieved,
    SUM(CASE WHEN sales_amount < target_amount THEN 1 ELSE 0 END) AS target_missed,
    COUNT(*) AS total_employees
FROM employee_sales
GROUP BY department
ORDER BY department;

-- "CASE WHEN inside aggregate functions enables you to calculate KPIs dynamically."

--------------------------------------------------------------------------------
-- SECTION 7: REPORTING BONUS BASED ON PERFORMANCE
--------------------------------------------------------------------------------

-- Real-world style analytical query combining aggregation and condition
SELECT
    emp_name,
    department,
    sales_amount,
    target_amount,
    CASE
      WHEN sales_amount >= 1.2 * target_amount THEN 'Gold Bonus'
      WHEN sales_amount BETWEEN target_amount AND (1.2 * target_amount) THEN 'Silver Bonus'
      ELSE 'No Bonus'
    END AS bonus_category
FROM employee_sales
ORDER BY department, bonus_category DESC;

-- "Business teams often categorize employees or regions based on target achievement tiers."

--------------------------------------------------------------------------------
-- SECTION 8: REGION-WISE TOTAL SALES WITH PERFORMANCE PERCENTAGE
--------------------------------------------------------------------------------

SELECT
    region,
    SUM(sales_amount) AS total_sales,
    SUM(target_amount) AS total_target,
    ROUND((SUM(sales_amount) / SUM(target_amount)) * 100, 2) AS performance_pct
FROM employee_sales
GROUP BY region
ORDER BY performance_pct DESC;

-- "Performance percentage KPIs are crucial for comparing regional effectiveness."

--------------------------------------------------------------------------------
-- SECTION 9: DEPARTMENTAL RANKING USING ANALYTICAL FUNCTION
--------------------------------------------------------------------------------

SELECT
    department,
    SUM(sales_amount) AS total_sales,
    RANK() OVER (ORDER BY SUM(sales_amount) DESC) AS dept_rank
FROM employee_sales
GROUP BY department;
-- "Combining GROUP BY with window functions lets you rank aggregate results directly."

Advanced Analytical SQL

--------------------------------------------------------------------------------
-- FILE: advanced_analytical_sql_demo.sql
-- PURPOSE: Demonstration of Advanced Analytical SQL using Window Functions
-- DATABASE: Oracle
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-- SECTION 1: SETUP - CREATE SAMPLE TABLE & DATA
--------------------------------------------------------------------------------

DROP TABLE employee_performance PURGE;

CREATE TABLE employee_performance (
    emp_id NUMBER,
    emp_name VARCHAR2(50),
    department VARCHAR2(30),
    region VARCHAR2(20),
    month_name VARCHAR2(10),
    sales_amount NUMBER
);

-- Insert realistic monthly performance data
INSERT INTO employee_performance VALUES (101, 'John Smith', 'Sales', 'NORTH', 'JAN', 120000);
INSERT INTO employee_performance VALUES (101, 'John Smith', 'Sales', 'NORTH', 'FEB', 115000);
INSERT INTO employee_performance VALUES (102, 'Emma Jones', 'Sales', 'SOUTH', 'JAN', 90000);
INSERT INTO employee_performance VALUES (102, 'Emma Jones', 'Sales', 'SOUTH', 'FEB', 95000);
INSERT INTO employee_performance VALUES (103, 'Raj Mehta', 'Finance', 'EAST', 'JAN', 65000);
INSERT INTO employee_performance VALUES (103, 'Raj Mehta', 'Finance', 'EAST', 'FEB', 67000);
INSERT INTO employee_performance VALUES (104, 'Priya Patel', 'Finance', 'WEST', 'JAN', 72000);
INSERT INTO employee_performance VALUES (104, 'Priya Patel', 'Finance', 'WEST', 'FEB', 69000);
INSERT INTO employee_performance VALUES (105, 'Arjun Nair', 'IT', 'NORTH', 'JAN', 110000);
INSERT INTO employee_performance VALUES (105, 'Arjun Nair', 'IT', 'NORTH', 'FEB', 115000);
INSERT INTO employee_performance VALUES (106, 'Alice Brown', 'IT', 'SOUTH', 'JAN', 108000);
INSERT INTO employee_performance VALUES (106, 'Alice Brown', 'IT', 'SOUTH', 'FEB', 112000);
COMMIT;

--------------------------------------------------------------------------------
-- SECTION 2: USE CASE 1 - RANK(): Top Performers by Department
--------------------------------------------------------------------------------

-- Management wants to identify top 3 earners in each department for incentive 
-- eligibility.

SELECT
    department,
    emp_name,
    SUM(sales_amount) AS total_sales,
    RANK() OVER (PARTITION BY department ORDER BY SUM(sales_amount) DESC) AS sales_rank
FROM employee_performance
GROUP BY department, emp_name
ORDER BY department, sales_rank;

-- "RANK() assigns the same rank to tied values and skips subsequent ranks — perfect for leaderboard reports."

--------------------------------------------------------------------------------
-- SECTION 3: USE CASE 2 - DENSE_RANK(): Continuous Ranking without Gaps
--------------------------------------------------------------------------------
-- HR wants to assign performance tiers (Gold/Silver/Bronze) 
-- without rank gaps for tied employees.

SELECT
    department,
    emp_name,
    SUM(sales_amount) AS total_sales,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY SUM(sales_amount) DESC) AS dense_rank
FROM employee_performance
GROUP BY department, emp_name
ORDER BY department, dense_rank;


-- "DENSE_RANK() ensures that rank numbers are sequential even when there are ties."

--------------------------------------------------------------------------------
-- SECTION 4: USE CASE 3 - LAG(): Compare Current Month vs Previous Month Performance
--------------------------------------------------------------------------------
-- Data analysts want to measure sales growth or drop compared to the previous month.

SELECT
    emp_name,
    department,
    month_name,
    sales_amount,
    LAG(sales_amount, 1) OVER (PARTITION BY emp_name ORDER BY month_name) AS prev_month_sales,
    sales_amount - LAG(sales_amount, 1) OVER (PARTITION BY emp_name ORDER BY month_name) AS sales_change
FROM employee_performance
ORDER BY emp_name, month_name;


-- "LAG() lets us access the previous row’s data without a self-join 
-- — ideal for month-to-month or quarter-to-quarter comparisons."

--------------------------------------------------------------------------------
-- SECTION 5: USE CASE 4 - LEAD(): Forecasting Next Month’s Trend
--------------------------------------------------------------------------------
-- Business team wants to view next month’s sales alongside the current month for planning.

SELECT
    emp_name,
    department,
    month_name,
    sales_amount,
    LEAD(sales_amount, 1) OVER (PARTITION BY emp_name ORDER BY month_name) AS next_month_sales,
    LEAD(sales_amount, 1) OVER (PARTITION BY emp_name ORDER BY month_name) - sales_amount AS expected_change
FROM employee_performance
ORDER BY emp_name, month_name;


-- "LEAD() helps to peek forward — useful for forecasting or comparing expected vs actual outcomes."

--------------------------------------------------------------------------------
-- SECTION 6: USE CASE 5 - PARTITION BY with AVG(): Departmental Comparison
--------------------------------------------------------------------------------

-- Management wants to know which employees are performing above or below their department’s average sales.

SELECT
    emp_name,
    department,
    SUM(sales_amount) AS total_sales,
    ROUND(AVG(SUM(sales_amount)) OVER (PARTITION BY department), 2) AS dept_avg_sales,
    CASE
      WHEN SUM(sales_amount) > AVG(SUM(sales_amount)) OVER (PARTITION BY department)
        THEN 'Above Average'
      ELSE 'Below Average'
    END AS performance_status
FROM employee_performance
GROUP BY department, emp_name
ORDER BY department, performance_status DESC;

-- "This pattern is powerful for comparative analysis — every employee is compared to their department’s average dynamically."

-------------------------------------------------------------------------------------
-- SECTION 7: USE CASE 6 - RANK() + LAG(): Identify Fastest Growing Employees
-------------------------------------------------------------------------------------
-- Analysts need to rank employees by growth percentage month-over-month.

WITH monthly_growth AS (
  SELECT
    emp_name,
    department,
    month_name,
    sales_amount,
    LAG(sales_amount) OVER (PARTITION BY emp_name ORDER BY month_name) AS prev_sales
  FROM employee_performance
)
SELECT
    emp_name,
    department,
    month_name,
    sales_amount,
    ROUND(((sales_amount - prev_sales) / prev_sales) * 100, 2) AS growth_pct,
    RANK() OVER (PARTITION BY department ORDER BY ((sales_amount - prev_sales) / prev_sales) DESC NULLS LAST) AS growth_rank
FROM monthly_growth
WHERE prev_sales IS NOT NULL
ORDER BY department, growth_rank;

-- "Combining LAG() with RANK() creates powerful performance growth leaderboards."

Subqueries & CTEs for Analysis

--------------------------------------------------------------------------------
-- FILE: subqueries_cte_analysis_demo.sql
-- PURPOSE: Demonstration of Subqueries & CTEs for Analytical SQL
-- DATABASE: Oracle
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-- SECTION 1: SETUP - CREATE SAMPLE TABLE AND DATA
--------------------------------------------------------------------------------

DROP TABLE employee_salary PURGE;

CREATE TABLE employee_salary (
    emp_id NUMBER,
    emp_name VARCHAR2(50),
    department VARCHAR2(30),
    designation VARCHAR2(30),
    salary NUMBER,
    hire_date DATE
);

-- Sample data for different departments and salary levels
INSERT INTO employee_salary VALUES (101, 'John Smith', 'Sales', 'Manager', 120000, DATE '2018-06-15');
INSERT INTO employee_salary VALUES (102, 'Emma Jones', 'Sales', 'Executive', 80000, DATE '2020-03-12');
INSERT INTO employee_salary VALUES (103, 'Raj Mehta', 'Finance', 'Analyst', 95000, DATE '2019-07-01');
INSERT INTO employee_salary VALUES (104, 'Priya Patel', 'Finance', 'Manager', 115000, DATE '2017-09-10');
INSERT INTO employee_salary VALUES (105, 'Alice Brown', 'IT', 'Developer', 90000, DATE '2021-02-22');
INSERT INTO employee_salary VALUES (106, 'Arjun Nair', 'IT', 'Lead', 130000, DATE '2016-05-19');
INSERT INTO employee_salary VALUES (107, 'Bob Taylor', 'Sales', 'Executive', 75000, DATE '2022-01-10');
INSERT INTO employee_salary VALUES (108, 'Carol White', 'Finance', 'Executive', 72000, DATE '2021-11-25');
INSERT INTO employee_salary VALUES (109, 'Ravi Kumar', 'IT', 'Developer', 88000, DATE '2020-08-03');
INSERT INTO employee_salary VALUES (110, 'Neha Sharma', 'Sales', 'Executive', 82000, DATE '2023-02-15');
COMMIT;

select * from employee_salary;

--------------------------------------------------------------------------------
-- SECTION 2: USE CASE 1 - FIND EMPLOYEES ABOVE DEPARTMENT AVERAGE SALARY (Subquery)
--------------------------------------------------------------------------------
-- HR wants to identify employees who earn more than their department’s average salary.

SELECT emp_name, department, salary
FROM employee_salary e
WHERE salary > (
    SELECT AVG(salary)
    FROM employee_salary
    WHERE department = e.department
)
ORDER BY department, salary DESC;

-- "This is a correlated subquery: it executes per department. Good for simple analytical filters."

--------------------------------------------------------------------------------
-- SECTION 3: SAME USE CASE USING CTE (Improved Readability)
--------------------------------------------------------------------------------

WITH dept_avg AS (
    SELECT department, AVG(salary) AS avg_sal
    FROM employee_salary
    GROUP BY department
)
SELECT e.emp_name, e.department, e.salary, d.avg_sal,
       CASE WHEN e.salary > d.avg_sal THEN 'Above Dept Avg'
            ELSE 'Below Dept Avg' END AS comparison
FROM employee_salary e
JOIN dept_avg d
ON e.department = d.department
ORDER BY e.department, e.salary DESC;

-- "Using a CTE makes your logic modular — easy to reuse the department averages in other queries."

--------------------------------------------------------------------------------
-- SECTION 4: USE CASE 2 - TOP 10% EMPLOYEES BY SALARY (Subquery vs CTE)
--------------------------------------------------------------------------------

-- Management wants to list the top 10% of earners across all departments.

-- Using Subquery
SELECT emp_id, emp_name, department, salary
FROM (
    SELECT emp_id, emp_name, department, salary,
           NTILE(10) OVER (ORDER BY salary DESC) AS decile_rank
    FROM employee_salary
)
WHERE decile_rank = 1
ORDER BY salary DESC;

-- Using CTE (same logic, clearer structure)
WITH salary_ranked AS (
    SELECT emp_id, emp_name, department, salary,
           NTILE(10) OVER (ORDER BY salary DESC) AS decile_rank
    FROM employee_salary
)
SELECT emp_id, emp_name, department, salary
FROM salary_ranked
WHERE decile_rank = 1
ORDER BY salary DESC;


-- "NTILE(10) splits data into 10 buckets — the first bucket is your top 10%. 
--  The CTE version reads like a step-by-step report."

--------------------------------------------------------------------------------
-- SECTION 5: USE CASE 3 - HIGHEST SALARY PER DEPARTMENT (Subquery vs CTE)
--------------------------------------------------------------------------------

-- Subquery Approach
SELECT emp_id, emp_name, department, salary
FROM employee_salary
WHERE (department, salary) IN (
    SELECT department, MAX(salary)
    FROM employee_salary
    GROUP BY department
)
ORDER BY department;

-- CTE Approach
WITH dept_max AS (
    SELECT department, MAX(salary) AS max_sal
    FROM employee_salary
    GROUP BY department
)
SELECT e.emp_name, e.department, e.salary
FROM employee_salary e
JOIN dept_max d
ON e.department = d.department AND e.salary = d.max_sal
ORDER BY e.department;

-- "Both work, but the CTE is clearer and can easily be extended for ranking or comparison analysis."

-------------------------------------------------------------------------------------
-- SECTION 6: USE CASE 4 - FIND EMPLOYEES HIRED BEFORE THEIR DEPARTMENT’S MANAGER
-------------------------------------------------------------------------------------

-- HR wants to check if any employee was hired before their department’s manager.

-- Subquery Version
SELECT emp_name, department, hire_date
FROM employee_salary e
WHERE hire_date < (
    SELECT MIN(hire_date)
    FROM employee_salary
    WHERE department = e.department AND designation = 'Manager'
)
ORDER BY department, hire_date;

-- CTE Version
WITH mgr_hire AS (
    SELECT department, MIN(hire_date) AS manager_hire_date
    FROM employee_salary
    WHERE designation = 'Manager'
    GROUP BY department
)
SELECT e.emp_name, e.department, e.hire_date, m.manager_hire_date
FROM employee_salary e
JOIN mgr_hire m
ON e.department = m.department
WHERE e.hire_date < m.manager_hire_date
ORDER BY e.department, e.hire_date;


-- "CTEs provide cleaner joins than nested subqueries — making HR timeline validations easier."

--------------------------------------------------------------------------------
-- SECTION 7: USE CASE 5 - MULTI-STEP ANALYSIS USING MULTIPLE CTEs
--------------------------------------------------------------------------------
-- Business wants to find departments where the average salary is above 90,000 
-- and list employees above their department average.

WITH dept_avg AS (
    SELECT department, AVG(salary) AS avg_sal
    FROM employee_salary
    GROUP BY department
),
high_avg_dept AS (
    SELECT department
    FROM dept_avg
    WHERE avg_sal > 90000
)
SELECT e.emp_name, e.department, e.salary
FROM employee_salary e
JOIN dept_avg d ON e.department = d.department
WHERE e.department IN (SELECT department FROM high_avg_dept)
  AND e.salary > d.avg_sal
ORDER BY e.department, e.salary DESC;

-- "Multiple CTEs act like modular steps in an analytical pipeline — making complex reports transparent."

Joins for Analysis

--------------------------------------------------------------------------------
-- FILE: joins_in_data_analysis_demo.sql
-- PURPOSE: Demonstration of SQL JOINs in Data Analytics / Reporting Use Cases
-- TRAINING: Learn SQL for Data Analysis (Corporate Session)

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-- SECTION 1: SETUP - CREATE SAMPLE TABLES
--------------------------------------------------------------------------------
-- 1. EMPLOYEES TABLE
--------------------------------------------------------------------------------
CREATE TABLE employees_da (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50),
    dept_id NUMBER,
    hire_date DATE
);

INSERT INTO employees_da VALUES (101, 'John Smith', 10, DATE '2019-04-12');
INSERT INTO employees_da VALUES (102, 'Emma Jones', 20, DATE '2020-07-15');
INSERT INTO employees_da VALUES (103, 'Ravi Kumar', 10, DATE '2021-01-09');
INSERT INTO employees_da VALUES (104, 'Priya Patel', 30, DATE '2018-11-23');
INSERT INTO employees_da VALUES (105, 'Arjun Nair', 40, DATE '2017-06-02');
INSERT INTO employees_da VALUES (106, 'Neha Sharma', 20, DATE '2022-03-14');
INSERT INTO employees_da VALUES (107, 'Rahul Verma', 50, DATE '2023-01-05');
INSERT INTO employees_da VALUES (108, 'Alice Brown', NULL, DATE '2023-05-10');

select * from employees_da;

--------------------------------------------------------------------------------
-- 2. DEPARTMENTS TABLE
--------------------------------------------------------------------------------
CREATE TABLE departments_da (
    dept_id NUMBER PRIMARY KEY,
    dept_name VARCHAR2(50),
    location VARCHAR2(30)
);

INSERT INTO departments_da VALUES (10, 'Sales', 'New York');
INSERT INTO departments_da VALUES (20, 'Finance', 'London');
INSERT INTO departments_da VALUES (30, 'IT', 'Bangalore');
INSERT INTO departments_da VALUES (40, 'HR', 'Delhi');
INSERT INTO departments_da VALUES (60, 'Marketing', 'Dubai'); -- dept with no employees

--------------------------------------------------------------------------------
-- 3. PROJECTS TABLE
--------------------------------------------------------------------------------
CREATE TABLE projects_da (
    project_id NUMBER PRIMARY KEY,
    project_name VARCHAR2(50),
    dept_id NUMBER
);

INSERT INTO projects_da VALUES (1001, 'CRM Implementation', 10);
INSERT INTO projects_da VALUES (1002, 'Payroll Automation', 20);
INSERT INTO projects_da VALUES (1003, 'Network Upgrade', 30);
INSERT INTO projects_da VALUES (1004, 'Employee Wellness', 40);

select * from projects_da;

--------------------------------------------------------------------------------    
-- 4. SALARY TABLE
--------------------------------------------------------------------------------
CREATE TABLE salary_da (
    emp_id NUMBER,
    salary NUMBER,
    bonus NUMBER
);

INSERT INTO salary_da VALUES (101, 120000, 10000);
INSERT INTO salary_da VALUES (102, 95000, 8000);
INSERT INTO salary_da VALUES (103, 88000, NULL);
INSERT INTO salary_da VALUES (104, 130000, 12000);
INSERT INTO salary_da VALUES (105, 70000, 5000);
INSERT INTO salary_da VALUES (106, 78000, 6000);
-- Employee 107 & 108 have no salary record (for LEFT JOIN demo)
COMMIT;

select * from salary_da;

--------------------------------------------------------------------------------
-- SECTION 2: DEMO 1 - INNER JOIN (Department-wise Salary Report)
--------------------------------------------------------------------------------
-- Use Case: Combine employee, department, and salary data for payroll analysis.

SELECT e.emp_name, d.dept_name, s.salary, s.bonus
FROM employees_da e
JOIN departments_da d ON e.dept_id = d.dept_id
JOIN salary_da s ON e.emp_id = s.emp_id
ORDER BY d.dept_name, s.salary DESC;

--INNER JOIN returns only those employees who exist in both EMPLOYEES and SALARY.

--------------------------------------------------------------------------------
-- SECTION 3: DEMO 2 - LEFT JOIN (Employees Without Salary or Project)
--------------------------------------------------------------------------------
-- Use Case: Identify employees missing salary details or project assignment.
select * from salary_da;

SELECT e.emp_name, d.dept_name, s.salary, p.project_name
FROM employees_da e
LEFT JOIN departments_da d ON e.dept_id = d.dept_id
LEFT JOIN salary_da s ON e.emp_id = s.emp_id
LEFT JOIN projects_da p ON e.dept_id = p.dept_id
WHERE s.salary IS NULL OR p.project_name IS NULL
ORDER BY e.emp_name;

-- LEFT JOIN ensures all employees are visible, even without salary/project.

--------------------------------------------------------------------------------
-- SECTION 4: DEMO 3 - RIGHT JOIN (Departments Without Employees)
--------------------------------------------------------------------------------
-- Use Case: Identify departments that currently have no employees assigned.

SELECT d.dept_id, d.dept_name, e.emp_name
FROM employees_da e
RIGHT JOIN departments_da d ON e.dept_id = d.dept_id
WHERE e.emp_name IS NULL;

-- RIGHT JOIN ensures departments appear even if no employees exist.

--------------------------------------------------------------------------------
-- SECTION 5: DEMO 4 - FULL OUTER JOIN (Combine All Employees & Departments)
--------------------------------------------------------------------------------
-- Use Case: Generate a full coverage view of all employees and departments.

SELECT NVL(e.emp_name, 'No Employee') AS emp_name,
       NVL(d.dept_name, 'No Department') AS dept_name,
       NVL(TO_CHAR(e.hire_date, 'YYYY-MM-DD'), '-') AS hire_date
FROM employees_da e
FULL OUTER JOIN departments_da d ON e.dept_id = d.dept_id
ORDER BY dept_name;

-- FULL OUTER JOIN helps in data completeness checks and audit reports.

--------------------------------------------------------------------------------
-- SECTION 6: DEMO 5 - SELF JOIN (Find Employees Paid More Than Department Peer)
--------------------------------------------------------------------------------
--  Use Case: Compare employee salaries within the same department.

SELECT e.emp_name AS employee, p.emp_name AS peer, e.department_id, e.salary, p.salary AS peer_salary
FROM (
    SELECT emp_id, emp_name, dept_id AS department_id, salary FROM salary_da
    JOIN employees_da USING (emp_id)
) e
JOIN (
    SELECT emp_id, emp_name, dept_id AS department_id, salary FROM salary_da
    JOIN employees_da USING (emp_id)
) p
ON e.department_id = p.department_id
AND e.salary > p.salary
ORDER BY e.department_id, e.salary DESC;

-- SELF JOIN is useful for internal comparisons and peer benchmarking.

--------------------------------------------------------------------------------
-- SECTION 7: DEMO 6 - COMBINED ANALYTICAL USE CASE
--------------------------------------------------------------------------------
-- Department-wise total salary, number of employees, and average bonus.

SELECT d.dept_name,
       COUNT(e.emp_id) AS total_employees,
       SUM(s.salary) AS total_salary,
       ROUND(AVG(NVL(s.bonus,0)),2) AS avg_bonus
FROM departments_da d
LEFT JOIN employees_da e ON e.dept_id = d.dept_id
LEFT JOIN salary_da s ON e.emp_id = s.emp_id
GROUP BY d.dept_name
ORDER BY total_salary DESC NULLS LAST;

-- Real-world analytic use case — combining JOINs with GROUP BY.