132
INF 314 Projeto de Bases de Dados Relacionais e Linguagem SQL Prof. C ´ elio Guimar ˜ aes IC - Unicamp Marc ¸o 2004

INF 314 Projeto de Bases de Dados Relacionais e Linguagem SQL …celio/inf314/slides/6.1.pdf · Fundamentos de bancos de dados Célio Cardoso Guimarães Modelagem, projeto e linguagem

  • Upload
    lyduong

  • View
    215

  • Download
    0

Embed Size (px)

Citation preview

INF 314Projeto de Bases de Dados

Relacionais e Linguagem SQL

Prof. Celio Guimaraes

IC - UnicampMarco 2004

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL O comando select

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

q1:select pnome, nome, ano n, cid resfrom jogadoreswhere pais = ’BRA’order by pnomeCada clausula desse exemplo (exceto aclausula order by) corresponde a umaoperacao da Algebra Relacional.selecione da tabela Jogadores todos os quesao de nacionalidade brasileira, apresentandopara cada um o prenome, sobrenome, ano denascimento e cidade de residencia, ordenandoalfabeticamente as linhas do resultado pelopre nome.© Célio Cardoso Guimarães

UNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL O comando select (cont)

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

1. Tabela intermediaria apos a clausula where:

numj nome pnome pais ano n ano p cid n cid res etc910 Kuerten Gustavo BRA 76 95 Florianopolis Florianopolis ...56 Sa Andre BRA 77 96 Belo Horizonte Belo Horizonte ...

412 Meligeni Fernando BRA 71 90 Buenos Aires Sao Paulo ...

2. Tabela intermediaria apos order by:

numj nome pnome pais ano n ano p cid n cid res etc56 Sa Andre BRA 77 96 Belo Horizonte Belo Horizonte ...

412 Meligeni Fernando BRA 71 90 Buenos Aires Sao Paulo ...910 Kuerten Gustavo BRA 76 95 Florianopolis Florianopolis ...

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL O comando select (cont)

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

3. Apos projecao no pre-nome, nome, ano n,ano res:

pnome nome ano n cid resAndre Sa 77 Belo HorizonteFernando Meligeni 71 Sao PauloGustavo Kuerten 76 Florianopolis

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL O comando select (cont)

�� � � �� � ��� � � �� � � �� � � �� �� �� �� �� �� �� � ��� � � �� �� �� �� �� �� �� �� �� �� �� � ��� � � �� �� �� �� �� �� �� �� �� �� �� � ��� � � �� � � �� �� �� �� �� ��q2:select *from jogadores,

copia da instancia atual da tabela Jogadores,com todos os atributos de um jogador.Algumas expressoes SQL nao produzemconjuntos matematicos (sem elementosrepetidos):q3:select ano nfrom jogadores

nao e equivalente a:

��� �� �

� � � � �� �� �

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL O comando select (cont)

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Para eliminarmos os anos repetidos temos queusar na clausula select a palavra reservadadistinct:

q4:

select distinct ano nasc

from jogadores

No exemplo abaixo distinct aplica-se a ambasas colunas:

q5:

select distinct cid res, ano prof

from jogadores

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Operadores de comparacao

�� � � �� � ��� �� �� �� �� �� �� �� �� �� �� �� �� � ��� �� �� �� �� �� �� �� �� �� �� �� �� � ��� �� �� �� �� �� �� �� �� �� �� �� �� � ��� �� �� �� �� �� �� �� �� ��Sao os 6 operadores que vimos na AlgebraRelacional:

��� �� � � � � � � � � � �q6: “Obtenha o nome dos jogadores que setornaram profissionais com menos de 22 anos”

select nome

from jogadores

where ano p - ano n � 22

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Conectores logicos and, or, not

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Usados na clausula where para comporexpressoes booleanas:

select nome

from jogadores

where (ano p - ano n � 22

and pais = ’BRA’)

or cid res = ’Florianopolis’

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Operador between

�� � � �� � ��� � � �� � � �� � � �� �� �� �� �� �� �� � ��� � � �� �� �� �� �� �� �� �� �� �� �� � ��� � � �� �� �� �� �� �� �� �� �� �� �� � ��� � � �� � � �� �� �� �� �� ��A expressao “A between B and C” eequivalente a:

� � � � � � � � �

“not ( A between B and C )”equivale a:

A not between B and C

q7: “Obtenha o nome e ano de nascimento dosjogadores nascidos entre 1975 e 1981”

select nome, ano n

from jogadores

where ano n between 75 and 81© Célio Cardoso Guimarães

UNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Operadores in, not in

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Usados para verificar se um valor esta contido(ou nao) num conjunto de constantes:

q8:

select numj

from jogadores

where cid res in

(’Las Vegas’, ’Monte Carlo’, ’Los Angeles’)

Exercıcio:

q9: “De o nome dos jogadores que nao moramnem em Las Vegas nem em Los Angeles”.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Operador like

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Faz um casamento de padroes de cadeias decaracteres:

q10:

select nome

from jogadores

where nome like ’B%’,

obtem nomes dos jogadores cujo nomecomeca pela letra B.

% significa: “qualquer cadeia com zero ou maiscaracteres”.© Célio Cardoso Guimarães

UNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Operador like (cont)

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

q11:

select nome

from jogadores

where nome like ’%n’

obtem o nome de cada jogador que terminacom a letra n.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Operador like (cont)

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

quando usado num padrao significa:“exatamente um caractere naquela posicao”:

q12:

select nome

from jogadores

where nome like ’%e ’

nome de cada jogador em que a letra e e apenultima.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Operador like (cont)

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Se quisermos buscar uma cadeia contendo ocaractere ’ ’, temos que usar um caractere deescape:

where nome like ’%= %’ escape ’=’

que procura os nomes que possuem ocaractere ’ ’

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Operador like (cont)

�� � � �� � ��� � � �� � � �� � � �� �� �� �� �� �� �� � ��� � � �� �� �� �� �� �� �� �� �� �� �� � ��� � � �� �� �� �� �� �� �� �� �� �� �� � ��� � � �� � � �� �� �� �� �� ��Exercıcios:

