1. Exercícios SQL Marilde Santos

2. Sumário • Revisão SQL • Conjunto de slides disponibilizado por Juliano Neves. • Restrições, Datas e mais SQL • Exercícios Laboratório de Banco de Dados • Controle acadêmico • Controle bancário • Dicas

3. SQLStructured Query Language Juliano Brito da Justa Neves PESCD – Programa de Estágio Supervisionado de Capacitação Docente 2003

4. SQL • Linguagem padrão para SGBDs relacionais • Motivo pelo grande sucesso dos SGBDs relacionais • Linguagem de Definição de Dados (DDL) • Linguagem de Manipulação de Dados (DML)

5. Esquema • Agrupar tabelas e outros elementos (constraints, views...) que pertencem a mesma aplicação de banco de dados CREATE SCHEMA <esquema> AUTHORIZATION <autorização> ;

6. Esquema • Esquemas no Oracle • Criar várias tabelas e views e dar permissões em uma única transação. CREATE SCHEMA AUTHORIZATION <esquema> [create_table, create_view, grant]+ ;

7. Esquema • Esquemas no Oracle • O CREATE SCHEMA não cria realmente um esquema. O esquema é automaticamente criado quando um usuário é criado. • Vamos utilizar um dos usuários previamente criados.

8. Oracle • Conexão com o SGBD Oracle • Putty • SSH: falcon.comp.ufscar.br • Login: Pessoal de cada um • No prompt do linux: • sqlplus • Login: compX  X número de 1 a 20 • Senha: igual ao login

