Answer to Question #235319 in Databases | SQL | Oracle | MS Access for Tarurendra Kushwah

Question

Consider a relation Employee with following attributes:

Name Null Type

-------------------------------- ----------------------- -------------------------

EMPNO NOT NULL NUMBER(4)

ENAME VARCHAR2(10)

JOB VARCHAR2(9)

MGR NUMBER(4)

HIREDATE DATE

SAL NUMBER(7,2)

COMM NUMBER(7,2)

DEPTNO NUMBER(3)

AGE NUMBER(3)

ESAL NUMBER(10)

a. List minimum , maximum , average salaries of employee.

b. What is the difference between maximum and minimum salaries of employees in the organization?

c. Display all employee names and salary whose salary is greater than minimum salary of the company and job title starts with ‘M’.

d. Display total salary spent for each job category.

e. Display lowest paid employee details under each manager.

Answer 1

a)SELECT SUM(ESAL), AVG(ESAL), MAX(ESAL), MIN(ESAL)
FROM employee;

b)SELECT MAX(ESAL) - MIN(ESAL) DIFFERENCE
FROM employee;

c) SELECT ENAME, ESAL
 from employee 
 where ESAL > (SELECT min(ESAL) from employee)) //innerQuery
 and JOB like "M%"

d)SELECT JOB, SUM(SAL) 
FROM  employee 
GROUP BY  JOB;

e)SELECT MGR, MIN(ESAL)
FROM employee
WHERE MGR IS NOT NULL
GROUP BY MGR
ORDER BY MIN(ESAL) DESC;