terça-feira, 27 de novembro de 2012

Sql Server JOINs - LEFT JOIN- Parte 2

Pessoal, dando continuidade aos tipos de junções entre tabelas vamos falar agora sobre as junções do tipo OUTER. Elas podem ser: LEFT, RIGHT e FULL. Nesta publicação falaremos especificamente a respeito da junção LEFT OUTER JOIN. A utilização deste operador permite fazer a junção entre duas tabelas, de modo que a tabela a esquerda do operador LEFT JOIN tenha todos os seus registros retornados (respeitando as condições da cláusula WHERE, se ela existir) independente de existir um registro correspondente na tabela a direita do operador LEFT JOIN. Utilizando o banco de dados AdventureWorks vamos ver um exemplo do funcionamento desse operador com o seguinte select:


SELECT P.FirstName, P.LastName,  P.BusinessEntityID, E.BusinessEntityID,  E.VacationHours, E.SickLeaveHours 
FROM Person.Person P 
LEFT OUTER JOIN HumanResources.Employee E 
ON E.BusinessEntityID = P.BusinessEntityID;

Os 3 primeiros registros retornados do select acima são:




Com base no resultado chegamos a conclusão que no registro 1 temos os campos FirstName, LastName e BusinessEntityID, ambos da tabela Person, e este registro tem uma referência na tabela Employee no campo   BusinessEntityID visto que o valor ambos os campos são iguais a 285, logo as colunas E.BusinessEntityID,  E.VacationHours e E.SickLeaveHours da tabela Employee tem dados. Porem os outros 2 registros não possuem referência na tabela Employee por isso não existe dados nas colunas E.BusinessEntityID,  E.VacationHours e E.SickLeaveHours.

Vimos no exemplo acima que utilizando o operador LEFT JOIN os dados da tabela Person sempre serão retornados, já os da tabela Employee, só serão retornados se existir referência com Person.

Versão do Sql Server utilizada nessa publicação:
Microsoft SQL Server 2008 (SP3) - 10.0.5512.0

quarta-feira, 14 de novembro de 2012

Sql Server operador LIKE

Eventualmente precisamos recuperar registros do banco de dados de acordo com um determinado padrão de string. O Sql Server disponibiliza duas formas de efetuar esse filtro, através do operador LIKE ou do predicado CONTAINS. Neste post vamos abordar o funcionamento do operador LIKE.

De uma forma geral utilizamos o operador LIKE na clausula WHERE da seguinte forma:

match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

Onde:


  • match_expression é uma expressão sql válida que retorna um tipo string (CHAR, VARCHAR, NVARCHAR, NCHAR);
  • [NOT] operador opcional utilizado para negar qualquer expressão boleana;
  • LIKE operador responsável pelo filtro, retorna TRUE se for encontrado o padrão pattern em match_expression, caso contrario retorna FALSE;
  • pattern é o padrão que será utilizado para efetuar o filtro nos registros com base na match_expression, pode ter até 8000 caracteres;
  • [ ESCAPE escape_character ] carácter opcional usado como literal para flexibilizar o operador LIKE informando que o carácter coringa deve ser interpretado como um carácter regular não como carácter curinga, veremos alguns exemplos desses caracteres abaixo. 
Para executar os exemplos utilizaremos o Sql Server Management Studio do Microsoft SQL Server 2008 (SP3) - 10.0.5512.0

Num banco de dados qualquer criado por você, abra uma nova consulta com conexão local (arquivo > novo > consulta com conexão local). Vamos criar uma tabela temporária e inserir alguns registros para fazer os testes.

Script da tabela temporária:


CREATE TABLE #CONTATO
(
 ID INT,
 NOME VARCHAR(100),
 EMAIL VARCHAR(100),
 PERCENTUAL VARCHAR(4)
)

Script para inserir os registros:


INSERT INTO #CONTATO VALUES
(1,'Maria de Fatima','mariaf@gmail.com','5%'),
(2,'Antonio Jose','antoniojose@yahoo.com','15%'),
(3,'Fernanda de Carvalho','fc@yahoo.com','70%'),
(4,'Uelinton Josenildo','josenildo.u@gmail.com','35%'),
(5,'Welinton Barbosa','wb@yahoo.com','84%')

Obs: Não feche a sua conexão, está é uma tabela temporária e está em memória, se a conexão for interrompida ela deixará de existir, outra forma de eliminar a tabela é através do comando DROP TABLE #CONTATO)

