SQL>select
distinct(job) from emp where deptno=10 or deptno=20
(or)
SQL>select
distinct(job) from emp where deptno in(10,20);
82)
Display the jobs which are unique to department 10.
SQL>select
distinct(job) from emp where deptno=10
83)
Display the details of those who do not have any person working under them.
SQL>select
e.ename from emp,emp e where emp.mgr=e.empno group by
e.ename having count(*)=1;
84)
Display the details of those employees who are in sales department and
grade is 3.
SQL>select
* from emp where deptno=(select deptno from dept where
dname='SALES')and sal between(select
losal from salgrade where grade=3)and
(select hisal from salgrade where
grade=3);
85)
Display those who are not managers and who are managers any one.
i)display
the managers names
SQL>select
distinct(m.ename) from emp e,emp m where m.empno=e.mgr;
ii)display
the who are not managers
SQL>select
ename from emp where ename not in(select distinct(m.ename)
from emp e,emp m where m.empno=e.mgr);
86)
Display those employee whose name contains not less than 4 characters.
SQL>select
ename from emp where length(ename)>4;
87)
Display those department whose name start with "S" while the location
name
ends with "K".
SQL>select
dname from dept where dname like 'S%' and loc like '%K';
88)
Display those employees whose manager name is JONES.
SQL>select
p.ename from emp e,emp p where e.empno=p.mgr and
e.ename='JONES';
89)
Display those employees whose salary is more than 3000 after giving 20%
increment.
SQL>select
ename,sal from emp where (sal+sal*.2)>3000;
90)
Display all employees while their dept names
SQL>select
ename,dname from emp,dept where emp.deptno=dept.deptno
91)
Display ename who are working in sales dept.
SQL>select
ename from emp where deptno=(select deptno from dept where
dname='SALES');
92)
Display employee name,deptname,salary and comm for those sal in between
2000 to 5000 while location is chicago .
SQL>select
ename,dname,sal,comm from emp,dept where sal
between 2000
and 5000 and loc='CHICAGO ' and emp.deptno=dept.deptno;
93)Display
those employees whose salary greter than his manager salary.
SQL>select
p.ename from emp e,emp p where e.empno=p.mgr and p.sal>e.sal
94)
Display those employees who are working in the same dept where his
manager is work.
SQL>select
p.ename from emp e,emp p where e.empno=p.mgr and
p.deptno=e.deptno;
95)
Display those employees who are not working under any manager.
SQL>select
ename from emp where mgr is null
96)
Display grade and employees name for the dept no 10 or 30 but grade is
not 4 while joined the company before
31-dec-82.
SQL>select
ename,grade from emp,salgrade where sal between losal and
High sal and deptno in(10,30) and
grade<>4 and hiredate<'31-DEC-82';
97)
Update the salary of each employee by 10% increment who are not
eligiblw for commission.
SQL>update
emp set sal=sal+sal*10/100 where comm is null;
98)
SELECT those employee who joined the company before 31-dec-82 while
their dept location is newyork or Chicago .
SQL>SELECT
EMPNO,ENAME,HIREDATE,DNAME,LOC FROM EMP,DEPT
WHERE (EMP.DEPTNO=DEPT.DEPTNO)AND
HIREDATE <'31-DEC-82' AND DEPT.LOC
IN('CHICAGO ','NEW YORK ');
99)
DISPLAY EMPLOYEE NAME,JOB,DEPARTMENT,LOCATION FOR ALL WHO ARE WORKING
AS
MANAGER?
SQL>select
ename,JOB,DNAME,LOCATION from emp,DEPT where mgr is not
null;
100)
DISPLAY THOSE EMPLOYEES WHOSE MANAGER NAME IS JONES? --
[AND ALSO DISPLAY THEIR MANAGER
NAME]?
SQL>
SELECT P.ENAME FROM EMP E, EMP P WHERE E.EMPNO=P.MGR AND
E.ENAME='JONES';
No comments:
Post a Comment