Consultas_SQL_Eventos_Parte1

Prévia do material em texto

CONSULTAS SQL
Instrução SELECT
Simulação de operações de produto 
cartesiano, junção, seleção e projeção
 
CONSULTAS SQL
• Uma instrução de consulta SQL permite a 
recuperação de dados de uma ou mais relações 
(tabelas, visões) existentes em uma base de dados;
• Não é necessário especificar a forma de 
recuperação e a ordem na qual os dados serão 
recuperados;
• SQL é uma linguagem não procedural;
• Instruções de consulta podem ser extremamente 
complexas;
• Para melhor entendimento inicia-se por instruções 
simples, chegando progressivamente às mais 
complexas.
 
CONSULTAS SQL
• Forma da instrução SELECT:
SELECT (operação de projeção pi)
FROM (produto cartesiano x)
WHERE (seleção σ) /* Não obrigatória */
• O conhecimento da estrutura e operadores 
da álgebra relacional é importante no 
entendimento do processo de execução das 
consultas;
 
CONSULTAS SQL
• Modelo básico de execução de uma consulta 
SQL:
1. É feito um produto cartesiano de todas as 
tabelas, relações envolvidas, citadas na cláusula 
FROM
2. São selecionadas todas as linhas que obedecem 
aos critérios definidos na cláusula WHERE
3. É feita a projeção das colunas que vão ao 
resultado, colunas definidas na cláusula 
SELECT
 
CONSULTAS SQL
• Exemplo: 
SELECT CodEve, Descricao
FROM Evento
Ou
SELECT * 
FROM Evento
• A cláusula FROM apresenta somente uma tabela, 
portanto não há produto cartesiano
• São projetados dois campos em ambos os casos, 
no segundo usa-se * para indicar que serão 
mostrados todos os campos
 
CONSULTAS SQL
• Álgebra Relacional:
� pi CodEve, Descricao (Evento)
� pi -> Operação de projeção dos campos que 
serão mostrados
– Inserindo uma operação de seleção para 
mostrar somente código igual 1 : pi CodEve, 
Descricao (σ CodEve = 1 (Evento))
– A primeira operação resulta na produção de 
uma relação com os registros resultantes da 
seleção (σ).
 
CONSULTAS SQL
• Exemplo 2: 
SELECT *
FROM Evento, SubArea
• Neste caso é realizado um produto cartesiano 
entre as tabelas Evento e SubArea
• O resultado do produto cartesiano é o número 
total de linhas da primeira tabela multiplicado 
pelo número de linhas da segunda
• O número de colunas é a soma das colunas das 
duas tabelas
 
CONSULTAS SQL
•Exemplo 2: 
–(Evento x SubArea): Produto cartesiano
 CODEVE DATAINI DATAFIM DESCRICAO CODINST CODSUBA CODSUBA1 DESCRICAO1 
 
 
 1 01/09/06 06/09/06 ESCOLA REGIONAL DE BANCO DE DADOS 1 1 1 BANCO DE DADOS 
 1 01/09/06 06/09/06 ESCOLA REGIONAL DE BANCO DE DADOS 1 1 2 INTELIGENCIA ARTIFICIAL 
 
 1 01/09/06 06/09/06 ESCOLA REGIONAL DE BANCO DE DADOS 1 1 3 LINGUAGENS DE PROGRAMACAO 
 
 1 01/09/06 06/09/06 ESCOLA REGIONAL DE BANCO DE DADOS 1 1 4 ARQUITETURA DE COMPUTADORES 
 
 2 15/09/06 21/09/06 SOCIEDADE BRASILEIRA DE IA 2 2 1 BANCO DE DADOS 
 2 15/09/06 21/09/06 SOCIEDADE BRASILEIRA DE IA 2 2 2 INTELIGENCIA ARTIFICIAL 
 2 15/09/06 21/09/06 SOCIEDADE BRASILEIRA DE IA 2 2 3 LINGUAGENS DE PROGRAMACAO 
 
 2 15/09/06 21/09/06 SOCIEDADE BRASILEIRA DE IA 2 2 4 ARQUITETURA DE COMPUTADORES 
 
 3 01/10/06 07/10/06 CONGRESSO DE ARQUITETURA DE COMPUTADORES 5 4 1 BANCO DE DADOS 
 
 3 01/10/06 07/10/06 CONGRESSO DE ARQUITETURA DE COMPUTADORES 5 4 2 INTELIGENCIA ARTIFICIAL 
 
 3 01/10/06 07/10/06 CONGRESSO DE ARQUITETURA DE COMPUTADORES 5 4 3 LINGUAGENS DE PROGRAMACAO 
 
 3 01/10/06 07/10/06 CONGRESSO DE ARQUITETURA DE COMPUTADORES 5 4 4 ARQUITETURA DE 