Vamos aos exemplos:

1- Selecionar todos os registros que no campo nome esteja contido exatamente o termo "Maria de Fatima".

SELECT * FROM #CONTATO WHERE nome like 'Maria de Fatima'

No comando acima estamos dizendo para o banco "eu quero todos os registros que no campo nome contenha exatamente a string "Maria de Fatima". Neste caso teríamos como resultado o registro cujo id é igual a 1.

2- Vamos supor que nossa aplicação executa um procedimento de pesquisa no banco onde a string de pesquisa não contenha espaços em branco, todo espaço em branco é substituído por underline, por exemplo "Antonio_Jose". Então para pesquisar todos os registros que no campo nome esteja contido exatamente o termo "Antonio_Jose" precisaremos fazer uma operação na coluna nome ficando da seguinte forma:

SELECT * FROM #CONTATO where REPLACE(nome,' ','_') LIKE 'Antonio_Jose'

Neste caso o operador LIKE está validando o resultado da expressão (REPLACE(nome,' ','_')) que contem a coluna nome, em vez de validar o termo diretamente na coluna como no exemplo 1. Neste caso teríamos como resultado o registro cujo id é igual a 2.


3- Agora vamos começar a flexibilizar o operador LIKE. Queremos selecionar todos os registros cuja a coluna nome contenha, em qualquer lugar, a string "Jose". Neste caso devemos utilizar o carácter curinga %.

SELECT * FROM #CONTATO where nome LIKE '%Jose%'

No comando acima estamos dizendo, "eu quero todos os registros que contenha o termo 'Jose' em qualquer parte da string presente na coluna nome". Desta forma seriam retornados os registros 2 (Antonio Jose) e 4 (Uelinton Josenildo). Se quiséssemos apenas os registros cujo o campo nome começassem com a string 'Jose' o carácter curinga só seria colocado a frente do termo, formando o pattern 'Jose%'. Como não existe nenhum nome que comece com Jose, nenhum registro será retornado.


4- E se o campo a ser pesquisado contivesse um carácter curinga  Neste caso precisamos de um artifício que faça o Sql Server interpretar o carácter curinga como um carácter literal. Para resolver o problema utilizamos a palavra reservada ESCAPE e um carácter auxiliar como no exemplo abaixo:

SELECT * FROM #CONTATO WHERE PERCENTUAL LIKE '%@%%' ESCAPE '@'

O select acima retornará todos os registros porque todos os dados da coluna percentual terminam com o sinal "%" que neste caso é interpretado como um carácter literal. Outra forma de obter o mesmo resultado seria colocando o carácter curinga entre colchetes. Assim:


SELECT * FROM #CONTATO WHERE PERCENTUAL LIKE '%[%]%'

5- Podemos pesquisar um único carácter também. Para obter esse recurso utilizamos o carácter underline. O select abaixo pode ser traduzido da seguinte forma: Pegue todos os contatos cujo nome comece com qualquer letra e termine com 'elinton'.

SELECT * FROM #CONTATO WHERE NOME LIKE '_elinton'

O select acima não retornará nenhum registro porque não existe nenhum contato onde o nome tenha apenas uma palavra. No exemplo 6 poderemos resolver esse problema.

6- Continuando com o mesmo problema do exemplo 5, faremos uma pequena modificação para poder obter todos os nomes que comecem com qualquer letra seguido da string 'elinton', porem desconsideraremos a string que vier depois, se houver. Neste caso devemos utilizar um segundo carácter curinga  Sim! Podemos utilizar mais de um carácter curinga. Vejamos como fica:

SELECT * FROM #CONTATO WHERE NOME LIKE '_elinton%'

Como resultado teremos os registros de id 4 e 5. Porque estamos dizendo:"eu não sei qual é a primeira letra mas depois dela vem a string 'elinton' e depois pode vir qualquer coisa".

7- No exemplo 6 resolvemos o problema da pesquisa, mas deixamos muito flexível, visto que o servidor terá que testar todos os carácteres possíveis. Podemos limitar essa pesquisa da seguinte forma:

SELECT * FROM #CONTATO WHERE NOME LIKE '[UW]elinton%'

Veja que agora estamos dizendo, "eu não sei qual é a primeira letra, mas sei que pode ser U ou W seguido de 'elinton' e o resto dos carácteres pode ser qualquer coisa.". Dessa forma limitamos o primeiro curinga a duas letras, mas poderia ser também um intervalo da seguinte forma:

