Oracle Interview Questions and answers with examples 2

Question. 20     To display the number of employees working with the company.

Answer:               SQL>SELECT COUNT(*) FROM emp;

Question.21     To display the total salaries payable to employees.

Answer:                SQL>SELECT SUM(sal) FROM emp; [sociallocker]

Question.22       To  display  the  maximum  salary  of  employee  working  as  a   salesman.

Answer:                SQL>SELECT MAX(sal) FROM emp

2   WHERE job = ‘SALESMAN’;

Question.23       To display the minimum salary of employee

Answer:                 SQL>SELECT  MIN(sal) FROM emp

Question. 24    To display the average salary and number of employees working in   department 20.     

Answer:                SQL>SELECT  AVG(sal) , COUNT(*) FROM emp

2     WHERE deptno = 20;

Question.25     List the department numbers and number of employees in each   department.                 

Answer:             SQL>SELECT        deptno, COUNT(*) FROM emp

2              GROUP BY deptno;

Question. 26     List the jobs and the number of employees in each job. The result   should be in descending order of the employees.

Answer:              SQL>SELECT        job, COUNT(*) FROM emp

2              GROUP BY job

3              ORDER BY 2 DESC ;

Question.27     List the total    salary, maximum and minimum salary and the  average salary of employees job wise, for department number 20  only.                                   

Answer:              SQL>SELECT  job, SUM(sal), AVG(sal), MAX(sal), MIN(sal)

2              FROM emp WHERE deptno = 20

3              GROUP BY job;

Question. 28   List the average salary for all departments employing more than   five people.

Answer:                SQL>SELECT  deptno, AVG(sal) FROM emp

2              GROUP BY deptno

3              Having count(*) > 5;

Question.29    Create a new table using AS clause.

Answer:                   SQL>CREATE TABLE new

2 AS SELECT ename, job, sal FROM emp;

Question.30    To confirm the contents of table NEW

Answer:                 SQL>SELECT * FROM new;

Question.31   Create an empty table.    

Answer:                  SQL>CREATE TABLE empty

2              AS SELECT * FROM dept

3              WHERE 1 = 2;

Question.32   To confirm the contents of the table empty.

Answer:                   SQL>SELECT * FROM empty;

Question.33   Create a new table using references, check constraints and   Default values.

Answer:                    SQL>CREATE TABLE SALESTAB

2              (id NUMBER(2),

3              custid REFERENCES customer,

4              orddate DATE DEFAULT SYSDATE,

5              shipdate DATE DEFAULT SYSDATE,

6              status CHAR(1),

7              CHECK(status IN(‘A’,’N’)));

Question. 34   Change the commission of employee 1234 to 555 and job to SALESMAN.

Answer:                   SQL>UPDATE emp

2              SET comm = 555, job=’SALESMAN’

3              WHERE empno = 1234;

Question. 35   Increase salary of all employees of department 30 to 1.5 times the    previous salary.

Answer:                    SQL>UPDATE emp

2 SET sal = sal *1.5

3 WHERE deptno = 30;

Question.36    Update loc of departments 40,60 of dept1 table to that of loc of  department  45  of  dept  table.(use  of  query  clause  of  update  statement)                                                                    

Answer:                    SQL>UPDATE dept1

2 SET loc=(SELECT loc FROM dept WHERE deptno=45)

3 WHERE deptno IN (40,60);

Question. 37    Confirm the changes made by the above statement.

Answer:                     SQL>SELECT * FROM dept1;

Question. 38   Display employee name, salary, 13% of salary as bonus rounded to  near whole number.              

Answer:                      SQL > SELECT ename, sal, ROUND(sal * 0.13 , 0) BONUS    FROM emp;

Question.39   Display employee name, salary, 13% of salary as bonus truncated   to near whole number.            

Answer:                       SQL > SELECT ename, sal, TRUNC(sal * 0.13 , 0) BONUS

FROM emp;

Get A live Demo  Oracle DBA Online Course