SQL code copied to buffer

SQLtest.online
Task 40: 
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.

Use Firebird syntax to write your answer. Descriptions of tables are provided at the bottom of the screen.

Get hint Copy code Clear editor

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 - 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]
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
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
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
EMPLOYEE_PROJECT - employee-project mapping.
  • EMP_NOEmployee number
  • PROJ_IDProject ID
EMP_NO PROJ_ID
144 DGPII
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
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
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]
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
Learning SQL: Generate, Manipulate, and Retrieve Data by Alan Beaulieu
Learning SQL: Generate, Manipulate, and Retrieve Data by Alan Beaulieu
As data floods into your company, you need to put it to work right away―and SQL is the best tool for the job. With the latest edition of this introductory guide, author Alan Beaulieu helps developers get up to speed with SQL fundamentals for writing database applications, performing administrative tasks, and generating reports. You’ll find new chapters on SQL and big data, analytic functions, and working with very large databases.