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; 
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;