Question
Asked Dec 4, 2019
8 views

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

EMP:
NUMBER (4)
EMPNO
VARCHAR2 (10)
ΕΝΑΜΕ
VARCHAR2 (9)
JOB
NUMBER (4)
MGR
HIREDATE
DATE
NUMBER (7, 2)
SAL
NUMBER (7, 2)
СOMM
NUMBER (2)
DE PTNΟ
DEPT:
NUMBER (2)
DE PTNΟ
VARCHAR2 (14)
DNAME
VARCHAR2 (13)
LOC
help_outline

Image Transcriptionclose

EMP: NUMBER (4) EMPNO VARCHAR2 (10) ΕΝΑΜΕ VARCHAR2 (9) JOB NUMBER (4) MGR HIREDATE DATE NUMBER (7, 2) SAL NUMBER (7, 2) СOMM NUMBER (2) DE PTNΟ DEPT: NUMBER (2) DE PTNΟ VARCHAR2 (14) DNAME VARCHAR2 (13) LOC

fullscreen
check_circle

Expert Answer

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

Check out a sample Q&A here.

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

Related Computer Science Q&A

Find answers to questions asked by student like you
Show more Q&A
add
question_answer

Q: 1. Write a program in python with no imports named filemaker.py that will be used to store the first...

A: The name of the program is filereader.pyOpen the file named friends.txtUse a variable named accumula...

question_answer

Q: *Initial Class Diagram is attached below Refine the UML class diagram so that it demonstrates the fo...

A: The class diagram defines the types of objects within the system and distinct kind of relationship w...

question_answer

Q: Exercise 1 Consider a hash table of size 13, and assume open addressing with linear probing   Show ...

A:  As the given size of the table is 13, as well as the number of elements to be inserted in the table...

question_answer

Q: Microsoft Excel 2016 has many new and improved functions. What do you think is the most beneficial f...

A: Beneficial functions in MS-Excel 2016:3D data Maps:In the 2013 version, the 3D data map was actually...

question_answer

Q: Which of the following best describes a reason to use SMB signing? To confirm a packet's poi...

A: Best reason to use SMB (Server Message Block) signing is:SMB is also known as “Common Internet File ...

question_answer

Q: I have to add this solution to a conversion code. This chapter is focused on  strings and string man...

A: Program Instructions:  ⦁ Create a Python3 program for the given problem of string slicing, search, a...

question_answer

Q: Write a program that promotes the user to enter the number of students and each student's name and s...

A: The java program and the approach that prompts the user to enter the names and the scores of the stu...

question_answer

Q: In Book computer networking a top-down approach sixth edition 4.4 (Network Address Translation (NAT)...

A: NAT table stands for Network Address Translation table.It is used by routers to translate private IP...

question_answer

Q: How can I create the following table using printf() method In Java Eclipce.

A: printf() method in java: In java, printf() belongs to the PrintStream class.The printf() method is u...