SQL Queries ask in Interviews Part 4

46) Display the various jobs and total salary for each job
SQL>select job,sum(sal) from emp group by job;

47) Display the various jobs and total salary for each job
SQL>select job,min(sal) from emp group by job;

48) Display the depart numbers with more than three employees in each dept.
SQL>select deptno,count(deptno) from emp group by deptno having count(*)>3;

49) Display the various jobs along with total salary for each of the jobs where total salary is greater than 40000.
SQL>select job,sum(sal) from emp group by job having sum(sal)>40000;

50) Display the various jobs along with total number of employees in each job.The output should contain only those  jobs with more than three employees.
SQL>select job,count(empno) from emp group by job having count(job)>3;

51) Display the name of the employee who earns highest salary.
SQL>select ename from emp where sal=(select max(sal) from emp);

52) Display the employee number and name for employee working as clerk and earning highest salary among clerks.
SQL>select empno,ename from emp where where job=’CLERK’ and sal=(select max(sal) from emp  where job=’CLERK’);

53) Display the names of salesman who earns a salary more than the highest salary of any clerk.
SQL>select ename,sal from emp where job=’SALESMAN’ and sal>(select max(sal) from emp where job=’CLERK’);

54) Display the names of clerks who earn a salary more than the lowest salary of any salesman.
SQL>select ename from emp where job=’CLERK’ and sal>(select min(sal) from emp where job=’SALESMAN’);

55) Display the names of the employees who earn highest salary in their respective departments.
SQL>select ename,sal,deptno from emp where sal in(select max(sal) from emp group by deptno);

56) Display the names of the employees who earn highest salaries in their respective job groups.
SQL>select ename,sal,job from emp where sal in(select max(sal) from emp group by job);

57) Display the employee names who are working in accounting department.
SQL>select ename from emp where deptno=(select deptno from dept where dname=’ACCOUNTING’);

58) Display the employee names who are working in Ahmedabad .
SQL>select ename from emp where deptno=(select deptno from dept where LOC=’Ahmedabad’);

59) Display the Job groups having total salary greater than the maximum salary for managers.
SQL>SELECT JOB,SUM(SAL) FROM EMP GROUP BY JOB HAVING SUM(SAL)>(SELECT MAX(SAL) FROM EMP WHERE JOB=’MANAGER’);

60) Display the names of employees from department number 10 with salary greater than that of any employee working in other department.
SQL>select ename from emp where deptno=10 and sal>any(select sal from emp where deptno not in 10);

<<<Previous        Continue>>>>