Linguagens de Consulta

1. Linguagens de Consulta Álvaro Vinícius de Souza Coêlho alvaro.degas@terra.com.br

2. Linguagens de Consulta para SGBDs Relacionais • Linguagem de Consulta é uma denominação genérica para linguagens que permitem DML (Data manipulation Language) e DDL (Data Definition Language). • No tocante ao respaldo comercial e acadêmico, três linguagens se destacam: Quel, QBE e, a mais importante de todas, SQL (ame-a ou deixe-a).

3. Ling. de Consulta p/ SGBDR • Existem outras, com importância não desprezível • Ling. Proprietárias de bancos de dados em Main Frame • Xbase, desenvolvida para sistemas de dados tipo DBF • Aproveitada em alguns SGBDs (TSGBD)

4. Ling. de Consulta p/ SGBDR • QBE • Sigla de Query By Example • Centro de Pesquisa T. J. Watson (IBM), início dos anos 70 • Sintaxe Bidimensional • consulta => exemplo da projeção desejada

5. Ling. de Consulta p/ SGBDR • Tabelas-Esqueleto • São um esquema das tabelas de Banco de Dados

6. Agencia Agencia_Nome Ativos Agencia_Cidade Cliente Cliente_Nome Rua Cliente_Cidade Emprestimo Agencia_Nome Emp_Numero Cliente_Nome Quantia Deposito Agencia_Nome Conta_Numero Cliente_Nome Saldo Ling. de Consulta p/ SGBDR

7. Deposito Agencia_Nome Conta_Numero Cliente_Nome Saldo Cruzeiro P._x Ling. de Consulta p/ SGBDR • Uma consulta • “Mostrar todos os clientes com conta em Cruzeiro”

8. Ling. de Consulta p/ SGBDR • _x é uma variável tipo tupla (que armazena conjuntos – relações – de dados). O _ é obrigatório • P. é um comando que indica que aquela variável deve ser impressa • Pode-se omitir a variável caso haja apenas uma na posição

9. Deposito Agencia_Nome Conta_Numero Cliente_Nome Saldo Cruzeiro P.ALL._x Ling. de Consulta p/ SGBDR • A QBE elimina resultados duplicados por padrão. Para mostrá-los, deve-se usar

10. Deposito Agencia_Nome Conta_Numero Cliente_Nome Saldo P. >1200 Ling. de Consulta p/ SGBDR • As consultas podem ter comparações aritméticas

11. Agencia Agencia_Nome Ativos Agencia_Cidade P. ~ Ilhéus Ling. de Consulta p/ SGBDR • Pode-se usar =, <, <=, >, >= e ~. Para a comparação de variáveis distintas há uma estrutura adicional mostrada adiante • Consulta: “Mostrar todas as agências fora de Ilhéus”

12. Deposito Agencia_Nome Conta_Numero Cliente_Nome Saldo Cruzeiro P._x Salobrinho _x Ling. de Consulta p/ SGBDR • As variáveis servem para ligar resultados parciais de pesquisa • “Listar clientes com contas em Cruzeiro e Salobrinho”

13. Deposito Agencia_Nome Conta_Numero Cliente_Nome Saldo Cruzeiro P._x Salobrinho P._y Ling. de Consulta p/ SGBDR • As variáveis tupla vão sendo “podadas” a cada condição satisfeita, de forma que só a intersecção é mantida • Modificar a consulta para: “Mostrar clientes que tenham conta em Cruzeiro ou Salobrinho (ou ambas)”

14. Deposito Agencia_Nome Conta_Numero Cliente_Nome Saldo _x José _x P. Ling. de Consulta p/ SGBDR • O uso de duas variáveis força a linguagem a fazer um filtro em cada, mantendo os conjuntos de dados intactos. As duplicações são removidas por padrão. • Se a consulta depende de um valor dado: “Mostrar clientes que tenham conta onde José também tem”

15. Emprestimo Agencia_Nome Emp_Numero Cliente_Nome Quantia Cruzeiro _x Cliente Cliente_Nome Rua Cliente_Cidade P._x P._y Ling. de Consulta p/ SGBDR • Consultas podem ser feitas em mais de uma tabela: “Qual o nome e a cidade dos clientes com conta em Cruzeiro?”

