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]

SQL Interview Questions part 5
SQL Interview Questions part 7
Shekhar Sharma

Shekhar Sharma

Shekhar Sharma is founder of testingpool.com. This website is his window to the world. He believes that ,"Knowledge increases by sharing but not by saving".

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *