Interview questions on emp and dept table (part 1)

Frequently asked questions about the emp (employee) and dept (department) tables, along with examples of SQL queries for each:

Click the link to find the Emp and dept table records

Find employees who have the highest salary in their job title category

SELECT e1.job, e1.ename, e1.sal
FROM emp e1
WHERE e1.sal = (
    SELECT MAX(sal)
    FROM emp e2
    WHERE e2.job = e1.job
);

Find employees whose salary is between two specific amounts (e.g., 1000 and 3000)

SELECT empno, ename, sal
FROM emp
WHERE sal BETWEEN 1000 AND 3000;

Find the total salary and total commission for each department.

SELECT d.dname, SUM(e.sal) AS total_salary, SUM(e.comm) AS total_commission
FROM emp e
JOIN dept d ON e.deptno = d.deptno
GROUP BY d.dname;

Find the oldest employee in each department.

SELECT e.deptno, e.ename, e.hiredate
FROM emp e
WHERE e.hiredate = (
    SELECT MIN(hiredate)
    FROM emp
    WHERE deptno = e.deptno
);

Find the department with the highest total commission.

SELECT d.dname, SUM(e.comm) AS total_commission
FROM emp e
JOIN dept d ON e.deptno = d.deptno
GROUP BY d.dname
ORDER BY total_commission DESC
LIMIT 1;

Find employees whose names start with a specific letter (e.g., 'J').

SELECT empno, ename
FROM emp
WHERE ename LIKE 'J%';

Find departments where the average salary is less than a specific amount (e.g., 2000).

SELECT d.dname, AVG(e.sal) AS avg_salary
FROM emp e
JOIN dept d ON e.deptno = d.deptno
GROUP BY d.dname
HAVING AVG(e.sal) < 2000;

Find the top 3 highest paid employees in the company.

SELECT empno, ename, sal
FROM emp
ORDER BY sal DESC
LIMIT 3;

Click below links for more examples on Emp and dept table.
 Interview questions on emp and dept table (part 1)
Interview questions on emp and dept table (part 2)
Interview questions on emp and dept table (part 3)
Interview questions on emp and dept table (part 4)
Tricky interview questions employee and manager column in emp table
Tricky interview questions employee and manager column in emp table