SQL Interview Question Answers

1) Display details of all employees
SQL> select * from emp;

2) Display the employee no and totalsalary for all the employees. Here totalsalary = sal+comm
SQL> select empno,ename,sal,comm, sal+nvl(comm,0) total_sal from emp;

3) Display the names of all the employees who are working in department 10.
SQL> select emame from emp where deptno=10;

4) Display the employee number and name who are earning comm.
SQL> select empno,ename from emp where comm is not null;

5) Display the names of the employees who are working in the company for the past 5 years;
SQL> select ename from emp where hiredate < add_months(sysdate,-60);

6) Display the names of employees working in department number 10 or 20 or 40 and working as CLERKS,SALESMAN or ANALYST.
SQL> select ename from emp where deptno in(10,20,40) and job in('CLERKS','SALESMAN','ANALYST');

7) Display the Employee names for employees whose name ends with alaphabet S.
SQL> select ename from emp where ename like '%S';

8) Display the maximum salary being paid to CLERK.
SQL> select max(sal) from emp where job='CLERK';

9) Display the total salary drawn by an ANALYST working in department 40.
SQL> select sum(sal) from emp where job='ANALYST' and deptno=40;

10) Display the department numbers with more than three employees in each dept.
SQL> select deptno,count(deptno) from emp group by deptno having count(*)>3;

11) Display the name of the employee who earns highest salary.
SQL> select ename from emp where sal=(select max(sal) from emp);

12) Display the employee number and name for employee working as clerk and earning highest salary among clerks.
SQL> select empno,ename from emp where where job='CLERK' and sal=(select max(sal) from emp where job='CLERK');

13) Display the names of the employees who earn highest salary in their respective departments.
SQL> select ename,sal,deptno from emp e where sal in(select max(sal) from emp m where m.deptno = e.deptno);

14) Display the current '15-Aug-2012' as 15th wednesday August twenty twelve.
SQL> select to_char(to_date('15-Aug-2012'),'ddth day Month year') from dual;

15) Display the Employee name and Managers names.
SQL> select e.ename employee, m.ename manager from emp e, emp m where m.empno=e.mgr;

16) Find out top 5 earners of company.
SQL> select * from (select * from emp order by sal desc) where rownum <= 5;

17) Display the department name and total number of employees in each department.
SQL> select dname, count(ename) from emp, dept where emp.deptno=dept.deptno group by dname;

18) Increase salary of all managers by 10%.
SQL> update emp set sal=sal*1.1 where empno in (select mgr from emp);

19) Delete duplicate department from dept table.
SQL> delete from dept where rowid not in (select max(rowid) from dept group by dname);

20) Create a backup of employee table with emp_tbd name
SQL> create table emp_tbd as select * from emp;

21) Get the details of the employee getting 3rd highest salary
SQL> select * from(select e.*, dense_rank() over (order by sal desc) r from emp e) where r=3;

22) Get the details of the employees getting department wise 3rd highest salary
SQL> select * from(select empno, ename, sal, dname, dense_rank() over (partition by dname order by sal desc) r from scott.emp e, scott.dept d where e.deptno=d.deptno) where r=3;

For Questions related to C Programming: http://cbasicprogram.blogspot.in/

Related Posts
- Difference Between Cursor And Ref Cursor
- Query optimization tips for Oracle
- How to connect SQLPlus without tnsnames.ora
- Oracle Database 12c New Features for Developers
- JSON in Oracle Database with Example
- New Features for Developers in Oracle 11g
- Top 15 new features of Oracle Database 12.2 for developers