Escreva uma consulta para cada um dos casosabaixo:

“De o nome de cada jogador cujo nomesatisfaz:

� q13: contem a sequencia de letras ’is’,

� q14: possui exatamente 5 caracteres,

� q15: possui pelo menos 5 caracteres,

� q16: tem a letra a como a segunda epenultima letra”.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Funcoes sobre cadeias de caracteres

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Tomam como operando uma cadeia decaracteres que pode ser o valor de uma colunada lista da clausula select, ou operando daclausula where ou uma constante literal.

� position (cadeia1 in cadeia2) - obtem aposicao onde a cadeia1 comeca na cadeia2(retorna 0 se cadeia1 nao encontrada).Exemplo:

position (’W’ in ’Hello World!’) - retorna 7

� char length(cadeia1) - numero decaracteres da cadeia1

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Funcoes sobre cadeias de caracteres

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� upper(cadeia1) - retorna o argumento emmaiusculas:

select upper(nome), upper(cid n)

from jogadores

where numj = 910

substring(cadeia1 from start [for comp]) - extraiuma subcadeia da cadeia1, comecando naposicao start e com comprimento compcaracteres. Se este for omitido, extrai ate o fimde cadeia1. Exemplo:

substring (’Hello World!’, 7, 4) retorna: ’World’

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Funcoes sobre cadeias de caracteres

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

operacao de concatenacao: ‘

� �

’ - util paraconcatenar valores alfanumericos provenientesde 2 ou mais colunas da lista da clausulaselect. Exemplo:

select pnome

� �

’ ’

� �

nome

from jogadores

where numj = 910

Esta consulta retorna a cadeia ’GustavoKuerten’

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Funcoes com operandos numericos

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� abs(valor-numerico). Exemplo: abs(-12)retorna 12

� mod(valor1, valor2). Exemplo: mod(8, 3)retorna 2

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Funcao cast: conversao de tipos

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

util para comparar tipos de dados diferentes:uma cadeia de caracteres que representa umnumero com um literal numerico

Suponha que a coluna numt fosse do tipochar(3) e contendo valores variando de ’001’ a’999’. Entao poderıamos usar:

where cast (numt as int) � 100

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Operacao de juncao

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

A seguinte consulta pode ser expressa comesta operacao:

q17: Para cada torneio, de o seu nome, ano eo numero do seu vencedor.

a tabela Torneios tem, do torneio, apenas oseu numero; para obter o seu nome temos que“olhar”a tabela Lista torneios:

select nomet, ano, numj

from torneios, lista torneios

where torneios.numt = lista torneios.numt

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Operacao de juncao (cont)

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Podemos interpretar a execucao dessecomando da seguinte forma:

1. obtenha o produto cartesiano de Torneioscom Lista torneios;

2. para cada linha do produto cartesianoselecione aquelas em que torneios.numt =lista torneios.numt

3. faca uma projecao do resultado nas colunasnomet, ano e numj.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Sinonimo para nome de tabela (alias)

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Em SQL podemos criar sinonimo para o nomede uma tabela:

fundamental em consultas envolvendo umajuncao de uma tabela consigo mesma.Exemplo:

q18: “obter os nomes dos jogadores maisvelhos que Kuerten”.

abaixo, j1 e j2 sao sinonimos de Jogadores:

select j1.nome

from jogadores j1, jogadores j2

where j2.nome = ’Kuerten’ and j1.ano n �

j2.ano n© Célio Cardoso Guimarães

UNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Sinonimo para nome de tabela (alias)

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

As vezes precisamos fazer a juncao de 3tabelas: O exemplo abaixo introduz alias paraas tabelas a fim de minimizar o texto daconsulta.

q19: “Para cada jogador que venceu algumtorneio obtenha o seu nome, o nome dotorneio e o ano da vitoria no torneio”.

select j.nome, lt.nomet, t.ano

from jogadores j, lista torneios lt, torneios t

where j.numj = t.numj and t.numt = lt.numt

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Problemas com operandos Nulos

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Valores Nulos sao problematicos:

� nao disponibilidade dos dados,

� como operandos de consultas SQL

� semantica de operacoes nao claramentedefinidas

� resultados inesperados podem ocorrer;expressao aritmetica: se um dos operandostem o valor Nulo, o resultado e Nulo.

Vamos denotar com o sımbolo ’?’ o valor Nulo.

Exemplo: 10 + ? = ?© Célio Cardoso Guimarães

UNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Problemas com operandos Nulos

�� �� �� ���� � � �� � � �� � � �� �� �� �� �� �� �� ���� � � �� �� �� �� �� �� �� �� �� �� �� ���� � � �� �� �� �� �� �� �� �� �� �� �� ���� � � �� � � �� �� �� �� �� ��Comparacoes com Nulos sao problematicas:

se x tem o valor Nulo o resultado dacomparacao ’x = 3’, nao e falso nemverdadeiro, mas desconhecido!

Logica booleana de tres valores: ‘falso’,‘verdadeiro’ e ‘desconhecido’.

Notacao: ’?’ valor booleano desconhecido.

Expressao Resultado

� �

?

� � � �

?

� � �

?

� � �

?

: valor nao Nulo

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Problemas com operandos Nulos

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

q20:

select * from jogadores

where natp = NULL

nao retorna nada, embora haja jogadores cujonatp tem o valor Nulo.

q21: select * from jogadores

where natp = natp

retorna apenas os jogadores cujo natp nao eNulo, em vez de retornar todos os jogadores.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Problemas com operandos Nulos

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Para testar se um determinado valor e Nulotemos um operador especial: IS NULL

q22:

select * from jogadores

where natp IS NULL,

nos da todos os jogadores aposentados.

Idem para testar se um valor nao e Nulo:

q23:

select * from jogadores

where natp IS NOT NULL,

nos da todos os jogadores que participam detorneios oficiais da ATP.© Célio Cardoso Guimarães

UNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Problemas com operandos Nulos

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

q24: “de o nome de todos os jogadores excetodaquele cujo natp e 1271”.

A solucao abaixo nao da o resultado esperado:

select nome

