BD 03 SQL Consultas

Texto

(1)Banco de Dados - II. Consultas SQL (1ª parte) Introdução As consultas de dados, na linguagem SQL, são realizadas através do comando SELECT (que pertence ao subconjunto de comandos DML – “data maniputation language”). Apesar do nome “SELECT”, este comando SQL permite realizar várias operações de álgebra relacional: seleção (linhas), projeção (colunas), junção, produto cartesiano, união, intersecção, diferença, etc. Este, certamente, é o comando mais aplicado nas interações com um banco de dados relacional. Além disso, permite muitas variações na sintaxe das sentenças SQL que utilizam essa instrução — exigindo prática para que aproveitemos, da melhor forma, os muitos recursos existentes. Esta primeira parte do estudo, aborda a sintaxe básica do comando SELECT, fundamentos sobre operadores e funções da linguagem SQL (no SGBD Oracle 8i ou 9i), além de alguns conceitos relacionados.. Comando SELECT Permite realizar consultas referentes aos dados de uma ou várias tabelas. O retorno de uma consulta, em um banco de dados relacional, sempre é na forma de uma tabela, isto é, com linhas de dados (registros, ou “tuplas”), divididas em colunas (campos, ou “atribuitos”). Sintaxe básica:. lista de colunas (ou expressões). a serem retornadas SELECT ALL|DISTINCT coluna_1, coluna_2, ..., coluna_n FROM tabela_a, tabela_b, ..., tabela_x lista de tabelas WHERE condições_de_seleção ORDER BY campo_i ASC|DESC, campo_ii ASC|DESC, ..., campo_ix ASC|DESC. •. lista de colunas para ordenação. Lista de colunas a serem retornadas: define os atributos a serem retornados — podem ser os nomes das colunas das tabelas, funções ou qualquer expressão que retorne um valor válido (caractere, numérico, data ou nulo). Se desejarmos que sejam retornadas todas as colunas de uma tabela, podemos usar o asterisco (*) no lugar da lista de colunas. As cláusulas ALL e DISTINCT são opcionais e mutuamente exclusivas; caso nenhuma das seja definida explicitamente, por padrão o interpretador da linguagem SQL irá considerar a opção ALL. –. ALL: (opção “default”) indica que mesmo as linhas idênticas (repetidas) deverão estar no resultado da consulta.. –. DISTINCT: assegura que não haverá repetição de linhas no resultado da consulta, ou seja, caso haja linhas idênticas (iguais), apenas uma será retornada.. •. Lista de tabelas: a cláusula FROM especifica as tabelas (ou “views”) que são as fontes de dados. Neste primeiro estudo básico, o objetivo é explicar as consultas mais simples, com apenas uma tabela.. •. Condições de seleção: (opcional) a cláusula WHERE permite definir uma expressão lógica (normalmente com testes envolvendo atributos da tabela) que atuará como um “filtro” — apenas as linhas para as quais a expressão for verdadeira, serão recuperadas no resultado da consulta. Se esta cláusula não especificada, todas as linhas da tabela serão retornadas na consulta.. •. Lista de colunas para ordenação: (opcional) a cláusula ORDER BY permite definir as colunas que serão base para ordenação da lista retornada na consulta. A classificação ocorrerá conforme o tipo de dado (datatype) de cada coluna indicada: alfabética/alfanumérica (seguindo a tabela de caracteres do idioma corrente na configuração), numérica ou cronológica. OBS.:. Ao invés do nome da coluna, também pode ser indicado o “alias” (apelido) ou o número correspondente à coluna, conforme definido na lista de retorno do SELECT.. Os modificadores ASC e DESC, após o cada coluna, são opcionais e mutuamente exclusivos; caso nenhum deles seja explicitamente definido, por padrão será considerada a opção ASC. –. ASC: ordem ascendente (crescente).. –. DESC: ordem descendente (decrescente).. Banco de Dados – II: Consultas SQL (1ª parte). Uninove – prof. André Santos – set./2003 - rev.: out./2005 – pág. 1 / 1.

