Exercicios resolvidos Banco de dados

Prévia do material em texto

Exercícios (LAB SQL) 
AULA 01 (INTRODUÇÃO SELECT) 
Você foi admitido como programador SQL da XYZW Corporation. Sua primeira tarefa é criar alguns relatórios com base 
nos dados das tabelas de recursos humanos. 
1. Sua primeira tarefa é determinar a estrutura da tabela DEPARTMENTS e seu conteúdo. 
DESCRIBE departments 
SELECT * 
FROM departments; 
2. Você precisa determinar a estrutura da tabela EMPLOYEES. 
DESCRIBE employees 
3. O departamento de recursos humanos deseja executar uma consulta para exibir o sobrenome, o código do 
cargo, a data de admissão e o telefone de cada funcionário, com o número do funcionário exibido primeiro. Forneça o 
apelido STARTDATE para a coluna HIRE_DATE. 
SELECT employee_id, last_name, job_id, hire_date AS StartDate, phone_integer 
FROM employees; 
4. O departamento de recursos humanos precisa de uma consulta para exibir todos os códigos de cargo 
exclusivos da tabela EMPLOYEES. 
SELECT DISTINCT job_id 
FROM employees; 
 
AULA 02 (SELECT COM RESTRIÇÕES) 
1. Em função de questões orçamentárias, o departamento precisa de um relatório com o sobrenome e o salário 
dos funcionários que ganham mais de US$ 12.000. 
SELECT last_name, salary 
FROM employees 
WHERE salary > 12000; 
2. Crie um relatório que exiba o sobrenome e o número do departamento do funcionário 176. 
SELECT last_name, department_id 
FROM employees 
WHERE employee_id = 176; 
3. O departamento de recursos humanos precisa localizar funcionários com altos e baixos salários. Crie uma 
query para exibir o sobrenome e o salário de todos os funcionários cuja faixa salarial não esteja entre US$ 5.000 e US$ 
12.000. 
SELECT last_name, salary 
FROM employees 
WHERE salary NOT BETWEEN 5000 AND 12000; 
4. Crie um relatório para exibir o sobrenome, o ID do cargo e a data de admissão dos funcionários cujos 
sobrenomes sejam Matos e Taylor. Organize a consulta em ordem crescente por data de admissão. 
SELECT last_name, job_id, hire_date 
FROM employees 
WHERE last_name IN ('Matos', 'Taylor') 
ORDER BY hire_date; 
5. Exiba o sobrenome e o número do departamento de todos os funcionários nos departamentos 20 e 50 em 
ordem alfabética crescente por nome. 
SELECT last_name, department_id 
FROM employees 
WHERE department_id IN (20, 50) ORDER BY last_name ASC; 
6. Construa uma query para exibir o sobrenome e o salário dos funcionários que ganham entre US$ 5.000 e US$ 
12.000 e estão no departamento 20 ou 50. Atribua às colunas os labels Employee e Monthly Salary, respectivamente. 
SELECT last_name "Employee", salary "Monthly Salary" 
FROM employees 
WHERE salary BETWEEN 5000 AND 12000 
AND department_id IN (20, 50); 
7. O departamento de recursos humanos precisa de um relatório que exiba o sobrenome e a data de admissão 
de todos os funcionários admitidos em 1994. 
SELECT last_name, hire_date 
FROM employees 
WHERE hire_date LIKE '1994%'; 
8. Crie um relatório que exiba o sobrenome e o cargo de todos os funcionários não subordinados a um gerente. 
SELECT last_name, job_id 
FROM employees WHERE manager_id IS NULL; 
9. Crie um relatório para exibir o sobrenome, o salário e a comissão de todos os funcionários que ganham 
comissão. Classifique os dados em ordem decrescente de salário e comissões. 
 