from jogadores

where natp �� 1271

V. deveria acrescentar na clausula where:

or natp IS NULL

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL

Expressoes booleanas envolvendoNulos

�� � � �� � ��� �� �� �� �� �� �� �� �� �� �� �� �� � ��� �� �� �� �� �� �� �� �� �� �� �� �� � ��� �� �� �� �� �� �� �� �� �� �� �� �� � ��� �� �� �� �� �� �� �� �� ��

Expressao Resultado

� � � � �

?

� �� �

?

� � � �

?

� � � �

? ou F(caso seja Falso)

� � �

? ou T(caso seja True)

Obs: na presenca de Nulos, linhas de umatabela nao sao selecionadas pela clausulawhere quando o resultado da expressaobooleana for falso ou desconhecido.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exercıcios envolvendo Nulos

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

1. q25: Obtenha uma solucao correta para aconsulta: “de o nome de todos os jogadoresexceto daquele cujo natp e 1271”.

2. q26: Qual o resultado do comando?:

select numj, natp

from jogadores

where natp � � natp

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Consultas aninhadas (subconsultas)

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

operando de uma expressao booleana naclausula where:

um outro comando select (“consultaaninhada”ou subconsulta).

Ha varias formas como isto pode ser feito:

Subconsultas com o operador in

q27: “Obtenha o nome de cada jogador quevenceu pelo menos um torneio.”

select nome

from jogadores

where numj in

(select numj from torneios)© Célio Cardoso Guimarães

UNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Consultas aninhadas (cont)

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Poderıamos fazer a mesma consulta usandouma juncao:

q28:

select distinct nome

from jogadores, torneios

where jogadores.numj = torneios.numj

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Consultas aninhadas (cont)

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

“q29: obter os nomes dos jogadores quevenceram uma ou mais vezes o torneio 1”:

select nome

from jogadores

where numj in

(select numj from torneios

where numt = 1),

q30: O exemplo acima poderia tambem serfeito com uma juncao (tente!).

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Consultas aninhadas (cont)

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Ha casos em que nao podemos usar umaconsulta aninhada no lugar de uma juncao:

q31: “Para cada jogador que venceu torneios,obtenha o seu nome, o numero do torneio e oano”.

select nome, numt, ano

from jogadores, torneios

where jogadores.numj = torneios.numj

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Consultas aninhadas (cont)

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

O contrario tambem e verdadeiro: ha casos emque uma consulta aninhada nao pode sersubstituıda por uma juncao:

q32: “obtenha os nomes dos jogadores quenao venceram torneios”:

select nome

from jogadores

where numj not in

(select numj from torneios)

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Consultas aninhadas (cont)

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

E se intuitivamente usarmos para a consulta:“obtenha os nomes dos jogadores que naovenceram torneios”, a seguinte juncao ?:

q33:

select nome

from jogadores, torneios

where jogadores.numj �� torneios.numj

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL

Subconsulta com operador decomparacao

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

q34: “Obtenha o pre-nome e o nome dojogador que ganhou o torneio 2 em 1997”.

select pnome, nome

from jogadores

where numj =

