SQL Practice: Get list of highly paid employees

SQL code copied to buffer

Task 18:

Find employees with the maximum salary in his department.
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 a window function.

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

To write the answer, use Firebird syntax. Descriptions of the tables are given in the right panel.

Explore over 280 diverse tasks on our platform.

Employee Database (Firebird)

Employee is a sample database that comes with the Firebird cross-platform database management system. You can use this database to explore Firebird SQL and other DBMS features.

Below is a list of this DB tables:

COUNTRY - table of countries.
JOB - table of jobs.
DEPARTMENT - table of departments.
EMPLOYEE - table of employees.
PROJECT - table of projects.
EMPLOYEE_PROJECT - table of employee to project relations.
PROJ_DEPT_BUDGET - table of project budgets.
SALARY_HISTORY - table of employees salary changes history.
CUSTOMER - table of customers.
SALES - table of sales.

Table COUNTRY

Table columns:

COUNTRY - Name of the country.
CURRENCY - Currency used in the country.

COUNTRY	CURRENCY
USA	Dollar

Table JOB

Table columns:

JOB_CODE - Job code.
JOB_GRADE - Job grade.
JOB_COUNTRY - Country associated with the job.
JOB_TITLE - Job title.
MIN_SALARY - Minimum salary for the job.
MAX_SALARY - Maximum salary for the job.
JOB_REQUIREMENT - Job requirements.
LANGUAGE_REQ - Language 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]

Table DEPARTMENT

Table columns:

DEPT_NO - Department number.
DEPARTMENT - Department name.
HEAD_DEPT - Head department (can be null).
MNGR_NO - Manager number.
BUDGET - Department budget.
LOCATION - Department location.
PHONE_NO - Phone number for the department.

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

Table EMPLOYEE

Table columns:

EMP_NO - Employee number.
FIRST_NAME - First name of the employee.
LAST_NAME - Last name of the employee.
PHONE_EXT - Phone extension for the employee.
HIRE_DATE - Date of employee's hire.
DEPT_NO - Department number.
JOB_CODE - Job code for the employee.
JOB_GRADE - Job grade for the employee.
JOB_COUNTRY - Country associated with the employee's job.
SALARY - Salary of the employee.
FULL_NAME - Full 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

Table PROJECT

Table columns:

PROJ_ID - Project ID.
PROJ_NAME - Project name.
PROJ_DESC - Project description.
TEAM_LEADER - Team leader for the project.
PRODUCT - Product associated with the project.

PROJ_ID	PROJ_NAME	PROJ_DESC	TEAM_LEADER	PRODUCT
VBASE	Video Database	Design a video database management system for controlling on-demand video distribution.	45	software

Table EMPLOYEE_PROJECT

Table columns:

EMP_NO - Employee number.
PROJ_ID - Project ID.

EMP_NO	PROJ_ID
144	DGPII

Table PROJ_DEPT_BUDGET

Table columns:

FISCAL_YEAR - Fiscal year.
PROJ_ID - Project ID.
DEPT_NO - Department number.
QUART_HEAD_CNT - Quarter headcount (can be null).
PROJECTED_BUDGET - Projected budget for the fiscal year.

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

Table SALARY_HISTORY

Table columns:

EMP_NO - Employee number.
CHANGE_DATE - Date of salary change.
UPDATER_ID - Updater ID.
OLD_SALARY - Previous salary.
PERCENT_CHANGE - Percentage change in salary.
NEW_SALARY - New 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

Table CUSTOMER

Table columns:

CUST_NO - Customer number.
CUSTOMER - Customer name.
CONTACT_FIRST - First name of the contact person.
CONTACT_LAST - Last name of the contact person.
PHONE_NO - Phone number for the customer.
ADDRESS_LINE1 - Address line 1.
ADDRESS_LINE2 - Address line 2 (can be null).
CITY - City of the customer.
STATE_PROVINCE - State or province of the customer.
COUNTRY - Country of the customer.
POSTAL_CODE - Postal code of the customer.
ON_HOLD - On 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]

Table SALES

Table columns:

PO_NUMBER - Purchase order number.
CUST_NO - Customer number associated with the order.
SALES_REP - Sales representative number.
ORDER_STATUS - Order status.
ORDER_DATE - Date of the order.
SHIP_DATE - Date of shipment.
DATE_NEEDED - Date needed (can be null).
PAID - Payment status.
QTY_ORDERED - Quantity ordered.
TOTAL_VALUE - Total value of the order.
DISCOUNT - Discount applied.
ITEM_TYPE - Type of item in the order.
AGED - Aged 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

SQLtest: interactive SQL practice

Employee Database (Firebird)

Table COUNTRY

Table JOB

Table DEPARTMENT

Table EMPLOYEE

Table PROJECT

Table EMPLOYEE_PROJECT

Table PROJ_DEPT_BUDGET

Table SALARY_HISTORY

Table CUSTOMER

Table SALES