(2) Terminologia: •. Query (no plural “queries”) = consulta SQL.. •. Projeção = é o conceito da álgebra relacional que refere-se à definição de quais colunas (atributos) irão constituir o resultado de uma consulta — corresponde à lista de colunas a serem retornada no comando SELECT.. •. Seleção = é o conceito da álgebra relacional que refere-se à definição de quais linhas (tuplas) serão recuperadas em uma consulta — corresponde à condição de seleção da cláusula WHERE.. •. Alias (no plural “aliases”) = é um apelido, ou nome alternativo, que pode ser definido em uma consulta SQL, para uma coluna ou tabela, sendo válido apenas em tempo de execução. É definido após o nome da coluna ou tabela. OBS.: Especificamente para alias de coluna, o apelido opcionalmente pode ser precedido da palavra reservada AS (apenas para melhor clareza na escrita do comando). Pode, também, estar definido entre aspas duplas (" ") — neste caso poderá conter caracteres especiais e, inclusive, espaços em branco.. •. View (no plural “views”) = uma “visão” (view) pode ser considerada e tratada como uma tabela virtual (respeitando-se algumas limitações). Na realidade, é uma consulta SQL (comando SELECT) cuja definição fica armazenada no dicionário de dados do SGBD — ou seja, uma view comum, em si, não possui dados. Quando é feita uma consulta a uma visão, ela (em tempo de execução) retorna os dados da(s) tabela(s), conforme sua definição.. A tabela DUAL No Oracle, um comando SELECT sempre deve referencial uma tabela ou view (a cláusula FROM é obrigatória). Porém há casos nos quais não se quer recuperar os dados armazenados em uma tabela, mas sim apenas retornar o resultado de uma expressão, literal ou função. Para isso existe a tabela “DUAL”, que é uma tabela “dummy”, isto é, existe apenas para atender a uma exigência de sintaxe, mas não tem influência direta no resultado da operação. A tabela DUAL possui uma única linha — para que nessas operações também haja apenas uma linha de retorno — e é acessível a qualquer usuário do banco de dados.. Exemplos: SELECT 4504 / 99 FROM DUAL; SELECT SYSDATE FROM DUAL;. Banco de Dados – II: Consultas SQL (1ª parte). Uninove – prof. André Santos – set./2003 - rev.: out./2005 – pág. 2 / 2.

(3) Operadores Operadores são elementos (símbolos ou palavras reservadas) da linguagem que atuam sobre itens de dados e retornam um resultado, em expressões numéricas, caracteres ou com datas.. Principais operadores utilizados em expressões numéricas, caractere e com datas. Aritméticos (valores numéricos) Operador Descrição. Exemplos. -. negativo (operador unário). -1. +. positivo (operador unário). +1. /. divisão. 10 / 2. *. multiplicação. 3 * 5. +. adição (operador binário). 3 + 4. -. subtração (operador binário). 100 – 60. Aritméticos (com datas) Operador Descrição. Exemplos data_vencto – SYSDATE. -. diferença entre datas, resultando número de dias. +. adição de dias a uma data, resultando uma data posterior. SYSDATE + 30. -. subtração de dias a uma data, resultando uma data anterior. SYSDATE – 1. Caractere (texto) Operador Descrição ||. Exemplos. concatenação (2 barras verticais, “pipe pipe”). 'São ' || 'Paulo'. Principais operadores utilizados em expressões lógicas. Comparação (também chamados de operadores “relacionais” em outras linguagens) Operador Descrição Exemplos =. igual. codigo = 1 sobrenome = 'SILVA' data_cadastro = '01/09/2003'. <>. diferente. pais != 'USA'. != >. valor <> 0. maior que. pct_juros > 0.5 data_vencto > SYSDATE. >=. maior ou igual a. salario >= 1000 data_nasc >= '01/01/1970' nome >= 'A'. <. menor que. Banco de Dados – II: Consultas SQL (1ª parte). peso < 1.5. Uninove – prof. André Santos – set./2003 - rev.: out./2005 – pág. 3 / 3.

