Baixe o app para aproveitar ainda mais
Prévia do material em texto
Banco de Dados para Redes Aula 5 Professor Daniel Silos - 2ª edição - 2013 dsilos@live.estacio.br Estrutura Básica de Consultas • SQL é baseada em operações de conjuntos e operações relacionais com certas modificações e avanços • Uma consulta SQL típica tem o formato: : SELECT A1, A2, ..., An FROM r1, r2, ..., rm WHERE P; – Ai - atributo – Ri - relação – P - predicativo. • O resultado de uma consulta SQL é uma relação; Diagrama Lógico Movies Amostra do esquema do BD Movies • Grupo(idGrupo, razaoSocial, nomeFantasia, CNPJ) • Cinema(idCinema, idGrupo, nomeCinema) • Contato(idContato, idCinema, nomeContato, tipoLogradouro, logradouro, numero, complemento, UF, cidade, telefone, telefone2, celular, fax, email, homepage, facebook) • Sala (idSala, idCinema, nomeSala, capacidade, capacidadeDefFisico) • Sessao (idSessao, idSala, idFilme, dataHora) • Filme(idFilme, idDiretor, titulo, tituloOriginal, anoLancamento, paisOrigem, sinopse) • Diretor(idDiretor, nomeDiretor, nomeArtisticoDiretor, descricaoDiretor) A cláusula SELECT • Lista os atributos desejados no resultado de uma consulta – Corresponde ao operador de projeção em álgebra relacional • Exemplo: encontre o nome de todos os grupos da relação grupo: SELECT nomeGrupo FROM Grupo; • NOTA: nomes em SQL não são caso sensitivo (isto é, você pode utilizar letras em caixa alta ou caixa baixa). • Exemplo: nomegrupo ≡ NOMEGRUPO ≡ nomeGRUPO ≡ nomeGrupo. • SQL permite duplicatas de relações em resultados de consultas. • Para forçar a eliminação de duplicatas, insira a palavra DISTINCT após SELECT. • A palavra ALL especifica que as duplicatas não serão removidas. ALL ocorre por default. Exemplos do Estudo de Caso – Movies SELECT nomeSala FROM sala; ou SELECT ALL nomeSala FROM sala; SELECT DISTINCT nomeSala FROM sala; nomeSala Sala 1 Sala 1 Sala 1 Sala 1 Sala 2 Sala 1 Sala 1 Sala 2 Sala 1 Sala 1 Sala 1 Sala 1 Sala 3 Sala 1 . . . nomeSala Sala 1 Sala 2 Sala 3 Sala 4 Sala 5 • Um asterisco na cláusula SELECT denota “todos os atributos” SELECT * FROM grupo; • A cláusula SELECT pode conter expressões aritméticas envolvendo os operadores +, –, , e /, e operação de restrição em atributos de tuplas. • A consulta: SELECT nomeSala, capacidade 0.80 FROM sala; retornará uma relação com o nome da sala e a capacidade da sala reduzida em 20%. A cláusula WHERE • A cláusula WHERE especifica condições que o resultado precisa satisfazer. • Corresponde a seleção de predicativos da álgebra relacional. • Para encontrar todos os nomes das salas do cinema de identificador 2 com mais de 100 lugares de capacidade, basta realizar a seguinte consulta: SELECT nomeSala FROM sala WHERE idCinema = 2 AND capacidade > 100; • Resultados comparativos podem ser combinados utilizando-se conectores lógicos AND, OR e NOT. • Comparações podem ser aplicadas em resultados de expressões aritméticas. Exemplo do Estudo de caso – Movies SELECT titulo FROM filme WHERE anoLancamento < 2012 titulo Javatar Ponte entre núvens Franciscotein O filho de Drácula Gargalhadas no Espaço Frank, meu melhor amigo Tropa de Franks A Boneca Bianca O ataque das sombras Hienas Bruce Lee - o Legado Operador de comparação BETWEEN SELECT titulo FROM filme WHERE anoLancamento BETWEEN 2010 AND 2012 titulo Ciber Vampiro Frank, meu melhor amigo Tropa de Franks Kung Fu Panda - O Filme A Boneca Bianca O ataque das sombras Hienas Bruce Lee - o Legado O Homem Esquilo O Vampiro do Espaço A cláusula FROM • A cláusula FROM lista as relações envolvidas na consulta • Corresponde ao operador de produto cartesiano da álgebra relacional. • Encontre o produto cartesiano sala x cinema SELECT FROM sala, cinema O operador de renomeação • O SQL permite renomear relações e atributos utilizando a cláusula AS : velho_nome AS novo_nome SELECT razaoSocial AS GRUPO, nomeCinema AS CINEMA FROM grupo G, cinema C WHERE G.idGrupo = C.idGrupo GRUPO Seberiano Libeiro CINEMA CineMex Seberiano Libeiro CineMox Seberiano Libeiro Largo da enchada Seberiano Libeiro Xodeon Seberiano Libeiro . . . Thunder Max . . . Variáveis de tuplas • Variáveis de tuplas são definidas por meio da cláusula FROM e do uso da cláusula AS. • OBS: No Oracle, a cláusula AS precisa ser ignorada como variável de tupla. Ex: Encontre o titulo e o ano de lançamento de todos os filmes do diretor James Camera SELECT titulo, anoLancamento FROM filme f, diretor D WHERE f.idDiretor = D.idDiretor; No SQL ANSI, a palavra AS é opcional e pode ser omitida AnoLancamento AS ANO ≡ anoLancamento ANO Operações de STRING • SQL inlui um operador de strings para comparações entre strings. • O operador LIKE usa padrões que se utilizam de dois caracteres: – percentagem (%). O caractere % corresponde a qualquer substring. – underscore (_). O caractere _ corresponde a qualquer caractere. • Encontre o nome de todos os contatos cuja rua inclui a substring “ndo”. SELECT nomeContato FROM Contato WHERE lougradouro LIKE '%ndo%' SELECT nomeContato FROM contato WHERE tipoLougradouro LIKE ‘Avenida’; nomeContato Filipe de Souza Pereira Daniel Costa Francis Tereza Costa de Lima Juçara Castelo Branco Fabiana Peixoto Moraes Andréa dos Santos Castelo Branco Daniel Rodrigues Guilherme Moraes de Lima SELECT nomeContato, telefone FROM contato WHERE nomeContato LIKE '%ant%‘; nomeContato Eliane Costa Santos telefone 2126857456 Fernanda Santos 2136397071 Hellem dos Santos Costa 2135519568 Juliana Santos 2129432222 Andréa Santos Castelo Branco 2124391148 Hudson dos Santos Martins 2121406078 Fábio dos Santos Moraes 2133663092 Reinaldo Francis dos Santos 2126395805 João Santos 2133419636 SELECT nomeContato, telefone FROM contato WHERE nomeContato LIKE 'Fab%‘; nomeContato Fábio do Amaral da Silva telefone 2126018115 Fabiana Costa 2128381895 Fábio Peixoto da Silva 2129459682 Fabiana Silva 2120588572 Fabiana Peixoto Moraes 2132548263 Fábio dos Santos Moraes 2133663092 • SQL suporta uma variedade de operações de string tais como – concatenação (utilizando “||”) – conversão de caixa alta para caixa baixa (e vice versa) – Encontrar o tamanho de uma string, extração de substrings, etc. Ordenando a visualização de tuplas • Podemos especificar DESC para ordem decrescente ou ASC para ordem crescente, para cada atributo; ordem crescente é o default. – Exemplo: ORDER BY nome_cliente DESC SELECT nomeContato NOME, telefone TEL FROM contato ORDER BY NOME SELECT nomeContato NOME, telefone TEL FROM contato ORDER BY NOME DESC Tereza do Amaral 2120259444 Tereza Costa de Lima 2131862935 Tereza Castelo Branco do Amaral 2133456086 Reinaldo Silva Rodrigues 2124262675 . . . Andréa da Silva Souza 2132431000 Andréa dos Santos Castelo Branco 2124391148 Andreia Costa Francis 2134924022 Arnaldo Castelo Branco 2123458204 Arnaldo da Costa 2122600123 Bernardo Rodrigues Carvalho 2138293488 . . . Operador IN Exemplo: SELECT titulo, tituloOriginal, anoLancamento FROM filme WHERE anoLancamentoIN (2008, 2011); Ponte entre nuvens Ponte entre nuvens 2008 Franciscotein Franciscotein 2011 O filho de Drácula Dracula's Son 2011 Gargalhadas no Espaço Laugther in Space 2011 Tropa de Franks Tropa de Franks 2011 O ataque das sombras O ataque das sombras 2011 Bruce Lee - o Legado Bruce Lee - o Legado 2011 Exemplo do Estudo de caso – Movies SELECT titulo FROM filme WHERE anoLancamento < 2012 titulo Javatar Ponte entre núvens Franciscotein O filho de Drácula Gargalhadas no Espaço Frank, meu melhor amigo Tropa de Franks A Boneca Bianca O ataque das sombras Hienas Bruce Lee - o Legado • SQL inclui o operador de comparação BETWEEN • Exemplo: Encontre o número de empréstimo de todos empréstimos com quantia entre $90,000 E $100,000 (isto é, $90,000 e $100,000) SELECT numero_emp FROM emprestimo WHERE quantia BETWEEN 90000 AND 100000 SELECT titulo FROM filme WHERE anoLancamento BETWEEN 2010 AND 2012 titulo Ciber Vampiro Frank, meu melhor amigo Tropa de Franks Kung Fu Panda - O Filme A Boneca Bianca O ataque das sombras Hienas Bruce Lee - o Legado O Homem Esquilo O Vampiro do Espaço A cláusula FROM • A cláusula FROM lista as relações envolvidas na consulta • Corresponde ao operador de produto cartesiano da álgebra relacional. • Encontre o produto cartesiano tomador X empréstimo SELECT FROM tomador, emprestimo Encontre o nome, número do empréstimo e quantia de todos os clientes que tem um empréstimo na agência Perryridge. SELECT nome_cliente, tomador.numero_emp, quantia FROM tomador, emprestimo WHERE tomador.numero_emp = emprestimo.numero_emp AND nome_agencia = 'Perryridge' O operador de renomeação • O SQL permite renomear relações e atributos utilizando a cláusula AS : velho_nome AS novo_nome • Encontre o nome, número do empréstimo e a quantia do empréstimo de todos os clientes; renomeie a coluna de nome numero_emp como ID_emprestimo. SELECT nome_cliente, tomador.numero_emp AS ID_emprestimo, quantia FROM tomador, emprestimo WHERE tomador.numero_emp = emprestimo.numero_emp SELECT razaoSocial AS GRUPO, nomeCinema AS CINEMA FROM grupo, cinema WHERE idGrupo = cine_idGrupo GRUPO Seberiano Libeiro CINEMA CineMex Seberiano Libeiro CineMox Seberiano Libeiro Largo da enchada Seberiano Libeiro Xodeon Seberiano Libeiro . . . Thunder Max . . . Variáveis de tuplas • Variáveis de tuplas são definidas por meio da cláusula FROM e do uso da cláusula AS. • Encontre os nomes dos clientes e seus respectivos números de empréstimos de todos os clientes que tem um empréstimo em alguma agência. SELECT nome_cliente, T.numero_emp, S.quantia FROM tomador AS T, emprestimo AS S WHERE T.numero_emp = S.numero_emp Encontre todos os nomes de todas as agências que tem ativos maiores que os das agências localizadas em Brooklyn. SELECT distinct T.nome_agencia FROM agencia AS T, agencia AS S WHERE T.ativos > S.ativos AND S.cidade_agencia = 'Brooklyn' A palavra AS é opcional e pode ser omitida tomador AS T ≡ tomador T Operações de STRING • SQL inlui um operador de strings para comparações entre strings. • O operador LIKE usa padrões que se utilizam de dois caracteres: – percentagem (%). O caractere % corresponde a qualquer substring. – underscore (_). O caractere _ corresponde a qualquer caractere. • Encontre o nome de todos os clientes cuja rua inclui a substring “Main”. SELECT nome_cliente FROM cliente WHERE endereco_cliente LIKE '% Main%' SELECT nomeContato FROM contato WHERE tipoLougradouro LIKE 'Avenida' nomeContato Filipe de Souza Pereira Daniel Costa Francis Tereza Costa de Lima Juçara Castelo Branco Fabiana Peixoto Moraes Andréa dos Santos Castelo Branco Daniel Rodrigues Guilherme Moraes de Lima SELECT nomeContato, telefone FROM contato WHERE nomeContato LIKE '%ant%' nomeContato Eliane Costa Santos telefone 2126857456 Fernanda Santos 2136397071 Hellem dos Santos Costa 2135519568 Juliana Santos 2129432222 Andréa Santos Castelo Branco 2124391148 Hudson dos Santos Martins 2121406078 Fábio dos Santos Moraes 2133663092 Reinaldo Francis dos Santos 2126395805 João Santos 2133419636 SELECT nomeContato, telefone FROM contato WHERE nomeContato LIKE 'Fab%' nomeContato Fábio do Amaral da Silva telefone 2126018115 Fabiana Costa 2128381895 Fábio Peixoto da Silva 2129459682 Fabiana Silva 2120588572 Fabiana Peixoto Moraes 2132548263 Fábio dos Santos Moraes 2133663092 • SQL suporta uma variedade de operações de string tais como – concatenação (utilizando “||”) – conversão de caixa alta para caixa baixa (e vice versa) – Encontrar o tamanho de uma string, extração de substrings, etc. Ordenando a visualização de tuplas • Listar em ordem alfabética pelo nome de todos os clientes que tem um na agência Perryridge SELECT DISTINCT nome_cliente FROM tomador, emprestimo WHERE tomador numero_emp = emprestimo.numero_emp AND nome_agencia = 'Perryridge' ORDER BY nome_cliente • Podemos especificar DESC para ordem decrescente ou ASC para ordem crescente, para cada atributo; ordem crescente é o padrão. – Exemplo: ORDER BY nome_cliente DESC SELECT nomeContato NOME, telefone TEL FROM contato ORDER BY NOME SELECT nomeContato NOME, telefone TEL FROM contato ORDER BY NOME DESC Tereza do Amaral 2120259444 Tereza Costa de Lima 2131862935 Tereza Castelo Branco do Amaral 2133456086 Reinaldo Silva Rodrigues 2124262675 . . . Andréa da Silva Souza 2132431000 Andréa dos Santos Castelo Branco 2124391148 Andreia Costa Francis 2134924022 Arnaldo Castelo Branco 2123458204 Arnaldo da Costa 2122600123 Bernardo Rodrigues Carvalho 2138293488 . . . A partir deste momento, o aluno está apto a resolver a lista de exercícios 3. Bibliografia – SILBERSCHATZ, Abraham; KORTH, Henry F; SUDARSHAN, S. A. Sistema de banco de dados. 5ª ed. Rio de Janeiro: Campus, 2006 – ELMASRI, R.; NAVATHE, S., Sistemas de Banco de Dados. Pearson Education do Brasil, 8ª.Ed 2011.
Compartilhar