Find highest salary for each job.

For solving the above question we need to create two tables (EMPLOYEE , DEPARTMENT)

create table employee
(
empid number(5) primary key,
empname varchar2(50),
salary number(10,2),
Grade number(1),
deptno number(3) references department(deptno),
location varchar2(50),
desig varchar(20)
);

create table department
(
deptno number(3) primary key,
deptname varchar(50)
);

Then Insert few records in Employee and Department table.

EMPLOYEE

EMPID	EMPNAME	SALARY	GRADE	DEPTNO	LOCATION	DESIG
1	AMIT SEN	11000	1	1	BHOPAL	MANAGER
2	RAMESH THAKUR	11000	2	1	BHOPAL	CLERK
3	RAKESH	12000	1	1	INDORE	CLERK
4	SUMIT SAKET	9000	3	2	BHOPAL	CLERK
5	AMIT SEN	11000	2	2	BHOPAL	MANAGER
6	AMIT SEN	12000	2	5	INDORE	CLERK
7	AMIT SEN	15000	1	6	INDORE	MANAGER
8	ANKIT CHOUHAN	17000	1	6	INDORE	CLERK
9	RAMAKANT RAJPOOT	12000	1	6	BHOPAL	CLERK

DEPARTMENT

DEPTNO	DEPTNAME
1	SALES
2	PRODUCTION
3	PURCHASE
4	MARKETING
5	ACCOUNTS
6	HR
7	ADMIN

SQL :

select deptno,MAX(salary) from employee GROUP BY deptno;

RESULT :

DEPTNO	MAX(salary)
1	12000
2	11000
5	12000
6	17000