Baixe o app para aproveitar ainda mais
Prévia do material em texto
UNIVERSIDADE FEDERAL DO PARÁ CENTRO DE CIÊNCIAS EXATAS E NATURAIS DEPARTAMENTO DE INFORMÁTICA Linguagem de Consulta SQL Carla Alessandra Lima Reis Belém, maio/1999 2 Sumário 1 Introdução a SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 1.1. Um Breve Histórico . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 1.2. Banco de Dados Relacional . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 2 Linguagem SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 2.1. Como definir o banco de dados com SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 2.1.1 Create Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 2.1.2 Create Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 Exercício 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 2.1.3 Alter Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 2.1.4 Drop Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 2.2. Manipulação com tabelas SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 2.2.1 Inserção de Tuplas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 2.2.2 Alteração de Tuplas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 2.2.3 Remoção de Tuplas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Exercício 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 2.3. Consultando Tabelas SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 2.3.1 Comando Select . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Exercício 3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 2.3.1.1 Comando SELECT - Cláusula DISTINCT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 Exercício 4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 2.3.1.2 Comando SELECT - Retorno de valores calculados . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Exercício 5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 2.3.1.3 Comando SELECT - Funções de Agregação . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 2.3.2 Cláusula WHERE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 2.3.2.1 Comparação [NOT] LIKE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 2.3.2.2 Comparação de nulos IS [NOT] NULL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 2.3.3 Cláusula UNION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 2.3.4.Cláusula INTERSECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 2.3.5 Claúsula MINUS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 2.4 Consultas envolvendo mais de uma tabela do BD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Exercício 6 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 2.5. Subconsultas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 2.5.1 Cláusula [NOT] IN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Exercício 7 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 2.5.2 Cláusula ANY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Exercício 8 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 2.5.3 Cláusula ALL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Exercício 9 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 2.5.4 Cláusula [NOT] EXISTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Exercício 10 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 2.5.5 Cláusula CONTAINS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 Exercício 11 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 2.6 Ordenando a exibição de tuplas - Cláusula ORDER BY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Exercício 12 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 2.7 Agrupando tuplas - Cláusulas GROUP BY e HAVING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Exercício 13 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 2.8 Comandos de atualização + comandos de consulta . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 3. Visões em SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 3 3.1 Consultas sobre Visões . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 3.2 Atualizações . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 3.3 Vantagens das visões . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 3.4 Algumas soluções para problemas com visões . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 Exercício 14 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 4. Autorizações de Acesso . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 4.1. Comando Grant . . . . . . . . . . . .. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 4.2. Comando Revoke . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 5. Mais Exercícios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 6 O Futuro do Padrão SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 7 Bibliografia . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 4 1 Introdução a SQL O objetivo deste curso é introduzir o aluno na tarefa de manipular bancos de dados através da linguagem SQL. SQL significa Structured Query Language (linguagem estruturada de consulta). Apesar do nome, fazer consultas a bancos de dados não é a única função de SQL. Ela é utilizada para criar tabelas, inserir, excluir e alterar dados do banco de dados, além de outras utilizações. A seguir é mostrado um breve histórico da linguagem seguido de uma rápida visão de bancos de dados relacionais. 1.1. Um Breve Histórico A linguagem SQL foi desenvolvida pela IBM em meados dos anos 70 como uma linguagem de manipulação de dados (DML - Data Manipulation Language) para suas primeiras tentativas de desenvolvimento de bancos de dados relacionais. A grande vantagem do SQL sobre modelos de dados anteriores é que as operações realizadas sobre os dados são especificadas numa linguagem não procedural e conjuntos de dados são manipulados com um único comando. Isto faz com que os programadores não tenham de navegar por uma estrutura complexa de banco de dados, reduzindo a quantidade de código necessário para acessar os dados. A SQL tornou-se de fato o padrão depois de 1986, quando o American National Standards Institute (ANSI), a organização responsável pelos padrões industriais nos Estados Unidos, endossou o SQL como linguagem padrão para os bancos de dados relacionais. Desde então, o SQL já sofreu duas atualizações oficiais, uma em 1989, e outra em 1992. A revisão de 1992, SQL-92 (ANSI X3.135-1992) é o padrão usado atualmente, mas desde 1993 há um trabalho sendo desenvolvido para atualizar o padrão de modo que este atenda às características das últimas versões de bancos de dados relacionais lançadas no mercado. O novo padrão SQL chama-se SQL3; o número 3 vem de 1993, o ano em que os trabalhos começaram. Em 1986, o ANSI publicou o primeiro padrão SQL, X3.135-1986. A International Organization for Standardization (ISO) pubicou um padrão tecnicamente idêntico, ISO 9075-1987, alguns meses depois em 1987. O padrão representava um denominador comum das implementações SQL existentes e consequentemente deixou de padronizar muitas características populares e necessárias da linguagem. Este padrão continha características de SQL de quatro linguagens de programação: COBOL, FORTRAN, Pascal e PL/I. Em 1989, tanto ANSI quanto ISO publicaram padrões substitutos (ANSI X3.135-1989 e ISO/IEC 9075:1989) que aumentaram a linguagem e acrescentaram uma capacidade opcional de integridade referencial, permitindo que projetistas de bancos de dados pudessem criar relacionamentos entre dados em diferentes partes do banco de dados. No mesmo ano, o ANSI adicionou ao padrão suporte para duas outras linguagens de programação, Ada e C. Em 1992, ISO e ANSI publicaram a terceira revisão do padrão SQL, o SQL92 (X3.135-1992 e ISO/IEC 9075:1992). Na mais nova versão, SQL3, a nova característica mais importante é a adição de características de orientação a objetos na linguagem. Devido ao sucesso da forma de consultar e manipular dados da SQL, dentro de um ambiente de banco de dados, a utilização da SQL foi se tornando ainda maior. Uma grande quantidade de SGBDs foi tendo como linguagem básica a SQL, a saber, SQL/DS e DB2 da IBM, ZIM, Oracle, RDB, Sybase, dentre outros. Cabe ressaltar que cada implementação de SQL possui uma adaptação da sintaxe para resolver determinados problemas, portanto, qualquer comando mostrado neste curso pode ser usado de forma diferente em um determinado SGBD. Recomenda-se a leitura do manual do fabricante para maiores informações sobre o uso da linguagem SQL em SGBDs comerciais. 1.2. Banco de Dados Relacional Para que haja um melhor entendimento da linguagem SQL é necessário abordar a evolução dos bancos de dados e de sua teoria. Sistemas de bancos de dados relacionais são aqueles baseados no modelo lógico 5 relacional, o qual foi proposto por E. F. Codd em 1970. A abordagem relacional está baseada no princípio de que as informações em uma base de dados podem ser consideradas como relações matemáticas e que estão representadas de maneira uniforme, através do uso de tabelas bidimensionais. Todos os dados de um BD relacional estão armazenados em relações (ou tabelas). Cada tabela possui atributos (ou campos), os quais são armazenados em colunas da tabela. As linhas guardam informações sobre uma entidade (registro). Alguns atributos da tabela são chave primária da mesma. Isto significa que não existem duas linhas iguais numa tabela. Cada linha pode ser identificada univocamente pela sua chave primária (por exemplo matrícula de aluno). A seguir é mostrada a tabela de alunos que tem como atributos Matrícula, Nome, Endereço e Cod_Curso. A chave primária da tabela é o atributo matrícula. Alunos Matrícula Nome Endereço Cod_Curso 9100807 José xxxx 88 9200789 Maria yyy 77 A seguir é mostrada a tabela de cursos com os atributos Cod_Curso e Nome. Cod_Curso é a chave primária. Cod_Curso Nome 77 Computação 88 Eng. Civil Note que cada aluno da tabela de alunos possui um valor de Cod_Curso que existe na tabela de cursos. Isso significa que se quisermos saber o nome do curso de um aluno, basta sabermos o código do curso do aluno e procurar pelo mesmo código na tabela de cursos. Quando existe essa ligação entre tabelas, o atributo que faz a ligação (no caso, Cod_Curso) é considerado chave estrangeira ou chave externa. Isto implica em várias regras de verificação num SGBD, pois um aluno não pode ter um código de curso que não existe na tabela de cursos, para citar um exemplo. Existem várias regras definidas por Codd para caracterizar um SGBD relacional. Em resumo, pode-se dizer que todos os dados do SGBD devem ser armazenados em tabelas e que a manipulação dessas tabelas através de inclusão, exclusão, alteração e consultas deve gerar novas tabelas 6 2 Linguagem SQL Atualmente, a linguagem SQL assume um papel muito importante nos sistemas de gerenciamento de banco de dados (SGBDs), podendo ter muitos enfoques. Através de comandos SQL, os usuários podem montar consultas poderosas sem a necessidade de criação de um programa, ou ainda utilizar comandos SQL embutidos em programas de aplicação que acessam os dados armazenados. O DBA, pessoa responsável pela administração de banco de dados pode realizar suas tarefas através de SQL. Da mesma forma, a linguagem pode ser usada como linguagem cliente/servidor, linguagem para banco de dados distribuídos e caminho de acesso a outros bancos de dados ou outras plataformas. Devido ao fato de possuir várias aplicações, a linguagem SQL provê suporte a várias funções de um SGBD. Ela consiste de: C DDL (linguagem de definição de dados), onde os dados a serem armazenados são definidos e estruturados; C DML (linguagem de manipulação de dados), que permite a inclusão, remoção, seleção ou atualização de dados armazenados no banco de dados; C Controle de acesso, permitindo proteção dos dados de manipulações não autorizadas; C Restrições de Integridade, que auxiliam no processo de definição da integridade dos dados, protegendo contra corrupções, inconsistências e falhas do sistema decomputação. Além dessas características principais, ainda podemos citar o suporte a visões, onde são especificadas as consultas disponíveis através de tabelas virtuais e especificação de transações, para garantia do compartilhamento dos dados. 2.1. Como definir o banco de dados com SQL Para definir o banco de dados utilizamos os comandos de definição de dados de SQL, que a transformam em DDL. 2.1.1 Create Database O comando create database não está presente em todos as versões de SQL, mas serve para definir um nome para o banco de dados a ser criado. Por exemplo: create database controle_bancário 2.1.2 Create Table O comando create table permite a definição de uma relação ou tabela com restrições de integridade de entidade e referencial dentro do banco de dados definido. Para isso ele contém as cláusulas: primary key: indica os atributos que formam a chave primária; unique key: indica os atributos que formam a chave candidata; foreign key: indica os atributos que formam a chave estrangeira e o nome da relação referida pela chave estrangeira 7 Formato do comando create table: CREATE TABLE nome_tabela (nome_atributo tipo_de_dado [NOT NULL],..., primary key (nome_atributo1, nome_atributo2,...) foreign key (nome_atributo) references nome_relação) Ex: BD bancário: create table cliente ( cliente_nome char(20) not null, rua char(30), cidade char(30, primary key (cliente_nome)) create table agência ( agencia_nome char(15) not null, ativos integer, cidade char(30), primary key (agencia_nome)) create table depósito ( agencia_nome char(15) not null, conta_numero char(10), cliente_nome char(20) not null, saldo integer, primary key (conta_numero, cliente_nome) foreign key (agencia_nome) references agencia, foreign key (cliente_nome) references cliente) 8 Tipos de dados suportados pelo Personal Oracle 7: Tipo Comentários CHAR dados alfanuméricos de até 255 caracteres. Acrescenta espaços depois do valor para complementar o tamanho da coluna DATE Inclui século, ano, mês, dia, hora, minuto e segundo LONG strings alfanuméricas de tamanho variado de até 2 gigabytes (memo) LONG RAW Dados binários até 2 gigabytes (BLOB) - gráficos, sons, vídeo, etc. NUMBER Números positivos ou negativos com ou sem ponto decimal RAW Dados binários até 255 bytes RAWID String hexadecimal representando o endereço único de uma linha em uma tabela VARCHAR2 Dados alfanuméricos de tamanho variado. Até 2000 caracteres. Exercício 1: Criar as tabelas sql para os esquemas abaixo: a) BD hospitalar médicos(codm, nomem, idade, especialidade) pacientes(codp, nomep, idade, problema) consultas (codm, codp, data, hora) b) Zoológico: animais (codA, tipo, nome, peso, idade) jaulas (nro, area) alocação (nro_jaula, codA, data_entrada) zeladores (codZ, nomez, tempo_serviço) manutenção (codZ, nro, horas) 2.1.3 Alter Table O comando Alter Table permite modificar a estrutura de uma tabela já definida. Pode-se adicionar uma coluna à tabela ou modificar uma coluna existente. A sintaxe do comando é mostrada a seguir: 9 alter table nome_tabela [add nome_coluna tipo_dados; | modify nome_coluna tipo_dados;] O comando a seguir altera a estrutura da tabela agência para aumentar o tamanho do campo cidade: alter table agência modify cidade char(40); A seguir é acrescentado o atributo país na tabela agência: alter table agência add país char(20) 2.1.4 Drop Table Para remoção de uma tabela, usa-se o comando drop table, o qual remove todas as informações sobre a relação (inclusive do catálogo) Ex: drop table agência 2.2. Manipulação com tabelas SQL Para manipular com tabelas do banco de dados, podem ser usados comandos de inserção, exclusão, alteração e consulta de tabelas. Os comandos de atualização de banco de dados com SQL são mostrados a seguir: 2.2.1 Inserção de Tuplas insert into nome_tabela values <campos> Ex: Inserção do cliente João: insert into cliente values (“João”, “A”, “Porto Alegre”) Obs: valores nulos podem estar presentes em alguns atributos insert into cliente values (“José”, null, “Santa Cruz do Sul”) 10 2.2.2 Alteração de Tuplas update nome_tabela set alteração [where condição] Exemplos: Alterar a tabela de depósitos para acrescentar 5% de juros em todos os saldos: update deposito set saldo = saldo * 1.05 Alterar a cidade do cliente João para “Belém” update cliente set cidade = “Belém” where cliente_nome = “João” Acrescentar 6% de juros às contas com saldo maior que 10000: update deposito set saldo = saldo * 1.06 where saldo > 10000 2.2.3 Remoção de Tuplas delete from nome_tabela [where condição] Ex: Remover todos os depóstos da conta de Smith: delete from deposito where cliente_nome = “Smith” Remover todas as tuplas da tabela cliente: delete cliente 11 Exercício 2: Remover as agências da cidade de Belém 2.3. Consultando Tabelas SQL Toda consulta SQL é baseada no modelo relacional, ou seja, retorna uma tabela como resposta. Para definir uma consulta, basta informarmos o que queremos e não como fazê-lo, pois SQL é uma linguagem não- procedural. 2.3.1 Comando Select Estrutura básica de uma consulta SQL: select <lista de atributos> from <lista de tabelas> [where <condição>] Select seleciona as colunas que deverão compor a tabela resultante. Os nomes dos atributos devem ser os mesmos definidos no banco de dados. É uma cláusula obrigatória em qualquer consulta. From indica as tabelas do banco de dados que devem ser consultadas. Também é obrigatória em qualquer consulta. Where indica uma condição pela qual os dados serão filtrados. A condição especificada deve retornar verdadeiro ou falso. Para cada linha da tabela, o interpretador SQL verifica se atende a condição especificada nesta cláusula e adiciona a linha na resposta caso seja verdadeira a avaliação. É uma cláusula opcional de consulta. - Observações: C Caso a cláusula where seja omitida, o predicado P (condição) é verdadeiro; C A lista de atributos A , A , ..., A pode ser substituida por um asterisco (*) para selecionar todos os1 2 n atributos de todas as relações da cláusula from; C SQL forma o produto cartesiano (relaciona cada linha da primeira tabela com todas as linhas da outra tabela) das relações chamadas na cláusula from, verifica a condição da cláusula where, e então, projeta o resultado para os atributos da cláusula select; C O resultado de uma consulta SQL é uma tabela. C Operadores lógicos: and, or e not C Operadores de comparação: >, >=, <, <=, = , != (ou <>) Exemplo: Dada a seguinte tabela de clientes: cliente_nome rua cidade José Pe. Eutíquio Belém Maria Pres. Vargas Fortaleza 12 Joana Alm Barroso Belém Rogério Praia de Belas Porto Alegre Para consultar os clientes (todos os atributos) que moram em Belém: select * from clientes where cidade = “Belém” O resultado seria: cliente_nome rua cidade José Pe. Eutíquio Belém Joana Alm Barroso Belém Exercício 3: Consultar no BD hospitalar: a) O nome dos médicos com idade > 22 ou com especialidade diferente de traumatologia; b) o nome e o problema dos pacientes com menos de 24 anos; c) as consultas para o dia 26/09/96 após as 15hs. 2.3.1.1 Comando SELECT - Cláusula DISTINCT A cláusula DISTINCT é usada para remover as duplicações de linhas no resultado da consulta. select distinct cliente_nome from depósito Ex: Se a tabela depósito tiver os dados: O resultado da consulta será: Agência_ conta_ cliente_n saldo cliente_nome nome ome número Ag1 001 João 100 Ag2 002 José 200 Ag3 003 Smith 150 Ag3 003 José 350 João José Smith 13 Exercício 4: No BD hospitalar: a) Buscar todas as especialidades dos médicos; b) Buscar todas as datas de consultas com horário após as 15hs c)Buscar todas as idades dos médicos 2.3.1.2 Comando SELECT - Retorno de valores calculados O comando Select permite retornar expressões aritméticas como resultado da consulta. Cada atributo especificado na cláusula Select também é uma expressão, e como tal pode ser manipulada. Essa é uma forma de mostrar resultados que na verdade não estão armazenados no banco de dados. Para atributos numéricos, podemos utilizar +, -, *, /. E para atributos do tipo string pode-se utilizar concatenação ||. Além desses operadores, ainda existem outras funções que serão mostradas na seção a seguir. Exemplo: Buscar o código do médico e os dias consecutivos de suas consultas: select codm, data + 1 from consultas Exercício 5: buscar a idade em meses de todos os pacientes e seus nomes Obs: Pode-se dar nomes novos as colunas resultantes de tabelas em algumas implementações de sql. Por exemplo: select codm, data + 1 dia_consecutivo from consultas Esta consulta resultaria em uma tabela com os atributos codm e dia_consecutivo. 2.3.1.3 Comando SELECT - Funções de Agregação As funções de agregação operam sobre um conjunto de linhas e algumas são mostradas a seguir: a) Count: contador de ocorrências Ex: 1) total de linhas (tuplas) da tabela de pacientes: select count (*) from pacientes 2) Quantas consultas para o dia 26/05/96 existem? 14 select count (data) {não conta nulos} from consultas where data = “26/05/96" b) Sum: somador de valores de atributos numéricos Ex: Soma dos saldos do dos clientes da agência Ag1: select sum (saldo) from depósito where agência_nome = “Ag1" c) Avg: Média de valores de atributos numéricos Ex: Obter média de idade dos médicos ortopedistas: select avg (idade) from médicos where especialidade = “ortopedia” d) Max / Min: Maior/Menor valor de um atributo Ex: Maior e menor saldo de contas em depósito: select max (saldo), min (saldo) from depósito 2.3.2 Cláusula WHERE Na cláusula WHERE são especificadas as condições de busca da tabela. Podem ser usados vários operadores relacionais, como =, >, <, <=, >=, <> e ainda operadores de comparação de padrões em valores, como LIKE e comparação de nulos. 2.3.2.1 Comparação [NOT] LIKE Permite a definição de padrões de busca * Padrões possíveis: a) Like “c*” - o valor do atributo inicia com o caracter “c” b) Like “*c” - o valor do atributo termina com “c” c) Like “*c*” - o valor do atributo possui o caracter “c” no meio da cadeia 15 Obs: algumas implementações de SQL usam % no lugar de * Ex:Buscar o nome de todos os pacientes que começam com a letra C: select nomep from pacientes where nomep like “C*” Buscar o código do médico, paciente e horário para todas as consultas marcadas para o ano de 1996: select codm, codp, hora {ou *} from consultas where data like “*96" 2.3.2.2 Comparação de nulos IS [NOT] NULL * Permite o teste sobre valores nulos de atributos Ex: buscar dados dos médicos que não têm especialidade: select * from medicos where especialidade is null 2.3.3 Cláusula UNION * Permite a união de duas tabelas compatíveis (equivalente ao operador c da álgebra relacional; Ex: buscar o nome de todas as pessoas cadastradas no hospital: select nomem from medicos union select nomep from pacientes Buscar todos os clientes com empréstimo ou depósito na agência Ag1: (select cliente_nome from depósito 16 where agencia_nome = “Ag1") union (select cliente_nome from empréstimo where agencia_nome = “Ag1") obs: a cláusula Union retira as repetições. Em algumas versões de SQL, pode-se utilizar UNION ALL, que retorna todas as tuplas das tabelas unidas, mesmo havendo repetição. 2.3.4.Cláusula INTERSECT Permite a interseção de duas tabelas compatíveis (operador 1 da álgebra relacional), retornando apenas as linhas que pertencem às duas tabelas Ex: buscar todos os clientes da Ag1 que possuem uma conta e empréstimo: (select distinct cliente_nome from depósito where agencia_nome = “Ag1") intersect (select distinct cliente_nome from empréstimo where agencia_nome = “Ag1") 2.3.5 Claúsula MINUS * Permite a subtração de duas tabelas compatíveis (operador - da álgebra relacional), retornando apenas as linhas que pertencem à primeira tabela, com exceção das que aparecem na segunda tabela. Ex: Buscar todos os clientes da Ag1 que possuem conta mas não possuem empréstimo: (select distinct cliente_nome from depósito where agencia_nome = “Ag1") minus (select distinct cliente_nome from empréstimo where agencia_nome = “Ag1") 17 2.4 Consultas envolvendo mais de uma tabela do BD Quando a resposta de uma consulta precisa ser buscada em várias tabelas do banco de dados, então é necessário especificar quais tabelas serão usadas na consulta SQL. Para isso é utilizada a cláusula FROM. Como SQL é baseada no modelo relacional, a forma de juntar tabelas é através de produto cartesiano das mesmas. Por exemplo, se quisermos usar duas tabelas, a de médicos e a de consultas, mostradas abaixo, o resultado é uma tabela que contém relacionamentos entre todos os médicos e todas as consultas. Obviamente que várias tuplas do resultado não são úteis, portanto temos que especificar quais as tuplas úteis através da cláusula WHERE. médicos(codm, nomem, idade, especialidade) codm nomem idade especialidade M1 João 33 traumatologia M2 José 23 clínica geral M3 Maria 30 obstetrícia consultas (codm, codp, data, hora) codm codp data hora M1 P1 12/12/1997 15 M1 P2 13/12/1997 14 M2 P1 05/10/1997 8 Se a consulta fosse: select * from médicos, consultas O resultado seria: codm nomem idade especialidade codm codp data hora M1 João 33 traumatologia M1 P1 12/12/1997 15 M1 João 33 traumatologia M1 P2 13/12/1997 14 M1 João 33 traumatologia M2 P1 05/10/1997 8 M2 José 23 clínica geral M1 P1 12/12/1997 15 M2 José 23 clínica geral M1 P2 13/12/1997 14 M2 José 23 clínica geral M2 P1 05/10/1997 8 18 M3 Maria 30 obstetrícia M1 P1 12/12/1997 15 M3 Maria 30 obstetrícia M1 P2 13/12/1997 14 M3 Maria 30 obstetrícia M2 P1 05/10/1997 8 O resultado expressa valores que nem sempre são verdadeiros. Por exemplo, existe uma linha com médico M1 e ao lado, uma consulta do médico M2. Se quisermos saber os dias de consulta do médico João, temos que escolher somente as linhas onde o codm em médicos é igual a codm em consultase o nome do médico seja João. Pode-se perceber que para saber datas de consultas basta olharmos na tabela de consultas, mas esta somente contém o código dos médicos. Portanto se quisermos avaliar o nome do médico, temos que buscar na tabela de médicos. Daí a necessidade de juntar as tabelas pelo código do médico. A esta operação damos o nomes de JOIN, ou seja, junção de valores relacionados. A consulta SQL que obtém tal resposta é: select data from médicos, consultas where médicos.codm = consultas.codm and médicos.nomem = “João" O resultado desta consulta é data 12/12/1997 13/12/1997 Na cláusula FROM, podemos definir quantas tabelas forem necessárias. A única restrição é de que as chaves estrangeiras devem ser igualadas para que os dados retornados sejam verdadeiros. Ex: Buscar o nome dos médicos com consulta marcada para o dia 22/05/96: select medicos.nomem from medicos, consultas where consultas.data = “22/05/96" and medicos.codm = consultas.codm * Pode-se ter variáveis de tupla associadas a cada tabela declarada na cláusula from Ex: select M.nomem, P. nomep from consultas C, medicos M, pacientes P where C.codm = M.codm and C.codp = P.codp and 19 C. hora = 15 Exercício 6: Buscar nome e idade dos médicos com consulta marcada com a paciente Ana. 2.5. Subconsultas Na linguagem SQL ANSI podem ser definidas subconsultas através das cláusulas IN, ANY, ALL, EXISTS e CONTAINS.Entretanto, somente podem ser definidas subconsultas na cláusula WHERE.. 2.5.1 Cláusula [NOT] IN Esta cláusula estabelece a relação de pertinência entre conjuntos (tabelas). Sua avaliação retorna um valor booleano. * sintaxe: where <atributo ou expressão> [not] in (<subconsulta>) Ex: Buscar nome de todos os pacientes sem consulta marcada: select nomep from pacientes where codp not in (select codp from consultas) A subconsulta (select codp from consultas) é avaliada primeiro, retornando uma tabela com todos os códigos de pacientes que possuem consulta marcada. Em seguida a consulta principal é processada selecionando os nomes dos pacientes cujo código não se encontra no resultado da subconsulta. Pode-se observar que essa consulta também poderia ser descrita através da cláusula minus, subtraindo os pacientes com consulta do conjunto de todos os pacientes cadastrados (mostre como!). Uma outra observação importante é que um item (codp) é comparado a um conjunto (o resultado da subconsulta). A cláusula IN comporta-se como o operador pertence da matemática, comparando um elemento a um conjunto de elementos do mesmo tipo. A subconsulta não poderia, por exemplo, retornar código de médico ao invés de pacientes, ou seja, o elemento comparado tem que ser do mesmo tipo dos elementos do conjunto. Ex: Buscar nome e problema dos pacientes com idade > 21 e que também são médicos: select nomep, problema from pacientes where idade > 21 and nomep in (select nomem from medicos) 20 Exercício 7: Buscar todos os clientes da Ag1 que possuem conta e empréstimo (com cláusula in): Buscar todos os clientes da Ag2 que possuem conta mas não possuem empréstimo. 2.5.2 Cláusula ANY Esta cláusula permite outras formas de comparação elemento-conjunto. Ao invés de somente verificar se um elemento pertence a um conjunto, podemos verificar se um elemento é maior, menor, diferente ou até igual a qualquer elemento do conjunto resultante da subconsulta. = any (<subconsulta>) {mesmo que in} > any (<subconsulta>) {verdadeiro se o atributo comparado for maior do que algum valor de atributo das tuplas resultantes da subconsulta} < any (<subconsulta>) <> any (<subconsulta>) Ex: Buscar o nome de todos os pacientes, exceto o mais idoso: select nomep from pacientes where idade < any (select idade from pacientes) Exercício 8: Buscar o nome e a idade do médico mais jovem. 2.5.3 Cláusula ALL Esta cláusula é utilizada quando uma condição deve ser satisfeita para todos os elementos de um conjunto. = all (<subconsulta>) {igual a todos} > all (<subconsulta>) {maior do que todos} < all (<subconsulta>) {menor que todos} <> all (<subconsulta>) {diferente de todos (= not in)} Ex: 1) Buscar o nome dos clientes que tem saldo menor que todos os clientes com depósto na agência Ag3: select cliente_nome 21 from deposito where saldo < all (select saldo from deposito where agencia_nome = “Ag3") 2)Buscar o nome dos médicos com consultas marcadas para horários mais tarde que todas as consultas da médica Maria select nomem from medicos m, consultas c where m.codm = c.codm and c.hora > all (select hora from consultas c1, medicos m1 where c1.codm = m1.codm and m1.nomem = “Maria”) Exercício 9: Buscar os dados dos pacientes com consultas marcadas para horários anteriores a todos os horários marcados para o dia 22/06/96 2.5.4 Cláusula [NOT] EXISTS Esta cláusula testa se uma subconsulta tem alguma tupla em seus resultados. Se for usado o NOT antes de EXISTS, o resultado é o contrário. Ex: Podemos reescrever a consulta: Encontre os clientes que possuem conta e empréstimo na Ag1. select cliente_nome from cliente where exists (select * from deposito where deposito.cliente_nome = cliente.cliente_nome and agencia_nome = “Ag1") and exists (select * from emprestimo where emprestimo.cliente_nome = cliente.cliente_nome and agencia_nome = “Ag1") 22 Ex2: Considere novamente a consulta: “Encontre todos os clientes que têm uma conta em todas as agências localizadas em “StaCruz”. (usando not exists e minus) select distinct S.cliente_nome from deposito S where not exists ((select agencia_nome from agencia where agencia_cidade = “StaCruz”) minus (select T.agencia_nome from deposito T where S.cliente_nome = T.cliente_nome)) Exercício 10 (BD hospitalar): a)Buscar o nome de todos os médicos com consulta marcada. b)Buscar o nome do médico mais jovem. 2.5.5 Cláusula CONTAINS A cláusula CONTAINS foi introduzida originalmente no sistema R, e não aparece no padrão ANSI pois o processamento dessa cláusula é muito custoso. As construções in, any e all permitem-nos testar um valor simples contra membros de um conjunto inteiro. Uma vez que o select gera um conjunto de tuplas, podemos querer comparar conjuntos para determinar se um conjunto contém todos os membros de algum outro conjunto. Tais comparações são feitas em SQL usando contains e not contains. Em outras palavras, esta cláusula é equivalente ao operador contém d da matemática Ex: Considere a mesma consulta do Ex2 acima. Para cada cliente, precisamos ver se o conjunto de todas as agências na qual um cliente possui uma conta contém o conjunto de todas as agências de “StaCruz”. A consulta pode ser escrita assim: select distinct S.cliente_nome from deposito S where (select T.agencia_nome from deposito T where S.cliente_nome = T.cliente_nome) contains (select agencia_nome from agencia where agencia_cidade = “StaCruz”) 23 Exercício 11: identifique outras possíveis consultas que possam ser reescritas com a cláusula contains. 2.6 Ordenando a exibição de tuplas - Cláusula ORDER BY Esta cláusula permite a ordenação do resultado da consulta. É utilizada após a cláusula Where. * Sintaxe: ORDER BY <Lista de atributos> [desc] [asc] {desc - ordem decrescente, asc - ordem crescente} Ex: Buscar os nomes dos clientes em ordem alfabética com empréstimo na agência “Ag3": select distinct cliente_nome from emprestimo where agencia_nome = “Ag3" order by cliente_nome Ex2: Listar todos os empréstimos na ordem decrescente de quantia e os empréstimos com a mesma quantia devem ser ordenados pelo número do empréstimo. select * from emprestimo order by quantia desc, emprestimo_numero asc Exercício 12: Buscar os dados de todas as consultas do paciente Carlos, ordenadas de forma decrescente pela hora da consulta. 2.7 Agrupando tuplas - Cláusulas GROUP BY e HAVING * Group by: agrupa partes do resultado de uma consulta, a partir do qual é possível utilizar funções de agregação (avg, min, max, count, sum); * Having: Especifica condições para a formação de um grupo. Só existe associado à cláusula group by. * As condições só podem envolver os atributos a serem buscados ou alguma função de agregação. Exemplos: 24 1) Encontre o saldo médio de conta em cada agência: select agencia_nome, avg(saldo) from deposito group by agencia_nome obs: as duplicatas são mantidas. 2) Encontre o número de depositantes de cada agência: select agencia_nome, count(distinct cliente_nome) from deposito group by agencia_nome obs: evita-se as duplicações com a cláusula distinct. 3) Buscar as agências nas quais a média dos saldos é maior do que 1200: select agencia_nome, avg(saldo) from deposito group by agencia_nome having avg(saldo) > 1200 4) Obter as agências com maior média de saldos: select agencia_nome from deposito group by agencia_nome having avg(saldo) >= all (select avg(saldo) from deposito group by agencia_nome) obs: não é possível usar max(avg(...)). 5) Encontre a média dos saldos de todos os depositantes que vivem em “StaCruz” e têm pelo menos 3 contas. (uso de where e having juntos) select avg(saldo) from deposito, cliente where deposito.cliente_nome=cliente.cliente_nome and cliente.cidade= “StaCruz” 25 group by deposito.cliente_nome having count(distinct conta_numero) >= 3 obs: O predicado na cláusula where é aplicado antes do predicado de having. Exercício 13: 1) buscar todas as datas de consultas e o total de consultas para esta data. 2) buscar somente as datas e o total de consultas para horários após as 14 horas 3) buscar somente as datas e o total de consultas para as datas onde haja mais de uma consulta marcada. 4) buscar, para a tabela de médicos, todas as idades e o total de médicos com a mesma idade. 5) buscar o nome dos médicos com mais de uma consulta marcada. 2.8 Comandos de atualização + comandos de consulta Expressões de consulta podem estar associadas aos comandos de atualização de dados, para melhor restringir o universo de tuplas a serem atualizadas. Os comandos usados são os já apresentados delete e update. Exemplos: 1) Remover todas as contas em agências localizadas em Canoas delete deposito where agencia_nome in (select agencia_nome from agencia where agencia_cidade = “Canoas”) 2) Remover todas as consultas do médico “João”: delete consultas where codm in ( select codm from medicos where nomem = “João”) 3) Acrescentar 5% de juros sobre contas cujos saldos sejam maiores do que a média: update deposito set saldo = saldo * 1.05 where saldo > (select avg (saldo) from deposito) 4) Passar para as 19hs todas as consultas da paciente Ana 26 update consultas set hora = “19:00" where codp in (select codp from pacientes where nomep = “Ana”) 27 3. Visões em SQL Um banco de dados é composto de várias tabelas relacionadas. Se quisermos obter qualquer informação, devemos citar essas tabelas nas consultas. Algumas consultas são bastante utilizadas no dia-a-dia e são complexas de escrever, devido ao uso de subconsultas. Muitas vezes os usuários não têm conhecimento adequado para formular tais consultas. Nesse caso, o DBA poderia colocar algumas consultas prontas à disposição, as quais seriam acessadas com consultas simples. As visões são tabelas virtuais derivadas das tabelas do banco de dados e são úteis para garantir segurança de acesso ao BD, disponibilização de tabelas que melhor se adequam às necessidades de uma aplicação e facilitade de acesso aos dados sem ter que formular consultas complexas. Exemplos: a) Um funcionário do hospital não deve ter acesso a todos os dados pessoais de um paciente, somente ao seu código e nome; b) Pode ser interessante vincular os dados de um médico aos dados de suas consultas A criação de visões em SQL obedece a seguinte sintaxe: create view <nome_visão> as <expressão_consulta> obs: a visão criada não é executada, e sim, armazenada no catálogo. Porém, para o usuário é como se tivesse uma visão <nome_visão> armazenada no BD. Para remover uma visão, usa-se o comando drop view <nome_visão> obs: a visão especificada é eliminada (a definição é removida) e todas as visões definidas em termos desta visão também são automaticamente anuladas. Porém os dados continuam existindo, já que a visão é uma tabela virtual. Exemplos: a) A seguir é criada uma visão chamada DadosPac, que, do ponto de vista do usuário comum, corresponde a uma tabela como outra qualquer. Esta visão retorna somente o código e o nome de todos os pacientes do hospital. Se um funcionário desse hospital tiver acesso somente a tabela (visão) DadosPac, então ele não verá o problema do paciente, o qual é um atributo da tabela pacientes. create view DadosPac as select codp, nomep 28 from pacientes b) A visão a seguir, chamada MedCons contém os nomes dos médicos e suas datas e horas de consulta. Observe que para obter essa informação, precisamos das tabelas de médicos e consultas e da junção entre os atributos codm das duas tabelas. A visão facilita a consulta já que, após a sua criação, basta pedirmos para ver todos os dados da tabela MedCons que automaticamente a consulta da visão é processada, sem termos que defini-la novamente. create view MedCons as select nomem, data, hora from medicos, consultas where medicos.codm = consultas.codm Obs: Uma vez definida uma visão, qualquer operação de consulta ou atualização pode ser aplicada sobre ela. Operações realizadas sobre uma visão se refletem diretamente sobre as tabelas físicas das quais ela deriva. 3.1 Consultas sobre Visões Nas operações de recuperação (select) o processo de conversão das operações em visão para operações em tabelas básicas é bem direto e funciona bem. Exemplos: a) o funcionáro do hospital deseja buscar o nome de todos os pacientes cadastrados que começam com a letra R: select nomep from DadosPac where nomep like “R*” O sistema (tradutor) converte esta consulta em: select nomep from pacientes where nomep like “R*” b) Buscar o nome dos médicos com consulta marcada para horários após as 18hs no mês de outubro: select nomem from MedCons 29 where hora > 18 and data like “*/10/94" O sistema (tradutor) converte esta consulta em: select nomem from medicos, consultas where medicos.codm=consultas.codm and hora > 18 and data like “*/10/94" 3.2 Atualizações * Operações de inserção, atualização e remoção de tuplas (insert, update e delete) em uma visão muitas vezes geram problemas. Nem todas as visões são atualizáveis Situação 1: Inserção de tuplas na visão MedCons Problemas: - violação da regra de integridade de entidade; (a chave primária não está na visão) - perde-se o relacionamento entre médicos e consultas Situação 2: Visão que relaciona total de consultas de um determinado paciente. create view ConsPac as select codp, count (*) from consultas group by codp Problemas: - inserção nesta visão viola a regra de integridade de entidade e não se pode inserir no campo count(*); - Não se pode atualizar um atributo calculado. 3.3 Vantagens das visões - Elas fazem com que o mesmo dado seja visto por diferentes usuários de diferentes formas (ao mesmo tempo) - A percepção do usuário é simplificada; - É óbvio que o mecanismo da visão possibilita aos usuários centrarem-se unicamente nos dados que lhes 30 são importantes e ignorarem o resto. O que talvez não seja tão óbvio é que, pelo menos na recuperação (select), tal mecanismo também possa simplificar consideravelmente as operações de manipulação de dados feitas pelo usuário. - Segurança automática para os dados ocultos Dados ocultos são aqueles não visíveis através de determinada visão. Ficam claramente protegidos por meio desta visão específica. Assim, obrigar os usuários a acessar o banco de dados através de visões é um mecanismo simples, porém eficaz de controle de autorização. 3.4 Algumas soluções para problemas com visões - Impedir operações de atualização sobre visões - As tuplas de uma visão devem corresponder a tuplas que tenham condições de serem identificadas nas tabelas físicas das quais ela deriva; - Cada atributo de uma visão deve corresponder a um atributo distinto e identificável de alguma das tabelas físicas das quais ela deriva. Exercício 14: Dada o BD de peças e fornecedores: fornecedores (codf, nomef, cidade) peças (codp, nomep, peso, cor) fornecem (codf, codp, qtde) e dada a definição de visão: create view peças_pesadas as select codp, peso, cor from peças where peso > 20 Demonstrar a operação executada (isto é, a forma convertida) do que segue: a) select * from peças_pesadas where cor = “preto” b) update peças_pesadas set cor = “branco” where peso = 30 c) insert into peças_pesadas values (“P4", 52, “vermelha”) 31 4. Autorizações de Acesso Muitos SGBDs relacionais podem ser acessados por diversos usuários. Cada usuário tem uma determinada necessidade em relação aos dados armazenados. De acordo com o projeto do bancode dados, alguns usuários só podem consultar alguns dados, outros podem atualizar, outros podem inserir, etc. Para que o dado fique protegido do uso indevido de qualquer usuário, a linguagem SQL permite a definição dos privilégios que cada um pode ter em relação às tabelas criadas no banco de dados. Os privilégios garantem a segurança e integridade dos dados, bem como a responsabilidade de cada usuário sobre seus dados específicos. 4.1. Comando Grant Este comando garante privilégios para os usuários sobre tabelas/visões. GRANT <privilégios> ON <nome_tabela/view> TO <usuários> <privilégios> podem ser: Select: pode executar uma consulta sobre a tabela Insert: pode executar uma inserção sobre a tabela Delete: pode apagar registros da tabela Update: pode modificar registros da tabela All Privileges/all: pode executar qualquer operação sobre a tabela <usuário>: nome do usuário que vai receber os privilégios. Deve ser um nome cadastrado dentro do ambiente. PUBLIC: concede os privilégios especificados a todos os usuários do ambiente Exemplos: - grant select on medicos to Paulo permite somente consultas do usuário Paulo na tabela de médicos - grant select, insert, update on consultas to Mary concede ao usuário Mary os privilégios de seleção, inserção e atualização sobre a tabela consultas - grant all privileges on DadosPac to public permite todos os privilégios a todos os usuários sobre a tabela (neste caso, visão) DadosPac. 32 4.2. Comando Revoke Este comando revoga os privilégios de acesso aos usuários. REVOKE <privilégios> ON <nome_tabela/view> FROM <usuários> Exemplos: - revoke select on medicos from Paulo - revoke all on DadosPac to public - revoga todos os direitos sobre a visão DadosPac 33 5. Mais Exercícios 1) BD formado pelas seguintes tabelas: medicos (codm, nomem, idade, especialidade) pacientes (codp, nomep,idade, problema) consultas (codm, codp, data, hora) a) Mostrar todos os dados da tabela de consultas. SELECT * FROM CONSULTAS b) Mostrar os dados dos médicos classificados por ordem de código SELECT * FROM MEDICOS ORDER BY codm ASC c) Obter os nomes e códigos de todos os médicos cirurgiões SELECT nomem, codm FROM MEDICOS WHERE especialidade= “cirurgia” d) fornecer os nomes e códigos de todos os médicos e seus respectivos dias de consulta SELECT nomem, data FROM medicos, consultas WHERE medicos.codm = consultas.codm e) fornecer os nomes dos médicos que possuem alguma consulta marcada com o paciente P4 Várias soluções possíveis: e.1) SELECT nomem FROM medicos WHERE codm in (SELECT codm FROM consultas WHERE codp= “P4") e.2) SELECT nomem FROM medicos WHERE “P4" in ( SELECT codp 34 FROM consultas WHERE codm = medicos.codm) e.3) SELECT nomem FROM medicos WHERE EXISTS (SELECT * FROM consultas WHERE codm = medicos.codm and CODP = “P4") f) mostrar os nomes dos médicos que não têm consulta marcada com a paciente P4 SELECT nomem FROM medicos WHERE NOT EXISTS (SELECT * FROM consultas WHERE codp = “P4" and codm = medicos.codm) g) Mostrar os nomes dos médicos que não tem consulta marcada com a paciente Maria SELECT nomem FROM medicos WHERE codm IN (SELECT codm FROM consultas WHERE codp NOT IN ( SELECT codp FROM pacientes WHERE nome = “Maria”) h) Mostrar o código dos pacientes que tem consulta marcada para o dia 23/09/96 ou com médicos pediatras. (SELECT codp FROM consultas WHERE data = “23/09/96") UNION (SELECT codp FROM consultas 35 WHERE codm IN (SELECT codm FROM medicos WHERE especialidade = “pediatra”)) obs: poderia ter sido utilizado o OR ao invés de UNION na primeira subconsulta? i) Mostrar os nomes dos médicos que não fornecem consultas para os pacientes de obstetras. SELECT nomem FROM medicos WHERE codm NOT IN (SELECT codm FROM consultas WHERE codp IN (SELECT C.codp FROM medicos M, consultas C, pacientes P WHERE M.especialidade = “obstetrícia” and M.codm = C.codm and C.codp = P.codp)) 2) BD formado pelas seguintes tabelas: empregado (codemp, salário, função, codd) departamento (codd, cidade, nome) joga (codemp, codtime, posição) time(codtime, nome, endereço) a) Mostrar os códigos dos empregados que ganham acima da média dos salários SELECT codemp FROM empregado WHERE salario > (SELECT avg(salario) from empregado) b) mostrar os departamentos que não tem empregados SELECT codd FROM departamento WHERE codd NOT IN (SELECT distinct codd 36 FROM empregado) c) mostrar os departamentos com média salarial inferior a 500 (obs: não cai na prova) SELECT codd FROM empregado GROUP BY codd HAVING avg(salario) < 500 d) mostrar os departamentos que possuem mais de 10 programadores (obs: não cai na prova) select codd from empregado where funcao = “programador” group by codd having count(*) > 10 e) mostrar o nome do time do empregado de maior salário SELECT nome FROM time WHERE codtime IN (SELECT codtime FROM joga WHERE codemp IN (SELECT codemp FROM empregado WHERE salario IN (SELECT Max(Salario) FROM empregado))) f) mostrar o maior salário dos empregados que jogam na ponta direita SELECT max(salario) FROM empregado, joga WHERE posicao = “ponta direita” and (joga.codemp = empregado.codemp) g) mostrar a posição e o código dos empregados de maior salário em cada departamento (obs: não cai na prova) 37 select codemp, posicao from joga where codemp in (select codemp from empregado where salario in (select max(salario) from empregado group by codd)) 38 6 O Futuro do Padrão SQL Recentemente temos assistido ao aumento da popularidade dos bancos de dados orientados a objetos (OODBMS -Object Oriented Database Management System). Um banco de dados orientado a objetos é aquele que incorpora objetos complexos, tipos abstratos de dados (Abstract Data Types - ADTs), encapsulamento e herança [MULL,1994]. Mantelman define um objeto complexo como aquele que armazena procedimentos além de dados, e estende a definição de um banco de dados orientado a objetos dizendo que não há restrições a dados baseados em caracteres e que podem ser acomodados objetos multimídia [MANT,1996]. Os bancos de dados orientados a objetos tratam os dados de modo diferente dos bancos de dados relacionais. Estes implementam acesso aos dados através de linhas e colunas, enquanto os orientados a objetos têm uma estrutura mais complexa, que não é suportada pelo padrão SQL92. Como alguns fornecedores de SGBDs começaram a acrescentar a seus produtos características além do escopo do padrão SQL92, houve a necessidade de se estudar um novo padrão, de modo a garantir a portabilidade entre os vários SGBDs. Daí a necessidade do SQL3. Algumas das características implementadas incluem tipos de dados novos e estendidos, incluindo tipos de dados abstratos, múltiplos tipos de estados nulos, suporte para objetos e identidade de objetos, encapsulamento, herança e triggers. O SQL92 tem um conjunto de tipos de dados atômicos. No SQL3, estes tipos de dados são mantidos como tipos de dados pré-definidos, embora alguns tenham sido modificados ou estendidos. Por exemplo, os tipos de dados character e bit foram estendidos para incluir o conceito de um "objeto maior"(large object). Estes "large objects" facilitam o uso de objetos multimídia dentro do banco de dados. Dois tipos adicionais de dados, antes não suportados, foram incluídos: boolean e enumerated. O tipo boolean somente pode ter os valores "true", "false" e "unknown". O tipo enumerated permite que se defina domínios cujos valores são restritos a um pequeno conjunto de valores. Como exemplo, teríamos o comando CREATE DOMAIN cores (azul, vermelho, amarelo) que define um domínio chamado cores e restringe os valores inseridos nacoluna de tipo enumerated a um dos três valores especificados para o domínio [MELT,1995]. Ao contrário do SQL92, o SQL3 suporta múltiplos estados nulos. Isto significa que diferentes classes de null são suportadas, permitindo às aplicações definir os significados de determinado estado nulo. Possíveis significados incluem "unknown" (como vimos acima, no caso do tipo enumerated), "missing", "not applicable" ou "pending". Cada definição de nulo tem uma representação diferente que o torna identificável durante uma consulta ou atualização. O segundo grupo de objeto de dados suportado pela nova versão, e não suportado pela anterior, é o tipo abstrato de dados (ADT). O ADT é utilizado pelo SQL3 para possibilitar o uso de objetos na estrutura SQL. O ADT permite aos usuários criar novos tipos de dados definindo tipos abstratos de dados a partir de tipos de dados pré-definidos ou a partir de outros ADTs já existentes. Os ADTs suportam os conceitos de encapsulamento e subtipos da orientação a objetos. Estendendo o que vimos acima, objetos são itens de dados que combinam complexas estruturas de dados com processos ou métodos para manipular estes dados. A noção de combinar dados e processos é chamada de encapsulamento. Enquanto os tipos de dados que conhecemos no SQL92 são identificados através de seus valores, no SQL3, um dado definido como object será identificado por um identificador único de objeto, gerado quando o objeto é criado. Os conceitos de subtipo e supertipo são baseados no conceito de herança da orientação a objetos, que é a capacidade de se criar novos objetos a partir de outros já existentes, herdando suas características. Atributos e métodos podem ser herdados, o que diminui a redundância de informações. No SQL3, um ADT pode ser um subtipo de outro ADT. Por exemplo, podemos definir um ADT como um objeto PESSOA. Um subtipo de PESSOA poderia ser ESTUDANTE, que herdaria as propriedades e comportamento de PESSOA. Um subtipo do subtipo ESTUDANTE poderia ser GRADUANDO, que herdaria as característica de PESSOA e ESTUDANTE. 39 Outra importante característica do SQL3 é o suporte a triggers. No padrão SQL92, esta característica foi deixada de lado porque seu uso foi subestimado. Existe hoje, entretanto, uma grande demanda desta característica por parte dos usuários, e mais e mais fornecedores estão incluindo suporte a triggers em seus produtos, como, por exemplo, a Oracle no seu SGBD Oracle7. Um trigger é um objeto do esquema do banco de dados ou um bloco de código que diz ao banco de dados que outras ações devem ser tomadas depois de executados certos comandos SQL. Por exemplo, pode-se querer adicionar um registro a uma tabela de log toda vez que um registro for excluído de uma outra tabela para se manter uma auditoria de quem está realizando as mudanças. Um trigger seria usado neste caso para iniciar uma sequência de ações (atualizar a tabela de log) depois que uma função de deleção fosse executada. O trabalho de revisão de um padrão para a inclusão de novas características é intenso e dinâmico. Os fornecedores de SGBDs aumentam o poder de seus produtos adicionando mais e mais características, algumas delas não cobertas pelo padrão atual, o que cria a necessidade de revisão. No caso do SQL3, a inclusão de características de orientação a objetos é a mais importante. 40 7 Bibliografia CAMPOS, Maria Luiza. “Laboratório de Banco de Dados”. Departamento de Ciência da Computação - UFRJ. Disponível no endereço “http://www.dcc.ufrj.br/~labbd/texto/sql.htm”. 1997. DATE, C. J. “Introdução a Sistemas de Bancos de Dados”. Ed. Campus. 1991. LIMA, CARLA A. G. “Linguagem SQL”. Banco de Dados I - Notas de Aula. Depto. Informática - UFPA. 1997. MACHADO, F.N; ABREU, M. “Projeto de Banco de Dados: Uma visão prática”. Érica. 1995. STEPHENS, R.; PLEW, R.; MORGAN, B.; PERKINS, J. “Teach yourself SQL in 21 days”. 2nd Edition. SAMS Publishing, USA, 1997.
Compartilhar