(select numj

from Torneios

where numt=2 and ano= 1997

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL

Subconsulta com operador decomparacao

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

q35: “Obtenha o nome de cada jogador que emais velho que Kuerten”.

select nome

from jogadores

where ano n �

(select ano n

from jogadores

where nome = ’Kuerten’)

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL

Subconsulta com operador decomparacao

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

q36: “Obtenha o(s) nome(s) do(s) jogador(es)mais velho(s) que todos os outros jogadores”.

Deve ser obvio que a solucao abaixo eincorreta:

select nome

from jogadores

where ano n � �(select ano n from jogadores)

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Operadores all e any em subconsultas

�� �� �� ���� � � �� � � �� � � �� �� �� �� �� �� �� ���� � � �� �� �� �� �� �� �� �� �� �� �� ���� � � �� �� �� �� �� �� �� �� �� �� �� ���� � � �� � � �� �� �� �� �� ��O operador all resolve o problema da solucaoerronea da consulta anterior:

select nome

from jogadores

where ano n � � all

(select ano n from jogadores),

onde � � � � � �� � � � � � � ��

significa:

� � � � � � � � � � � � � � � � �

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Operadores all e any em subconsultas

�� �� �� ���� � � �� � � �� � � �� �� �� �� �� �� �� ���� � � �� �� �� �� �� �� �� �� �� �� �� ���� � � �� �� �� �� �� �� �� �� �� �� �� ���� � � �� � � �� �� �� �� �� ��Operador any: q37: “ obter o nome da cadajogador que nao pertence ao grupo dos maisvelhos”select nomefrom jogadoreswhere ano n � any(select ano n from jogadores)

� � �

�� � � � � � ��

significa: “algumdentre”� � � � � � �, ou seja,

� � � � �

�� � � � � � � ��significa:

� � � � �� � � � �� � � � �

Observe que apenas o(s) jogador(es) maisvelho(s) da(dao) um resultado falso para acomparacao acima.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Operadores exists e not exists

�� �� �� ���� �� �� �� �� �� �� �� �� � � �� �� �� ���� �� �� �� �� � � �� � � �� � � �� �� �� ���� �� �� �� �� � � �� � � �� � � �� �� �� ���� �� �� �� �� � � �� � � �� � �Tem funcao semelhante ao operador

doCalculo Relacional. Eles sao sempre usadosna forma:

. . . where [not] exists

(subconsulta)

q38: “obtenha o nome de cada jogador quevenceu algum torneio”.

select nome

from jogadores

where exists

(select * from torneios

where torneios.numj = jogadores.numj)

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Operadores exists e not exists

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

O conceito de “subconsulta correlacionada”.

antes uma subconsulta era inteiramenteavaliada e o seu resultado passado para aconsulta mais externa;

neste caso, para cada linha da tabelaJogadores a subconsulta e avaliada, utilizandoo valor corrente de jogadores.numj.

Como so estamos interessados em saber se oresultado da subconsulta e ou nao uma tabelavazia, e comum usar a clausula: select *(embora nao seja obrigatorio).

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Operadores exists e not exists

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

q39: “obtenha o nome de todos os jogadoresque nao foram vencedores de torneio”.

select nome

from jogadores

where not exists

(select * from torneios

where torneios.numj = jogadores.numj)

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exercıcios:

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Utilize o operador exists ou not exists paraexpressar as consultas abaixo:

1. q40: “Obtenha o nome de cada jogador quevenceu o torneio de Wimbledon”.

2. q41: “Obtenha o nome de cada jogador quenao foi vencedor de torneios vencidos pelojogador Sampras (numj = 812)”.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL

Escopo de nomes em comandosselect

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

um atributo numa clausula where com omesmo nome de atributo de tabelareferenciada na clausula from imediatamenteanterior, e considerado como se referindo aoatributo dessa tabela.

select nome

from jogadores

where not exists

(select * from torneios

where numj = jogadores.numj

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL

Escopo de nomes em comandosselect

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Esse esquema e recursivo:

� as clausulas select e from definem um novoescopo de variaveis locais para tabelas esuas colunas,

� ele evisıvel em todas as consultasaninhadas que se seguem,

� exceto se houver redefinicao de algumatabela em alguma consulta mais aninhada.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL

Operadores uniao (union), intersecao(intersect) e diferenca (except)

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Jogadores Aposentados Jogs Ativosnumj nome numj nome817 Courier 412 Meligeni19 Edberg 29 Agassi102 Muster 16 Bruguera1122 Becker 812 Sampras123 Korda 218 Kafelnikov... ... ... ...

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exemplo (uniao):

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

q42: “De o numero e nome de todos osjogadores”.

select numj, nome

from jogadores aposentados

union

select numj, nome

from jogadores ativos

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Restricoes uniao/intercecao/diferenca

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� Varias unioes sao admissıveis encadeandocomandos select via union; idem paraintersect e except; qualquer combinacaodeles e admissıvel,

� apenas o ultimo select pode conter order by,

� linhas duplicadas sao eliminadas, a menosque se use a palavra reservada all apos onome do operador.

� colunas correspondentes devem ser domesmo tipo

� distinct nao e permitido

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Clausula order by

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

E possıvel especificar multiplas colunas paraordenacao:

q43:

select numj, ano

from torneios

order by numj, ano

Nesse caso e feita uma ordenacaolexicografica: por jogador, e para cada jogadorpelo ano do(s) torneio(s) que venceu.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Clausula order by

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

A ordenacao e ascendente por default, maspode ser descendente usando a palavrareservada desc

q44: select nome, cid res

from jogadores

order by cid res asc, nome desc

Onde aparecem valores Nulos no exemplo ?:

q45: select natp

from jogadores

order by natp

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Clausula order by

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

dependente da implementacao:

SQL Server e Sybase: aparecem antes dequalquer valor, seja para ordem ascendente oudescendente;

Oracle: aparecem em ultimo lugar se ordemascendente e em primeiro lugar se ordemdescendente.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Funcoes agregado

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Obtem estatısticas sobre os valores de umadada coluna.

count(nome-coluna): obtem o numero devalores na coluna especificada

min(nome-coluna): valor mınimo da coluna

max(nome-coluna): valor maximo da coluna

sum(nome-coluna): soma os valores da coluna

avg(nome-coluna): media aritmetica dosvalores da coluna

count(*): conta o numero de linhas

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Funcoes agregado

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� sum e avg so admitem colunas com valoresnumericos

� count, sum e avg podem ter comoargumento: distinct nome-coluna. Nessecaso somente valores distintos saoconsiderados.

� valores Nulos na coluna especificada saodesconsiderados; por esta razaocount(nome-coluna) pode dar menor quecount(*) se nome-coluna possui valoresNulos.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Funcoes agregado na clausula select

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Sao aplicadas sobre todas as linhasselecionadas:

q46: “Quantos torneios existem?”.

select count (*)

from lista torneios

q47: “Qual foi o premio de valor maximo?”.

select max(premio)

from torneios

q48: “ Quantas cidades de residencia distintasexistem?”.

select count (distinct cid res)

from jogadores

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Funcoes agregado na clausula select

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

q49: “ Quantos jogadores vivem em MonteCarlo?”.

select count (*)

from jogadores

where cid res = ’Monte Carlo’

q50: “* Quantos premios sao iguais ao mınimopremio?”.

select count (premio)

from torneios

where premio = (select min (premio) fromtorneios)© Célio Cardoso Guimarães

UNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Funcoes agregado na clausula select

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

q51: “Obtenha para cada jogador que venceutorneios, o valor do seu maior premio, onumero do torneio e o ano em que foi ganho”.

select numj, premio, numt,ano

from torneios t1

where premio =

( select max(premio )

from torneios t2

where t2.numj = t1.numj )

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exercıcios:

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� Qual a diferenca entre os comandos?:q52: select avg (premio)from torneioseselect sum (premio) / count ( * )from torneios

� O que ha de errado no comando? :q53: select count (*), numjfrom jogadores

� q54: Qual o premio medio para jogadoresque venceram o torneio 1 ?

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exercıcios (cont):

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� q55: * Obtenha o valor medio de jogadorespor cidade de residencia, i. e, total dejogadores / total de cidades de residencia

� Obtenha as seguintes estatısticas dedesempenho dos jogadores:

q56: Qual a porcentagem de vitorias decada jogador em relacao ao numero total detorneios disputados, isto e: vits*100/(vits +derrs) Apresente os jogadores em ordemdecrescente dessa metrica.

q57: Qual jogador obteve o melhordesempenho segundo a metrica acima?

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exercıcios (cont):

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

q58: Qual a porcentagem de vitorias emtorneios de simples e duplas de cada jogador,em relacao ao numero total de torneiosdisputados? Apresente-os em ordemdecrescente dessa medida.

q59: Qual o jogador que obteve o melhordesempenho segundo essa metrica?

q60: Quais jogadores tiveram mais derrotasque vitorias em torneios da ATP?

q61: Qual jogador obteve a maior soma devitorias em torneios de simples e duplas?

q62: * Obtenha os nomes dos jogadores cujasvitorias sao maiores que o dobro das derrotas,em ordem decrescente dessa metrica.© Célio Cardoso Guimarães

UNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Miscelanea

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Pode ser util definir sinonimos (alias) paranomes de colunas, especialmente envolvendofuncoes agregado ou expressoes aritmeticas.Exemplo:

select pnome as nome, nome as sobrenome,ano n as ”ano nascimento”,

ano p - ano n as idade profissionalizacao

from jogadores

(quando o alias e um nome com espacos embranco, o mesmo deve ser colocado entreaspas duplas).

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Miscelanea

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Pode ser util concatenar os valores de duas oumais colunas via operador

� �

:

select pnome

� �

’ ’

� �

nome as ’nome completo’

from jogadores

produzindo como resultado:

nome completo

Fernando Meligeni

Jim Courier

etc

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Comando insert

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Insercao de uma so linha:

q63: insert torneios (numt, ano, numj)

values (2, 2003, 910)

ou,

q64: insert torneios

values (2, 2003, 910)

ou

q65: insert into torneios (numt, ano, numj)

values (2, 2003, 910)© Célio Cardoso Guimarães

UNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Insercao de linhas via outra tabela

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

q66: insert into jogadores aposentados (numj,nome, cidade, pais)

select numj, nome, cidade, pais

from jogadores

where natp is null

Insercao com coluna calculada:

q67: insert maiores premios

select numj, ano, premio*1.33

from torneios

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Comando update

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Permite atualizar uma ou mais colunas de umatabela:

q68: update jogadores

set natp = ’2000’

where numj = 1272

q69: update jogadores

set cid res= ’Los Angeles’,

pais = ’EUA’

where nome = ’Agassi’

q70: update torneios

set premio = premio * 1.5© Célio Cardoso Guimarães

UNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Insercao a partir de 2 tabelas

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Seja a tabela:

vencedores (numj, nome, numt, ano, premio),

queremos inserir linhas a partir do comando:

q71: insert into vencedores

select torneios.numj, nome, numt, ano, premio

from jogadores, torneios

where jogadores.numj = torneios. numj

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Comando delete

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Permite remover uma ou mais linhas de umatabela:

q72:

delete from torneios

where numj = 224,

remove as linhas da tabela Torneios,correspondentes ao jogador 224.

q73:

delete torneios,

remove todas as linhas da tabela torneios.© Célio Cardoso Guimarães

UNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Tabelas temporarias e/ou dinamicas

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Tabelas temporarias: existem durante asessao onde foram criadas.

create temporary table newtable as

(numj int, nome varchar(16))

Tabelas dinamicas:

criadas a partir de subconsulta sobre uma oumais tabelas,

utilidade: copias personalizadas ou copiastemporarias de uma tabela

create table novos jogadores as

select nome, pnome, ano p, tit s

from jogadores

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Clausula group by

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

argumento: nome de um ou mais atributos.

� coleta as linhas de uma tabela em grupos,

� cada grupo contem as linhas com mesmovalor do(s) atributo(s) especificado(s) naclausula group by;

� sobre as linhas do grupo se quer obter umaestatıstica via funcao agregado:numero de linhas do grupo, soma dosvalores de um atributo dessas linhas,

� o comando select onde se encontra aclausula group by retorna uma unica linhapor grupo

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Clausula group by

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

q74: select numj

from torneios

group by numj

apresenta os torneios agrupados pelos seusvencedores.

pouco interessante, a consulta mais simples,

q75: select numj

from torneios

difere apenas num ponto: nao ha repeticao dosnumeros dos jogadores, pois apenas uma linhae retornada por grupo.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Clausula group by

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

E possıvel ter mais de uma coluna nogrupamento:

q76: select numj, numt

from torneios

group by numj, numt

grupo: par com valores distintos (numj, numt)

se um jogador ganhou um torneio mais de umavez o grupo tera mais de uma linha: apenas opar (numj, numt) e retornado.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Clausula group by

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

uma funcao agregado apos os atributos daclausula select, gera consultas maisinteressantes:

q77: select numj, count(*)

from torneios

group by numj

retorna para cada jogador (um grupo) onumero total de torneios que ele venceu =numero de linhas do grupo.

count(*) e aplicada sobre as linhas de cadagrupo.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Clausula group by

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Para o jogador 812 (Sampras) o grupo contem13 linhas sendo retornado:

910 13

Ja a consulta:

select numj, numt, count(*)

from torneios

group by numj, numt

retorna para cada jogador e para cada torneioque ele venceu (um grupo), o numero de vezesque ele venceu esse torneio.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Clausula group by

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Para o jogador 812 (Sampras) retornaria:

812 1 2

812 3 7

812 4 4

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Clausula group by - Restricoes

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� atributos na clausula select devem ser osmesmos da clausula group by,

� na lista de select podem aparecer: nome decolunas mencionadas em group by e/oufuncoes agregado,

� se na lista de select so aparecem funcoesagregado, entao group by pode mencionarquaisquer colunas de tabelas mencionadasna clausula from

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Clausula group by - Exemplos

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

q78: “ Para cada cidade de residencia de ototal de jogadores”.

select cid res, count (numj)

from jogadores

group by cid res

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Clausula group by - Exemplos

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

q79: * “Para cada jogador de o seu nome, onumero total de torneios que ele ganhou, emordem decrescente desse numero”

select nome, count(*) as num vitorias

from torneios t, jogadores j

where t.numj=j.numj

group by nome

order by num vitorias desc, nome asc

possıvel ordenar o resultado pelo valor defuncao agregado,

possıvel criar um alias para esse valor e usa-loem order by.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Clausula having

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

serve para especificar condicao para que linhaseja incluıda no grupo

condicao pode ser uma funcao agregado, a seraplicada sobre cada grupo.

q80: “De o numero de cada jogador quevenceu mais de um torneio”.

select numj

from torneios

group by numj

having count(*) � 1

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Clausula having

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

apos agrupar as linhas da tabela Torneios porjogador:

count e aplicada sobre o grupo,

somente os grupos satisfazendo a condicaosao selecionados.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Clausula having - Exemplos

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Variante nao trivial da consulta:

q81: * “De o numero de cada jogador quevenceu pelo menos dois torneios distintos”.

select numj

from torneios

group by numj

having count(distinct numt) � 1

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Clausula having - Exemplos

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

q82: “De os numeros dos jogadores quereceberam premios cuja media foi superior a400 KUS$”.

select numj

from premios

group by numj

having avg (premio) � 400

Obs: avg = sum (valores nao Nulos) / count(valores nao Nulos)

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Clausula having - Exemplos

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

q83: “ Para cada jogador que venceu mais deum torneio de a soma total dos premiosrecebidos”.

select numj, sum(premio)

from torneios

group by numj

having count(*) � 1

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Clausula having - Exemplos

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

q84: “ Quantos torneios foram vencidos nomesmo ano por cada jogador?”

select numj,ano, count(*)

from torneios

group by numj,ano

order by count(*) desc, numj

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Clausula having - Exemplos

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

q85: “* Obtenha os numeros dos jogadores queganharam pelo menos k torneios distintos”.

select numj, count(distinct numt) ct

from torneios

group by numj

having count(distinct numt) � � k

order by ct desc

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Influencia de valores Nulos

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

O fato de que valores Nulos nao saoconsiderados pelas funcoes agregado e usadona consulta a seguir:

q86: “ De o nome de cada cidade de residenciaque tem apenas um jogador ativo”.

select cid res

from jogadores

group by cid res

having count(natp) = 1

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exercıcios

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� q87: Para a coluna ano n da tabelaJogadores determine o valor retornado por:

1. count (ano n)

2. min (ano n)

3. max (ano n)

4. sum (ano n)

5. avg (ano n)

6. count (distinct (ano n)

7. min (distinct (ano n)

8. max (distinct (ano n)

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exercıcios

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� q88: Encontre as cidades em que mais de 2jogadores vivem.

� q89: De o nome de cada jogador quevenceu mais de um torneio

� q90: O que esta errado no comandoabaixo? :select numj, premiofrom torneiosgroup by numj

� q91: Para cada cidade de residencia de aquantidade de jogadores nascidos apos1975 .

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exercıcios

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� q92: De o nome e ano de nascimento decada jogador nascido apos o ano medio denascimento de todos os jogadores.

� q93: Para cada jogador que ganhou algumpremio acima de 400 KUS$, de o seunumero e o maximo premio que ele ganhou.

� q94: * “Obtenha o nome do jogador, onumero de torneios de simples, tit s, e onumero de torneios de Grand Slam por elevencidos. Apresente o resultado em ordemdecrescente do numero de torneios desimples vencidos”.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exercıcios

�� �� �� ���� �� �� �� �� �� �� �� �� � � �� �� �� ���� �� �� �� �� � � �� � � �� � � �� �� �� ���� �� �� �� �� � � �� � � �� � � �� �� �� ���� �� �� �� �� � � �� � � �� � �

� q95: “Obtenha a media do numero demateriais fornecidos por fornecedor”, isto e,numero de fornecimentos / numero defornecedores.

� q96: Obtenha a media do numero detorneios vencidos por jogador, isto e,

� �� � � � � �� � � � �� � � �� � � �� � �� � � � �� � � � �� ��

�� � � � � � � �� �� � � � � � �� � � � � �� � � � ��

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exercıcios

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� q97: * “Para cada jogador de o seu nome eo numero de torneios distintos que elevenceu”.

� q98: * “De o nome dos jogadores quevenceram pelo menos k torneios distintos”.

� q99: * “De o nome do(s) jogador(es) quevenceu o maior numero de torneiosdistintos”.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exercıcios

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� q100: * “ Apresente cada jogador queganhou a maior soma de premios e qualesse valor”.

select numj, sum(premio)

from torneios

group by numj

having sum(premio)� � ALL

(select sum(premio) from torneios

group by numj)

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exercıcios

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� q101: * Para cada torneio, quais jogadoreso venceram mais vezes ?select t.numt, t.numj,count(*)from torneios tgroup by t.numj,t.numthaving count(*) � � all(select count(*) from torneioswhere numt=t.numtgroup by numj)order by t.numt

� q102: * De o nome de cada torneio e dosjogadores que o venceram mais vezes

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exercıcios

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� q103: * “Para cada ano, qual(is) jogador(es)venceu(ram) o maior numero de torneiosnesse ano, e quantos torneios ele venceu?

select ano,numj, count(*)

from torneios t1

group by ano,numj

having count(*) � � all

(select count(*) from torneios

where ano=t1.ano

group by numj)

order by ano,numj

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exercıcios

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� q104: ** “Para cada vencedor de torneioapresente o seu nome, o nome do torneio eo numero de vezes que venceu essetorneio”.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exercıcios

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� q105: * “ Obtenha os jogadores que tiveramo maior intervalo de tempo entre vitorias(medido em anos), e qual foi esse intervalo”.

select numj, (max(ano) - min(ano))

from torneios

group by numj

having (max(ano) - min(ano))� � all

(select max(ano) - min(ano)

from torneios

group by numj)

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exercıcios

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� q106: * “De o nome e numero de torneiosganhos pelos jogadores que venceram omaior numero de torneios em quadrasrapidas (’D’, ’G’, ’T’)select nome, count(*)from torneios t, lista torneios lt, jogadores jwhere t.numt = lt.numt and j.numj = t.numjand quadra in (’D’, ’G’, ’T’)group by nomehaving count(*) � � all (select count(*)from torneios t, lista torneios ltwhere t.numt=lt.numt and quadra in(’D’,’G’,’T’) group by t.numj)

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exercıcios

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� q107: Repita o problema acima, para “obtero maior vencedor de quadras ‘lentas’ (saoas de ‘Saibro’)”.

� q108: ** “Obtenha uma lista dos jogadoresem ordem decrescente da seguinte metrica:numero de tıtulos em torneios de simples /intervalo em anos em que os tıtulos foramobtidos (se maior do que zero)”.

� q109: * “Considere a tabela D(p, f) doexercıcio 4.3.3, representando orelacionamento entre pais e filhos.Expresse em SQL as consultas daqueleexercıcio”.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exercıcios�

��

��

��

��

��

��

��

��

��

��

��

��

�� �

��

��

��

��

��

��

��

��

��

��

��

��

�� �

��

��

��

��

��

��

��

��

��

��

��

��

�� �

��

��

��

��

��

��

��

��

��

��

��

��

��

� Considere novamente a tabela D(p, f).Expresse em SQL as seguintes consultas(V. deve evitar a repeticao de linhas noresultado):

1. q110: * “Para cada casal (avo, avo) de alista dos seus netos sob a forma detriplas (avo, avo, neto)”

.

2. q111: ** “Para cada avo, obtenha onumero de seus netos”.

3. q112: ** “Obtenha o avo ou avos quepossuem o maior numero de netos, equal esse numero”.

� nao considere literalmente o sexo, pois este dado nao esta disponıvel na tabela D

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exercıcios

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

4. q113: ** “Obtenha pares de pessoas (p1,p2) onde p1 e sobrinho de p2”.

5. q114: ** “Para cada sobrinho, obtenha alista dos seus tios”.

6. q115: ** “Obtenha pares de pessoas quesao primos em

� �

grau”.

7. q116: ** “Obtenha pares de pessoas quesao meio-irmaos (isto e, apenas um dospais e comum a ambos)”.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exercıcios

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� q117: ** “Apresente uma lista em ordemalfabetica com os nomes dos torneios;acrescente uma numeracao aos itens dalista”.

A dificuldade desse problema reside nanumeracao; para obte-la vamos fazer umaauto-juncao de Lista Torneios , comparandoa coluna nome consigo mesma, seguida deum group by numa das colunas e contandoas ocorrencias do grupo, que contera anumeracao desejada:

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exercıcios

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

select count(*), t1.nome

from lista torneios t1, lista torneios t2

where t1.nomet � � t2.nomet

group by t1.nomet

order by count(*)

Para esse truque funcionar e preciso que acoluna de ordenacao nao tenha elementosrepetidos ( Exercıcio: por que? ).

Se quisessemos mostrar apenas os kprimeiros itens da lista, bastaria acrescentar aclausula: having count(*) � � k .

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exercıcios

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� q118: “* Apresente uma lista numeradacontendo em ordem decrescente os kmaiores numeros da ATP.”

select count(*), j1.natp

from jogadores j1, jogadores j2

where j1.natp � � j2.natp

group by j1.natp

having count(*) � � korder by j1.natp desc

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Juncao natural e juncao exterior

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Consulta sobre tabelas de Funcionarios eDependentes:

“Para cada funcionario de o seu nome e, se foro caso, de o nome, parentesco e data denascimento de cada um de seus dependentes”.

Juncao natural nao resolve: funcionarios semdependentes nao aparecem.

Solucao: “juncao exterior esquerda”entreFuncionarios e Dependentes (nessa ordem)

select nomef, nomed, parentesco, data nasc

from Funcionarios LEFT JOIN Dependentes

on Funcionarios.numf = Dependentes.numf© Célio Cardoso Guimarães

UNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL juncao exterior direita e full join

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

“juncao exterior direita”: (RIGHT JOIN)

acrescentados ao resultado da consulta osatributos selecionados de todas as linhas databela mencionada “a direita”de RIGHT JOIN.

FULL JOIN: obtem os atributos de ambas astabelas, independentemente de haver ou naoum “match”na coluna da juncao

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exemplo de full join

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

suponha permitido na tabela Dependenteslinhas com dependentes de ex-funcionarios:

queremos ter uma relacao completa dosfuncionarios e de todos os dependentes:

select nomef, nomed, parentesco, data nasc

from Funcionarios FULL JOIN Dependentes

on Funcionarios.numf = Dependentes.numf

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Visoes -Views

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Uma Visao e uma tabela virtual derivada deuma ou mais tabelas reais (ou tabelas base)ou de outra visao.

A instancia de uma visao e construıda sobdemanda:

� quando um comando select toma o nomeda visao como argumento,

� quando um comando de atualizacao toma onome da visao como argumento.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Propriedades adicionais de visoes

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� apenas definicao armazenada na BD

� conteudo sempre atual, pois refletequalquer mudanca nas tabelas base dasquais a visao e derivada

� pode ser definida a partir de outra visao

� definicao armazenada no catalogo dosistema (semelhante a uma macro delinguagem de programacao)

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Propriedades adicionais de visoes

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Uma visao comporta-se quase sempre comouma tabela real:

� pode ser consultada via comando select

� pode ser atualizada (com certas restricoes)

� consulta tem efeito semelhante ao daexpansao de macro.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Utilidades de Visoes�

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� Foco e personalizacao de dados:

� Privacidade: nem todos os dados de umfuncionario podem ser vistos sem restricao.

� Acesso restrito a subconjuntos de linhas oude colunas. Exemplo: coluna de salarios.

� Acesso restrito a apenas resumosestatısticos : por exemplo, somas, medias,de dados financeiros.

� Acesso restrito a subconjunto de outravisao.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Utilidades de Visoes

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� Elaboracao de consultas complexas:consulta requer varias subconsultasaninhadas: solucao fica mais clarautilizando visoes no lugar das subconsultas;

� solucao requer a utilizacao de uma ou maissubconsultas na clausula from, mas SGBDnao suporta: substitua as subconsultas poruma ou mais visoes!.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Definicao de uma Visao

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

q119:

create view jogadores ativos as

select *

from jogadores

where natp is not null

Obs: Esta visao possui as mesmas colunasque a tabela jogadores

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Consulta a uma Visao

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

q120: select nome, cid res

from jogadores ativos

where cid res = ’Monte Carlo’

Colunas da visao podem receber novosnomes:

q121: create view pessoas(njog, nreg)

select numj, natp

from jogadores ativos

Obs: visao criada a partir de outra visao

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exemplos

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Colunas de uma visao podem ser calculadas:

q122:

create view residentes (cidade, numero) as

select cid res, count(*)

from jogadores

group by cid res

Obs: nesse caso e mandatorio dar nome ascolunas

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Atualizacao / remocao de visoes

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

q123: delete from jogadores ativos

where natp = 281

drop view jogadores ativos

Obs: drop view remove apenas a definicao davisao, nao remove os dados da tabela base!

q124: update jogadores ativos

set natp = ’8477’

where numj = 224

q125:

insert into jogadores ativos

values (1999, ’Simoni’, 1971, ...)© Célio Cardoso Guimarães

UNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Processamento de consultas a Visao

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Feito pelo SGBD atraves de:

”merge”da consulta com a definicao da visao,de forma transparente ao usuario.

q126: select numj

from jogadores ativos

where cid res = ’Monte Carlo’

Apos o merge a consulta equivalente e:

select numj

from jogadores

where cid res = ’Monte Carlo’

and natp is not null© Célio Cardoso Guimarães

UNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Atualizacao de Visoes

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Regra basica:

Visoes sao tabelas virtuais:

� atualizacao de uma visao sempre incidesobre uma tabela base.

� Caso isto nao seja possıvel (devido aambiguidades, restricoes de integridade,etc) a atualizacao e rejeitada”.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Restricoes para atualizacao de Visoes

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Notacao: U = update, I = insert, D = delete

� Colunas calculadas nao podem seratualizadas: (U, I, D) proibidas. Exemplo:coluna do tipo ano n - ano p

� Visoes definidas com agregados de linhas(via group by ou funcoes agregado) naopodem ser atualizadas: (U, I, D) proibidas.

� Insercoes devem incluir as colunas not Nullda tabela base, ou colunas para as quaisum valor default nao tenha sido definido.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Restricoes para atualizacao de Visoes

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� Ilegal atualizar (U) visao formada a partir deuma juncao, a menos que colunas de umaso tabela base sejam atualizadas.

� Insercao de linha pode ser rejeitada caso atabela base tenha coluna com ındice tipoUnique Index.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Anomalias na atualizacao de visoes

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

comando insert: uma (ou mais) linhas saoinseridas numa tabela,

comando update: um ou mais atributos de umaou mais linhas sao alterados.

A execucao desses comandos sobre umavisao pode dar resultados inesperados, sendopossıvel:

� atualizar uma linha de uma visao e a linhaatualizada nao mais fazer parte da visao

� inserir uma linha numa visao e a linhainserida nao mais pertencer a visao!

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exemplos de Anomalias

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

q127: update jogadores ativos

set natp = NULL

where numj = 16

q128:

insert into jogadores ativos

values(999, ’Newcombe’, . . . , NULL)

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Solucao: clausula with Check Option

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

create view jogadores ativos as

select *

from jogadores

where natp is not null

with check option

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Tipos de dados para datas e horas

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� tipo de dados date: possui os campos year,month, day, nessa ordem,

� tipo time: campos hour, minute, second,nessa ordem.

� tipo timestamp: contem todos os 6 camposacima na ordem indicada.

Segundos podem ser fracionarios. Exemplos:seja q o numero de casas decimais apos avırgula:

time(q)

timestamp(q)© Célio Cardoso Guimarães

UNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exemplo

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

Create Table mytable

data nascimento date,

hora chegada time,

explosao super nova timestamp(6))

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL

Valores para campos date, time,timestamp

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� year numeric(4), valores: 0001 a 9999

� month numeric(2), valores: 01 a 12

� day numeric(2), valores: 01 a 31

� hour numeric(2), valores: 00 a 23

� minute numeric(2), valores: 00 a 59

� second numeric(2+q, q) valores: 00.000. . . ,61.999. . .

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Restricoes adicionais para valores

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

calendario Gregoriano: Abril tem 30 dias,Fevereiro 28 dias, ou 29 nos anos bissextos.

tipo de dados interval: para representarintervalos de tempo. Ha dois tipos de dadosinterval:

� year-month: qualquer combinacao doscampos year e month

� day-time: qualquer combinacao de camposconsecutivos dentre day, hour, minute,second.

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Sintaxe para definicao de intervalos

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

interval start [to end],

start e end: um dentre year, month, day, hour,minute, second. Exemplos:

interval year

interval year to month: um numero dado deanos e um numero dado de meses

interval day: intervalo dado em dias

interval hour(3) to minute: horas com 3 dıgitos,e minutos

interval second (2,3) segundos, com 2 casasdecimais antes da vırgula e 3 apos

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Funcoes intrınsecas para data e hora

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� CURRENT DATE devolve a data de “hoje”.

� CURRENT TIME hora correspondente ahora local corrente.

� CURRENT TIMESTAMP retorna aconcatenacao dos dois valores acima.

� EXTRACT (campo FROMexpressao-escalar)

Exemplo:

extract (year from date ’2002-12-11’)

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exemplos

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

q129: “Obtenha os nomes dos jogadores quese profissionalizaram com menos de 18 anos”.

select nome

from jogadores

where data p � data n + interval ’18’ year

q130: “Obtenha o nome e a idade (em anos)de cada jogador”.

select nome, extract(year from current date) -extract(year from data n)

from jogadores

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Exemplos (cont)

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

q131: “Obtenha os nomes dos jogadores quese profissionalizaram ha mais de 10 anos”.

select nome

from jogadores

where current date � data p + interval ’10’year

q132: “Para cada jogador obtenha o nome, anode profissionalizacao e idade por ocasiao dasua profissionalizacao”.

select nome, extract (year from data p),

extract (year from data p) - extract (year fromdata n)

from jogadores

© Célio Cardoso GuimarãesUNICAMP

Fundamentos debancos de dados

Célio Cardoso Guimarães

Modelagem, projeto e linguagem SQL Expressoes aritmeticas validas

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

��

� datetime + ou - interval = datetime

� datetime - datetime = interval

� interval + ou - interval = interval

� interval * ou / number = interval

A expressao e avaliada de acordo com ocalendario Gregoriano:

DATE ’1999-01-31’ + INTERVAL ’30’ day =DATE ’1999-03-02’

“vai-uns”sao propagados para os campos maissignificativos.

© Célio Cardoso GuimarãesUNICAMP