1.
List the emps whose sal < his Manager but more than other Manager
Select distinct A.* from emp A, emp B, emp C
where A.sal < B.sal and A.mgr = B.empno and
A.sal > C.sal and A.mgr not in (select empno
from emp where empno <> B.Empno and job = ‘MANAGER’) ;
2. List the employee names and his annual salary dept wise.
SQL> Select ename, dname, sal*12 from emp, dept where
emp.deptno in (select deptno from emp group by deptno)
and emp.deptno = dept.deptno ;
OR
Select deptno, ename, sal*12 from emp group by deptno,ename,sal ;
3. Find out least 5 earners of the company.
SQL> select * from emp where rownum < 6 order by sal asc
Find out the no of emps whose salary is > their Manager salary.
select count(A.empno) from emp A, emp B where
A.sal > B.sal and A.Mgr = B.empno ;
List the Mgrs who are not working under ‘PRESIDENT’ but are working
under other Manager.
105. List the Mgrs who are not working under ‘PRESIDENT’ but are working under oth
Manager.
SQL> select A.ENAME, A.JOB from emp A, emp B where
A.Mgr = B.empno and B.ename <> ‘PRESIDENT’ AND A.job = ‘MANAGER’ ;
106. List the records from emp whose deptno is not in dept
SQL> HOW CAN IT BE POSSIBLE???
[A REFERENCE KEY VALUE WITHOUT A PRIMARY KEY VALUE.]
select * from emp where deptno not in (select deptno from dept)
List the name, sal, comm. And net pay
is more than any other employee.
SQL> select empno, sal, comm, sal+sal*1.2+80
Net from emp where
sal = (select max(sal) from emp) ;
List the enames who are
retiring after ’31-DEC-89’ the
max job period is 20Y.
SQL> Select empno, ename, hiredate, sysdate,
months_between(sysdate,hiredate)/12 Exp from e
where months_between(sysdate,hiredate)/12 > 20
order by hiredate ;
List those emps whose sal is odd
value.
SQL> Select * from emp where mod(sal,2) <> 0
110. List the emps whose sal contain 3 dig
SQL> Select * from emp where length(sal) = 3 ;
List the emps whose names contains
‘A’
SQL> select ename from emp where ename lik
‘%A%’ ;
List the emps whose deptno is
available in his salary.
SQL> select ename, sal, deptno from emp
where substr(sal,length(sal)-1,2) = deptno
or substr(sal,length(sal)-2,2) = deptno
or substr(sal,length(sal)-3,2) = deptno ;
List the emps whose first 2 chars
from hiredate = last 2 chars of
salary.
SQL> select ename, sal, hiredate from emp
where substr(hiredate,1,2) =
substr(sal,length(sal)-1,2) ;
List the emps whose 10% of sal is
equal to year of Joining
SQL> select ename,sal , sal * .1, hiredate from emp
where sal * .1 like substr(hiredate,length(hireda
1,2) ;
List first 50% of chars
of ename in lower
case and remaining
are upper case.
SQL> select ename, lower(substr(ename,1,length(ename)/2)),
upper(substr(ename,(length(ename)/2)+1,length(ename)))
emp ;
List the dname whose no of emps is =
to no of chars in the dname.
SQL> select dname, length(dname) Len from dep
where deptno =
(select emp.deptno from emp, dept where
emp.deptno = dept.deptno having
Count(empno) in
(select length(dname) from dept group by
dname)
group by emp.deptno) ;
