Guia Completo de Comandos Transact-SQL e SQL Server

GROUP BY (Transact-SQL)

Agrupa um conjunto de linhas selecionadas em um conjunto de linhas de resumo pelos valores de uma ou mais colunas ou expressões no SQL Server.

SELECT Tipo, SUM(Quantidade) AS 'Quantidade em Estoque' FROM Produtos GROUP BY Tipo

Exemplo com Inner Join

-- Selecione os produtos para exibir a quantidade de tipos de produtos agrupando por código da categoria

select Categoryname,COUNT(*) 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 codcategoria 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 */

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

-- 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

Cláusula HAVING com GROUP BY

A cláusula HAVING determina uma condição de busca para um grupo ou um conjunto de registros, definindo critérios para limitar os resultados obtidos a partir do agrupamento de registros. É importante lembrar que essa cláusula só pode ser usada em parceria com GROUP BY.

SELECT Tipo, SUM(Quantidade) AS 'Quantidade em Estoque' FROM Produtos GROUP BY Tipo HAVING SUM(Quantidade) > 200

Obs: O HAVING é diferente do WHERE. O WHERE restringe os resultados obtidos sempre após o uso da cláusula FROM, ao passo que a cláusula HAVING filtra o retorno do agrupamento.

CREATE PROCEDURE (Transact-SQL)

Cria um procedimento armazenado Transact-SQL ou CLR (Common Language Runtime) no SQL Server.

/* Crie uma procedure para consultar os clientes por cidade passando como parâmetro a cidade */

create procedure ClientePorCidade @cidade nvarchar(30) as

begin

select * from Customers where city = @cidade

end

go

exec ClientePorCidade'london'

exec ClientePorCidade'campinas'

go

/* Crie uma procedure para consultar os produtos por categoria passando como parâmetro o código da categoria */

go

create procedure [Produto Por Categoria2] @codcat int as

select * from products where CategoryID = @codcat

go

exec [Produto Por Categoria2]3

go

/* Crie uma procedure para consultar os pedidos entre 2 datas passando 2 parâmetros para a data do pedido */

go

create procedure [pedido por faixa de data]

@dataini date ,@datafim date as

select * from orders

where OrderDate between @dataini and @datafim

go

exec [pedido por faixa de data]'01/03/2016','31/03/2016'

/* Criar stored procedure para inserir um registro nas categorias */

create procedure Inserecategorias

@nomecat nvarchar(30),@descricao nvarchar(50) as

insert into Categories(CategoryName,Description)

values ( @nomecat,@descricao)

exec Inserecategorias'limpeza','produtos para limpeza em geral'

select * from Customers

/* Crie uma stored procedure para inserir um cliente */

create procedure InsereCliente

@customerid as varchar(5),

@companyName as varchar(30),

@contactName as varchar(30),

@contactTitle as varchar(30),

@Address as varchar(30),

@city as varchar(30),

@postalcode as varchar(30),

@country as varchar(30),

@phone as varchar(30),

@fax as varchar(30) as

insert into customers( customerid, companyName ,contactName,

contactTitle,Address,city,postalcode,

country,phone,fax)

values ( @customerid, @companyName ,@contactName,

@contactTitle,@Address,@city,@postalcode,

@country,@phone,@fax)

exec inserecliente'ICPAVAO','Ivan pavão','Ivan','mr','rua princesa','sbc','00000',

'brazil','2222-2222','3333-3333'

EXECUTE (Transact-SQL)

Executa um procedimento armazenado de sistema ou procedimento armazenado definido pelo usuário.

DECLARE @local_variable (Transact-SQL)

As variáveis são declaradas no corpo de um lote.

SET @local_variable (Transact-SQL)

Define valores para a variável local especificada.

Exemplo: SET @myvar = 'This is a test';

SELECT @local_variable (Transact-SQL)

Especifica que a variável local especificada criada através de DECLARE @local_variable deve ser definida para a expressão especificada.

Exemplo: SELECT @var1 = 'Generic Name'

SELECT (Transact-SQL)

Recupera linhas do banco de dados e permite a seleção de uma ou várias linhas ou colunas de uma ou várias tabelas no SQL Server 2012.

DELETE (Transact-SQL)

Remove uma ou mais linhas de uma tabela ou exibição no SQL Server 2012.

DELETE com WHERE

DELETE FROM Production.ProductCostHistory

WHERE StandardCost > 1000.00;

INSERT (Transact-SQL)

Insere linhas na tabela

INSERT INTO Production.UnitMeasure

CREATE FUNCTION (Transact-SQL)

-- Calcular a média da Fatec: Media = p1*0.35 + p2*0.45 + t*0.2

Create function mediafatec(@p1 float, @p2 float , @t float )

returns float as

begin

declare @media float

set @media=@p1*0.35 + @p2*0.45 + @t*0.2

return @media

end

go

-- Teste da função

select dbo.mediafatec(4,7,10)

CREATE TRIGGER (Transact-SQL)

Trigger é um gatilho que é executado automaticamente quando um evento ocorre no servidor de banco de dados.

create table tabelaA(

Codcliente int primary key,

Hospede nvarchar(20),

Quarto nvarchar(20)

create table tabelaB(

Codcliente int primary key,

Hospede nvarchar(20),

Quarto nvarchar(20))

CREATE TABLE CAIXA

(

DATA_CAIXA DATETIME,

VALOR_INICIAL DECIMAL(12,2),

VALOR_FINAL DECIMAL(12,2)

GO

INSERT INTO CAIXA

VALUES (CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),103)),

100, 100)

GO

CREATE TABLE VENDAS

(

DATA_DA_VENDA DATETIME,

CODVENDA INT,

VALOR_VENDA DECIMAL(12,2)

GO

select * from caixa

Crie uma trigger que ao inserir uma nova venda o valor desta venda seja automaticamente atualizado no campo valor caixa da tabela de caixas.

CREATE TRIGGER TGR_VENDAS_AI

ON VENDAS

FOR INSERT

AS

BEGIN

DECLARE

@VALOR DECIMAL(10,2),

@DATA DATETIME

SELECT @DATA = DATA_DA_VENDA, @VALOR = VALOR_VENDA

FROM INSERTED

UPDATE CAIXA SET Valor_FINAL = valor_FINAL + @VALOR

WHERE DATA_caixa = @DATA

END

GO

INSERT INTO VENDAS

VALUES (CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 103)), 1, 10)

-- Teste da trigger que insere

insert into VENDAS(DATA_DA_VENDA,CODVENDA,VALOR_VENDA)

values ('24/05/2017',1,20)

insert into VENDAS(DATA_DA_VENDA,CODVENDA,VALOR_VENDA)

values ('24/05/2017',2,200)

insert into VENDAS(DATA_DA_VENDA,CODVENDA,VALOR_VENDA)

values ('24/05/2017',3,300)

select * from caixa