- 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
- 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
- List the number of employee in each department, except 30, sorted high to low. Only include department with 3 or more employee.
- Sql query to find second highest salary of employee
- Display the number of employees who has annual salary more than 10000 department-wise
- Display the max salary for each of the job excluding all the employee whose having commission.
- Find the total salary department number wise where more than two employees exits?
- Display job wise and department wise least salary only if the least salary is less than 3000 in department 10,30?
- list all the employees except those who are working in Dept 30 and 20?
- Display the department number which are having more than 200 as their commission along with employees whose name having 'A' is one of character.
- Display department number and total salary whose average salary is greater than 500 for each department.
- Display Job wise salary of the employees b/w 2000 & 5000 excluding dept no 30 .
- display job wise employee names which consist of 5 characters and job designation should be neither salesman nor analyst
- Display the department number along with the number of employees and also employee name?
- Display department wise maximum and minimum salary of all salesman.
- Write a query to display number of employees having Commission in Dept 30?
- Display number of employee, total salary paid to employee work in each department?
- Display job wise with a no of employees whose salary is greater than 2000.
- Display maximum salary, minimum salary, average salary of each department
- Display number of employees department wise who are having sal greater than 2000 & working as manager.
- display department wise, number of manager.
- Display minimum salary for each of the job whose name starts wit s
- display the Department names along with the number of employees in it
- Display job wise highest hire date if the hire date is greater than 02 Apr 81 from the employee table
- Display the department number which are having less than 2 employees in them
- Display the department number which is having clerk in it & having salary more than 1500.
- Write the query to get the department and department wise total(sum) salary, display it in descending order according to salary.
- Write the query to get the department and department wise total(sum) salary, display it in descending order according to salary.
- Write the query to get the department and department wise total(sum) salary, display it in descending order according to salary.
- 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.
- 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.
- List job with average salary between 1000 and 2000.
- Write the query to get the department and department wise total salary from employee details table
- Display the number of employees department-wise, whose job has character R in it
- Display hiredate wise the employee working as clerk in department 20 &30 having salary more than 1000
- Department wise average salary from employee table order by salary ascending ?
- Display the department numbers along with employee names having salary greater than or equal to 1500?
- list all the salesman in Dept number 20 and having salary greater than 950?
- Display the deptno. Which is having more than 1 reporting manager.
- Display year wise joining date along with department number and job.
- Display each employee of annual salary and excluding ename start with 's'
- Display student. Name who are having more than 60 percent?
- Display job wise total salary.
- display jobwise max salary except Analyst,president?
- Write the query to get the department, total number of departments, total salary with respect to department from employee table?
- Display total salary to distribute job wise in the year 81.
- Display the number of employees jobwise and are having reporting manager
- Display job wise hiredate in descending order for those who receive commission.
- display min salary for each of the job for employee name whose name starting with A.
- Display Department wise number of salesman
- Display the Department numbers which are havung more than 2 employees in them
- Display the Dept numbers along with the number of employees and reporting managers in it
- Display all the minimum salary for each of the job including all the employees whole name ends with 'S'
- Display the department number in which employees names having a string man,and having salary more than 1000
- Display the deptno which is having more then 3 salesman in it.
- Display the deptno which is having more then 3 salesman in it.
- Display the deptno which is having more then 3 salesman in it.
- 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
- 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
- Query to find Max Salary from each MGR.
- Write the query to get department and department wise total salary display it in ascending order according to salary
- Write a query to display the number of managers department-wise
- Display job wise total salary who is working as clerk or manager having SALARY more than 1500 without commission
- Display the department , no of employee in a department , total salary with respect to a department from employee table order by total salary descending ?
- Write a query to display department wise number of manager?
- 3.list all the employees who's name is having at least 2 A's in it?
- Display job,deptno. Having more employees in a year 81.
- Display month wise joining date along with reporting manager and salesman.
- Display job wise least salary along with there MGR Name.
- display deptno,along with their job designation consist of a string 'ER'
- Display student name who are having more than 4 letters in the sname?
- Display the minimum salary of the employees for each department with having 5 characters in employee name and the third alphabet is ‘A’.
- display min salary of employees whose job in salesman,clerk?
- Write down the query to fetch department name assign to more than one employee ?
- List the no of employee in each department where the number is more than 3.
- Display the number of employees department wise then jobwise and salary more than 1000
- Display the deptno for those who have MGR 7839 and name end with s.
- display department wise,having more than 2 salesman.
- Display the Department number which are having less than 5000 as their departmental total salary
- Display the minimum Salary for each of the job excluding all the employees whose name ends with K
- Display the highest hire date for each job excluding all the employees whose name ends with 'N'.
- Display job-wise highest salary only if the highest salary is more than 2000
- Display dept no which are contains more then 2 emp in them
- Display dept no which are contains more then 2 emp in them
- Display dept no which are contains more then 2 emp in them
- Display employee name job wise whose having commission greater than 250 and having a reporting manager whose number starts with 76
- Display employee name job wise whose having commission greater than 250 and having a reporting manager whose number starts with 76
- find number of employees whose hiredate is between 01-jan-80 to 31-dec-82
- Write the query to get the department and department wise total salary display it in descending from employee detail table
- Write a query to display number of employees department-wise whose job end with T
- 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.
- Display no of employee in each job and display all the employees whose reporting manager has end with '8' no in it .
- Display the department number which is having more than 1 salesman in it?
- list all the employees who are not earning salary by the range 1250 and 4000?
- Display jobwise lowest salary of 1000excluding dept 10.sort the data based on their lowest salary in the ascending order.
- Display job wise and employee name whose having reporting manager and having salary range from 1000 to 2500.
- Display Department numbers which are having salary more than 2000 except manager.
- display job wise whose joining date is oldest and newest and whose getting some commission
- display department wise number of 'MANAGERs'?
- Display the branch wise percentages of the students scored more than 85 and the branch would be having less than 200 students.
- display deptwise number of salesman except dept 20?
- Display all the department where department has 3 employee?
- Display department no and max salary for each department.
- Display number of employees whose name starts with S or A jobwise and are having more than or equal to 3 employees
- Display deptno who has man string in there job having deptno 30.
- display department wise ,for those department having sal less than 5000.
- Display Department wise number of president
- Display the Department numbers which are having more thab 5000 as their departmental total salry
- Display Dept wise number of employees who get a commission of more than 500
- Display the department numbers which are having lesser than 5000 as the department total salary
Monday, 16 January 2017
Questions on group by clause
SUB QUERIES WITH ANSWER
SQL QUERIES ON SUB-QUERIES AND JOIN
1. List the departments which does not have empyloyee at all.
select dname from dept where deptno not in (select deptno
from emp);
DNAME
--------------
OPERATIONS
2.Display all the employees whose
job is same as scott.
SELECT * FROM EMP
WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME='SCOTT');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
3.Display scott's manager's manager'S
department name.
SELECT DNAME FROM DEPT WHERE DEPTNO IN(SELECT DEPTNO FROM
EMP WHERE EMPNO IN( SELECT MGR
2 FROM EMP WHERE EMPNO IN(SELECT MGR FROM
EMP WHERE ENAME='SCOTT')));
DNAME
--------------
ACCOUNTING
4.List employess whose job is same
as scott and their salary greater than smith's salary.
SELECT * FROM EMP WHERE JOB =(SELECT JOB FROM EMP WHERE
ENAME='SCOTT') AND SAL >(SELECT SAL FROM EMP WHERE ENAME='SMITH');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
5.Display all the employees whose
job is same as scott and allen.
SELECT * FROM EMP
WHERE JOB IN (SELECT JOB FROM EMP WHERE ENAME IN('SCOTT','ALLEN'));
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250
1400 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
6.Display all employees who are
actual managers.
select * from emp
where empno in(select mgr from emp);
EMPNO ENAME JOB
MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER
7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7.Display who are all employees
reporting to scott.
select * from emp where mgr=(select empno from emp where
ename='SCOTT');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
8.Select 2nd maximum salary.
SELECT MAX(SAL) FROM
EMP WHERE SAL<(SELECT MAX(SAL) FROM EMP);
MAX(SAL)
----------
3000
9. select 3rd maximum salary.
SELECT MAX(SAL) FROM
EMP WHERE SAL<(SELECT MAX(SAL) FROM EMP WHERE SAL<(SELECT MAX(SAL) FROM
EMP));
MAX(SAL)
----------
2975
10.Display all the employees who
earn more than all the managers(job).
select * from emp where sal> all (select sal from emp
where job='MANAGER');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
11.Display all the employees who
earn more than any the managers(job).
select * from emp
where sal> any (select sal from emp where job='MANAGER');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
12.Display empno,job and salary of
all the analyst who are earning more than any of the manager
select empno,job,sal
from emp where job='ANALYST' and sal>any(select sal from emp where
job='MANAGER');
EMPNO JOB SAL
---------- --------- ----------
7902
ANALYST 3000
7788
ANALYST 3000
13.select the department name and
location of all employees working for clark
select dname,loc from dept where deptno in(select deptno
from emp where mgr in(select empno from emp where ename='CLARK'));
DNAME LOC
-------------- -------------
ACCOUNTING NEW
YORK
14. select all the employees working
in dallas.
select * from emp
where deptno in(select deptno from dept where loc='DALLAS')
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
------ ---------- --------- ---------- --------- ----------
---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
15.Display all the employees whose
salary is greater than average salary of deartment 20.
select * from emp where sal>(select avg(sal) from emp
where deptno=20);
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000
20
16.select all the employees who gets
maximum salary.
select * from emp
where sal=(select max(sal) from emp);
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
17.Display first employee record
based on hiredate.
select * from emp
where hiredate=(select min(hiredate) from emp);
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
18.list the employees who have
salary greater than Allen.
select * from emp
where sal >(select sal from emp where ename='ALLEN');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
19.list the daprtment names which
are having analysts.
select dname from
dept where deptno in(select deptno from emp where job ='ANALYST')
DNAME
--------------
RESEARCH
20.List the department names which
have salesman in it.
select dname from
dept where deptno in(select deptno from emp where job='SALESMAN')
DNAME
--------------
SALES
21.Display the employees whose location which has atleast one
o in it.
select * from emp where deptno in (select deptno from dept
where loc like '%O%');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
22.List the department names that
are having at least one employee in it.
select dname from
dept where deptno in (select deptno from emp group by deptno having
count(*)>=1)
DNAME
--------------
SALES
RESEARCH
ACCOUNTING
23.list the department names having
atleast 4 employees in it.
select dname from
dept where deptno in (select deptno from emp group by deptno having
count(*)>=4);
DNAME
--------------
SALES
RESEARCH
24.Display the department names
which are having at least 2 Clerks in it.
select dname from dept where deptno in(select deptno from
emp where job='CLERK' group by deptno having count(*) >=2);
DNAME
--------------
RESEARCH
25.Query to display the employee
names who is having maximum salary in Dept Name "Accounting"
SELECT ENAME FROM EMP WHERE SAL = (SELECT MAX(SAL) FROM EMP
WHERE DEPTNO =(SELECT DEPTNO FROM DEPT WHERE DNAME='ACCOUNTING')) ;
ENAME
----------
KING
26. Query to display the Dept Name
who is having Highest Commission
SELECT DNAME FROM
DEPT WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE COMM =(SELECT MAX(COMM) FROM
EMP));
DNAME
--------------
SALES
27. Query to display all the
employees whose Dept Number is same as Scott.
SELECT * FROM EMP
WHERE DEPTNO = ( SELECT DEPTNO FROM EMP WHERE ENAME = 'SCOTT');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
28.Display all the employees in
'Operations and Accounting' Dept. list the employees who has salary greater
than miller
SELECT * FROM EMP
WHERE DEPTNO IN ( SELECT DEPTNO FROM DEPT WHERE DNAME IN (
'OPERATIONS','ACCOUNTING')) AND
SAL > ( SELECT SAL
FROM EMP WHERE ENAME ='MILLER');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
29. List department name having
atleast 3 salesman
SELECT DNAME FROM
DEPT WHERE DEPTNO IN( SELECT DEPTNO FROM EMP WHERE JOB='SALESMAN' GROUP BY DE
PTNO HAVING COUNT(*)>=3);
DNAME
--------------
SALES
30.Display all employess who do not
have any reporter.
select * from emp where mgr is null;
king
31. display all the employees who
are not managers.
SELECT * FROM EMP WHERE EMPNO NOT IN ( SELECT MGR FROM EMP
WHERE MGR IS NOT NULL);
EMPNO ENAME JOB
MGR HIREDATE SAL COMM
DEPTNO
------ ---------- --------- ---------- --------- ----------
---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
31.List all the
employees who are reporting to jones manager
SELECT * FROM EMP
WHERE empno =(SELECT mgr FROM EMP WHERE ENAME ='JONES');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
32. list employees from
research&accounting having atleast 2 reporting.
SELECT * FROM EMP WHERE DEPTNO IN( SELECT DEPTNO FROM DEPT
WHERE DNAME IN ('RESEARCH','ACCOUNTING'))
AND EMPNO IN ( SELECT MGR FROM EMP GROUP BY MGR HAVING
COUNT(*)>=2);
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
33. Display the departname of the
employee whose name starts with S and
salary between 1500 to 3000.
SELECT DNAME FROM
DEPT WHERE DEPTNO IN ( SELECT DEPTNO FROM EMP WHERE ENAME LIKE 'S%' AND SAL
BETWEEN 1500 AND 3000);
DNAME
--------------
RESEARCH
34.Display location of employee
whose salary is minimum salary but salary is greater than 2000
SELECT LOC FROM DEPT WHERE DEPTNO IN (SELECT DEPTNO FROM EMP
WHERE SAL IN( SELECT MIN(SAL) FROM
EMP WHERE
SAL>2000));
LOC
-------------
NEW YORK
35.Display the location of an
employee in accounting department.
SELECT LOC FROM DEPT
WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME='ACCOUNTING');
LOC
-------------
NEW YORK
36.Display the department location
that are having greater than four employees in it.
SELECT LOC FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(*)>4);
LOC
-------------
CHICAGO
DALLAS
37.Write a query to display all the
employee whose job not same as Allen and salary is greater than Martin.
select * from emp where Job !=(select job from emp where
ename='ALLEN') AND SAL >(SELECT SAL FROM EMP WHERE ENAME='MARTIN');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- --------- ----------
---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
38.Display all the employees who is
having location same as Adam's manager.
SELECT * FROM EMP WHERE DEPTNO =(SELECT DEPTNO FROM DEPT
WHERE LOC =(SELECT LOC FROM DEPT WHERE
DEPTNO =(SELECT
DEPTNO FROM EMP WHERE EMPNO = (SELECT MGR FROM EMP WHERE ENAME='ADAMS'))));
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
39. Display the job, manager of
employees who is working for Jones
SELECT JOB, ENAME,MGR FROM EMP WHERE MGR =(SELECT EMPNO FROM
EMP WHERE ENAME='JONES');
JOB ENAME MGR
--------- ---------- ----------
ANALYST SCOTT 7566
ANALYST FORD 7566
40. Display the employee names,
higher date, commission of Ford's manager
SELECT ENAME
,HIREDATE ,COMM FROM EMP WHERE EMPNO =(SELECT MGR FROM EMP WHERE ENAME =
'FORD');
ENAME
HIREDATE COMM
---------- --------- ----------
JONES 02-APR-81
41.Display the number of employees
who are getting salary less then Blake's MANAGER salary.
SELECT COUNT(ENAME)
FROM EMP WHERE SAL<(SELECT SAL FROM EMP WHERE EMPNO=(SELECT MGR FROM EMP
WHERE ENAME='BLAKE'));
COUNT(ENAME)
----------
13
42.List the employess who are located in chicago and their
commission is zero.
SELECT COUNT(*)
FROM EMP WHERE COMM=0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE
LOC='CHICAGO');
COUNT(*)
--------
1
43.List employees who work for sales department and their
salary greater than average salary of their department.
SELECT * FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME='SALES') AND SAL > ANY
(SELECT
AVG(SAL) FROM EMP
GROUP BY DEPTNO);
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
------ ----------
--------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
44.List employees who are working in research department and
they are manager.
SELECT ENAME FROM
EMP WHERE DEPTNO =(SELECT DEPTNO FROM DEPT WHERE DNAME='RESEARCH') AND EMPNO IN
(SELECT MGR FROM EMP);
ENAME
-------
JONES
SCOTT
FORD
45. Display department name of the employees who earn
commission.
SELECT DNAME FROM
DEPT WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE COMM IS NOT NULL);
DNAME
--------
SALES
46.Display department name of the employee who earn maximum
salary and have no reporting manager.
SELECT DNAME FROM
DEPT WHERE DEPTNO IN ( SELECT DEPTNO FROM EMP WHERE MGR IS NULL AND SAL=(SELECT
MAX(SAL) FROM EMP));
DNAME
--------------
ACCOUNTING
47.Display employee details who are reporting to blake and
have commission without using null or not null.
SELECT * FROM EMP
WHERE MGR IN (SELECT EMPNO FROM EMP WHERE ENAME='BLAKE') AND COMM IS NOT NULL;
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
48.List all the deprtnaments and loc of all the salesman's
manager's manager.
SELECT DNAME ,LOC
FROM DEPT WHERE DEPTNO IN(SELECT DEPTNO FROM EMP WHERE EMPNO IN( SELECT MGR
FROM EMP WHERE
EMPNO IN(SELECT MGR FROM EMP WHERE JOB='SALESMAN')));
DNAME LOC
-------------- -------------
ACCOUNTING NEW
YORK
49.List the employee deptname and loc of all the employees
who are clerk ,reporting to blake and salary is lesser than martin salary
SELECT DNAME ,LOC
FROM DEPT WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE JOB='CLERK' AND MGR IN
(SELECT EMPNO FROM EMP WHERE ENAME='BLAKE' )
AND SAL<(SELECT SAL FROM EMP WHERE
ENAME='MARTIN'));
DNAME LOC
-------------- -------------
SALES CHICAGO
50.List the employees who does not directly report to
president,have commission and salary more than max salary of all the clerk
without
using null or not null.
SELECT ENAME FROM
EMP WHERE MGR != (SELECT EMPNO FROM EMP WHERE JOB='PRESIDENT') AND COMM Is NOT
NULL
AND SAL>
ALL(SELECT MAX(SAL) FROM EMP WHERE JOB='CLERK' );
no rows selected
51. List the
employees who joined after 2 years of first employee of the company and more
than blake salary.
SELECT * FROM EMP
WHERE HIREDATE>ALL(SELECT MIN(HIREDATE)+(365*2) FROM EMP) AND SAL>(SELECT
SAL FROM EMP WHERE ENAME='BLAKE');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
------ ---------- --------- ---------- --------- ----------
---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
52. Display location
of all the employees who are reporting to Blake.
SELECT LOC FROM
DEPT WHERE DEPTNO IN( SELECT DEPTNO FROM EMP WHERE MGR =(SELECT EMPNO FROM EMP
WHERE ENAME='BLAKE'));
LOC
-------------
CHICAGO
53. List all the employees whose job is same as jones and
their salary lesser than scott.
SELECT * FROM EMP
WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME='JONES') AND SAL<(SELECT SAL
FROM EMP WHERE ENAME='SCOTT');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
54. Display the
king's first year salary and his location with department.
SELECT SAL*12 AS fIRST_YEAR_SAL,DNAME,LOC FROM EMP,DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO AND ENAME='KING';
FIRST_YEAR_SAL DNAME
LOC
-------------- -------------- -------------
60000
ACCOUNTING NEW YORK
55. Display all the employees of department 30, 20 with
their anual salary and having atleast 3 employees.
SELECT SAL*12 AS
ANUAL_SAL, ENAME FROM EMP WHERE DEPTNO IN(20,30) AND DEPTNO IN (SELECT DEPTNO
FROM EMP GROUP BY DEPTNO HAVING COUNT(*)>=3);
ANUAL_SAL ENAME
---------- ----------
11400 JAMES
18000 TURNER
34200 BLAKE
15000 MARTIN
15000 WARD
19200 ALLEN
36000 FORD
13200 ADAMS
36000 SCOTT
35700 JONES
9600 SMITH
56. Display department name which is having second highest
average salary and third highest induvidul salary.
57. Display all the employees who are earning less than any
of the salesman.
SELECT * FROM EMP
WHERE SAL < ANY(SELECT SAL FROM EMP WHERE JOB='SALESMAN');
EMPNO ENAME
JOB MGR HIREDATE SAL COMM
DEPTNO
----- ---------- --------- ---------- --------- ----------
---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
58.Display all the employees who are joined before the last
person
SELECT * FROM EMP
WHERE HIREDATE < (SELECT MAX(HIREDATE) FROM EMP);
EMPNO ENAME
JOB MGR HIREDATE SAL COMM
DEPTNO
----- ---------- --------- ---------- --------- ----------
---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
59.Display last but one employee record
sELECT * FROM EMP WHERE HIREDATE =(SELECT MAX(HIREDATE FROM
EMP WHERE HIREDATE<(SELECT MAX(HIREDATE) FROM EMP));
sELECT * FROM EMP
WHERE HIREDATE =(SELECT MAX(HIREDATE) FROM EMP WHERE HIREDATE<(SELECT
MAX(HIR
EDATE) FROM EMP))
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ---------
---------- ----------
DEPTNO
----------
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
60.Find third minimum salary in the employee table
SELECT MIN(SAL) FROM EMP WHERE SAL > (SELECT MIN(SAL)
FROM EMP WHERE SAL > (SELECT MIN(SAL) FROM EMP));
MIN(SAL)
--------
1100
61.Display all the employees who are earning more than any
of the manager.
select * from emp
where sal > ANY (select sal from emp where job ='MANAGER');
EMPNO ENAME
JOB MGR HIREDATE SAL COMM
DEPTNO
----- ---------- --------- ---------- --------- ----------
---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER
7839 01-MAY-81 2850 30
SQL> select * from emp where sal > ANY (select sal
FROM EMP WHERE EMPNO IN (SELECT MGR FROM EMP));
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ---------
---------- ----------
DEPTNO
----------
7839 KING PRESIDENT 17-NOV-81 5000
10
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7788 SCOTT ANALYST
7566 19-APR-87 3000
20
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ---------
---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
62. List employees
who joined after 4 years of 1st employee of the company and less than blake
salary.
SELECT ENAME FROM EMP
WHERE HIREDATE > (SELECT MIN(HIREDATE)+(365*4) FROM EMP) AND SAL< (SELECT
SAL FROM EMP WHERE ENAME='BLAKE');
ENAME
-------
ADAMS
63.Display the department information of employee who is
working for newyork location.
SELECT DNAME, DEPTNO
FROM DEPT WHERE LOC ='NEW YORK'
DNAME
DEPTNO
-------------- ----------
ACCOUNTING
10
64. Display the salary wise employee information for deptno
20 in descending order
SELECT * FROM EMP
WHERE DEPTNO=20 ORDER BY SAL DESC;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
65. Display second employee record
SELECT * FROM EMP WHERE HIREDATE IN (SELECT MIN(HIREDATE)
FROM EMP WHERE HIREDATE > (SELECT MIN(HIREDATE) FROM EMP));
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
--------- ---------- --------- ---------- ---------
---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
66.Display location of employees, whose name start with A
and salary between 1000 and 3000.
SELECT LOC FROM
EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND ENAME LIKE 'A%' AND SAL BETWEEN 1000
AND 3000;
LOC
-------------
CHICAGO
DALLAS
67.Display department name of all the employees who are
reporting to Blake.
SELECT DNAME FROM
DEPT WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE MGR IN (SELECT EMPNO FROM
EMP WHERE ENAME='BLAKE'));
DNAME
--------------
SALES
68.Display Martin's Manager's Manager's department location
SELECT DNAME , LOC
FROM DEPT WHERE DEPTNO = (SELECT DEPTNO
FROM EMP WHERE EMPNO IN (SELECT
MGR FROM EMP WHERE EMPNO IN (SELECT MGR
FROM EMP WHERE ENAME
='MARTIN')));
DNAME LOC
-------------- -----------
ACCOUNTING NEW
YORK
69. Display the employee name and their salary who joined 2
years before the last person hired.
SELECT ENAME,SAL FROM EMP WHERE HIREDATE IN (SELECT MAX(HIREDATE)-(365*2) FROM EMP);
ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
KING 5000
TURNER 1500
JAMES 950
FORD 3000
MILLER 1300
70. Display the employee name,job and department for those
who don't have commission
SELECT ENAME,JOB,
DNAME,E.DEPTNO FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO AND E.COMM IS NULL
ENAME JOB
DNAME DEPTNO
---------- --------- -------------- ----------
SMITH CLERK RESEARCH 20
JONES MANAGER RESEARCH 20
BLAKE
MANAGER SALES 30
CLARK
MANAGER ACCOUNTING 10
SCOTT
ANALYST RESEARCH 20
KING PRESIDENT
ACCOUNTING 10
ADAMS CLERK RESEARCH 20
JAMES CLERK SALES 30
FORD
ANALYST RESEARCH 20
MILLER CLERK ACCOUNTING 10
71. Display the employee details with their location who
earn maximum commission
SELECT * FROM
EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND COMM IN (SELECT MAX(COMM) FROM EMP);
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO DEPTNO DNAME LOC
------ ---------- --------- ---------- --------- ----------
---------- ---------- ---------- ------
----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO
72. Display all the employee whose department is sales and
who is earning some commission (i.e commission is not null or zero)and whose
hired before the last person hired.
SQL> SELECT * FROM EMP WHERE COMM IS NOT NULL AND
HIREDATE <(SELECT MAX(HIREDATE) FROM EMP)
2 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE
DNAME='SALES');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
73.Display all the department names and manager who is
ward's manager's manager.
SELECT DNAME,MGR FROM EMP E,DEPT D WHERE E.DEPTNO= D.DEPTNO
AND EMPNO IN (SELECT MGR FROM EMP W
HERE EMPNO IN (SELECT MGR FROM EMP WHERE ENAME='WARD'));
DNAME MGR
-------------- ----------
ACCOUNTING
74.Display department names and maximum commission of
employee's whose salary is greater than average salary of all the clerk's
SELECT D.DNAME,MAX(E.COMM) FROM EMP E,DEPT D WHERE
E.DEPTNO=D.DEPTNO AND E.SAL> ALL(SELECT AVG(SAL) FROM EMP where
JOB='ClERK' GROUP BY JOB );
75. Display the last employee record with location.
SELECT ENAME,LOC FROM
EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO AND HIREDATE =(SELECT MAX(HIREDATE)
FROM EMP);
ENAME LOC
---------- -------------
ADAMS DALLAS
76. Display the department number who working in sales
department and they are manager.
SELECT DEPTNO FROM
DEPT WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES') AND DEPTNO IN
(SELECT DEPTNO FROM EMP WHERE JOB='MANAGER');
DEPTNO
----------
30
77. Display department name of the employee who earn minmum
salary and have reporting manager.
SELECT DNAME FROM DEPT WHERE DEPTNO IN( SELECT DEPTNO FROM
EMP WHERE
SAL =( SELECT MIN( SAL) FROM EMP WHERE MGR IS NOT NULL))
DNAME
--------------
RESEARCH
78. Display hiredate and Dname of all the employees working
for sales
SELECT
D.DNAME,E.HIREDATE FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO AND E.DEPTNO IN
(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES')
DNAME
HIREDATE
-------------- ---------
SALES
20-FEB-81
SALES
22-FEB-81
SALES
28-SEP-81
SALES
01-MAY-81
SALES
08-SEP-81
SALES
03-DEC-81
79. Display Location and Dname of employee who is working as
president.
SELECT DNAME,LOC FROM DEPT WHERE DEPTNO=(SELECT DEPTNO FROM
EMP WHERE JOB='PRESIDENT');
DNAME LOC
-------------- -------------
ACCOUNTING NEW
YORK\
80.Display the Dname of employees whoes salary is maximum
salary but lesser than 3000
SELECT DNAME FROM DEPT WHERE DEPTNO IN (SELECT DEPTNO FROM
EMP WHERE SAL IN (SELECT MAX(SAL) FROM EMP WHERE SAL IN(SELECT SAL FROM EMP
WHERE SAL<3000)));
SQL> SELECT DNAME FROM DEPT WHERE DEPTNO IN (SELECT
DEPTNO FROM EMP WHERE SAL IN (SELECT MAX(SAL) FR
OM EMP WHERE SAL< 3000));
DNAME
--------------
RESEARCH
81.Display the department name who are reporting to ADAMS
SELECT DNAME FROM DEPT WHERE DEPTNO IN (SELECT DEPTNO FROM
EMP WHERE MGR IN( SELECT EMPNO FROM
EMP WHERE ENAME='ADAMS'));
no rows selected
82.Display last employee record.
SELECT * FROM EMP
WHERE HIREDATE IN (SELECT MAX(HIREDATE) FROM EMP);
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
83.Display all the employee whose salary is greater than
average salary of department 30.
SELECT * FROM EMP
WHERE SAL>(SELECT AVG(SAL) FROM EMP WHERE DEPTNO=30);
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
84. Display the number of employees who work for research
dept and their salary is lesser than one of the salary in department in 10.
SELECT COUNT(*) FROM
EMP WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE DNAME='RESEARCH') AND SAL<
ANY(SELECT SAL FROM EMP WHERE DEPTNO=10);
COUNT(*)
----------
5
85.Display the dname that are having clerk in it.
SELECT DNAME FROM
DEPT WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE JOB='CLERK');
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
86. Display the department names that are having atleast one
L in it.
SELECT DNAME FROM DEPT WHERE DNAME LIKE '%L%';
DNAME
--------------
SALES
87. Display all the employees who are joined after BLAKE.
SELECT * FROM EMP
WHERE HIREDATE>(SELECT HIREDATE FROM EMP WHERE ENAME='BLAKE');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN
7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST
7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
88. List the dept name that are having at least 3 employees
but not more than 5 employees in it.
SELECT DNAME FROM
DEPT WHERE DEPTNO IN (SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(*)
BETWEEN 3 AND 5);
DNAME
--------------
RESEARCH
ACCOUNTING
89.Display the location of all employees whose reporting
manager salary is greater than 2000.
SELECT LOC FROM DEPT WHERE DEPTNO IN (SELECT DEPTNO FROM EMP
WHERE MGR IN (SELECT EMPNO FROM EMP WHERE SAL >2000))
LOC
-------------
CHICAGO
DALLAS
NEW YORK
90.Select the employees whose dname is having at least two
'E' in it.
SELECT ENAME, DNAME FROM EMP, DEPT WHERE EMP.DEPTNO =
DEPT.DEPTNO AND DNAME LIKE '%E%E%'
ENAME DNAME
---------- -----------
SMITH RESEARCH
JONES RESEARCH
SCOTT RESEARCH
ADAMS RESEARCH
FORD RESEARCH
91.Display ename,sal of employees who are earning more than
any of the analyst.
SELECT ENAME,SAL FROM EMP WHERE SAL>ANY(SELECT SAL FROM
EMP WHERE JOB='ANALYST');
ENAME SAL
---------- ----------
KING 5000
92. Select all the
employees who are working for CHICAGO
SELECT * FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC='CHICAGO');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
93. Query to display employee names who is having minimum
salary in department research.
SELECT * FROM EMP
WHERE SAL = ( SELECT MIN (SAL) FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT
WHERE
DNAME='RESEARCH'));
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
94.List the department names that are having salesman.
SELECT DNAME FROM DEPT WHERE DEPTNO IN(SELECT DEPTNO FROM
EMP WHERE JOB='SALESMAN');
DNAME
--------------
SALES
95. List the department names that are having at least 3
employes in it.
SELECT DNAME FROM DEPT WHERE DEPTNO IN (SELECT DEPTNO FROM
EMP GROUP BY DEPTNO HAVING COUNT(*)>=3);
DNAME
--------------
SALES
RESEARCH
ACCOUNTING
96.List employees from research and accounting department
having atleast two reporting.
SELECT ENAME FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE DNAME
IN('ACCOUNTING','RESEARCH')) AND
EMPNO IN(SELECT MGR
FROM EMP GROUP BY MGR HAVING COUNT(*)>=2)
ENAME
----------
KING
JONES
97. Write a query to display employee name,
job,dname,location of all employees who are working as manager and works at
Chicago.
SELECT ENAME,JOB,LOC FROM EMP E,DEPT D WHERE
E.DEPTNO=D.DEPTNO AND JOB='MANAGER'AND LOC='CHICAGO';
ENAME JOB LOC
---------- --------- -------------
BLAKE
MANAGER CHICAGO
98. Select ename,dname of employee who earns 2nd max salary
and works for location Dallas.
SELECT ENAME,DNAME
FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO AND E.SAL IN(SELECT MAX(SAL) FROM EMP
WHERE SAL<(SELECT MAX(SAL) FROM EMP)
AND LOC='DALLAS'
ENAME DNAME
---------- --------------
SCOTT RESEARCH
FORD RESEARCH
99. Write a query to display the employee information who is
not taking commission and joined company after July 83
SELECT * FROM EMP
WHERE COMM IS NULL AND HIREDATE > '31-JULY-83';
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- ---------
---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
100. List employees from sales and research department
having atleast 2 reporting employees
SELECT ENAME FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM
DEPT WHERE DNAME IN('SALES','RESEARCH'))
AND EMPNO IN(SELECT MGR FROM EMP GROUP BY MGR HAVING
COUNT(*)>=2);
ENAME
----------
BLAKE
JONES
pradeepamr007@gmail.com
Subscribe to:
Posts (Atom)