SQL Interview Questions For Experienced Professionals

database testing
SQL INTERVIEW QUESTION PART-2

SQL Interview Questions For Experienced Professionals

List of most frequently asked SQL Interview Questions For Experienced Professionals. Questions from basics to advance level.

151) Display those who working as a manager using a co-related subquery.

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

152) Display those employees whose manager name is Jones and also with his manager name.

SQL>select * from emp where mgr= (select empno from emp whereename='JONES') union select * from emp where empno= (select mgrfrom emp where ename='JONES');

 

153) 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;

 

154) Use the variable in a statement that finds all employees who can earn 30,000a year or more.

SQL>select * from emp where &emp_ann_sal>30000;

 

155) Find out how many managers are there without listing them.

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

 

156) Find out the avg sal and avg total remuneration for each job type remember salesman earns a commission.

SQL>select job, avg (sal+nvl (comm, 0)), sum (sal+nvl (comm, 0)) from empgroup by job;

 

157) Check whether all employee’s numbers are indeed unique.

SQL>select count(empno),count(distinct(empno)) from emp havingcount(empno)=(count(distinct(empno)));

 

158) List out the lowest-paid employees working for each manager, exclude any groups where min sal is less than 1000 sort the output by sal.

SQL>select e.ename, e.mgr, e.sal from emp e where sal in (select min (sal) from emp where mgr=e.mgr) and e.sal>1000 order by sal;

 

159) List name, job, annual sal, deptno, dname, and grade who earn 30000 per year and who are not clerks.

SQL>select e.ename, e.job, (e.sal+nvl (e.comm,0))*12, e.deptno, d.dname,s.grade from emp e, salgrade s , dept d where e.sal between s.losaland s.hisal and e.deptno=d.deptno

and (e.sal+nvl(comm,0))*12>30000 and e.job <> ‘CLERK’;

 

160) Create a table called as newemp. This table should contain only empno, ename, name.

SQL> create table newemp as select empno, ename, dname from emp e, deptd where e.deptno=d.deptno;

 

161) Find out all employees who joined the company before their manager.

SQL>select * from emp e where hiredate< (select hiredate from emp whereempno=e.mgr);

 

162) List out all employees by name and number along with their manager’s names and number also display ‘No Manager’ who has no manager.

SQL>select e.empno, e.ename, m.empno Manager, m.ename ManagerNamefrom emp e, emp m where e.mgr=m.empnounion select empno, ename, mgr,'No Manager' from

emp where mgr is null;

 

163) find out the employees who earned the highest Sal in each job typed sort in descending Sal order.

SQL>select * from emp e where sal = (select max (sal) from emp where job=e.job);

 

164) find out the employees who earned the min Sal for their job in ascending order.

SQL>select * from emp e where sal = (select min (sal) from emp where job=e.job) order by sal;

 

165) find out the most recently hired employees in each dept order by hire date.

SQL>select * from emp order by deptno, hiredate desc;

 

166) Display ename, sal, and deptno for each employee who earns a Sal greater than the avg of their department order by deptno.

SQL>select ename, sal, deptno from emp e where sal> (select avg (sal) fromemp where deptno=e.deptno) order by deptno;

 

167) Display the department where there are no employees.

SQL>select deptno, dname from dept where deptno not in (selectdistinct(deptno) from emp);

 

168) Display the dept no with highest annual remuneration bill as compensation.

SQL>select deptno, sum (sal) from emp group by deptno having sum (sal) =(select max(sum(sal)) from emp group by deptno);

 

169) In which year did most people join the company. Display the year and number of employees.

SQL>select count (*), to_char (hiredate,'yyyy') from emp group byto_char (hiredate,'yyyy');

 

170) Display avg sal figure for the dept.

SQL>select deptno, avg (sal) from emp group by deptno;

 

171) Write a query of display against the row of the most recently hired employee.display ename hire date and column max date showing.

SQL>select empno, hiredate from emp where hiredate= (select max (hiredate) from emp);

 

172) Display employees who can earn more than the lowest Sal in dept no 30.

