SQL Interview Questions For 5 Years Experience

SQL INTERVIEW QUESTION PART-2

SQL Interview Questions For 5 Years Experience

Below is the list of SQL Interview Questions For 5 Years Experience

List of most frequently asked SQL Interview Questions For 5 Years Experience. Questions from basics to advance level with the solution.

101) Display those employees whose manager name is Jones, and also display their manager name.

SQL>select e.empno, e.ename, m.ename MANAGER from emp e, emp mwhere e.mgr=m.empno and m.ename='JONES';

 

102) Display the name and salary of ford if his Sal is equal to the high Sal of his grade.

SQL>select ename, sal from emp e where ename='FORD' and sal=(selecthisal from salgrade where grade=(select grade from salgrade wheree.sal>=losal and e.sal<=hisal));

 

103) Display employee name, his job, his dept name, his manager name, his grade and make out of an under department wise.break on deptno;

SQL>select d.deptno, e.ename, e.job, d.dname, m.ename, s.grade fromemp e, emp m, dept d, salgrade s where e.deptno=d.deptno and e.salbetween s.losal and s.hisal and

e.mgr=m.empno order by e.deptno;

 

104) List out all the employees’ names, jobs, and salary grades, and department names for everyone in the company except ‘CLERK’. Sort on salary display the highest salary.

SQL>select empno, ename, sal, dname, grade from emp e, dept d, salgrade swhere e.deptno=d.deptno and e.sal between s.losal and s.hisal ande.job<>'CLERK' order by sal;

 

105) Display employee name, his job, and his manager. Display also employees who are without a manager.

SQL>select e.ename, e.job, m.ename Manager from emp e,emp m wheree.mgr=m.empno union select ename,job,'no manager' from emp wheremgr is null;

 

106) Find out the top 5 earners of the company.

SQL>select * from emp e where 5> (select count (*) from emp wheresal>e.sal) order by sal desc;

 

107) Display the name of those employees who are getting the highest salary.

SQL>select empno, ename, sal from emp where sal=(select max(sal) fromemp);

 

108) Display those employees whose salary is equal to the average of maximum and minimum.

SQL>select * from emp where sal= (select (max (sal) +min (sal))/2 fromemp);

 

109) Display count of employees in each department where count greater than 3.

SQL>select deptno, count (*) from emp group by deptno having count (*)>3;

 

110) Display dname where at least 3 are working and display the only name.

SQL>select dname from dept where deptno in (select deptno from emp group by deptno having count (*)>3);

 

111) Display the name of those managers name whose salary is more than the average salary of the company.

SQL>select ename, sal from emp where empno in (select mgr from emp) andsal > (select avg (sal) from emp);

 

112) Display those managers name whose salary is more than the average salary of their employees.

SQL>select ename, sal from emp e where empno in (select mgr from emp) and e.sal> (select avg (sal) from emp where mgr=e.empno);

 

113)Display employee name, sal, comm, and net pay for those employees whose net pay are greater than or equal to any other employee salary of the company?

SQL>select ename, sal, comm, sal+nvl (comm, 0) netPay from emp wheresal+nvl (comm., 0)>=any (select sal from emp);

 

114) Display those employees whose salary is less than his manager but more than the salary of any other managers.

SQL>select * from emp e where sal<(select sal from emp where empno =e.mgr) and sal>any(select sal from emp where empno!=e.mgr);

 

115) Display all employee’s names with the total Sal of the company with each employee name.

SQL> Select ename, (select sum (sal) from emp) from emp;

 

116) Find out the last 5(least) earners of the company?

SQL>select * from emp e where 5> (select count (*) from emp wheresal<e.sal) order by sal;

 

117) Find out the number of employees whose salary is greater than their manager salary?

SQL>select count (*) from emp e where sal> (select sal from emp whereempno=e.mgr);

 

118) Display that manager who is not working under the president but they are working under any other manager?

SQL>select * from emp e where mgr in (select empno from emp whereename<>'KING');

 

