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; [sociallocker]

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. [/sociallocker]

Visit:  Learn Oracle DBA Online