COMPUTADORES 
 4 13/10/06 18/10/06 CONGRESSO INTERNACIONAL DE BD 6 1 1 BANCO DE DADOS 
 4 13/10/06 18/10/06 CONGRESSO INTERNACIONAL DE BD 6 1 2 INTELIGENCIA ARTIFICIAL 
 4 13/10/06 18/10/06 CONGRESSO INTERNACIONAL DE BD 6 1 3 LINGUAGENS DE PROGRAMACAO 
 
 4 13/10/06 18/10/06 CONGRESSO INTERNACIONAL DE BD 6 1 4 ARQUITETURA DE COMPUTADORES 
 
 5 01/11/06 08/11/06 SIMPOSIO DE APLICACOES DE IA 5 2 1 BANCO DE DADOS 
 5 01/11/06 08/11/06 SIMPOSIO DE APLICACOES DE IA 5 2 2 INTELIGENCIA ARTIFICIAL 
 5 01/11/06 08/11/06 SIMPOSIO DE APLICACOES DE IA 5 2 3 LINGUAGENS DE PROGRAMACAO 
 
 5 01/11/06 08/11/06 SIMPOSIO DE APLICACOES DE IA 5 2 4 ARQUITETURA DE COMPUTADORES 
 
 
CONSULTAS SQL
• Exemplo 2: Resultado da consulta SELECT * 
FROM Cidade, Pais (produto cartesiano das duas 
tabelas)
CODCID NOME ESTADO CODPAIS CODPAIS1 NOME1 
 
 1 Santa Cruz do Sul RS 1 1 Brasil 
 1 Santa Cruz do Sul RS 1 2 Argentina 
 1 Santa Cruz do Sul RS 1 3 Uuguai 
 1 Santa Cruz do Sul RS 1 4 Alemanha 
 1 Santa Cruz do Sul RS 1 5 Estados Uni 
 2 Porto Alegre RS 1 1 Brasil 
 2 Porto Alegre RS 1 2 Argentina 
 2 Porto Alegre RS 1 3 Uuguai 
 2 Porto Alegre RS 1 4 Alemanha 
 2 Porto Alegre RS 1 5 Estados Uni 
 3 Lajeado RS 1 1 Brasil 
 3 Lajeado RS 1 2 Argentina 
 3 Lajeado RS 1 3 Uuguai3 Lajeado RS 1 4 Alemanha 
 3 Lajeado RS 1 5 Estados Uni 
 4 Caxias do Sul RS 1 1 Brasil 
 4 Caxias do Sul RS 1 2 Argentina 
 4 Caxias do Sul RS 1 3 Uuguai 
 4 Caxias do Sul RS 1 4 Alemanha 
 4 Caxias do Sul RS 1 5 Estados Uni 
 5 Buenos Aires AR 2 1 Brasil 
 5 Buenos Aires AR 2 2 Argentina 
 ...
 
CONSULTAS SQL
• Junção x Produto cartesiano: 
• Neste exemplo é desnecessário mostrar a 
combinação de um país com todos as cidades, 
como o resultado obtido anteriormente
• Somente o País relacionado deve ser 
considerado (chave estrangeira)
• Para tanto incluimos uma cláusula WHERE 
para seleção dos registros associados
 
CONSULTAS SQL
• Junção: 
– Representação usando a álgebra 
relacional:
� σ Cidade.CodPais = Pais.CodPais (Cidade x Pais)
– É realizada uma seleção para mostrar somente 
os registros que atendam ao critério de seleção
– Na álgebra existe o operador |x| que faz a junção 
natural, desde que os campos associados tenham 
nomes iguais
 