SELECT last_name, salary, commission_pct 
FROM employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC, commission_pct DESC; 
10. Os membros do departamento de recursos humanos desejam ter mais flexibilidade em relação às consultas 
criadas. Eles desejam um relatório que exiba o sobrenome e o salário dos funcionários que ganham mais do que uma 
quantia especificada pelo usuário após o prompt. (SÓ O SCRIPT) 
SELECT last_name, salary 
FROM employees 
WHERE salary > &sal_amt; 
11. O departamento de recursos humanos deseja executar relatórios baseados em um gerente. Crie uma consulta 
que solicite um ID de gerente ao usuário e gere o ID de funcionário, o sobrenome, o salário e o departamento dos 
funcionários desse gerente. O departamento de recursos humanos deseja ter permissão para classificar o relatório em 
uma coluna selecionada. Você pode testar os dados com os seguintes valores: (SÓ O SCRIPT) 
ID do gerente = 103, classificado pelo sobrenome do funcionário: 
ID do gerente = 201, classificado pelo salário: 
ID do gerente = 124, classificado pelo ID do funcionário: 
SELECT employee_id, last_name, salary, department_id 
FROM employees 
WHERE manager_id = &mgr_num 
ORDER BY &order_col; 
12. Exiba todos os sobrenomes dos funcionários cuja terceira letra do nome seja a. 
SELECT last_name 
FROM employees 
WHERE last_name LIKE '__a%'; 
13. Exiba o sobrenome de todos os funcionários que contenham a e e. 
SELECT last_name 
FROM employees 
WHERE last_name LIKE '%a%' 
AND last_name LIKE '%e%'; 
14. Exiba o sobrenome, o cargo e o salário de todos os funcionários cujo cargo seja representante de 
vendas(SA_REP) ou estoquista (ST_CLERK) e cujo salário seja diferente de US$ 2.500, US$ 3.500 ou US$ 7.000. 
SELECT last_name, job_id, salary 
FROM employees 
WHERE job_id IN ('SA_REP', 'ST_CLERK') 
AND salary NOT IN (2500, 3500, 7000); 
 15. Construa uma query para exibir o sobrenome, o salário e a comissão de todos os funcionários cuja comissão 
seja de 20%. 
SELECT last_name , salary , commission_pct 
FROM employees 
WHERE commission_pct = .20; 
 
AULA 03 (FUNÇÕES DE DATA, CARACTER E NÚMERO) 
1. O departamento de recursos humanos solicitou um relatório de todos os funcionários e os respectivos IDs de 
cargo. Exiba o sobrenome concatenado com o ID do cargo (separado por uma vírgula e um espaço) e nomeie a coluna 
como Employee and Title. 
SELECT CONCAT(last_name, ', ' , job_id) "Employee and Title" 
FROM employees; 
2. O departamento de recursos humanos precisa de um relatório para exibir o número do funcionário, o 
sobrenome, o salário e o salário com 15,5% de aumento (especificado como um número inteiro) de cada funcionário. 
Atribua o label New Salary à coluna. 
SELECT employee_id, last_name, salary, 
 ROUND(salary * 1.155, 0) "New Salary" 
 FROM employees; 
3. Modifique o exercício anterior para adicionar uma coluna que subtraia o salário antigo do novo salário. 
Atribua o label Increase à coluna. 
SELECT employee_id, last_name, salary, 
 ROUND(salary * 1.155, 0) "New Salary", 
 ROUND(salary * 1.155, 0) - salary "Increase" 
FROM employees; 
 
4. Crie uma consulta que exiba o sobrenome e o tamanho do sobrenome de todos os funcionários cujos nomes 
comecem com a letra J, A ou M. Atribua um label apropriado a cada coluna. Classifique os resultados pelos 
sobrenomes dos funcionários. 
 
SELECT last_name "Name", 
 LENGTH(last_name) "Length" 
