CSI440 Aula 14 - SQL

Prévia do material em texto

CSI440 – Banco de 
Dados I
Bruno Rabello Monteiro
Aula 14
UFOP  ICEA  DECSI
Conteúdo
• Introdução
• Consultas 
• Atributos Ambíguos
• Cláusula e 
• Cláusula 
• Operador 
• Operações da Teoria de Conjuntos
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 3
UFOP  ICEA  DECSI
Introdução
• (Structured Query Language)
• Padrão para bancos de dados relacionais
• 1° versão: 1986 ( )
• Versão mais recente 
• Suportada pelos principais SGBDs comerciais
• Linguagem declarativa com base na álgebra 
relacional
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 4
UFOP  ICEA  DECSI
Introdução
• compreende
• Definição de dados –
• Manipulação/consulta aos dados –
• Geração de visões –
• Regras para uso com linguagens de 
programação ( , ou )
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 5
UFOP  ICEA  DECSI
: Consultas
• Forma básica do comando 
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 6
<lista atributos>
<lista tabelas>
<condições>;
UFOP  ICEA  DECSI
: Consultas
• é ortogonal
• Resultado de uma consulta é uma tabela
• Consultas podem apresentar linhas duplicadas
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 7
UFOP  ICEA  DECSI
Banco de Dados EMPRESA
(ELMASRI & NAVATHE, 2011)
UFOP  ICEA  DECSI
: Consultas
• : Recuperar a data de
nascimento e o endereço dos funcionários
chamados João B. Silva
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 9
UFOP  ICEA  DECSI
: Consultas
• : Recuperar a data de
nascimento e o endereço dos funcionários
chamados João B. Silva
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 10
Datanasc, Endereco
FUNCIONARIO
Pnome = ‘João’ Minicial = ‘B’
Unome = ‘Silva’;
UFOP  ICEA  DECSI
: Consultas
• : Recuperar nome e endereço
dos funcionários que trabalham no
departamento ‘Pesquisa’
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 11
UFOP  ICEA  DECSI
: Consultas
• : Recuperar nome e endereço
dos funcionários que trabalham no
departamento ‘Pesquisa’
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 12
Pnome, Minicial, Unome, Endereco
FUNCIONARIO, DEPARTAMENTO
Dnome = ‘Pesquisa’ 
Dnr = Dnumero;
UFOP  ICEA  DECSI
: Consultas
• : Liste o número do projeto, o
número do departamento que o controla, o
sobrenome e endereço do gerente desse
departamento. Apenas para projetos
localizados em ‘Mauá’
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 13
UFOP  ICEA  DECSI
: Consultas
• : Liste o número do projeto, o
número do departamento que o controla, o
sobrenome e endereço do gerente desse
departamento. Apenas para projetos
localizados em ‘Mauá’
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 14
Projnumero, Dnr, Unome, Endereco
PROJETO, DEPARTAMENTO, FUNCIONARIO
Projlocal = ‘Mauá’ Dnum = Dnumero
Cpf_gerente = Cpf;
UFOP  ICEA  DECSI
: Consultas
• : Recuperar o número e nome
dos departamentos localizados em ‘São
Paulo’
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 15
UFOP  ICEA  DECSI
: Consultas
• : Recuperar o número e nome
dos departamentos localizados em ‘São
Paulo’
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 16
Dnumero, Dnome
DEPARTAMENTO, LOCALIZACAO_DEP
Dnumero = Dnumero
Dlocal = ‘São Paulo’;
UFOP  ICEA  DECSI
: Consultas
• : Recuperar o número e nome
dos departamentos localizados em ‘São
Paulo’
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 17
Problema!
Problema!
Dnumero, Dnome
DEPARTAMENTO, LOCALIZACAO_DEP
Dnumero = Dnumero
Dlocal = ‘São Paulo’;
UFOP  ICEA  DECSI
: Atributos Ambíguos
• Em tabelas diferentes podem existir
atributos de mesmo nome
• Algumas consultas envolvem a mesma tabela
mais de uma vez
• Necessário qualificar os atributos
• Usar o próprio nome da tabela
• Criar uma variável de tabela
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 18
UFOP  ICEA  DECSI
: Consultas
• : Recuperar o número e nome
dos departamentos localizados em ‘São
Paulo’
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 19
D.Dnumero, D.Dnome
DEPARTAMENTO D, LOCALIZACAO_DEP L
D.Dnumero = L.Dnumero
L.Dlocal = ‘São Paulo’;
UFOP  ICEA  DECSI
: Consultas
• : Para cada funcionário,
recupere o nome e sobrenome mais o nome
e sobrenome do seu supervisor
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 20
UFOP  ICEA  DECSI
: Consultas
• : Para cada funcionário,
recupere o nome e sobrenome mais o nome
e sobrenome do seu supervisor
• ou
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 21
F.Pnome, F.Unome, S.Pnome, S.Unome
FUNCIONARIO F, FUNCIONARIO S
F.supervisor = S.Cpf;
F.Pnome, F.Unome, S.Pnome, S.Unome
FUNCIONARIO F, FUNCIONARIO S
F.supervisor = S.Cpf;
UFOP  ICEA  DECSI
: Consultas
• É possível renomear os atributos dentro de 
uma consulta
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 22
Pn, Mn, Un
FUNCIONARIO F(Pn, Mn, Un, Cpf, Dn, Ende, Sex, Sal, Sup, Dnr)
Sal > 10000;
UFOP  ICEA  DECSI
: Consultas
• : Selecione os números de CPF 
dos funcionários
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 23
UFOP  ICEA  DECSI
: Consultas
• : Selecione os números de CPF 
dos funcionários
• Cláusula não é obrigatória
• Todas as tuplas da(s) tabela(s) especificada(s) 
serão retornadas
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 24
Cpf
FUNCIONARIO;
UFOP  ICEA  DECSI
: Consultas
• : Recupere os funcionários 
lotados no departamento 1
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 25
UFOP  ICEA  DECSI
: Consultas
• : Recupere os funcionários 
lotados no departamento 1
• Asterisco ( ) é usado para retornar todos os 
atributos das tabelas da cláusula 
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 26
*
FUNCIONARIO
Dnr = 1;
UFOP  ICEA  DECSI
: Consultas
• : Recupere os funcionários 
lotados no departamento ‘Pesquisa’
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 27
UFOP  ICEA  DECSI
: Consultas
• : Recupere os funcionários 
lotados no departamento ‘Pesquisa’
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 28
F.*
FUNCIONARIO F, DEPARTAMENTO D
D.Dnome = ‘Pesquisa’ 
F.Dnr = D.Dnumero;
UFOP  ICEA  DECSI
: Consultas
• : Recupere os salários pagos 
para cada funcionário
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 29
UFOP  ICEA  DECSI
: Consultas
• : Recupere os salários pagos 
para cada funcionário
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 30
Salario
FUNCIONARIO;
UFOP  ICEA  DECSI
: Consultas
• : Recupere os salários pagos 
para cada funcionário
• A cláusula elimina as tuplas
duplicadas do resultado
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 31
Salario
FUNCIONARIO;
UFOP  ICEA  DECSI
: Consultas
• A é um modelo não-ordenado
• O resultado pode ser mostrado em qualquer 
ordem!
• Cláusula ordena as tuplas do 
resultado
• Ascendente ( ) ou Decrescente ( )
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 32
UFOP  ICEA  DECSI
: Consultas
• : Recupere o nome dos
funcionários, o nome do projeto em que
trabalham e o nome do departamento que
controla o projeto, ordenados pelo nome do
departamento e em seguida pelo sobrenome
do funcionário
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 33
UFOP  ICEA  DECSI
: Consultas
• : Recupere o nome dos
funcionários, o nome do projeto em que
trabalham e o nome do departamento que
controla o projeto, ordenados pelo nome do
departamento e em seguida pelo sobrenome
do funcionário
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 34
D.Dnome, F.Unome, P.Unome, P.Projnome
FUNCIONARIO F, DEPARTAMENTO D,
PROJETO P, TRABALHA_EM T
D.Dnumero = F.Dnr AND F.Cpf = T.Fcpf
AND T.Pnr = P.Projnumero
D.Dnome , F.Unome ;
UFOP  ICEA  DECSI
: Consultas
• Operador é usado para comparar 
cadeias de caracteres
• Faz uso de dois caracteres
• substitui um número qualquer de zero ou mais
caracteres
• substitui um único caractere
• Operador de concatenação pode ser usado
para anexar dois valores de cadeia
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 35
UFOP  ICEA  DECSI
: Consultas
• : Recuperar os funcionáriosque
moram na cidade São Paulo
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 36
UFOP  ICEA  DECSI
: Consultas
• : Recuperar os funcionários que
moram na cidade São Paulo
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 37
Pnome, Unome
FUNCIONARIO
Endereco ‘%São Paulo%’;
UFOP  ICEA  DECSI
: Consultas
• : Recuperar os funcionários que
nasceram na década de 60
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 38
UFOP  ICEA  DECSI
: Consultas
• : Recuperar os funcionários que
nasceram na década de 60
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 39
Pnome, Unome
FUNCIONARIO
(Datanasc ) ‘%196%’;
UFOP  ICEA  DECSI
: Consultas
• Caso os caracteres ou estejam 
presentes
• Necessário usar um caractere de escape
• Qualquer caractere não usado pode ser escolhido 
como caractere de escape
• Ex.: ‘AB _CD %EF’ ‘ ’
• Se a aspas simples ( ) for necessária, ela 
será representado como aspas duplas ( )
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 40
UFOP  ICEA  DECSI
: Consultas
• : Recupere o nome dos
funcionários que trabalham no projeto
‘ProdutoX’, junto do seu salário acrescido
em 10%.
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 41
UFOP  ICEA  DECSI
: Consultas
• : Recupere o nome dos
funcionários que trabalham no projeto
‘ProdutoX’, junto do seu salário acrescido
em 10%.
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 42
F.Pnome, F.Unome, F.Salario * 1.1 Novo_Salario
FUNCIONARIO F, PROJETO P,
TRABALHA_EM T
F.Cpf = T.Fcpf T.Pnr = P.Projnumero
P.Projnome = ‘ProdutoX’;
UFOP  ICEA  DECSI
: Consultas
• É permitido o uso de operadores aritméticos
nas consultas
• , , e podem ser usados nas consultas
• Para tipos de dados , e os
operadores e – servem como incremento e
decremento
• Intervalo é a diferença entre dois valores de
• Operador pode ser usado para
comparar valores
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 43
UFOP  ICEA  DECSI
: Consultas
• : Recuperar os funcionários do
departamento 5 cujo salário esteja entre
R$30.000 e R$40.000
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 44
UFOP  ICEA  DECSI
: Consultas
• : Recuperar os funcionários do
departamento 5 cujo salário esteja entre
R$30.000 e R$40.000
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 45
*
FUNCIONARIO F
(F.Salario 30000 40000) 
Dnr = 5;
UFOP  ICEA  DECSI
: Consultas
• Operações da teoria de conjuntos
•
• ( )
•
• Tabelas precisam ser compatíveis para 
união
• Resultado dessas operações não possuem 
tuplas duplicadas
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 46
UFOP  ICEA  DECSI
: Consultas
• : Liste os projetos que tenham
um funcionário com sobrenome ‘Silva’. Seja
como um trabalhador ou como gerente do
departamento que controla o projeto
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 47
UFOP  ICEA  DECSI
: Consultas
• : Liste os projetos que tenham
um funcionário com sobrenome ‘Silva’. Seja
como um trabalhador ou como gerente do
departamento que controla o projeto
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 48
( Projnumero
PROJETO P, DEPARTAMENTO D,
FUNCIONARIO F
P.Dnum = D.Dnumero F.Unome = ‘Silva’
F.cpf = D.cpf_gerente)
( Pnr
TRABALHA_EM TE, FUNCIONARIO F
F.Cpf = TE.Fcpf F.Unome = ‘Silva’ );
UFOP  ICEA  DECSI
: Consultas
• : Nome dos funcionários sem
dependente
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 49
UFOP  ICEA  DECSI
: Consultas
• : Nome dos funcionários sem
dependente
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 50
( Pnome, Unome
FUNCIONARIO)
( F.Pnome, F.Unome
FUNCIONARIO F, DEPENDENTE D
F.Cpf = D.FCpf);
UFOP  ICEA  DECSI
: Consultas
• Para que o resultado das operações da 
teoria de conjuntos apresentem duplicadas
• Uso de operações de multiconjunto
•
•
•
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 51
UFOP  ICEA  DECSI
: Consultas
• : Cpfs dos funcionários que
trabalham nos projetos de número 10 ou 20
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 52
UFOP  ICEA  DECSI
: Consultas
• : Cpfs dos funcionários que
trabalham nos projetos de número 10 ou 20
PROF. BRUNO R. MONTEIRO CSI440 - BANCO DE DADOS I 53
( F.Cpf
TRABALHA_EM TE, FUNCIONARIO F
F.Cpf = TE.Fcpf TE.Pnr = 20)
( F.cpf
TRABALHA_EM TE, FUNCIONARIO F
F.Cpf = TE.Fcpf TE.Pnr = 10);
UFOP  ICEA  DECSI
Referências
Elmasri, R.; Navathe, S. B. (2011). “SQL básica” In: Elmasri, R.; Navathe, S. B. Sistemas 
de Banco de Dados, 6 ed. São Paulo: Pearson, p. 57-75.