Guia Prático de Consultas e Funções em SQL

Filtros com WHERE e Operador LIKE

SELECT * FROM CUSTOMERS WHERE CustomerID = 'ANTON'
-- Nome Anton é o único que será encontrado

SELECT * FROM CUSTOMERS WHERE COMPANYNAME LIKE '[B-E]%'
-- Iniciem entre B e E

SELECT * FROM CUSTOMERS WHERE COMPANYNAME LIKE '[BMF]%'
-- Iniciem em B, M ou F

SELECT * FROM CUSTOMERS WHERE CONTACTTITLE LIKE '%ETI%'
-- Contenham ETI em qualquer lugar

SELECT * FROM CUSTOMERS WHERE CONTACTTITLE LIKE '%ETI%ASS%'
-- Contenham ETI e ASS

SELECT * FROM CUSTOMERS WHERE CONTACTNAME LIKE 'L[AI]%'
-- Iniciem em L e a segunda letra seja A ou I

Uso de ALIAS (AS)

ALIAS – AS: São apelidos para colunas

SELECT COMPANYNAME, REGION + '/' + COUNTRY AS LOCAL FROM CUSTOMERS

SELECT COMPANYNAME, LOCAL = REGION + '/' + COUNTRY FROM CUSTOMERS

Funções de Manipulação de Strings

• ASCII(string) = Pega o valor em ASCII da string.
• CHAR(integer) = Troca o inteiro da tabela ASCII em um caractere.
• LEN(string) = Identifica o comprimento de uma expressão em caracteres.
• LOWER(string) = Converte uma string de caixa alta (uppercase) para caixa baixa (lowercase).
• LTRIM(string) = Remove os espaços em branco à esquerda.
• REPLICATE(string, integer) = Repete N vezes um caractere especificado.
• REVERSE(string) = Retorna o inverso de uma expressão.
• RTRIM(string) = Remove os espaços em branco à direita de uma string.
• SPACE(integer) = Retorna o número de espaços em branco informados no parâmetro.
• SUBSTRING(string texto, posicao_inicial, tamanho) = Retorna uma string com o comprimento definido em "tamanho" extraída da string "texto", a partir da "posicao_inicial".
• UPPER(string) = Retorna a string em maiúsculas.

Exemplos de Filtros e Seleções Básicas

-- Seleciona o produto de código 20
SELECT * FROM Products WHERE ProductID = 20

-- Selecionar o produto de nome 'Chai'
SELECT * FROM Products WHERE ProductName = 'chai'

-- Selecionar todos os produtos com preço menor ou igual a 20
SELECT * FROM Products WHERE UnitPrice <= 20

-- Todos os preços de produtos que sejam maior ou igual a 20 e menor ou igual a 30
SELECT * FROM Products WHERE UnitPrice >= 20 AND UnitPrice <= 30

-- Todos os produtos que tenham a quantidade no estoque maior ou igual a 80 e menor ou igual a 100
SELECT * FROM Products WHERE UnitsInStock >= 80 AND UnitsInStock <= 100

-- Todos os empregados cujo primeiro nome comece com a letra 'M'
SELECT * FROM Employees WHERE FirstName LIKE 'm%'

-- Todos os empregados cujo primeiro nome termine com a letra 'T'
SELECT * FROM Employees WHERE FirstName LIKE '%t'

-- Todos os clientes cujo nome da companhia contenha a palavra 'gourmet'
SELECT * FROM Customers WHERE CompanyName LIKE '%gourmet%'

-- Todos os clientes cujo nome da companhia comece com as letras de "A" até "D"
SELECT * FROM Customers WHERE CompanyName LIKE '[a-d]%'

Seleção de Colunas Específicas e Operações

-- Escolhendo apenas algumas colunas:
SELECT ProductName, UnitPrice, UnitsInStock FROM Products

-- Escolhendo apenas algumas colunas e fazendo cálculo com as colunas:
SELECT *, UnitPrice * Quantity AS 'total' FROM [Order Details]

-- Correto mesmo... com o desconto ficaria...
-- Escolhendo apenas algumas colunas e fazendo cálculo com as colunas:
SELECT *, UnitPrice * Quantity AS 'total', UnitPrice * Quantity * (1 - Discount) AS 'total com desconto' FROM [Order Details]