FROM employees 
WHERE last_name LIKE 'J%' 
OR last_name LIKE 'M%' 
OR last_name LIKE 'A%' 
ORDER BY last_name ; 
5. Recrie a consulta anterior para que o usuário seja solicitado a informar a letra inicial do sobrenome. Por 
exemplo, se o usuário informar H quando uma letra for solicitada, a saída deverá mostrar todos os funcionários cujos 
sobrenomes começam com a letra H. 
SELECT last_name "Name", 
 LENGTH(last_name) "Length" 
FROM employees 
WHERE last_name LIKE '&start_letter%' 
ORDER BY last_name; 
6. O departamento de recursos humanos deseja saber qual é o tempo de emprego de cada funcionário. Para 
cada funcionário, exiba o sobrenome e calcule o número de meses entre hoje e a data de admissão do funcionário. 
Atribuao label MONTHS_WORKED à coluna. Ordene os resultados pelo número de meses em que o funcionário está 
empregado. Arredonde o número de meses para o número inteiro mais próximo. 
SELECT last_name, ROUND(datediff (curdate(), hire_date)/30,0) as MONTHS_WORKED 
FROM employees 
ORDER BY MONTHS_WORKED; 
7. Crie um relatório que produza estas informações somente para os funcionários com salários entre 2000 e 
4000: 
<sobrenome do funcionário> recebe <salário> mensalmente, mas deseja <3 vezes o salário>. 
Atribua o label Dream Salaries à coluna. 
SELECT CONCAT(last_name,’ recebe ‘,salary,’mensalmente, mas deseja ‘,salary*3,’.’) 
as ‘Dream Salaries’ 
FROM employees WHERE salary BETWEEN 2000 and 4000; 
8. Crie uma consulta que exiba o sobrenome e o salário de todos os funcionários. Formate o salário para defini-
lo com um tamanho de 15 caracteres e preenchê-lo à esquerda com o símbolo $. Atribua o label SALARY à coluna. 
SELECT last_name, LPAD(salary, 15, '$') SALARY FROM employees; 
9. Exiba o sobrenome, a data de admissão e a data do fim da experiência (90 dias após a contratação) de todos 
os funcionários cuja função seja REPRESENTANTE DE VENDAS (SA_REP). Atribua os labels data de contratacao e fim 
da experiência às respectivas colunas. Formate as datas para que sejam exibidas no formato "2000-july-23th”. 
SELECT last_name,date_format(hire_date,'%Y-%M-%D') as 'data de contratacao', 
date_format(hire_date+90, '%Y-%M-%D') as 'fim da experiencia' 
FROM employees WHERE job_id='SA_REP'; 
OBS: Função DATE_ADD : Soma corretamente um certo número de dias a uma data. 
DATE_ADD(<data>, INTERVAL <número> DAY); 
SELECT last_name,date_format(hire_date,'%Y-%M-%D') as 'data de contratacao', 
date_format(date_add(hire_date,interval 90 day),'%Y-%M-%D') as 'fim da experiencia' 
FROM employees WHERE job_id='SA_REP'; 
10. Crie uma consulta que exiba os sobrenomes e as comissões dos funcionários. Se um funcionário não ganhar 
comissão, a informação "No Commission" deverá ser exibida. Atribua o label COMM à coluna. 
SELECT last_name, COALESCE(commission_pct, 'No Commission') COMM FROM employees; 
11. Com a function CASE, crie uma consulta que exiba o nível de todos os funcionários com base no valor da 
coluna JOB_ID. Use estes dados: 
 Cargo Nível 
 AD_PRES A 
 ST_MAN B 
 IT_PROG C 
 SA_REP D 
 ST_CLERK E 
 Nenhuma das opções anteriores 0 
SELECT job_id, CASE job_id 
 WHEN 'ST_CLERK' THEN 'E' WHEN 'SA_REP' THEN 'D' 
 WHEN 'IT_PROG' THEN 'C' WHEN 'ST_MAN' THEN 'B' 
 WHEN 'AD_PRES' THEN 'A ELSE '0' END GRADE FROM employees; 
