Oracle Interview Questions and answers with examples

Emp Table:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-Dec-95 800 20
7499 ASHA SALESMAN 7698 20-Feb-95 1600 300 30
7521 WASIM SALESMAN 7698 22-Feb-95 1250 500 30
7566 RAJA MANAGER 7839 2-Apr-95 2975 20
7654 MARIA SALESMAN 7698 28-Sep-95 1250 1400 30
7698 BILL MANAGER 7839                    01-MAY-95 2850 30
7782 SUJIT MANAGER 7839 9-Jun-95 2450 10
7788 SUNIL ANALYST 7566                    09-DEC-96 3000 20
7839 CHRIS PRESIDENT                    17-NOV-95 5000 20
7844 TINA SALESMAN 7698 8-Sep-95 1500 30
7876 AJIT CLERK 7788 12-Jan-96 1100 20
7900 JACK CLERK 7698 3-Dec-95 950 30
7902 ANIL ANALYST 7566 3-Dec-95 3000 20
7934 MILAN CLERK 7782 23-Jan-96 1300 10

Dept Table:

DEPTNO           DNAME                      LOC

———————————————————-

10           ACCOUNTING                   NEW YORK

20           RESEARCH                         DALLAS

30           SALES                                 CHICAGO

40           OPERATIONS                    BOSTON

Question. 1    To display the name of all departments.

Answer:     SQL>SELECT dname FROM dept;

Question.2   To display employee name, sal, job and deptno for all employees   in department 30.

Answer:

SQL>SELECT ename,sal,job,deptno

2              FROM emp

3              WHERE deptno=30; [sociallocker]

Question.3  To display employee name,job,salary,department number for those  employees in department 20 who earn 2000 or more, as well as all   employees in department 30.         

Answer:

SQL>SELECT ename,job,sal,deptno

2              FROM emp

3              WHERE sal>=2000 AND deptno=20 OR deptno=30;

Question.4   To display employee name, job, salary, department number for     those employees in department 20 or 30 who earn 2000 or more.

Answer:

                    SQL> SELECT ename,job,sal,deptno

2    FROM emp

3    WHERE sal>=2000 AND(deptno=20 OR deptno=30);

Question. 5   To display employee name,job for all clerks and analysts.

Answer:

SQL>SELECT ename, job

                2 FROM emp

                3 WHERE job IN (‘CLERK’,’ANALYST’ );

Question.6   To display employee number,name, department number for those   hired between January 1,1995 and January 1,1996.

Answer:

SQL>SELECT empno,ename,deptno,

2              hiredate FROM emp

3              WHERE hiredate BETWEEN ‘01-JAN-95’

4              AND ‘01-JAN-96’;

Question.7   To display name, job, salary for those whose name begins with  letter A.

Answer:

SQL Command  SQL> SELECT ename,job,sal

2              FROM emp

3              WHERE ename LIKE ‘A%’;

Question.8   To display employee name, job, salary, department number for   employees whose name has I as the second character.

Answer:

SQL Command  SQL> SELECT ename,job,sal,deptno

2              FROM emp

3              WHERE ename LIKE ‘_I%’;

Question.9  To display employee name, commission for employees who are    not eligible for commission.

Answer:               SQL> SELECT ename,comm

                                        2 FROM emp

                                      3 WHERE comm IS NULL;

Question.  10   List the name, salary and PF amount of all the employees (PF iscalculated as 10% of salary)

Answer:

                SQL> SELECT ename,sal, sal*.1

                            2 FROM emp ;

Question.11  To display name and salaries of employees in department 20 in  ascending order of salary.

Answer:               SQL> SELECT ename, sal, deptno

                                  FROM emp

                                  WHERE deptno=20

                                 ORDER BY SAL;

Question. 12   To display name and salaries of employees in department 20 in  ascending order of salary, using the position of column in the  syntax.                           

Answer:

SQL> SELECT ename,sal,deptno

2              FROM emp

3              WHERE deptno=20

4              ORDER BY 2;

Question.13   To  display number,  name,  department  number,  salary  of  all  employees.  Order  the  result  by  department  number  and  in      descending order of salary.

Answer:

SQL> SELECT empno,ename,deptno,sal

2              FROM emp

3              ORDER BY deptno,sal DESC;

Question.14   Display all data from table dept.

Answer:               SQL>SELECT * FROM dept;

Question. 15  To display distinct values returned by either query.

Answer:

SQL>SELECT dname FROM dept UNION 2 SELECT dname FROM dept1;

Question.16  To display all the values returned by both queries.

Answer:     

SQL>SELECT dname FROM dept UNION ALL

2 SELECT dname FROM dept1;

Question. 17  To display common values which are present in the values returned  by both the queries.

Answer:

SQL>SELECT dname FROM dept INTERSECT

2 SELECT dname FROM dept1;

Question.18  To display values returned by the first query which are not present  in the values returned by the second query.

Answer: 

SQL>SELECT dname FROM dept MINUS

2 SELECT dname FROM dept1;

Question. 19   To display values returned by the first query which are not present   in the values returned by the second query.

Answer:

 SQL>SELECT dname FROM dept MINUS

2 SELECT * FROM dept1; [/sociallocker]

Learn Oracle and pl/sql online Course By Expert Trainer