Mini SQL Project| Dataplexa
MySQL Logo
Beginner Level ▶
Intermediate Level ▶
Advanced Level ▶

Mini SQL Project

Congratulations 🎉 You have reached the final lesson of the Dataplexa SQL course.

This mini project combines Beginner, Intermediate, and Advanced SQL into a single real-world scenario.

This is how SQL is actually used in professional environments.

Project Scenario

You are working as a Data Analyst for a growing organization.

The company wants a unified reporting system to analyze employees, departments, performance, and structured data.

Database Schema

departments

+----+------------+
| id | name       |
+----+------------+
| 1  | IT         |
| 2  | HR         |
| 3  | Finance    |
+----+------------+

employees

+----+----------+---------------+--------+-------------------------------+
| id | name     | department_id | salary | profile (JSON)               |
+----+----------+---------------+--------+-------------------------------+
| 1  | Alice    | 1             | 75000  | {"role":"Dev","city":"NY"}   |
| 2  | Bob      | 1             | 72000  | {"role":"Dev","city":"SF"}   |
| 3  | Charlie  | 2             | 60000  | {"role":"HR","city":"NY"}    |
| 4  | Diana    | 3             | 80000  | {"role":"Fin","city":"TX"}   |
| 5  | Evan     | NULL          | 55000  | {"role":"Temp","city":"CA"}  |
+----+----------+---------------+--------+-------------------------------+

Project Goals

  1. Combine employees with departments
  2. Handle missing relationships
  3. Rank salaries by department
  4. Query JSON data
  5. Create reusable views
  6. Optimize performance

Step 1: Employee & Department Report

SELECT e.name,
       d.name AS department,
       e.salary
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.id;

LEFT JOIN ensures employees without departments are still included.

Step 2: Employees Without Departments

SELECT name
FROM employees
WHERE department_id IS NULL;

Step 3: Salary Ranking (Window Functions)

SELECT name,
       department_id,
       salary,
       RANK() OVER (
         PARTITION BY department_id
         ORDER BY salary DESC
       ) AS salary_rank
FROM employees;

Step 4: Querying JSON Data

SELECT name,
       profile->>'$.city' AS city
FROM employees
WHERE profile->>'$.city' = 'NY';

Step 5: Create a Reporting View

CREATE VIEW employee_full_report AS
SELECT e.name,
       d.name AS department,
       e.salary,
       e.profile
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.id;

Step 6: Performance Optimization

CREATE INDEX idx_emp_dept
ON employees(department_id);

Concepts Used

  • JOINs and NULL handling
  • Window functions
  • JSON querying
  • Views
  • Indexes

🎉 Congratulations!

You have successfully completed the Dataplexa SQL Course.

Click the button below to celebrate your achievement 🚀

What You Achieved

  • Built real SQL projects
  • Optimized queries and indexes
  • Used advanced SQL features
  • Completed an end-to-end SQL course

You are now ready for real-world SQL roles.

← Previous Lesson SQL Index