# EMP:NUMBER (4)EMPNOVARCHAR2 (10)ΕΝΑΜΕVARCHAR2 (9)JOBNUMBER (4)MGRHIREDATEDATENUMBER (7, 2)SALNUMBER (7, 2)СOMMNUMBER (2)DE PTNΟDEPT:NUMBER (2)DE PTNΟVARCHAR2 (14)DNAMEVARCHAR2 (13)LOC

Question
8 views

- Find the employee in each department who makes the highest salary in their departments

check_circle

Step 1

To find the highest salary in each department.

• Left join is to be used to both the table together.
• Group by clause is used to make all the data in group by department.
• MAX() function is used to find the maximum of salary.
Step 2

Created schema of table EMP:

/* Create a table called EMP */

CREATE TABLE EMP(EMPNO number(4) PRIMARY KEY, ENAME varchar2(10),JOB varchar2(9), MGR number(4), HIREDATE DATE, SAL number(7,2), COMM number(7,2), DEPTNO number(2));

Created schema of table DEPT:

/* Create a table called DEPT */

CREATE TABLE DEPT(DEPTNO number(2) PRIMARY KEY, DNAME varchar2(14),LOC varchar2(13));

Step 3

Created some dummy record in the table in EMP:

/* Create few records in this table */

INSERT INTO EMP VALUES(1,'Harry','Sales',45, '2003-06-17', 6000.00,400.00,1001);

INSERT INTO EMP VALUES(2,'Alpha','Operation',84,'2007-05-21',3100.00, 1500.00,2001);

INSERT INTO EMP VALUES(3,'Frank','Sales',54,'1991-09-28',1700.00,600.00,2001);

INSERT INTO EMP VALUES(4,'James','Sales',98,'1992-01-23', 1050.00,900.00,3001);

INSERT INTO EMP VALUES(5,'Robert','IT',74,'1997-04-19',1700.00,  0.00, 1001...

### Want to see the full answer?

See Solution

#### Want to see this answer and more?

Solutions are written by subject experts who are available 24/7. Questions are typically answered within 1 hour.*

See Solution
*Response times may vary by subject and question.
Tagged in
EngineeringComputer Science