🔍 Top 15 SQL Queries for Employee and Department Analysis

🔍 Analytical SQL Questions & Answers for Employees and Departments

Explore these practical SQL queries to analyze employee and department data effectively.

1. 🧮 Total number of employees in each department
   

   SELECT department_id, COUNT(*) AS total_employees
   FROM employees
   GROUP BY department_id;

2. 💰 Departments with the highest average employee salary
   

   SELECT department_id, AVG(salary) AS avg_salary
   FROM employees
   GROUP BY department_id
   ORDER BY avg_salary DESC;

3. 📈 Employees earning more than the average salary of their department
   

   SELECT e.employee_id, e.employee_name, e.salary, e.department_id
   FROM employees e
   WHERE e.salary > (
       SELECT AVG(salary)
       FROM employees
       WHERE department_id = e.department_id
   );

4. 🔢 Departments with more than 10 employees
   

   SELECT department_id, COUNT(*) AS employee_count
   FROM employees
   GROUP BY department_id
   HAVING COUNT(*) > 10;

5. 🏆 Employee with the highest salary in each department
   

   WITH RankedSalaries AS (
     SELECT employee_id, employee_name, salary, department_id,
            ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
     FROM employees
   )
   SELECT employee_id, employee_name, salary, department_id
   FROM RankedSalaries
   WHERE rank = 1;

6. 🚫 Number of departments with no employees
   

   SELECT COUNT(*) AS departments_with_no_employees
   FROM departments d
   LEFT JOIN employees e ON d.department_id = e.department_id
   WHERE e.employee_id IS NULL;

7. 🧑‍🤝‍🧑 Employee names with department names, including those without a department
   

   SELECT e.employee_name, d.department_name
   FROM employees e
   LEFT JOIN departments d ON e.department_id = d.department_id;

8. 📊 Salary distribution (min, max, avg) by department
   

   SELECT department_id,
          MIN(salary) AS min_salary,
          MAX(salary) AS max_salary,
          AVG(salary) AS avg_salary
   FROM employees
   GROUP BY department_id;

9. 🌍 Employees working in departments located in a specific city
   

   SELECT e.employee_name, d.department_name, d.location
   FROM employees e
   JOIN departments d ON e.department_id = d.department_id
   WHERE d.location = 'Dhaka';

10. ❌ Departments where max salary is less than 50,000
    

    SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING MAX(salary) < 50000;

11. 🥇 Department with the largest number of employees
    

    SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id
    ORDER BY employee_count DESC
    LIMIT 1;

12. 🧑‍💼 Employees with the same job title as their department manager
    

    SELECT e.employee_name, e.job_title, d.department_name
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    JOIN employees m ON d.manager_id = m.employee_id
    WHERE e.job_title = m.job_title;

13. 🗓️ Departments with employees hired in the last 6 months
    

    SELECT DISTINCT d.department_id, d.department_name
    FROM departments d
    JOIN employees e ON d.department_id = e.department_id
    WHERE e.hire_date >= ADD_MONTHS(SYSDATE, -6);

14. 📐 Percentage of employees per department relative to total employees
    

    SELECT department_id,
           COUNT(*) AS employee_count,
           ROUND((COUNT(*) * 100.0) / (SELECT COUNT(*) FROM employees), 2) AS percentage_of_total
    FROM employees
    GROUP BY department_id;

15. 🔄 Employees belonging to multiple departments or recently transferred
    

    SELECT employee_id, COUNT(DISTINCT department_id) AS department_count
    FROM employee_department_history
    GROUP BY employee_id
    HAVING COUNT(DISTINCT department_id) > 1;

employees, departments, SQL queries, select query, group by, having clause, join, employee salary, department analysis, analytical questions, database tutorial, SQL practice, oracle SQL, employee management, hire date, salary distribution, department manager, employee transfers