16. Deposito Agencia_Nome Conta_Numero Cliente_Nome Saldo Cruzeiro P._x Emprestimo Agencia_Nome Emp_Numero Cliente_Nome Quantia Cruzeiro _x Ling. de Consulta p/ SGBDR • Ou pode-se trazer e mostrar dados de duas tabelas: “Mostrar os clientes que tenham uma conta e um empréstimo na agência Cruzeiro”

17. Ling. de Consulta p/ SGBDR • A consulta inversa “tem conta em Cruzeiro mas não tem empréstimo lá” muda apenas por ganhar a negação sob o nome da tabela empréstimo • O sinal de negação sob o nome da tabela tem o sentido, para a QBE de “não inclua as seleções dali”. Se o sinal, ao contrário, aparece sob uma coluna, tem o sentido de ≠.

18. Deposito Agencia_Nome Conta_Numero Cliente_Nome Saldo _x _y ~_x P._y Ling. de Consulta p/ SGBDR • “Mostrar os clientes que tem conta em duas agências distintas”

19. Conditions _y ~ José Ling. de Consulta p/ SGBDR • A condition box • Serve para explicitar condições de seleção que envolvam mais de uma variável ou coluna • “Não mostrar, na consulta anterior, o cliente José”

20. Deposito Agencia_Nome Conta_Numero Cliente_Nome Saldo P._x _y Conditions _y < 1500 _y > 1300 Ling. de Consulta p/ SGBDR • Mostrar as contas com saldo entre 1300 e 1500

21. Agencia Conditions Agencia_Nome Ativos Agencia_Cidade _x > _y P. _x _y Ilhéus Ling. de Consulta p/ SGBDR • “Mostrar as agencias com ativos maiores que qualquer agência de Ilhéus”

22. Ling. de Consulta p/ SGBDR • Na caixa de condição podem aparecer também os operadores lógicos and e or (o ~também pode assumir as vezes de not). Ex: _x = (>= 1300 and <= 2000 and ~1500) • Pode usar o operador or para fazer uma conjunção de valores. Ex: _x = (Ilhéus or Itabuna)

23. Deposito Agencia_Nome Conta_Numero Cliente_Nome Saldo Cruzeiro P.AO._x Deposito Agencia_Nome Conta_Numero Cliente_Nome Saldo Cruzeiro P.AO(1)._x P.DO(2)._y Ling. de Consulta p/ SGBDR • Ordenando resultados: P.AO e P.DO (Ascending Order e Descending Order) como nos dois exemplos distintos: Ou

24. Ling. de Consulta p/ SGBDR • A última mostra os clientes e os saldos das contas em Cruzeiro classificados pelo nome em ordem crescente, e cada cliente pelo saldo em ordem decrescente

25. Ling. de Consulta p/ SGBDR • Agregações • A QBE inclui operações como AVG, MAX, MIN, SUM E CNT que operam sobre agregados de dados • É necessário pós-fixá-las com ALL pois o QBE omite duplicações

26. Deposito Agencia_Nome Conta_Numero Cliente_Nome Saldo José P.SUM.ALL Ling. de Consulta p/ SGBDR

27. Deposito Agencia_Nome Conta_Numero Cliente_Nome Saldo P.G. P.AVG.ALL Ling. de Consulta p/ SGBDR • Agrupamentos • O Operador G agrupa os resultados de acordo com o valor da coluna

28. Conditions AVG.ALL >= 1200 Ling. de Consulta p/ SGBDR • A agregação pode vir dentro da condition Box

29. Deposito Agencia_Nome Conta_Numero Cliente_Nome Saldo D. Sandra Ling. de Consulta p/ SGBDR • DML em QBE • Exclusão: O comando D.

30. Agencia Agencia_Nome Ativos Agencia_Cidade I. Capital Itabuna Ling. de Consulta p/ SGBDR • Inclusão: O comando I . • Isso só é possível se a coluna Ativos não for obrigatória. • Caso contrário, deve-se valorá-la.

31. Deposito Agencia_Nome Conta_Numero Cliente_Nome Saldo I. Cruzeiro _x _y 200 Emprestimo Agencia_Nome Emp_Numero Cliente_Nome Quantia Cruzeiro _x _y Ling. de Consulta p/ SGBDR • Inclusão a partir de consultas • “Criar um depósito de R$200 para clientes de Cruzeiro, com número de conta igual ao número do empréstimo”

