GROUPING

1. Display the deptno which is having the maximum salary and maximum salary more than
1500 earned by the employee and job description not as a clerk and sort by descending
order.
select job,deptno,max(sal) from emp
where sal>1500
group by deptno,job
having job NOT IN ('CLERK')
order by max(Sal) desc
2. List the number of employee in each department, except 30, sorted high to low. Only
include department with 3 or more employee.
SELECT COUNT(ENAME),DEPTNO FROM EMP
WHERE DEPTNO NOT IN(30)
GROUP BY DEPTNO
3. Sql query to find second highest salary of employee
SELECT ENAME,MAX(SAL) FROM EMP E
WHERE 1=(SELECT COUNT(DISTINCT SAL) FROM EMP E1 WHERE E.SAL<E1.SAL)
GROUP BY ENAME

OR

SELECT MAX(SAL) FROM EMP WHERE SAL<(SELECT MAX(SAL) FROM EMP)

4. Display the number of employees who has annual salary more than 10000 department-
wise
SELECT COUNT(ENAME),SAL,SAL*12 "ANNUAL_SAL",DEPTNO FROM EMP
GROUP BY DEPTNO,SAL
5. Display the max salary for each of the job excluding all the employee whose having
commission.
SELECT MAX(SAL),JOB,COMM FROM EMP
GROUP BY JOB,COMM
HAVING COMM IS NULL
6. Find the total salary department number wise where more than two employees exits?

7. Display job wise and department wise least salary only if the least salary is less than
3000 in department 10,30?

8. list all the employees except those who are working in Dept 30 and 20?
9. Display the department number which are having more than 200 as their commission
along with employees whose name having 'A' is one of character.
10.Display department number and total salary whose average salary is greater than 500
for each department.
11.Display Job wise salary of the employees b/w 2000 & 5000 excluding dept no 30 .
12.display job wise employee names which consist of 5 characters and job designation
should be neither salesman nor analyst
13.Display the department number along with the number of employees and also
employee name?
14.Display department wise maximum and minimum salary of all salesman.
15.Write a query to display number of employees having Commission in Dept 30?
16.Display number of employee, total salary paid to employee work in each department?
17.Display job wise with a no of employees whose salary is greater than 2000.
18.Display maximum salary, minimum salary, average salary of each department
19.Display number of employees department wise who are having sal greater than 2000 &
working as manager.
20.display department wise, number of manager.
SELECT DEPTNO,COUNT(MGR) FROM EMP
GROUP BY DEPTNO;
21.Display minimum salary for each of the job whose name starts with s
select min(sal),ename,JOB from emp
where ename like 'S%'
GROUP BY ENAME,JOB
22.display the Department names along with the number of employees in it
SELECT D.DNAME,COUNT(E.ENAME) FROM EMP E, DEPT D
WHERE D.DEPTNO=E.DEPTNO
GROUP BY D.DNAME;
23.Display job wise highest hire date if the hire date is greater than 02 Apr 81 from the
employee table

