4/11/16

Oracle Interview Questions and answers with examples-3

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;