Exercícios+LAB+SQL+BASICO+va

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. 
2. Você precisa determinar a estrutura da tabela 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. 
4. O departamento de recursos humanos precisa de uma consulta para exibir todos os códigos de cargo exclusivos da 
tabela 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. 
2. Crie um relatório que exiba o sobrenome e o número do departamento do funcionário 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. 
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. 
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. 
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. 
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. 
8. Crie um relatório que exiba o sobrenome e o cargo de todos os funcionários não subordinados a um gerente. 
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. 
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) 
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: 
12. Exiba todos os sobrenomes dos funcionários cuja terceira letra do nome seja a. 
13. Exiba o sobrenome de todos os funcionários que contenham a e 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. 
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%. 
 
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. 
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. 
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. 
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. 
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. 
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. Atribua o 
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. 
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. 
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. 
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 semelhante a "2000-july-23th”. 
OBS: Função DATE_ADD : Soma corretamente um certo número de dias a uma data. 
DATE_ADD(<data>, INTERVAL <número> DAY); 
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. 
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 
 
 
 
AULA 04 (FUNÇÕES DE AGRUPAMENTO) 
O departamento de RH necessita dos seguintes relatórios: 
1. Obtenha o salário máximo, o salário mínimo, a soma dos salários e o salário médio de todos os funcionários. 
Atribua os labels Maximum, Minimum, Sum e Average, respectivamente, às colunas. Arredonde os resultados para o 
número inteiro mais próximo. 
2. Modifique a consulta anterior para exibir o salário mínimo, o salário máximo, a soma dos salários e o salário médio 
de cada tipo de cargo. 
3. Crie uma consulta para exibir o número de pessoas com o mesmo cargo. Generalize a consulta para que o usuário 
do departamento de RH seja solicitado a informar um cargo. 
4. Determine o número de gerentes sem listá-los. Atribua o label Number of Managers à coluna. Dica: Use a coluna 
MANAGER_ID para determinar o número de gerentes. 
5. Descubra a diferença entre o salário mais alto e o mais baixo. Atribua o label DIFFERENCE à coluna. 
6. Crie um relatório para exibir o número do gerente e o salário do funcionário com menor remuneraçãodesse 
gerente. Exclua todas as pessoas cujo gerente seja desconhecido. Exclua todos os grupos em que o salário mínimo seja US$ 
6.000 ou inferior. Classifique a saída em ordem decrescente de salário. 
7. Crie um relatório para exibir a quantidade de funcionários admitidos e a soma dos salários dos mesmos para cada 
ano de contratação da empresa. Atribua os labels ANO, TOTAL DE ADMITIDOS e SOMA DOS SALARIOS para as colunas 
correspondentes. 
8. Modifique a consulta anterior agora para buscar somente a quantidade de admitidos superior a 10 empregados. 
9. Crie um relatório para exibir a média e o desvio padrão salarial (arredondados com duas casas decimais) para cada 
tipo de cargo. 
10. Modifique a consulta anterior agora para buscar somente os registros com desvio padrão diferente de zero e 
ordenação decrescente pela média salarial. 
11. Crie um relatório que gere a seguinte tabela: 
Codigo do Gerente(ID) Menor % de comissao Maior % de comissao 
OBS 1: Os percentuais de comissão são referentes aos funcionários chefiados pelos respectivos gerentes. 
OBS 2: Os dados de funcionários não chefiados por um gerente não devem aparecer no relatório 
OBS 3: Os valores nulos de comissão devem ser substituídos por 0. 
12. Modifique o relatório anterior, para que o mesmo não exiba os gerentes cujos funcionários não tenham 
comissionamento (isto é, % de comissão = 0,00%). 
 
AULA 05 (JUNÇÕES DE TABELAS) 
1. Crie uma consulta para o departamento de recursos humanos a fim de gerar os endereços de todos os 
departamentos. Use as tabelas LOCATIONS e COUNTRIES. Mostre o ID do local, o endereço, a cidade, o estado e o país na 
saída (USAR O JOIN “CLÁSSICO” E O INNER JOIN). 
2. O departamento de recursos humanos precisa de um relatório de todos os funcionários. Crie uma consulta para 
exibir o sobrenome, o número do departamento e o nome do departamento de todos os funcionários (USAR O JOIN 
“CLÁSSICO” E O INNER JOIN). 
3. O departamento de recursos humanos precisa de um relatório dos funcionários em Toronto. Exiba o sobrenome, o 
cargo, o número do departamento e o nome do departamento de todos os funcionários que trabalham em Toronto. 
4. O departamento de recursos humanos precisa de um relatório sobre níveis de cargos e salários. Para se familiarizar 
com a tabela JOB_GRADES, primeiro mostre a estrutura dessa tabela. Em seguida, crie uma consulta que exiba o 
sobrenome, o cargo, o nome do departamento, o salário e o nível de todos os funcionários. 
5. O departamento de recursos humanos precisa de um relatório sobre os funcionários e os seus respectivos 
departamentos. Crie uma consuta exiba o nome e sobrenome concatenados, o salário e o nome do departamento, 
somente para os funcionários com salário entre 2000 e 4000. 
6. Modifique o relatório anterior para todos os funcionários da empresa (independente do salário). Crie uma consulta 
para verificar se existe algum funcionário não alocado em nenhum departamento. 
7. Modifique o relatório anterior para todos os funcionários da empresa (independente do salário). Crie uma consulta 
para verificar se existe algum departamento sem nenhum funcionário (não ocupado). 
 
