Task 174:

Top Earners by Department

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.

Use Firebird syntax to write your answer. Descriptions of tables are provided in the right pane.

Write your request in the field below and click the "Check it!" button.

Tutorial Get hint

Copy code Clear editor

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

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

Head First SQL: A Learner's Guide to Querying and Managing Data by Kimberly Fessel

Do you have an abundance of data but don't know how to make sense of it? Do you want to gain useful insights from your data, but you're not sure where to begin? Mining data is a vital, well-paying skill, and SQL provides the most fundamental way to query and manage data. But learning SQL can be intimidating. This thoroughly revised book teaches you SQL fundamentals in a painless and enjoyable manner. With the Head First series’ hands-on, conversational style, you'll quickly grasp SQL concepts, then move to intermediate topics, including subqueries and joins. You'll gain the knowledge, skills, and confidence necessary to get the most out of your data with SQL.

Practice

Lessons

Playground

Test

Top Earners by Department

Login

Register

Top Earners by Department