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