AULA 06 (SUBCONSULTAS) 
1. O departamento de recursos humanos precisa de uma consulta que solicite ao usuário o sobrenome de um 
funcionário. A consulta exibe o sobrenome e a data de admissão de todos os funcionários no mesmo departamento do 
funcionário cujo nome foi fornecido (excluindo esse funcionário). Por exemplo, se o usuário informar Zlotkey, serão 
exibidos todos os funcionários que trabalham com Zlotkey (excluindo ele próprio). 
2. Crie um relatório que exiba o número e o sobrenome de todos os funcionários cujo salário é maior que o salário 
médio. Classifique os resultados em ordem crescente de salário. 
 
3. Crie uma consulta que exiba o número e o sobrenome de todos os funcionários que trabalham em um 
departamento com funcionários cujos sobrenomes contêm a letra u. 
4. O departamento de recursos humanos precisa de um relatório que exiba o sobrenome, o número do departamento 
e o ID do cargo de todos os funcionários cujo ID de local do departamento é 1700. 
5. Modifique a consulta para que um ID de local seja solicitado ao usuário. 
6. Crie um relatório para o departamento de recursos humanos que exiba o sobrenome e o salário de todos os 
funcionários subordinados a King. 
7. Crie um relatório para o departamento de recursos humanos que exiba o número do departamento, o sobrenome 
e o ID do cargo de todos os funcionários no departamento executivo. 
8. Modifique a consulta do exercício 3 para exibir o número, o sobrenome, bem como o salário de todos os 
funcionários que ganham mais que o salário médio e trabalham em um departamento com funcionários cujos sobrenomes 
contêm a letra u. 
 
AULA 07 (DML e TRANSAÇÕES) 
O departamento de recursos humanos deseja criar instruções SQL para inserir, atualizar e deletar dados de 
funcionários. Como protótipo, use a tabela MY_EMPLOYEE antes de fornecer as instruções ao departamento de 
recursos humanos. 
OBS: Não se esqueça de desativar o autocommit do MySQL 
 Mysql>SET AUTOCOMMIT = 0; 
1. Construir a tabela MY_EMPLOYEE a partir da tabela EMPLOYEES. A tabela deverá ter: 
- CAMPOS: Employee_id, first_name, last_name, hire_date, salary 
- TODOS OS 107 REGISTROS DA TABELA EMPLOYEES 
2. Descreva a estrutura da tabela MY_EMPLOYEE para identificar os nomes de colunas. 
3. Confirme a existência dos 107 registros da tabela MY_EMPLOYEE. 
Atualize e delete dados na tabela MY_EMPLOYEE. 
4. Altere o sobrenome do funcionário 179 para Drexler. 
5. Altere o salário de todos os funcionários com salário inferior a 3000 para 3500. 
6. Verifique as alterações na tabela. 
7. Delete a funcionária Alana Walsh da tabela MY_EMPLOYEE. 
8. Confirme todas as alterações pendentes na tabela MY_EMPLOYEE. 
Controle a transação de dados na tabela MY_EMPLOYEE. 
9. Esvazie a tabela inteira. 
10. Confirme se a tabela está vazia. 
11. Descarte a alteração de esvaziar toda a tabela. 
12. Confirme se os dados da tabela não foram apagados. 
13. Inserir o seguinte funcionário na tabela MY_EMPLOYEES 
207 Zina Ronaldo 23/08/2009 5200 
14. Torne a adição de dados permanente. 
 
