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 | 02-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 | 09-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 | 08-Sep-95 | 1500 | 30 | |
| 7876 | AJIT | CLERK | 7788 | 12-Jan-96 | 1100 | 20 | |
| 7900 | JACK | CLERK | 7698 | 03-Dec-95 | 950 | 30 | |
| 7902 | ANIL | ANALYST | 7566 | 03-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;
Learn Oracle and pl/sql online Course By Expert Trainer