tecnologia | consultoria | banco de dados

Banco de Dados - Grupos de Dados (GROUP BY)

A cláusula GROUP BY facilita a criação de grupos, a qual aparece depois das cláusulas WHERE, mas antes da cláusula ORDER BY. Esta cláusula é bastante utilizada em conjunto com funções de grupo para realizar contagens, localizar o maior / menor valor de um grupo ou do conjunto de registros de uma tabela.

Exemplo (agrupando por departamento):
OBS: a sintaxe abaixo mostrará por departamento a quantidade de funcionários, o maior e menor salário.
SELECT
id_departamento,
MAX(vl_salario) AS maior_salario,
MIN(vl_salario) AS menor_salario,
COUNT(*) AS quantidade_funcionarios
FROM tb_funcionarios
GROUP BY id_departamento
ORDER BY id_departamento;

Exemplo (sem agrupamento, escopo total da tabela):
OBS: a sintaxe mostrará a quantidade de funcionários, o maior e menor salário da tabela.
SELECT
MAX(vl_salario) AS maior_salario,
MIN(vl_salario) AS menor_salario,
COUNT(*) AS quantidade_funcionarios
FROM tb_funcionarios;
Data publicação: 18:16 12/07/2020

Banco de Dados - Funções (Functions)

Uma função é um programa gravado para aceitar opcionalmente parâmetros de entrada, realizar uma operação ou retornar um valor único. Uma função retorna apenas um valor por execução.

Funções de Linhas Simples:
   - UPPER
Retorna a conversão da cadeia de caracteres em letras maiúsculas;
Exemplo Oracle: SELECT UPPER('texto em letras minúsculas') AS texto FROM DUAL;
Exemplo SQL Server: SELECT UPPER('texto em letras minúsculas') AS texto;
- LOWER
Retorna a conversão da cadeia de caracteres em letras minúsculas;
Exemplo Oracle: SELECT LOWER('texto em letras maiúsculas') AS texto FROM DUAL;
Exemplo SQL Server: SELECT LOWER('texto em letras maiúsculas') AS texto;
- SUBSTRING
Retorna parte da cadeia de caracteres de acordo com os parâmetros informados;
Exemplo Oracle: SELECT SUBSTRING('Database', 1, 4) AS texto FROM DUAL;
Exemplo SQL Server: SELECT SUBSTRING('Database', 1, 4) AS texto;
Funções Oracle:
- LENGTH
Retorna a quantidade de caracteres;
Exemplo: SELECT LENGTH('Paralelepípedo') AS palavra FROM DUAL;
- SYSDATE
Retorna a data atual;
Exemplo: SELECT SYSDATE AS data_atual FROM DUAL;
Funções Microsoft SQL Server:
- LEN
Retorna a quantidade de caracteres;
Exemplo: SELECT LEN('Paralelepípedo') AS palavra;
- GETDATE()
Retorna a data atual;
Exemplo: SELECT GETDATE() AS data_atual;

Funções de Linhas Múltiplas / Grupo:
   - MAX
Retorna o valor máximo;
Exemplo: SELECT MAX(VL_SALARIO) AS MAIOR_SALARIO FROM TB_FUNCIONARIOS;
- MIN
Retorna o valor mínimo;
Exemplo: SELECT MIN(VL_SALARIO) AS MENOS_SALARIO FROM TB_FUNCIONARIOS;
- SUM
Retorna a soma dos valores;
Exemplo: SELECT SUM(VL_SALARIO) AS TOTAL_SALARIO FROM TB_FUNCIONARIOS;
- AVG
Retorna a média dos valores;
Exemplo: SELECT AVG(VL_SALARIO) AS MEDIA_SALARIAL FROM TB_FUNCIONARIOS;
- COUNT
Retorna a quantidade de registros;
Exemplo: SELECT COUNT(*) AS QTD_FUNCIONARIOS FROM TB_FUNCIONARIOS;
Data publicação: 20:18 09/05/2020