CONSULTAS SQL
• Junção natural, usando cláusula WHERE
• Inlcuir: WHERE Cidade.CodPais = Pais.CodPais
• É necessário colocar o nome da tabela em frente ao 
nome do campo porque ambas as tabelas possuem 
campos com o mesmo nome (CodPais)
• No resultado do produto cartesiano anteriormente um 
dos campos foi mostrado como CodPais1, porém este 
não é o nome correto; o campo foi renomeado 
automaticamente por existir outro com mesmo nome 
 
CONSULTAS SQL
• Junçã natural da consulta inicial: SELECT * 
FROM Evento, SubArea 
 WHERE Evento.CodSubA = 
SubArea.CodSubA
• Neste caso somente a subárea relacionada ao 
evento será exibida.
• SEMPRE QUE A CLÁUSULA FROM 
ENVOLVER MAIS DE UMA TABELA 
DEVERÁ HAVER IGUALDADE DE 
CAMPOS RELACIONADOS NO WHERE
 
CONSULTAS SQL
• Junção na cláusula FROM
– A junção pode ser realizada combinando a 
cláusula FROM com a cláusula WHERE 
como nos exemplos anteriores ou somente 
na cláusula FROM
– Uma junção na cláusula FROM tem a 
sintaxe FROM Tabela1 [OpçãoJuncao] 
JOIN Tabela2 ON Campo1=Campo2
– Qualquer uma das formas pode ser usada
 
CONSULTAS SQL
• Opções de junção:
– INNER: Padrão, equivalente a uso 
simples dos nomes das tabelas
– LEFT: Mostra todos os registros da 
primeira tabela, mesmo que não estejam 
associados a nenhum da segunda
– RIGHT: Todos os registros da segunda 
tabela são exibidos, mesmo não estando 
associados a nenhum da primeira
 
CONSULTAS SQL
• Opções de junção:
– Quando for usada uma opção diferente do 
padrão (INNER), é necessário definir os 
critérios de seleção, usando ON na cláusula 
FROM
– Se houverem várias junções, deve haver cuidado 
para efetuar uma junção e o resultado dela com 
outro resultado ou outra tabela
– É fundamental conhecer princípios da álgebra
 
CONSULTAS SQL
• Exemplo junção externa à direita: 
Mostrando todos as SubAreas, mesmo as 
que não possuem Evento associado
• SELECT * FROM SubArea LEFT JOIN 
Evento ON SubArea.CodSubA = 
Evento.CodSubA
• A junção LEFT, mostra todos os registros 
da primeira tabela
 
CONSULTAS SQL
• Junções LEFT e RIGHT são chamadas 
“externas”
• Os campos da segunda tabela possuem 
valor null nos registros da primeira tabela 
que não possuem nenhum registro 
relacionado na segunda
• Junção útil nos casos em que é necessário 
mostrar todos os registros de uma tabela
 
CONSULTAS SQL
• Exemplo 3: Valores repetidos
• Mostrar o código de todos as Instituições 
que já organizaram algum evento
SELECT CodInst
FROM Evento
• É necessária somente a tabela Evento 
porque o campo a ser mostrado é o 
código da Instituição.
 
CONSULTAS SQL
• Exemplo 3: Evitando valores repetidos
• No SQL mostrado, se uma mesma Instituição 
tiver organizado mais de um evento, irá 
aparecer no resultado tantas vezes quanto o 
número de eventos que realizou
• Na álgebra relacional, valores não são 
mostrados mais de uma vez
• Para não haver valores duplicados, é 
necessário usar uma cláusula DISTINCT
 SELECT DISTINCT CodInst FROM Evento
 
CONSULTAS SQL
• A teoria dos conjuntos define que o resultado não 
apresentará valores duplicados (Álgebra relacional)
• Em SQL o padrão é mostrar valores duplicados na 
resposta a menos que seja claramente definido que 
isto não deve ocorrer
• Quando são exibidos os valores de mais de um 
campo, o distinct deve ser usado para valores 
repetidos em ambos os campos, 
• Ex. SELECT DISTINCT CodInst, CodEve FROM 
Autorizacao
• No exemplo não existe a possibilidade de valores 
repetidos em ambos os campos, uma vez que os 
mesmos formam a chave primária
 
