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 DEPTNO, MAX(SAL) FROM EMP WHERE JOB <>'CLERK'
GROUP BY DEPTNO HAVING MAX(SAL)>1500 ORDER BY DEPTNO DESC;
List the number of employee in each
department, except 30, sorted high to low. Only include department with 3 or
more employee.
SELECT DEPTNO,COUNT(*) FROM EMP WHERE DEPTNO<>30 GROUP
BY DEPTNO HAVING COUNT(*)>=3 ORDER BY DEPTNO DESC;
Sql query to find second highest
salary of employee---------SUBQUERY
SELECT MAX(SAL) FROM EMP WHERE SAL<(SELECT MAX(SAL) FROM
EMP);
Display the number of employees who
has annual salary more than 10000 department-wise
SELECT DEPTNO, COUNT(*) FROM EMP WHERE SAL*12>10000 GROUP
BY DEPTNO;
Display the max salary for each of
the job excluding all the employee whose having commission.
SELECT JOB ,MAX(SAL) FROM EMP WHERE COMM IS NULL GROUP BY
JOB;
Find the total salary department
number wise where more than two employees exits?
SELECT DEPTNO, COUNT(*),SUM(SAL) FROM EMP GROUP BY DEPTNO
HAVING COUNT(*)>2;
Display job wise and department wise
least salary only if the least salary is less than 3000 in department 10,30?
SELECT DEPTNO,JOB,MIN(SAL) FROM EMP WHERE DEPTNO IN (10,30)
GROUP BY DEPTNO,JOB HAVING MIN(SAL)<3000;
list all the employees except those
who are working in Dept 30 and 20?
SELECT * FROM EMP WHERE DEPTNO NOT IN (20,30);
Display the department number which
are having more than 200 as their commission along with employees whose name
having 'A' is one of character.
SELECT * FROM EMP WHERE ENAME LIKE '%A%' AND COMM>200;
Display department number and total
salary whose average salary is greater than 500 for each department.
SELECT DEPTNO, AVG(SAL),SUM(SAL) FROM EMP GROUP BY DEPTNO
HAVING AVG(SAL)>500;
Display Job wise salary of the
employees b/w 2000 & 5000 excluding dept no 30 .
SELECT JOB,SAL FROM EMP WHERE SAL BETWEEN 2000 AND 5000
GROUP BY JOB,SAL HAVING DEPTNO<>30;
display job wise employee names
which consist of 5 characters and job designation should be neither salesman
nor analyst
SELECT ENAME,JOB FROM EMP WHERE ENAME LIKE '_____' GROUP BY
JOB,ENAME HAVING JOB NOT IN ('SALESMAN','ANALYST')ORDER BY JOB;
Display the department number along
with the number of employees and also employee name?
SELECT ENAME,DEPTNO,COUNT(*) FROM EMP GROUP BY DEPTNO,ENAME
ORDER BY DEPTNO;
Display department wise maximum and
minimum salary of all salesman.
SELECT DEPTNO ,MIN(SAL),MAX(SAL) FROM EMP WHERE
JOB='SALESMAN' GROUP BY DEPTNO;
Write a query to display number of
employees having Commission in Dept 30?
SELECT DEPTNO,COUNT(*) FROM EMP WHERE COMM IS NOT NULL GROUP
BY DEPTNO HAVING DEPTNO=30;
Display number of employee, total
salary paid to employee work in each department?
SELECT DEPTNO,SUM(SAL),COUNT(*) FROM EMP GROUP BY DEPTNO;
Display job wise with a no of
employees whose salary is greater than 2000.
SELECT JOB ,COUNT(*) FROM EMP WHERE SAL>2000 GROUP BY
JOB;
Display maximum salary, minimum
salary, average salary of each department
SELECT DEPTNO,MAX(SAL),MIN(SAL),AVG(SAL) FROM EMP GROUP BY
DEPTNO;
Display number of employees
department wise who are having sal greater than 2000 & working as manager.
SELECT DEPTNO,COUNT(*) FROM EMP WHERE SAL>2000 AND JOB
='MANAGER' GROUP BY DEPTNO;
display department wise, number of
manager.
SELECT DEPTNO,COUNT(*) FROM EMP WHERE JOB ='MANAGER' GROUP
BY DEPTNO;
Display minimum salary for each of
the job whose name starts wit s
SELECT JOB,MIN(SAL) FROM EMP WHERE ENAME LIKE 'S%' GROUP BY
JOB;
display the Department names along
with the number of employees in it
SELECT DNAME FROM DEPT WHERE DEPTNO IN (SELECT DEPTNO FROM
EMP WHERE,COUNT(*) FROM EMP GROUP BY DEPTNO);----------------JOIN
Display job wise highest hire date
if the hire date is greater than 02 Apr 81 from the employee table
SELECT JOB,MAX(HIREDATE) FROM EMP GROUP BY JOB HAVING
MAX(HIREDATE)>'02-APR-81';
Display the department number which
are having less than 2 employees in them
SELECT DEPTNO,COUNT(*) FROM EMP GROUP BY DEPTNO HAVING
COUNT(*)<2;
Display the department number which
is having clerk in it & having salary more than 1500.
SELECT DEPTNO FROM EMP WHERE JOB='CLERK' AND SAL>1500
GROUP BY DEPTNO;
Write the query to get the
department and department wise total(sum) salary, display it in descending
order according to salary.
SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO ORDER BY
SUM(SAL) DESC;
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.
SELECT BRANCH, AVG(PER) ,COUNT(*) FROM STUDENT GROUP BY
BRANCH HAVING AVG(PER)>55 AND COUNT(*)<=180;
List job with average salary between
1000 and 2000.
SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB HAVING
AVG(SAL)>1000 AND AVG(SAL)<2000;
Write the query to get the
department and department wise total salary from employee details table
SELECT DEPTNO ,SUM(SAL) FROM EMP GROUP BY DEPTNO;
Display the number of employees
department-wise, whose job has character R in it
SELECT DEPTNO,COUNT(*) FROM EMP WHERE JOB LIKE '%R%' GROUP
BY DEPTNO;
Display hiredate wise the employee
working as clerk in department 20 &30 having salary more than 1000
SELECT * FROM EMP WHERE JOB='CLERK' AND DEPTNO IN (20,30)
AND SAL>1000 ORDER BY HIREDATE;
Department wise average salary from
employee table order by salary ascending ?
SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO ORDER BY
AVG(SAL);
Display the department numbers along
with employee names having salary greater than or equal to 1500?
SELECT DEPTNO,ENAME FROM EMP WHERE SAL>=1500;
List all the salesman in Dept number
20 and having salary greater than 950?
SELECT * FROM EMP WHERE DEPTNO=20 AND JOB='SALESMAN' AND
SAL>950;
Display the deptno. Which is having
more than 1 reporting manager.
SELECT DEPTNO,MGR FROM EMP GROUP BY DEPTNO,MGR HAVING
MGR>1;
Display year wise joining date along
with department number and job.
SELECT DEPTNO,JOB,HIREDATE FROM EMP ORDER BY HIREDATE;
Display each employee of annual
salary and excluding ename start with 's'
SELECT ENAME,SAL*12 FROM EMP WHERE ENAME LIKE 'S%';
Display student. Name who are having
more than 60 percent?
SELECT * FROM STUDENT WHERE PER>60;
Display job wise total salary.
SELECT JOB,SUM(SAL) FROM EMP GROUP BY JOB;
display jobwise max salary except
Analyst,president?
SELECT JOB,MAX(SAL) FROM EMP WHERE JOB NOT IN
('PRESIDENT','ANALYST') GROUP BY JOB;
Write the query to get the
department, total number of departments, total salary with respect to
department from employee table?
SELECT DEPTNO, COUNT(DEPTNO),SUM(SAL) FROM EMP GROUP BY
DEPTNO;
Display total salary to distribute
job wise in the year 81.
SELECT JOB,SUM(SAL) FROM EMP WHERE HIREDATE LIKE '%81' GROUP BY JOB;
Display the number of employees
jobwise and are having reporting manager
SELECT JOB,COUNT(ENAME) FROM EMP WHERE MGR IS NOT NULL GROUP
BY JOB;
Display job wise hiredate in
descending order for those who receive commission.
SELECT JOB,HIREDATE FROM EMP WHERE COMM IS NOT NULL GROUP BY
JOB,HIREDATE ORDER BY HIREDATE;
Display min salary for each of the
job for employee name whose name starting with A.
SELECT JOB,ENAME,MIN(SAL) FROM EMP WHERE ENAME LIKE'A%'
GROUP BY JOB ,ENAME ;
Display Department wise number of salesman
SELECT DEPTNO,COUNT(JOB) FROM EMP WHERE JOB='SALESMAN' GROUP
BY DEPTNO;
Display the Department numbers which
are having more than 2 employees in them
SELECT DEPTNO,COUNT(ENAME) FROM EMP GROUP BY DEPTNO HAVING
COUNT(ENAME)>2;
Display the Dept numbers along with
the number of employees and reporting managers in it
SELECT DEPTNO,COUNT(ENAME),MGR FROM EMP GROUP BY DEPTNO,MGR
;
Display all the minimum salary for
each of the job including all the employees whole name ends with 'S'
SELECT JOB,MIN(SAL),ENAME FROM EMP WHERE ENAME LIKE'%S'
GROUP BY JOB,ENAME;
Display the department number in
which employees names having a string man,and having salary more than 1000
SELECT DEPTNO,JOB FROM EMP WHERE JOB LIKE'%MAN%' AND
SAL>1000 GROUP BY DEPTNO,JOB;
Display the deptno which is having
more then 3 salesman in it.
SELECT DEPTNO,JOB,COUNT(JOB) FROM EMP GROUP BY DEPTNO,JOB
HAVING COUNT(JOB)>3;
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
SELECT DEPTNO,JOB,ENAME,SUM(SAL) FROM EMP WHERE
DEPTNO<>20 GROUP BY DEPTNO,JOB,ENAME HAVING SUM(SAL)>2000 ORDER BY
DEPTNO,JOB,ENAME DESC;
Query to find Max Salary from each
MGR.
SELECT JOB,ENAME,MGR ,MAX(SAL) FROM EMP WHERE MGR IS NOT
NULL GROUP BY ENAME,JOB,MGR;
Write the query to get department
and department wise total salary display it in ascending order according to
salary
SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO ODER BY
SUM(SAL) ASC;
Write a query to display the number
of managers department-wise
SELECT DEPTNO,COUNT(JOB) FROM EMP WHERE JOB='MANAGER' GROUP
BY DEPTNO;
Display job wise total salary who is
working as clerk or manager having SALARY more than 1500 without commission
SELECT DEPTNO,SUM(SAL) FROM EMP WHERE JOB
IN('CLERK','MANAGER') AND SAL>1500 GROUP BY DEPTNO ;
Display the department , no of
employee in a department , total salary with respect to a department from
employee table order by total salary descending ?
SELECT DEPTNO,SUM(SAL),COUNT(ENAME) FROM EMP GROUP BY DEPTNO
ORDER BY SUM(SAL) DESC ;
Write a query to display department
wise number of manager?
SELECT DEPTNO,COUNT(JOB) FROM EMP WHERE JOB='MANAGER' GROUP
BY DEPTNO ;
list all the employees who's name is
having at least 2 A's in it?
SELECT ENAME FROM EMP WHERE ENAME LIKE'%A%A%';
Display job,deptno. Having more
employees in a year 81.
SELECT JOB,DEPTNO,HIREDATE FROM EMP WHERE HIREDATE
LIKE'%81';
Display month wise joining date
along with reporting manager and salesman.
SELECT DEPTNO,JOB,HIREDATE FROM EMP WHERE JOB='SALESMAN' AND
MGR IS NOT NULL GROUP BY DEPTNO,JOB,HIREDATE ORDER BY HIREDATE ;
Display job wise least salary along
with their MGR Name.
SELECT ENAME,JOB,MIN(SAL) FROM EMP WHERE ENAME IN (SELECT
ENAME FROM EMP WHERE EMPNO IN(SELECT DISTINCT MGR FROM EMP)) GROUP BY
JOB,ENAME;
Display deptno,along with their job
designation consist of a string 'ER'
SELECT JOB,DEPTNO FROM EMP WHERE JOB LIKE'%ER%';
Display student name who are having
more than 4 letters in the sname?
SELECT SNAME FROM STUDENT WHERE LENGTH(SNAME)>4 ;
Display the minimum salary of the
employees for each department with having 5 characters in employee name and the
third alphabet is ‘A’.
SELECT ENAME,DEPTNO,MIN(SAL) FROM EMP WHERE ENAME LIKE'__A%'
AND JOB LIKE '_____'GROUP BY ENAME,DEPTNO ;
Display min salary of employees
whose job in salesman,clerk?
SELECT ENAME,DEPTNO,MIN(SAL) FROM EMP WHERE JOB IN
('SALESMAN','CLERK')GROUP BY ENAME,DEPTNO ;
Write down the query to fetch
department name assign to more than one employee?
SELECT DNAME FROM DEPT WHERE DEPTNO IN (SELECT DEPTNO FROM
EMP GROUP BY DEPTNO HAVING COUNT(ENAME)>1);
List the no of employee in each
department where the number is more than 3.
SELECT DEPTNO, COUNT(ENAME) FROM EMP GROUP BY DEPTNO HAVING
COUNT(ENAME)>3;
Display the number of employees
department wise then jobwise and salary more than 1000
SELECT JOB,DEPTNO FROM EMP WHERE SAL>1000 GROUP BY
JOB,DEPTNO ;
Display the deptno for those who
have MGR 7839 and name end with s.
SELECT DEPTNO FROM EMP WHERE MGR=7839 AND ENAME LIKE'%S';
Display department wise,having more
than 2 salesman.
SELECT DEPTNO, COUNT(ENAME) FROM EMP WHERE JOB='SALESMAN'
GROUP BY DEPTNO HAVING COUNT(ENAME)>2;
Display the Department number which
are having less than 5000 as their departmental total salary
SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO HAVING
SUM(SAL)<5000;
Display the minimum Salary for each
of the job excluding all the employees whose name ends with K
SELECT JOB,MIN(SAL) FROM EMP WHERE ENAME NOT LIKE '%K'GROUP
BY JOB;
Display the highest hire date for
each job excluding all the employees whose name ends with 'N'.
SELECT JOB,MAX(HIREDATE) FROM EMP WHERE ENAME NOT LIKE
'%N'GROUP BY JOB;
Display job-wise highest salary only
if the highest salary is more than 2000
SELECT JOB,MAX(SAL) FROM EMP GROUP BY JOB HAVING
MAX(SAL)>2000;
Display dept no which are contains
more then 2 emp in them
SELECT DEPTNO, COUNT(ENAME) FROM EMP GROUP BY DEPTNO HAVING
COUNT(ENAME)>2;
Display employee name job wise whose
having commission greater than 250 and having a reporting manager whose number
starts with 76
SELECT ENAME,JOB FROM EMP WHERE COMM>250 AND MGR
LIKE'76%'GROUP BY JOB,ENAME;
Find number of employees whose
hiredate is between 01-jan-80 to 31-dec-82
SELECT COUNT(ENAME) FROM EMP WHERE HIREDATE BETWEEN
'01-JAN-80' AND '31-DEC-82' GROUP BY DEPTNO;
SELECT DEPTNO ,COUNT(ENAME) FROM EMP WHERE HIREDATE BETWEEN
'01-JAN-80' AND '31-DEC-82' GROUP BY DEPTNO;
Write the query to get the
department and department wise total salary display it in descending from
employee detail table
SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO ORDER BY
SUM(SAL) DESC ;
Write a query to display number of
employees department-wise whose job end with T
SELECT DEPTNO,COUNT(*) FROM EMP WHERE JOB LIKE'%T' GROUP BY
DEPTNO ;
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.
SELECT JOB ,MAX(SAL) FROM EMP WHERE DEPTNO<>20 GROUP
BY JOB HAVING MAX(SAL)>2000 ORDER BY MAX(SAL);
Display no of employee in each job
and display all the employees whose reporting manager has end with '8' no in it
.
SELECT JOB,ENAME,COUNT(ENAME) FROM EMP WHERE MGR LIKE'%8'
GROUP BY JOB,ENAME;
Display the department number which
is having more than 1 salesman in it?
SELECT DEPTNO, COUNT(JOB) FROM EMP WHERE JOB ='SALESMAN'
GROUP BY DEPTNO,JOB HAVING COUNT(JOB)>1
List all the employees who are not
earning salary by the range 1250 and 4000?
SELECT * FROM EMP WHERE SAL NOT BETWEEN 1250 AND 4000;
Display jobwise lowest salary of
1000excluding dept 10.sort the data based on their lowest salary in the
ascending order.
SELECT JOB, MIN(SAL) FROM EMP WHERE DEPTNO<>10 GROUP
BY JOB HAVING MIN(SAL)<1000;
Display job wise and employee name
whose having reporting manager and having salary range from 1000 to 2500.
SELECT * FROM EMP WHERE MGR IS NOT NULL AND SAL BETWEEN 1000
AND 2500 ORDER BY JOB;
Display Department numbers which are
having salary more than 2000 except manager.
SELECT DEPTNO FROM EMP WHERE SAL>2000 AND
JOB<>'MANAGER';
Display job wise whose joining date
is oldest and newest and whose getting some commission
SELECT JOB,MAX(HIREDATE),MIN(HIREDATE) FROM EMP WHERE COMM
IS NOT NULL GROUP BY JOB ;
Display department wise number of
'MANAGERs'?
SELECT DEPTNO,JOB, COUNT(*) FROM EMP GROUP BY DEPTNO,JOB
HAVING JOB='MANAGER';
Display the branch wise percentages
of the students scored more than 85 and the branch would be having less than
200 students.
SELECT BRANCH ,COUNT(*) FROM STUDENT WHERE PER>85 GROUP
BY BRANCH HAVING COUNT(*)<200;
Display deptwise number of salesman
except dept 20?
SELECT DEPTNO FROM EMP WHERE JOB ='SALESMAN' AND
DEPTNO<>20;
Display all the department where
department has 3 employee?
SELECT DEPTNO,COUNT(*) FROM EMP GROUP BY DEPTNO HAVING
COUNT(*)=3;
Display department no and max salary
for each department.
SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO;
Display number of employees whose
name starts with S or A jobwise and are having more than or equal to 3
employees
SELECT JOB,COUNT(*) FROM EMP WHERE ENAME LIKE 'S%' OR ENAME
LIKE 'A%' GROUP BY JOB HAVING COUNT(*)>=3;
Display deptno who has man string in
there job having deptno 30.
SELECT DEPTNO FROM EMP WHERE JOB LIKE '%MAN%' AND DEPTNO=30;
Display department wise ,for those
department having sal less than 5000.
SELECT DEPTNO ,SAL FROM EMP WHERE SAL<5000;
Display Department wise number of
president
SELECT DEPTNO FROM EMP WHERE JOB='PRESIDENT';
Display the Department numbers which
are having more than 5000 as their departmental total salary
SELECT DEPTNO ,SUM(SAL) FROM EMP GROUP BY DEPTNO HAVING
SUM(SAL)>5000;
Display Dept wise number of
employees who get a commission of more than 500
SELECT DEPTNO,COUNT(*) FROM EMP WHERE COMM>500 GROUP BY
DEPTNO;
Display the department numbers which
are having lesser than 5000 as the department total salary
SELECT DEPTNO ,SUM(SAL) FROM EMP GROUP BY DEPTNO HAVING
SUM(SAL)<5000;