Banco de Dados - Classificando Dados (ORDER BY)

A cláusula ORDER BY é responsável pela transformação da saída da consulta em dados classificados. Esta cláusula é sempre a última cláusula em uma declaração SELECT.

Classificação Ascendente (ASC):
   SELECT * FROM tb_funcionarios ORDER BY nm_funcionario ASC;
SELECT * FROM tb_funcionarios ORDER BY nm_funcionario;
Cláusulas específicas do Oracle:
SELECT * FROM tb_funcionarios ORDER BY nm_funcionario ASC NULLS FIRST;
SELECT * FROM tb_funcionarios ORDER BY nm_funcionario ASC NULLS LAST;

Classificação Decrescente (DESC):
   SELECT * FROM tb_funcionarios ORDER BY nm_funcionario DESC;
Cláusulas específicas do Oracle:
SELECT * FROM tb_funcionarios ORDER BY nm_funcionario DESC NULLS FIRST;
SELECT * FROM tb_funcionarios ORDER BY nm_funcionario DESC NULLS LAST;
Data publicação: 21:20 25/12/2019

Banco de Dados - Restringindo a Seleção de Dados (WHERE)

A cláusula WHERE estende a declaração SELECT ao fornecer o idioma às linhas restringidas retornadas, baseadas em uma ou mais condições.

Exemplos:
SELECT * FROM tb_funcionarios WHERE nm_funcionario = 'LEANDRO';
SELECT * FROM tb_funcionarios WHERE nm_funcionario IN('LEANDRO', 'Leandro');
SELECT * FROM tb_funcionarios WHERE nm_funcionario LIKE 'Leandro%';
SELECT * FROM tb_funcionarios WHERE nm_funcionario IS NULL;

Ponto de Atenção: O banco de dados Oracle é "Sensível a Maiúsculas" (Case Sensitive), portanto ao filtrar uma coluna desconhecida pode ser necessária a utilização de fórmulas para transformar o texto em maiúsculas (este será tema abordado no próximo tópico).
Data publicação: 21:54 01/10/2019

Banco de Dados - Seleção de Dados (SELECT)

A declaração SELECT é um mecanismo flexível para recuperar informações de uma tabela de banco dados. Esta declaração nunca altera o as informações armazenadas no banco de dados.

Exemplos:
SELECT * FROM tb_funcionarios;
SELECT DISTINCT nm_funcionario FROM tb_funcionarios;

Oracle:
SELECT 'Olá mundo!' FROM dual;

Microsoft SQL Server:
SELECT 'Olá mundo!';

Data publicação: 19:29 29/09/2019

Banco de Dados - Tipos de Armazenamento de Dados (Data Types)

Para armazenar os dados em um banco de dados é necessário ter conhecimento prévio sobre o tipo de dados que serão inseridos em cada coluna das tabelas. Abaixo será listado os principais tipos de dados.

Oracle:
Texto:
CHAR
VARCHAR2
Número:
NUMBER
FLOAT
Data:
DATE
INTERVAL

Microsoft SQL Server:
Texto:
CHAR
VARCHAR
Número:
INT
BIGINT
FLOAT
MONEY
Data:
DATETIME
SMALLDATETIME
Data publicação: 19:27 29/09/2019

SQL Server - Exportar tabelas utilizando BCP

Nesta postagem irei mostrar como exportar tabelas utilizando o utilitário BCP (Bulk Copy Program) do banco de dados Microsoft SQL Server.

Sintaxe do utilitário BCP:
bcp {table|view|"query"}
{out|queryout|in|format}
{data_file|nul}
{[optional_argument]...}

Parâmetros para indicar importação ou exportação:
- out: Indica que o utilitário irá exportar uma tabela ou view para um arquivo.
- queryout: Indica que o comando irá exportar os dados de uma query para um arquivo.
- in: Indica que o utilitário irá importar os dados de um arquivo para uma tabela.