Mais Exemplos com LIKE

-- Seleciona os clientes cujo nome começa com M:
SELECT * FROM Customers WHERE CompanyName LIKE 'm%'

-- Seleciona os clientes cujo nome do contato termina com 'ang':
SELECT * FROM Customers WHERE ContactName LIKE '%ang'

-- Seleciona os clientes que tenham Gourmet no nome:
SELECT * FROM Customers WHERE CompanyName LIKE '%gourmet%'

-- Seleciona os clientes que sejam Wang ou Wong:
SELECT * FROM Customers WHERE ContactName LIKE '%w_ng%'

-- Seleciona os clientes cujo nome começa de A até a letra D:
SELECT * FROM Customers WHERE CompanyName LIKE '[a-d]%'

-- Seleciona os clientes cujo nome começa com vogal:
SELECT * FROM Customers WHERE CompanyName LIKE '[aeiou]%'

-- Seleciona os clientes cujo nome começa com consoante:
SELECT * FROM Customers WHERE CompanyName NOT LIKE '[aeiou]%'

Funções de Agregação

Função COUNT

-- Contar a quantidade de produtos:
SELECT COUNT(*) FROM Products

-- Qual a quantidade de clientes do Brasil:
SELECT COUNT(*) FROM Customers WHERE Country = 'brazil'

-- Qual a quantidade de produtos do código da categoria 1:
SELECT COUNT(*) FROM Products WHERE CategoryID = 1

-- Qual a quantidade de pedidos com OrderDate do ano de 2016:
SELECT COUNT(*) FROM Orders WHERE OrderDate BETWEEN '01/01/2016' AND '31/12/2016'
SELECT COUNT(*) FROM Orders WHERE YEAR(OrderDate) = '2016'

-- Qual a quantidade de pedidos do cliente de código 'ANTON':
SELECT COUNT(*) FROM Orders WHERE CustomerID = 'anton'

-- Qual a quantidade de pedidos em que o ShippedDate é maior que o RequiredDate (quantos pedidos foram entregues fora do prazo):
SELECT COUNT(*) FROM Orders WHERE ShippedDate > RequiredDate

Função SUM

-- SUM >> Somar ou totalizar um campo

-- Ver todos os produtos da categoria 1:
SELECT * FROM Products WHERE CategoryID = 1

-- Agora quero a soma de toda a quantidade de estoque da categoria de código 1:
SELECT SUM(UnitsInStock) FROM Products WHERE CategoryID = 1

-- Quero o valor monetário das minhas bebidas:
SELECT SUM(UnitsInStock * UnitPrice) FROM Products WHERE CategoryID = 1

-- Quero o valor monetário total do estoque:
SELECT SUM(UnitsInStock * UnitPrice) FROM Products

Função AVG

-- Calcula a média do preço unitário dos produtos cadastrados:
SELECT AVG(UnitPrice) FROM Products

-- Ver o preço médio dos frutos do mar (código da categoria 8):
SELECT AVG(UnitPrice) FROM Products WHERE CategoryID = 8

Função MAX

-- Qual o produto mais caro no meu estoque:
SELECT MAX(UnitPrice) FROM Products

-- Qual o meu funcionário mais antigo:
SELECT MAX(YEAR(GETDATE()) - YEAR(HireDate)) FROM Employees

-- Na verdade, seria melhor fazer sem usar o MAX:
SELECT FirstName, HireDate, YEAR(GETDATE()) - YEAR(HireDate) AS 'tempo de casa' FROM Employees

-- Qual o meu funcionário com maior idade:
SELECT MAX(YEAR(GETDATE()) - YEAR(BirthDate)) FROM Employees

Função MIN

-- Qual o produto mais barato no meu estoque:
SELECT MIN(UnitPrice) FROM Products

-- Qual o meu funcionário mais novo:
SELECT MIN(YEAR(GETDATE()) - YEAR(HireDate)) FROM Employees

-- Na verdade, seria melhor fazer sem usar o MIN:
SELECT FirstName, HireDate, YEAR(GETDATE()) - YEAR(HireDate) AS 'tempo de casa' FROM Employees

-- Qual o meu funcionário com menor idade:
SELECT MIN(YEAR(GETDATE()) - YEAR(BirthDate)) FROM Employees

