Sql Server Interview Questions and Answers Part 5

31.Display the various jobs and total number of employees within each job group.
        select job,count(job)from EMPTABLE group by job;
 
32.Display the depart numbers and total salary for each department.
          select deptno,sum(sal) from EMPTABLE group by deptno;
 
33.Display the depart numbers and max salary for each department.
         select deptno,max(sal) from EMPTABLE group by deptno;
 
 34.Display the various jobs and total salary for each job
           select job,sum(sal) from EMPTABLE group by job;
 
35.Display the depart numbers with more than three employees in each dept.
           SQL>select deptno,count(deptno) from EMPTABLE group by deptno having count(*)>3;
 
36.Display the various jobs along with total salary for each of the jobs where total salary is greater than 40000.
            select job,sum(sal) from EMPTABLE group by job having sum(sal)>40000;
 
37.Display the various jobs along with total number of employees in each job.
           select job,count(EMPTABLEno) from EMPTABLE group by job;
 
38.Display job wise EMPTABLEloys count and only those jobs with more than three employees.
          select job,count(EMPTABLEno) from EMPTABLE group by job having count(job)>3
 
39.Display the EMPTABLE number and name for EMPTABLE working as clerk and earning highest salary among clerks.
         Select EMPTABLEno,ename from EMPTABLE where job=’CLERK’ and sal=(select max(sal) from EMPTABLE where job=’CLERK’);
 
40.Display the names of clerks who earn a salary more than the lowest salary of any salesman.
         Select ename from EMPTABLE where job=’clerk’ and sal<(select min(sal) from EMPTABLE where job =’SALSEMAE’);





Subscribe

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

Delivered by FeedBurner

Protected by Copyscape Duplicate Content Checker