1 / 93

Exercícios SQL

Exercícios SQL. Marilde Santos. 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. SQL Structured Query Language. Juliano Brito da Justa Neves

karis
Download Presentation

Exercícios SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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.

More Related