Para exemplificar a exportação, foi utilizado um arquivo batch (exporta_tb_alunos.bat) com o código abaixo.
bcp estudos..tb_alunos out "C:\EXPORT\tb_alunos.txt" -T -c

Explicando brevemente o código acima, "estudos..tb_alunos" é o nome da database seguido do nome da tabela a ser exportada, "out" informa que a tabela será exportada no arquivo informado, "-T" é o parâmetro para Windows Authentication (utiliza o usuário e senha do Windows para acessar o banco de dados), "-c" indica que as colunas serão exportadas como caracteres.

Abaixo será mostrado outra forma de exportar a mesma tabela utilizando query.
bcp "select * from estudos..tb_alunos" queryout "C:\EXPORT\tb_alunos.txt" -T -c

Parâmetros:
-S     => Servidor
-F2 => Primeira linha a importar (neste exemplo, segunda linha)
-L100 => Última linha a importar (neste exemplo, centésima linha)
-U => Usuário do banco de dados
-P => Senha do usuário do banco de dados
-c => Tipo de dados caractere (delimitadores padrões: linha "ENTER" e coluna "TAB")
-w => Tipo de dados unicode (delimitadores padrões: linha "ENTER" e coluna "TAB")
-t"\t" => Delimitador de coluna (neste exemplo, "TAB")
-r"\n" => Delimitador de linha (neste exemplo, "ENTER")
-T => Windows Authentication
-e => Arquivo para armazenamento dos erros de importação
-o => Arquivo para armazenamento do output após a execução do BCP
-f => Format file cujo define a estrutura da tabela

Publicações relacionadas:
- SQL Server - Importar tabelas utilizando BCP.
Data publicação: 22:01 12/07/2018

Oracle - Expressões regulares

-- Manter apenas números na coluna

SELECT
REGEXP_REPLACE('REGISTRO 123.456.789-10', '[^0-9]+', '')
FROM dual;
-- Resultado: 12345678910
SELECT
REGEXP_REPLACE('ABC12345DEF', '[^[:digit:]]+', '')
FROM dual;
-- Resultado: 12345
SELECT
REGEXP_REPLACE('1A2B3C4D', '\D+', '')
FROM dual;
-- Resultado: 1234

-- Selecionar linhas numéricas

SELECT
e.col1
FROM tb_leandro e
WHERE REGEXP_LIKE(e.col1 , '^[[:digit:]]+$');

-- Selecionar linhas com letras e espaços