119) Delete those departments where no employee working?

SQL> delete from dept d where 0= (select count (*) from emp wheredeptno=d.deptno);

 

120) Delete those records from EMP table whose deptno not available in dept table?

SQL> delete from emp where deptno not in (select deptno from dept);

 

121) Display those earners whose salary is out of the grade available in the Sal grade table?

SQL>select * from emp where sal< (select min (losal) from salgrade) orsal> (select max (hisal) from salgrade);

 

122) Display employee name, sal, comm. and whose net pay is greater than any other in the company?

SQL>select ename, sal, comm from emp where sal+sal*15/100-sal*5/100+sal*10/100 = (select max (sal+sal*15/100-sal*5/100+sal*10/100) from emp);

 

123) Display name of those employees who are going to retire 31-dec-99. If the maximum job is period is 18 years?

SQL>select * from emp where (to_date ('31-dec-1999')-hiredate)/365>18;

 

124) Display those employees whose salary is ODD value?

SQL>select * from emp where mod (sal, 2) =1;

 

125) Display those employees whose salary contains at least 4 digits?

SQL>select * from emp where length (sal)>=4;

 

126) Display those employees who joined the company in the month of DEC?

SQL>select * from emp where upper (to_char (hiredate,'mon')) ='DEC';

 

127) Display those employees whose name contains “A”?

SQL>select * from emp where instr (ename,'A', 1, 1)>0;

 

128) Display those employees whose deptno is available in salary?

SQL>select * from emp where instr (sal, deptno, 1, 1)>0;

 

129) Display those employees whose first 2 characters from hire date-last 2characters of salary?

SQL>select substr(hiredate,0,2)||substr(sal,length(sal)-1,2) from emp; select concat( substr(hiredate,0,2), substr(sal,length(sal)-1,2) ) fromemp;

 

130) Display those employees whose 10% of salary is equal to the year of joining?

SQL>select * from emp where to_char (hiredate,'yy') =sal*10/100;

 

131) Display those employees who are working in sales or research?

SQL>select * from emp where deptno in (select deptno from dept wheredname in ('SALES','RESEARCH'));

 

132) Display the grade of Jones?

SQL>select grade from salgrade where losal<= (select (sal) from emp whereename='JONES') and hisal>= (select (sal) from emp whereename='JONES');

 

133) Display those employees who joined the company before the 15Th of the month?

SQL>select empno, ename from emp where hiredate< (to_date ('15-'||to_char(hiredate,'mon')||'-'||to_char(hiredate,'yyyy')));

 

134) Delete those records where no of the employee in a particular department is less than 3?

SQL>delete from emp where deptno in (select deptno from emp group bydeptno having count (*)>3);

 

135) Delete those employees who joined the company 21 years back from today?

SQL>select * from emp where round ((sysdate-hiredate)/365)>21;

 

Or

SQL>select * from emp where (to_char (sysdate, 'yyyy')-to_char (hiredate,'yyyy'))>21;

 

136) Display the department name the no of characters of which is equal to no of employees in any other department?

SQL>select dname from dept where length (dname) in (select count (*) fromemp group by deptno);

 

137) Display those employees who are working as manager?

SQL>select * from emp where empno in (select mgr from emp);

 

138) Count the no of employees who are working as manager (use set operation)?

SQL>select count (*) from emp where empno in (select mgr from emp);

 

139) Display the name of then dept those employees who joined the company on the same date?

SQL>select empno, ename, hiredate, deptno from emp e where hiredate in (select hiredate from emp where empno<>e.empno);

 

141) Display the manager who is having the maximum number of employees working under him?

SQL>select mgr from emp group by mgr having count (*) =(selectmax(count(mgr)) from emp group by mgr);

 

Above are the most common SQL Interview Questions For 5 Years Experience, also visit MySQL interview questions.

Check Database Testing Interview Questions

Wikipedia SQL

Leave a Comment

Your email address will not be published.