CONSULTAS SQL
• Exemplo 3: Incluindo mais tabelas
• Se houver necessidade de mostrar também 
o nome da Instituição, isto implicará na 
adição da tabela Instituição à cláusula 
FROM
• Entretanto, a adição da tabela irá gerar um 
produto cartesiano, o que obriga o 
desenvolvedor a fazer a seleção dos 
registros relacionados pelo código da 
Instituição
 
CONSULTAS SQL
• Exemplo 3: Exibindo mais de um campo e 
evitando repetições
SELECT DISTINCT Instituicao.CodInst, Nome 
FROM Instituicao, Evento
WHERE Evento.CodInst=Instituicao.CodInst
• O DISTINCT neste caso evita repetição 
porque existem valores repetidos em ambos 
os campos
• No campo CodInst o nome da tabela é 
necessário porque existem dois campos 
CodInst (ambas as tabelas)
 
CONSULTAS SQL
• Passos básicos para elaboração de uma consulta 
(SELECT, FROM, WHERE): 
1. Definir quais as tabelas que deverão constar na 
cláusula FROM
2. Efetuar a junção, selecionando apenas os registros 
relacionados
3. Projetar os campos que devem aparecer no resultado
4. Definir outros critérios de seleção
5. Definir agrupamentos, operações sobre campos, 
ordenação, etc.
 
CONSULTAS SQL
• Operadores para seleção:
– Comparações: >, <, >=, <=, =
– Negação: NOT ou !
• CodDep != 1
– LIKE: Conteúdo interno em um campo
• ‘%Rua%’: Qualquer valor que 
contenha a palavra Rua
• ‘Rua%’: Rua no início
• ‘_%’: Pelo menos um caractere
 
CONSULTAS SQL
• Operadores para seleção:
– No operador LIKE os caracteres % e _ 
significam um conjunto qualquer de 
caracteres e um caractere respectivamente
– Operador LIKE possui alto custo de 
processamento
– BETWEEN: Entre dois valores
• Ex.: Valor BETWEEN 10 AND 15
– Conectores lógicos: AND, OR
 
CONSULTAS SQL
• Ordenação de resultados: Qualquer combinação 
de colunas, mesmo os que não são projetados 
pela cláusula SELECT
• A cláusula ORDER BY permite indicar uma lista 
de campos que definem a ordem de exibição dos 
dados
• Ex.: ORDER BY Nome
• Quando houver mais de um campo, os mesmos 
serão separados por “,”
• ORDER é sempre a última cláusula 
 
CONSULTAS SQL
• Renomear tabelas:
– SELECT I.Nome, E.Descricao FROM 
Instituicao I, Evento E WHERE I.CodInst = 
E.CodInst
– Em alguns casos, pode ser necessário 
renomear uma tabela, principalmente quando 
a tabela é utilizada mais de uma vez na 
cláusula FROM
– Em casos de haver mais de uma chave 
estrangeira associada a mesma tabela em um 
consulta, é comum usar esta operação
 
CONSULTAS SQL
• Renomear tabelas:Auto relacionamento 
– Considerando a existênciade um campo que 
indique o supervisor de um funcionário: 
Funcionario(CodFunc, Nome, CodSup)
CodSup referencia Funcionario
– O campo indica o código do funcionário 
supervisor na mesma tabela => Auto-
relacionamento
– Neste caso, para mostrar o nome do 
funcionário e de seu supervisor, a tabela é 
associada a ela mesma
 
CONSULTAS SQL
• Exemplo dos dados de Funcionario
SELECT F.NOME, FSUP.NOME FROM 
FUNCIONARIO FFUNC, 
FUNCIONARIO FSUP WHERE 
FFUNC.CODSUP = FSUP.CODFUNC
CODFUNCNOME ENDERECO CODDEP CODSUP
50 Joao da Silva Rua das Oliveiras, 125 5 68
51 Jose dos Santos Rua XYZ 1 68
68 Evandro Franzen Rua E.K. 5 68
70 Fulano Rua das Oliveiras 12 51
73 Carlos Rua E.K. 5 51
 
