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