Oracle Interview Questions and answers with examples 3
Question.40 Display the name,job of the employees and display Executive if the job is Clerk , RM if the job is Manager else display the job.
Answer:
SELECT ename,job, DECODE(job, ‘CLERK’, ‘EXECUTIVE’, ‘MANAGER’, ‘RM’, job) FROM emp;
Question.41 To display employee name, department name and department number for all employees.
Answer:
SQL>SELECT ename, dname, emp.deptno
2 FROM emp, dept
3 WHERE emp.deptno = dept.deptno;
Question.42 To display employee name, department name and department number for employees of departments 20 to 40.
Answer:
SQL>SELECT ename, dname, emp.deptno
2 FROM emp, dept
3 WHERE emp.deptno = dept.deptno
4 AND dept.deptno BETWEEN 20 AND 40;
Question. 43 To display employee name, department name and department number for all employees.
Answer:
SQL>SELECT ename, dname, emp.deptno
2 FROM emp, dept
3 WHERE emp.deptno (+) = dept.deptno;
Question.44 Display name of employees and their managers
Answer: SQL>SET HEADING OFF
SQL >SELECT worker.ename||’works for’||manager.ename
FROM emp worker,emp manager
WHERE worker.mgr = manager.empno ;
Question.45 Display the name and job of all employees in department
Answer:
SQL>SELECT ename, job
2 FROM emp
3 WHERE dept no= (SELECT deptno
4 FROM emp
5 WHERE UPPER (ename)=’RAJA’);
Question. 46 Display number of employees who are getting salary above average salary.
Answer: SQL>SELECT sal, COUNT (DISTINCT empno)”Number of emp”
2 FROM emp GROUP BY sal
3 HAVING sal>(SELECT AVG (sal) FROM emp);
Question.47 Display the employees who work under the employee who is the manager of salesman of department 20.
Answer:
SQL>SELECT empno, ename, job FROM emp
2 WHERE mgr =(SELECT DISTINCT mgr FROM emp
3 WHERE job =’SALESMAN AND deptno = 30);
Question.48 Display names of employees who work in department same as that of any employee whose name begins with ‘M’
Answer:
SQL>SELECT ename, deptno
2 FROM emp
3 WHERE deptno IN (SELECT deptno FROM emp WHERE ename LIKE ‘M%’);
Question.49 Display employees having the same job as employees in Sales department.
Answer:
SQL>SELECT empno, ename, job FROM emp
2 WHERE job IN (SELECT job FROM emp, dept
3 WHERE dname=’SALES’
4 AND emp.deptno =dept.deptno);
Question.50 Display name and salary of all employees whose salary is greater than average employee salary, and who works in the same department as employee Raja.
Answer:
SQL>SELECT ename, sal, hiredate
2 FROM emp
3 WHERE sal > (SELECT AVG (sal)
4 FROM emp)
5 AND deptno IN (SELECT deptno
6 FROM emp
7 WHERE ename = ‘RAJA’);
Question.51 Create view containing employee number, name, job, department number for employees in department 20.
Answer:
SQL> CREATE VIEW emp20(ID,Name,Destination,Department)
2 AS SELECT empno,ename,job,deptno
3 FROM emp
4 WHERE deptno=20;
Question.52 Create view containing all columns from emp table for department 20 WITH CHECK option.
Answer:
SQL> CREATE VIEW empl20
2 AS SELECT * FROM emp
3 WHERE deptno=20
4 WITH CHECK OPTION;
Result View created.
Question. 53 Update empl20 and try to change the deptno of one of the employees to 30
Answer:
SQL> UPDATE empl20
2 SET deptno=30
3 WHERE empno=1235;
Question.54 Create a simple sequence for dept table. Start with 30 and increment by 10.
Answer:
SQL>CREATE SEQUENCE s_deptno
2 INCREMENT BY 10
3 START WITH 45;
Result Sequence created.
Question.55 To view the contents of the user_sequences table to confirm, that the created sequence is a part of the Data Dictionary.
Answer:
SQL>SELECT sequence_name,min_value,max_value,
2 last_number,increment_by
3 FROM user_sequences;
Question.56 Alter the existing sequence s1 with increment by 10, maximum Value 90, minimum value 10 and sequence should continue to generate even after it has reached its maximum value and have a cache value of 5.
Answer:
SQL>CREATE SEQUENCE s1
2 INCREMENT BY 10
3 MAXVALUE 90
4 MINVALUE 10
5 CYCLE
6 CACHE 5;
Result Sequence altered.
Visit: Learn Oracle DBA Online