SELECT * FROM #CONTATO WHERE NOME LIKE '[M-W]elinton%'

Assim o Sql Server tentará encontrar um nome cuja a primeira letra seja alguma de M até W.

8- E por fim podemos usar o carácter ^ (acento circunflexo) para excluir um carácter, conjunto ou intervalo. Vejamos abaixo como fica:

SELECT * FROM #CONTATO WHERE NOME LIKE '[^W]elinton%' 

No exemplo acima estamos dizendo "o primeiro carácter pode ser qualquer um menos o W". Neste caso somente o registro cujo id é 4 será retornado. Abaixo, vamos fazer a exclusão de um grupo de carácteres:

SELECT * FROM #CONTATO WHERE NOME LIKE '[^ABCD]elinton%'

Acima podemos ver como podemos dizer ao Sql Server que não queremos determinados carácteres. No exemplo informamos que os contatos podem começar com qualquer letra menos A,B,C,D, seguido da string 'elinton'. O exemplo acima poderia ser escrito também como um intervalo da seguinte forma:

SELECT * FROM #CONTATO WHERE NOME LIKE '[^A-D]elinton%'

Bom pessoal, espero ter conseguido exemplificar um pouco do funcionamento do operador LIKE no Sql Server. Antes de terminar gostaria de salientar algumas observações importantes:


  1. Verifique a collation do seu servidor/banco/tabela/coluna, a depender da configuração o resultado pode não ser o esperado;
  2. Atente para os tipos de dados da match_expression e pattern, CHAR e VARCHAR armazenam os dados de forma diferente;
  3. Quandos os argumentos (match_expression e pattern) são do tipo ASCII tudo será executado como ASCII, caso um dos argumentos sejam Unicode, todos serão convertidos para Unicode.


Sql Server JOINs - INNER JOIN- Parte 1

Em banco de dados normalizados, as informações necessárias para um único conjunto de dados podem estar localizadas em mais de uma tabela. Para suprir essa necessidade, utilizamos o operador JOIN.
Existem vários tipos de operadores JOINs, cada um com seu propósito dentro desse contexto de junção de tabelas. Os operadores são:

INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, e CROSS.

Neste post vamos falar especificamente do operador INNER. Quando desejamos fazer junções entre tabelas, precisamos definir as tabelas que serão relacionadas, o tipo de operador JOIN que será utilizado e o padrão que será utilizado para retonar os registros das tabelas. No exemplo abaixo (executado no banco Adventure Works) as tabelas são: "HumanResources.Employee" e "Person.Person", o operador é "INNER JOIN" e a condição ou padrão é "HumanResources.Employee.BusinessEntityID = Person.Person.BusinessEntityID"

SELECT FirstName,LastName,JobTitle,VacationHours,SickLeaveHours
FROM HumanResources.Employee 
INNER JOIN Person.Person
ON HumanResources.Employee.BusinessEntityID = Person.Person.BusinessEntityID;

Quando não especificamos o tipo de JOIN (FROM HumanResources.Employee
JOIN Person.Person) o Sql Server adota o INNER JOIN como padrão.
Na clausula da condição estamos especificando o nome completo (schema.tabela.coluna) da coluna para evitar ambiguidade, visto que possuem o mesmo nome e estão em ambas as tabelas.

Mas qual a definição do INNER JOIN? Basicamente ele retorna os registros das tabelas especificadas que satisfação a condição. Só serão retornados os registros da tabela Employee que tenha o valor do seu campo BusinessEntityID referênciado na tabela Person no campo BusinessEntityID. Execute a query acima e veja o resultado. O registro de Employee que não tiver nenhuma referência em Person não será retornado e vice-versa.

No exemplo acima o SELECT possui apenas 2 tabelas, porem o INNER JOIN pode ser utilizado N vezes seguindo a regra descrita acima. Na condição do INNER JOIN utilizamos os campos que representam a chave primária e estrangeira das tabelas de modo a garantir a integridade referencial dos dados. Alem disso a condição especificada possui apenas uma regra mas pode existir N regras também, vejamos o exemplo abaixo:


SELECT FirstName,LastName,JobTitle,VacationHours,SickLeaveHours
FROM HumanResources.Employee E
INNER JOIN Person.Person P ON E.BusinessEntityID = P.BusinessEntityID and VacationHours > 30
INNER JOIN HumanResources.EmployeeDepartmentHistory EH ON EH.BusinessEntityID = E.BusinessEntityID;


