Exercicio SQL 1

Fundamento de Banco de Dados
Bacharelado em Sistemas de Informação
Prof. Josino Rodrigues
Lista de Exercícios – SQL
1) Criar as seguintes tabelas de acordo com a estrutura abaixo:
 EMPR
(Tabela Empregados)
 DEPT
(Tabela Departamentos)
 PROJETO (Tabela Projetos)
Seguem as descrições das tabelas:
EMPR, Tabela Empregados
Coluna
MATR
NOME
SOBRENOME
DEPT
FONE
DINADIM
CARGO
NIVELED
SEXO
DATANAS
SALARIO
BONUS
COMIS
Tipo
CHAR
VARCHAR
VARCHAR
CHA R
CHAR
DATE
CHAR
NUMBER
CHAR
DATE
NUMBER
NUMBER
NUMBER
Tam
6
12
15
3
14
Nulo
não
não
não
10
1
9,2
9,2
9,2
Descrição
Matricula única do empregado
Primeiro nome
Sobrenome
Código de departamento do empregado
Numero de telefone
Data de admissão
Cargo do empregado
Educação formal em anos
M = masc F=fem.
Data de nascimento
Salário Anual
Bônus Anual
Comissão Anual
DEPT, Tabela Departamentos
Coluna
DCODIGO
DNOME
GERENTE
DSUPER
Tipo
CHAR
VARCHAR
CHAR
CHAR
Tam
3
36
6
3
Nulo Descrição
não
Código único do departamento
não
Nome do departamento
Matrícula do Gerente
Depto a qual está subordinado
PROJETO, Tabela Projetos
Coluna
PCODIGO
PNOME
Tipo
CHAR
VARCHAR
Tam
6
24
Nulo Descrição
não
Código único do Projeto
não
Nome do projeto
DCODIGO
RESP
EQUIPE
DATAINI
DATAFIM
PSUPER
CHAR
CHAR
NUMBER
DATE
DATE
CHAR
3
6
5
não
não
6
Código do Departamento
Matrícula do Responsável
Número de Empregados no Projeto
Data de início
Data do final
Projeto Principal (se subprojeto)
LAB SQL SELECT BÁSICO
Objetivo:
•
Responder questões extraindo dados de tabelas usando instruções SQL SELECT.
Primeiramente devemos inserir alguns dados na tabelas em questão, posteriormente, codifique instruções
SELECT para resolver os problemas.
PROBLEMAS DE SQL
1.
Mostre o sobrenome, primeiro nome, departamentos data de nascimento, data de admissão e salário de
todos os empregados que ganham mais que $30.000 por ano.
Resultado Esperado!
Sobrenome
HAAS
THOMPSON
KWAN
GEYER
STERN
PULASKI
HENDERSON
SPENSER
LUCCHESI
NICHOLLS
ADAMSON
WALKER
BROWN
SCHNEIDER
GOUNOT
Nome
CHRISTINE
MICHAEL
SALLY
JOHN
IRVING
EVA
EILEEN
THEODORE
VINCENZO
HEATHER
BRUCE
JAMES
DAVID
ETHEL
JASON
Dept
A00
B01
C01
E01
D11
D21
E11
E21
A00
C01
D11
D11
D11
E11
E21
Datanas
14/08/33
02/02/48
11/05/41
15/09/25
07/07/45
26/05/53
14/01/46
25/11/61
05/11/29
18/11/47
01/04/22
17/10/51
18/07/23
27/10/48
01/12/59
Dinadim
01/01/65
10/10/73
05/04/75
17/08/49
14/09/73
30/09/80
01/06/73
12/04/89
16/05/58
05/04/75
17/08/49
04/03/79
03/12/50
14/03/76
18/04/87
Salario
52750,00
41250,00
38250,00
40175,00
32250,00
36170,00
40300,00
39000,00
46500,00
35040,00
38780,00
38990,00
40505,00
30400,00
40330,00
2.
Liste todas as informações de qualquer departamento cujo gerente seja desconhecido.
Resultado Esperado!
Dcodigo
D01
3.
Dnome
CENTRO DE DESENVOLVIMENTO
Gerente
Dsuper
D2
Mostre o sobrenome, primeiro nome, departamento, data de nascimento, data de admissão e salário de
todos os empregados que ganhem menos de $20.000 por ano. Classifique o resultado pelo sobrenome e
primeiro nome.
Resultado Esperado!
Sobrenome
JEFFERSON
JOHNSON
JONES
LEE
LUTZ
MEHTA
PARKER
PEREZ
SCOUTTEN
SETRIGHT
SMITH
SMITH
4.
Nome
JAMES
SYBIL
WILLIAM
WING
JENIFER
RAMLAL
JOHN
MARIA
MARYLIN
MAUDE
DANIEL
PHILIP
Dept
D21
D21
D11
E21
D11
E21
E11
D21
D11
E11
D21
E11
Datanas
24/06/60
05/10/36
23/02/53
02/09/47
25/07/49
11/08/32
09/07/46
24/03/49
17/10/48
21/04/31
12/11/39
27/10/36
Dinadim
10/11/87
11/09/75
11/04/79
18/01/75
10/12/76
07/07/65
30/05/80
09/08/76
04/03/76
12/09/64
30/10/69
19/06/72
Salario
19333,00
17250,00
18270,00
10304,00
19405,00
19950,00
15340,00
13403,00
19330,00
15900,00
19180,00
17750,00
Liste tudo sobre os departamentos subordinados ao departamento A00.
Resultado Esperado!
Dcodigo
A00
B01
C01
D01
E01
5.
Dnome
DIV. SERVIÇOS DE COMPUTADOR
PLANEJAMENTO
CENTRO DE INFORMAÇÕES
CENTRO DE DESENVOLVIMENTO
SERVIÇOS DE SUPORTE
Gerente
000010
000020
000030
000050
Dsuper
A00
A00
A00
A00
A00
Liste o código e o nome dos departamento que apresentam “SERVIÇO” compondo seu nome.
Resultado Esperado!
Dcodigo
A00
Dnome
DIV. SERVIÇOS DE COMPUTADOR
E01
6.
SERVIÇOS DE SUPORTE
Mostre a matricula, sobrenome, departamento e telefone dos empregados cujo código de departamento
estejam compreendidos entre D11 e D21 (inclusive).
Resultado Esperado!
Matr
000060
000070
000150
000160
000170
000180
000190
000200
000210
000220
000230
000240
000250
000260
000270
7.
Sobrenome
STERN
PULASKI
ADAMSON
PIANKA
YOSHIMURA
SCOUTTEN
WALKER
BROWN
JONES
LUTZ
JEFFERSON
MARINO
SMITH
JOHNSON
PEREZ
Dept
D11
D21
D11
D11
D11
D11
D11
D11
D11
D11
D21
D21
D21
D21
D21
Fone
6423
7831
4510
3782
2890
1682
2986
4501
0942
672
2094
3780
0961
8953
9001
Produza uma lista dos empregados nos departamentos B0l, C0l e D01, mostrando o sobrenome,
departamento e rendimento (salário + comissão) Liste a saída em ordem descendente de rendimento dentro
de cada departamento.
Resultado Esperado!
Sobrenome
THOMPSON
KWAN
NICHOLLS
QUINTANA
8.
Dept
B01
C01
C01
C01
Salario+comis
45375
42075
38544
25630
Mostre o sobrenome, salário anual e departamento dos empregados com salário mensal maior que
$3000. Classifique a lista pelo sobrenome.
Resultado Esperado!
Sobrenome
Salario Dept
ADAMSON
BROWN
GEYER
GOUNOT
HAAS
HENDERSON
KWAN
LUCCHESI
PULASKI
SPENSER
THOMPSON
WALKER
38780,00 D11
40505,00 D11
40175,00 E01
40330,00 E21
52750,00 A00
40300,00 E11
38250,00 C01
46500,00 A00
36170,00 D21
39000,00 E21
41250,00 B01
38990,00 D11
EXERCÍCIOS OPCIONAIS
9.
Produza uma lista de todos os empregados cujo departamento somente com “E”. Mostre a matricula,
primeiro nome e sobrenome. Classifique pelo sobrenome.
Resultado Esperado!
Matr
000050
000340
000090
000330
000320
000290
000280
000310
000300
000100
Nome
JOHN
JASON
EILEEN
WING
RAMLAL
JOHN
ETHEL
MAUDE
PHILIP
THEODORE
Sobrenome
GEYER
GOUNOT
HENDERSON
LEE
MEHTA
PARKER
SCHNEIDER
SETRIGHT
SMITH
SPENSER
10. Produza uma lista dos homens cujo salário mensal seja menor do que $1600. Mostre a matricula,
sobrenome e salário mensal. Classifique cm ordem descendente de salário.
Resultado Esperado!
Matr
000250
000210
000300
000260
000290
Sobrenome
SMITH
JONES
SMITH
JOHNSON
PARKER
Salario/12
1598,33333333333
1522,5
1479,16666666667
1437,5
1278,33333333333
000330
LEE
858,666666666667
11. Para cada representante de vendas (REPVENDA), apresente a comissão em porcentagem do total de
rendimento (salário, bônus e comissão). Liste nome e porcentagem.
Resultado Esperado!
Sobrenome
LUCCHESI
QUINTANA
(100*comis)/(salario+comis+bon
8,92857142857143
8,92857142857143
12. Mostre todas as informações referentes aos departamentos “E01” e departamentos subordinados ao
departamento “E01”.
Resultado Esperado!
Dcodigo
E01
E11
E21
Dnome
SERVIÇOS DE SUPORTE
OPERAÇÃO
SUPORTE SOFTWARE
Gerente
000050
000090
000100
Dsuper
A00
E01
E01
13. Liste o sobrenome, salário, função e nível de educação de qualquer empregado que se enquadre numa
das seguintes condições:
•
Salário maior que $40.000;
•
Função gerente com nível menor que 16.
Resultado Esperado!
Sobrenome
HAAS
THOMPSON
KWAN
GEYER
STERN
PULASKI
HENDERSON
SPENSER
LUCCHESI
BROWN
GOUNOT
Salario Cargo
52750,00 GERENTE
41250,00 GERENTE
38250,00 GERENTE
40175,00 GERENTE
32250,00 GERENTE
36170,00 GERENTE
40300,00 GERENTE
39000,00 GERENTE
46500,00 REPVENDA
40505,00 PRJTISTA
40330,00 REPCAMPO
Niveled
19
18
15
16
14
14
17
15
18
17
17
LAB SQL SELECT AVANÇADO
Objetivos:
•
Codificar e executar várias instruções SQL SELECT usando column functions e GROUP BY.
•
Obter dados de mais de uma tabela (UNION e JOIN).
Codifique instruções SQL para resolver os problemas apresentados.
PROBLEMAS SOL
1.
Mostre a soma dos salários de toda a companhia, salário médio, salário mínimo e o salário máximo.
Resultado Esperado!
Sum(salario)
892122
2.
Avg(salario)
28778,1290322581
Min(salario)
10304
Max(salario)
52750
Qual é o primeiro sobrenome em ordem alfabética?
Resultado Esperado!
Min(sobrenome)
ADAMSON
3.
Quantos departamentos tem empregados?
Resultado Esperado!
Count(distinctdept)
8
4.
Mostre o salário médio para cada função.
Resultado Esperado!
Cargo
ANALISTA
ATENDTE
GERENTE
PRJTISTA
REPCAMPO
Avg(salario)
37603,3333333333
19640
40018,125
24820,5
20592,8
REPVENDA
5.
34900
Liste a média salarial das funções onde o salário médio seja maior que $35.000.
Resultado Esperado!
Cargo
ANALISTA
GERENTE
6.
Avg(salario)
37603,3333333333
40018,125
Mostre o sobrenome e função dos empregados dos departamentos cujo nome inclua “PLAN”.
Resultado Esperado!
Sobrenome
THOMPSON
7.
Cargo
GERENTE
Mostre o sobrenome e primeiro nome de todos os empregados que trabalham no mesmo departamento
do Adamson.
Resultado Esperado!
Sobrenome
STERN
YOSHIMURA
WALKER
LUTZ
JONES
BROWN
SCOUTTEN
PIANKA
ADAMSON
Nome
IRVING
MASATOSHI
JAMES
JENIFER
WILLIAM
DAVID
MARYLIN
ELIZABETH
BRUCE
EXERCÍCIOS OPCIONAIS
8.
Produza uma lista mostrando departamento média salarial e quantidade de empregados para cada
departamento excluindo a função “ATENDTE”.
Exclua departamentos com menos de quatro empregados
Classifique a lista em ordem descendente de quantidade de empregados.
Resultado Esperado!
Dept
D11
D21
E21
9.
Avg(salario)
31543,3333333333
22983,25
27396
Count(*)
6
4
4
Liste o departamento e sobrenome dos gerentes dos departamentos subordinados ao departamento D01.
Resultado Esperado!
Dcodigo
D11
D21
10.
Sobrenome
STERN
PULASKI
Mostre a média salarial dos homens e a média salarial das mulheres de cada departamento. Identifique
os departamentos pelo código e pelo nome. Classifique o resultado em ordem descendente de salário
dentro de cada departamento.
Resultado Esperado!
Dcodigo
A00
A00
B01
C01
C01
D11
D11
D21
D21
E01
E11
E11
E21
Dnome
DIV. SERVIÇOS DE COMPUTADOR
DIV. SERVIÇOS DE COMPUTADOR
PLANEJAMENTO
CENTRO DE INFORMAÇÕES
CENTRO DE INFORMAÇÕES
SISTEMAS MANUFATURA
SISTEMAS MANUFATURA
SISTEMA ADMINISTRAÇÃO
SISTEMA ADMINISTRAÇÃO
SERVIÇOS DE SUPORTE
OPERAÇÃO
OPERAÇÃO
SUPORTE SOFTWARE
Avg(salario) Sexo
52750 F
46500 M
41250 M
35040 M
30775 F
33033 M
20373 F
24786,5 F
19173,25 M
40175 M
28866,6666666667 F
16545 M
27396 M