Sql Server Interview Questions and Answers Part 6

41.Display the names of the employees who earn highest salary in their respective departments.
        Select * from EMPTABLE e where sal =(select max(sal) form EMPTABLE where deptno=e.deptno)
 
42.Display the names of the employees who earn highest salaries in their respective job groups.
       Select * from EMPTABLE  e where sal  in(select max(sal) form EMPTABLE group by having  e.job=job).
 
43. Display the Job groups having total salary greater than the maximum salary for managers.
       SELECT JOB,SUM(SAL) FROM EMPTABLE GROUP BY JOB HAVING SUM(SAL)>(SELECT MAX(SAL) FROM EMPTABLE WHERE JOB='MANAGER');
 
44. Display the names of employees from department number 10 with salary greater than that of any EMPTABLE working in other department.
       select ename from EMPTABLE where deptno=10 and sal>any(select sal from EMPTABLE where deptno not in 10).
 
45.  Display the names of the employees from department number 10 with salary greater than that of all EMPTABLE working in other departments.
     select ename from EMPTABLE where deptno=10 and sal>all(select sal from EMPTABLE where deptno not in 10).
 
46.Display name of those EMPTABLE who are getting the highest salary?
      select ename from EMPTABLE where sal=(select max(sal) from EMPTABLE);
 
47. Display those EMPTABLE whose salary is equal to average of maximum and minimum?
        select ename from EMPTABLE where sal=(select max(sal)+min(sal)/2 from EMPTABLE);
 
48.  Select count of EMPTABLE in each department  where count greater than 3?
        Select count(*) from EMPTABLE group by deptno having count(deptno)>3
 
49. Display dname where at least 3 are working and display only department name?
         select distinct d.dname from dept d,EMPTABLE e where d.deptno=e.deptno and 3>any (select count(deptno) from EMPTABLE group by deptno)
 
50. Display name of those managers name whose salary is more than average salary of his company?
              SELECT E.ENAME,EMPTABLE.ENAME FROM EMPTABLE,EMPTABLE E WHERE EMPTABLE.EMPTABLENO=E.MGR AND E.SAL>(SELECT AVG(SAL) FROM EMPTABLE);





Subscribe

Receive Quality Tutorials Straight in your Inbox by submitting your Email below:

Delivered by FeedBurner

Protected by Copyscape Duplicate Content Checker