Baixe o app para aproveitar ainda mais
Prévia do material em texto
Banco de Dados 44 Prof.ª Maria Angélica Faculdade de Tecnologia de Sorocaba Curso de Análise e Desenvolvimento de Sistemas Disciplina Tópicos em Banco de Dados Introdução à Linguagem SQL em Ambiente Oracle Apostila elaborada pela prof.ª Maria Angélica C. Andrade Cardieri Atualização: Janeiro/2013 Índice 1. Introdução a Linguagem SQL 3 1.1 SQL Básico 3 1.2 A ferramenta SQL*Plus 3 2. Tipos de declarações SQL 4 3. Padrões do Oracle-8 5 3.1 Tipos de dados no ORACLE-8 5 3.2 Restrições (constraints) 6 3.3 Nome de campos no Oracle8 6 4. Gerenciando Objetos no Oracle 7 4.1 Comandos DDL - Linguagem de Definição de Dados 8 4.1.1 Criando Tabelas 8 4.1.2 Alterando a estrutura de tabelas 9 4.1.3 Excluindo Tabelas 9 4.1.4 Renomeando uma tabela 10 4.1.5 Visualizando a estrutura de uma tabela e as tabelas criadas 10 4.1.6 Definido valores Default 10 4.2 Restrições 11 4.3 Comandos DML - Linguagem de Manipulação de dados 14 4.4 Pesquisa em Tabelas 15 4.5 Exemplos de pesquisas (comando select) 16 4.6 Funções 18 4.7 Funções para Manipulação de Datas 19 5. Extraindo Dados de mais de uma Tabela - Junção 20 6. Visões 21 7. Subconsultas 24 7.1 Consultas Encadeadas (Claúsula Where com Subquery) 24 7.2 Subconsultas em comandos Update e Delete. 27 8. Variáveis de Substituição em Consultas 28 8.1 Comando Define 28 8.2 Comando Accept 29 9. Usando Apelidos para Tabelas 30 10 . Criação de seqüências automáticas 31 11. Inclusão de tabelas a partir de outra tabela 32 12. Criando uma Tabela através de uma consulta 32 13. O Dicionário de Dados do Oracle-8 33 14. Transações 35 15. Utilização dos Operadores Relacionais no Oracle 36 16. Exercícios 38 16.1 Lista de exercícios complementar sobre o modelo Paciente-Consulta 38 Lista número 1 38 Parte 2.1 – Criando e manipulando o banco de dados 39 Parte 2.2 – Realizando Consultas 40 Parte 2.3 – Consultas usando funções 41 Parte 2.4 – Consultas avançadas 41 Parte 2.5 – Visões 42 Parte 2.6 – Subconsultas 42 Laboratório SQL - Lista Número 3 43 1. Introdução a Linguagem SQL Esta apostila apresenta uma introdução à linguagem SQL do SGBD ORACLE, apresentando os comandos que permitem criar, manter e acessar uma base de dados relacional. 1.1 SQL Básico A linguagem SQL é a ferramenta básica para acesso aos dados de um banco de dados relacional. Foi desenvolvida inicialmente nos laboratórios da IBM (1976) por E.F. Codd, sendo atualmente um padrão para os SGBD relacionais. Apesar de existirem alguns padrões da linguagem SQL, a maioria dos fabricantes de banco de dados criou suas próprias extensões para explorar as características do banco de dados como é o caso da Oracle com o PL/SQL. A SQL é considerada como sendo uma linguagem não procedural uma vez que ela processa conjunto de registros e fornece acesso ou navegação automática de dados. A base para esta linguagem é a álgebra relacional, muito embora existam diversas características importantes que não pertencem à álgebra relacional. Na verdade, a SQL padrão deve ser considerada como uma sub-linguagem, uma vez que não possui comandos ou declarações que permitam o controle de repetições ou de desvios condicionais e incondicionais. Para este tipo de controle a Oracle desenvolveu o PL/SQL que consiste em uma extensão da SQL padrão. 1.2 A ferramenta SQL*Plus A interface interativa do Oracle chama-se SQL*PLUS. Em um ambiente cliente-servidor, o módulo cliente do ORACLE deve estar instalado, fornecendo esta ferramenta. Para conectar ao banco através do SQL*PLUS acessar o menu INICIAR => Programas => Oracle FOR WINDOWS NT => SQL*PLUS. 2. Tipos de declarações SQL Os comandos ou declarações em SQL são divididos nas seguintes categorias: DDL (Data Definition Language) É a parte do SQL usada para definir dados e objetos de um banco de dados. Através destes comandos são feitas inserções no dicionário de dados. A tabela abaixo indica comandos DDL : Comando Função Create Table Cria uma tabela Create Index Cria um índice Alter Table Altera ou insere uma coluna da tabela Drop Table Elimina uma tabela do banco de Dados Drop Index Elimina um índice Grant Concede privilégios de acesso p/a um usuário DML (Data Manipulation Language) É a parte da SQL usada para a recuperação ou manipulação de dados. Permite realizar consultas, adicionar, alterar e excluir informações do banco de dados. Possui também cláusulas adicionais e funções para cálculo de totais, médias, contagem de registros e máximo e mínimo entre valores. A tabela abaixo apresenta alguns dos mais importantes comandos dessa categoria: Comando Função Select Seleciona dados de uma base Insert Insere uma linha em uma tabela Delete Apaga linhas da Tabela Update Altera o conteúdo de colunas (campos) da tabela. Commit Grava no disco as alterações feitas Rollback Desfaz as alterações 3. Padrões do Oracle-8 3.1 Tipos de dados no ORACLE-8 No Oracle os tipos de dados armazenados em uma tabela podem ser declarados conforme indicados na tabela abaixo: Tipo de Dado Descrição Char(n) Armazena dados do tipo caracter de comprimento fixo onde n indica o tamanho. Máximo de 255. Varchar2(n) Armazena dados do tipo caracter de tamanho variável. Máximo de 2000 bytes. Number(t,d) Armazena dados numéricos onde t é o total de dígitos e d indica o número de casas decimais. Máximo de 38 caracteres Date Data e hora entre 1/1/4712 a. C. até 31/12/4712 d.C. Long Semelhante ao varchar2, mas com tamanho até 2 GB. Raw (n) Armazena dados binários de tamanho até 2000 bytes. Long raw(n) Armazena dados binários de tamanho até 2GB. Outros tipos de dados: ROWID : Cada linha de uma tabela possui uma identificação ou endereço próprio. A função ROWID mostra o endereço de uma linha. Exemplo: SELECT ROWID FROM PACIENTE: Retornará o número interno das linhas: ROWID ------------------ AAAGD5AADAAAAADAAA AAAGD5AADAAAAADAAB AAAGD5AADAAAAADAAC AAAGD5AADAAAAADAAD AAAGD5AADAAAAADAAE 3.2 Restrições (constraints) São recursos proporcionados pelo SGBD para implementar regras de consistência básicas para os dados de uma tabela. Restrição Descrição Not Null Especifica que a coluna não pode conter um valor null. Unique Especifica que uma coluna ou combinação de colunas devem ser únicas para todas as linhas na tabela. Primary Key Identifica unicamente cada linha da tabela. Foreign key/ References Estabelece e força um relacionamento entre uma coluna e a tabela referenciada Check Especifica que uma condição deve ser True No item 4.2 desta apostila encontram-se mais detalhes sobre como definir restrições no banco de dados Oracle. 3.3 Nome de campos no Oracle8 É permitido o máximo de 30 caracteres para nomes de tabelas e colunas. Os nomes podem ser compostos de caracteres alfabéticos; dígitos numéricos(0 a 9) e os caracteres especiais “_” (underline), “$” (cifrão) , “#” (number). Um nome deve sempre começar com um caracter alfabético. Não é permitido o uso de palavras reservadas do Oracle como nome de tabelas e colunas. Para um mesmo usuário, não podem existir dois objetos do banco de dados com o mesmo nome. 4. Gerenciando Objetos no Oracle Nesta apostila utilizaremos o modelo abaixo como referência para os exemplos que se seguem. Modelo Entidade-Relacionamento Paciente 1:1 Faz 1: N Consulta 1: Natende 1:1 Médico Tabelas correspondentes ao Modelo E-R Tabela de Paciente Codpaciente number(04) (PK) Nomepaciente varchar2(30) Datanasc Date Sexo char(01) Endereco varchar2(25) Cidade varchar2(15) Desconto char(01) 4.1 Comandos DDL - Linguagem de Definição de Dados 4.1.1 Criando Tabelas Uma tabela pode ser criada a qualquer momento e sua estrutura pode ser modificada on-line. O nome de uma tabela não é sensível a letras maiúsculas/minúsculas. · Comando Create Table Create Table <nome _tabela> ( < nome coluna1 > <Tipo_dado> [< restrições_C1> ] . . . . . . . ( < nome colunaN> <Tipo_dado> [< restrições_CN> ] [, < Restrição > ...... < Restrição_TN> ] ) ; Exemplo: Create table Paciente ( Codpaciente number(4,0) Primary Key, Nompaciente varchar2(30) not null, Datanasc date, Sexo char( 1 ) check (sexo in ( ‘F’, ‘M’ )), Endereco varchar2(25) ) ; Create table Consulta ( Codconsulta number(3,0) Primary Key , Dataconsulta date, Tipocons char(01) check (tipocons in (´P´,´C´)), Codpaciente number(4,0) Not Null References Paciente, Codmedico number(4,0) Not Null References Medico , Valconsulta number(5,0) Not Null ) ; Obs.: Após o comando ser executado o Oracle retorna a seguinte mensagem: Table Created . 4.1.2 Alterando a estrutura de tabelas · Comando Alter Table Através deste comando o usuário pode alterar a estrutura de uma tabela, adicionar ou retirar uma constraint, etc ALTER TABLE < nome da tabela> [ ADD [< nome da coluna> <tipo_dado> [ <restriçãoC>] | < restriçãoT > ] ] [MODIFY (< nome da coluna > , . . .) ] [ DROP CONSTRAINT < restição> ] ; Exemplo: ALTER TABLE Paciente ADD (cidpaciente varchar2( 15 ) ) ; ALTER TABLE Paciente MODIFY (cidpaciente varchar2( 20 ) NOT Null ) ; ALTER TABLE Paciente ADD (desconto char( 01 ) check (desconto in (‘S`,´N` ) )) ; ALTER TABLE Paciente ADD ( Check (sexo in (´F´, ´M´ ))) ; Excluindo uma coluna ( para versões a partir do oracle 8i): ALTER TABLE Paciente DROP COLUMN desconto; 4.1.3 Excluindo Tabelas · Comando Drop Table Remove uma tabela da base de dados. Ao deletar uma tabela todos os seus dados e índices serão perdidos. Caso existam visões (View) definidas sobre a tabela estas continuarão existindo porém estarão inválidas. Só o criador da tabela e o DBA podem eliminar uma visão. DROP TABLE <nome_tabela> ; Exemplo: Drop Table paciente; Drop Table consultas; 4.1.4 Renomeando uma tabela Rename nome-antigo to nome-novo Exemplo: Rename paciente to paciente_BKP; 4.1.5 Visualizando a estrutura de uma tabela e as tabelas criadas Através do comando: SQL> Describe <nome_da_tabela>; É possível verificar a estrutura da tabela criada. O comando: SQL > Select table_name from user_tables; Lista o nome de todas as tabelas criadas pelo usuário em sua base de dados. 4.1.6 Definido valores Default Create table x ( ........ ......... datapagto date default sysdate, .......... status char(01) default ´F´ check (status in (´F´,´B´)), ..........); 4.2 Restrições As restrições são regras estabelecidas referentes ao preenchimento de uma coluna. Estas regras podem ser definidas no momento da criação da tabela, sendo no final da especificação da coluna ou ao final do comando Create. Também é possível definir restrições após a criação da tabela através do comando ALTER TABLE. Toda restrição tem um nome associado a ela. Se este nome não for indicado o ORACLE atribui um nome padronizado. É interessante dar nome as restrições, principalmente quando se trabalha em um ambiente cliente-servidor, pois quando da ocorrência de erros relativos a não observância das regras, o nome da restrição é propagado para o cliente, e é sempre mais fácil de ser diagnosticado se o nome for definido pelo usuário. Entre as restrições destacam-se: Chaves primárias Chaves estrangeiras Chaves únicas Identificadores de campos obrigatórios Condições para valores permitidos para determinado campo. Exemplos: Chave Primária: --- Forma mais simples (sem nomear a restrição): Create table Medico ( codMedico number(4) primary key, nomemedico varchar2(30)); ---- Nomeando a restrição Create table Medico (codMedico number(4), nomemedico varchar2(30), Constraint PK_Medico_codmedico Primary Key (codMedico) ); --- Criando uma restrição após a criação da tabela Create table Medico ( codMedico number(4), nomemedico varchar2(30)) ; Alter table medico add Constraint PK_Medico_codmedico primary key (codMedico); Obs: É interessante que o nome da restrição identifique claramente a qual tabela e campo esta se refere. Em aplicações cliente servidor isto pode ser útil para facilitar a identificação de erros. Chave Estrangeira: --- Forma mais simples (sem nomear a restrição): Create table consulta ( codConsulta number(3) primary key, dataconsulta date, codpaciente number(4) references paciente, codmedico number(4) references medico, valconsulta number(5) ); -- Nomeando a restrição Create table consulta ( codConsulta number(3) primary key, dataconsulta date, codpaciente number(4) , codmedico number(4) , valconsulta number(5), constraint FK_Consulta_codpaciente foreign key (codPaciente) references Paciente, Constraint FK_Consulta_codmedico foreign key (codMedico) references Medico); --- Criando uma restrição nomeada após a criação da tabela Create table consulta ( codConsulta number(3) primary key, dataconsulta date, codpaciente number(4) , codmedico number(4) , valconsulta number(5) ); alter table consulta add ( constraint FK_consulta_codpaciente foreign key (codPaciente) references paciente); alter table consulta add ( constraint FK_consulta_codmedico foreign key (codmedico) references Medico); Cláusula Check: Define um conjunto de valores permitidos ou condições de inserção em uma tabela --- Forma mais simples (sem nomear a restrição): Create table paciente ( ------ ------ sexo char(1) check (sexo in (‘F’, ‘M’)) , ------ ); OU --- Criando uma restrição nomeada após a criação da tabela Create table paciente ( ------ ------ sexo char(1) , ------ ); alter table paciente add constraint CK_paciente_sexo check (sexo in (‘F’, ‘M’)); 4.3 Comandos DML - Linguagem de Manipulação de dados · Comando INSERT é usado para inserir linhas em uma tabela. Não é necessário atribuir conteúdo para todos os campos. Sintaxe: Insert Into tabela [ (coluna,coluna... ) ] Values (valor, valor,....) Exemplo: Insert into paciente values (001,´João da Silva´, ´01-09-1957´ , ´M´, ´Rua das Flores, 30´, ´Sorocaba´,´S´ ); Observações: a) O formato padrão de datas pode variar dependendo da instalação do Oracle. b) Se na criação da tabela for utilizada uma cláusula Check, o dado digitado deve respeitar os valores definidos dentro da cláusula. Isto inclui, no caso de letras, a distinção entre maiúsculas e minúsculas. · Comando UPDATE Permite ao usuário alterar valores em linhas de uma tabela: Sintaxe: Update tabela set coluna [, coluna...] = [expressão, subquery ] [Where condição ] ; Exemplo: Update paciente set endereço= ‘Rua das Jaboticabas, 40` where codpaciente= 003; Update paciente set desconto = ´S´ where datanasc < ‘01/01/1937’; · Comando Delete Usado para eliminar linhas de uma tabela. Sintaxe: Delete from tabela [Where condição] ; Exemplo: Delete from paciente where codpaciente = 001; 4.4 Pesquisa em Tabelas · Comando Select O comando básico para pesquisa simples a uma tabela é o SELECT. Este comando faz parte da DML (Data Manipulation Language ou Linguagem de Manipulação de Dados). Sintaxe: SELECT informação-coluna FROM tabela(s) WHERE condição ORDER BY expressão da palavra chave Onde: informação_coluna : especifica as colunas da tabela a serem recuperadas. Podem também ser expressões ou constantes . tabela(s) : Nome das tabelas envolvidas. Where: Claúsula opcional, identifica um critério para recuperar linhas específicas. Filtra as linhas a serem exibidas. Order by : Claúsula opcional. Especifica a ordem no qual as linhas serão exibidas. Cláusula Where Representa o operador relacional de restrição, pois indica as condições que devem ser satisfeitas para seleção das linhas e colunas de uma tabela. A tabela a seguir apresenta os operadores lógicos que podem ser utilizados em uma cláusula Select . Operador Significado = igual a > maior que < menor que >= maior ou igual <= menor ou igual <> Diferente de AND E OR Ou NOT Negação BETWEEN Testa faixa de valores LIKE Valores correspondentes ao valor especificado IN Valor que seja igual a algum dos valores especificados em uma lista. % Curinga para qualquer combinação de caracteres - Curinga para qualquer caracter na posição Operadores lógicos suportados pelo SQL 4.5 Exemplos de pesquisas (comando select) 1- Exibir todos os dados de todas as linhas da tabela Paciente: Select * From Paciente; 2- Selecionar o nome e o endereço de todos os pacientes. Select nomepaciente, endereco From Paciente; 3- Selecionar linhas sem repetição de dados: Select distinct cidade From paciente; 4- Utilizando cabeçalhos específicos Select nomepaciente “Nome”, datanasc “Nascimento” From paciente; 5- Seleção utilizando uma função Select sum(valconsulta) , codmedico From consulta group by codmedico; Obs: A cláusula group by está detalhada na página 13 desta apostila. 6- Expressões aritméticas em determinada coluna Select codconsulta, valconsulta * 0.8 From consulta; O valor da consulta exibido será 80% do armazenado no banco de dados. O valor real não é alterado. 7- Exibindo os dados em determinada ordem Select * From paciente order by cdpaciente; Neste caso, a cláusula order by fará com que a consulta seja exibida em ordem de paciente. Qualquer campo pode ser utilizado nesta cláusula. 8- Exibindo dados com cláusula para seleção de registros específicos Select * from paciente where sexo = `F` 9- Concatenando colunas Select codpaciente || ´-´ || nompaciente from paciente ; O resultado deste comando será: Codpaciente ||´-´|| nompaciente ------------------------------------ 1- Lucélia Santos 2- Osmar Santos 3- Silvio Santos 4- José Santos 10- Tratando Valores Nulos Se uma coluna não possui valor (lembre-se espaço em branco é valor), ela é considerada com o valor NULO. O valor NULO afeta uma operação aritmética pois seu resultado é sempre nulo Podemos usar a função NVL para a conversão de valores nulos. Por exemplo: Select Cod_consulta, NVL( valconsulta,100) * 0.8 From Consulta Neste exemplo, se o valor da consulta for nulo será substituído pelo valor 100. 11. Cláusula Group By Utilizada para agrupar as linhas selecionadas baseada no valor expr para cada linha e retorna uma única linha de informação para cada grupo. Exemplo: SQL> Select coddepto, min(salario), max(salario), sum(salario) from emp Group by coddepto; coddepto min(salario) max(salario) sum(salario) ---------- -------------- -------------- -------------- 100 1050 2000 9500 102 850 1800 5700 103 2000 5000 12000 4.6 Funções Uma função é uma rotina que realiza uma operação específica retornado um resultado. O SQL possui funções de linha simples ou escalares (single Row) e funções de grupo (Group). Funções de Grupo As funções de grupo atuam sobre um conjunto de linhas retornando um único resultado. Função AVG - Calcula a média dos valores selecionados Exemplo: SQL > Select avg(nota) from aluno; Função MIN/MAX - Seleciona o menor ou o maior valor dentre os selecionados. Exemplo: SQL> Select min(nota) from aluno; Função COUNT - Retorna o número de linhas da consulta. SQL> Select count(Idaluno) from curso; Função SUM - realiza o somatório dos dados extraídos da coluna especificada. Os valores null são ignorados. 4.7 Funções para Manipulação de Datas · SYSDATE Retorna a data do sistema. Exemplo: SELECT SYSDATE FROM DUAL; SYSDATE -------- 18/08/05 · Função TO_CHAR Converte campo do tipo data em uma string. È possível extrair substrings correspondentes ao dia, mês , ano, hora, minuto e segundo. Exemplos: SELECT NOMEPACIENTE FROM PACIENTE WHERE TO_CHAR(DATANASC,’MM/YYYY’) = ‘03/1959’; SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM DUAL; TO_CHAR( -------- 14:42:38 · Função ADD_MONTHS Soma ou subtrai o número de meses indicados da data. Exemplo: SELECT ADD_MONTHS(DATACONS,+3) FROM CONSULTA; O exemplo acima exibe o campo data da consulta acrescido de 3 meses Para subtrair usar –3. · Para Somar Dias: basta somar o número de dias ao campo data. update consulta set datacons = datacons + 7 where codpaciente = 003; · Subtração entre duas datas O Oracle devolve o número de dias. SELECT (SYSDATE – DATANASC) FROM PACIENTE; · Inclusão de data com a hora minuto e segundo usando a função TO_DATE Insert into consulta values (020, To_Date(‘20/09/2003:10:15:00’,‘dd/mm/yyyy:hh24:mi:ss’) ,’P’, ........); 5. Extraindo Dados de mais de uma Tabela - Junção As vezes torna-se necessário juntar duas ou mais tabelas seguindo um determinado critério com o objetivo de selecionar colunas das mesmas. Neste caso as colunas das tabelas envolvidas na relação são comparadas. Será apresentado a seguir a junção denominada equi-join. Equi-Join : Quando o valor da coluna de uma tabela tem o seu valor correspondente na segunda. Exemplo : Exibir o nome do paciente e o código de todas as consultas realizadas por ele. Select nompaciente, codconsulta, data From paciente, consulta Where paciente.codpaciente = consulta.codpaciente; Será exibido na tela: Nompaciente Codconsulta Data --------------------- ---------------------- -------- Pedro Luiz Silva 010 10-AUG-97 Pedro Luiz Silva 020 12-SEP-97 Pedro Luiz Silva 028 01-NOV-97 Antônio Vieira 014 15-JAN-98 Cristina Souza 024 15-MAR-98 Cristina Souza 03020-ABR-98 Exemplo: Exibir código do paciente, nome do paciente, e o nome do médico e o código de cada consulta Select paciente.codpaciente, nompaciente, codconsulta, nommedico From paciente, consulta, medico Where paciente.codpaciente = consulta.codpaciente and consulta.codmedico = medico.codmedico; Será exibido na tela: CODPACIENTE Nompaciente Codconsulta Nommedico --------------------- ---------------------- --------------------- ------------------- 507 Pedro Luiz Silva 010 Dr. Carlos Wen 507 Pedro Luiz Silva 020 Dr. Sérgio Cesar 507 Pedro Luiz Silva 028 Dr. Sérgio Cesar 604 Cristina Souza 024 Dr. Eleno Góis 604 Cristina Souza 030 Dr. Carlos Wen 6. Visões É uma forma de visualizar várias tabelas como se fossem uma única tabela. Uma visão é como uma tabela lógica, pois não existe fisicamente e seus dados são obtidos dinamicamente a partir de tabelas do banco de dados. Ela pode conter colunas de diferentes tabelas e linhas que satisfaçam alguma condição. As visões ou visualizações tem como vantagem o fato de poder limitar as colunas e linhas que serão exibidas na tela. Este recurso é importante para proteção contra acesso a dados não permitidos para um determinado usuário. Outra vantagem é a simplificação do comando Select uma vez que, para um comando Select complexo e frequentemente utilizado podemos criar uma visão facilitando a execução do comando. · Comando Create View Create [ or replace ] View < nome_visão> [ < nome_coluna1>, . . . ] as <consulta> [with check option [constraint < restrição> ] ] ; Exemplos: Create View pacsorocaba as select * from pacientes where cidpaciente = ´Sorocaba´ with check option ; Create or replace View pacientes-res as select codpaciente, nomepaciente from paciente ; Create View PacConsulta as select codpaciente, nomepaciente, codconsulta,dataconsulta from paciente, consultas where paciente.codpaciente = consultas.codpaciente ; OBS: A view PacConsulta se refere a mais de uma tabela. Para selecionar todos os dados da view criada : Select * from paciente_res; Obs: A cláusula order by não pode ser usada em views. · Comando Drop View Exclui uma visão do dicionário de dados. Drop view < nome da Visão> ; Exemplo: Drop view pacientes-res ; · Alteração de dados através de uma Visão Uma visão pode sofrer operações de alteração, deleção, inclusão e consulta respeitando porém as seguintes restrições: Deleção, Atualização e Inclusão de dados (delete, update e insert) através de uma visão não é permitida se: · A visão foi criada a partir da junção de uma ou mais tabelas. · Se existir uma função de agrupamento (group by) · Se possuir a cláusula distinct · Se possuir colunas definidas por expressões Além disso, o insert não é permitido se alguma coluna Not Null, sem valor default especificado, não foi selecionada pela visão. 7. Subconsultas 7.1 Consultas Encadeadas (Claúsula Where com Subquery) Uma subquery ou subconsulta é quando o resultado de uma consulta é utilizado por outra consulta, de forma encadeada e contida no mesmo comando SQL. Restrições: · A query interna deve ser usada dentro de parênteses · A subquery não pode conter a cláusula order by · Quando utilizando múltiplas colunas em uma subquery, estas colunas devem aparecer na mesma ordem e com os mesmos tipos de dados da query principal, além do mesmo número de colunas · Podem ser utilizados operadores lógicos · As cláusulas numa instrução SQL são executadas na seguinte ordem: From (produto cartesiano) where (restrição) group by (agrupamento das linhas que satisfizerem o where) having (restrição sobre linhas do agrupamento) Exemplo: Produto Item-pedido @Codigo produto @Numero do pedido Descrição @Codigo do produto Unidade Quantidade Valor unit. Consulta: Que produtos tem pedidos cuja quantidade é superior a 20 ? Select descricao from produto Where cod_produto in (select cod_Produto from item-pedido where quantidade > 20) Resultado: Descrição Disquetes Papel Caneta Construtores: A construção in testa a pertinência de conjuntos, onde o conjunto é uma coleção de valores produzidos por uma cláusula select. O not-in testa a não pertinência de conjuntos. O construtor exist retorna o valor true se o resultado da subconsulta é não vazio. O not exists testa a não existência de tuplas em uma subconsulta. Consulta: Quais vendedores ganham um salário fixo abaixo da média? Vendedor @codigo-vendedor Nome-vendedor Faixa-de-Comissão Salário-Fixo Select nome-vendedor from vendedor Where salario-fixo < (select AVG(salario-fixo) from vendedor); Consulta: Quais os produtos que não estão presentes em nenhum pedido? Select cod-produto, descrição from produto Where not exists ( select * from item-pedido where produto.cod-produto = item-pedido.cod-produto) Consulta: Quais clientes estão presentes em mais de três pedidos ? Cliente Pedido @Código-cliente @Numero-pedido Nome-cliente Prazo-entrega Endereço Codigo-cliente Cidade Codigo-vendedor Cep Select nome-cliente from cliente where exists (select count( * ) from pedido where pedido.cod-cliente = cliente.cod-cliente group by cliente.cod-cliente having count(*) > 3 ); Exemplos com base no Modelo da vídeo-locadora: Consulta: Exibir nome dos sócios com uma ou mais locações Select cod-socio from socio where exists (select cod-socio from locacao where locacao.cod-socio = socio.cod-socio); ou podemos usar o operador IN : select cod-socio from socio where cod-socio in (select cod-socio from locacao); Consulta: Exibir código dos sócios que não possuam locações. - Utilizaremos o operador NOT EXISTS. Select cod-socio from socio where not exists (select cod-socio from locacao where locacao.cod-socio = socio.cod-socio) - Podemos também escrever este comando utilizando o operador NOT IN : Select cod-socio from socio where cod-socio not in ( select cod-socio from locacao); 7.2 Subconsultas em comandos Update e Delete. É possível utilizar subconsultas como argumento da cláusula where. Exemplo: 1. Alterar o valor da consulta em menos 10% se o campo desconto da tabela de paciente for igual a ‘S’. UPDATE CONSULTA SET VALCONSULTA = VALCONSULTA * 0.9 WHERE CODPACIENTE IN (SELECT CODPACIENTE FROM PACIENTE WHERE DESCONTO = ‘S’); 2. Excluir os pacientes que não tem consultas realizadas. DELETE PACIENTE WHERE CODPACIENTE NOT IN (SELECT DISTINCT CODPACIENTE FROM CONSULTA) 7.3 Cláusula Having A Cláusula HAVING é utilizada para selecionar linhas de uma seleção feita com group by ( restrição para o group by). É muito utilizada em subqueries. Exemplo: Alunos cuja média final é superior à média do aluno JOSE(cod_aluno = 002). Select nom_aluno, avg(val_nota_final) from matricula m, aluno a where m.cod_aluno = a .cod_aluno group by nom_aluno having avg(val_nota_final) > (select avg(val_nota_final) from matricula where cod_aluno = 002); 8.Variáveis de Substituição em Consultas Uma consulta pode conter variáveis que são informadas no momento da sua execução. Exemplo: SQL> Select codsocio, nomesocio 2 from socio where 3 cidsocio = &cidade; Enter value for cidade: ‘Sorocaba’ Old 3: cidsocio = &cidade new 3: cidsocio = ‘Sorocaba’ Para que a variável seja pedida apenas uma vez utilizar o símbolo &&. Na próxima referência a esta variável, ela já assume o valor especificado anteriormente. SQL> Select codsocio, nomesocio 2 from socio where 3 cidsocio = &&cidade; Enter value for cidade: ‘Sorocaba’ Old 3: cidsocio = &&cidade new 3: cidsocio = ‘Sorocaba’ 8.1 Comando Define Cria uma variável, ou substitui o valor previamente definido. Exemplo: SQL> Define codigo = 5; SQL> Select nomesocio, endsocio from socio where codsocio = &codigo ; => O comando Undefine libera a variável. SQL> undefine codigo; 8.2 Comando Accept Permite criar uma variável e obter o valor para ela, incluindo um texto explicativo. ACCEPT variável [number / char] [Prompt / Noprompt ‘mensagem-de- solicitação:’ ] [Hide] Com o comando accept é possível: - Testar o tipo de dado que está sendo informado. - Incluir texto explicativo. - Suprimir o eco, isto é não aparecer o que está sendo digitado. Exemplo: SQL> accept nome char prompt ‘ Informe o Nome: ` ; Informe o Nome: João da Silva SQL> Select codsocio, endsocio from socio where nomesocio = &nome; Char faz com que o tipo de dado seja verificado. SQL> accept password char prompt ´ Informe a senha: ` hide; Informe a senha: ****** Neste caso não aparece o que está sendo digitado. SQL> accept codsocio char noprompt; Neste caso não aparece a mensagem. 9. Usando Apelidos para Tabelas Apelidos ou table aliases podem ser usados para : 1- Simplificar o uso do nome de tabelas nas consultas; 2- Realizar junção usando a mesma tabela; isto é join de uma tabela com ela mesma. Exemplo: SQL> select distinct nomesocio from locacao l, socio s where l.codsocio = s.codsocio; SQL> select e.nomfunc, g.nomefunc from empregado e, funcionário g where e.mgr = g.numfunc Tabela funcionário numfunc ( numero de matrícula do funcionário) nomfunc (nome do funcionário) mgr (numero de matrícula gerente) Utilizando Sinônimos · Sinônimos podem ser definidos também no dicionário de dados da forma: Create synonym func for funcionário; · Sinônimos podem ser removidos pelo comando DROP SQL> Drop synonym [ Nome-do-sinônimo] 10 . Criação de seqüências automáticas Seqüências são estruturas do banco de dados que geram números seqüenciais. Isto é interessante para gerar chaves primárias ou seqüências geradas pelo próprio sistema. Sintaxe: Create sequence Nome-da-sequencia [ Increment by incrementador] [ start with valor-inicial] [ maxvalue valor_maximo | nomaxvalue ] [ minvalue valor_minimo | nominvalue ] [ Cycle | nocycle ] [ cache | nocache ] [ order | noorder ] Exemplo: SQL> Create sequence SEQcli increment by 1 start with 10 maxvalue 9999 nocache minvalue 1 order; SQL> Insert into cliente values (seqcli.nextval, ´Maria da Silva`, ´Rua das Flores`, ´Sorocaba`, ´01-sep-97` ); Para verificação e utilização dos números gerados pela seqüência: nome-da-sequencia.nextval => indica o próximo valor da sequência nome-da-sequencia.currval => indica o valor corrente SQL> select nsocio.currval from dual; SQL> select nsocio.nextval from dual; Para alterar uma sequência: SQL> Alter sequence seq increment by 1 Para eliminar uma sequência: SQL> Drop sequence seq; 11. Inclusão de tabelas a partir de outra tabela É possível usar os dados de uma tabela já existente para inclusão em outra. Insert into nome-da-tabela [ (coluna, coluna, ...) ] select lista-de-seleção from tabela; Exemplo: SQL> insert into socio-aux ( codsocio, nomesocio, datinclusão) select codsocio, nomesocio, datinclusão from socio where cidsocio = ´Sorocaba`; ou se as duas tabelas tem as mesmas colunas: SQL> insert into socio_aux select * from socio; 12. Criando uma Tabela através de uma consulta É possível criar uma tabela usando como base uma já existente. Create table paciente-bkp as select * from paciente; Neste caso será criada a tabela paciente_bkp com estrutura igual a tabela paciente e herdará também os dados. Create table paciente-fem as select codpaciente, nomepaciente where sexo = ´F´ Neste caso será criada a tabela paciente-fem apenas com dois campos da tabela paciente e somente com as linhas cujo campo sexo for igual a ´F´. Dica: Para criar uma nova tabela aproveitando a estrutura de outra mas sem levar os dados deve-se utilizar a cláusula Select com uma condição(Where) que force a consulta a não retornar nehuma linha. 13. O Dicionário de Dados do Oracle-8 Consiste de um conjunto de tabelas e views que são um guia de referência sobre a base de dados. Contém informações sobre: - O nome dos usuários Oracle - Direitos e privilégios - Nome dos objetos do banco de Dados - Constrains de tabelas Acesso ao dicionário de dados Através de comandos select nas tabelas e views do dicionário. Principais Views: User_xxx Mostra informações sobre objetos do usuário all_xxxxxx Mostra objetos que o usuário tem acesso além dos objetos do próprio usuário Dba_xxxxx Para usuários que tem acesso de dba, mostra objetos pertencentes a base de dados . Dictionary Contém todos os objetos do diconário de dados acessíveis pelo usuário Exemplo: SQL> Select * from dictionary; As views mais utilizadas são: Nome da View Descrição Dictionary Todos os objetos do dicionário de dados acessíveis pelo usuário User_objects Objetos pertencentes ao usuário User_tables Descrição das tabelas do usuário User_sequences Descrição das sequences do usuário User_synonym Sinônimos do usuário User_views texto das views do usuário User_indexes Descrição dos índices do usuário all_objects Objetos acessíveis pelo usuário Exemplo: Tabelas do usuário corrente: select table_name from user_tables; Colunas do tipo date na tabela locação select column_name from user_tab_columns where data_type = ´DATE` ; Informações do objeto do usuário corrente select object_name, object_type , created from user_objects; Definição de uma view select text from user_views where view_name = ´sociossor`; Todos os sinônimos visíveis pelo usuário Select synonym_name, table_name from all_synonyms; Consultar o nome da primary Key da tabela sócio Select constraint_name from user_constraints where table_name = ´SOCIO` and constraint_type = ´P` ; Obs: tipos de constrains: P = Primary Key C = Cláusula Check R = Foreign Key Verificar todas as constraints select constraint_name, constraint_type, table_name from user_constraints; 14. Transações Até agora vimos os comandos que pertencem a categoria DDL e DML. Existe uma terceira categoria de comandos chamada TPL (Transaction Process Language). Esta categoria inclui os comandos COMMIT e ROLLBACK que são os responsáveis pela gravação definitiva no banco de dados das alterações feitas pelos comandos DDL e DML e pela recuperação da situação anterior caso necessário. Commit : Grava uma transação no Banco de dados Rollback: Restaura uma transação. Recupera a posição em que o banco estava após o último commit ser executado. Exercício: 1- Inclua um novo paciente na tabela de pacientes. Através do comando select verifique se a inclusão foi realizada. Digite: SQL> Rollback ; Execute o select novamente e voce irá notar que o registro incluído não existe mais. 2- Inclua um novo paciente na tabela de pacientes.Digite: SQL> Commit ; Através do comando select verifique se a inclusão foi realizada. Digite: SQL> Rollback ; Execute o select novamente e voce irá notar que o registro incluído ainda existe apesar do Rollback ter sido executado. Isto acontece porque o rollback desfaz o que foi feito entre o último commit executado e a situação atual. 15. Utilização dos Operadores Relacionais no Oracle UNIÃO - unir 2 consultas em uma única As tabelas devem ser união compatíveis isto é ter a mesma estrutura. select * from paciente union select * from paciente_bkp; _______________________________________________________________________ INTERSECÇÃO - unir 2 consultas, somente as linhas idênticas serão retornadas. select * from paciente intersect select * from paciente_bkp; _______________________________________________________________________ DIFERENÇA - unir 2 consultas. Retorna todas as linhas da primeira consulta, menos as linhas iguais à segunda consulta. select * from paciente minus select * from paciente_bkp; _______________________________________________________________________ Outros Operadores (+) Indica que uma coluna é "outer join". Mesmo não existindo na outra tabela uma linha correspondente à coluna indicada com o operador. select A.nome, B.nome from paciente A, paciente_bkp B where A.codpaciente = B.codpaciente (+); _______________________________________________________________________ PROJEÇÃO select codpaciente, nomepac from paciente; SELEÇÃO select * from paciente where cidade = 'São Roque'; junção - (inner join) _______________________________________________________________________ deletar linha da tabela paciente_bkp delete from paciente_bkp where codpaciente = 003; ________________________________________________________ 16. Exercícios 16.1 Lista de exercícios complementar sobre o modelo Paciente-Consulta Lista número 1 1- Criar uma nova coluna na tabela paciente com o nome País varchar2(15); 2- Alterar a coluna endereço da tabela de paciente para 28 posições. 3- Excluir a nova coluna criada no exercício 1. 4- Incluir a restrição NOT NULL na coluna endereco. 5- Alterar a data de nascimento do paciente de código = 001 para ´01-09-1960´. 6- Alterar o campo desconto de todos os pacientes para ´N´. 7- Sobre a tabela Paciente, alterar o campo endereço para ‘Rua Melo Alves,40’ e cidade para ‘Itu” para o paciente de código = 02. 8- Excluir a consulta 002, somente se o campo valor consulta for igual a zero. 9- Excluir o paciente de código 5 10- Excluir todos os pacientes da cidade de sorocaba de sexo feminino. 11- Alterar o campo desconto de todos os pacientes de sexo feminino e idade superior a 60 anos para ‘S’. 12- Criar um novo campo na tabela paciente para registrar o telefone do paciente. Este campo deverá ser varchar2(15). Inserir o numero de telefone dos pacientes neste novo campo. 16.2 Laboratório SQL - Lista Número 2 As tabelas apresentadas neste item deverão ser utilizadas nos exercícios da parte 2.1 a parte 2.6 da lista número 2. Observação: PK - Chaves primárias FK - Chaves estrangeiras Parte 2.1 – Criando e manipulando o banco de dados 1. Criar as tabelas do modelo acima. 2. Inserir pelo menos 5 linhas de dados para cada tabela. 3. Alterar o campo endereço da tabela Cliente para 30 posições. 4. Acrescentar um novo campo de nome Pco_Unit (numérico com 4 inteiros e duas decimais), na tabela de itens de pedido. 5. Alterar o CEP de todos os clientes da cidade de Sorocaba para ‘18035-400’. 6. Alterar o prazo de entrega de todos os pedidos do cliente de código 20 para mais 10 dias. 7. Alterar o valor unitário para mais 10% para todos os produtos com unidade = ‘KG’. 8. Excluir todos os produtos com unidade = ‘CX’ e valor unitário superior a 50,00. Parte 2.2 – Realizando Consultas 1. Listar número do pedido e prazo de entrega de todos os pedidos. 2. Listar a descrição e o valor unitário dos produtos. 3. Listar nome e endereço de todos os clientes. 4. Listar nome de todos os vendedores. 5. Listar todas as informações da tabela de clientes. 6. Listar todas as informações da tabela produtos. 7. Listar o nome de todos os vendedores. Alterar o cabeçalho da coluna para nome. 8. Listar o preço dos produtos simulando um aumento em 10%. 9. Listar o salário fixo dos vendedores simulando o aumento em 5%. 10. Listar o nome dos clientes que moram em Sorocaba. 11. Listar todos os dados dos vendedores com salário fixo <$400,00. 12. Listar o código do produto e a descrição para os produtos cuja unidade seja igual a "Kg". 13. Listar o número dos pedidos com prazo de entrega entre 01/05/2012 a 01/06/2012. 14. Listar o pedido e o prazo de entrega para os pedidos com data de entrega no ano de 2004. 15. Listar os dados dos produtos cujo valor unitário seja maior que $100,00 e menor que $200,00. 16. Listar o número do pedido e o código do produto cuja quantidade esteja entre 1000 e 1500. 17. Listar o nome dos vendedores cujo nome comece por "José". 18. Listar o nome de todos os clientes cujo ultimo nome seja "Silva". 19. Listar a descrição e o código dos produtos que tem a seqüência "AC" em qualquer parte da descrição. 20. Listar os nomes dos clientes que não tenham endereço cadastrado. 21. Listar as cidades onde moram os clientes (exibir cada cidade apenas uma vez). 22. Listar os dados dos clientes por ordem alfabética de nome. 23. Listar os dados dos clientes por ordem alfabética decrescente de cidade. 24. Listar os dados dos clientes por ordem de cidade e por ordem do nome dos clientes. 25. Listar os códigos e a descrição dos produtos, cuja unidade igual o "Kg" por ordem alfabética de descrição. Parte 2.3 – Consultas usando funções 1. Listar a maior quantidade que conste na tabela de item do pedido. 2. Listar o menor valor unitário da tabela de produtos. 3. Listar a somatória dos salários fixos pago aos vendedores. 4. Listar o numero de produtos cuja unidade seja igual a "Lt". 5. Listar o numero de clientes agrupados por cidade. 6. Listar quantos pedidos cada vendedor realizou. 7. Listar o maior e o menor valor unitário para cada tipo de unidade de produto. 8. Listar o numero de clientes, agrupados por cidade para todas as cidades que aparecem mais de 4 vezes na tabela de clientes. Parte 2.4 – Consultas avançadas 1. Listar o código e o nome dos vendedores que efetuaram vendas para o cliente com código 10. 2. Listar o número do pedido, prazo de entrega, a quantidade e a descrição do produto com código 100. 3. Quais os vendedores (código e nome) fizeram pedidos para o cliente 'José da Silva'. 4. Listar o número do pedido, o código do produto, a descrição do produto, o código do vendedor, o nome do vendedor , o nome do cliente, para todos os clientes que moram em Sorocaba. 5. Listar o código do produto, a descrição, a quantidade pedida e o prazo de entrega para o pedido número 123. 6. Quais os cliente ( nome e endereço) da cidade de Itu ou Sorocaba tiveram seus pedidos tirados com o vendedor de código igual a 10. Parte 2.5 – Visões 1. Escreva uma visão que exiba o número do pedido, codcliente e prazo de entrega. 2. Escreva uma visão que liste todos os produtos com unidade = ‘KG’. 3. Escreva uma visão que liste todos os produtos com valor_unitário menor que a média. 4. Escreva uma visão que liste o numero total de pedidos feitos por vendedor. Esta visão deve conter o Codigo do vendedor, nome do vendedor e número total de pedidos. Qual das visões acima é atualizável? Parte 2.6 – Subconsultas1. Listar todos os clientes que moram na mesma cidade que 'João da Silva'. 2. Quais produtos tem valor unitário maior que a média. 3. Quais os clientes que só compraram com o vendedor com codigo 10 e com mais nenhum outro vendedor (fidelidade). 4. Quais vendedores não fizeram mais de 5 pedidos. 5. Quais os vendedores não fizeram nenhum pedido no mês de maio/2013 6. Listar o nome do vendedor que mais fez pedidos. 7. Listar o nome dos clientes e o número total de pedidos associados a cada cliente em ordem decrescente de vendas, isto é do cliente que mais tem pedidos para o que menos tem. 8. Excluir todos os itens dos pedidos feitos pelo cliente de código =2; 9. Alterar o valor unitário de todos os produtos sem vendas no ano de 2013 para menos 20%. Laboratório SQL - Lista Número 3 1- Criar uma sequência automática para o número do pedido da tabela pedido. Incluir duas tuplas usando este recurso. 2- Criar uma tabela de nome itens-bkp a partir da tabela de itens. Esta nova tabela deverá conter os campos: numero do pedido, código do produto para todos os pedidos do ano de 2011. 3- Listar o nome de todas as tabelas de seu banco de dados e o nome dos campos que são chave estrangeira nestas tabelas. 4- Listar todas as constraints da tabela pedido. 5- Listar o nome de todas as visões que você criou em seu banco de dados e o seu respectivo texto. 6- Listar todas as cláusulas CHECK e o nome da tabela a qual pertence. 7- Crie uma visão que exiba o nome da tabela, nome da coluna, tipo e a constraint (P,R,C) caso exista. ============================================================== Anexo Dicas: Para declarar valores default em tabelas: Na criação da tabela: Create table teste (codigo number(3) PRIMARY KEY, VALOR NUMBER(3) DEFAULT 10); SQL> INSERT INTO TESTE(CODIGO) VALUES (4); 1 linha criada. SQL> SELECT * FROM TESTE14; CODIGO VALOR ---------- ---------- 1 2 2 4 10 Após criação da tabela, ao adicionar uma nova coluna: ALTER TABLE TESTE14 ADD CAMPO NUMBER(3) DEFAULT 1; Tabela de Consulta Codconsulta� number(03) PK� � DataCon� Date� � TipoCon� char(01) P=Particular C=Convênio� � Codmedico� number(04) FK� � Codpaciente� number(04) FK� � Valconsulta� Number(7,2)� � Tabela de Médico Codmedico� number(4) PK� � Nommedico� varchar2(30)� � Cliente� � Cod_cliente (num,5) (PK)� � Nome_cliente (alfan.,30)� � Endereco (alfan.,25)� � Cidade (alfan.,20)� � Cep (alfan.,10)� � UF (alfan.,2)� � � � � � � � � � Pedido� � Num_pedido (num,5) (PK)� � Prazo_entrega (data)� � Cod_cliente (FK )� � Cod_vendedor (FK)� � Produto� � Cod_produto (num,3) (PK)� � Descrição (alfan.,20)� � unidade (alfan.,2)� � valor_unitario (6 inteiros e 2 decimais)� � Item_Pedido� � Num_pedido (FK)� � cod_produto(FK) PK� � Quantidade 5 inteiros� � Vendedor� � Cod_vendedor (num,5) (PK)� � nomevendedor (alfanum. 30 posições) � � faixa_comissão (2 inteiros e 2 decimais)� � salario_fixo ( 5 inteiros e 2 decimais)� � � � Equação de Junção Equação de Junção Para 3 tabelas Indica chave estrangeira Nome da restrição Nome da restrição Nome da restrição Nome da restrição Indica o formato 1-24
Compartilhar