SELECT
e.col1
FROM tb_leandro e
WHERE REGEXP_LIKE(e.col1 , '^[[:alpha:][:space:]]+);

-- Contar espaço no início da string antes de algum carácter

SELECT
NVL(LENGTH(REGEXP_SUBSTR(' Four Spaces', '^[[:space:]]+',1,1)),0) AS Result
FROM dual;
-- Resultado: 4
SELECT
REGEXP_INSTR(' Four Spaces', '\S',1,1) AS Result
FROM dual;
-- Resultado: 4
SELECT
NVL(LENGTH(REGEXP_SUBSTR(' Two Spaces', '^[[:space:]]+',1,1)),0) AS Result
FROM dual;
--Resultado: 2

-- Extrair string que possui | como delimitador

SELECT
REGEXP_SUBSTR('One|Two|Three|Four|Five', '[^|]+',1,1) AS Result
FROM dual;
-- Resultado: One
SELECT
REGEXP_SUBSTR('One|Two|Three|Four|Five', '[^|]+',1,4) AS Result
FROM dual;
-- Resultado: Four

-- Extrair primeiro e último valor da string

SELECT
REGEXP_REPLACE('Leandro Sacramento de Paula', ' .+ ', ' ')
From dual;
-- Resultado: Leandro Paula


CARÁCTER FUNÇÃO
\ Carácter de escape
^ Posição inicial da linha (string)
$ Posição final da linha (string)
[ ] Corresponde ao caracteres listados entre os colchetes
[^ ] Não corresponde aos caracteres listados entre os colchetes
( ) Sub-expressão ou grupo. Pode ser uma string ou uma expressão complexa
. Qualquer caractere ("coringa")
+ Uma ou mais ocorrências da sub-expressão precedente
? Zero ou uma ocorrência da sub-expressão precedente
* Zero ou mais ocorrências da sub-expressão precedente
{n} Número de caracteres exatas da sub-expressão precedente
{m,} Número de ocorrências de pelo menos m vezes da sub-expressão precedente
{m,n} Número de ocorrências de pelo menos m vezes, mas não mais que n vezes da sub-expressão precedente
| Alternativa de padrão de busca, por exemplo ('leandro'|'sacramento')
Data publicação: 01:27 13/11/2015

Oracle - Consulta aleatória e inserção de múltiplos registros

No Banco de Dados Oracle, existe a possibilidade de retornar registros aleatórios em uma consulta utilizando a procedure RANDOM da package DBMS_RANDOM.

A procedure RANDOM retorna um número aleatório positivo ou negativo.

SELECT DBMS_RANDOM.RANDOM FROM DUAL;
Resultado 1: -1640736766
Resultado 2: 58909080


Para o exemplo da consulta aleatória, foi criada uma tabela com os meses do ano.

Em cada consulta realizada os registros serão retornados em ordem aleatória:

SELECT *
FROM TB_MESES
ORDER BY DBMS_RANDOM.RANDOM;


Retornar 3 meses aleatórios a cada consulta:

SELECT *
FROM (SELECT *
FROM TB_MESES
ORDER BY DBMS_RANDOM.RANDOM)
WHERE ROWNUM <= 3;


Tabela utilizada no exemplo:


CREATE TABLE TB_MESES
(
CD_MES NUMBER,
NM_MES VARCHAR2(10)
);
INSERT ALL
INTO TB_MESES (CD_MES, NM_MES) VALUES (1, 'Janeiro')
INTO TB_MESES (CD_MES, NM_MES) VALUES (2, 'Fevereiro')
INTO TB_MESES (CD_MES, NM_MES) VALUES (3, 'Março')
INTO TB_MESES (CD_MES, NM_MES) VALUES (4, 'Abril')
INTO TB_MESES (CD_MES, NM_MES) VALUES (5, 'Maio')
INTO TB_MESES (CD_MES, NM_MES) VALUES (6, 'Junho')
INTO TB_MESES (CD_MES, NM_MES) VALUES (7, 'Julho')
INTO TB_MESES (CD_MES, NM_MES) VALUES (8, 'Agosto')
INTO TB_MESES (CD_MES, NM_MES) VALUES (9, 'Setembro')
INTO TB_MESES (CD_MES, NM_MES) VALUES (10, 'Outubro')
INTO TB_MESES (CD_MES, NM_MES) VALUES (11, 'Novembro')
INTO TB_MESES (CD_MES, NM_MES) VALUES (12, 'Dezembro')
SELECT 1 FROM DUAL;
COMMIT;

Segue acima, exemplo de inserção de vários registros com apenas uma query em uma tabela. Também possível inserir registros em mais de uma tabela.
Data publicação: 21:36 24/03/2015

Oracle - Formatação de números

Para formatar números no Oracle para exibição, por exemplo, em relatórios é necessário utilizar a função TO_CHAR.

Função = TO_CHAR(número, ['máscara'], ['nls_parameters'])

Comandos da máscara:
D = Marcador decimal
G = Separador de milhar
FM = Remove os espaços em branco adicionais

Comandos do NLS_PARAMETERS:
NLS_NUMERIC_CHARACTERS = dg
OBS: o primeiro elemento, d, indica o caracter a ser utilizado com marcador decimal, enquanto o segundo elemento, g, indica o caracter a ser utilizado como separador de milhar.


SELECT
TO_CHAR(1013.50, 'FM999G999G990D00', 'NLS_NUMERIC_CHARACTERS=,.')
FROM DUAL;
-- Resultado: 1.013,50
SELECT
TO_CHAR(1013.50, 'FM999G999G990D00', 'NLS_NUMERIC_CHARACTERS=, ')
FROM DUAL;
-- Resultado: 1 013,50
SELECT
TO_CHAR(1013.50, 'FM099G999G990D00', 'NLS_NUMERIC_CHARACTERS=,.')
FROM DUAL;
-- Resultado: 000.001.013,50

Para converter número no formato brasileiro, separador decimal com vírgula, para o formato americano, separador decimal com ponto, basta utilizar a seguiente instrução:


SELECT
TO_NUMBER('1212,545', '9999999999990D000000000000', 'NLS_NUMERIC_CHARACTERS=,.')
FROM DUAL;
-- Resultado: 1212.545

Data publicação: 16:00 01/03/2015

Oracle - Agrupamento por faixa de valores

Para este exemplo, utilizei a tabela Employees do Schema HR padrão do Oracle.


SELECT
(CASE
WHEN TRUNC((MONTHS_BETWEEN(SYSDATE,hire_date)/12),0) BETWEEN 0 AND 4 THEN '0 a 4 anos'
WHEN TRUNC((MONTHS_BETWEEN(SYSDATE,hire_date)/12),0) BETWEEN 5 AND 8 THEN '5 a 8 anos'
WHEN TRUNC((MONTHS_BETWEEN(SYSDATE,hire_date)/12),0) BETWEEN 9 AND 12 THEN '9 a 12 anos'
ELSE 'Superior ou 13 anos'
END) AS "FAIXA",
COUNT(*) AS "TOTAL"
FROM hr.employees
GROUP BY
(CASE
WHEN TRUNC((MONTHS_BETWEEN(SYSDATE,hire_date)/12),0) BETWEEN 0 AND 4 THEN '0 a 4 anos'
WHEN TRUNC((MONTHS_BETWEEN(SYSDATE,hire_date)/12),0) BETWEEN 5 AND 8 THEN '5 a 8 anos'
WHEN TRUNC((MONTHS_BETWEEN(SYSDATE,hire_date)/12),0) BETWEEN 9 AND 12 THEN '9 a 12 anos'
ELSE 'Superior ou 13 anos'
END);

OBS: poderia ser utilizado tanto TRUNC(coluna_data, 0) como FLOOR(coluna_data).
Data publicação: 10:09 30/09/2014

Banco de Dados - Declarações SQL

Nesta publicação, irei listar como os comandos de banco de dados estão divididos / classificados.

1. DML - Data Manipulation Language (Linguagem de Manipulação de Dados)

SELECT
INSERT
UPDATE
DELETE
MERGE

OBS: alguns autores não consideram a instrução SELECT como DML.

2. DDL - Data Definition Language (Linguagem de Definição de Dados)

CREATE
ALTER
DROP
TRUNCATE
RENAME
COMMENT

3. DCL - Data Control Language (Linguagem de Controle de Dados)
Instruções Oracle:
GRANT
REVOKE

4. TCL - Transaction Control Language (Linguagem de Controle de Transações)

COMMIT
ROLLBACK
Instruções Oracle:
SAVEPOINT
Instruções Microsoft SQL Server:
BEGIN TRANSACTION

Ponto de Atenção: No banco de dados Oracle, é necessário executar a cláusula COMMIT ou ROLLBACK após a execução de um comando DML (com exceção do SELECT). Comandos DDL possuem a execução do COMMIT implícito.
Data publicação: 16:02 10/09/2014
Perfil
Olá jovem Padawan, seja bem vindo! Este site foi criado com o intuito de compartilhar um pouco de conhecimento de Tecnologia da Informação e Business Intelligence.

Linkedin  Youtube

"Eu não sei como vencer os outros; sei apenas como vencer a mim mesmo." (Yagu Munenori)


Leandro Sacramento, Todos os direitos reservados - 2012 - 2020