EXTRAS 
1. Para fins orçamentarios, o RH precisa de um relatório sobre os aumentos salariais projetados. O relatório deve 
exibir os funcionários que não ganham comissão, mas terão um aumento salarial de 10% (arredonde os salários 
para o formato monetário) 
FORMATO: 
O salário de <funcionário> depois de um aumento de 10% é <salário com aumento>. 
 
2. Crie um relatório contendo os funcionários , os salários e os respectivos tempos de emprega (em anos). Ordene 
o relatório pelo tempo de emprego dos funcionários. O funcionário empregado a mais tempo deve estar no 
início da lista. 
 
3. Exiba os funcionários cujos sobrenomes começam com a letra J,K ,L ou M (OBRIGATORIAMENTE UTILIZANDO A 
FUNÇÃO IN). Ordenar pelo sobrenome. 
 
4. Crie um relatório que exiba os seguintes dados dos funcionários: 
- Nome e Sobrenome do funcionário (concatenados e em caixa alta) 
- Cargo 
- Salário 
- Data de contratação (formato  24/September/2009) 
- Tempo de casa em anos (arredondado sem casas decimais) 
- Participação nos lucros da empresa em função do tempo de casa, mediante o seguinte critério: 
 Até 13 anos de casa: 20% do salário 
 Até 14 anos de casa: 25% do salário 
 Até 15 anos de casa: 30% do salário 
 Até 16 anos de casa: 40% do salário 
 Mais que 16 anos de casa: 50% do salário 
- Escalão do funcionário em função do cargo, mediante o seguinte critério: 
 AD_PRES,AD_VP - “ALTA GESTÃO” 
 AC_MGR,SA_MAN,ST_MAN,MK_MAN - “GESTÃO OPERACIONAL” 
 SA_REP,MK_REP - “NEGÓCIOS” 
 Outros cargos - “APOIO” 
Deverão constar no relatório os funcionários admitidos entre os anos de 1990 e 1997. 
O relatório deverá estar ordenado pelo tempo de casa descendente e pelo salário ascendente. 
RESPOSTAS 
1 – 
Select 
 concat('O salário de ',last_name,' depois de um aumento de 10% é ',round(salary*1.10,2)) as 'NOVO SALARIO' 
from employees 
where commission_pct is null; 
 
2 – 
Select 
last_name, salary, round(datediff(curdate(),hire_date)/365,0) as years 
from employees 
order by years desc ; 
3 – 
Select last_name from employees 
where 
lower(last_name) like 'j%' or 
lower(last_name) like 'k%' or 
lower(last_name) like 'l%' or 
lower(last_name) like 'm%' 
order by last_name; 
OU 
Select last_name from employees 
where 
substr(last_name,1,1) in ('J','K','L','M') 
order by last_name 
 
 
 
4- 
select 
upper (concat (first_name,' ',last_name)) as funcionario, 
job_id as cargo, 
salary as salario, 
date_format(hire_date,'%d/%M/%Y') as admissao, 
round(datediff(curdate(),hire_date)/365,0) as tempo_de_casa, 
case when round(datediff(curdate(),hire_date)/365,0) < 13 then salary*0.20 
 when round(datediff(curdate(),hire_date)/365,0) < 14 then salary*0.25 
 when round(datediff(curdate(),hire_date)/365,0) < 15 then salary*0.30 
 when round(datediff(curdate(),hire_date)/365,0) < 16 then salary*0.40 
 else salary*0.50 
 end 
as Gratificacao, 
case when lower(job_id) in('ad_pres','ad_vp') then 'ALTA GESTAO' 
 when lower(job_id) in('ac_mgr','sa_man','st_man','mk_man') then 'GESTAO OPERACIONAL' 
 when lower(job_id) in('sa_rep','mk_rep') then 'NEGOCIOS' 
 else 'APOIO' 
 end 
as Escalao 
FROM employees 
where 
year(hire_date) between 1990 and 1997 
order by 
tempo_de_casa desc, salary ;