Task 171:

Staff Availability Report

Get a list of engineers (Eng) and managers (Mngr) who are not engaged in any current projects. 
Output the result with the columns EMP_NO, FULL_NAME, JOB_CODE and JOB_COUNTRY sorted first by country, then by last name.

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

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

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.

Practice

Lessons

Playground

Test

Staff Availability Report

Login

Register

Staff Availability Report