(4) qtde <= 1000. <=. menor ou igual a. IN. retorna verdadeiro se um atributo estiver contido em uma lista. estado IN ('SP','RJ','MG','ES'). retorna verdadeiro se um atributo estiver entre um intervalo especificado (limites mínimo e máximo inclusos na faixa definida):. salario BETWEEN 240 AND 1000. BETWEEN. tipo NOT IN (4, 5). dt_nasc NOT BETWEEN '01/01/1970' AND '31/12/1970'. valor BETWEEN mínimo AND máximo LIKE. nome LIKE 'MARIA%'. comparação de textos, utilizando caracteres “curinga”:. estado NOT LIKE 'R_'. % (percentual): substibui vários caracteres (porção de texto de zero a “n” caracteres). _ (sublinha, underline, underscore): substibui um único caractere. IS NULL. data_pagto IS NULL. é nulo. endereco IS NOT NULL. IS NOT NULL não é nulo. Lógicos Operador. Descrição. AND. “e” lógico. OR. “ou” lógico. NOT. Exemplos valor >= 500 AND valor <= 1000 tipo = 'A' OR valor > 1000 NOT ( tipo = 1 AND valor > 1000 ). negação lógica. Precedência na avaliação dos operadores em expressões As partes de uma expressão que estiverem entre parênteses ( ) são sempre avaliadas primeiro, dos mais internos para os mais externos. Quando não houverem parênteses, a ordem de avaliação segue conforme a precedência dos operadores: 1º) Negativo e positivo (unários): – e + 5º) negação lógica: NOT 2º) Multiplicação e divisão: * e /. 6º) “e” lógico: AND. 3º) Adição e subtração: + e –. 7º) “ou” lógico: OR. Concatenação de texto: || 4º) Operadores de comparação. Comentários Operador Descrição Comentário de linha simples. --. Exemplos -- Qualquer comentário de linha SELECT * -- o que vier será ignorado FROM DUAL;. /*. OBS.:. */. Comentário em múltiplas linhas, SELECT SYSDATE /* a função sysdate retorna a data corrente */ iniciando com /* e terminando com */ FROM DUAL;. Na ferramenta SQL Plus, uma linha iniciada com REMARK, ou simplesmente REM, também será um comentário (será ignorada).. Banco de Dados – II: Consultas SQL (1ª parte). Uninove – prof. André Santos – set./2003 - rev.: out./2005 – pág. 4 / 4.

(5) Conversões automáticas de tipos de dados em expressões No Oracle, sempre que necessário numa expressão, poderá haver conversões automáticas, implícitas, entre os tipos de dados caractere, numérico ou data — caso seja possível, ou seja, se o interpretador SQL conseguir converter em tipos compatíveis para resolver a expressão. Os operadores utilizados podem ser determinantes nessas conversões. OBS.:. Especialmente em dados do tipo date (datas), a conversão automática pode ser problemática. Um texto representando uma data, somente será convertido se estiver compatível de acordo com o formato de data da configuração corrente no SGBD.. Exemplos: SELECT '100' - 1 FROM DUAL; SELECT 'Pelé camisa ' || (5 + 5) FROM DUAL; -- Se uma tabela TESTE, a coluna COD for number e a coluna NASC for date -- o comando abaixo será efetuado com sucesso -- (DEPENDENDO se o formato da data for aceito como válido): UPDATE teste SET nasc = '20/01/1970' WHERE cod = '001';. Valor nulo (NULL) Em bancos de dados, um valor nulo expressa um dado desconhecido, um valor não definido, representa a ausência de valor — ou seja, não se deve confundir “nulo” (NULL) com o valor numérico zero ou espaços em branco ou mesmo com uma string de comprimento zero (campo de texto vazio). OBS.:. o SGBD Oracle avalia uma string de comprimento zero como sendo equivalente a nulo (apesar de, conceitualmente, serem coisas diferentes). Apesar desse tratamento peculiar, não é recomendado que consideremos da mesma forma — além disso, esse comportamento pode não ser suportado em versões futuras.. Deve-se ter cuidado com valores nulos pois, numa expressão qualquer, se existir um valor nulo, isso fará com que a expressão toda seja avaliada com nula (resultando NULL). A única exceção é em com o operador de concatenação de textos ( || ). Em expressões lógicas, não se pode utilizar os operadores de igualdade ( = ) ou desigualdade ( <> ) para testar valores nulos. Ao invés disso, deve-se usar o operador IS (IS NULL ou IS NOT NULL).. Banco de Dados – II: Consultas SQL (1ª parte). Uninove – prof. André Santos – set./2003 - rev.: out./2005 – pág. 5 / 5.

