Analytical Questions on Employees and Departments Using SELECT Queries
Here are some analytical questions you can explore using SQL SELECT queries on Employee and Department data. These will help you understand how to extract meaningful insights from your database.
- How can we find the total number of employees in each department?
- Which departments have the highest average employee salary?
- List all employees who earn more than the average salary of their department.
- Find departments that have more than 10 employees.
- Get the details of the employee with the highest salary in each department.
- How many departments currently have no employees?
- Retrieve employee names along with their department names, including those employees not assigned to any department.
- What is the salary distribution (minimum, maximum, average) across different departments?
Hints: Read Lab-1 from " https://www.techarticle.blog/2025/07/oracle-sqlpl-sql-syllabus.html "
Try writing SQL queries for these questions to sharpen your data analysis skills!
1) SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;
āĻāĻ¤্āĻ¤āĻ°āĻŽুāĻুāĻ¨2) SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC;
3)SELECT *
FROM employees e , department d
WHERE salary > (SELECT AVG(salary) FROM employees WHERE d.department_id = e.department_id);
4) SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;
5) SELECT *
FROM employees e
WHERE salary = (SELECT MAX(salary) FROM employees WHERE department_id = e.department_id);
6) SELECT *
FROM departments
WHERE department_id NOT IN (SELECT department_id FROM employees);
7) SELECT e.name AS employee_name,
d.name AS department_name
FROM employees e, department d
JOIN departments d ON e.department_id = d.department_id;
8) SELECT department_id,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
Answer
āĻāĻ¤্āĻ¤āĻ°āĻŽুāĻুāĻ¨1. SELECT d.dept_name, COUNT(e.emp_id) AS total_employees FROM departments d JOIN employees e ON d.dept_id = e.dept_id GROUP BY d.dept_name;
2. SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id ORDER BY avg_salary DESC FETCH FIRST 1 ROWS ONLY;
3. SELECT e.emp_id, e.emp_name, e.salary, e.dept_id FROM employees e JOIN ( SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id ) a ON e.dept_id = a.dept_id WHERE e.salary > a.avg_salary;
4. SELECT dept_id, COUNT(*) AS employee_count FROM employees GROUP BY dept_id HAVING COUNT(*) > 10;
5. SELECT e.emp_id, e.emp_name, e.salary, e.dept_id FROM employees e WHERE (e.dept_id, e.salary) IN (SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id );
6. SELECT COUNT(*) AS departments_without_employees FROM departments d JOIN employees e ON d.dept_id = e.dept_id WHERE e.emp_id IS NULL;
7. SELECT e.emp_name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id;
8. SELECT d.dept_name, MIN(e.salary) AS min_salary, MAX(e.salary) AS max_salary, AVG(e.salary) AS avg_salary FROM departments d JOIN employees e ON d.dept_id = e.dept_id GROUP BY d.dept_name;
1. SELECT department_id, COUNT(*) AS total_employees
āĻāĻ¤্āĻ¤āĻ°āĻŽুāĻুāĻ¨FROM employees
GROUP BY department_id
3.
SELECT e.*
FROM employees e
JOIN (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
) a ON e.department_id = a.department_id
WHERE e.salary > a.avg_salary;
4. SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;.
6. SELECT COUNT(*)
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;
7. SELECT e.employee_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;.
8. SELECT department_id, MIN(salary), MAX(salary), AVG(salary)
FROM employees
GROUP BY department_id;.
1. SELECT d.dept_name, COUNT(e.emp_id) AS total_employees FROM dept d JOIN emp e ON d.dept_id = e.dept_id GROUP BY d.dept_name;
āĻāĻ¤্āĻ¤āĻ°āĻŽুāĻুāĻ¨2. SELECT dept_id, AVG(salary) AS avg_salary FROM emp GROUP BY dept_id ORDER BY avg_salary DESC FETCH FIRST 1 ROWS ONLY;
3. SELECT e.emp_id, e.emp_name, e.salary, e.dept_id FROM emp e JOIN (SELECT dept_id, AVG(salary) AS avg_salary FROM emp GROUP BY dept_id) a ON e.dept_id = a.dept_id WHERE e.salary > a.avg_salary;
4. SELECT dept_id, COUNT() AS employee_count FROM emp GROUP BY dept_id HAVING COUNT() > 10;
5. SELECT e.emp_id, e.emp_name, e.salary, e.dept_id FROM emp e WHERE (e.dept_id, e.salary) IN (SELECT dept_id, MAX(salary) FROM emp GROUP BY dept_id);
6. SELECT COUNT(*) AS departments_without_employees FROM dept d LEFT JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id IS NULL;
7. SELECT e.emp_name, d.dept_name FROM emp e LEFT JOIN dept d ON e.dept_id = d.dept_id;
8. SELECT d.dept_name, MIN(e.salary) AS min_salary, MAX(e.salary) AS max_salary, AVG(e.salary) AS avg_salary FROM dept d JOIN emp e ON d.dept_id = e.dept_id GROUP BY d.dept_name;