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.