Agrupamento de Dados com GROUP BY

O GROUP BY serve para agrupar os valores. É necessário usar uma função de agregação e, caso mostre uma coluna de legenda, esta deve ser a mesma utilizada para o agrupamento.

-- Quantidade de tipos de produtos agrupando por código da categoria:
SELECT COUNT(*) FROM Products GROUP BY CategoryID

-- O problema no exemplo anterior é que está mostrando apenas os valores:
SELECT CategoryID, COUNT(*) FROM Products GROUP BY CategoryID

SELECT * FROM Categories AS c INNER JOIN Products AS p ON p.CategoryID = c.CategoryID

SELECT CategoryName, COUNT(*) FROM Categories AS c INNER JOIN Products AS p ON p.CategoryID = c.CategoryID GROUP BY CategoryName

-- Contar a quantidade de clientes por país:
SELECT Country, COUNT(*) FROM Customers GROUP BY Country

-- Contar a quantidade de pedidos de cada país:
SELECT Country, COUNT(*) AS 'total' FROM Customers AS c INNER JOIN Orders AS o ON c.CustomerID = o.CustomerID GROUP BY Country ORDER BY total DESC

-- Quero a quantidade de pedidos que cada funcionário vendeu:
SELECT * FROM Orders AS o INNER JOIN Employees AS e ON o.EmployeeID = e.EmployeeID
SELECT FirstName, Title, COUNT(*) AS 'total' FROM Orders AS o INNER JOIN Employees AS e ON o.EmployeeID = e.EmployeeID GROUP BY FirstName, Title ORDER BY total DESC

-- Contar a quantidade de produtos de cada fornecedor:
SELECT CompanyName, COUNT(*) FROM Products AS p INNER JOIN Suppliers AS s ON p.SupplierID = s.SupplierID GROUP BY CompanyName

-- Somando a quantidade de produtos no estoque por categoria:
SELECT CategoryName, SUM(UnitsInStock) FROM Categories AS c INNER JOIN Products AS p ON p.CategoryID = c.CategoryID GROUP BY CategoryName

/* A média dos preços dos produtos por categoria */
SELECT CategoryName, AVG(UnitPrice) AS 'media' FROM Categories AS c INNER JOIN Products AS p ON p.CategoryID = c.CategoryID GROUP BY CategoryName

/* A média dos preços dos produtos por categoria apenas dos produtos com CategoryID maior que 5 */
SELECT CategoryName, AVG(UnitPrice) AS 'media' FROM Categories AS c INNER JOIN Products AS p ON p.CategoryID = c.CategoryID WHERE c.CategoryID > 5 GROUP BY CategoryName

/* A média dos preços dos produtos por categoria filtrando média maior ou igual a 30 */
SELECT CategoryName, AVG(UnitPrice) AS 'media' FROM Categories AS c INNER JOIN Products AS p ON p.CategoryID = c.CategoryID GROUP BY CategoryName HAVING AVG(UnitPrice) >= 30
SELECT AVG(UnitPrice) AS 'media' FROM Categories AS c INNER JOIN Products AS p ON p.CategoryID = c.CategoryID WHERE AVG(UnitPrice) >= 30 GROUP BY CategoryName

-- Quantidade de pedidos por funcionários (usando todos os pedidos):
SELECT FirstName, COUNT(*) AS 'total' FROM Employees AS e INNER JOIN Orders AS o ON e.EmployeeID = o.EmployeeID GROUP BY FirstName

-- Quantidade de pedidos por funcionários (usando os pedidos de 2016):
SELECT FirstName, COUNT(*) AS 'total' FROM Employees AS e INNER JOIN Orders AS o ON e.EmployeeID = o.EmployeeID WHERE OrderDate BETWEEN '01/01/2016' AND '31/12/2016' GROUP BY FirstName

-- Quantidade de pedidos por funcionários (usando os pedidos de 2016) com quantidade de pedidos maior ou igual a 50:
SELECT FirstName, COUNT(*) AS 'total' FROM Employees AS e INNER JOIN Orders AS o ON e.EmployeeID = o.EmployeeID WHERE OrderDate BETWEEN '01/01/2016' AND '31/12/2016' GROUP BY FirstName HAVING COUNT(*) >= 50

