Published April 11, 2016 by

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

Question.21     To display the total salaries payable to employees.
Question.22       To  display  the  maximum  salary  of  employee  working  as  a   salesman.
2   WHERE job = ‘SALESMAN’;
Question.23       To display the minimum salary of employee
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.
2 AS SELECT ename, job, sal FROM emp;
Question.30    To confirm the contents of table NEW
Question.31   Create an empty table.
2              AS SELECT * FROM dept
3              WHERE 1 = 2;
Question.32   To confirm the contents of the table empty.
Question.33   Create a new table using references, check constraints and   Default values.
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.
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.