32. Agencia Agencia_Nome Ativos Agencia_Cidade Cruzeiro U.10000000 Ling. de Consulta p/ SGBDR • Atualização: O comando U. • Muda o valor dos ativos da agência Cruzeiro para R$10000000

33. Deposito Agencia_Nome Conta_Numero Cliente_Nome Saldo U. _x*1.05 _x Ling. de Consulta p/ SGBDR • Pode-se usar como base o próprio valor da coluna

34. Ling. de Consulta p/ SGBDR • Quel • Feita para o SGBDR Ingres • Desenvolvida em Berkeley (como o Unix) pela atual Ingres Inc (que pertence à CA-Computer Associates) • Declarativa – Pode ser embutida

35. Ling. de Consulta p/ SGBDR • Estrutura Básica Range of t1 is r1 Range of t2 is r2 ... range of tm is rm retrieve (ti1.aj1, ti2.aj2, …, tin.ajn) where P

36. Ling. de Consulta p/ SGBDR • Consultas simples: “Mostrar o nome dos clientes com conta em Cruzeiro” Range of d is deposito Retrieve (t.cliente_nome) Where t.agencia_nome = “Cruzeiro”

37. Ling. de Consulta p/ SGBDR • Ao contrário de QBE, Quel não elimina duplicações • A nova consulta elimina as duplicações Range of d is deposito Retrieve unique (t.cliente_nome) Where t.agencia_nome = “Cruzeiro”

38. Ling. de Consulta p/ SGBDR • Pode-se fazer consultas usando mais de uma tabela • “Mostrar o nome e a cidade de todos os clientes com um empréstimo em Cruzeiro”

39. Ling. de Consulta p/ SGBDR • Fica assim: Range of c is cliente Range of e is emprestimo Retrieve unique (c.cliente_nome, c.cliente_cidade) Where e.agencia_nome = “Cruzeiro” and e.cliente_nome = c.cliente_nome

40. Ling. de Consulta p/ SGBDR • Usa-se os operadores lógicos and, or e not • “Encontre o nome dos clientes com conta e empréstimo em Cruzeiro”

41. Ling. de Consulta p/ SGBDR • Fica: Range of e is empréstimo Range os d is deposito Retrieve unique (d.cliente_nome) Where d.agencia_nome = “Cruzeiro” and e.agencia_nome = “Cruzeiro” and d.cliente_nome = e.cliente_nome

42. Ling. de Consulta p/ SGBDR • Pode-se usar mais de uma variável (do tipo tupla) como range da mesma tabela: • “Mostrar os clientes que tem conta em agências onde José também tem”

43. Ling. de Consulta p/ SGBDR • Fica: Range of d1 is deposito Range of d2 is deposito Retrieve unique (d1.cliente_nome) Where d1.agencia_nome = d2.agencia_nome and d2.cliente_nome = “José”

44. Ling. de Consulta p/ SGBDR • Range of... é desnecessário quando não há duas projeções da mesma tabela • Mas se escreve muito no Predicado

45. Ling. de Consulta p/ SGBDR • Funções agregadas • Quel inclui as funções count, sum, avg, max, min, countu, sumu, avgu e any • As terminadas em u removem duplicações • O agrupamento é definido como parte da expressão agregada

46. Ling. de Consulta p/ SGBDR • Funções agregadas • Função (t.a) • Função (t.a where P) • Função (t.a by s.b1, s.b2, ..., s.bn where P)

47. Ling. de Consulta p/ SGBDR • Consulta: “Mostrar a media dos saldos das contas da agência Cruzeiro” Range of t is deposito Retrieve avg(t.saldo where t.agencia_nome = Cruzeiro)

48. Ling. de Consulta p/ SGBDR • A função agregada pode aparecer na cláusula where • “Mostrar os clientes com saldo acima da média”

49. Ling. de Consulta p/ SGBDR • Fica: Range of d1 is deposito Range of d2 is deposito Retrieve unique(d1.cliente_nome) Where d1.saldo > avg(d2.saldo)

50. Ling. de Consulta p/ SGBDR • Modificação: Mostrar os clientes com saldo acima da média das contas na agência Cruzeiro • A cláusula where mudaria para Where d1.saldo > avg(d2.saldo where d2.agencia_nome = “Cruzeiro”)