Funções de Data e Hora

-- Pega a data e hora corrente do sistema:
SELECT GETDATE()

-- Retorna o dia:
SELECT DAY('21/05/1972')
SELECT DAY(GETDATE())

-- Retorna o mês:
SELECT MONTH('21/05/1972')
SELECT MONTH(GETDATE())

-- Retorna o ano:
SELECT YEAR('21/05/1972')
SELECT YEAR(GETDATE())

-- Retorna se uma data é válida (verdadeira):
SELECT ISDATE('21/05/1972')
SELECT ISDATE(GETDATE())
SELECT ISDATE('29/02/2017')

Intervalos de Data (Datepart)

• dd - Dia
• mm - Mês
• yy - Ano
• hh - Hora
• mi - Minuto
• ss - Segundo
• ms - Milissegundo
• ww - Número da semana
• dw - Número do dia da semana
• dy - Número do dia do ano
• qq - Trimestre do ano

SELECT DATEPART(d, GETDATE())

SELECT DATEPART(ww, GETDATE())

SELECT DATEPART(dw, GETDATE())

SELECT DATEPART(dy, GETDATE())

SELECT DATEPART(dw, '12/07/1996')

SELECT FirstName, DATEPART(dw, BirthDate) FROM Employees

SELECT DATEPART(qq, GETDATE())

SELECT DATEPART(qq, '21/05/1972')

SELECT DATEPART(qq, '22/11/2017')

Função DATENAME

SELECT DATENAME(d, GETDATE())

SELECT DATENAME(ww, GETDATE())

SELECT DATENAME(dw, GETDATE())

SELECT DATENAME(dy, GETDATE())

SELECT DATENAME(qq, GETDATE())

SELECT DATENAME(qq, '21/05/1972')

SELECT DATENAME(qq, '12/07/2017')

SELECT DATENAME(dw, '12/07/2017')

SELECT DATENAME(mm, '12/07/2017')

Função DATEDIFF

SELECT DATEDIFF(dd, '08/03/2017', '12/07/2017')

SELECT DATEDIFF(dd, GETDATE(), '12/07/2017')

SELECT DATEDIFF(yy, '12/07/1996', GETDATE())

SELECT OrderID, OrderDate, RequiredDate, ShippedDate, DATEDIFF(dd, OrderDate, RequiredDate) AS 'prazo', DATEDIFF(dd, ShippedDate, RequiredDate) AS 'entrega' FROM Orders

Funções Matemáticas

-- Retorna o número sem o sinal:
SELECT ABS(5)
SELECT ABS(-5)

-- Retorna o arco-cosseno de um número:
SELECT ACOS(0.5)

-- Retorna o sinal de um número (sendo: -1 para negativo, 1 para positivo e 0 para zero):
SELECT SIGN(5)
SELECT SIGN(0)
SELECT SIGN(-5)

-- Retorna o valor de PI:
SELECT PI()

-- Calcular a área de um círculo de raio 5 cm:
SELECT PI() * 5 * 5

-- Calcula a raiz quadrada:
SELECT SQRT(400)

-- Retorna o quadrado de um número:
SELECT SQUARE(8)
SELECT PI() * SQUARE(5)

-- Retorna um número elevado a outro:
SELECT POWER(2, 10)

-- Retorna valor aleatório:
SELECT RAND()
SELECT FLOOR(RAND() * 6 + 1)

Arredondamentos

-- FLOOR: Arredonda para baixo retornando número inteiro:
SELECT FLOOR(2.2)
SELECT FLOOR(2.456)
SELECT FLOOR(2.00001)
SELECT FLOOR(2.999999999)

-- CEILING: Arredonda para cima retornando número inteiro:
SELECT CEILING(2.2)
SELECT CEILING(2.456)
SELECT CEILING(2.00001)
SELECT CEILING(2.999999999)

-- ROUND: Arredonda seguindo as regras matemáticas e pode-se escolher a quantidade de casas decimais:
SELECT ROUND(2.823645, 5)
SELECT ROUND(2.823645, 1)
SELECT ROUND(2.823645, 3)
SELECT ROUND(2.8236555, 4, 1)
SELECT ROUND(2.823645, 0, 5)

