Banco de dados

1. Banco de dados Baseado no material do Professor Raul Paradeda

2. Revisão • Vimos na aula passada como criar um banco de dados e realizar alterações e restrições de integridade.

3. SQL • Crie um banco de dados com o seu nome. • Crie uma tabela chamada alunos. • Crie outra tabela chamada cursos. • Crie outra tabela chamada alu_cur. • Os atributos das duas primeiras tabelas você escolhe, apenas temos que ter chaves primárias em cada uma. • Na tabela alu_cur será apresentado apenas chaves estrangeiras das duas primeiras tabelas.

4. SQL - Inserção • Inserir de elementos (tuplas) em uma relação (tabela). • Sintaxe: INSERT INTO tabela [<lista atributos>] VALUES (<lista de valores atômicos>) • Exemplo: INSERT INTO alunos(nome_alu, sexo) VALUES (‘Johnny’, ‘m’); INSERT INTO alunos VALUES (1, ‘Allan’, 26, ‘1983-10-04’,‘m’); INSERT INTO alunos VALUES (2, ‘André’, 24, ‘1985-11-10’,‘m’); INSERT INTO alunos VALUES (3, ‘Renata’, 20, ‘1989-02-24’,‘f’); INSERT INTO alunos VALUES (4, ‘Lucas’, 26, ‘1983-01-07’,‘m’); Caso este parâmetro seja a chave primária, normalmente tem-se como auto-increment, não sendo necessário seu preenchimento.

5. SQL - Inserção • Podemos omitir uma ou mais colunas da relação destino. • Toda tupla inserida terá um valor nulo em cada posição de coluna omitida. • Sintaxe: INSERT INTO <tabela>(<atributos>) VALUES (<valores>); • Exemplo: INSERT INTO alunos(nome_alu, sexo) VALUES (‘Johnny’, ‘m’);

6. SQL - Atualização • O comando UPDATE modifica o valor de atributos de uma ou mais tuplas. • Sintaxe:UPDATE <tabela>SET <lista_atributos com atribuições de valores>[WHERE <condição>] • Omitir a cláusula WHERE implica em modificar todas as tuplas da relação.

7. SQL - Atualização • UPDATE alunos SET id_alu=10 WHERE id_alu=1; • UPDATE alunos SET id_alu=1 WHERE nome_alu='Allan' ; • UPDATE alunos SET data_alu=curdate() wheredata_alu='0000-00-00' ; • UPDATE alunos SET sexo='m' ;

8. SQL - Remoção • O comando DELETE remove tuplas de uma relação. • Sintaxe: DELETEFROM tabela [WHERE condição] • Omitir a cláusula WHERE implica em remover todas as tuplas da relação. • A relação permanece no BD como uma relação vazia.

9. SQL - Remoção • DELETE FROM alunos WHERE id_alu=1; • DELETE FROM alunos WHERE nome_alu=‘Allan’; • DELETE FROM alunos;

10. SQL - Seleção • A sintaxe básica do comando de seleção é: SELECT <lista atributos>FROM <lista de tabelas>WHERE <condição>Onde: • <lista atributos> é uma lista de nomes de atributos cujos valores são para ser recuperados pela consulta. • <lista de tabelas> é uma lista de nomes de relações requeridas para processar a consulta. • <condição> é uma expressão (Booleana) que identifica as tuplas a serem recuperadas pela consulta, se não houver será selecionada todas as tuplas da relação.

11. SQL - Seleção • É usada para listar os atributos desejados no resultado da consulta, exemplo: SELECT nome, cidade FROM Fornecedores; • Será listado apenas o nome e a cidade de todos os fornecedores. • O resultado de uma consulta SQL é também uma relação.

12. SQL - Seleção • A seleção permite duplicação na consulta, exemplo: SELECT cidade FROM Fornecedores; • Será listado a cidade de todos os fornecedores da relação, sendo os valores duplicados, caso houver.

13. SQL - Seleção • Para remover a duplicação é utilizado o parâmetro DISTINCT na consulta, exemplo: SELECT DISTINCTcidade FROM Fornecedores;

