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

17 comments:

  1. it is very nice perfect notes for begi...

    ReplyDelete
    Replies
    1. Keep coming for other useful posts under "Oracle SQL" tag

      Delete
  2. This type of "quick reference" is very useful, thanks for sharing!

    ReplyDelete
    Replies
    1. Keep visiting for other useful articles

      Delete
  3. Nash Somaroo - South AfricaSeptember 3, 2012 at 5:00 PM

    Thank you, Thank you, Thank you.
    Your notes are extremely helpful to assist a beginner in Oracle.

    ReplyDelete
    Replies
    1. Glad that it helped you, keep visiting for more useful posts.

      Delete
  4. Thanks nimish - great for beginners! Found some good ones that are more advanced here:

    Complex sql interview questions

    ReplyDelete
  5. Hi ! can anyone explain me about pragma init? I have gone through some articles but I am not able to understand.

    Thanks in advance

    ReplyDelete
    Replies
    1. check this link: http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/errors.htm#i1863

      Delete
  6. Good stuff, all pretty straightforward but number 14 is wrong

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

    You would need to specify "day" to output "wednesday"

    ReplyDelete
    Replies
    1. and also in question i forgot to specify august :) thanks !!!

      Delete
  7. hi, I want a query, i have emp and dept table. both table having empid .I want a query to the show id whose empid is holding all dept name.

    ReplyDelete
    Replies
    1. Do you mean, you want to search a employee who is member of all the departments ?

      Delete
    2. SELECT E.EID,D.EID FROM EMP E,DEPT D WHERE E.EID=D.EID;

      Delete
  8. Hi,can u please send me questions on subquery

    ReplyDelete
  9. helpful for beginner......

    ReplyDelete