SELECT UnitPrice, UnitPrice * 1.05 AS 'Aumenta 5%', ROUND(UnitPrice * 1.05, 2) AS 'Aumenta 5% e arred', SQRT(UnitPrice) AS 'raiz' FROM Products

Mais Funções de String

-- Retorna quantidade de caracteres:
SELECT LEN('Ivan Carlos Pavao')

-- Retorna o código do caractere na tabela ASCII:
SELECT ASCII('a')
SELECT ASCII('A')
SELECT ASCII('3')

-- Retorna o caractere da tabela ASCII a partir de um número:
SELECT CHAR(97)
SELECT CHAR(65)
SELECT CHAR(51)

-- Retira espaços em branco da esquerda:
SELECT LTRIM(' Ivan ') + 'carlos'

-- Retira espaços em branco da direita:
SELECT RTRIM(' Ivan ') + 'carlos'
SELECT LTRIM(RTRIM(' Ivan ')) + 'carlos'

-- Transforma texto em minúsculas:
SELECT LOWER('Ivan Carlos Pavao')

-- Transforma texto em maiúsculas:
SELECT UPPER('Ivan Carlos Pavao')

-- Acrescenta espaços:
SELECT 'Ivan' + SPACE(15) + 'carlos'

-- Replica o texto a quantidade de vezes indicada:
SELECT REPLICATE('Ivan ', 8)

-- Recorta da esquerda a quantidade de caracteres indicada de um texto:
SELECT LEFT('computador', 3)

-- Recorta da direita a quantidade de caracteres indicada de um texto:
SELECT RIGHT('computador', 3)

-- Recorta uma frase determinando o ponto inicial do corte e a quantidade a ser cortada de um texto:
SELECT SUBSTRING('computador', 4, 4)
SELECT 'estou ' + LEFT('computador', 3) + ' uma ' + SUBSTRING('computador', 4, 4) + SPACE(1) + RIGHT('computador', 3) + ' de cabeça '

-- Substitui um texto por outro:
SELECT REPLACE('banana', 'n', 't')
SELECT REPLACE('O garotão bonito', 'bonito', 'boiola')

-- Retorna o grau de diferença entre as palavras:
SELECT DIFFERENCE('abacate', 'abacate')
SELECT DIFFERENCE('abacate', 'abaca')
SELECT DIFFERENCE('abacate', 'abacaxi')
SELECT DIFFERENCE('abacate', 'obatoilado')

-- Inverte o texto:
SELECT REVERSE('ivan carlos pavao')

Funções de Data e Hora Adicionais

-- Retorna a data e hora do sistema:
SELECT GETDATE()
SELECT DAY(GETDATE())
SELECT MONTH(GETDATE())
SELECT YEAR(GETDATE())

Tabela de Intervalos

• dd - Dia
• mm - Mês
• yy - Ano
• hh - Hora
• mi - Minuto
• ss - Segundo
• ms - Milissegundos
• ns - Nanossegundos
• ww - Número da semana
• dw - Dia da semana
• dy - Dia do ano
• qq - Trimestre

SELECT DATEPART(ww, GETDATE())
SELECT DATEPART(dw, GETDATE())
SELECT DATEPART(qq, '10/01/2014')
SELECT DATEPART(dy, GETDATE())
SELECT DATENAME(dw, GETDATE())
SELECT DATENAME(mm, GETDATE())

Cálculos com DATEADD e Formatação

-- DATEADD (intervalo, quantidade, data):
SELECT DATEADD(dd, 40, GETDATE())
SELECT DATEADD(dd, 80, GETDATE())
SELECT DATEADD(dd, 120, GETDATE())
SELECT DATEADD(mm, 9, GETDATE())
SELECT DATEADD(yy, 5, GETDATE())

-- Altera o padrão de data do sistema para dia, mês e ano:
SET DATEFORMAT DMY

SELECT DATEDIFF(dd, GETDATE(), '21/12/2014')
SELECT DATEDIFF(ss, '21/05/1972', '18/09/2014')

SELECT * FROM Orders

SELECT OrderID, CustomerID, OrderDate, ShippedDate, DATEDIFF(dd, OrderDate, ShippedDate) AS 'TEMPO DE ENTREGA' FROM Orders ORDER BY [TEMPO DE ENTREGA] DESC