AULA 08 (DDL) 
1. Crie a tabela DEPT com base no quadro de instâncias de tabela a seguir. Verifique se a tabela foi criada 
(estrutura). 
Nome da Coluna DEPT_ID DEPT_NAME FUND_DATE CATEGORY COST 
Tipo de Chave PK 
Valores 
Nulos/Exclusivos 
NN,U NN NN OPER,EXEC, 
ESTRAT 
>1000 
Tipos de Dados INTEGER VARCHAR(20) DATE VARCHAR(20) FLOAT(8,2) 
NN = NOT NULL 
U = UNIQUE 
2. Inserir os 5 registros abaixo na tabela DEPT (confirmar a inclusão) 
dept_Id dept_name fund_data category Cost 
32 COMPRAS 12-05-1998 OPER 10000 
33 TI 25-05-1998 ESTRAT 20000 
34 VENDAS 20-05-1998 OPER 50000 
35 DIRETORIA 10-05-1998 EXEC 50000 
36 RH 20-06-1998 OPER 35000 
3. Inserir na tabela DEPT o campo TIPO (do tipo Varchar(3)). Em seguida, preencher este campo para os 5 
registros de acordo com o seguinte critério: 
 Se o custo for menor igual a R$20.000,00 o tipo é B, caso contrário o tipo é A. 
4. Alterar o campo DEPT_NAME para o tipo VARCHAR(35). 
5. Excluir o campo FUND_DATA da tabela DEPT. 
6. Excluir todos os dados da tabela DEPT (Sem excluir a estrutura da tabela). 
7. Excluir a tabela DEPT do banco de dados. 
 
AULA 09 (VISÕES e ÍNDICES) 
1. A equipe do departamento de recursos humanos deseja ocultar alguns dados da tabela EMPLOYEES. Ela 
deseja uma view denominada EMPLOYEES_VU com base nos números e nomes de funcionário, bem como nos 
números de departamento da tabela EMPLOYEES. Atribua EMPLOYEE como cabeçalho do nome do funcionário. 
2. Verifique se a viewfunciona. Exiba o conteúdo da view EMPLOYEES_VU. 
3. Usando a view EMPLOYEES_VU, crie uma consulta para o departamento de recursos humanos a fim de exibir 
todos os nomes de funcionário e números de departamento. 
4. Usando a view EMPLOYEES_VU, crie uma consulta para o departamento de recursos humanos a fim de exibir 
todos os números e nomes dos funcionários com o código de departamento igual a 60. 
5. Usando a view EMPLOYEES_VU, crie uma consulta para o departamento de recursos humanos a fim de exibir 
os nomes dos funcionários e números de apartamento, cujos nomes dos funcionários tenham como 3º caractera a 
letra E e o número do departamento seja igual a 80. 
6. Crie uma view chamada FICHA que mostre os seguintes dados dos funcionários: 
Nome 
Sobrenome 
Data de Contratação 
Salário 
Nome do Departamento 
Cidade onde o Departamento esta Localizado 
- Ordenar pelo Salário do funcionário (do maior para o menor). 
OBS: Em seguida, verifique o conteúdo da VIEW e apague a mesma. 
7. Crie um índice não exclusivo na coluna manager_id da tabela DEPARTMENTS (O nome deverá ser IDXIDGER). 
8. Elimine o índice criado no exercício 7. 
AULA 10 (Gerenciamento de Usuários) 
1. Crie 3 novos usuários para o SGBD e defina os seus privilégios, de acordo com os seguintes critérios 
NOME Usuario1 Usuario2 Usuario3 
PRIVILÉGIOS Todos Apenas atualizar,inserir,selecionar e 
deletar registros das tabelas 
Criação, alteração e 
deleção de tabelas. 
NÍVEL Todo o banco de dados RH Apenas a tabela EMPLOYEES do BD RH Todos os bancos de dados 
SENHA 87534 abc345 etd598 
2. Entre no MySQL (PROMPT DE COMANDO), com cada um dos usuários e teste os privilégios 
TESTE: Usuário 1 
a) Visualizar e entrar no banco de dados mysql 
b) Entrar no banco de dados RH 
c) Selecionar todos os departamentos com localização em Seattle (código e nome do departamento e a cidade 
em que o mesmo se localiza). 
TESTE: Usuário 2 
a) Mostrar todo o conteúdo da tabela departments do BD RH. 
b) Selecionar todos os empregados (sobrenome,salário e data de contratação) com a data de contratação entre 
01/01/1993 a 01/08/1994, ordenado pela data de contratação em ordem decrescente e salário. 
TESTE: Usuário 3 
a) Mostrar todo o conteúdo da tabela locations 
b) Entrar no BD mysql e tentar ver todos os usuários cadastrados. 
3. Volte a se conectar como ROOT (Administrador) e efetue as seguintes ações: 
a) Verifique os privilégios do usuário2 
b) Retire do usuário 2 o direito de deletar os registros das tabelas. 
c) Verifique os privilégios do usuário3 
d) Redefina os privilégios do usuário3, permitindo que o mesmo possa usar a clausula SELECT e use somente o 
BD RH. 
 e) Mude o nome do usuário3 para user3. 
f) Altere a senha do user3 para def123 
g) Exclua o usuário1 do SGBD. 
h) Acesse a tabela que mostra todos os usuários cadastrados no SGBD (mostrando o nome do usuário e a senha).