SQL Interview Questions part 6

You can see part 5 here.

76) Display the following output for each row from emp table.

Scott has joined the company on Wednesday 13th August nineteen ninety.

[hideshow type=”sql76″]SQL>select ENAME||’ HAS JOINED THE COMPANY ON  ‘||to_char(HIREDATE,’day ddth Month  year’)   from EMP;[/hideshow]

77) Find the date for nearest saturday after current date.

[hideshow type=”sql77″]SQL>SELECT NEXT_DAY(SYSDATE,’SATURDAY’)FROM DUAL;[/hideshow]

78) Display current time.

[hideshow type=”sql78″]SQL>select to_char(sysdate,’hh:MM:ss’) from dual;[/hideshow]

79) Display the date three months Before the current date.

[hideshow type=”sql79″]SQL>select add_months(sysdate,3) from dual;[/hideshow]

80) Display the common jobs from department number 10 and 20.

[hideshow type=”sql80″]SQL>select job from emp where deptno=10 and job in(select job from emp where deptno=20);[/hideshow]

81) Display the jobs found in department 10 and 20 Eliminate duplicate jobs.

[hideshow type=”sql81″]

SQL>select distinct(job) from emp where deptno=10 or deptno=20

           (or)

SQL>select distinct(job) from emp where deptno in(10,20);

[/hideshow]

82) Display the jobs which are unique to department 10.

[hideshow type=”sql82″]SQL>select distinct(job) from emp where deptno=10;[/hideshow]

83) Display the details of those who do not have any person working under them.

[hideshow type=”sql83″]SQL>select e.ename from emp,emp e where emp.mgr=e.empno group by e.ename having count(*)=1;[/hideshow]

84) Display the details of those employees who are in sales department and grade is 3.

[hideshow type=”sql84″]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);[/hideshow]

85) Display those who are not managers and who are managers any one.

i)display the managers names

[hideshow type=”sql851″]SQL>select distinct(m.ename) from emp e,emp m where m.empno=e.mgr;[/hideshow]

ii)display the who are not managers

[hideshow type=”sql852″]SQL>select ename from emp where ename not in(select distinct(m.ename) from emp e,emp m where m.empno=e.mgr);[/hideshow]

 86) Display those employee whose name contains not less than 4 characters.

[hideshow type=”sql86″]SQL>select ename from emp where length(ename)>4;[/hideshow]

87) Display those department whose name start with “S” while the location name ends with “K”.

[hideshow type=”sql87″]SQL>select dname from dept where dname like ‘S%’ and loc like ‘%K’;[/hideshow]

88) Display those employees whose manager name is JONES.

[hideshow type=”sql88″]SQL>select p.ename from emp e,emp p where e.empno=p.mgr and e.ename=’JONES’;[/hideshow]

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

[hideshow type=”sql89″]SQL>select ename,sal from emp where (sal+sal*.2)>3000;[/hideshow]

90) Display all employees while their dept names.

[hideshow type=”sql90″]SQL>select ename,dname from emp,dept where emp.deptno=dept.deptno;[/hideshow]