CEFET/RJ Projeto de Banco de Dados Prof. Jorge Soares
A Linguagem de Consulta Estruturada (SQL − Structured Query Language) n
Introdução Todo Sistema Gerenciador de Banco de Dados (SGBD) deve oferecer aos seus usuários e administradores meios de criar definições de dados, bem como manipular estes dados armazenados em suas bases. Cabe ressaltar que estas linguagens têm um caráter diferenciado das linguagens de programação, de natureza procedural (linguagens de 4a geração). Para isto, cada SGBD implementa a chamada Sublinguagem de Dados (DSL), que se subdivide na Linguagem de Definição de Dados (DDL – Data Definition Language), e a Linguagem de Manipulação de Dados (DML – Data Manipulation Language). A Linguagem Estruturada de Consulta (SQL – Structured Query Language) foi desenvolvida pela IBM, e padronizada pelo comitê ANSI (American National Standardization Institute), segundo o padrão ANSI 89. Tem o objetivo de acessar, de forma estruturada e declarativa, (não procedural, ou seja, não é necessário que o usuário programe qualquer algoritmo para acessar as tuplas de uma relação) instâncias de tabelas de bases de dados. Sua principal tarefa é, portanto, criar e manter objetos, manipular e recuperar dados do banco de dados, administrar dados e manter a segurança.
o
Comentários Temos duas formas diferentes de comentar consultas SQL: 1. Com delimitadores: /*
e
*/
Estas marcam delimitam o escopo de um comentário em uma consulta. Esta pode ser inclusive maior que uma linha. Exemplo: /* Este é um exemplo de um comentário que aparece em uma ou mais consultas SQL. Como podemos notar, este comentário não se restringe a apenas uma linha. */ 2. Sem delimitadores:
--
A marca acima indica que, a partir daquele ponto, tudo que se seguir até o final da linha representa um comentário. Portanto, caso seja necessário comentar uma nova parte da consulta em uma linha posterior, deve-se ou utilizar os delimitadores de início e fim de comentário explanados no item 1, ou marcar com um novo --. Lembre-se que não se pode continuar uma consulta após a marca anterior ter sido indicada. Exemplo: SELECT nome_func FROM Funcionário WHERE idade >= 25
-- Estamos selecionando o campo “nome_func” -- da tabela de funcionários -- que tenham pelo menos 25 anos de idade 1
q
A Linguagem de Manipulação de Dados da SQL Subcategoria da SQL que permite a visualização e alteração do conteúdo dos dados de tabelas básicas, temporárias ou visões. É de valia tanto para o Administrador de Banco de Dados, porém tem seu uso mais freqüente com os desenvolvedores de aplicação e usuários avançados. Quatro comandos integram esta classe: • SELECT
–
Seleção de atributos de uma tabela
• INSERT
–
Inclusão de uma ou várias tuplas em uma tabela
• DELETE
–
Remoção de uma ou várias tuplas de uma tabela
• UPDATE
–
Atualização de valores de atributos
Seguem-se as especificações de cada um dos comandos DML da SQL:
ª O comando SELECT Formato Geral: SELECT [ALL | DISTINCT] lista-de-seleção FROM tabela [nome-correlato] [ , tabela [nome-correlato] ] [ WHERE condição ] [ GROUP BY atributo [ , atributo ] ] [ HAVING condição ] [ ORDER BY atributo [ , atributo ] [ASC | DESC] ] Este comando, um dos mais utilizados pelos usuários de Sistemas Gerenciadores de Banco de Dados, tem a função principal de recuperar de dados de uma ou mais tabelas do banco de dados, possivelmente segundo algum critério, porém não obrigatoriamente. Além disso, o resultado da consulta pode ser classificado de diversas formas: agrupado de forma discriminada ou não, ordenado ascendente ou descendentemente. Podemos também utilizar as funções agregadas (pág.21) para inferir algum resultado de uma ou mais tabelas. Seu mecanismo declarativo permite consultas em várias tabelas que se relacionam, através dos atributos que implementam estes relacionamentos. Projeções são da mesma forma permitidas, bem como a declaração de subconsultas. Para detalhar com maior acurácia todo o poder desta instrução, detalhemos primeiramente o significado de cada uma de suas cláusulas, seguindo com exemplos de consultas que possuem diferentes características. Exploraremos, nestas consultas, o uso de predicados, a junção entre tabelas (aliás, o aspecto mais importante nos sistemas relacionais), o agrupamento e as consultas baseadas no resultado de outras consultas. Nos exemplos a seguir, considere as tabelas-exemplo anteriores com as seguintes instâncias:
2
FUNCIONÁRIO matricula
nome_func
endereco
salario
cod_lotacao
10223
Carlos Henrique Oliveira
Av. Marques de Sapucaí,40
14.875,22
7321
10258
Marcelo Faria Espíndola
Alameda Final, 1000
79.500,00
7322
10377
Adelci Moura Lima
Av. Rio Branco, 09
63.000,00
7105
10490
Renato Braga Gadelha
R. das Acácias, 60
80.000,00
7300
10712
Aldenir Machado Flores
Tr. Mário Filho, 60
78.000,00
7323
10902
Alberto Mello de Cima
Av. Marques de Sapucaí,40
8.574,45
7323
10922
Leonardo Sartori Filho
R. Miguel Couto, 23
25.880,01
7322
11057
Roberto Fernandes Gomes
Av. Barão de Ladário, 50
45.500,00
7321
11344
Jorge de Abreu Soares
R. Uruguaiana, 50
500,00
7321
11345
Marcelo Vasconcelos
NULL
NULL
7321
DIVISÃO cod_divisao
sig_divisao
nome_divisao
mat_diretor
depto
7105
DIMED
Divisão de Assistência Médica, Odontológica e Social
10377
7100
7321
DIDAP
Divisão de Desenvolvimento de Aplicações
11057
7300
7322
DISUT
Divisão de Suporte Técnico
10258
7300
7323
DISOP
Divisão de Suporte Operacional
10713
7300
DEPARTAMENTO cod_dep
sig_depto
nome_depto
mat_diretor
mat_assessor
7100
RH
Departamento de Recursos Humanos
10922
NULL
7300
INF
Departamento de Informática
10490
11345
3
As consultas a seguir terão os as tabelas abaixo como resultados (classificação segundo Date [1]): (a) Consulta simples com recuperação de alguns campos:
(b) Especificação de rótulos para dados de saída:
SELECT matricula, nome_func FROM Funcionário
SELECT ‘Nome do Funcionário: ‘, nome_func FROM Funcionário
/* Mostre o número da matrícula e o nome de todos os funcionários */
/* Mostre o nome de todos os funcionários */ col1
matricula
nome_func
nome_func
Nome do Funcionário: Carlos Henrique Oliveira
10223
Carlos Henrique Oliveira
Nome do Funcionário: Marcelo Faria Espíndola
10258
Marcelo Faria Espíndola
Nome do Funcionário: Adelci Moura Lima
10377
Adelci Moura Lima
Nome do Funcionário: Renato Braga Gadelha
10490
Renato Braga Gadelha
Nome do Funcionário: Aldenir Machado Flores
10713
Aldenir Machado Flores
Nome do Funcionário: Alberto Mello de Cima
10902
Alberto Mello de Cima
Nome do Funcionário: Leonardo Sartori Filho
10922
Leonardo Sartori Filho
Nome do Funcionário: Roberto Fernandes Gomes
11057
Roberto Fernandes Gomes
11344
Jorge de Abreu Soares
11345
Marcelo Vasconcelos
Nome do Funcionário: Jorge de Abreu Soares Nome do Funcionário: Marcelo Vasconcelos
(d) Uso do qualificador DISTINCT:
(c) Uso do qualificador ALL:
SELECT DISTINCT depto FROM Divisao
SELECT ALL depto FROM Divisao /* Mostre o código das responsáveis por divisões */
/* Mostre sem repetições o código departamentos responsáveis por divisões */
departamentos
depto depto
7100
7100
7300
7300 7300 7300
4
das
(e) Consulta simples com qualificação: SELECT matricula, nome_func, endereco, salario FROM Funcionário WHERE matricula < 11000 /* Mostre o número da matrícula, o nome, o endereço e o salário de todos os funcionários que possuam matrícula menor que 11000 */ matricula
nome_func
endereco
salario
10223
Carlos Henrique Oliveira
Av. Marques de Sapucaí,40
14.875,22
10258
Marcelo Faria Espíndola
Alameda Final, 1000
79.500,00
10377
Adelci Moura Lima
Av. Rio Branco, 09
63.000,00
10490
Renato Braga Gadelha
R. das Acácias, 60
80.000,00
10713
Aldenir Machado Flores
Tr. Mário Filho, 60
78.000,00
10902
Alberto Mello de Cima
Av. Marques de Sapucaí,40
8.574,45
10922
Leonardo Sartori Filho
R. Miguel Couto, 23
25.880,01
(f) Consulta simples com ordenação padrão: SELECT matricula, nome_func FROM Funcionário WHERE matricula >= 11000 ORDER BY nome_func /* Mostre o número da matrícula e o nome de todos os funcionários que possuam matrícula maior ou igual a 11000, em ordem crescente de nome do funcionário */ matricula
nome_func
11344
Jorge de Abreu Soares
11345
Marcelo Vasconcelos
11057
Roberto Fernandes Gomes
(g) Consulta simples com ordenação decrescente: SELECT matricula, nome_func FROM Funcionário WHERE matricula >= 11000 ORDER BY nome_func DESC /* Mostre o número da matrícula e o nome de todos os funcionários com matrícula maior ou igual a 11000, em ordem decrescente do nome do funcionário */
5
matricula
nome_func
11057
Roberto Fernandes Gomes
11345
Marcelo Vasconcelos
11344
Jorge de Abreu Soares
Obs.: Poderíamos ter especificado a coluna de ordenação pelo seu número (SELECT ... ORDER BY 2 DESC)
(h) Consulta de junção com igualdade simples: SELECT cod_dep, Departamento.nome_depto FROM Departamento, Divisao WHERE (depto = cod_dep) AND (sig_divisao = ‘DIDAP’) /* Mostre o código e o nome das departamentos que tenham uma divisão com nome ‘DIDAP’ */ cod_dep
nome_depto
7300
Departamento de Informática
(i) Consulta de junção com desigualdade simples: SELECT cod_dep, Departamento.nome_depto FROM Departamento, Divisao WHERE (depto = cod_dep) AND (sig_divisao <> ‘DISOP’) /* Mostre o código e o nome das departamentos que não tenham uma divisão com nome ‘DISOP’ */ cod_dep
nome_depto
7100
Departamento de Recursos Humanos
(j) Consulta de junção de uma tabela com ela mesma: SELECT f1.matricula AS O_que_ganha_mais, f2. matricula AS O_que_ganha_menos FROM Funcionário f1, Funcionário f2 WHERE f1.cod_lotacao = f2.cod_lotacao AND f1.salario > f2.salario AND f1.cod_lotacao = 7321
6
/* Mostre em pares o número de matrícula do funcionário que receba um salário maior que outro funcionário, se ambos trabalharem na divisão de código 7321 */ O_que_ganha_mais
O_que_ganha_menos
10223
11344
11057
11344
11057
10223
(k) Consulta de junção de três tabelas: SELECT Funcionário.* FROM Departamento, Divisao, Funcionário WHERE (cod_lotacao = cod_divisao) AND (depto = cod_dep OR cod_lotacao = cod_dep) AND sig_depto = ‘INF’ ORDER BY matricula /* Mostre todos os dados do funcionário que trabalhem na departamento de nome ‘INF’, ou em uma divisão cuja departamento responsável tenha nome ‘INF’ */ matricula
nome_func
endereco
salario
cod_lotacao
10223
Carlos Henrique Oliveira
Av. Marques de Sapucaí,40
14.875,22
7321
10258
Marcelo Faria Espíndola
Alameda Final, 1000
79.500,00
7322
10490
Renato Braga Gadelha
Rua do Acre, 80
80.000,00
7300
10713
Aldenir Machado Flores
Tr. Mário Filho, 60
78.000,00
7323
10902
Alberto Mello de Cima
Av. Marques de Sapucaí,40
8.574,45
7323
10922
Leonardo Sartori Filho
R. Miguel Couto, 23
25.880,01
7322
11057
Roberto Fernandes Gomes
Av. Barão de Ladário, 50
45.500,00
7321
11344
Jorge de Abreu Soares
R. Uruguaiana, 50
500,00
7321
11345
Marcelo Vasconcelos
NULL
NULL
7321
A cláusula UNION Efetua a união lógica do resultado de duas consultas. Formato Geral: consulta1 UNION consulta2 O resultado das consultas 1 e 2 devem ser compatíveis, ou seja, os atributos retornados pelas consultas devem ser os mesmos.
7
Exemplo: ( SELECT matricula, nome_func, endereco FROM Funcionário WHERE endereco LIKE ‘Av.%’ ) UNION ( SELECT matricula, nome_func, endereco FROM Funcionário WHERE matricula > 11000 ) /* Mostre o número da matrícula, o nome e o endereço dos funcionários que tenham endereço iniciando com o padrão ‘Av.’ ou com o número da matrícula menor que 11000 */ tem como resultado: matricula
nome_func
endereco
10223
Carlos Henrique Oliveira
Av. Marques de Sapucaí,40
10377
Adelci Moura Lima
Av. Rio Branco, 09
10902
Alberto Mello de Cima
Av. Marques de Sapucaí,40
11057
Roberto Fernandes Gomes
Av. Barão de Ladário, 50
11344
Jorge de Abreu Soares
R. Uruguaiana, 50
11345
Marcelo Vasconcelos
NULL
A cláusula GROUP BY Este operador organiza a tabela em grupos que possuam o mesmo valor de atributo. Esta organização é lógica, e não se dá no nível físico do banco de dados. Por exemplo, imagine que queremos saber o número de funcionários alocados por divisão. Para isso, nossa consulta deverá trabalhar em cima de grupos, e a partir destes grupos procederemos os devidos cálculos. No exemplo citado, a consulta SQL teria a seguinte forma: SELECT cod_lotacao, count(*) AS total_funcionarios FROM Funcionário GROUP BY cod_lotacao /* Mostre o número da divisão e o seu respectivo número total de funcionários lotados */ e resultaria: cod_lotação
total_funcionarios
7105
1
7300
1
7321
4
7322
2
7323
2
8
Se quiséssemos melhorar o aspecto da consulta anterior, imprimindo não só o código mas também o nome da divisão ou da departamento, teríamos de realizar uma junção com a tabela de divisões e a de departamentos, e teríamos a seguinte consulta: ( SELECT sig_divisao, nome_divisao, count(*) AS total_funcionarios FROM Funcionário, Divisao WHERE cod_lotacao = cod_divisao -- Junção das tabelas “Divisão” e “Funcionário” GROUP BY sig_divisao, nome_divisao ) UNION ( SELECT sig_depto, nome_depto, count(*) AS total_funcionarios FROM Funcionário, Departamento WHERE cod_lotacao = cod_dep GROUP BY sig_depto, nome_depto ) /* Mostre o código, o nome, o nome estendido e o total de funcionários de todas as divisões, agrupando pelo código das divisões */ e o resultado a seguir:
sig_divisao
nome_divisao
total_funcionarios
DIDAP
Divisão de Desenvolvimento de Aplicações
4
DIMED
Divisão de Assistência Médica, Odontológica e Social
1
DISOP
Divisão de Suporte Operacional
2
DISUT
Divisão de Suporte Técnico
2
INF
Departamento de Informática
1
Observação: A cláusula AS, especificada na consulta acima, serve para entitular uma coluna de um resultado de consulta. Quando recuperamos, através da cláusula FROM, um atributo, seu nome vem encabeçando os resultados. Contudo, quando usamos funções agregadas, por exemplo, o interpretador de consultas não tem um nome padrão com significado a atribuir (o padrão é o número da coluna; no caso acima, ao invés de total_funcionarios, teríamos col3). Quando se utiliza a cláusula FROM, o rótulo passado é utilizado. Esta cláusula não permite o uso de strings. Exemplo: ‘total funcionarios’ não pode substituir total_funcionarios. A cláusula HAVING A cláusula WHERE no comando SELECT serve, na maioria dos casos, para impor uma determinada restrição sobre linhas de uma tabela, seja ela uma tabela básica ou derivada de junções. Por exemplo, quando o interpretador analisa a consulta: SELECT * FROM Funcionário WHERE matricula > 10500
9
a tabela de funcionários tem o atributo matricula de todas as tuplas analisado, e somente os que atendem à condição especificada (no caso, matricula > 10500) são devolvidas. matricula
nome_func
endereco
salario
cod_lotacao
10713
Aldenir Machado Flores
Tr. Mário Filho, 60
78000,00
7323
10902
Alberto Mello de Cima
Av. Marquês de Sapucaí, 40
8574,45
7323
10922
Leonardo Sartori Filho
R. Miguel Couto, 23
25880,01
7322
11057
Roberto Fernandes Gomes
Av. Barão de Ladário, 50
45500,00
7321
11344
Jorge de Abreu Soares
R. Uruguaiana, 50
500,00
7321
11345
Marcelo Vasconcelos
NULL
NULL
7321
Com a cláusula HAVING acontece um processamento equivalente. Porém, a semântica se aplica à consultas que possuam a cláusula GROUP BY. Logo, toda vez que queremos especificar a cláusula HAVING, deve-se ter um GROUP BY associado. A cláusula HAVING, na verdade, impõe uma determinada restrição (ou grupo de restrições) à grupos de uma tabela, que representa o resultado de um processamento. Analisemos um exemplo semelhante ao anterior: SELECT sig_divisao, nome_divisao, count(*) AS total_funcionarios FROM Funcionário, Divisao WHERE cod_lotacao = cod_divisao -- Junção das tabelas “Divisao” e “Funcionário” GROUP BY sig_divisao, nome_divisao que tem como resultado: sig_divisao
nome_divisao
total_funcionarios
DIDAP
Divisão de Desenvolvimento de Aplicações
4
DIMED
Divisão de Assistência Médica, Odontológica e Social
1
DISOP
Divisão de Suporte Operacional
2
DISUT
Divisão de Suporte Técnico
2
Se tivéssemos imposto uma restrição aos grupos, através da cláusula HAVING, a consulta seria: SELECT sig_divisao, nome_divisao, count(*) AS total_funcionarios FROM Funcionário, Divisao WHERE cod_lotacao = cod_divisao -- Junção das tabelas “Divisao” e “Funcionário” GROUP BY sig_divisao, nome_divisao HAVING count(*) > 1 /* Mostre o código, o nome, o nome estendido e o total de funcionários de todas as divisões, agrupando pelo código das divisões, se o número de funcionários destas divisões for maior que 1 */ sig_divisao
nome_divisao
total_funcionarios
DIDAP
Divisão de Desenvolvimento de Aplicações
4
DISUT
Divisão de Suporte Técnico
2
DISOP
Divisão de Suporte Operacional
2
10
Predicados Especificam um relacionamento entre duas expressões: expressão1 predicado expressão2 Operadores de comparação válidos: •
[not] like
•
all | any | some
•
[not] between
•
[not] exists
•
[not] in
•
is [not] NULL
1. O predicado LIKE Usado quando quer-se selecionar strings quaisquer que obedeçam a um certo padrão. O “caracter-coringa” ‘_‘ é usado para substituir 1 caracter, e ‘%’ uma substring. Formato Geral: expressão [NOT] LIKE padrão [ESCAPE caracter_de_escape ] onde expressão pode ser um atributo ou uma expressão contendo funções de string. O padrão deve obrigatoriamente ser uma string. Exemplo: Se, em uma tabela, temos as strings ‘ABC DE‘,‘A SDFGF‘, e ‘DRF DA‘, e especificamos como argumento de uma consulta a string ‘___ __‘, a primeira e a terceira strings serão devolvidas como resultado da consulta. Já se especificarmos um predicado LIKE com uma string ‘A%’, a consulta retornará as strings ‘ABC DE‘ e ‘A SDFGF‘. A consulta: SELECT * FROM Funcionário WHERE nome_func LIKE ‘C__los %’ OR nome_func LIKE ‘%lores’ /* Mostre todos os dados dos funcionários que tenham nome terminando começando com ‘C’, seguido de dois caracteres quaisquer e do padrão ‘los ’. Os demais caracteres do nome, depois destas verificações, não mais importam */ retornaria como resultado: matricula
nome_func
endereco
salario
cod_lotacao
10223
Carlos Henrique Oliveira
Av. Marques de Sapucaí,40
14.875,22
7321
10713
Aldenir Machado Flores
Tr. Mário Filho, 60
78.000,00
7323
Se a cláusula ESCAPE for especificada, o processador de consultas não tratará o caracter especificado nesta cláusula com um tratamento comum. Vejamos como seria analisada uma consulta utilizando esta cláusula:
11
SELECT * FROM Funcionário WHERE nome_func LIKE ‘%\[A-F\]ima’ ESCAPE ‘\’ /* Mostre todos os dados dos funcionários que tenham nome terminando com o padrão ‘Aima’, ‘Bima’, ‘Cima’, ‘Dima’, ‘Eima’ ou ‘Fima’ */ retornaria como resultado: matricula
nome_func
endereco
salario
cod_lotacao
10902
Alberto Mello de Cima
Av. Marques de Sapucaí,40
8.574,45
7323
Na consulta acima, os colchetes têm a função específica de indicar uma faixa de caracteres (entre os colchetes). O interpretador de consultas irá procurar no atributo nome_func das tuplas de funcionário strings de qualquer tamanho, que necessariamente terminem com ‘IMA’. Contudo, o quarto caracter, a partir do último, pode ser qualquer um dentro da faixa A-F, ou seja, os caracteres ‘A’, ’B’, ‘C’, ‘D’, ‘E’ e ‘F’. Se alguma tupla tiver um nome que termine em ‘AIMA’, ‘BIMA’, ‘CIMA’, ‘DIMA’, ‘EIMA’ ou ‘FIMA’ satisfazem à consulta. Já a consulta: SELECT * FROM Funcionário WHERE nome_func LIKE ‘%[A-F]ima’ /* Mostre todos os dados dos funcionários que tenham nome terminando com o padrão ‘[A-F]ima’ */ retornaria como resultado a consulta vazia, pois procurar-se-ia strings no atributo nome_func das tuplas que terminassem em ‘[A-F]ima’. 2. O predicado BETWEEN Verifica se o valor de um determinado atributo ou expressão pertence a um intervalo, limitado por dois outros atributos ou expressões. Formato Geral: y [NOT] BETWEEN x AND z Ou seja, a condição acima é verdadeira se e somente se x < y e y < z. Exemplos: SELECT * FROM Funcionário WHERE matricula BETWEEN 10500 AND 11000 /* Mostre todos os dados dos funcionários que tenham número de matrícula entre 10500 e 11000 */ matricula
nome_func
endereco
salario
cod_lotacao
10713
Aldenir Machado Flores
Tr. Mário Filho, 60
78.000,00
7323
10902
Alberto Mello de Cima
Av. Marques de Sapucaí,40
8.574,45
7323
10922
Leonardo Sartori Filho
R. Miguel Couto, 23
25.880,01
7322
12
e o resultado da consulta: SELECT * FROM Funcionário WHERE matricula NOT BETWEEN 10500 AND 11000 /* Mostre todos os dados dos funcionários que não tenham número de matrícula entre 10500 e 11000 */ matricula
nome_func
endereco
salario
cod_lotacao
10223
Carlos Henrique Oliveira
Av. Marques de Sapucaí,40
14.875,22
7321
10258
Marcelo Faria Espíndola
Alameda Final, 1000
79.500,00
7322
10377
Adelci Moura Lima
Av. Rio Branco, 09
63.000,00
7105
10490
Renato Braga Gadelha
R. das Acácias, 60
80.000,00
7300
11057
Roberto Fernandes Gomes
Av. Barão de Ladário, 50
45.500,00
7321
11344
Jorge de Abreu Soares
R. Uruguaiana, 50
500,00
7321
11345
Marcelo Vasconcelos
NULL
NULL
7321
Observação: Não se pode utilizar subconsultas neste predicado. 3. O predicado IS NULL Formato Geral: expressão IS [NOT] NULL Esta sentença é verdadeira se o valor retornado por expressão for nulo (ou não for nulo, quando NOT é especificado). Exemplos: SELECT * FROM Funcionário WHERE endereco IS NULL /* Mostre todos os dados dos funcionários que tenham um endereço nulo */ tem como resultado: matricula
nome_func
endereco
salario
cod_lotacao
11345
Marcelo Vasconcelos
NULL
NULL
7321
e a consulta: SELECT * FROM Funcionário WHERE endereco IS NOT NULL /* Mostre todos os dados dos funcionários que não tenham um endereço nulo */ retorna:
13
matricula
nome_func
Endereco
salario
cod_lotacao
10223
Carlos Henrique Oliveira
Av. Marques de Sapucaí,40
14.875,22
7321
10258
Marcelo Faria Espíndola
Alameda Final, 1000
79.500,00
7322
10377
Adelci Moura Lima
Av. Rio Branco, 09
63.000,00
7105
10490
Renato Braga Gadelha
R. das Acácias, 60
80.000,00
7300
10713
Aldenir Machado Flores
Tr. Mário Filho, 60
78.000,00
7323
10902
Alberto Mello de Cima
Av. Marques de Sapucaí,40
8.574,45
7323
10922
Leonardo Sartori Filho
R. Miguel Couto, 23
25.880,01
7322
11057
Roberto Fernandes Gomes
Av. Barão de Ladário, 50
45.500,00
7321
11344
Jorge de Abreu Soares
R. Uruguaiana, 50
500,00
7321
4. O predicado IN O resultado da expressão que contém este predicado será verdadeiro se e somente se o valor do operando (chamemos nos nossos exemplos de x) aparecer na lista de valores ou na subconsulta subsequente. Formato Geral: x [NOT] IN (lista_de_valores | subconsulta) onde: lista_de_valores = valor [ , valor ] Exemplos: SELECT * FROM Funcionário WHERE matricula IN (10078, 11404, 11057) /* Mostre todos os dados dos funcionários que tenham matrícula igual a 10078, 11404 ou 11057 */ retorna: matricula
nome_func
endereco
salario
cod_lotacao
11057
Roberto Fernandes Gomes
Av. Barão de Ladário, 50
45.500,00
7321
e SELECT cod_divisao, nome_divisao FROM Divisao WHERE cod_divisao IN ( SELECT cod_lotacao FROM Funcionário WHERE salario < 1000 ) /* Mostre o código e o nome estendido da divisão que tenha um funcionário com salário menor que R$ 1000,00 */ retorna: cod_divisao
nome_divisao
7321
Divisão de Desenvolvimento de Aplicações
14
5. O predicado EXISTS Formato Geral: [NOT] EXISTS (subconsulta) A sentença acima é verdadeira se e somente se o conjunto retornado pela subconsulta não for vazio. No caso de a palavra reservada NOT ser explicitada, o sentido se inverte: a sentença é verdadeira se o conjunto retornado pela subconsulta for vazio. Exemplos: SELECT nome_depto FROM Departamento WHERE NOT EXISTS (SELECT * FROM Divisao WHERE sig_divisao = ‘DIMED’) /* Exiba todos os dados das departamentos, se por acaso não existir alguma divisão de nome ‘DIMED’ */ não exibe nenhum resultado, já que a subconsulta retorna uma tupla, e a consulta principal executaria a seleção no caso de nenhuma tupla ser encontrada. Já a consulta: SELECT cod_dep, sig_depto, nome_depto, mat_diretor FROM Departamento WHERE EXISTS ( SELECT * FROM Divisao WHERE sig_divisao = ‘DIMED’) /* Exiba todos os dados das departamentos, se por acaso existir alguma divisão de nome ‘DIMED’ */ exibiria: cod_dep
sig_depto
nome_depto
mat_diretor
7100
RH
Departamento de Recursos Humanos
10922
7300
INF
Departamento de Informática
10490
Observação: •
A cláusula EXISTS, neste caso, funciona como um sinalizador da execução da consulta; ou seja, a consulta mais externa só seria executada no caso de haver alguma divisão na tabela de divisões cujo nome fosse “DIMED”
•
Não é mandatória a especificação da subconsulta com o caracter *.
6. O predicado ANY-OR-ALL Formato Geral: operador-any-or-all (subconsulta)
15
A sentença acima é verdadeira se e somente se pelo menos um dos valores retornados pela subconsulta for idêntico ao valor sendo comparado, no caso do operador ANY. Já com o operador ALL, a sentença é verdadeira se o valor comparado for igual a todos os valores retornados pela subconsulta. Os operadores any-or-all podem ser: = ANY (equivalente ao IN)
= SOME (equivalente ao IN)
= ALL
<> ANY (ou != ANY)
<> SOME (ou != SOME)
<> ALL(ou != ALL)
< ANY
< SOME
< ALL
<= ANY
<= SOME
<= ALL
> ANY
> SOME
> ALL
>= ANY
>= SOME
>= ALL
Exemplos: SELECT cod_divisao, nome_divisao FROM Divisao WHERE mat_diretor = ANY ( SELECT matricula FROM Funcionário WHERE sig_divisao = ‘DIDAP’) /* Mostre o código e o nome estendido das divisões cujo nome seja ‘DIDAP’ */ retorna: cod_divisao
nome_divisao
7321
Divisão de Desenvolvimento de Aplicações
SELECT * FROM Funcionário WHERE matricula < ALL ( SELECT matricula FROM Funcionário WHERE matricula >= 10500 ) /* Mostre todos os dados dos funcionários com número de matrícula menor que 10500 */ devolve: matricula
nome_func
endereco
salario
cod_lotacao
10223
Carlos Henrique Oliveira
Av. Marques de Sapucaí,40
14.875,22
7321
10258
Marcelo Faria Espíndola
Alameda Final, 1000
79.500,00
7322
10377
Adelci Moura Lima
Av. Rio Branco, 09
63.000,00
7105
10490
Renato Braga Gadelha
R. das Acácias, 60
80.000,00
7300
16
ª
O comando INSERT Temos dois formatos possíveis: INSERT INTO tabela [ ( nome-do atributo [ , nome-do atributo ] ) ] VALUES ( valor-constante [ , valor-constante ] ) ou INSERT INTO tabela [ ( nome-do atributo [ , nome-do atributo ] ) ]
sub-consulta Comando que insere ou um registro completo, ou alguns atributos de um registro, deixando os demais com valor nulo. Exemplos: 1. INSERT INTO Funcionário VALUES ( 10250, ‘Alberto Mello’, ‘Rua do Acre, 80’, 5100.00, 7322) insere o registro acima com todos os campos preenchidos na tabela de Empregados. 2. Já a sentença INSERT INTO Funcionário ( matricula, nome_func, cod_lotacao ) VALUES ( 11344, ‘Jorge de Abreu Soares’, 7321) insere o registro acima na tabela de Funcionários, com o campo “endereco” desconhecido (nulo). 3. As sentenças CREATE TABLE Temporaria ( sig_depto nome_func INSERT INTO
VARCHAR(50), SMALLINT );
Temporaria SELECT sig_depto, count(Divisao.*) FROM Departamento, Divisão WHERE (cod_dep = depto) GROUP BY sig_depto
armazena na tabela “Temporária“, o nome de cada departamento e o número de diretores das suas divisões subordinadas.. Esta tabela pode ser removida posteriormente com o comando: DROP TABLE Temporaria /* Remova do banco de dados a tabela ‘Temporaria’ */ ª
O comando DELETE Formato Geral: DELETE FROM tabela [WHERE condição]
17
Este comando remove todas as tuplas de uma tabela, segundo a especificação da cláusula WHERE, caso exista. Se esta não existir, todas as instâncias da tabela são removidas; contudo, a descrição da tabela no catálogo do sistema permanece intacta, por só poder ser alterada pelos comandos da linguagem de definição de dados. Exemplos: 1. A sentença DELETE FROM Funcionário WHERE nome_func = ‘João da Silva’ removeria todos os Funcionários que tivessem o nome “João da Silva”. 2. Já a sentença DELETE FROM Funcionário removeria todos os Funcionários da tabela “Funcionário”. 3. DELETE FROM Funcionário WHERE matricula = ( SELECT FROM WHERE AND
matricula Funcionário, Divisão cod_lotacao = cod_divisao Divisão.sig_divisao = ‘DISOP’ )
/* Remova todos os registros de funcionários que trabalhem na divisão de nome ‘DISOP’ */ removeria todas as tuplas de funcionários da DISOP. ª
O comando UPDATE Formato Geral: UPDATE tabela SET atributo = expressão [ , atributo = expressão ] [WHERE condição] Modifica o(s) valor(es) de atributo(s) de uma tabela, seguindo ou não uma determinada condição. Exemplos: UPDATE Funcionário SET endereco = ‘Rua Mena Barreto, 20’ WHERE nome_func = ‘Jorge de Abreu Soares’ /* Modifique os endereços das tuplas cujo nome de funcionário é “Jorge de Abreu Soares” */
Funções Agregadas Retornam um único valor de acordo com o conteúdo de uma coluna. São amplamente utilizadas, já que têm a característica importante de inferir resultados a partir de dados resultantes de consultas. Formato Geral:
nome-da-função ( [DISTINCT | ALL] nome-da-função ) 18
onde nome-da-função pode assumir os seguintes valores:
Nome da função
Tipo do valor de retorno
Descrição
count
Inteiro
Determina o número de instâncias de um atributo
sum
Inteiro, Real
Soma os valores das instâncias de um atributo
avg
Real
Determina o valor da média (sum/count) dos valores constantes em um atributo
max
O mesmo do argumento
Ocorrência de valor máximo dentre todas em uma coluna
min
O mesmo do argumento
Ocorrência de valor mínimo dentre todas em uma coluna
Observações: •
A palavra reservada DISTINCT elimina ocorrências duplicadas do argumento, e não pode ser usada em conjunto com as funções “max“ e “min“.
•
Valor padrão Ö ALL (todas as ocorrências, mesmo as duplicadas, são retornadas).
•
Valores nulos são ignorados por funções agregadas, exceto a função “count“.
A função agregada “count” •
Aceita o caracter “*” como máscara, tendo a função de determinar não mais o número de ocorrências de uma coluna, mas sim das linhas de uma tabela
•
Com o caracter “*”, não podemos utilizar os qualificadores ALL ou DISTINCT
•
Valores nulos serão computados caso se use esta função com o caracter “*”
Exemplos: Baseando-se ainda na tabela e nas instâncias da tabela de Funcionários:
Consulta
Valor Retornado
SELECT sum(Funcionário.salario) FROM Funcionário
395.829,68
/* Mostre a soma dos salarios de todos os funcionários */ SELECT count(*) FROM Funcionário
2
WHERE cod_lotacao = 7323 /* Mostre o número de funcionários lotados na divisão/departamento de número 7323 */ SELECT count(*) FROM Funcionário WHERE cod_lotacao = 7321
4
/* Mostre o número de funcionários lotados na divisão/departamento de código 7321 */ SELECT avg(Funcionário.salario) FROM Funcionário WHERE cod_lotacao = 7321 /* Mostre a média salarial dos funcionários lotados na divisão/departamento de número 7321 */ 19
20.291,74
SELECT max(Funcionário.salario) FROM Funcionário /* Mostre o valor máximo de salário percebido por um funcionário */ SELECT min(Funcionário.matricula) FROM Funcionário /* Mostre a menor matrícula de funcionário */
80.000,00 10223
Note que, na 3a consulta, o 6o registro da tabela de Funcionários possui alguns valores nulos de atributos. Como a função count não consegue diferenciar este tipo de valor quando conta linhas, o resultado final inclui este 6o registro. Separadores de Sentenças •
Para separar duas sentenças SQL, usa-se o caracter ’;’
•
A última sentença não necessita do separador
•
Sempre ao final de cada instrução SQL que atualize dados, é boa prática executar a instrução commit, de forma que se garanta que os dados modificados sejam realmente gravados na base de dados
Nomes Correlatos Servem para renomear temporariamente uma tabela em uma consulta, seja para abreviar nomes de tabelas longos, como para propiciar a junção de uma tabela com ela mesma. Exemplo: SELECT func.nome_func FROM Funcionário func Observações: •
Só podem existir até 30 correlações em uma consulta
•
Nomes correlatos devem ser únicos, ou seja, não se pode especificar um nome correlatos para duas tabelas diferentes. Exemplo: SELECT tab.nome_func FROM Funcionário tab, Cidade tab -- “tab” foi especificado para duas tabelas distintas
•
Se for especificado um nome correlativo para uma tabela, só se deve utilizar este nome na consulta, e não mais o nome real da tabela Exemplo: SELECT func.nome_func FROM Funcionário func
-- Consulta correta
e não SELECT Funcionário.nome_func FROM Funcionário func -- Consulta errada
20
•
Não se pode usar nomes de tabelas existentes como correlatas para outras tabelas. Exemplo: Suponha que, além da tabela de Funcionários, possuíssemos a tabela de Localidades (endereço completo do Funcionário) e de Cidades. A seguinte consulta conteria um erro sintático: SELECT func.nome_func FROM Funcionário Func, Cidade Localidade WHERE Localidade.nom_locali = ‘Rio de Janeiro’
•
O interpretador não procura por atributos de tabelas não correlatas entre sub-consultas do mesmo nível Exemplo: SELECT nome_func, cod_lotacao FROM Funcionário, Divisão WHERE ( cod_lotacao = ( SELECT cod_divisao FROM Divisão, Departamento WHERE (depto = cod_dep) AND (sig_depto = ‘INF’) ) OR ( cod_lotacao = ( SELECT cod_divisao FROM Funcionário WHERE (nome_func = ‘José da Silva’) ) /* Mostre o nome dos funcionários e o código de sua lotação que trabalhem nas divisões da departamento de nome ‘INF’, ou cujo nome do funcionário seja ‘José da Silva’ */ O atributo func da sentença func = ‘José da Silva’ será buscado primeiramente na tabela Divisão da cláusula FROM. Como não será encontrado, a análise continua na cláusula FROM imediatamente acima (... FROM Funcionário, Divisão ...). Em hipótese alguma este atributo seria buscado na cláusula ... FROM Divisão, Departamento ... da primeira subconsulta.
•
Em consultas aninhadas, quando não se qualificam as tabelas com nomes correlatos, o interpretador de consultas irá resolvê-las a partir da cláusula FROM mais próxima para a mais externa. Exemplo: SELECT nome_func FROM Funcionário WHERE salario > ( SELECT avg(salario) FROM Divisão WHERE (cod_lotacao = cod_divisao) AND AND
(sig_divisao = ‘DIDAP’) (matricula > 200) )
/* Mostre o nome dos funcionários que trabalhem na divisão de nome ‘DIDAP’, que tenham matricula maior que 200, e que recebam acima da média da divisão */ Os atributos sig_divisao da sentença sig_divisao = ‘DIDAP’ e o atributo matricula da sentença matricula > 200 serão buscados na relação Divisão. O atributo sig_divisao será encontrado na tabela
21
de divisões, mas matricula não pertence a nenhuma delas. Portanto, a análise da consulta sobe um nível de cláusula FROM, buscando e encontrando o atributo matricula na relação Funcionário. p
A Linguagem de Definição de Dados da SQL Esta é a subcategoria de SQL que permite alteração de dados no catálogo do sistema. Normalmente, é de uso exclusivo do Administrador de Banco de Dados; contudo, seu uso fica sujeito à política da empresa. Três comandos integram esta classe: • CREATE [TABLE | VIEW | INDEX]
–
Criação de uma tabela (CREATE TABLE), de uma visão
(CREATE VIEW) ou de um índice (CREATE INDEX). • ALTER TABLE – Alteração da definição (meta-esquema) de uma tabela, com a adição de uma coluna à esquerda. • DROP [TABLE | VIEW | INDEX]
–
Destruição (dados e definição) de uma tabela básica (DROP
TABLE), de uma visão (DROP VIEW) ou de um índice (DROP INDEX). Especificaremos em detalhes cada um deles. Observações: • Considere os itens envoltos em colchetes como opcionais. • Nos exemplos a seguir, utilizaremos as tabelas: FUNCIONÁRIO(matricula, nome_func, endereco, salario, cod_lotacao) DIVISÃO(cod_divisao, sig_divisao, nome_divisao, mat_diretor, cod_dep_respon) DEPARTAMENTO (cod_dep, sig_depto, nome_depto, mat_diretor, mat_assessor) ª O comando CREATE TABLE Formato Geral: CREATE TABLE nome-da-tabela ( definição-da-coluna [ , definição-da-coluna ] ) e definição-da-coluna é definida como:
nome-da-coluna tipo-de-dado [NOT NULL] Exemplo: A sentença CREATE TABLE Funcionário ( matricula SMALLINT NOT NULL, nome_func CHAR(30), endereco VARCHAR(50), salario DECIMAL, cod_lotacao SMALLINT PRIMARY KEY (matricula)
NOT NULL )
cria a tabela FUNCIONÁRIO(matricula, nome_func, endereco, salario, cod_lotacao);
22
e a obrigatoriedade de valores não nulos nos campos matricula e cod_lotacao. ª O comando CREATE VIEW Formato Geral: CREATE VIEW nome-da-visão [ ( nome-do atributo [ , nome-do atributo ] ) ] AS sub-consulta Exemplo: CREATE VIEW Func_Info ( mat, nomef ) AS SELECT matricula, nome_func FROM Funcionário, Divisão WHERE (cod_lotacao = cod_divisao) AND (sig_divisao = ‘DIDAP’) /* Crie a visão “Funcionários_Informatica”, com o número da matrícula e o nome dos funcionários que trabalhem na divisão de nome ‘DIDAP’ */ SELECT nomef FROM Func_Info WHERE nomef LIKE “%SILVA” AND mat > 50 ª O comando CREATE INDEX Formato Geral: CREATE [UNIQUE] INDEX nome-do-índice ON nome-do-tabela ( nome-do atributo [ , ordem ] [ , [ nome-do atributo [ , ordem ] ] ) [ CLUSTER ] onde ordem pode assumir os valores ASC (ascendente – valor padrão) ou DESC (descendente) para o armazenamento do índice para um certo atributo. Este é um comando normalmente utilizado paras definir chaves secundárias. A cláusula CLUSTER especifica um índice agrupado (o armazenamento físico dos registros no disco levará em conta a relação entre eles, ou seja, se a tupla t1 se relaciona com a tupla r2, então o sistema procurará armazená-las de forma que o tempo de transferência de dados do disco magnético para a memória seja o menor possível). Cabe salientar que uma tabela básica só pode ter um índice utilizando a cláusula CLUSTER em um dado momento. A cláusula UNIQUE faz com que duas tuplas da tabela não tenham o mesmo valor de campo indexado em um dado momento. Exemplo: 23
CREATE UNIQUE INDEX ind_func ON Funcionário (nome_func) /* Crie um índice único para cada atributo nome_func da tabela Funcionário */
ª O comando ALTER TABLE Formato Geral: ALTER TABLE nome-da-tabela ADD [CONSTRAINT nome-da-restrição] cláusula-de-restrição | DROP CONSTRAINT nome-da-restrição | ADD [COLUMN] nome-do-atributo tipo-de-dado [cláusula default] [cláusula nula] [restrição-da-coluna] | DROP [COLUMN] nome-do-atributo RESTRICT | CASCADE Exemplo: A sentença ALTER TABLE Funcionário ADD COLUMN telefone
varchar(15)
transforma a tabela de Funcionários em: FUNCIONÁRIO(matricula, nome_func, endereco, salario, cod_lotacao, telefone) Obs.: Não disponível no CA-OpenIngres com esta funcionalidade ª O comando DROP TABLE Formato Geral: DROP TABLE nome-da-tabela Esta sentença não só remove a descrição da tabela do catálogo do banco, como todas suas instâncias, visões e índices definidos sobre ela. Exemplo
DROP TABLE Funcionário
/* Remova do banco de dados a tabela “Funcionário” */
ª O comando DROP INDEX Formato Geral: DROP INDEX nome-do-índice Esta sentença remove a descrição do índice do catálogo do banco. Exemplo:
DROP INDEX ind-func
/* Remova do banco de dados o índice “ind-func” */
ª O comando DROP VIEW Formato Geral:
24
DROP VIEW nome-da-visão Remoção da visão especificada, bem como todas as visões definidas a partir da especificada. Exemplo: DROP VIEW Funcionários_Informatica /* Remova do banco de dados a visão “Funcionários_Informatica” */ ª O comando COMMENT Usado para tecer e armazenar comentários sobre objetos do banco de dados. Formatos Gerais: COMMENT ON TABLE nome-da-tabela IS string-de-comentário COMMENT ON COLUMN nome-da-coluna IS string-de-comentário Exemplos: COMMENT ON TABLE Funcionário IS ‘Tabela de Alocação de Funcionários’ /* Adicione a string ‘Tabela de Alocação de Funcionários’ como comentário à tabela “Funcionário” */ COMMENT ON COLUMN Funcionário.nome_func IS ‘Nome completo do funcionário’ /* Adicione a string ‘Nome completo do Funcionário’ como comentário à coluna “nome_func” da tabela “Funcionário” */
Referências Bibliográficas [1] C. J. Date; Introdução a Sistemas de Bancos de Dados; Ed. Campus, 1991 [2] CA-OpenIngres SQL Reference Guide 1.1; Computer Associates, Junho 1995 [3] CA-OpenIngres Open SQL Reference Guide 1.1; Computer Associates, Junho 1995
25