SQL>select * from emp where sal> (select min (sal) from emp wheredeptno=30);

 

173) Find employees who can earn more than every employee in dept no 30.

SQL>select * from emp where sal> (select max (sal) from emp wheredeptno=30); select * from emp where sal>all (select sal from emp wheredeptno=30);

 

174) Select dept name dept no and the sum of Salbreak on deptno on name.

SQL>select e.deptno, d.dname, sal from emp e, dept d where.deptno=d.deptno order by e.deptno;

 

175) Delete the rows of employees who are working in the company for more than 2years.

SQL> Delete from emp where floor (sysdate-hiredate)>2*365;

 

176) Find all departments which have more than 3 employees.

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

 

177) Display those departments whose names start with “S” while the location name ends with “K”.

SQL>select dname from dept where dname like 'S%'and loc like '%K';

 

178) Display those employees whose manager name is JONES.

SQL>select p.ename from emp e, emp p where e.empno=p.mgr and e.ename='JONES';

 

179) Display those employees whose salary is more than 3000 after giving20% increment.

SQL>select ename, sal from emp where (sal+sal*.2) >3000;

 

180) Display all employees while their dept names;

SQL>select ename, dname from emp, dept where emp.deptno=dept.deptno

 

181) Display the half of the enames in upper case and remaining lower case.

SQL>select concat ( upper ( substr ( ename, 0 , length (ename)/ 2) ),lower (substr (ename, length(ename) / 2+1, length(ename) )) ) from emp;

 

182) Oh! This column should be related to empno. Give a command to add this constraint.

SQL> Alter table emp add constraint emp_mgr foreign key (empno);

 

183) Add dept no column to your emp table.

SQL> Alter table emp add deptno number (3);

 

184) This dept no column should be related to deptno column of dept table.

SQL> Alter table emp1 add constraint emp1_deptno foreign key (deptno) references dept (deptno);

 

185) Select ename if ename exists more than once.

SQL>select distinct (ename) from emp e where ename in (select ename fromemp where e.empno<>empno);

 

186) display all enames in reverse order.

SQL>select ename from emp order by ename desc;

 

187) Display those employees whose joining of month and grade is equal.

SQL>select empno, ename from emp e, salgrade s where e.sal betweens.losal and s.hisal and to_char (hiredate,'mm') =grade;

 

188) Display those employees whose joining date is available in dept no.

SQL>select * from emp where to_char (hiredate,'dd') =deptno;

189) Display those employees name as follows A ALLEN, B BLAKE.

SQL>select substr (ename, 1, 1) ||' '||ename from emp;

 

190) List out the employees ename, sal, PF from emp.

SQL>select ename, sal, sal*15/100 PF from emp;

 

191) Create table called as new emp. Using single command creates this table as well as to get data into this table (use create table as).

SQL> create table newemp as select *from emp;

 

192) Create table emp with only one column empno.

SQL> Create table emp (empno number (5));

193) Add this column to emp table ename Varchar (20).

SQL> alter table emp add ename varchar2 (20) not null;

 

194) OOPS! I forgot to give the primary key constraint. Add it now.

SQL> alter table emp add constraint emp_empno primary key (empno);

 

195) now increase the length of ename column to 30 characters.

SQL> alter table emp modify ename varchar2 (30);

196) Add salary column to emp table.

SQL> alter table emp add sal number (7, 2);

 

197) I want to give a validation saying that sal cannot be greater 10,000(note give aname to this column).

SQL> alter table emp add constraint emp_sal_check check (sal<10000);

 

198) For the time being I have decided that I will not impose this validation. My boss has agreed to pay more than 10,000.

SQL> Alter table emp disable constraint emp_sal_check;

 

199) my boss has changed his mind. Now he doesn’t want to pay more than10, 000. So revoke that salary constraint

SQL> Alter table emp enable constraint emp_sal_check;

 

200) Add column called as mgr to your emp table.

SQL> Alter table emp add mgr number (5);

 

Above are the most asked SQL Interview Questions For Experienced Professionals, also visit MySQL interview questions.

Check Database Testing Interview Questions

Wikipedia SQL