Prática SQL: Relatório de disponibilidade de pessoal

Código SQL copiado para a área de transferência

Tarefa 12:

Obtenha uma lista de engenheiros (Eng) e gerentes (Mngr) não envolvidos em nenhum dos projetos atuais. Produza o resultado com as colunas EMP_NO, FULL_NAME, JOB_CODE e JOB_COUNTRY ordenadas primeiro por país, depois por sobrenome.

Escreva sua solicitação no campo abaixo e clique no botão "Verificar!"

Para escrever a resposta, use a sintaxe Firebird. As descrições das tabelas estão no painel à direita.

Explore mais de 280 tarefas diversas na nossa plataforma.

Faça login para salvar seu progresso.

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.

Introdução à Linguagem SQL: Abordagem prática para iniciantes

Introdução à Linguagem SQL: Abordagem prática para iniciantes

Atualmente as empresas estão coletando dados a taxas exponenciais e mesmo assim poucas pessoas sabem como acessá-los de maneira relevante. Se você trabalha em uma empresa ou é profissional de TI, este curto guia prático lhe ensinará como obter e transformar dados com o SQL de maneira significativa. Você dominará rapidamente os aspectos básicos do SQL e aprenderá como criar seus próprios bancos de dados. O autor Thomas Nield fornece exercícios no decorrer de todo o livro para ajudá-lo a praticar em casa suas recém descobertas aptidões no uso do SQL, sem precisar empregar um ambiente de servidor de banco de dados. Além de aprender a usar instruções-chave do SQL para encontrar e manipular seus dados, você descobrirá como projetar e gerenciar eficientemente bancos de dados que atendam às suas necessidades.

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