HR Management System — SQL Project

A MySQL-based project simulating a company's HR database, covering employees, departments, and job roles. Includes 17 real-world business queries — from basic filtering to joins, subqueries, aggregations, and window functions.

📊 Database Overview

3 Tables:

Table	Description
employees	Employee records — name, email, hire date, job, salary, manager, department
departments	Department name and location
jobs	Job titles with min/max salary range

Relationships:

• employees.department_id → departments.department_id
• employees.job_id → jobs.job_id
• employees.manager_id → employees.employee_id (self-referencing, for manager hierarchy)

🛠️ Tech Used

• MySQL

📁 Repository Structure

HR-Management-SQL-Project/
├── schema.sql      -- Table definitions (DDL)
├── data.sql        -- Sample data (DML)
├── queries.sql      -- All 17 solved business questions
└── README.md

▶️ How to Run

1. Run schema.sql to create the tables.
2. Run data.sql to insert sample records.
3. Run any query from queries.sql to test the results.

📋 Questions Solved

1. Employees earning more than 80,000
2. Employees assigned to the IT or Marketing departments
3. Employees who are not assigned to the IT department
4. Top 3 highest-paid employees
5. Employees whose first name starts with "A"
6. Employees in IT or HR department using IN
7. Employee summary report with readable alias names
8. Combined result set of employees and managers (self-join)
9. Count of employees in each department
10. Departments with more than 1 employee
11. All departments and their employees (LEFT JOIN)
12. Employees earning more than their department's average salary
13. Employee full name, job title, and department name
14. Employee with manager name (fallback for no manager)
15. Rank employees by salary company-wide
16. Rank employees by salary within each department
17. Top earner in every department using a CTE

👤 Author

Ashutosh Shrivastava