SQL code copied to buffer

SQLtest

- SQL practice online

 Here you can test your SQL knowledge, practice in SQL queries write and prepare to job interview.
RU
Task 15: 
Find employees whose salaries were increased several times within one year.
The result should contain the following columns EMP_NO, FIRST_NAME, LAST_NAME - employee data, CHANGES_YEAR - year of salary increase, CHANGES_COUNT - number of increases per year. Sort the result by employee number

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.

Employee Database (Firebird) description

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:

Table: COUNTRIES

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
Have a questions? Ask in our telegram chat!
Like the site? Share it!
© 2023-2024 SQLtest.online About Privacy policy