Baixe o app para aproveitar ainda mais
Prévia do material em texto
Banco de Dados Msc. Arquimedes Sidney Lima de Medeiros SQL Structured Query Language Conteúdo SGBD Oracle SQL Exercício Bibliografia Utilizada Msc. Arquimedes S. L. de Medeiros 2 SGBD Oracle É um sistema de gerenciamento de banco de dados relacional (RDBMS, Relational Database Manager System) com Extensões de objeto relacional. Um sistema RDBMS tem por finalidade o gerenciamento de dados que devem ser normalizados e convertidos em tabelas de 2 dimensões. Msc. Arquimedes S. L. de Medeiros 3 SGBD Oracle Promove o gerenciamento de grandes bases de dados em um ambiente multiusuário. Proporciona que vários usuários possam acessar um mesmo item de dados de forma concorrente. Provê alto desempenho bem como fornece soluções que previnem o acesso não autorizado. Viabiliza uma solução completa de backup, restauração e recuperação de falhas. Msc. Arquimedes S. L. de Medeiros 4 SGBD Oracle Arquivos básicos de armazenamento Control file; Data Files; Archived Redo Logs Files; Parameter File; Password File; Backup Files; Trace Files; Alert Log Files. Msc. Arquimedes S. L. de Medeiros 5 SGBD Oracle Tablespaces e Datafiles Logicamente, um banco de dados Oracle é formado por um conjunto de tablespaces. As tabelas em um banco de dados Oracle são armazenadas na tablespaces. A tablespace permite o perfeito controle da localização física das tabelas. Uma tablespace é armazenada fisicamente em datafiles, sendo que o mapeamento tabespace <-> datafiles é uma tarefa do DBA. Msc. Arquimedes S. L. de Medeiros 6 SGBD Oracle Tablespaces e Datafiles Msc. Arquimedes S. L. de Medeiros 7 Tablespace SYSTEM Tablespace DADOS Tablespace RBS SYS01.ORA 500 MB DAD01.ORA 1 TB DAD01.ORA 2 TB RBS.ORA 300 MB SGBD Oracle Instância do Banco de Dados É um conjunto formado pela estrutura de memória e processos que tornam possível a manipulação do banco de dados. Estrutura de memória SGA (System Global Area) Buffer cache; Dictionary cache; Redo log buffer; Shared pool; Java pool. PGA (Program Global Area) Msc. Arquimedes S. L. de Medeiros 8 SGBD Oracle Processos Database writer process (DBWn); Log writer process (LGWR); Checkpoint process (CKPT); System monitor process (SMON); Process monitor process (PMON); Recoverer process (RECO); Job queue coordinator (CJQ0); Job slave processes (Jnnn); Archiver processes (ARCn); Queue monitor processes (QMNn). Msc. Arquimedes S. L. de Medeiros 9 SGBD Oracle Schemas Oracle São usuários do banco de dados, ou seja, os proprietários dos dados nele armazenados; Em cada esquema pode ser armazenado tabelas, índices, visões, stored procedures, enfim, qualquer objeto armazenado no Oracle; Os usuários administradores do banco de dados são SYS e SYSTEM. Msc. Arquimedes S. L. de Medeiros 10 SGBD Oracle Tipos de Dados CHAR(TAM) Literal de armazenamento fixo. O Seu tamanho pode variar de 1 a 2000. VARCHAR2(TAM) Literal de armazenamento variável. O tamanho máximo pode variar de 1 a 4000. NUMBER(P,S) Armazenamento de ponto flutuante entre 1 x 10- 130 e 9.99999 x 10125. p = indica a quantidade total de dígitos, incluindo as casas decimais - s: número de dígitos decimais. DATE Armazenamento de datas. Msc. Arquimedes S. L. de Medeiros 11 SGBD Oracle Tipos de Dados BINARY_INTEGER Valor inteiro que varia -2-31 a 231. BOOLEAN Valores lógicos ( TRUE, FALSE ). Long Literais que armazena texto de até 4 gb. Long Raw Tipo utilizado para armazenar sequencias de bytes de até 2 GB. LOB Utiliza armazenamento de dados não estruturados, tais como imagens, sons e vídeos. O limite é de 4 GB. PLS_INTEGER -2147483647 e 2147483647. Msc. Arquimedes S. L. de Medeiros 12 SGBD Oracle Tipos de Dados BLOB Tipo semelhante ao LOB, sendo que este permite a conversão de conjunto de caracteres utilizados e oferece total suporte transacional. BFILE Permite acesso de leitura a arquivos armazenados no sistema de arquivo do Sistema Operacional. Msc. Arquimedes S. L. de Medeiros 13 SGBD Oracle Tipos de Dados BLOB Tipo semelhante ao LOB, sendo que este permite a conversão de conjunto de caracteres utilizados e oferece total suporte transacional. BFILE Permite acesso de leitura a arquivos armazenados no sistema de arquivo do Sistema Operacional. BINARY_FLOAT Representação de 32 bits. BINARY_DOUBLE Representação de 64 bits. Msc. Arquimedes S. L. de Medeiros 14 SGBD Oracle Operadores Aritméticos Adição ( + ) e Subtração ( - ); Multiplicação ( * ) e Divisão ( / ). Concatenação ( || ) Comparação Básica Igualdade ( = ) e Diferença ( <>, != ); Maior ( > ) e Maior ou igual ( >= ); Menor ( < ) e Menor ou Igual ( <= ); [NOT] BETWEEN valor_inicial AND valor_final; IS [NOT] NULL. Msc. Arquimedes S. L. de Medeiros 15 SGBD Oracle Operadores IN Verifica se o conteúdo de uma variável está presente em uma dada coleção; cod_curso IN ( 2, 5, 7, 9, 10 ). LIKE Avalia se o conteúdo de uma variável segue o padrão definido por uma literal; Os caracteres de substituição são: %, para uma cadeia de caracteres; _, para um caractere. nome like 'Ana%'. Msc. Arquimedes S. L. de Medeiros 16 SGBD Oracle Operadores SOME e ANY Deve ser utilizado com um dos seguintes operadores: =, >, <, >= e <=; Verifica se o conteúdo de uma variável quando aplicado um dos operadores básicos de comparação é verdadeiro para algum dos elementos da lista; media = some( 6, 7, 9 ). ALL Com sintaxe semelhante ao SOME e ANY, o resultado será verdadeiro apenas se o conteúdo da variável quando aplicado o operador básico de comparação for verdadeiro para todos os elementos da lista; media > all( 6, 7, 9 ). Msc. Arquimedes S. L. de Medeiros 17 SGBD Oracle Operadores lógicos NOT, AND, OR. Precedência Operadores multiplicativos ( *, / ); Operadores aditivos ( +, - ); Operadores de comparação ( =, >, is null, ... ); NOT; AND; OR; Os parênteses podem ser utilizados para alterar a precedência de avaliação das expressões. Msc. Arquimedes S. L. de Medeiros 18 SGBD Oracle Principais Funções Numéricas ABS( n ) Obtém o módulo de um número. Ceil( n ) Retorna o maior inteiro maior ou igual a n. EXP( n ) Retorna o valor de e (2.71828183) elevado a n. Floor( n ) Retorna o menor inteiro menor ou igual a n. LN( n ) Retorna o logaritmo natural de n. Msc. Arquimedes S. L. de Medeiros 19 SGBD Oracle Principais Funções Numéricas LOG( b, n ) Retorna o logaritmo de n na base b. MOD( n, d ) Obtém o resto da divisão de n por b. POWER( n, m ) Recupera o valor de n elevado a m. ROUND( n, d ) Arredonda o valor de n na casa decimal d; O valor default é zero; O valor de d pode ser negativo. Msc. Arquimedes S. L. de Medeiros 20 SGBD Oracle Principais Funções Numéricas SQRT( n ) Obtém a raiz quadrada de n. TRUNC( n, m ) Trunca o valor de na na casa decimal definida por m; O valor default para m é zero; O valor de m pode ser negativo. SIN( n ) Seno de n. COS( n ) Cosseno de n. TAN( n ) Tangente de n. Msc. Arquimedes S. L. de Medeiros 21 SGBD Oracle Principais Funções Literais ASCII( c ) Recupera o código ascii de um determinado caractere. CHR( n ) Mapeia o caractere ascii referenteao número n. CONCAT( c1, c2 ) Retorna o resultado da concatenação de c1 com c2. INITCAP( c ) Retorna a literal c com todas as iniciais de palavras em letras maiúsculas e o restante em letras minúsculas. Msc. Arquimedes S. L. de Medeiros 22 SGBD Oracle Principais Funções Literais INSTR( c1, c2, n, m ) Recupera a posição, considerando que a primeira letra é a posição 1, onde c2 aparece em c1; O parâmetro n é opcional e indica a posição de início da busca em c1; O parâmetro m determina qual a ocorrência de c2 em c1 deve ser considerada; Os valores default de n e m são 1; A expressão instr( nome, 'es', 4, 2 ) indica que deve ser retornada a posição da segunda ocorrência da literal 'es' a partir da quarta posição do conteúdo da variável nome. Msc. Arquimedes S. L. de Medeiros 23 SGBD Oracle Principais Funções Literais LOWER( c ) e UPPER( c ) Converte todas as letras da literal c para minúscula (lower) ou maiúsculas (upper). LPAD( c1, tam, c2 ) e RPAD( c1, tam, c2 ) Retorna a literal c1 preenchida à esquerda (lpad) ou à direita (rpad) com a literal c2 até o tamanho definido pelo parâmetro tam; O parâmetro c2 é opcional, e quando não informado é considerado o espaço em branco. LTRIM( c1, c2 ) e RTRIM( c1, c2 ) Retorna a literal c1 retirando as ocorrência de c2 à esquerda (ltrim) ou à direita (rtrim); c2 é opcional sendo que o default é espaço em branco. Msc. Arquimedes S. L. de Medeiros 24 SGBD Oracle Principais Funções Literais LENGTH( c1 ) Retorna o tamanho da literal c1. REPLACE( c1, c2, c3 ) Retorna o resultado da substituição de todas as ocorrências de c2 por c3 em c1. SUBSTR( c1, m, n ) Recupera uma substring de c1 iniciada na posição m com tamanho n; n é opcional, e quando omitido é considerada toda a literal a partir da posição m. Msc. Arquimedes S. L. de Medeiros 25 SGBD Oracle Principais Funções com Data ADD_MONTHS( d, n ) Retorna uma data que é resultado da adição de n meses à data d. MONTHS_BETWEEN( d1, d2 ) Obtém o número de meses existente entre as datas d1 e d2. SYSDATE Retorna a data atual do sistema, incluindo hora, minuto e segundo. TRUNC( d ) Retorna a data d sem as informações de hora, minuto e segundos. Msc. Arquimedes S. L. de Medeiros 26 SGBD Oracle Principais Funções de Conversões TO_DATE( lit, fmt ) Converte a literal lit para o tipo data utilizando o formato especificado por fmt. TO_CHAR( d, fmt ) Converte uma data para uma literal com o formato definido por fmt. TO_CHAR( n, fmt ) Converte um número para uma literal com o formato definido por fmt. TO_NUMBER ( l, fmt ) Converte uma literal para um valor numérico utilizando como formato de conversão o parâmetro fmt. Msc. Arquimedes S. L. de Medeiros 27 SGBD Oracle Principais Funções de Conversões Se o padrão de conversão não for informado, o Oracle utilizará o formato default da seção do usuário. Principais padrões para número: D – indica o separador de milhar de um número; G – indica o separador decimal de um numero; 0 – define que um número deve ser retornado; 9 – indica que um número pode ser retornado; $ - define que o símbolo deve ser retornado; L – define o símbolo local para moeda. Exemplos: Msc. Arquimedes S. L. de Medeiros 28 select to_char(1210.73, '9G999D00') formato_numerico from dual; select to_char(1210.73, 'L9G999D00') formato_moeda from dual; select to_char(21, '000099') zero_esquerda from dual; SGBD Oracle Principais Funções de Conversões Principais padrões para data; DD – Dia com dois dígitos; MM – Mês com dois dígitos; YYYY – Ano com 4 dígitos; HH24 – Hora com 2 dígitos (0..23); MI – Minutos em 2 dígitos; SS – Segundos com 2 dígitos; D – Dia da semana (1..7); DAY – Descrição do dia; MONTH – Descrição do mês; MON – Três letras iniciais do mês; Ex.: 'DD/MM/YYYY HH24:MI:SS. Msc. Arquimedes S. L. de Medeiros 29 SGBD Oracle Álgebra com Datas A adição ou subtração de números inteiros a uma data ocasionam, respectivamente, o acréscimo ou decréscimos de dias à data; A subtração de duas datas retorna o número de dias existentes entre elas; Datas não podem ser multiplicadas ou divididas; Adicionar 1 minuto a uma data equivale a adição de 1/1440 a ela; Adicionar 1 segundo a uma data equivale a adição de 1/86400 a ela. Msc. Arquimedes S. L. de Medeiros 30 SGBD Oracle Exemplo de Padrões com Data Uma data formatada com ano em quatro dígitos, horas, minutos e segundos: ‘DD/MM/YYYY HH24:MI:SS’; 15/10/2012 13:22:30. Formatação para recuperar a descrição do mês, a palavra “de”, e o ano em quatro dígitos: ‘MONTH “ de “ YYYY’; ABRIL DE 2012. Formato para obter o número do dia de uma data (1-Domingo, 2-Segunda, etc ...) ‘D’ Msc. Arquimedes S. L. de Medeiros 31 SGBD Oracle Outras Funções GREATEST( arg1, arg2, arg3, ..., argn ) Recupera o maior valor existente entre os argumentos. LEAST( arg1, arg2, arg3, ..., argn ) Recupera o menor valor existente entre os argumentos. NVL( arg1, arg2 ) Testa o valor de arg1 de modo que se o mesmo for nulo, a função retorna o valor de arg2, caso contrário, retorna o próprio valor de arg1. USER Recupera o nome do usuário corrente. Msc. Arquimedes S. L. de Medeiros 32 SQL Padrão de linguagem de consulta relacional aceito pelos comitês ANSI (www.ansi.org) e ISO (www.iso.org). Histórico Criada no início da década de 70 pela IBM: Edgar Frank Codd. “A Relational Model of Data for Large Shared Data Banks”. Msc. Arquimedes S. L. de Medeiros 33 SQL Histórico ANSI e ISO em conjunto lançam: SQL–86 ou SQL1; SQL–92 ou SQL2; SQL:1999 ou SQL3; SQL:2003 e SQL:2006 (XML); SQL:2008 (SGBDOO). Características Linguagem declarativa; DML e DDL. Msc. Arquimedes S. L. de Medeiros 34 SQL DML – Data Manipulation Language SELECT INSERT UPDATE DELETE DDL – Data Definition Language CREATE TABLE CREATE INDEX ALTER TABLE DROP TABLE GRANT e REVOKE Msc. Arquimedes S. L. de Medeiros 35 DML – DATA MANIPULATION LANGUAGE SELECT Msc. Arquimedes S. L. de Medeiros 36 Estrutura básica do comando SELECT SELECT <cláusula de projeção> FROM <cláusula de seleção> WHERE <condições da consulta> GROUP BY <critérios de agrupamento> HAVING <condições do agrupamento> ORDER BY <critério de ordenação>; Msc. Arquimedes S. L. de Medeiros 37 Cláusula de Projeção Lista de elementos separados por vírgula; Caso o desejado seja listar todas as colunas deve-se utilizar o asterisco (*); Os elementos de projeção podem ser colunas, constantes e expressão; Cada elemento projetado pode ter um apelido associado; Os apelidos são definidos de forma explícita na cláusula AS, ou através da simples colocação do apelido no elemento projetado; Se o apelido contiver espaços ou caracteres especiais, deve estar entre aspas duplas (“”). Msc. Arquimedes S. L. de Medeiros 38 Exemplo SQL Lista para tabela dual alguns exemplos de projeção de constantes, colunas e expressões. select 'Exemplo de Constante' Constante, dummy, 'Data atual: '|| to_char( sysdate, 'DD/MM/YYYY HH24:MI:SS' ) "Expressão" from dual; CONSTANTE DUMMY Expressão -------------------- ----- ------------------------------- Exemplo de Constante X Data atual: 15/04/2012 21:30:32 1 rows selected Msc. Arquimedes S. L. de Medeiros 39 Cláusula FROM Determina a tabela ondeos dados serão obtidos para a resolução da consulta; Juntamente com a cláusula SELECT, a cláusula FROM define a especificação mínima para um comando de seleção; Na sua formação mais simples, o comando de seleção implementa apenas a operação de projeção, recuperando assim todas as linhas da tabela. Msc. Arquimedes S. L. de Medeiros 40 Exemplo SQL Listar a matrícula, o nome e a data de nascimento de todos os alunos. Os atributos listados devem seguir a nomenclatura matrícula, nome e data, respectivamente. select mat_alu matricula, nom_alu nome, dat_nasc data from alunos; MATRICULA NOME DATA ----------- -------------------------------------------------- ----------- 107648 LUCAS FERNANDES SANTOS - TESTE 17/04/1991 107061 FELLIPE HELENA MARQUES SANTOS - TESTE 11/09/1990 107064 RAVELLY JOSEFINA MENEZES SOUSA - TESTE 25/02/1990 151280 ANDRESS SANTANA - TESTE 17/05/1991 105333 CAMILLA FELIX PASSOS 18/09/1990 104933 ELISSON DANDARA MENEZES - TESTE 16/07/1990 120120 VICTOR DARLAN PIRES - TESTE 08/04/1991 Msc. Arquimedes S. L. de Medeiros 41 Cláusula WHERE Define uma expressão condicional cujo resultado da avaliação irá determinar quais linhas da tabela que serão recuperadas; Implementa a operação de seleção da álgebra relacional; A condição é avaliada para cada linha da tabela: Caso seja satisfeita a linha é recuperada no resultado final; Caso contrário a linha é descartada da composição do resultado. Msc. Arquimedes S. L. de Medeiros 42 Exemplo SQL Obter a matrícula, nome e MGP de todos os alunos do curso 4 que possuem MGP maior que sete. select mat_alu as “Matrícula”, nom_alu “Nome”, mgp “Média Ponderada” from alunos where cod_curso = 4 and mgp > 7; Matrícula Nome Média Ponderada ----------- ----------------------------------------- --------------- 151280 ANDRESS SANTANA - TESTE 7,85 120120 VICTOR DARLAN PIRES - TESTE 7,22 149505 LUCAS BISPO SANTANA - TESTE 8,11 137932 LYSIANNE BERNARDO OLIVEIRA SOUZA - TESTE 7,99 127549 LEONARDO CERQUEIRA JUNIOR 7,58 107654 ALAN ANDRADE MILITAO VIRGINIA - TESTE 7,22 106691 DENYSE ELIANE SANTOS - TESTE 7,03 Msc. Arquimedes S. L. de Medeiros 43 Exemplos SQL Observe as sentenças SQL. select a.mat_alu as “Matricula”, a.nom_alu as “Nome”, a.cod_curso as “Curso” from alunos a where cod_curso = 44 or cod_curso = 13; select a.mat_alu as “Matricula”, a.nom_alu as “Nome”, a.cod_curso as “Curso” from alunos a where cod_curso in ( 44, 13 ); select a.mat_alu as “Matricula”, a.nom_alu as “Nome”, a.cod_curso as “Curso” from alunos a where cod_curso = 10 and mgp >= 5; Msc. Arquimedes S. L. de Medeiros 44 Exemplos SQL Observe as sentenças SQL. select a.mat_alu as “Matricula”, a.nom_alu as “Nome”, a.cod_curso as “Curso” from alunos a where mgp between 3 and 5; select a.mat_alu as “Matricula”, a.nom_alu as “Nome”, a.cod_curso as “Curso” from alunos a where mgp not between 3 and 5; select a.mat_alu as “Matricula”, a.nom_alu as “Nome”, a.cod_curso as “Curso” from alunos a where cod_curso not in (10,8); Msc. Arquimedes S. L. de Medeiros 45 Considerações sobre SELECT O identificador DISTINCT pode ser utilizado na projeção para retirar linhas repetidas do resultado. Na especificação da condição de seleção podemos utilizar funções composição de expressões que criem filtros sobre os dados recuperados. Os comandos SQL não são sensitivos a letras maiúsculas e minúsculas, mas o conteúdo das colunas são. Msc. Arquimedes S. L. de Medeiros 46 Exemplo SQL Obter os cursos dos alunos que possuem ‘Oliveira’ ou ‘Santos’ em seu sobrenome. select distinct cod_curso from alunos where upper( nom_alu ) like '%OLIVEIRA%' or upper( nom_alu ) like '%SANTOS%'; COD_CURSO --------- 123 13 44 26 4 35 52 131 103 Msc. Arquimedes S. L. de Medeiros 47 Cláusula ORDER BY Deve ser utilizada para classificar o resultado de uma consulta sob um determinado critério. Um resultado pode ser ordenado por qualquer coluna ou elemento projetado. A especificação da ordem é feita por uma lista de elementos (colunas ou expressões) separados por vírgula. Msc. Arquimedes S. L. de Medeiros 48 Cláusula ORDER BY Alguns SGBDs permitem que a cláusula de ordenação possa ser indicada por números que representam os elementos projetos no SELECT. O número 1 representa o primeiro elemento projeto, o 2 o segundo, o 3 o terceiro, e o número n o n-ésimo. É possível obter a ordem descendente para um dado elemento de ordenação especificando o identificador DESC após o elemento. Msc. Arquimedes S. L. de Medeiros 49 Exemplo SQL Obter o código do curso, matrícula, nome e MGP de todos os alunos nascidos a partir de 1990 que possuem MGP maior que sete. O resultado deve ser ordenado ascendentemente pelo código do curso, e para um mesmo curso, a ordem dos nomes deve ser decrescente. select cod_curso, mat_alu, nom_alu, mgp from alunos where dat_nasc >= '01/01/1990‘ and mgp > 7 order by cod_curso, nom_alu desc; COD_CURSO MAT_ALU NOM_ALU MGP --------- ----------- ------------------------------------------ ------ 4 149502 YGO AQUINO SANTANA - TESTE 7,35 4 130541 YASMIN CARDOSO SAMPAIO VILANOVA - TESTE 7,89 4 131214 YARIN COSTA SANTOS VINICIUS - TESTE 8,42 4 136710 VINICIUS CRUZ OLIVEIRA - TESTE 7,59 13 139255 YVES OLIVEIRA SILVA - TESTE 8,21 13 117085 YVES FIGUEIREDO ROCHA - TESTE 8,77 13 125838 WESLHEY CHAGAS MARIA SANTIAGO - TESTE 8,47 Msc. Arquimedes S. L. de Medeiros 50 Exercício 1) Listar todos os alunos do curso 44 exibindo matrícula, nome e data de nascimento do mais velho ao mais novo. 2) Listar todos os alunos dos cursos 44 e 13 que possuem média superior a 7,00, exibindo código do curso, matrícula e nome do aluno, ordenados por código do curso e nome. 3) Listar todos alunos que possuem o nome “Maria” na formação de seu nome exibindo matrícula e nome, ordenados pelo nome. 4) Listar todas as matrículas, sem repetir, dos alunos que estão matriculados no ano 2011, semestre 3 na disciplina 273 ordenados pela matrícula. 5) Liste todos os professores que começam com o nome “Maria” ordenados pela matricula em ordem decrescente, ou seja, da maior para a menor. Msc. Arquimedes S. L. de Medeiros 51 Exercício 6) Listar todas as disciplinas que tem na composição do nome a palavra “Computadores” ordenadas pelo código e o nome. 7) Liste o código da disciplina, as vagas e o código do professor que a ministrou no ano de 2011, semestre 3, ordenada pelo código da disciplina. 8) Liste o ano, o semestre, a matrícula e a situação dos alunos que cursaram a disciplina 3810, ordenando pela matrícula e sistuação. Msc. Arquimedes S. L. de Medeiros 52 Produto Cartesiano A cláusula FROM admite o uso de uma lista de tabelas separadas por vírgula. Se nenhuma condição de junção for definida na cláusula WHERE, o resultado da seleção é o produto cartesiano entre as tabelas. Para resolver conflitos de nome de colunas, é permitido o uso de apelidos para as tabelas. Os apelidos são definidospor identificadores colocados após os nomes. Msc. Arquimedes S. L. de Medeiros 53 Exemplo SQL Listar a combinação de todos os cursos com todos os alunos. select mat_alu, nom_alu, nom_curso from alunos, cursos order by nom_alu, nom_curso; MAT_ALU NOM_ALU NOM_CURSO ---------- -------------------------------------------------- ---------------------------------------- 156100 AARON MARINHO SILVA - TESTE Arquitetura e Urbanismo - Manha 156100 AARON MARINHO SILVA - TESTE Ciencia da Computacao - Tarde/Noite 156100 AARON MARINHO SILVA - TESTE Direito Diurno 156100 AARON MARINHO SILVA - TESTE Direito Noturno 156100 AARON MARINHO SILVA - TESTE Enfermagem - Tarde/Noite 156100 AARON MARINHO SILVA - TESTE Informatica e Aplicacoes Web - Manha 156100 AARON MARINHO SILVA - TESTE Odontologia - Integral 156100 AARON MARINHO SILVA - TESTE Petroleo e Gas - Noite 156100 AARON MARINHO SILVA - TESTE Sistemas de Informacao - Noite 156100 AARON MARINHO SILVA - TESTE Sistemas Para Internet - Manha 156100 AARON MARINHO SILVA - TESTE Sistemas Para Internet - Noite 133934 ABDA CARVALHO LOURENCO SOARES - TESTE Arquitetura e Urbanismo - Manha 133934 ABDA CARVALHO LOURENCO SOARES - TESTE Ciencia da Computacao - Tarde/Noite 133934 ABDA CARVALHO LOURENCO SOARES - TESTE Direito Diurno Msc. Arquimedes S. L. de Medeiros 54 Junção Natural Uma forma comum e genérica para definição de junção natural é utilizar operadores de seleção e produto cartesiano combinadas. A junção natural é realizada especificando na cláusula WHERE a condição de junção. A condição de junção é implementada no SQL igualando a chave estrangeira de uma tabela com respectiva chave primária de outra. Msc. Arquimedes S. L. de Medeiros 55 Junção Natural Em junções que envolvam mais de duas tabelas, deve existir pelo menos uma condição de junção para cada uma das tabelas. As várias condições de junção devem formar uma “corrente de junções” entre as tabelas da cláusula FROM. Se umas das junções não for especificada, é realizado um produto cartesiano entre as tabelas não ligadas. Msc. Arquimedes S. L. de Medeiros 56 Junção Natural SELECT T1.COL_01, T2.COL_02, T3.COL_03, T4.COL_04 FROM TABELA_01 T1, TABELA_02 T2, TABELA_03 T3, TABELA_04 T4 WHERE T1.COL_01 = T2.COLD_01 AND T2.COL_02 = T3.COL_02 AND T3.COL_03 = T4.COLD_03; Condição de Junção 1-2 Condição de Junção 2-3 Condição de Junção 3-4 Msc. Arquimedes S. L. de Medeiros 57 Exemplo SQL Listar a matrícula, o nome, o código do curso e nome do curso de todos os alunos. O resultado deve ser ordenado por nome do curso e nome do aluno. select c.cod_curso, c.nom_curso, a.mat_alu, a.nom_alu from alunos a, cursos c where a.cod_curso = c.cod_curso order by nom_curso, nom_alu; Msc. Arquimedes S. L. de Medeiros 58 Exemplo SQL COD_CURSO NOM_CURSO MAT_ALU NOM_ALU --------- ----------------------------------- ---------- ------------------------------------- 131 Arquitetura e Urbanismo - Manha 132159 ALYNE BARROS MARQUES SANTOS - TESTE 131 Arquitetura e Urbanismo - Manha 136019 ANA DORIA SANTOS - TESTE 131 Arquitetura e Urbanismo - Manha 131837 ANGELA CABRAL JUNTA - TESTE 26 Ciencia da Computacao - Tarde/Noite 134683 ACACIO ALMEIDA MOREIRA - TESTE 26 Ciencia da Computacao - Tarde/Noite 135133 AILSSON BOMFIM RODRIGUES - TESTE 26 Ciencia da Computacao - Tarde/Noite 131502 ALAN GUERRA ROCHA - TESTE 13 Direito Diurno 80345 ABDIAS NASCIMENTO SILVA - TESTE 13 Direito Diurno 111687 ABDON EDUARDO SANTANA - TESTE 13 Direito Diurno 102837 ACHILLES CUNHA SILVA - TESTE 4 Direito Noturno 126928 ALINE ASSIS PEREIRA - TESTE 4 Direito Noturno 98255 ALINE BARRETO NERES 4 Direito Noturno 143206 ALINE BRAZ LOPES 44 Enfermagem - Tarde/Noite 126389 ANDRESA OLIVEIRA TELES - TESTE 44 Enfermagem - Tarde/Noite 143204 ANDRESSA GOIS PEREIRA 44 Enfermagem - Tarde/Noite 117739 ANDREZA BRITO SALES - TESTE 44 Enfermagem - Tarde/Noite 112450 ANDREZA CARDOSO KOTOVICZ MOTA - TESTE 44 Enfermagem - Tarde/Noite 110296 ANDREZZA BARRETO MENDONCA - TESTE 44 Enfermagem - Tarde/Noite 108915 ANDREZZA BATISTA SANTOS - TESTE 44 Enfermagem - Tarde/Noite 126412 ANDRIELLE GONZAGA 44 Enfermagem - Tarde/Noite 117171 ANNA APARECIDA MATOS SANTOS - TESTE 44 Enfermagem - Tarde/Noite 135992 ANNA GUEDES REIS - TESTE 44 Enfermagem - Tarde/Noite 125120 ANNE CARVALHO LIMA - TESTE 44 Enfermagem - Tarde/Noite 51858 ANNE CARVALHO MARIA SILVA - TESTE 44 Enfermagem - Tarde/Noite 114759 ANNE CASTRO SANTOS - TESTE 44 Enfermagem - Tarde/Noite 117632 ANNY ELOUIZY SANTOS - TESTE Msc. Arquimedes S. L. de Medeiros 59 Exemplo SQL Listar o nome da disciplina, matrícula, nome dos alunos aprovados em 2008/3 na disciplina 3810 com média acima de sete. select d.nom_disc, a.mat_alu, a.nom_alu from periodos_letivos l, historicos h, disciplinas d, alunos a where l.ano = h.ano and l.semestre = h.semestre and h.cod_disc = d.cod_disc and h.mat_alu = a.mat_alu and d.cod_disc = '3810' and h.ano = 2008 and h.semestre = 3 and h.situacao = 'AP' and h.media > 7 order by a.nom_alu; Msc. Arquimedes S. L. de Medeiros 60 Exemplo SQL NOM_DISC MAT_ALU NOM_ALU ----------------------------- ---------- -------------------------------------- DIREITO CIVIL II (OBRIGACOES) 108720 AGNES LOUIZE SANTOS - TESTE DIREITO CIVIL II (OBRIGACOES) 90599 ALEX AZEVEDO DANTAS DIREITO CIVIL II (OBRIGACOES) 91180 ANA FATIMA SEVERINO - TESTE DIREITO CIVIL II (OBRIGACOES) 71087 AYALA ANDRADE ILZE SANTOS - TESTE DIREITO CIVIL II (OBRIGACOES) 90556 CLISSIA CRUZ REIS TELES - TESTE DIREITO CIVIL II (OBRIGACOES) 92240 DANIELLE COSTA SOUZA - TESTE DIREITO CIVIL II (OBRIGACOES) 83757 JULIANA ALMEIDA MARIA SANTOS - TESTE DIREITO CIVIL II (OBRIGACOES) 98692 KARLLA DANTAS NETO - TESTE DIREITO CIVIL II (OBRIGACOES) 90776 LEILANE COELHO ROCHA - TESTE DIREITO CIVIL II (OBRIGACOES) 90889 LOUISE ANDRADE MOZARTT SANTANA - TESTE Msc. Arquimedes S. L. de Medeiros 61 Cláusula JOIN Outra forma de implementara junção natural é utilizar o operador JOIN (INNER JOIN) na cláusula FROM para indicar tabelas que irão fazer parte da junção. Na cláusula JOIN a condição de junção é especificada pela cláusula ON. Não há necessidade o uso de parênteses para duas junções sucessivas. Sintaxe: TABELA_01 JOIN TABELA_02 ON <condição-junção> Msc. Arquimedes S. L. de Medeiros 62 Exemplo SQL Listar o nome da disciplina, matrícula, nome dos alunos aprovados em 2008/3 na disciplina 3810 com média acima de sete. select d.nom_disc, a.mat_alu, a.nom_alu from periodos_letivos l join historicos h on l.ano = h.ano and l.semestre = h.semestre join disciplinas d on h.cod_disc = d.cod_disc join alunos a on h.mat_alu = a.mat_alu where d.cod_disc = '3810' and h.ano = 2008 and h.semestre = 3 and h.situacao = 'AP' and h.media > 7 order by a.nom_alu; Msc. Arquimedes S. L. de Medeiros 63 Exemplo SQL NOM_DISC MAT_ALU NOM_ALU ----------------------------- ---------- -------------------------------------- DIREITO CIVIL II (OBRIGACOES) 108720 AGNES LOUIZE SANTOS - TESTE DIREITO CIVIL II (OBRIGACOES) 90599 ALEX AZEVEDO DANTAS DIREITO CIVIL II (OBRIGACOES) 91180 ANA FATIMA SEVERINO - TESTE DIREITO CIVIL II (OBRIGACOES) 71087 AYALA ANDRADE ILZE SANTOS - TESTE DIREITO CIVIL II (OBRIGACOES) 90556 CLISSIA CRUZ REIS TELES - TESTE DIREITO CIVIL II (OBRIGACOES) 92240 DANIELLE COSTA SOUZA - TESTE DIREITO CIVIL II (OBRIGACOES) 83757 JULIANA ALMEIDA MARIA SANTOS - TESTE DIREITO CIVIL II (OBRIGACOES) 98692 KARLLA DANTAS NETO - TESTE DIREITO CIVIL II (OBRIGACOES) 90776 LEILANE COELHO ROCHA - TESTE DIREITO CIVIL II (OBRIGACOES) 90889 LOUISE ANDRADE MOZARTT SANTANA - TESTE Msc. Arquimedes S. L. de Medeiros 64 Considerações sobre Junção Natural A existência da junção natural em um determinado comando não inviabiliza a utilização das demais cláusulas do comando SELECT. É importante ressaltar que em termos lógicos, as cláusulas WHERE e FROM, e portando a junção natural, são avaliadas antes das outras cláusulas. Assim, as cláusulas GROUP BY (será vista posteriormente) e ORDER BY são aplicadas sobre o resultado da junção. Msc. Arquimedes S. L. de Medeiros 65 OUTER JOIN A junção natural (INNER JOIN) possui como característica básica a exclusão de linhas das tabelas que não tenham a respectiva referência. O OUTER JOIN recupera as linhas da tabela mesmo que não haja igualdade de valores. Existem três tipos de junção externa: LEFT JOIN; RIGHT JOIN; FULL JOIN. Msc. Arquimedes S. L. de Medeiros 66 Exemplo SQL Recuperar para cada curso os seus coordenadores. O resultado deve conter o código do curso, nome do curso e nome do professor coordenador. Os cursos que não possuem coordenador devem ser recuperados. select c.cod_curso, c.nom_curso, p.nom_prof from cursos c left join professores p on c.idt_prof = p.idt_prof order by c.nom_curso; Msc. Arquimedes S. L. de Medeiros 67 Exemplo SQL COD_CURSO NOM_CURSO NOM_PROF --------- ---------------------------------------- ------------------------------------ 131 Arquitetura e Urbanismo - Manha Ricardo Soares Mascarello 26 Ciencia da Computacao - Tarde/Noite Danielle Amaral Menendez 13 Direito Diurno 4 Direito Noturno Gabriela Maia Reboucas 44 Enfermagem - Tarde/Noite Maria da Pureza Ramos de Santa Rosa 95 Informatica e Aplicacoes Web - Manha 35 Odontologia - Integral Suzane Rodrigues Jacinto Goncalves 123 Petroleo e Gas - Noite Marco Antonio Domingues 52 Sistemas de Informacao - Noite Danielle Amaral Menendez 125 Sistemas Para Internet - Manha 103 Sistemas Para Internet - Noite Msc. Arquimedes S. L. de Medeiros 68 Exemplo SQL Recuperar para cada curso os seus coordenadores. O resultado deve conter o código do curso, nome do curso e nome do professor coordenador. Os professores que não coordenam cursos também devem ser recuperados select c.cod_curso, c.nom_curso, p.nom_prof from cursos c right join professores p on c.idt_prof = p.idt_prof order by c.nom_curso; Msc. Arquimedes S. L. de Medeiros 69 Exemplo SQL COD_CURSO NOM_CURSO NOM_PROF --------- ---------------------------------------- -------------------------------------------------- 131 Arquitetura e Urbanismo - Manha Ricardo Soares Mascarello 26 Ciencia da Computacao - Tarde/Noite Danielle Amaral Menendez 4 Direito Noturno Gabriela Maia Reboucas 44 Enfermagem - Tarde/Noite Maria da Pureza Ramos de Santa Rosa 35 Odontologia - Integral Suzane Rodrigues Jacinto Goncalves 123 Petroleo e Gas - Noite Marco Antonio Domingues 52 Sistemas de Informacao - Noite Danielle Amaral Menendez Maria Lucila Hernandez Macedo Ana Maria de Souza Martins Farias Jose Paulo de Andrade Fabia Carvalho Figueiredo Maria Suely Ferreira de Araujo Geilsa Alves Almeida Joao Francisco do Lago Rodrigues Marco Antonio Ramos Nunes Sandro Luis Medeiros Msc. Arquimedes S. L. de Medeiros 70 Exemplo SQL Recuperar para curso todos os seus coordenadores. O resultado deve conter o código, nome do curso e nome do coordenadores. Os cursos sem coordenadores, e os professores sem coordenação em cursos, devem ser listados select p.nom_prof, c.cod_curso, c.nom_curso from cursos c full join professores p on c.idt_prof = p.idt_prof order by p.nom_prof, c.nom_curso; Msc. Arquimedes S. L. de Medeiros 71 OUTER JOIN O Oracle permite que a junção externa seja implementada na cláusula WHERE. Na condição de junção, a tabela a ter os dados exibidos incondicionalmente deve possui no elemento oposto da condição a indicação “(+)”. Se na consulta pretendemos recuperar os cursos sem coordenador, a condição de junção seria: c.idt_prof = p.idt_prof (+). Esta sintaxe não permite o FULL JOIN. Msc. Arquimedes S. L. de Medeiros 72 Exemplo SQL Recuperar para cada curso todos os seus coordenadores. O resultado deve conter o código, nome do curso e nome do coordenador. Os curso que não possuírem coordenadores devem ser listados. select c.cod_curso, c.nom_curso, p.nom_prof from cursos c, professores p where c.idt_prof = p.idt_prof (+) order by c.nom_curso, p.nom_prof; Msc. Arquimedes S. L. de Medeiros 73 Exemplo SQL COD_CURSO NOM_CURSO NOM_PROF --------- ---------------------------------------------------------------------------- 131 Arquitetura e Urbanismo - Manha Ricardo Soares Mascarello 26 Ciencia da Computacao - Tarde/Noite Danielle Amaral Menendez 13 Direito Diurno 4 Direito Noturno Gabriela Maia Reboucas 44 Enfermagem - Tarde/Noite Maria da Pureza Ramos de Santa Rosa 95 Informatica e Aplicacoes Web - Manha 35 Odontologia - Integral Suzane Rodrigues Jacinto Goncalves 123 Petroleo e Gas - Noite Marco Antonio Domingues 52 Sistemas de Informacao - Noite Danielle Amaral Menendez 125 Sistemas Para Internet - Manha 103 Sistemas Para Internet - Noite Msc. Arquimedes S. L. de Medeiros 74 Exemplo SQL Listar o código e o nome dos coordenadores com seus respectivos cursos. Professores que não são coordenadores devem ser listados. select p.mat_prof, p.nom_prof, c.nom_curso from cursos c, professores p where p.idt_prof (+) = c.idt_prof order by p.nom_prof, c.nom_curso; Msc. Arquimedes S. L. de Medeiros 75 Cláusula USING Utilizando a cláusula JOIN, é possível especificar a junção definindo apenas o nome da coluna a ser utilizada na junção. Isto só é possível se o nome da coluna for o mesmo para as duas tabelas. Sintaxe: Tabela-01 JOIN Tabela-02 USING (<colunas>) Uma lista de colunas separadas por vírgula é utilizada em junção com chave composta. Não admite apelidos para as tabelas. Msc. Arquimedes S. L. de Medeiros 76 Exemplo SQL Recuperar para cada curso todos os seus coordenadores. O resultado deve conter o código, nome do curso e o nome do coordenador. Os cursos que não possuírem coordenadores deve ser considerados no resultado. select cod_curso, nom_curso, nom_prof from cursos left join professores using ( idt_prof ) order by nom_curso, nom_prof; Msc. Arquimedes S. L. de Medeiros 77 Cláusula NATURAL A cláusula NATURAL aplicada ao JOIN elimina a necessidade de especificar a condição de junção. O SGDB automaticamente assume os atributos comuns para efetuar a junção entre as tabelas. Sintaxe: Tabela-01 NATURAL JOIN Tabela-02 Tal como no uso da cláusula USING, não admite apelidos para as tabelas. Msc. Arquimedes S. L. de Medeiros 78 Exemplo SQL Recuperar para cada curso todos os seus coordenadores. O resultado deve conter o código, nome do curso e o nome do coordenador. Os cursos que não possuírem coordenadores deve ser considerados no resultado. select cod_curso, nom_curso, nom_prof from cursos natural left join professores order by nom_curso, nom_prof; Msc. Arquimedes S. L. de Medeiros 79 Exercício 1)Listar o nome dos alunos e o nome do curso de todos os alunos e seus cursos. 2) Listar o nome dos alunos, o nome dos cursos e a MGP, cujos os alunos possuem média maior que 7,0. Ordenar pelo nome do curso e nome do aluno. 3) Listar todos os códigos das disciplinas, ano, semestre, identificador do professor e nome, para as turmas do ano 2008 e semestre 3, ordenada pelo código da disciplina. Mesmo que as disciplinas não possuam professor alocado deve ser listadas. 4) Listar todos os códigos das disciplinas, ano, semestre, identificador do professor e nome para as turmas do ano 2008 e semestre 3, ordenada pelo código do professor. Mesmo que os professores não possuam disciplinas devem ser listadas. 5) Listar todos os códigos das disciplinas, ano, semestre, identificador do professor e nome para as turmas do ano 2008 e semestre 3. Mesmo que os professores não possuam disciplinas ou as disciplinas não possuam professores devem ser listadas. Msc. Arquimedes S. L. de Medeiros 80 Exercício 6) Listar o nome dos alunos, o nome da disciplina e as notas e faltas destes que estão matriculados no ano de 2011, semestre 3, ordenado pelo nome do aluno e depois pelo nome da disciplina. 7) Listar todos os alunos que estão e não estão matriculados no curso 52. A lista deve conter o nome do aluno, o semestre, o ano e o nome do curso e ser ordenada pelo nome do aluno. 8) Listar todos os alunos do curso 52 que possuem em seu histórico média maior que 6. A lista deve conter o nome do aluno, semestre, ano, média, faltas e o nome da disciplina. Msc. Arquimedes S. L. de Medeiros 81 Funções de Agregação COUNT( [DISTINCT] coluna ) Recupera o número de elementos de um determinado grupo. Quando especificado com o identificador DISTINCT, efetua a contagem dos valores diferentes existentes na coluna. O asterisco (*) pode ser utilizado como argumento da função para indicar a contagem no número de linhas. SUM( [DISTINCT] coluna ) Calcula o somatório dos valores de uma coluna. Msc. Arquimedes S. L. de Medeiros 82 Funções de Agregação AVG ( [DISTINCT] coluna ) Obtém a média entre os valores de uma coluna. MIN ( coluna ) Obtém o menor valor contido em uma coluna. MAX ( coluna ) Obtém o maior valor armazenado em uma coluna. Os valores nulos não são considerados para o cálculo do resultado da função. A exceção a isto é o count (*). Msc. Arquimedes S. L. de Medeiros 83 Funções de Agregação Para considerar os valores nulos deve ser utilizada uma função, tal como a NVL do Oracle, para tratamento destes valores. AVG( NVL( media, 0 ) ) Quando aplicadas a uma tabela, é considerado apenas um grupo formado por todos os elementos da tabela. Msc. Arquimedes S. L. de Medeiros 84 Exemplo SQL select count(*) TOT_LINHAS, count( DISTINCT COD_CURSO ) TOT_CURSOS_DIF, MAX(MGP) MAIOR_MGP, MIN(MGP) MENOR_MGP, trunc(AVG(MGP),2) MEDIA_GERAL_MGP from alunos; TOT_LINHAS TOT_CURSOS_DIF MAIOR_MGP MENOR_MGP MEDIA_GERAL_MGP ---------- -------------- --------- --------- --------------- 5710 10 9,82 0 6,09 Msc. Arquimedes S. L. de Medeiros 85 Cláusula GROUP BY A cláusula GROUP BY permite que os dados consultados sejam agrupados segundo um determinado critério. Sobre os grupos formados podem ser extraídas informações sumarizadas. O critério de agrupamento (itens de grupo) é definido a partir de uma lista de elementos separados por vírgula. Cada item de grupo pode ser uma coluna ou uma expressão. Msc. Arquimedes S. L. de Medeiros 86 Cláusula GROUP BY Em uma consulta agrupada, só podem ser projetados itens de grupo (elementos da lista da cláusula GROUP BY) ou uma das funções de agregação. O inverso não é verdadeiro, ou seja, um item de grupo não precisa necessariamente ser projetado. Msc. Arquimedes S. L. de Medeiros 87 Exemplo SQL Recuperar para cada curso, a quantidade de alunos, a menor MGP, maior MGP e média geral das MGPs. select cod_curso "Código Curso", nom_curso "Curso", count(*) "Total Alunos", min(mgp) "Menor MGP", max(mgp) "Maior MGP", trunc(avg(mgp),2) "Média MGP" from alunos join cursos using ( cod_curso ) group by cod_curso, nom_curso order by nom_curso; Msc. Arquimedes S. L. de Medeiros 88 Considerações sobre o GROUP BY A cláusula agrupada pode ser combinada com a cláusula WHERE, sendo que os dados só são agrupados depois de filtrados. Quando o critério de agrupamento possui mais de um item de grupo, a classificação dos dados nos vários grupos levará em consideração a combinação dos valores das colunas que compõem a cláusula GROUP BY. Msc. Arquimedes S. L. de Medeiros89 Cláusula HAVING Funciona de modo similar a cláusula WHERE, sendo que o filtro é aplicado aos grupos formados pela cláusula GROUP BY. A cláusula HAVING só pode ser definida combinada com a cláusula GROUP BY. Somente itens de grupo e funções de agregação podem ser utilizadas nas expressões condicionais do HAVING. As funções de agregação utilizadas na cláusula HAVING não precisam ser as mesmas utilizadas na projeção. Msc. Arquimedes S. L. de Medeiros 90 Exemplo SQL Selecionar o código do curso, total de alunos, maior mgp e média geral dos alunos daqueles cursos que possuem a menor média geral superior a 3 select cod_curso "Cód. Curso", count(*) "Tot. Alunos", max(mgp) "Maior MGP", trunc(avg(mgp),2) "Média MGP" from alunos group by cod_curso having min( mgp ) > 3; Msc. Arquimedes S. L. de Medeiros 91 Exercício 1) Obter a quantidade de alunos cadastrados por curso, exibindo código do curso e quantidade de alunos ordenados pelo código do curso. 2) Obter a quantidade de disciplinas que possuem mais do que 10 alunos matriculados, exibindo código da disciplina e quantidade de alunos ordenado pela quantidade de alunos. 3) Exibir a maior e menor mgp dos alunos da universidade. 4) Obter a quantidade de alunos matriculados por turma. 5) Obtenha as disciplinas que fazem parte do currículo do curso 13 exibindo código da disciplina, nome da disciplina e quantidade de créditos ordenado pelo período e nome da disciplina. Msc. Arquimedes S. L. de Medeiros 92 Exercício 6)Obtenha a quantidade de alunos, por curso, exibindo código do curso, nome do curso e quantidade ordenado pelo nome do curso. 7)Obtenha, através do histórico, as disciplinas que possuem média geral maior que 7, agrupando por ano, semestre e nome da disciplina, sabendo que a média geral é a soma das médias dos alunos para a disciplina, divido pela sua quantidade. Ordene pelo nome da disciplina, ano e semestre. 8) Obtenha todos os alunos que possuem média acima de 8. 9) Liste a quantidade de turmas por professor, ano e semestre, mostrando o nome do professor, ano, semestre e quantidade, ordenando também nessa ordem. Msc. Arquimedes S. L. de Medeiros 93 Subselect É um recurso oferecido pelo SQL que permite a um comando SELECT obter e utilizar dinamicamente os dados de um outro para composição do resultado final. O subselect pode ocorrer nas cláusulas SELECT, FROM, WHERE e HAVING. A única exigência para o uso de subselects é o respeito à compatibilidade entre os operadores das condições e o resultado do subselect. Msc. Arquimedes S. L. de Medeiros 94 Exemplo de subselect Quais são os alunos que possuem a MGP maior que Victor (matrícula 101049)? Qual a média de Victor? Consulta principal Subconsulta Msc. Arquimedes S. L. de Medeiros 95 Exemplo de subselect Quais são os alunos que possuem a MGP maior que Victor? select mgp from alunos where mat_alu = 101049; MGP ---- 7,22 select mat_alu, nom_alu, mgp from alunos where mgp > 7.22 order by mgp desc; O resultado da primeira consulta (7.22) é utilizado para obter o resultado final Msc. Arquimedes S. L. de Medeiros 96 Exemplo de Subselect Quais são os alunos que possuem a MGP maior que Victor (matrícula 101049)? select mat_alu, nom_alu, mgp from alunos where mgp > ( select mgp from alunos where mat_alu = 101049 ) order by mgp desc; Msc. Arquimedes S. L. de Medeiros 97 Subselect na Cláusula WHERE No exemplo mencionado, a média obtida do resultado da primeira consulta à tabela de alunos é automaticamente utilizado na seleção dos dados da própria tabela alunos no SELECT principal. Na comparação da cláusula WHERE podemos verificar que o valor média é compatível com o valor único recuperado no subselect. Para operadores como o IN, o resultado do subselect pode recuperar uma coleção de valores. Msc. Arquimedes S. L. de Medeiros 98 Observações para Subselects Os subselects devem estar envolvidos entre parênteses. Devem ser colocados no lado direito do operador de comparação. Utilizar operadores de uma única linha com subselect de uma única linha. Utilizar operadores de várias linhas com subselects de várias linhas. Msc. Arquimedes S. L. de Medeiros 99 Exemplo SQL Recuperar a matrícula, nome e mgp dos alunos com melhor média geral ponderada da instituição. select mat_alu, nom_alu, mgp from alunos where mgp = ( select max(mgp) from alunos ) select mat_alu, nom_alu, mgp from alunos where mgp >= all( select mgp from alunos ) Msc. Arquimedes S. L. de Medeiros 100 Exemplo SQL Encontrar os alunos com média maior do que a média da instituição. select mat_alu, nom_alu, mgp from alunos where mgp > ( select avg(mgp) from alunos ); Msc. Arquimedes S. L. de Medeiros 101 Exemplo SQL Encontrar os alunos do curso 13 com média maior do que pelo menos uma das médias dos alunos do curso 26. select mat_alu, nom_alu, mgp from alunos where cod_curso = 13 and mgp > any( select mgp from alunos where cod_curso = 26 ); Msc. Arquimedes S. L. de Medeiros 102 Exemplo SQL Encontrar os alunos do curso 13 que possuam média maior do que todas as médias dos alunos do curso 26. select mat_alu, nom_alu, mgp from alunos where cod_curso = 13 and mgp > all( select mgp from alunos where cod_curso = 26 ); Msc. Arquimedes S. L. de Medeiros 103 Subselect com ligação É possível para um subselect utilizar dados oriundos do select mais externo. O escopo de visibilidade dos dados só permite a um subselect mais interno enxergar dados de um select mais externo. Em termos lógicos, a execução ocorre da seguinte forma: Para cada linha do select mais externo, o subselect é executado utilizado os valores da linha atual do select mais externo como parâmetro. Msc. Arquimedes S. L. de Medeiros 104 Exemplo SQL Obter o código, nome do curso, nome do aluno e média dos alunos com a melhor média de cada curso. select c.cod_curso, c.nom_curso, mat_alu, nom_alu, mgp from alunos a join cursos c on a.cod_curso = c.cod_curso where mgp = ( select max(mgp) from alunos a1 where a1.cod_curso = a.cod_curso ) order by c.nom_curso, nom_alu; Msc. Arquimedes S. L. de Medeiros 105 Considerações sobre exemplo Podemos afirmar que o exemplo anterior possui um subselect com ligação. Para cada linha do resultado da junção, a média do aluno é comparada com o resultado da subconsulta, ou seja, com a maior MGP entre os alunos do curso. Nos subselects com ligação, o subselect interno tem a execução dependente do valor da linha atual do select externo. Msc. Arquimedes S. L. de Medeiros 106 Considerações sobre o exemplo No exemplo, a subconsulta utiliza na sua condição de seleção o valor do código do curso do select externo ( c.cod_curso ). Um algoritmo lógico de execução: para cada linha da junção faça obter linha atual; executar o subselect substituindo o c.cod_curso por linha_atual.cod_curso obter maior_mgp; se linha_atual.mgp = maior_mgp então adicionar linha_atual no resultado; fim se fim para Msc. Arquimedes S. L. de Medeiros 107 Subselects com lista de valores É possível comparar listas de valores com o resultado de um subselect. Para tanto, deve-se indicar a lista de valores através do uso de parênteses. O número de elementos da lista de valores deve ser igual ao número de elementos projetados no subselect. Os i-ésimos domínios também devem ser compatíveis. Msc. Arquimedes S. L. de Medeiros 108 Exemplo SQL Obter o código, nomedo curso, nome do aluno e média dos alunos com a melhor média de cada curso. select c.cod_curso, c.nom_curso, mat_alu, nom_alu, mgp from alunos a join cursos c on a.cod_curso = c.cod_curso where ( a.cod_curso, mgp ) in ( select a1.cod_curso, max(mgp) from alunos a1 group by a1.cod_curso ) order by c.nom_curso, nom_alu; Msc. Arquimedes S. L. de Medeiros 109 Operador EXISTS É normalmente utilizado em situações de subselect onde o operador IN pode ser aplicado. Em sua lógica de execução, os elementos do select mais externo serão retornados no resultado se a execução do subselect recuperar algum valor. Em linhas gerais é utilizado em subselects com ligação. É possível utilizar em conjunto com o operador de negação. Msc. Arquimedes S. L. de Medeiros 110 Exemplo SQL Recuperar uma lista com o código e nome da disciplina que o aluno de matrícula 83944 foi aprovado. select cod_disc, nom_disc from disciplinas d where exists( select h.faltas from historicos h where h.cod_disc = d.cod_disc and h.situacao = 'AP' and h.mat_alu = 83944 ) order by 2; Msc. Arquimedes S. L. de Medeiros 111 Subselect na cláusula HAVING O subselect na cláusula HAVING funciona de maneira análoga ao subselect na cláusula WHERE. A diferença ocorre que os elementos utilizados na composição da condição devem ser itens de grupo ou funções de agregação. As condições devem respeitadas os tipos de operadores em relação a resposta do subselect. Msc. Arquimedes S. L. de Medeiros 112 Exemplo SQL Listar os cursos que possuem a média ponderada superior a média geral de todos os alunos da instituição. select c.cod_curso, c.nom_curso, trunc(avg(mgp),2) media from cursos c join alunos a on c.cod_curso = a.cod_curso group by c.cod_curso, c.nom_curso having avg(mgp) > ( select avg(mgp) from alunos ) order by 3 desc; Msc. Arquimedes S. L. de Medeiros 113 Subselect na cláusula FROM Um subselect na cláusula FROM funciona como uma tabela dinâmica cujos dados são obtidos em tempo de execução do comando de seleção. Logicamente, o subselect da cláusula FROM é executado, e a tabela resultante da sua execução é utilizada na resolução da consulta. Não há restrição quando a composição do subselect na cláusula FROM. Msc. Arquimedes S. L. de Medeiros 114 Exemplo SQL Obter o código do curso, nome do aluno e média dos alunos com a melhor média de cada curso. select a.cod_curso, nom_alu, mgp from alunos a, ( select cod_curso, max(mgp) maximo from alunos group by cod_curso ) m where a.cod_curso = m.cod_curso and a.mgp = m.maximo order by nom_alu; Msc. Arquimedes S. L. de Medeiros 115 Ordenando Subselects Um subselect na cláusula FROM pode ser ordenado para que possam ser obtidos os n primeiros elementos do critério de ordenação. Os n primeiros elementos do resultado do subselect podem ser filtrados na cláusula WHERE do select principal usando o atributo implícito ROWNUM. Msc. Arquimedes S. L. de Medeiros 116 Exemplo SQL Obter os três alunos de maior MGP. select * from ( select mat_alu, nom_alu, mgp from alunos order by mgp desc ) where rownum <= 3; Msc. Arquimedes S. L. de Medeiros 117 Subselect na Projeção O subselect também pode ser definido como um elemento de projeção. A lógica de execução destes subselect é a mesma aplicada na cláusula WHERE. Os subselects definidos na projeção são normalmente utilizados para capturar os dados de outras tabelas que devem compor o resultado. O resultado do subselect deve recuperar apenas um valor, ou seja, uma coluna e uma linha. Msc. Arquimedes S. L. de Medeiros 118 Exemplo SQL Listar a matrícula, nome, MGP e nome do curso de todos os alunos cuja média é superior a sete. select mat_alu, nom_alu, mgp, ( select nom_curso from cursos c where c.cod_curso = a.cod_curso ) curso from alunos a where mgp > 7 Msc. Arquimedes S. L. de Medeiros 119 Exercício 1. Listar a turma que possui a maior quantidade de alunos matriculados, exibindo ano, semestre e quantidade. 2. Listar a disciplina que possui a maior quantidade de alunos matriculados exibindo código da disciplina, nome da disciplina e quantidade de alunos matriculados. 3. Listar o percentual de reprovação de cada disciplina, exibindo código, nome e percentual de reprovação. 4. Listar o código do professor que possui o maior percentual de reprovação. 5. Listar os alunos que tem a maior média, exibindo o nome do aluno e a MGP. Msc. Arquimedes S. L. de Medeiros 120 Exercício 6. Listar os alunos que possuem a maior média por curso, exibindo o nome do aluno a média e nome do curso. 7. Listar os alunos que possuem a menor média por curso, exibindo o nome do aluno a média e nome do curso. 8. Listar as disciplinas que estão acima da média geral ponderada de todas as disciplinas. Mostrando a disciplina e a média geral. 9. Listar todos os alunos que estão com média, maior que a média geral ponderada de todos os alunos. Mostrar nome e média. Msc. Arquimedes S. L. de Medeiros 121 Operador UNION Implementa a operação relacional de união, ou seja, recupera, sem repetição, as linhas pertencentes aos dois comandos de seleção A união somente é viável se os dois selects que a compõem tiverem o mesmo número de itens projetados e os i-nésimos domínios compatíveis A cláusula de ordenação é única para toda a operação união Os nomes das colunas recebem os mesmos nomes do primeiro select da união Msc. Arquimedes S. L. de Medeiros 122 Exemplo SQL Obter a lista dos coordenadores e professores que ministram aulas em 2011/3. select idt_prof, mat_prof, nom_prof from professores join cursos using ( idt_prof ) union select idt_prof, mat_prof, nom_prof from turmas join matriculas using ( ano, semestre, cod_disc ) join professores using ( idt_prof ) where ano = 2011 and semestre = 3 order by nom_prof; Msc. Arquimedes S. L. de Medeiros 123 Exemplo SQL IDT_PROF MAT_PROF NOM_PROF -------- -------- ------------------------------------- 1410 7196 Danielle Amaral Menendez 1137 6425 Gabriela Maia Reboucas 1745 8373 Marco Antonio Domingues 4309 4868 Maria da Pureza Ramos de Santa Rosa 1144 6441 Ricardo Soares Mascarello 2776 4480 Suzane Rodrigues Jacinto Goncalves 6 linhas selecionadas Msc. Arquimedes S. L. de Medeiros 124 Operador UNION ALL Se utilizado, o resultado da união NÃO irá remover as linhas repetidas. select idt_prof, mat_prof, nom_prof from professores join cursos using ( idt_prof ) union all select idt_prof, mat_prof, nom_prof from turmas join matriculas using ( ano, semestre, cod_disc ) join professores using ( idt_prof ) where ano = 2011 and semestre = 3 order by nom_prof; Msc. Arquimedes S. L. de Medeiros 125 Operador MINUS Implementa a operação relacional de diferença. Recupera todas a linhas existentes no resultado do primeiro select que não existam no resultado do segundo. São válidas as mesmas regras sintáticas do UNION. A operação de diferença pode ser definida por um subselect. Msc. Arquimedes S. L. de Medeiros 126 Exemplo SQL Listar os coordenadores que não ministraram aulas em 2011/3. select idt_prof, mat_prof, nom_prof from professores join cursos using ( idt_prof ) minus select idt_prof, mat_prof, nom_prof from turmas join matriculas using ( ano, semestre, cod_disc ) join professores using( idt_prof ) where ano = 2011 and semestre = 3 order by nom_prof; Msc. Arquimedes S. L. de Medeiros 127 Exemplo SQL Listar os coordenadores que não ministraram aulas em 2011/3. select p.idt_prof, mat_prof, nom_prof from professores p join cursos c on p.idt_prof = c.idt_prof where not exists( select 1 from turmas t join matriculas m on t.ano = m.ano and t.semestre = m.semestre and t.cod_disc = m.cod_disc join professores pt on t.idt_prof = pt.idt_prof where t.ano = 2011 and t.semestre = 3 and t.idt_prof = p.idt_prof ) order by nom_prof; Msc. Arquimedes S. L. de Medeiros 128 Operador INTERSECT Implementa a operação relacional de interseção. Recupera as linhas que são comuns aos resultados dos dois selects que compõem a interseção. São válidas as mesmas regras sintáticas do UNION. Msc. Arquimedes S. L. de Medeiros 129 Exemplo SQL Listar os coordenadores que ministraram aulas em 2011/3. select idt_prof, mat_prof, nom_prof from professores join cursos using ( idt_prof ) intersect select idt_prof, mat_prof, nom_prof from turmas join matriculas using ( ano, semestre, cod_disc ) join professores using ( idt_prof ) where ano = 2011 and semestre = 3; Msc. Arquimedes S. L. de Medeiros 130 Exercício 1. Utilizando o operador de união, listar o nome do curso e disciplinas, bem como seus respectivos códigos. 2. Utilizando o operador de interseção, listar o código das disciplinas que estão na matriz do período 1 e 2. 3. Utilizando o operador de diferença, listar o código das disciplinas que não estão na matriz do período 1. 4. Utilizando o operador de diferença, listar o código dos alunos que não estão matriculados na disciplina 10 do semestre 1 do ano de 2010. Msc. Arquimedes S. L. de Medeiros 131 DML – DATA MANIPULATION LANGUAGE INSERT UPDATE DELETE Msc. Arquimedes S. L. de Medeiros 132 Comando INSERT Sintaxe: INSERT INTO <tabela> VALUES (<lista de valores>) Nesta sintaxe, a ordem em que os valores são informados na lista de valores, deve ser a mesma em que a tabela está definida. No caso Oracle, na mesma ordem em que o comando DESC exibe a estrutura da tabela. O número de valores informados deve ser o mesmo do número de colunas da tabela. Msc. Arquimedes S. L. de Medeiros 133 Comando INSERT Sintaxe: INSERT INTO <tabela> (<lista de colunas>) VALUES (<lista de valores>) Nesta sintaxe, os valores serão atribuídos na mesma ordem da lista de colunas. A quantidade de elementos das listas de valores e colunas deve ser a mesma. Não é necessário informar os valores das colunas opcionais, sendo automaticamente atribuído o valor nulo. Msc. Arquimedes S. L. de Medeiros 134 Exemplos INSERT insert into cursos values ( 130, 'Medicina', 500, null ); insert into cursos ( cod_curso, nom_curso, idt_prof, tot_cred ) values ( 130, 'Medicina', null, 500 ); insert into cursos ( nom_curso, tot_cred, cod_curso ) values ( 'Medicina', 500, 130 ); insert into cursos ( cod_curso, nom_curso, idt_prof, tot_cred ) values ( 130, 'Medicina', 1865, 500 ); Msc. Arquimedes S. L. de Medeiros 135 Comando DELETE Sintaxe DELETE FROM <tabela> [ WHERE <condição> ]; O comando exclui as linhas da tabela, mas não a própria tabela Se a cláusula WHERE não for especificada todas as linhas da tabela são excluídas. Quando a cláusula WHERE é definida, apenas as linhas cuja condição é verdadeira serão excluídas. É permitido subselect na cláusula WHERE. Msc. Arquimedes S. L. de Medeiros 136 Exemplos DELETE delete from cursos; delete from cursos where cod_curso in ( 26, 52 ); delete from cursos c where cod_curso not in ( select cod_curso from alunos ); Msc. Arquimedes S. L. de Medeiros 137 Exemplo DELETE delete from cursos c where not exists ( select 1 from alunos a where c.cod_curso = a.cod_curso); Msc. Arquimedes S. L. de Medeiros 138 Comando UPDATE Sintaxe UPDATE <tabela> SET <lista de atribuições> [WHERE <condição> ]; Atualiza as linhas da tabelas. Se a cláusula WHERE não for especificada, todas as linhas da tabelas são atualizadas. A cláusula WHERE restringe as linhas a serem atualizadas Pode ser utilizado subselect. Msc. Arquimedes S. L. de Medeiros 139 Exemplo UPDATE update cursos set nom_curso = 'Desenvolimento de Aplicações Web‘, tot_cred = tot_cred + 20 where cod_curso = 95; update cursos set tot_cred = 210; update cursos set tot_cred = 300 where cod_curso in ( select distinct cod_curso from alunos where mgp > 9 ); Msc. Arquimedes S. L. de Medeiros 140 Exemplo UPDATE update cursos c set tot_cred = 0 where not exists ( select mat_alu from alunos a where c.cod_curso = a.cod_curso ); update cursos c set tot_cred = nvl(( select sum(creditos) from matrizes m inner join disciplinas d using (cod_disc) where c.cod_curso = m.cod_curso having sum(creditos) < 1000 ),0); Msc. Arquimedes S. L. de Medeiros 141 Exemplo UPDATE update cursos c set tot_cred = 0 where ( select count(*) from alunos a where a.cod_curso = c.cod_curso ) = 0; Msc. Arquimedes S. L. de Medeiros 142 Exercício 1. Insira um aluno novo , a média geral deve ser 0; 2. Inclua cinco novos alunos no curso de computação, a média geral deve ser 0; 3. Inclua três turmas novas e o professor deve estar como nulo; 4. Matricule os três alunos criados no exercício 2, em pelo menos 2 disciplinas do curso de computação no período letivo de semestre 1 ano 2012; 5. Altere a matrícula dos alunos criados no exercício 2 colocando as notas e faltas dos mesmos. 6. Coloque para as turmas criadas o professor de idt 13; 7. Excluir os registros das matrículas cujo o aluno já possua a disciplina no histórico; 8. Altere os alunos incluídos no exercício 2 para o curso de sistemas de informação. 9. Atualizar a média geral ponderada (mgp) dos alunos. Só devem ser consideradas as disciplinas do histórico com situação de AP ou RM e que pertencem a grade do curso do aluno. Msc. Arquimedes S. L. de Medeiros 143 DDL – DATA DEFINITION LANGUAGE CREATE ALTER DROP Msc. Arquimedes S. L. de Medeiros 144 Comando CREATE TABLE CREATE TABLE <nome_da_tabela> ( <lista de atributos>, [<lista restante>] ) [TABLESPACE <nome da tablespace>]; <lista de atributos> :: <def. atrib.> (, <def. atrib.> )* <def. atrib.> :: <nome> <tipo de dado> [ DEFAUT <valor> ] [ NULL | NOT NULL ] [ <restante coluna> ] Msc. Arquimedes S. L. de Medeiros 145 Comando CREATE TABLE CREATE TABLE <nome_da_tabela> ( <lista de atributos>, [<lista restante>] ) [TABLESPACE <nome da tablespace>]; <lista de atributos> :: <def. atrib.> (, <def. atrib.> )* <def. atrib.> :: <nome> <tipo de dado> [ DEFAUT <valor> ] [ NULL | NOT NULL ] [ <restante coluna> ] Msc. Arquimedes S. L. de Medeiros 146 Comando CREATE TABLE <restante coluna> || [ CONSTRAINT <nome> ] ( PRIMARY KEY | UNIQUE | REFERENCES <tabela> ( <coluna> ) | CHECK ( <condição> ) ); Msc. Arquimedes S. L. de Medeiros 147 Exemplo SQL create table alunos2 ( idt_alu number(6) not null constraint alunos_PK primary key, mat_alu number(10) not null, nome varchar2(40) not null, endereco varchar2(60) not null, cidade varchar2(30) default 'ARACAJU' not null, uf char(2) default 'SE' not null constraint alunos_ck_uf check ( uf in ( 'SE', 'BA', 'AL' ) ), cod_curso number(2) constraint alunos_fk_cursos references cursos ( cod_curso ), constraint alunos_uk unique ( mat_alu ) using index tablespace indices_acad ) tablespace dados_acad;Msc. Arquimedes S. L. de Medeiros 148 Exemplo SQL create table matriculas2 ( seq_mat number(6) not null, idt_alu number(6) not null, cod_disc number(6) not null, nota number(3,1) not null check( nota between 0 and 10 ), faltas number(3) not null check ( faltas >= 0 ), constraint mat_pk2 primary key ( seq_mat ) using index tablespace indices_acad, constraint mat_fk_alunos2 foreign key (idt_alu) references alunos2( idt_alu), constraint mat_fk_disc2 foreign key (cod_disc ) references disciplinas ( cod_disc ), constraint matric_alu_uk unique ( idt_alu, cod_disc ) using index tablespace indices_acad ) tablespace dados_acad; Msc. Arquimedes S. L. de Medeiros 149 Comando DROP TABLE Sintaxe DROP TABLE <nome da tabela> [CASCADE CONSTRAINTS]; Opção CASCADE CONSTRAINTS Se utilizada remove todas as validações de chaves estrangeiras que referenciam a tabela removida. Não remove a coluna que possui a chave estrangeira, mas sim a regra de integridade referencial. Quando esta opção não é definida, a tabela só é removida se não existirem chaves estrangeiras que a referencie. Msc. Arquimedes S. L. de Medeiros 150 Comando ALTER TABLE ALTER TABLE <nome da tabela> ( <cláusula de adição> | <cláusula de alteração> | <cláusula de remoção> ) <cláusula de adição> :: ADD ( <def. atrib.> | <def. restante> <cláusula de alteração> :: MODIFY <def. atrib.> <cláusula de remoção> :: DROP ( COLUMN <nome da coluna> | PRIMARY | UNIQUE ( <lista de colunas> ) | CONSTRAINT <nome da restrição> ) [CASCADE] Msc. Arquimedes S. L. de Medeiros 151 Exemplos SQL alter table alunos2 add cpf number(11) not null check ( cpf > 0 ); alter table alunos2 add constraint alunos_uk_cpf unique ( cpf ) using index tablespace indices_acad; alter table matriculas2 drop unique ( idt_alu, cod_disc ) cascade; alter table alunos2 modify mat_alu number(11); Msc. Arquimedes S. L. de Medeiros 152 Exemplo SQL create table cursos2 ( cod_curso number(2) not null, nom_curso varchar2(40) not null, cod_coord number(4), constraint crs_pk primary key (cod_curso) ); create table professores2 ( cod_prof number(4), nom_prof varchar2(40) not null, cod_curso number(2) not null, constraint prf_pk primary key (cod_prof) ); Msc. Arquimedes S. L. de Medeiros 153 Exemplo SQL alter table cursos2 add constraint cursos_fk_prof foreign key ( cod_coord ) references professores2 ( cod_prof ); alter table professores2 add constraint prof_fk_cursos foreign key ( cod_curso ) references cursos ( cod_curso ); Msc. Arquimedes S. L. de Medeiros 154 Comando CREATE INDEX Sintaxe CREATE INDEX <nome do índice> ON <nome da tabela> ( <colunas> ) [TABLESPACE <nome da tablespace>]; Alguns SGBDs admitem que os índices sejam criados com base em funções. Os índices são estruturas cujo objetivo é agilizar a recuperação dos dados. Nem sempre o uso de índice melhora o desempenho. Msc. Arquimedes S. L. de Medeiros 155 Exemplo SQL create index tca_dsc_i on tipos_contas (dsc_tpo) tablespace indices_acad; create index cre_nom_i on credores (nom_cred) tablespace indices_acad; create index coa_tca_fk_i on contas (idt_tpo) tablespace indices_acad; create index coa_cre_fk_i on contas (idt_cred) tablespace indices_acad; Msc. Arquimedes S. L. de Medeiros 156 Modificando índices Comando DROP INDEX Sintaxe: DROP INDEX <nome do índice>; Comando ALTER INDEX Sintaxe ALTER INDEX <nome do índice> REBUILD TABLESPACE <nome da tablespace>; Msc. Arquimedes S. L. de Medeiros 157 ALTER TABLE MOVE Alterando a TABLESPACE de uma tabela Sintaxe: alter table alunos move tablespace dados_scai; Msc. Arquimedes S. L. de Medeiros 158 Sequências Tem o objetivo de gerar números em sequência controlando a concorrência de acesso Uma sequência jamais gera o mesmo número mais de uma vez Sintaxe básica de criação: CREATE SEQUENCE <nome da sequência> START WITH <número inicial> INCREMENT BY <incremento> [MINVALUE <valor mínimo>] [MAXVALUE <valor máximo>] [NOCYCLE | CYCLE] [NOCACHE | CACHE <valor cache> ] Msc. Arquimedes S. L. de Medeiros 159 Sequências Sintaxe de remoção DROP SEQUENCE <nome da sequência>; Sintaxe de alteração DROP SEQUENCE <nome da sequência> <parâmetros de criação>; Obtenção de valores: NEXTVAL Recupera o próximo valor gerado pela sequência. CURRVAL Obtém o valor corrente da sequência. Msc. Arquimedes S. L. de Medeiros 160 Visões Sintaxe CREATE [OR REPLACE] VIEW <nome da visão> AS <comando SELECT>; O comando de seleção pode ser qualquer consulta válida da linguagem SQL Uma vez criada, a visão funciona para o usuário como uma tabela no banco de dados, sendo seus dados recuperados dinamicamente Nem toda visão pode se atualizada Msc. Arquimedes S. L. de Medeiros 161 Exemplo SQL CREATE OR REPLACE VIEW V_CONTAS AS SELECT idt_conta idt_conta, ano ano, mes mes, dat_emissao dat_emissao, dat_venc dat_venc, c.idt_cred idt_cred, nom_cred nom_cred, t.idt_tpo idt_tpo, dsc_tpo dsc_tpo, valor valor, status status, obs obs from contas c, credores cr, tipos_contas t where c.idt_cred = cr.idt_cred and c.idt_tpo = t.idt_tpo; Msc. Arquimedes S. L. de Medeiros 162 Considerações sobre visões As colunas das visões terão os mesmos nomes das colunas projetadas. Os apelidos das colunas serão os nomes das colunas da visão. A cláusula ORDER BY não pode ser utilizada na criação da visão, ou seja, na seleção utilizando a visão é que pode ser estabelecido o critério de ordenação. O comando de criação permite, após o nome da visão, a definição dos nomes das colunas. Msc. Arquimedes S. L. de Medeiros 163 Exemplo SQL CREATE OR REPLACE VIEW V_CONTAS (IDT_CONTA,ANO,MES,DAT_EMISSAO,DAT_VENC ,IDT_CRED,NOM_CRED,IDT_TPO,DSC_TPO,VALOR ,STATUS,OBSERVACAO) AS SELECT idt_conta, ano, mes, dat_emissao, dat_venc, c.idt_cred, nom_cred, t.idt_tpo, dsc_tpo, valor, status, obs from contas c, credores cr, tipos_contas t where c.idt_cred = cr.idt_cred and c.idt_tpo = t.idt_tpo Msc. Arquimedes S. L. de Medeiros 164 Justificativas do uso de visões Objetivos: Restringir o acesso a dados. Facilitar as consultas complexas. Permitir a independência de dados. Suporte a múltiplas visões dos dados. Msc. Arquimedes S. L. de Medeiros 165 Comando GRANT Comando para conceder privilégio de acesso aos “objetos” armazenados no banco de dados. Sintaxe: GRANT <privilégio> ON <objeto> TO <receptor>; <privilégio> :: priv, ( , priv ) * priv :: ( SELECT | INSERT | DELETE | UPDATE [ ( colunas ) ] | EXECUTE ) Msc. Arquimedes S. L. de Medeiros 166 Comando GRANT Sintaxe: <objeto> :: ( <TABLE | VIEW | PROCEDURE ) <receptor> :: ( <usuário> | <grupo> ) Exemplos: grant select, delete, update (nom_curso) on cursos to arquimedes; grant execute on f_pagou_matric to CALLCENTER; grant select on v_alunos to DPTO_FINANCEIRO; Msc. Arquimedes S. L. de Medeiros 167 Comando REVOKE Remove o privilégio de acesso A sintaxe é semelhante ao comando GRANT, substituindo o TO por FROM A retirada do privilégio de UPDATE não recebe colunas como parâmetro Exemplos: revoke select, delete, update on cursos from arquimedes; revoke execute on f_pagou_matric from CALLCENTER; grant select on v_alunos to DPTO_FINANCEIRO; Msc. Arquimedes S. L. de Medeiros 168 Criação de Grupos (ROLES) create role DFI_CONSULTA; grant connect to DFI_CONSULTA; grant select on academico.cursos
Compartilhar