Task 24:

Top Earners by Department

Firebird

Find employees with the highest salary in their respective departments. 
Display a table with columns DEPARTMENT - department name, EMP_NO, FIRST_NAME, LAST_NAME - employee data, SALARY - his salary. Sort the data in descending order of salary.
Solve the problem using sub-query.

Expected result Columns: 5 · Rows: 19

DEPARTMENT	EMP_NO	FIRST_NAME	LAST_NAME	SALARY

Sample rows open after wrong checks: 0 of 2

Write your query below and click "Check it!". Tables are described in the right pane.

Tutorial

The query result will appear here. Ctrl+Enter - Run query

Employee Database: table structure and overview

The Employee database (Firebird) is a sample dataset used to learn SQL and explore Firebird DBMS capabilities.

This page describes table structure, key columns, and relationships for practical SQL querying.

The Employee database contains 9 main tables.

ER diagram of the Employee database

More about the Employee database: schema, sample queries and all exercises →

List of tables

COUNTRY - countries table.

COUNTRYName of the country
CURRENCYCurrency used in the country

COUNTRY	CURRENCY
USA	Dollar

JOB - company's staff schedule.

JOB_CODEJob code
JOB_GRADEJob grade
JOB_COUNTRYCountry associated with the job
JOB_TITLEJob title
MIN_SALARYMinimum salary for the job
MAX_SALARYMaximum salary for the job
JOB_REQUIREMENTJob requirements
LANGUAGE_REQLanguage requirements

JOB_CODE	JOB_GRADE	JOB_COUNTRY	JOB_TITLE	MIN_SALARY	MAX_SALARY	JOB_REQUIREMENT	LANGUAGE_REQ
CEO	1	USA	Chief Executive Officer	130000.00	250000.00	No specific requirements.	[null]

FOREIGN KEY (JOB_COUNTRY) REFERENCES COUNTRY(COUNTRY)

DEPARTMENT - company divisions.

DEPT_NODepartment number
DEPARTMENTDepartment name
HEAD_DEPTHead department (can be null)
MNGR_NOManager number
BUDGETDepartment budget
LOCATIONDepartment location
PHONE_NOPhone number for the department

DEPT_NO	DEPARTMENT	HEAD_DEPT	MNGR_NO	BUDGET	LOCATION	PHONE_NO
000	Corporate Office	[null]	105	1000000.00	Monterey	(408) 555-1234

FOREIGN KEY (HEAD_DEPT) REFERENCES DEPARTMENT(DEPT_NO)

EMPLOYEE - list of employees.

EMP_NOEmployee number
FIRST_NAMEFirst name of the employee
LAST_NAMELast name of the employee
PHONE_EXTPhone extension for the employee
HIRE_DATEDate of employee's hire
DEPT_NODepartment number
JOB_CODEJob code for the employee
JOB_GRADEJob grade for the employee
JOB_COUNTRYCountry associated with the employee's job
SALARYSalary of the employee
FULL_NAMEFull name of the employee

EMP_NO	FIRST_NAME	LAST_NAME	PHONE_EXT	HIRE_DATE	DEPT_NO	JOB_CODE	JOB_GRADE	JOB_COUNTRY	SALARY	FULL_NAME
2	Robert	Nelson	250	1988-12-28 00:00:00	600	VP	2	USA	105900.00	Nelson, Robert

FOREIGN KEY (DEPT_NO) REFERENCES DEPARTMENT(DEPT_NO)
FOREIGN KEY (JOB_CODE) REFERENCES JOB(JOB_CODE)

PROJECT - list of projects.

PROJ_IDProject ID
PROJ_NAMEProject name
PROJ_DESCProject description
TEAM_LEADERTeam leader for the project
PRODUCTProduct associated with the project

PROJ_ID	PROJ_NAME	PROJ_DESC	TEAM_LEADER	PRODUCT
VBASE	Video Database	Development of a video database management system for managing video distribution on demand.	45	software

FOREIGN KEY (TEAM_LEADER) REFERENCES EMPLOYEE(EMP_NO)

EMPLOYEE_PROJECT - employee-project mapping.