14. SQL - Seleção • A cláusula SELECT pode conter expressões aritméticas envolvendo as operações de adição (+), subtração (-), multiplicação (*) e divisão (/). SELECT nome, status*2 FROM Fornecedores;

15. SQL - Seleção • Para renomear atributos na seleção é utilizado o parâmetro AS, exemplo: SELECT id_fornecedor, status AS situacao FROM fornecedores;

16. SQL - Seleção • A cláusula FROM permite que se combine informações de duas relações, exemplo: Selectfornecedores.id_fornecedor, pecas.nome, fornecedores.cidade, pecas.cidade fromfornecedores, pecas; • As linhas são obtidas combinando-se cada linha da primeira tabela com todas as linhas da segunda tabela: • O esquema resultante é a concatenação dos esquemas das duas relações fornecidas como argumento.

17. SQL - Seleção  a b c x y a a b b c c x y x y x y

18. SQL - Seleção • A eliminação de tuplas incoerentes pode ser feita através do parâmetro WHERE. Select fornecedores.id_fornecedor, peças.nome, fornecedores.cidade, peças.cidade from fornecedores, peças where fornecedores.cidade=peças.cidade;

19. SQL - Seleção

20. SQL - Seleção Select fornecedores.id_fornecedor, peças.nome, fornecedores.cidade, peças.cidade from fornecedores, peças where fornecedores.cidade=“Natal”;

21. SQL - Seleção • Encontre todos os identificadores dos fornecedores localizados na cidade de Pelotas: selectid_fornecedor fromfornecedores where cidade=“Pelotas”; • Encontre todas as matriculas dos alunos com nome de João: Selectmatricula fromalunos wherenome_aluno=“João”;

22. SQL - Seleção • A cláusula WHERE usa os conectivos lógicos AND, OR e NOT: • Encontre o nome de todas as peças fornecidas por Jones para o Projeto Classificador selectdistinct peças.nome from fornecedores, peças, projeto, fornecedor_projeto_pecas where fornecedores.nome=“Jones” AND projeto.nome=“Classificador”;

23. SQL - Seleção • Encontre a matricula e o nome de todos os alunos com o nome de João ou Maria: select matricula, nome_aluno from alunos wherenome_aluno=“João” OR nome_aluno=“Maria”;

24. SQL - Seleção • Na condição é permitido usar expressões aritméticas de comparação (=, <>, <, <=, >=, >) • Encontre o nome de todos os fornecedores que fornecem mais de 300 peças para um só projeto: selectdistinct fornecedores.nome_fornecedor from fornecedores, fornecedor_proj_peças wherefornecedor_proj_peças.qde>300;

25. SQL - Seleção • Encontre todas as informações de todos os alunos do sexo masculino: select * from alunos where sexo=‘m’;

26. SQL - Seleção • Operador BETWEEN permite que um atributo seja comparado dentro de uma faixa especificada. • É permitido usar expressões aritméticas de comparação (=, <>, <, <=, >=, >) • Encontre o nome de todos os alunos que pagam mensalidades entre 300 a 700 reais • selectnome_aluno from alunos where mensalidade between300 AND 700;

27. SQL - Seleção • Operador LIKE permite a comparações em seqüências de caracteres. • Padrões são descritos usando dois caracteres especiais: • percentual (%) casa com qualquer conjunto de caracteres, exemplo: select * fromfornecedoreswherecidadeLIKE "P%";

28. SQL - Seleção • sublinhado (_). O caractere “_” casa com qualquer caractere select * fromfornecedoreswherecidadeLIKE "P_l_t_s"; select * fromfornecedoreswherecidade LIKE "P_l%";

29. SQL - Seleção • Operador IS NULL verificar se o atribulo é nulo. • Encontre os nomes de todos os projetos cuja cidade não tenha sido informada na relação select nome from projeto where cidade is null; • Pode-se usar IS NOT NULL select nome from projeto where cidade is notnull;

30. SQL - Seleção • Operador IN permite que um atributo seja comparado com um conjunto. select nome from peças where cor IN(‘Vermelha’, ‘Azul’); select * from fornecedores where cidade IN ("Pelotas","Natal");

