Skip to content

About

Advanced SQL project demonstrating JOINs, subqueries, window functions, string and date functions, and CASE statements for data querying and transformation.

Topics

Resources

Stars

0 stars

Watchers

0 watching

Forks

Latest commit

 

History

3 Commits

Folders and files

NameName
Last commit message
Last commit date
 
 
 
 

Repository files navigation

🔄 Data Transformer — Advanced SQL Project

A comprehensive SQL project demonstrating advanced querying techniques — JOINs, Subqueries, Window Functions, String Functions, Date Functions, and CASE statements — across a real-world Customers, Orders and Employees database.


📌 Project Overview

This project builds and queries a relational database with 3 tables and covers 17 advanced SQL operations — going beyond basic CRUD to explore data transformation, ranking, running totals, conditional logic, and string manipulation used in real Data Analyst workflows.


🗂️ Database Schema

data_transformer
│
├── customers    → CustomerID, First_name, Last_name, Email, Registration_date
├── orders       → OrderID, CustomerID, Order_date, Total_amount
└── employees    → employeeID, firstname, lastname, department, hiredate, salary

🚀 Operations Covered (17 Queries)

🔗 JOIN Operations

# Query Type
1 Get customer name with their orders INNER JOIN
2 All customers including those with no orders LEFT JOIN
3 All orders including unmatched customers RIGHT JOIN
4 Complete data from both tables FULL JOIN (UNION)

🔍 Subqueries

# Query Description
5 Customers who spent above average WHERE Total_amount > AVG()
6 Employees earning above average salary WHERE salary > AVG()

📅 Date Functions

# Query Function Used
7 Extract Year & Month from Order date YEAR(), MONTH()
8 Days between order date & today DATEDIFF()
9 Format date to DD-MM-YY DATE_FORMAT()

🔤 String Functions

# Query Function Used
10 Combine First & Last name CONCAT()
11 Replace a name value REPLACE()
12 Convert name to UPPER/LOWER case UPPER(), LOWER()
13 Remove extra spaces from email TRIM()

🪟 Window Functions

# Query Function Used
14 Running total of order amounts SUM() OVER()
15 Rank orders by total amount RANK() OVER()

⚖️ CASE Statements

# Query Description
16 Assign discount based on order amount CASE WHEN >= 1000 THEN 10% OFF
17 Categorize employee salaries CASE WHEN HIGH / MEDIUM / LOW

🛠️ Tech Stack

  • Database: MySQL
  • Concepts: DDL, DML, JOINs, Subqueries, Window Functions, String Functions, Date Functions, CASE
  • Tool: MySQL Workbench

📁 Project Structure

sql-data-transformer/
│
├── Project_Data_Trasformer.sql   # Complete SQL script
└── README.md                     # Project documentation

▶️ How to Run

  1. Clone the repository
git clone https://github.com/ravindra-data/sql-data-transformer.git
  1. Open MySQL Workbench

  2. Open Project_Data_Trasformer.sql and run it

  3. Database data_transformer will be created with all tables and queries!


📸 Sample Queries & Output

-- Running Total of Orders
SELECT OrderID, Total_amount,
SUM(Total_amount) OVER (ORDER BY OrderID) AS Running_total
FROM orders;
-- Output: 500.25 → 625.30 → 1402.40 → 1522.51 → 2172.51

-- Salary Category using CASE
SELECT firstname, salary,
CASE
    WHEN salary >= 75000 THEN 'HIGH'
    WHEN salary BETWEEN 60000 AND 75000 THEN 'MEDIUM'
    ELSE 'LOW'
END AS Salary_Category FROM employees;

-- Discount based on Order Amount
-- >= 1000 → 10% OFF | >= 500 → 5% OFF | else → No Discount

💡 What I Learned

  • Writing all 4 types of JOINs — INNER, LEFT, RIGHT, FULL (UNION)
  • Using Subqueries to filter data based on aggregate values
  • Applying Window Functions — RANK() and SUM() OVER() for running totals
  • Transforming strings using CONCAT(), REPLACE(), UPPER(), LOWER(), TRIM()
  • Formatting and calculating dates using DATEDIFF(), DATE_FORMAT(), YEAR(), MONTH()
  • Implementing conditional logic using CASE WHEN statements

About

Advanced SQL project demonstrating JOINs, subqueries, window functions, string and date functions, and CASE statements for data querying and transformation.

Topics

Resources

Stars

0 stars

Watchers

0 watching

Forks

Releases

Packages

Contributors