24.Display the department number which are having less than 3 employees in them
SELECT D.DEPTNO,COUNT(E.ENAME) FROM EMP E, DEPT D
WHERE D.DEPTNO=E.DEPTNO
GROUP BY D.DEPTNO
HAVING COUNT(E.ENAME)<=3
25.Display the department number which is having MANAGER in it & having salary more
than 1500.
SELECT D.DEPTNO,E.ENAME,E.SAL,E.JOB FROM EMP E, DEPT D
WHERE D.DEPTNO=E.DEPTNO
AND E.SAL>1500
AND JOB='MANAGER'
26.Write the query to get the department and department wise total(sum) salary, display it
in descending order according to salary.
27.Display branch wise students wherein each branch number of students should not
exceed more that 180 and average percentage of each branch should be at least more
than 55.
28.Display branch wise students wherein each branch number of students should not
exceed more that 180 and average percentage of each branch should be at least more
than 55.
29.List job with average salary between 1000 and 2000.
select job,avg(sal) from emp
where sal between 1000 and 2000
group by job
30.Write the query to get the department and department wise total salary from employee
details table
31.Display the number of employees department-wise, whose job has character R in it
32.Display hiredate wise the employee working as clerk in department 20 &30 having
salary more than 1000
33.Department wise average salary from employee table order by salary ascending ?
34.Display the department numbers along with employee names having salary greater than
or equal to 1500?
35.list all the salesman in Dept number 30 and having salary greater than 950?
SELECT D.DEPTNO,E.ENAME,E.SAL,E.JOB FROM EMP E, DEPT D
WHERE D.DEPTNO=E.DEPTNO
AND E.JOB='SALESMAN' AND D.DEPTNO=30
AND E.SAL>950
36.Display the deptno. Which is having more than 1 reporting manager.
37.Display year wise joining date along with department number and job.
38.Display each employee of annual salary and excluding ename start with 's'
SELECT ENAME,JOB,SAL*12 "ANNUAL_SAL" FROM EMP
WHERE ENAME NOT LIKE 'S%'
39.Display student. Name who are having more than 60 percent?
40.Display job wise total salary.
SELECT JOB,SUM(SAL) FROM EMP
GROUP BY JOB;
41.display jobwise max salary except Analyst,president?
SELECT JOB,MAX(SAL) AS SALARY FROM EMP
WHERE JOB NOT IN ('ANALAYST','PRESIDENT')
GROUP BY JOB
42.Write the query to get the department, total number of departments, total salary with
respect to department from employee table?
43.Display total salary to distribute job wise in the year 81.
44.Display the number of employees jobwise and are having reporting manager
45.Display job wise hiredate in descending order for those who receive commission.
46.display min salary for each of the job for employee name whose name starting with A.
47.Display Department wise number of salesman
48.Display the Department numbers which are havung more than 2 employees in them
49.Display the Dept numbers along with the number of employees and reporting managers
in it
50.Display all the minimum salary for each of the job including all the employees whole
name ends with 'S'
51.Display the department number in which employees names having a string man,and
having salary more than 1000
52.Display the deptno which is having more then 3 salesman in it.
select d.deptno,COUNT(e.job),E.JOB from emp e, dept d
where d.deptno=e.deptno
and e.job='SALESMAN'
GROUP BY D.DEPTNO,E.JOB
53.Display deptno, job,employee name and having a salary greater than 2000 and having
the total salary in each dept and excluding deptno 20 and sort it by descending order
54.Display deptno, job,employee name and having a salary greater than 2000 and having
the total salary in each dept and excluding deptno 20 and sort it by descending order
55.Query to find Max Salary from each MGR.
56.Write the query to get department and department wise total salary display it in
ascending order according to salary
57.Write a query to display the number of managers department-wise
58.Display job wise total salary who is working as clerk or manager having SALARY more
than 1500 without commission
59.Display the department , no of employee in a department , total salary with respect to a
department from employee table order by total salary descending ?
60.Write a query to display department wise number of manager?
61.3.list all the employees who's name is having at least 2 A's in it?
62.Display job,deptno. Having more employees in a year 81.
63.Display month wise joining date along with reporting manager and salesman.
64.Display job wise least salary along with there MGR Name.
65.display deptno,along with their job designation consist of a string 'ER'
66.Display student name who are having more than 4 letters in the sname?
67.Display the minimum salary of the employees for each department with having 5
characters in employee name and the third alphabet is ‘A’.
68.display min salary of employees whose job in salesman,clerk?
69.Write down the query to fetch department name assign to more than one employee ?
70.List the no of employee in each department where the number is more than 3.
71.Display the number of employees department wise then jobwise and salary more than
1000
72.Display the deptno for those who have MGR 7839 and name end with s.
73.display department wise,having more than 2 salesman.
74.Display the Department number which are having less than 5000 as their departmental
total salary
75.Display the minimum Salary for each of the job excluding all the employees whose name
ends with K
76.Display the highest hire date for each job excluding all the employees whose name ends
with 'N'.
77.Display job-wise highest salary only if the highest salary is more than 2000
78.Display dept no which are contains more then 2 emp in them
79.Display dept no which are contains more then 2 emp in them
80.Display dept no which are contains more then 2 emp in them
81.Display employee name job wise whose having commission greater than 250 and having
a reporting manager whose number starts with 76
82.Display employee name job wise whose having commission greater than 250 and having
a reporting manager whose number starts with 76
83.find number of employees whose hiredate is between 01-jan-80 to 31-dec-82
84.Write the query to get the department and department wise total salary display it in
descending from employee detail table
85.Write a query to display number of employees department-wise whose job end with T
86.Display job wise highest salary only is the highest salary is more than 2000 excluding
deptno 20 sort the data based on highest salary in ascending order.
87.Display no of employee in each job and display all the employees whose reporting
manager has end with '8' no in it .
88.Display the department number which is having more than 1 salesman in it?
89.list all the employees who are not earning salary by the range 1250 and 4000?
90.Display jobwise lowest salary of 1000excluding dept 10.sort the data based on their
lowest salary in the ascending order.
91.Display job wise and employee name whose having reporting manager and having salary
range from 1000 to 2500.
92.Display Department numbers which are having salary more than 2000 except manager.
93.display job wise whose joining date is oldest and newest and whose getting some
commission
94.display department wise number of 'MANAGERs'?
95.Display the branch wise percentages of the students scored more than 85 and the
branch would be having less than 200 students.
96.display deptwise number of salesman except dept 20?
97.Display all the department where department has 3 employee?
98.Display department no and max salary for each department.
99.Display number of employees whose name starts with S or A jobwise and are having
more than or equal to 3 employees
100. Display deptno who has man string in there job having deptno 30.
101. display department wise ,for those department having sal less than 5000.
102. Display Department wise number of president
103. Display the Department numbers which are having more thab 5000 as their
departmental total salry
104. Display Dept wise number of employees who get a commission of more than 500
105. Display the department numbers which are having lesser than 5000 as the
department total salary