(6) Funções SQL Funções SQL (denominamos assim para diferenciar das funções definidas pelo usuário), são rotinas nativas da linguagem SQL (conforme a implementação do SGBD). Uma função SQL pode ou não receber argumentos (parâmetros), mas sempre retorna um valor (caractere, numérico, data ou nulo). Quando a função receber parâmetros, a lista de argumentos estará entre parênteses ( ) e os elementos estarão separados por vírgula (,). Funções podem fazer parte de expressões e serem chamadas diretamente em comandos SQL.. Algumas funções SQL do Oracle. Funções Matemáticas Função/sintaxe básica Descrição ROUND(n,d). Exemplos. Arredonda um valor “n”, conforme o número de casas decimais “d”.. ROUND(125.14, 1). Caso “d” não seja definido, será considerado 0 (zero), ou seja, o número será arredondado para um inteiro.. ROUND(125.16, 1). ROUND(125.15, 1). ROUND(3.5, 0) ROUND(3.5) ROUND(155, -1) ROUND(154, -1). TRUNC(n,d). Trunca um valor “n”, conforme o número de casas decimais “d”.. TRUNC(125.14, 1). Caso “d” não seja definido, será considerado 0 (zero), ou seja, o número será arredondado para um inteiro.. TRUNC(125.16, 1). TRUNC(125.15, 1). TRUNC(3.5, 0) TRUNC(3.5) TRUNC(155, -1) TRUNC(154, -1). SQRT(n). SQRT(9). Retorna a raiz quadrada do valor “n”. SQRT(1500). POWER(b,e). POWER(2, 4). Retorna a potência, de base “b” e expoente “e”. POWER(9, 0.5). MOD(n,d). MOD(10, 3). Retorna o resto da divisão n/d (“n” dividendo e “d” divisor). MOD(5, 0). Outras funções matemáticas: ABS, SIGN, SIN, COS, TAN, LN, LOG, EXP, ACOS, ATAN, FLOOR, etc.. Funções Caractere Função/sintaxe básica LOWER(t). Descrição. Exemplos. Retorna o texto em minúsculas. LOWER('MINUSCULAS') LOWER(email_cliente). UPPER(t). Retorna o texto em maiúsculas. UPPER('sp') UPPER(nome_emp). LENGTH(t). Retorna a quantidade de caracteres do texto. Outras funções caractere:. LENGTH('teste'). ASCII, CHR, INITCAP, INSTR, LPAD, LTRIM, REPLACE, RPAD, RTRIM, SOUNDEX, SUBSTR, TRANSLATE, TRIM, etc.. Banco de Dados – II: Consultas SQL (1ª parte). Uninove – prof. André Santos – set./2003 - rev.: out./2005 – pág. 6 / 6.