Versão do Sql Server utilizada:
Microsoft SQL Server 2008 (SP3) - 10.0.5512.0

Sql Server Termo Expressão

Estudando a documentação do Sql Server (Books online) sempre nos deparamos com o termo "expressão". Mas o que vem a ser esse termo? De um modo geral podemos sintetizar o significado dessa palavra da seguinte forma:

"É uma combinação de símbolos e operadores que o mecanismo de banco de dados avalia para obter um valor " (Microsoft Database Development 2008 70-433)

Por exemplo, utilizando o banco de dados Adventure Works executaremos a seguinte query:


SELECT PP.FirstName + ' ' + PP.LastName as "Employee Name",
PP.PersonType as Category, 'Employee' as "Description"
FROM Person.Person as PP
WHERE PP.PersonType = 'EM';

A primeira coluna (Employee Name) desse select é uma expressão, porque retorna o resultado da concatenação do primeiro com o ultimo nome do empregado.

Versão do Sql Server utilizada nessa publicação:
Microsoft SQL Server 2008 (SP3) - 10.0.5512.0

terça-feira, 13 de novembro de 2012

Sql Server Operador BETWEEN

Olá pessoal, o operador lógico BETWEEN é utilizado no Sql Server quando se deseja resgatar dados baseados num intervalo a ser especificado na clausula WHERE. Sua sintaxe é:

test_expression [ NOT ] BETWEEN begin_expression AND end_expression

Onde:

  • test_expression é a expressão a ser testada no intervalo definido em begin_expression e end_expression;
  • NOT é o operador lógico opcional para negar o resultado do teste;
  • begin_expression é uma expressão que indica o valor inicial do teste;
  • end_expression é uma expressão que indica o valor final do teste;
  • AND operador lógico que indica que test_expression deve estar dentro do intervalo (ou fora caso o operador NOT seja utilizado) compreendido entre begin_expression e end_expression.
Obs.: test_expression, begin_expression e end_expression devem ser do mesmo tipo de dados.

Vamos a alguns exemplos (utilizando o banco AdventureWorks disponibilizado aqui):

1- Selecionar os funcionários com taxa de pagamento entre 27 e 30.


SELECT e.FirstName, e.LastName, ep.Rate
FROM HumanResources.vEmployee e 
JOIN HumanResources.EmployeePayHistory ep 
ON e.BusinessEntityID = ep.BusinessEntityID
WHERE ep.Rate BETWEEN 27 AND 30
ORDER BY ep.Rate;

Com este exemplo podemos verificar que o operador BETWEEN é inclusivo, ou seja, os valores dos extremos do intervalo são levados em consideração.

2- Selecionar os funcionários com taxa de pagamento fora do intervalo 27 a 30.

SELECT e.FirstName, e.LastName, ep.Rate
FROM HumanResources.vEmployee e 
JOIN HumanResources.EmployeePayHistory ep 
ON e.BusinessEntityID = ep.BusinessEntityID
WHERE ep.Rate NOT BETWEEN 27 AND 30
ORDER BY ep.Rate;

Neste caso utilizamos o operador NOT para obter o resultado desejado. 

Versão do Sql Server utilizada:
Microsoft SQL Server 2008 (SP3) - 10.0.5512.0

segunda-feira, 12 de novembro de 2012

Sql Server Banco de dados Adventure Works

Pessoal,

Nas próximas publicações utilizarei o banco de dados exemplo que a Microsoft disponibiliza chamado AdventureWorks. Vocês pode fazer o download dele no meu google drive aqui ou diretamente no site da microsoft, aqui . Para fazer a instalação do banco é simples, basta seguir os passos abaixo:
  1. Descompactar o arquivo;
  2. Copiar os arquivos AdventureWorks2008_Data.mdf e AdventureWorks2008_Log.ldf para C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA;
  3. No Sql Server Management Studio conecte a sua instancia na janela Object Explorer;
  4. No nó "Banco de dados" clique com o direito em "Anexar...";
  5. Clique em Adicionar escolha AdventureWorks2008_Data.mdf e depois clique em OK;
  6. Novamente clique em Adicionar escolha AdventureWorks2008_Log.ldf e depois clique em OK;
  7. Depois clique em OK novamente para fechar a janela "Anexar Banco de Dados"
Esse processo foi feito no Sql server 2008, mas acredito que o processo seja semelhante nas outras versões. Qualquer dúvida é só comentar.