CONSULTAS SQL
• Renomear tabelas: Execução
– Inicialmente a tabela será renomeada
– A junção será realizada com as relações 
F e FSUP
– Embora seja uma junção com dados da 
mesma tabela, para a instrução FROM 
será um produto cartesiano normal 
como se fossem tabelas diferentes
 
CONSULTAS SQL
• Renomear tabelas:Resultado 
– Seria conveniente neste caso renomear 
os campos a serem mostrados para 
NomeFuncionario, NomeSupervisor
– Não sendo renomeados, o resultado fica:
NOME NOME1
Joao da Silva Evandro Franzen
Jose dos Santos Evandro Franzen
Evandro Franzen Evandro Franzen
Fulano Jose dos Santos
Evandro Franzen Jose dos Santos
 
CONSULTAS SQL
• União de resultados de consultas:
– Equivalente ao operador ∪ da álgebra 
relacional
– De acordo com as características deste 
operador:
• As tabelas ou relações resultantes de uma 
consulta devem ter o mesmo número de 
campos
• O domínio da i-ésima coluna da primeira 
dever igual ao da i-ésima da segunda
 
CONSULTAS SQL
• Exemplo: Mostrar os eventos da 
subárea 1 e 2
• SELECT CodEve, Descricao FROM 
Evento WHERE CodSubA=1 
UNION SELECT CodEve, 
Descricao FROM Evento WHERE 
CodSubA=2
 
CONSULTAS SQL
• Campos calculados e renomeados:
– Na operação de projeção, é possível 
efetuar um cálculo usando valores 
dos campos
– Ex. SELECT CodEve, CodPess, 
Valor * 2 AS DobroValor FROM 
Inscricao
– O valor da inscrição é multiplicado 
por 2 e renomeado para DobroValor
 
CONSULTAS SQL
• Campos calculados:
– É possível utilizar qualquer operador 
matemático comum (*, /, -,+)
– Mais de um campo pode ser usado na 
expressão de cálculo 
• Na projeção podem aparecer textos fixos 
‘teste’, Ex. SELECT ‘Teste’
• Algumas funções podem ser aplicadas a 
valores unitários de campos UPPER(c1)
 
CONSULTAS SQL
• Exercícios:
– Mostrar código e descrição do evento, 
nome da instituição, descrição da 
subárea dos eventos com código de 
subárea igual a 1
– Mostrar código e descrição do evento, 
sequência e descrição da atividade, para 
todas as atividades em eventos que 
iniciaram no mês de setembro/2006
 
CONSULTAS SQL
• Outros exemplos:
– Mostrar código do evento, sequencia da 
atividade, descrição do tipo da atividade, 
nome do ministrante, de todas as atividades 
do evento 2
– Mostrar código, data de início, de fim, 
descrição do evento, código e descrição do 
tópico para todos os tópicos dos eventos que 
iniciam entre 01/09/06 e 31/10/06 e que a 
palavra ‘XML’ apareça na descrição do 
tópico
 
CONSULTAS SQL
• Outros exemplos:
– Mostrar código da pessoa, nome da pessoa, 
endereço, nome da cidade, nome do País, 
data da inscrição, para as inscrições 
realizadas para o evento com o código igual 
a 2.
– Modificar esta consulta para mostrar os 
mesmos dados das inscrições para o evento 
com a descrição ‘CONGRESSO DE 
ARQUITETURA DE COMPUTADORES’
 
CONSULTAS SQL
• Outros exemplos:
– Mostrar o código e descrição do evento, 
sequencia e descrição da atividade, 
descrição do tipo de atividade, de todas 
as atividades que estão inscritas 
pessoas da Instituição ‘UNISC’. 
Ordenar pela descrição do evento e 
sequencia de atividade.
 
CONSULTAS SQL
• Criar a consulta abaixo:
– Mostrar o nome da cidade, de todas 
as cidades que possuem alguém 
inscrito no evento 1. Mostrar cada 
nome de cidade apenas uma vez, 
mesmo que existam diversas pessoas 
que residem em uma mesma cidade 
inscritos.