(7) Funções de Data/Hora Função/sintaxe básica. Descrição. Exemplos SYSDATE. Retorna a data e hora correntes do servidor. SYSDATE ADD_MONTHS(d,m). ADD_MONTHS(sysdate, 3). Retorna a data “d” mais “m” meses. Outras funções de data: LAST_DAY, MONTHS_BETWEEN, NEXT_DAY, ROUND, TRUNC, etc.. Funções de Conversão Função/sintaxe básica TO_CHAR(n,fmt) TO_CHAR(d,fmt). Descrição. Exemplos. Retorna um texto (opcionalmente com formato definido pela máscara “fmt”), correspondente a um valor numérico ou data/hora.. TO_CHAR( 10/3, '999.99') TO_CHAR(sysdate, 'dd/mm/yyyy') TO_CHAR(sysdate, 'hh24:mi:ss'). TO_NUMBER (t,fmt). Retorna um valor numérico correspondente a um texto (opcionalmente, poderá ser definida uma máscara “fmt” que irá orientar o modo de interpretar o texto a ser convertido).. TO_NUMBER('100.05'). TO_DATE(t,fmt). Retorna uma data/hora correspondente a um texto (opcionalmente, poderá ser definida uma máscara “fmt” que irá orientar o modo de interpretar o texto a ser convertido).. TO_DATE('01/02/03','dd/mm/yy') TO_DATE('20030130','YYYYMMDD'). Outras funções de conversão: CHARTOROWID, TO_SINGLE_BYTE, etc.. Máscaras de formatação: abaixo seguem alguns exemplos de máscaras de formatação (para mais detalhes consulte sobre “format models”, na documentação do Oracle). Considerar que as máscaras são sempre definidas em uma string (texto entre apóstrofos). Valor Máscara de formatação Valor apresentado 1000.5. '999999999.99'. 1000.5. 'FM999999999.99'. 1000.5. '000000000.00'. 1000.5. 'FM000000000.00'. 1000.5. '999,999,999.99'. sysdate. 'dd/mm/yyyy'. sysdate. 'dd/mm/yyyy hh24:mi:ss'. sysdate. 'day'. sysdate. 'dy'. sysdate. 'month'. sysdate. 'mon'. Funções diversas (miscelâneas) Função/sintaxe básica Descrição NVL(v,s). Outras miscelâneas:. 1000.50 1000.5 000001000.50 000001000.50 1,000.50 29/09/2003 29/09/2003 14:03:45 monday mon september sep. Exemplos. Retorna o valor “v” (caractere, numérico ou data) caso não seja nulo (NULL). Se “v” for nulo, será retornado o valor substituto “s”.. NVL(valor, 0). DECODE, NVL2, USER, USERENV, VSIZE, etc.. Banco de Dados – II: Consultas SQL (1ª parte). Uninove – prof. André Santos – set./2003 - rev.: out./2005 – pág. 7 / 7.

(8) Funções de grupo Uma função de grupo (agregação) retorna um único valor correspondente a um conjunto de linhas (ao invés de um valor para cada linha). OBS.:. Consultas mais complexas, com agrupamentos, serão vistas numa próxima etapa do estudo.. Funções de grupo Função/sintaxe básica. Descrição. Exemplos. Passando um asterisco (*) com argumento, retorna a quantidade de linhas da consulta.. COUNT(*) COUNT(x) COUNT(DISTINCT x). Se o argumento for o nome de uma coluna (ou uma expressão), irá retornar a quantidade de linhas nas quais o valor correspondente não seja nulo.. COUNT(*) COUNT(data_nasc) COUNT(DISTINCT nome). Com a palavra reservada DISTINCT, serão desconsiderados os valores repetidos, na contagem. SUM(n). Retorna a soma dos valores da coluna (ou expressão) numérica.. SUM(salario) SUM(salario * 1.10). Serão consideradas apenas as linhas da consulta nas quais o valor de “n” não seja nulo. AVG(n). Retorna a média dos valores da coluna (ou expressão) numérica.. AVG(salario) AVG(qtde * preco_unit). Serão consideradas apenas as linhas da consulta nas quais o valor de “n” não seja nulo, tanto na soma quanto na divisão para obtenção da média. MIN(x). Retorna o mínimo valor (caractere, numérico ou data) da coluna (ou expressão).. MIN(data_nasc). Valores nulos não são considerados. MAX(x). Retorna o máximo valor (caractere, numérico ou data) da coluna (ou expressão).. MAX(salario) MAX(nome). Valores nulos não são considerados. Outras de grupo:. STDDEV, VARIANCE, etc.. Banco de Dados – II: Consultas SQL (1ª parte). Uninove – prof. André Santos – set./2003 - rev.: out./2005 – pág. 8 / 8.

(9)