employee table
CREATE TABLE public.employee
(
id integer NOT NULL,
employee_name character varying(255),
salary double precision,
id_dept integer,
CONSTRAINT prim_id PRIMARY KEY (id),
CONSTRAINT department_id FOREIGN KEY (id_dept)
REFERENCES public.department (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.employee
OWNER TO postgres;
department table
CREATE TABLE public.department
(
id integer NOT NULL,
name character varying(255),
CONSTRAINT dep_id PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.department
OWNER TO postgres;
Highest salary of employee according to each department:
select d.name, e.employee_name, e.salary from employee as e
inner join department as d
on e.id_dept = d.id
where (e.id_dept, e.salary) in(
select e.id_dept, max(e.salary) from employee as e
group by id_dept)
select * from employee order by salary desc limit 1 offset 1