31. SQL - Seleção • O operador IN provê um mecanismo para o aninhamento de subconsultas: selectmatricula fromalunos wherenomeIN ( selectnome fromalunos wherenome=“J%” );

32. SQL - Seleção • A função EXISTS também provê aninhamento de subconsultas. • É usada para checar se o resultado de uma consulta aninhada é vazia ou não. select * fromalunos where exists ( select * frompagamento wherepagamento=“ok” ); Se existir um aluno com o pagamento OK seráapresentadotodososalunos.

33. SQL - Seleção • Note que quando você está utilizando o operador EXISTS, não importa o que o comando SELECT interno irá buscar interessa apenas se ele retorna ou não linhas: • selectexists (select * from fornecedores where cidade in ("Pelotas","Natal"));

34. SQL - Seleção • A cláusula SELECT pode conter funções que operam sobre uma coleção de valores de uma coluna de uma relação e retorna um valor. • O SQL fornece 5 funções embutidas: • COUNT: número de tuplas ou valores. • SUM: soma os valores de uma coluna. • AVG: calcula a média dos valores de uma coluna. • MAX: identifica o maior valor de uma coluna. • MIN: identifica o menor valor de uma coluna. • Todas as funções, exceto count(*), ignoram as tuplas com valores nulos.

35. SQL - Seleção • Quantas relações aluno, curso, disciplina temos? • selectcount(*) fromaluno_curso_disciplina; • Quantos projetos ativos temos? • selectcount(distinctpr) fromfornecedor_projeto_pecas; • Quantas peças são fornecidas ao todo? • selectsum(qde) fromfornecedor_projeto_pecas;

36. SQL - Seleção • Em médias quantas peças são fornecidas por um determinado fornecedor em um certo projeto? • selectavg(qde) fromfornecedor_projeto_pecas; • Qual é a maior quantidade de peças fornecidas por um único fornecedor a um só projeto? • selectmax(qde) fromfornecedor_projeto_pecas;

37. SQL - Seleção • Qual é a menor quantidade de peças fornecidas por um único fornecedor a um só projeto? • selectmin(qde) fromfornecedor_projeto_pecas;

38. SQL - Seleção • ORDER BY é usada para ordenar as linhas selecionadas pelo comando de seleção. • Crescente: select cidade,status from fornecedores orderbystatus ASC; • Decrescente: select cidade,status from fornecedores orderbystatus DESC;

39. SQL - Seleção • GROUP BY é usada para agrupar linhas com base em valores de determinadas colunas: select f, p, sum(qde) as qde fromfornecedor_projeto_pecas groupby f, p; select cidade,status from fornecedores groupbystatus,cidade;

40. SQL - Seleção • A cláusula GROUP BY deve vir antes da ORDER BY e depois do WHERE select cidade,status from fornecedores groupbystatus,cidade; orderbycidade;

41. SQL - Atividade • Criar um banco de dados com seu nome e uma tabela chamada acessos. • Nesta tabela teremos quatro atributos: • id_acesso: chave primária inteira não nula auto incrementável; • dia_acesso: atributo inteiro não nulo; • mes_acesso: atributo inteiro não nulo; • ano_acesso: atributo inteiro não nulo;

42. SQL - Atividade • SQL da tabela: CREATETABLE acessos ( id_acessoINTNOTNULLAUTO_INCREMENT ,dia_acessoINTNOTNULL ,mes_acessoINTNOTNULL ,ano_acessoINTNOTNULL ,PRIMARYKEY (id_acesso) ) ;

43. SQL - Atividade • Realizar a inserção de 10 tuplas na tabela acessos com valores diferentes para mês_acesso e ano_acesso: • Ex: INSERT INTO acessos(dia_acesso,mes_acesso,ano_acesso) VALUES (15, 03,2009) ; INSERT INTO acessos(dia_acesso,mes_acesso,ano_acesso) VALUES (13, 02,2009) ; INSERT INTO acessos(dia_acesso,mes_acesso,ano_acesso) VALUES (15, 01,2009) ; INSERT INTO acessos(dia_acesso,mes_acesso,ano_acesso) VALUES (10, 02,2009) ;