SQL Query and Answers - Part4

76. Find the date of nearest Saturday after Current day.

  Select  next_day (sysdate,’SATURDAY’ ) from dual;

77. Display current time.

 Select to_char(Sysydate,’SATURDAY’) from dual;

78. Display the date three months before the current date.

  Select add_months(sysdate,-3) from dual;

79. Display the common jobs from department number 10 and 20.

Select job from emp where deptno=10 and  job in(select job from emp where deptno=20);

(or)

Select job from emp where deptno=10 intersect select job from emp where deptno=10);

80. Display the jobs found in department number 10 and 20 eliminate duplicate jobs.

  Select distinct(job) from emp where deptno=10 and job in(select job from emp where deptno =20

(or)

Select job from emp where deptno=10 intersect select job from emp where deptno=10);

81. Display the jobs which are unique to dept no 10.

 Select job from emp where deptno=10 minus select job from emp where deptno!=10;

(or)

Select job from emp where deptno=10 and job not in (select job from emp  where deptno<>10);

82. Display the details fo those who do not have any person working under them?

select empno from emp where empno not in (select mgr from emp where mgr is not null);

83. Display the details fo employees who are in sales dept and grad is 3

select * from emp where sal>=(select losal from salgrade where grade=3) and sal <=(select hisal from salgrade where grade=3) and deptno = (Select deptno from dept where dname='SALES');

84. Display those who are not managers and who are managers any one.

select * from emp pwhere empon is  ( selelct magr from emp) union select * from emp where empno not in (select mgr from emp where mgr is not null);

85. Display those employees whose name contains not less than 4 chars.

select * from emp where length(ename)>4;

86. Display those departments whose name start with 'S' while location name end with 'O'

Select * from emp where dname like 'S%' and loc like '%O';

876. Display those employees whose manager name is JONES

select * from emp where mgr=(select empno from emp where enames='JONES');

88. Display those employees whose salary is more than 3000 after giving 20% increment.

Select * from emp where sal*120/1000>3000;

(or)

Select * from emp where sal+sal*20/1000>3000;

89.Display all employees with there dept name

select ename, dname from emp e,dept d where e.deptno = d.deptno;

90. Display ename who are working in sales dept.

select empno, ename from emp where deptno=(selec deptno from dept where dname='SALES');

91. Display employee name, deptname, salary and comm. for those sal in between 2000 and 5000 while location is chicago.

select empno, ename, deptno from emp where deptno=(select deptno from dept where loc='CHICAGO:) and sal between 2000 and 5000;

92. Display those employees whose salary greater than his manager salary.

Select  * from emp e where sal>(select sal from emp where empno=e.mgr);

93. Display those employee who are working in the same dept where his manager is working

select * from emp e where deptno = ( select deptno from emp where empno = e.mgr);

94. Display those employees who are not working under any manager

select * from emp where mgr is null or empno=mgr;

95. 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

select empno, ename, sal, deptno, hiredate, grade from emp e, salgrade s where e.sal>= s.local and e.sal<=hisal and deptno in (10,30) and grade <>4 and hiredate<'01-dec-1981';

96. Update the salary of each employee by 10% increments that are not eligible for commission.

update emp set sal = sal+(sal*10/100) where comm is null;

97. Delete those employees how joined the company before 31-dec-82 while there dept location is 'NEW YORK'  or 'CHICAGO'

delete from emp where hiredate<'31-dec-1982' and deptno in (select deptno from dept where loc in ('NEW YORK','CHICAGO'));

98. Display employee name, job, deptname, location for all who are working as managers.

select ename, job, dname, loc from emp e, dept d where e.deptno=d.deptno and empno in (select mgr from emp);

99. Display those employees whose managers names is Jones, and also display there manager name.

select e.empno, e.ename, m.ename MANAGER from emp e, emp m where e.mgr=m.empno and m.ename='JONES'

100. Display employee name, his job, his dept name, his manager name, his grade and make out of an under department wise.

break on deptno;

select d.deptno, e.ename, e.job, d.dname, m.ename, s.grade from emp e, emp m, dept d, salgrade s where e.deptno= d.deptno and e.sal between s.losal and s.hisal=m.empno order by e.deptno;