SQL QUERIES
***********************************************************
Display the number of employees who
are getting salary less than the Blake’s manager.
SELECT COUNT(*)
FROM EMP
WHERE SAL<(SELECT SAL
FROM EMP
WHERE EMPNO IN(SELECT MGR
FROM EMP
WHERE ENAME='BLAKE'));
----------------------------------------------------------------------------------------
List employees who located in
Chicago and their commission is zero.
SELECT * FROM EMP
WHERE COMM=0 AND DEPTNO IN(SELECT DEPTNO
FROM DEPT
WHERE LOC='CHICAGO');
----------------------------------------------------------------------------------------
List employees who work for SALES
department and their salary greater than average salary of their department.
SELECT * FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO
FROM DEPT
WHERE DNAME='SALES') AND SAL>ANY (SELECT AVG(SAL)
FROM EMP
GROUP BY DEPTNO);
----------------------------------------------------------------------------------------
List employees who are working in
RESEARCH department and they are MANAGER(i.e.JOB).
SELECT *
FROM EMP
WHERE JOB='MANAGER' AND DEPTNO IN(SELECT DEPTNO
FROM DEPT
WHERE DNAME='RESEARCH');
--------------------------------------------------------------------------------------------
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);
------------------------------------------------------------------------------------------------
Display department name of the
employees who earn maximum salary and have no reporting manager.
SELECT DNAME
FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO
FROM EMP
WHERE SAL=(SELECT MAX(SAL)
FROM EMP
WHERE MGR IS NULL));
----------------------------------------------------------------------------------------------------
Display employee details who are
reporting to blake and have commission without using null or not null.
select *
from emp
where comm >=0 and mgr in (select empno
from emp
where ename ='BLAKE' );
-----------------------------------------------------------------------------------------------
List all the department name and loc
of all the salesman managers,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')));
----------------------------------------------------------------------
List the employee department name
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' or sal < (select sal from emp
where ename ='MARTIN')));
---------------------------------------------------------------------------------------------------
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 *
from emp
where job<> 'PRESIDENT' and comm >=0 and empno in
(select empno
from emp
where Sal > (select max(sal)
from emp
where job='CLERK'));
--------------------------------------------------------------------------------------------
List the employees who joined after
2 years of first employee of the company and more then blake salary.
Select *
From emp
Where hiredate > (select add_months(min(hiredate),2*12)
From emp)
And sal > (select sal
From emp
Where ename='BLAKE')
-------------------------------------------------------------------------------
Display location of all the
employees who reporting to BLAKE.
SELECT LOC
FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO
FROM EMP
WHERE MGR IN (SELECT EMPNO
FROM EMP
WHERE ENAME = 'BLAKE'))
--------------------------------------------------------------------------------------
List all the employees whose job is
same as jones and their salary lesser than SCOTT.
SELECT *
FROM EMP
WHERE JOB IN (SELECT JOB
FROM EMP
WHERE ENAME='JONES') AND
SAL < (SELECT SAL
FROM EMP
WHERE ENAME='SCOTT');
----------------------------------------------------------------
Display all the employees of
department 30,20 with there annual salary and having atleast 3 employees.
SELECT EMP.*,SAL*12 "ANNUAL SALARY"
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*)>=3);
--------------------------------------------------------------------------------
Display all the employees who are
earn less than any of the salesman.
SELECT *
FROM EMP
WHERE SAL <ANY(SELECT SAL
FROM EMP
WHERE JOB='SALESMAN');
---------------------------------------------------------------------------------
Display all the employees who are
joined before the last person.
select *
from emp
where HIREDATE < (select max(hiredate)
from emp);
----------------------------------------------------------------------
Display last but one employee
record.
SELECT *
FROM EMP A
WHERE 1 = (SELECT COUNT(DISTINCT(HIREDATE))
FROM EMP B
WHERE A.HIREDATE < B.HIREDATE)
------------------------------------------------------
Find 3rd 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));
-----------------------------------------------------------
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')
OR
SELECT *
FROM EMP
WHERE SAL > (SELECT MIN(SAL)
FROM EMP
WHERE JOB='MANAGER')
---------------------------------------------------------------------------------------
List employees who joined after 4
years of 1st employee of the company and less than blake salary.
SELECT *
FROM EMP
WHERE HIREDATE > (SELECT ADD_MONTHS(MIN(HIREDATE),4*12)
FROM EMP)
AND SAL < (SELECT SAL
FROM EMP
WHERE ENAME='BLAKE')
------------------------------------------------------------
Display the department information
of employee who is working for 'NEW YORK' location.
SELECT DEPTNO,DNAME
FROM DEPT
WHERE LOC='NEW YORK';
-------------------------------------------------------
Display the salary wise employee
information for deptno 20 in descending order.
SELECT SAL
FROM EMP
WHERE DEPTNO=20
ORDER BY SAL DESC;
-------------------------------------------------------
Display location of employees, whose
name doesn’t start with A and salary between 1000 and 3000.
SELECT LOC
FROM EMP E, DEPT D
WHERE E.DEPTO=D.DEPTNO AND ENAME NOT LIKE 'A%' AND SAL
BETWEEN 1000 AND 3000
---------------------------------------------------------------
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');
---------------------------------------------
Display martin’s manager’s manager’s
department location.
SELECT 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 ENAME = 'MARTIN')));
-----------------------------------------------
Display the employee name and their
salary who joined 2 years before the last person hired.
SELECT ENAME,SAL
FROM EMP
WHERE HIREDATE > (SELECT ADD_MONTHS(MIN(HIREDATE),2*12)
FROM EMP);
-----------------------------------------------------------------------------
Display the employee name,job and
department for those who don’t have commission.
SELECT ENAME,JOB,DEPTNO
FROM EMP
WHERE COMM IS NULL;
---------------------------------------------------------
Display the employee details with
their location who earn maximum commission.
SELECT E.*,LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND COMM IN (SELECT MAX(COMM)
FROM EMP);
-----------------------------------------------------
Display all the employee whose
department is sales and who is earning some commission (i.e commissison is not
null or zero) and whose hired before the last person hired.
SELECT E.*,D.*
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND
COMM >=0 AND DNAME='SALES' AND HIREDATE < (SELECT
MAX(HIREDATE)
FROM EMP);
-------------------------------------------------------------------------------
Display all the department names and
manager who is wards manager’s manager.
SELECT DNAME,MGR
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND EMPNO IN (SELECT EMPNO
FROM EMP
WHERE EMPNO IN (SELECT MGR
FROM EMP
WHERE EMPNO IN (SELECT MGR
FROM EMP
WHERE ENAME='WARD')));
-----------------------------------------------------------------------------
Display department names and maximum
commission of employees whose salary is greater than average salary of all the
clerk’s.
SELECT ENAME,EMPNO,COMM,DNAME
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO AND COMM IN (SELECT MAX(COMM)
FROM EMP
WHERE SAL > (SELECT AVG(SAL)
FROM EMP
WHERE JOB='CLERK'));
------------------------------------------------------------------------------------------
Display the last employee record
with location.
SELECT E.*,LOC
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO AND HIREDATE IN (SELECT
MAX(HIREDATE)
FROM EMP);
-------------------------------------------------------------
Display the department number who
working in sales department and they are manager.
SELECT *
FROM EMP
WHERE EMPNO IN (SELECT MGR
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE DNAME='SALES'))
-----------------------------------------------------------------------------------------------
Display deprtment name of the
employees who earn minimum salary and have reporting manager.
SELECT DNAME
FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO
FROM EMP
WHERE SAL IN (SELECT MIN(SAL)
FROM EMP
WHERE MGR IS NOT NULL));
------------------------------------------------------------------
Display hiredate and department name
of all the employees working for sales.
SELECT HIREDATE,DNAME
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO AND DNAME='SALES';
----------------------------------------------------------------------
Display location and department name
of employees who is working as president.
SELECT DNAME,LOC
FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO
FROM EMP
WHERE JOB='PRESIDENT');
--------------------------------------------------------------------------------
Display the department name of
employees whose 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 < 3000));
-----------------------------------------------------------------------------------
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'));
------------------------------------------------------------------------------
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);
-----------------------------------------------------------------------------
Display the number of employees who
work for RESEARCH dept and their sal is lesser than one of the sal in dept 10.
SELECT COUNT(*)
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND DNAME='RESEARCH' AND SAL <ANY
(SELECT SAL
FROM EMP
WHERE DEPTNO=10);
----------------------------------------------------------------------------------
Display the dname that are having
clerk in it.
SELECT DNAME
FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO
FROM EMP
WHERE JOB='CLERK');
----------------------------------------------------------------------------------
Display the dname that are having
atleast one L in it.
SELECT DNAME
FROM DEPT
WHERE DNAME LIKE '%L%';
------------------------------------------------------
Display the employees who joined
after BLAKE.
SELECT *
FROM EMP
WHERE HIREDATE > (SELECT HIREDATE
FROM EMP
WHERE ENAME='BLAKE');
---------------------------------------------------
List dname that are having at least
3 employee but not more than 5 employee in it.
SELECT DNAME
FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*) BETWEEN 3 AND 5)
--------------------------------------------------------------------------
Display the location of all employee
whose reporting manager sal is greater than 2000.
SELECT LOC
FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO
FROM EMP
WHERE SAL IN (SELECT SAL
FROM EMP
WHERE SAL > 2000 AND MGR IN (SELECT EMPNO
FROM EMP)));
------------------------------------------------------------
Select the emp whose dname is having
at least two E in it.
SELECT *
FROM DEPT
WHERE DNAME LIKE '%E%E%';
----------------------------------------------------------------------------------------------
Display ename, sal of employee who are earning more than any of the analyst.
SELECT ENAME,SAL
FROM EMP
WHERE SAL >ANY (SELECT SAL
FROM EMP
WHERE JOB='ANALYST');
-----------------------------------------------------------------------------------
Select all the emp who are working
for CHICAGO.
SELECT E.*
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND LOC='CHICAGO';
-------------------------------------------------------------------------------------------------
Query to display employee names who is having minimum salary in RESEARCH
department.
SELECT ENAME
FROM EMP
WHERE SAL IN (SELECT MIN(SAL)
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE DNAME='RESEARCH'));
-------------------------------------------------------------------------
List the department names that are
having SALESMAN.
SELECT DNAME
FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO
FROM EMP
WHERE JOB='SALESMAN');
---------------------------------------------------------------------------
List the department names that are
having at least 3 employees in it.
SELECT DNAME
FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*)>=3);
-------------------------------------------------------------------------
List employees from research and
accounting department having at least two reporting.
SELECT *
FROM EMP
WHERE EMPNO IN (SELECT MGR
FROM EMP
GROUP BY MGR
HAVING COUNT(*)>=2) AND
DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE DNAME IN ('ACCOUNTING','RESEARCH'))
OR
SELECT E.*
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND DNAME IN
('RESEARCH','ACCOUNTING') AND EMPNO IN (SELECT MGR
FROM EMP
GROUP BY MGR
HAVING COUNT(*)>=2)
------------------------------------------------------------------------------------------------
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 E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND DNAME='RESEARCH' AND SAL <ANY
(SELECT SAL
FROM EMP
WHERE DEPTNO=10);
------------------------------------------------------------------------------
DISPLAY DNAME AND LOC OF LAST EMPLOYEE RECORD.
SELECT DNAME,LOC
FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO
FROM EMP
WHERE HIREDATE IN (SELECT MAX(HIREDATE)
FROM EMP));
-----------------------------------------------------------
List the department names that are
having no employees at all.
SELECT DNAME
FROM DEPT
WHERE DEPTNO NOT IN (SELECT DEPTNO
FROM EMP);
--------------------------------------------------------------------------
Display all the employees whose job
is same as SCOTT.
SELECT *
FROM EMP
WHERE JOB IN (SELECT JOB
FROM EMP
WHERE ENAME='SCOTT');
--------------------------------------------------------------------------------------
Display SCOTT's manager’s manager
department name.
SELECT DNAME
FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO
FROM EMP
WHERE EMPNO IN (SELECT MGR
FROM EMP
WHERE EMPNO IN (SELECT MGR
FROM EMP
WHERE ENAME = 'SCOTT')));
---------------------------------------------------------------------------
List employees whose job is same as
SCOTT and their salary greater than SMITH’s salary.
SELECT *
FROM EMP
WHERE JOB IN (SELECT JOB
FROM EMP
WHERE ENAME='SCOTT')
AND
SAL > (SELECT SAL
FROM EMP
WHERE ENAME='SMITH');
--------------------------------------------------------------------------
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'));
-----------------------------------------------------------------------------
Display all the employees who are
actual managers.
SELECT *
FROM EMP
WHERE MGR IN (SELECT EMPNO
FROM EMP);
----------------------------------------------------------------
Display who are all the employees
reporting to SCOTT.
SELECT *
FROM EMP
WHERE MGR IN (SELECT EMPNO
FROM EMP
WHERE ENAME='SCOTT');
-----------------------------------------------------------------------
Display the 2nd maximum salary.
SELECT MAX(HIREDATE)
FROM EMP
WHERE HIREDATE < (SELECT MAX(HIREDATE)
FROM EMP);
OR
SELECT A.SAL
FROM EMP A
WHERE 1 = (SELECT COUNT(DISTINCT(SAL))
FROM EMP B
WHERE A.SAL < B.SAL);
----------------------------------------------------------------
Display the 3rd maximum salary.
SELECT MAX(HIREDATE)
FROM EMP
WHERE HIREDATE < (SELECT MAX(HIREDATE)
FROM EMP
WHERE HIREDATE < (SELECT MAX(HIREDATE)
FROM EMP));
OR
SELECT A.SAL
FROM EMP A
WHERE 2 = (SELECT COUNT(DISTINCT(SAL))
FROM EMP B
WHERE A.SAL < B.SAL);
----------------------------------------------------------------------------
Display all the employees who are
earning more than all the MANAGER(JOB).
SELECT *
FROM EMP
WHERE SAL >ALL (SELECT SAL
FROM EMP
WHERE JOB='MANAGER');
OR
SELECT *
FROM EMP
WHERE SAL > (SELECT MAX(SAL)
FROM EMP
WHERE JOB='MANAGER');
---------------------------------------------------------------
Display all the employees who are
earning more than any of the MANAGER(JOB).
SELECT *
FROM EMP
WHERE SAL >ANY (SELECT SAL
FROM EMP
WHERE JOB='MANAGER');
OR
SELECT *
FROM EMP
WHERE SAL > (SELECT MIN(SAL)
FROM EMP
WHERE JOB='MANAGER');
--------------------------------------------------------------------------------------
Select empno, job and salary of all
the ANALYST who are earning MORE THAN any of the MANAGER(JOB).
SELECT EMPNO,JOB,SAL
FROM EMP
WHERE JOB='ANALYST' AND SAL >ANY (SELECT SAL
FROM EMP
WHERE JOB='MANAGER');
OR
SELECT EMPNO,JOB,SAL
FROM EMP
WHERE JOB='ANALYST' AND SAL > (SELECT MIN(SAL)
FROM EMP
WHERE JOB='MANAGER');
---------------------------------------------------------------------------
Select the department name and
location of all the 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'));
--------------------------------------------------------------------------------
Select all the employees working for DALLAS.
SELECT *
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC='DALLAS');
-------------------------------------------------------------------------------------
Display all the employees whose
salary is greater than avg sal of department 20.
SELECT SAL
FROM EMP
WHERE SAL > (SELECT AVG(SAL)
FROM EMP
WHERE DEPTNO=20);
--------------------------------------------------------------------------------------------
Display all the employees who gets
maximum salary.
SELECT *
FROM EMP
WHERE SAL IN (SELECT MAX(SAL)
FROM EMP);
-------------------------------------------------------------------------------------------------
Display first employee record based
on hiredate.
SELECT *
FROM EMP
WHERE HIREDATE IN (SELECT MIN(HIREDATE)
FROM EMP);
-------------------------------------------------------------------------
List the employees who has salary
greater than ALLEN.
SELECT *
FROM EMP
WHERE SAL > (SELECT SAL
FROM EMP
WHERE ENAME='ALLEN');
-------------------------------------------------------------------------------------------
List the department names that are
having ANALYST.
SELECT DNAME
FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO
FROM EMP
WHERE JOB='ANALYST');
OR
SELECT DNAME
FROM DEPT D
WHERE EXISTS (SELECT JOB
FROM EMP E
WHERE E.DEPTNO=D.DEPTNO AND JOB='ANALYST');
---------------------------------------------------------------------------
List the department names which are
having SALESMAN in it.
SELECT DNAME
FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO
FROM EMP
WHERE JOB='SALESMAN');
----------------------------------------------------------------------------------------
Display the employees whose location
is having at least one 'O' in it.
SELECT *
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC LIKE '%O%');
--------------------------------------------------------------------------------------------
List the department names that are
having at least 1 employee in it.
SELECT DNAME
FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*)>=1);
--------------------------------------------------------------------------------------
List the department names that are
having at least 4 employees in it
SELECT DNAME
FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*)>=4);
------------------------------------------------------------------------------------
Display the department names which
are having at least two CLERKS in it.
SELECT DNAME
FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO
FROM
EMP
WHERE
JOB='CLERK'
GROUP
BY DEPTNO
HAVING COUNT(*) >=2)
-----------------------------------------------------------------------------------------------
Display empno ,ename ,deptno and
sal, sort the output based on name with in name by deptno and with in deptno by
sal.
SELECT EMPNO,ENAME,SAL,DEPTNO
FROM EMP
GROUP BY ENAME,DEPTNO,SAL;
-------------------------------------------------------------------------------------------
Display the name of the employee
along with their annual salary.The name of the employee earning highest annual
salary should appear first.
SELECT ENAME, SAL*12 "ANNUAL SALARY"
FROM EMP
ORDER BY "ANNUAL SALARY" DESC;
OR
SELECT ENAME, SAL*12 "ANNUAL SALARY"
FROM EMP
ORDER BY 2 DESC;
---------------------------------------------------------------------------------
Display deptno and total number of
employees working in each department.
SELECT DEPTNO,COUNT(*)
FROM EMP
GROUP BY DEPTNO;
------------------------------------------------------------------------------------
Display the various jobs and total
number of employees within each job group.
SELECT JOB,COUNT(*)
FROM EMP
GROUP BY JOB;
-------------------------------------------------------------------------------------
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 EMP
GROUP BY JOB
HAVING SUM(SAL) > 40000;
------------------------------------------------------------------------------------------
Display the various jobs along with
total number of employees in each job.The output should contain only those jobs
with more than three employee.
SELECT JOB,COUNT(*)
FROM EMP
GROUP BY JOB
HAVING COUNT(*)>3;
--------------------------------------------------------------------------------------
Display no. of emp for each managers
for 'RESEARCH' AND 'ACCOUNTING'.
SELECT B.ENAME MANAGER,COUNT(*)
FROM EMP A, EMP B
WHERE A.MGR=B.EMPNO AND A.DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE
DNAME IN ('ACCOUNTING','RESEARCH'))
GROUP BY B.ENAME;
----------------------------------------------------------------------------------
Display the name of the employees
who joined on the same date.
SELECT A.ENAME,A.HIREDATE
FROM EMP A, EMP B
WHERE A.HIREDATE=B.HIREDATE AND A.EMPNO<>B.EMPNO;
--------------------------------------------------------------------------
Display the manager name who is
having maximum number of employees working under him.
SELECT B.ENAME,COUNT(*)
FROM EMP A, EMP B
WHERE A.MGR=B.EMPNO
GROUP BY B.ENAME
HAVING COUNT(*)=(SELECT MAX(COUNT(MGR))
FROM EMP
GROUP BY MGR);
-----------------------------------------------------------------------------
Display those emp whose manager name
is 'JONES' and also with his manager name.
SELECT A.EMPNO,A.ENAME EMPLOYEES, B.EMPNO,B.ENAME MANAGER
FROM EMP A, EMP B
WHERE A.MGR=B.EMPNO AND B.EMPNO IN (SELECT EMPNO
FROM EMP
WHERE ENAME='JONES');
----------------------------------------------------------------------------
Display those emp who havegh same
managers.
SELECT DISTINCT A.EMPNO, A.ENAME EMPLOYEE
FROM EMP A, EMP B
WHERE A.MGR=B.MGR AND A.EMPNO<>B.EMPNO;
---------------------------------------------------------------------------------
Display all the employees whose job
has string 'MAN'.
SELECT ENAME,JOB
FROM EMP
WHERE INSTR(JOB,'MAN',1) >0;
-----------------------------------------------------------------------------
Display all the emp whose name hass
'L' as third character.
SELECT ENAME
FROM EMP
WHERE SUBSTR(ENAME,3,1)='L';
------------------------------------------------------------------------
Display the result in he following
formt, first character in lower case and rest in upper case.
SELECT CONCAT(LOWER(SUBSTR(ENAME,1,1)),UPPER(SUBSTR(ENAME,2))
)
FROM EMP
-------------------------------------------------------------------------
Replace third character with * in
the ename column.
SELECT ENAME, CONCAT(REPLACE(ENAME, SUBSTR(ENAME,3), '*'),
SUBSTR(ENAME,4) )
FROM EMP;
-----------------------------------------------------------------------------------
Display ename with first three
character in upper case and remaining in lower case.
SELECT ENAME,
CONCAT(UPPER(SUBSTR(ENAME,1,3)), LOWER(SUBSTR(ENAME,4)) )
FROM EMP;
-----------------------------------------------------------------------------------------------
Display the number of occurence of
'L' in each name.
SELECT ENAME, LENGTH(ENAME)
- LENGTH(REPLACE(ENAME,'L'))
FROM EMP;
-------------------------------------------------------------------------------------
Display all the employees which has
at least two 'L's in it.
SELECT ENAME
FROM EMP
WHERE INSTR(ENAME,'LL')<>0;