EMP_NOEmployee number
PROJ_IDProject ID

EMP_NO	PROJ_ID
144	DGPII

FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE(EMP_NO)
FOREIGN KEY (PROJ_ID) REFERENCES PROJECT(PROJ_ID)

PROJ_DEPT_BUDGET - project budgets.

FISCAL_YEARFiscal year
PROJ_IDProject ID
DEPT_NODepartment number
QUART_HEAD_CNTQuarter headcount (can be null)
PROJECTED_BUDGETProjected budget for the fiscal year

FISCAL_YEAR	PROJ_ID	DEPT_NO	QUART_HEAD_CNT	PROJECTED_BUDGET
1994	GUIDE	100	[null]	200000.00

FOREIGN KEY (PROJ_ID) REFERENCES PROJECT(PROJ_ID)
FOREIGN KEY (DEPT_NO) REFERENCES DEPARTMENT(DEPT_NO)

SALARY_HISTORY - history of employee salary changes.

EMP_NOEmployee number
CHANGE_DATEDate of salary change
UPDATER_IDUpdater ID
OLD_SALARYPrevious salary
PERCENT_CHANGEPercentage change in salary
NEW_SALARYNew salary after the change

EMP_NO	CHANGE_DATE	UPDATER_ID	OLD_SALARY	PERCENT_CHANGE	NEW_SALARY
28	1992-12-15 00:00:00	admin2	20000.00	10.000000	22000.000000

FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE(EMP_NO)

CUSTOMER - company clients.

CUST_NOCustomer number
CUSTOMERCustomer name
CONTACT_FIRSTFirst name of the contact person
CONTACT_LASTLast name of the contact person
PHONE_NOPhone number for the customer
ADDRESS_LINE1 Address line 1
ADDRESS_LINE2Address line 2 (can be null)
CITYCity of the customer
STATE_PROVINCEState or province of the customer
COUNTRYCountry of the customer
POSTAL_CODEPostal code of the customer
ON_HOLDOn hold status (can be null)

CUST_NO	CUSTOMER	CONTACT_FIRST	CONTACT_LAST	PHONE_NO	ADDRESS_LINE1	ADDRESS_LINE2	CITY	STATE_PROVINCE	COUNTRY	POSTAL_CODE	ON_HOLD
1001	Signature Design	Dale J.	Little	(619) 530-2710	15500 Pacific Heights Blvd.	[null]	San Diego	CA	USA	92121	[null]

FOREIGN KEY (COUNTRY) REFERENCES COUNTRY(COUNTRY)

SALES - list of sales.

PO_NUMBERPurchase order number
CUST_NOCustomer number associated with the order
SALES_REPSales representative number
ORDER_STATUSOrder status
ORDER_DATEDate of the order
SHIP_DATEDate of shipment
DATE_NEEDEDDate needed (can be null)
PAIDPayment status
QTY_ORDEREDQuantity ordered
TOTAL_VALUETotal value of the order
DISCOUNTDiscount applied
ITEM_TYPEType of item in the order
AGEDAged value

PO_NUMBER	CUST_NO	SALES_REP	ORDER_STATUS	ORDER_DATE	SHIP_DATE	DATE_NEEDED	PAID	QTY_ORDERED	TOTAL_VALUE	DISCOUNT	ITEM_TYPE	AGED
V91E0210	1004	11	shipped	1991-03-04 00:00:00	1991-03-05 00:00:00	[null]	y	10	5000.00	0.100000	hardware	1.000000000

FOREIGN KEY (CUST_NO) REFERENCES CUSTOMER(CUST_NO)
FOREIGN KEY (SALES_REP) REFERENCES EMPLOYEE(EMP_NO)

Below is a list of this DB views:

PHONE_LIST - employee phone list view.

EMP_NOEmployee number
FIRST_NAMEFirst name of the employee
LAST_NAMELast name of the employee
PHONE_EXTPhone extension for the employee
LOCATIONDepartment location
PHONE_NODepartment phone number

EMP_NO	FIRST_NAME	LAST_NAME	PHONE_EXT	LOCATION	PHONE_NO
2	Robert	Nelson	250	Monterey	(408) 555-1234