select job minsal from emp group by job 48 Display the department numbers with

Select job minsal from emp group by job 48 display

This preview shows page 3 - 5 out of 13 pages.

select job, min(sal) from emp group by job; 48. Display the department numbers with more than three employees in each dept. select deptno, count(*) from emp group by deptno having count(*)>3; 49. Display the various jobs along with total sal for each of the jobs where total sal is greater than 40000. 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. select job, count(*) from emp group by job having count(*)>3; 51. Display the name of emp who earns highest sal. select ename from emp where sal=(select max(sal) from emp); 52. Display the employee number and name of employee working as CLERK and earning highest salary among CLERKS. select empno, ename from emp where job='CLERK' and sal=(select max(sal) from emp where job='CLERK'); 53.Display the names of the salesman who earns a salary more than the highest salary of any clerk. select ename from emp where job=?SALESMAN? and sal > (select max(sal) from emp where job='CLERK'); 54. Display the names of clerks who earn salary more than that of James of that of sal lesser than that of Scott. select ename from emp where job='CLERK' and sal<(select sal from emp where ename='SCOTT') and sal>(select sal from emp where ename='JAMES'); 55. Display the names of employees who earn a Sal more than that of James or that of salary greater than that of Scott. select ename from emp where sal < (select sal from emp where ename='SCOTT') and sal > (select sal from emp where ename='JAMES'); Part – II SQL Interview Questions 1. Display the names of the employees who earn highest salary in their respective departments. select * from emp e where sal = (select max(sal) from emp where deptno=e.deptno) 2. Display the names of employees who earn highest salaries in their respective job groups. select * from emp e where sal in (select max(sal) from emp group by job having e.job=job) 3.Display the employee names who are working in accountings dept. select ename from emp where deptno = (select deptno from dept where dname=?ACCOUNTING?); (or) select ename from emp where deptno in (select deptno from dept where dname=?ACCOUNTING?); 4.Display the employee names who are working in Chicago. select ename from emp where deptno = (select deptno from dept where loc=?CHICAGO?); 5. Display the job groups having total salary greater then the maximum salary for managers. select job, sum(sal) from emp group by job having sum(sal) > (select max(sal) from emp where job='MANAGER');
Image of page 3
4 of 13 6. Display the names of employees from department number 10 with salary greater than that of any employee working in other departments. select ename,sal,deptno from emp e where deptno=10 and sal > any(select sal from emp where e.deptno!=deptno); 7. Display the names of employee from department number 10 with salary greater then that of all employee working in other departments.
Image of page 4
Image of page 5

You've reached the end of your free preview.

Want to read all 13 pages?

  • Spring '16
  • emp

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture