Hi! Could we please enable some services and cookies to improve your experience and our website?

Privacy & Cookie Policy.
SQL code copied to buffer

SQLtest.online

Practice

Lessons

Test

RU PT FR
Task 14:

Find foreign employees

Write a query for retrieve list of all employees working outside the US.
The result must contain all columns from the table EMPLOYEE.

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.
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 showing table relationships 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
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.