Atividade Unidade IV - Linguagem de Banco de Dados - Cruzeiro do Sul Virtual

Prévia do material em texto

AS – Unidade IV 
Ciência de Dados Cruzeiro do Sul Virtual
Linguagem de Banco de Dados
PERGUNTA 1
Com base no seguinte DER:
Qual é a sintaxe CORRETA para mostrar o número do departamento, último nome e código do 
cargo para cada empregado que esteja no departamento Executive?
a. select department_id, last_name, job_id
from employees
where department_id=(select department_id from departments
where department_name='Executive')
b. select department_id, last_name, job_id
from employees
where department_id between (select department_id from
departments
where department_name='Executive')
c. select department_id, last_name, job_id
from employees
where department_id==(select department_id from departments
where department_name='Executive'
d. select department_id, last_name, job_id
from employees
where department_id<(select department_id from departments
where department_name='Executive')
e. select department_id, last_name, job_id
from employees
where department_id>(select department_id from departments
where department_name='Executive')
 
PERGUNTA 2
Com base no seguinte DER:
Qual é a construção CORRETA para a seguinte solicitação?
Encontrar os empregados que têm, no mínimo, uma pessoa subordinada aos quais.
a. SELECT EMPLOYEE_ID, MANAGER_ID, FIRST_NAME, LAST_NAME,
JOB_ID, DEPARTMENT_ID, SALARY
FROM EMPLOYEES E
WHERE EXISTS IN (SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE EMPLOYEES.MANAGER_ID =
E. EMPLOYEE_ID)
ORDER BY EMPLOYEE_ID;
b. SELECT EMPLOYEE_ID, MANAGER_ID, FIRST_NAME, LAST_NAME,
JOB_ID, DEPARTMENT_ID, SALARY
FROM EMPLOYEES E
WHERE EMPLOYEE_ID >= (SELECT EMPLOYEE_ID FROM EMPLOYEES
WHERE EMPLOYEES.MANAGER_ID =
E. EMPLOYEE_ID)
ORDER BY EMPLOYEE_ID;
c. SELECT EMPLOYEE_ID, MANAGER_ID, FIRST_NAME, LAST_NAME,
JOB_ID, DEPARTMENT_ID, SALARY
FROM EMPLOYEES E
WHERE NOT EXISTS (SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE EMPLOYEES.MANAGER_ID =
E. EMPLOYEE_ID)
ORDER BY EMPLOYEE_ID;
d. SELECT EMPLOYEE_ID, MANAGER_ID, FIRST_NAME, LAST_NAME,
JOB_ID, DEPARTMENT_ID, SALARY
FROM EMPLOYEES E
WHERE EXISTS (SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE EMPLOYEES.MANAGER_ID =
E. EMPLOYEE_ID)
ORDER BY EMPLOYEE_ID;
e. SELECT EMPLOYEE_ID, MANAGER_ID, FIRST_NAME, LAST_NAME,
JOB_ID, DEPARTMENT_ID, SALARY
FROM EMPLOYEES E
WHERE EXISTS (SELECT EMPLOYEE_ID
FROM EMPLOYEES)
ORDER BY EMPLOYEE_ID;
PERGUNTA 3
Com base no seguinte DER:
Qual é a forma CORRETA para criar uma query que mostre o último nome, número do 
departamento e código do cargo de todos os empregados cuja localização do departamento seja 
igual a 1.700?
a. select last_name "Último Nome",
department_id "Código do Depto",
job_id "Código Do Cargo"
from employees
where department_id not in(select department_id from departments
where Location_id=1700)
b. select last_name "Último Nome",
department_id "Código do Depto",
job_id "Código Do Cargo"
from employees
where department_id in (select department_id from departments
where location_id=1700)
c. select last_name "Último Nome",
department_id "Código do Depto",
job_id "Código Do Cargo"
from employees
where department_id =(select department_id from departments
where location_id=1700)
d. select last_name "Último Nome",
department_id "Código do Depto",
job_id "Código Do Cargo"
from employees
where department_id <=(select department_id from departments
where location_id=1700)
e. select last_name "Último Nome",
department_id "Código do Depto",
job_id "Código Do Cargo"
from employees
where department_id >(select department_id from departments
where location_id=1700)
 
PERGUNTA 4
Com base no seguinte DER:
Qual é a sintaxe CORRETA para selecionar nome, cargo e salário dos funcionários cuja 
remuneração seja maior que a média salarial de todos os empregados?
a. SELECT First_Name, Job_id, Salary
FROM Employees
WHERE Salary = (SELECT AVG(Salary)
FROM Employees)
b. SELECT First_Name, Job_id, Salary
FROM Employees
WHERE Salary > (SELECT SUM(Salary)
FROM Employees
c. SELECT First_Name, Job_id, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary)
FROM Employees)
d. SELECT First_Name, Job_id, Salary
FROM Employees
WHERE Salary >= (SELECT AVG(Salary)
FROM Employees)
e. SELECT First_Name, Job_id, Salary
FROM Employees
WHERE Salary > (SELECT MAX(Salary)
FROM Employees)