9. Estudo de Caso EQUIPE (#Codigo_Equipe,Nome_Equipe,Cidade,Estado) JOGADOR (#Codigo_Jogador,Nome_Jogador,Posição_Jog,#Codigo_Equipe) PARTIDA (#Codigo_Partida,Cidade,Estado,Nome_Juiz,Data) JOGA (#Codigo_Jogador,#Codigo_Partida,Numero_Gols)

10. Tabelas CREATE TABLE [esquema.]tabela ( coluna1 tipo_dado [DEFAULT expr] [constraint_coluna], ... colunaN tipo_dado [DEFAULT expr] [constraint_coluna], [constraint_tabela] );

11. Tipos de Dados • Integer, Float, Real... • Char (n) • Varchar2 (n) • Clob • Long • Blob • Raw e Long Raw • Number (p,e) • Date • Timestamp • Interval Year (p) to month • Interval Day (dp) to second (sp)

12. Constraints • Grupo 1 • NOT NULL • Unique • Grupo 2 • Check • Primary key • Foreign key CONSTRAINT nome tipo expr constraint cod Primary Key constraint fcod Foreign Key references tabela(coluna) constraint chk Check (uf in (‘SP’, ‘MG’))

13. Exemplo EQUIPE (#Codigo_Equipe,Nome_Equipe,Cidade,Estado) CREATE TABLE equipe ( codigo_equipe INTEGER constraint equipe_pk Primary Key, nome_equipe Varchar2(20) NOT NULL, cidade Varchar2(10), estado Varchar2(10) );

14. Exemplo JOGADOR (#Codigo_Jogador,Nome_Jogador,Posição_Jog,#Codigo_Equipe) Codigo_jogador  INTEGER Nome_jogador  Varchar2(20) Posicao_jog  Varchar2(15) Codigo_Time  INTEGER

15. Exemplo JOGADOR (#Codigo_Jogador,Nome_Jogador,Posição_Jog,Codigo_equipe) CREATE TABLE jogador ( codigo_jogador INTEGER constraint jogador_pk Primary Key, nome_jogador Varchar2(20) NOT NULL, posicao_jog Varchar2(15), codigo_equipe INTEGER, constraint jogador_fk Foreign Key (codigo_equipe) references equipe(codigo_equipe) );

16. Exemplo PARTIDA (#Codigo_Partida,Cidade,Estado,Nome_Juiz,Data) Codigo_partida  INTEGER cidade  Varchar2(10) estado  Varchar2(10) nome_juiz  Varchar2(20) data  date

17. Exemplo PARTIDA (#Codigo_Partida,Cidade,Estado,Nome_Juiz,Data) CREATE TABLE partida ( codigo_partida INTEGER constraint partida_pk Primary Key, cidade Varchar2(10) NOT NULL, estado Varchar2(10), nome_juiz Varchar2(20) NOT NULL, data Date );

18. Mais Constraints! • ON DELETE • SET NULL • CASCADE • SET DEFAULT • ON UPDATE • SET NULL • CASCADE • SET DEFAULT

19. Exemplo JOGA (#Codigo_Jogador,#Codigo_Partida,Numero_Gols) codigo_jogador  Integer Se apagar jogador, apaga. codigo_partida  Integer Se apagar partida, apaga. numero_gols  Integer

20. Exemplo JOGA (#Codigo_Jogador,#Codigo_Partida,Numero_Gols) CREATE TABLE joga ( codigo_jogador integer, constraint joga1_fk foreign key (codigo_jogador) references jogador(codigo_jogador) on delete cascade, codigo_partida integer, constraint joga2_fk foreign key (codigo_partida) references partida(codigo_partida) on delete cascade, numero_gols integer );

21. Alteração de tabelas • Criar, alterar ou eliminar colunas • Renomear a tabela • Criar ou eliminar constraints • Habilitar ou desabilitar constraints

22. Alteração de Tabelas ALTER TABLE [esquema.]tabela [add coluna tipo_dado [DEFAULT expr] [constraint_coluna] ] [modify coluna tipo_dado [DEFAULT expr] [constraint_coluna] ] [add constraint_coluna/constraint_tabela] [drop constraint_coluna/constraint_tabela [cascade]] [enable constraint_coluna/constraint_tabela] [disable constraint_coluna/constraint_tabela] ;

23. Exemplo • Alterar a tabela joga para que o valor default de Numero_gols seja 0; ALTER TABLE joga MODIFY numero_gols INTEGER DEFAULT ‘0’; • Adicionar uma Primary Key em joga ALTER TABLE joga ADD constraint joga_pk Primary Key (codigo_jogador, codigo_partida);

24. Apagando Tabelas DROP TABLE [esquema.]tabela [CASDADE CONSTRAINTS]; • CASCADE CONSTRAINTS elimina todas as restrições presentes em outras tabelas que façam referência à tabela que está sendo eliminada.

25. Índices • Criar CREATE [UNIQUE] INDEX índice ON tabela (coluna [ASC | DESC]); • UNIQUE  Índice não aceita valores repetidos. • É criado um índice UNIQUE sempre que uma Primary Key é criada. • Apagando o índice DROP INDEX índice;

26. Exemplo select index_name from user_indexes; CREATE UNIQUE INDEX my_index ON partida (nome_juiz); select index_name from user_indexes;

27. Linguagem de manipulação de dados • Inserindo dados INSERT INTO [esquema.]tabela (coluna1, coluna2, ... colunaN) VALUES (valor1, valor2 ... valorN);

28. Inserindo dados • Se for inserir na mesma ordem da definição da tabela: INSERT INTO [esquema.]tabela VALUES (valor1, ... valorN); • Inserção em determinados campos INSERT INTO [esquema.]tabela (colunaX, colunaY) VALUES (valorX, valorY);

29. s S s Exemplo • Criar o time Saravá Saci Soccer INSERT INTO equipe (codigo_equipe,nome_equipe,cidade,estado) VALUES (1,’SSS’,’São Carlos’, ‘São Paulo’); Opção INSERT INTO equipe VALUES (1, ‘SSS’, ‘São Carlos’, ‘São Paulo’);

30. Exemplo • Inserir time “Tiradentes”, de Brasília, DF INSERT INTO equipe VALUES (2,’Tiradentes’,’Brasilia’,’DF’); • Inserir time “Enc97FC” INSERT INTO equipe (codigo_equipe, nome_equipe) VALUES (3,’Enc97FC’);

31. Inserindo dados • Cuidados com as restrições (constraints)! INSERT INTO JOGADOR VALUES (1,’Juliano’,’goleiro’,4); INSERT INTO equipe (codigo_equipe, nome_equipe) VALUES (4, ‘Selecao’); INSERT INTO JOGADOR VALUES (1,’Juliano’,’goleiro’,4);

32. Atualizando dados UPDATE tabela SET coluna = valor [, coluna = valor...] [WHERE condição]; • Exemplo: Juliano mudou para atacante! UPDATE jogador SET posicao_jog = ‘atacante’ where codigo_jogador = 1;

33. Apagando dados DELETE [FROM] tabela [WHERE condição]; • Exemplo: Apagar Enc97FC DELETE FROM equipe WHERE codigo_equipe = 3;

34. Apagando dados • Cuidados com as restrições (constraints)! DELETE FROM equipe WHERE codigo_equipe = 4; FALHA! DELETE jogador; DELETE FROM equipe WHERE codigo_equipe = 4; OK!

35. Consultando dados: SELECT • Até o final da aula! • Álgebra relacional • Endereço do script http://www.dc.ufscar.br/~juliano • Execução do script sqlplus> @ sql.txt

36. Selecionando dados • Forma básica: SELECT <lista de atributos> FROM <lista de tabelas> WHERE <condição>

37. Selecionando dados SELECT [DISTINCT] {*, colunas [AS alias], expressões, funções..} FROM {tabelas [AS alias]} [WHERE condição] [GROUP BY colunas] [HAVING condição] [ORDER BY colunas [ASC | DESC]];

38. Álgebra Relacional • π(*) jogador SELECT DISTINCT * FROM jogador; • σ(posicao_jog = atacante) jogador SELECT * FROM jogador WHERE posicao_jog = ‘Atacante’;

39. Álgebra Relacional • πnome_jogador(σ(posicao_jog = Atacante)Jogador) SELECT DISTINCT nome_jogador FROM jogador where posicao_jog = ‘Atacante’; • ρNome(πnome_jogador(σ(posicao_jog = Atacante)Jogador)) SELECT DISTINCT nome_jogador AS Nome FROM jogador where posicao_jog = ‘Atacante’;

40. União • R1  πnome_jogador(σ(posicao_jog = Atacante)Jogador) • R2  πnome_jogador(σ(codigo_equipe = 2)Jogador) • Resultado  R1 U R2 SELECT DISTINCT nome_jogador FROM jogador WHERE posicao_jog = ‘Atacante’ UNION SELECT DISTINCT nome_jogador FROM jogador WHERE codigo_equipe = 2;

41. Interseção • R1  πnome_jogador(σ(posicao_jog = Atacante)Jogador) • R2  πnome_jogador(σ(codigo_equipe = 2)Jogador) • Resultado  R1 ∩ R2 SELECT DISTINCT nome_jogador FROM jogador WHERE posicao_jog = ‘Atacante’ INTERSECT SELECT DISTINCT nome_jogador FROM jogador WHERE codigo_equipe = 2;

42. Subtração • R1  πnome_jogador(σ(posicao_jog = Atacante)Jogador) • R2  πnome_jogador(σ(codigo_equipe = 2)Jogador) • Resultado  R1 – R2 SELECT DISTINCT nome_jogador FROM jogador WHERE posicao_jog = ‘Atacante’ MINUS SELECT DISTINCT nome_jogador FROM jogador WHERE codigo_equipe = 2;

43. Produto Cartesiano • R1  πnome_jogador(π(*) Jogador) • R2  πnome_equipe(π(*) Equipe) • Resultado  R1 × R2 SELECT DISTINCT nome_jogador, nome_equipe FROM jogador, equipe;

44. Join (Equijoin) • R1  π(*) Jogador • R2  π(*) Equipe • Resultado  R1 |x|equipe.codigo_equipe = jogador.codigo_equipe R2 SELECT * FROM jogador, equipe WHERE equipe.codigo_equipe = jogador.codigo_equipe;

45. Theta Join • R1  π(*) Jogador • R2  π(*) Equipe • Resultado  R1 |x|equipe.codigo_equipe > jogador.codigo_equipe R2 SELECT * FROM jogador, equipe WHERE equipe.codigo_equipe > jogador.codigo_jogador;

46. Natural Join • R1  πnome_jogador, codigo_equipe(π(*) Jogador) • R2  πnome_equipe, codigo_equipe(π(*) Equipe) • Resultado  πnome_jogador,nome_equipe( R1 *codigo_equipe R2) SELECT DISTINCT nome_jogador, nome_equipe, codigo_equipe FROM jogador, equipe WHERE equipe.codigo_equipe = jogador.codigo_equipe;

47. Outer Join ]x| • R1  πnome_jogador, codigo_equipe(π(*) Jogador) • R2  πnome_equipe, codigo_equipe(π(*) Equipe) • Resultado  πnome_jogador,nome_equipe( R1 ]x|equipe.codigo_equipe = jogador.codigo_equipe R2) SELECT nome_jogador, nome_equipe FROM jogador, equipe WHERE equipe.codigo_equipe (+) = jogador.codigo_jogador;

48. Divisão • Todos os jogadores que fizeram gol em partidas onde Fabio Simplicio fez gol Fabio  σnome_jogador = ‘Fabio Simplicio’(Jogador) F_Par  πcodigo_partida(Joga |x|joga.codigo_jogador = Fabio.codigo_jogador Fabio) Jog_Par  πcodigo_jogador,codigo_partida(Joga) Result  Jog_Par F_Par

49. Divisão SELECT * FROM jogador WHERE nome_jogador = ‘Fabio Simplicio’; SELECT DISTINCT codigo_partida FROM joga WHERE codigo_jogador = 51; SELECT DISTINCT codigo_jogador, codigo_partida FROM joga; SELECT DISTINCT codigo_jogador FROM joga WHERE (codigo_partida = 1 OR codigo_partida = 5) AND (codigo_jogador != 51);

50. Outras consultas • Cobrimos todos os operadores da álgebra relacional. • Veremos agora outros tipos de consultas comuns em SGBDs relacionais.