T-SQL

-- CURSO SQL SERVER BÁSICO

CREATE DATABASE db_Biblioteca ON PRIMARY (
    NAME=db_Biblioteca,
    FILENAME='C:\SQL\db_Biblioteca.MDF',
    SIZE=16MB,
    MAXSIZE=10MB,
    FILEGROWTH=10%
)

USE db_Biblioteca;

sp_helpdb db_Biblioteca; -- VERIFICAR A ESTRUTURA DA TABELA

CREATE TABLE tbl_Livro (
    ID_Livro SMALLINT PRIMARY KEY IDENTITY(100,1),
    Nome_Livro VARCHAR(50) NOT NULL,
    ISBN VARCHAR(30) NOT NULL UNIQUE,
    ID_Autor SMALLINT NOT NULL,
    Data_Pub DATETIME NOT NULL,
    Preco_Livro MONEY NOT NULL
)

CREATE TABLE tbl_Autores (
    ID_Autor SMALLINT PRIMARY KEY,
    Nome_Autor VARCHAR(50),
    SobreNome_Autor VARCHAR(60)
)

CREATE TABLE tbl_Editoras (
    ID_Editora SMALLINT PRIMARY KEY IDENTITY,
    Nome_Editora VARCHAR(50) NOT NULL
)

sp_help tbl_Livro; -- INFORMAÇÕES SOBRE A TABELA EXISTENTE


-- TESTANDO IDENTITY

CREATE TABLE tbl_teste_identidade (
    ID_Teste SMALLINT PRIMARY KEY IDENTITY(100,2), -- COMEÇANDO NO 100 E INCREMENTANDO DE 2 EM 2
    Valor SMALLINT NOT NULL
)

INSERT INTO tbl_teste_identidade (Valor) VALUES (10)
INSERT INTO tbl_teste_identidade (Valor) VALUES (20)
INSERT INTO tbl_teste_identidade (Valor) VALUES (30)
INSERT INTO tbl_teste_identidade (Valor) VALUES (40)

SELECT * FROM tbl_teste_identidade

-- ALTER TABLE - DROP

ALTER TABLE tbl_Livro DROP COLUMN ID_Autor -- EXCLUINDO COLUNA

ALTER TABLE tbl_Livro DROP CONSTRAINT nome_constraint --EXCLUINDO CONSTRAINT DE UMA TABELA

-- ALTER TABLE - ADD

ALTER TABLE tbl_Livro ADD
ID_Autor SMALLINT NOT NULL
CONSTRAINT fk_ID_Autor FOREIGN KEY (ID_Autor) REFERENCES tbl_Autores

ALTER TABLE tbl_Livro ADD
ID_Editora SMALLINT NOT NULL
CONSTRAINT fk_ID_Editora FOREIGN KEY (ID_Editora) REFERENCES tbl_Editoras

-- ALTER TABLE - ALTER COLUMN

ALTER TABLE tbl_Livro ALTER COLUMN ID_Autor SMALLINT -- ALTERANDO TIPO DE DADOS

-- ALTER TABLE - ADD PRIMARY KEY

ALTER TABLE Clientes ADD PRIMARY KEY (ID_Cliente)

-- DROP TABLE
USE db_Biblioteca -- SEMPRE USAR
DROP TABLE tbl_teste_identidade;
SELECT * FROM tbl_Editoras;
-- INSERT INTO
INSERT INTO tbl_Autores (ID_Autor, Nome_Autor, SobreNome_Autor)
VALUES
(1, 'Daniel', 'Barret'),
(2, 'Gerald', 'Carter'),
(3, 'Mark', 'Sobell'),
(4, 'William', 'Stanek'),
(5, 'Richard', 'Blum');

INSERT INTO tbl_Editoras (Nome_Editora)
VALUES
('Prentice Hall'),
('O´Reilly'),
('Microsoft Press'),
('Wiley');

INSERT INTO tbl_Livro (Nome_Livro, ISBN, Data_Pub, Preco_Livro, ID_Autor, ID_Editora)
VALUES
('Linux Command Line and Shell Scripting','143856969','20091221', 68.35, 5, 4),
('SSH, the Secure Shell','127658789','20091221', 58.30, 1, 2),
('Using Samba','123856789','20001221', 61.45, 2, 2),
('Fedora and Red Hat Linux','123346789', '20101101', 62.24, 3, 1),
('Windows Server 2012 Inside Out','123356789','20040517', 66.80, 4, 3),
('Microsoft Exchange Server 2010','123366789','20001221', 45.30, 4, 3);

-- TRUNCATE TABLE: EXCLUIR TODOS OS DADOS DE UMA TABELA

SELECT COUNT(*) FROM tbl_Livro
GO
TRUNCATE TABLE tbl_Livro
GO
SELECT COUNT(*) FROM tbl_Livro

-- ORDER BY

SELECT * FROM tbl_livro
ORDER BY Nome_Livro ASC; -- ACRESCENTE

SELECT Nome_Livro, Preco_Livro FROM tbl_livro
ORDER BY Preco_Livro DESC; -- DECRESCENTE

SELECT Nome_Livro, ID_Editora FROM tbl_livro
ORDER BY ID_Editora; --


-- SELECT DISTINCT

SELECT DISTINCT ID_Autor FROM tbl_Livro

-- CLAUSULA WHERE

SELECT ID_Autor FROM tbl_Autores
WHERE SobreNome_Autor = 'Stanek';

/* OPERADORES AND e OR

AND = SE TODAS RETORNAREM VERDADEIRO
OR = SE PELO MENOS UMA RETORNAR VERDADEIRO (RETORNA FALSE SE TODAS FOREM FALSAS)

*/

SELECT * FROM tbl_Livro
WHERE ID_Livro > 2 AND ID_Autor < 3

SELECT * FROM tbl_Livro
WHERE ID_Livro > 2 OR ID_Autor < 3


-- SELECT TOP

SELECT TOP 10 PERCENT Nome_Livro
FROM tbl_Livro

SELECT TOP 3 Nome_Livro
FROM tbl_Livro

-- APELIDOS EM TABELAS 'AS'

SELECT Nome_Livro AS Livro
FROM tbl_Livro;

/* OPERADOR UNION
1. DEVE TER A MESMA QUANTIDADE DE COLUNAS ENTRE AS TABELAS USADAS UNION
2. OS MESMOS TIPOS DE DADOS
3. ORDENS IGUAIS

UNION ALL = MOSTRA TODOS
UNION = MOSTRA SEM REPETIÇÕES
*/

SELECT Nome_Autor FROM tbl_Autores
UNION
SELECT Nome_Livro FROM tbl_Livro

-- UPDATE

UPDATE tbl_livro
SET Preco_Livro = 71.20,
ISBN = '654738322'
WHERE ID_Livro = 103;


-- SELECT INTO – Criar nova tabela a partir de uma tabela existente – SQL Server

SELECT * INTO Testando
FROM tbl_Livro

SELECT Nome_Livro, ID_Autor INTO LivroAutor
FROM tbl_Livro
WHERE ID_Livro > 2


-- Funções Agregadas – SUM, COUNT, MAX, MIN, AVG – SQL Server

SELECT COUNT(*) AS QTD FROM tbl_Autores

SELECT MIN(Preco_Livro) AS 'PREÇO Minimo' FROM tbl_Livro

SELECT MAX(Preco_Livro) FROM tbl_Livro

SELECT AVG(Preco_Livro) FROM tbl_Livro
FROM tbl_Livro
WHERE Data_Pub
SELECT SUM(Preco_Livro) FROM tbl_Livro


-- BETWEEN – Seleção de Intervalos em Registros – SQL Server

SELECT Nome_Livro BETWEEN '20091221' AND '20101101'

-- LIKE e NOT LIKE – Filtrar por padrões específicos – Curso de SQL Server

SELECT * FROM tbl_livro
WHERE Nome_Livro LIKE 'F%';

SELECT * FROM tbl_livro
WHERE Nome_Livro NOT LIKE 'S%';

SELECT * FROM tbl_livro
WHERE Nome_Livro LIKE '[a-z]%';

SELECT * FROM tbl_livro
WHERE Nome_Livro LIKE '%[GL]';

SELECT * FROM tbl_livro
WHERE Nome_Livro LIKE '_i__o%';

SELECT Nome_Livro
FROM tbl_livro
WHERE Nome_Livro LIKE '_i%';

SELECT Nome_Livro AS Livro, Preco_Livro AS Valor
FROM tbl_livro
WHERE Nome_Livro NOT LIKE '_i%'
AND Preco_Livro > 62.00;

-- JOINS e INNER JOIN – Selecionar dados de duas ou mais tabelas

SELECT * FROM tbl_livro
INNER JOIN tbl_autores
ON tbl_livro.ID_Autor = tbl_autores.ID_Autor;

SELECT tbl_livro.Nome_Livro, tbl_livro.ISBN, tbl_autores.Nome_Autor
FROM tbl_livro
INNER JOIN tbl_autores
ON tbl_livro.ID_Autor = tbl_autores.ID_Autor;

SELECT L.Nome_Livro AS Livros, E.Nome_editora AS Editoras
FROM tbl_livro AS L
INNER JOIN tbl_editoras AS E
ON L.ID_editora = E.ID_editora
WHERE E.Nome_Editora LIKE 'M%';

-- OUTER JOINS – LEFT e RIGHT – Selecionar dados de várias tabelas – SQL Server
/*
1º TABELA DO SELECT É SEMRPE A ESQUERDA, A SEGUNDA A DIREITA.
*/

SELECT * FROM tbl_Livro as L
LEFT JOIN tbl_Autores AS A
ON A.ID_Autor = L.ID_Autor
WHERE A.ID_Autor IS NULL -- TABELA DA DIREITA, CAMPO CHAVE IS NULL

SELECT * FROM tbl_Autores A
RIGHT JOIN tbl_Livro L
ON A.ID_Autor = L.ID_Autor
WHERE A.ID_Autor IS NULL;

-- FULL OUTER JOIN – Selecionar dados de várias tabelas – SQL Server

SELECT L.Nome_Livro, L.ID_Autor, A.Nome_Autor
FROM tbl_Livro AS L
FULL JOIN tbl_Autores AS A
ON L.ID_Autor = A.ID_Autor
WHERE L.ID_Autor IS NULL OR
A.ID_Autor IS NULL

--  Operadores IN e NOT IN – Filtros de Múltiplas Condições – SQL Server

SELECT * FROM tbl_Livro
WHERE ID_Autor IN (1,2)

SELECT * FROM tbl_Livro
WHERE ID_Autor NOT IN (1,2)


-- CRIANDO CAMPOS CALCULADOS

CREATE TABLE Produtos(
    codProduto SMALLINT,
    NomeProduto VARCHAR(20),
    Preco MONEY,
    Quant SMALLINT,
    Total AS (Preco*Quant)
)

SELECT * FROM Produtos

INSERT INTO Produtos (codProduto, NomeProduto, Preco, Quant)
VALUES
(1, 'Mouse', 15.00, 2),
(2, 'Teclado', 18.00, 1),
(3, 'Fones', 25.00, 3),
(4, 'Pendrive', 25.00, 3),
(5, 'SD Card', 29.00, 2),
(6, 'DVD-R', 1.30, 12)

SELECT * FROM Produtos

-- Índices – CREATE INDEX – SQL Server
-- APENAS PARA TABELAS QUE RECEBAM MUITAS CONSULTAS;
-- TABELAS INDEXADAS LAVAM MAIS TEMPO PARA SEREM ATUALIZADAS;

CREATE INDEX idx_nome_livro
ON tbl_Livro(Nome_Livro)

SELECT * FROM idx_nome_livro

-- Criando Regras com CREATE RULE

SELECT * FROM tbl_Livro

CREATE RULE rl_preco AS @VALOR > 10.00 AND

EXEC SP_BINDRULE rl_preco, 'tbl_Livro.Preco_Livro'

UPDATE tbl_Livro
SET Preco_Livro = 10.00
WHERE ID_Livro = 101

-- BACKUP DO BANCO DE DADOS E RESTAURAÇÃO

use db_Biblioteca;
BACKUP DATABASE db_Biblioteca
TO DISK = 'C:\SQL\Backup.bak'
--WITH FORMAT;
GO

USE db_Biblioteca;

-- Concatenação de Strings
-- OPERADOR DE CONCATENAÇÃO +

SELECT * FROM tbl_Livro

SELECT 'Lucas '+'Felicio'
SELECT Nome_Autor + ' ' + SobreNome_Autor FROM tbl_Autores

-- Collation – Agrupamento de Caracteres ou Colação

SELECT * FROM fn_helpcollations() -- PARA VER AS OPÇÕES DE AGRUPAMENTOS DISPONÍVEIS

-- PARA VER O ESQUEMA DE COLAÇÃO USADO ATUALMENTE PELO SERVIDOR

SELECT SERVERPROPERTY('Collation') AS 'Colação Usada'

-- ALTERAR ESQUEMA DE COLAÇÃO DE UM BANCO DE DADOS

SELECT DATABASEPROPERTYEX('TESTANDO', 'Collation')

ALTER DATABASE TESTANDO
COLLATE Greek_CI_AI

-- ALTERANDO COLAÇÃO EM APENAS UMA COLUNA

SELECT * FROM tbl_Livro
ORDER BY Nome_Livro
COLLATE Icelandic_CI_AI

-- WITH TIES no SQL Server
-- QUERY ABAIXO EXIBE TOP 3 PONTUAÇÕES MESMO QUE O QUARTO TENHA VALOR IGUAL AO 3
-- VERIFICA SE O ULTIMO VALOR TEM O MESMO VALOR LISTADO PELO ULTIMO DO TOP E EXIBE
-- SEMPRE UTILIZADA COM ORDER BY

SELECT TOP(3) WITH TIES Nome_Time, Pontos
FROM tbl_Autores
ORDER BY Pontos DESC

-- VIEWS (Exibições) – Criar, Alterar e Excluir
--CREATE OU ALTER PARA ALTERAR
CREATE VIEW vw_LivrosAutores
AS
SELECT L.Nome_Livro AS Livro, A.Nome_Autor + ' ' + A.SobreNome_Autor AS Autor, Preco_Livro AS Preco
FROM tbl_Livro AS L
INNER JOIN tbl_Autores AS A
ON L.ID_Autor = A.ID_Autor

SELECT *
FROM vw_LivrosAutores
WHERE LIVRO LIKE 'S%'

DROP VIEW vw_LivrosAutores

-- Subconsultas (subqueries) com Tabelas Derivadas

SELECT Resultado.Nome_Autor AS Autor, COUNT(Resultado.Nome_Livro) AS 'Qtd Livros'
FROM
(SELECT Nome_Autor, Nome_Livro, Preco_Livro
FROM tbl_Livro AS L
INNER JOIN tbl_Autores AS A
ON L.ID_Autor = A.ID_Autor) AS Resultado
GROUP BY Resultado.Nome_Autor
ORDER BY Resultado.Nome_Autor

-- CTE – Common Table Expression

WITH ConsultaCTE (Autor, Livros)
AS
(SELECT Nome_Autor AS Autor, Nome_Livro AS Livros
FROM tbl_Livro AS L
INNER JOIN tbl_Autores AS A
ON L.ID_Autor = A.ID_Autor)
SELECT Autor, COUNT(Livros) AS 'Qtd Livros'
FROM ConsultaCTE
GROUP BY Autor
ORDER BY Autor

-- Variáveis – Declaração e atribuição de valores

DECLARE @valor INT,
        @texto VARCHAR(40),
        @data_nasc DATE,
        @nada MONEY
SET @valor = 50
SET @texto = 'Lucas'
SET @data_nasc = GETDATE()
SELECT @valor AS Valor, @texto AS Texto,
@data_nasc AS 'Data de Nascimento',
@nada AS Salário

-- ATRIBUINDO VALORES A VARIAVEL UTILIZANDO SELECT

DECLARE @livro VARCHAR(30)
SELECT @Livro = Nome_Livro
FROM tbl_Livro
WHERE ID_Livro = 101

SELECT @Livro AS 'Nome do Livro'

-- UTILIZANDO EXEMPLO DE VARIAVEL COM CALCULO

DECLARE @preco MONEY, @Quantidade INT, @Nome VARCHAR(30)
SET @Quantidade = 5

SELECT @preco = Preco_Livro, @Nome = Nome_Livro
FROM tbl_Livro
WHERE ID_Livro = 101

SELECT @nome AS 'Nome do Livro',
@Preco * @Quantidade AS 'Preço dos Livros'

-- Conversão de Tipos de Dados no SQL Server com Cast e Convert

SELECT 'O Preço do livro '+Nome_Livro + 'é de R$' +
CAST(Preco_Livro AS VARCHAR(6)) AS Item
FROM tbl_Livro
WHERE ID_Autor = 2

-- UTILIZANDO FUNÇÃO CONVERT

SELECT 'O Preço do livro '+Nome_Livro + 'é de R$ ' +
CONVERT(VARCHAR(6), Preco_Livro)
FROM tbl_Livro
WHERE ID_Autor = 2

SELECT 'A Data de publicação é: '+
CONVERT(VARCHAR(15), Data_Pub, 103)
FROM tbl_Livro
WHERE ID_Livro = 102

-- Condicional IF / ELSE no SQL Server

DECLARE @numero INT,
        @texto VARCHAR(10)

SET @numero = 20
SET @texto = 'Fabio'

IF @numero = 20
    SELECT 'Número correto'
IF @texto = 'Lucas'
    BEGIN
        SET @numero = 30
        SELECT @numero
    END;
ELSE
    BEGIN
        SET @numero = 40
        SELECT 'Número incorreto'
    END;

-- EXEMPLO 2

DECLARE @NOME VARCHAR (30),
        @MEDIA REAL,
        @RESULTADO VARCHAR(10)
SELECT
    @NOME = Nome_Aluno,
    @MEDIA = (tbl_alunos.nota1, tbl_alunos.nota2, tbl_alunos.nota3, tbl_alunos.nota4) / 4
FROM tbl_alunos
WHERE nome_aluno = 'Fabio'
BEGIN
    IF @MEDIA >= 7
        SELECT @RESULTADO = 'Aluno Aprovado';
    END;
    ELSE
    BEGIN
        SELECT @RESULTADO = 'Aluno Reprovado';
    END;

SELECT 'O Aluno '+@Nome + ' está ' + @RESULTADO +
       ' com média ' CAST(@MEDIA AS VARCHAR);

-- Loop While no SQL Server – Estrutura de Repetição

DECLARE @VALOR INT
SET @VALOR = 0

WHILE @VALOR < 10
    BEGIN
        PRINT 'Número: ' + CAST(@VALOR AS VARCHAR(2))
        SET @VALOR = @VALOR +1
    END;

-- EXEMPLO COM BANCO DE DADOS db_Biblioteca

DECLARE @CODIGO INT
SET @CODIGO = 100

    WHILE @CODIGO < 106
        BEGIN
            SELECT ID_Livro AS ID, Nome_Livro AS LIVRO, Preco_Livro AS PRECO
            FROM tbl_Livro
            WHERE ID_Livro = @CODIGO
            SET @CODIGO = @CODIGO +1
        END;

-- Como Criar e Executar Stored Procedures no SQL Server – parte 01

CREATE PROCEDURE teste
AS
SELECT 'Lucas Moura' AS Nome

EXEC teste

--

CREATE PROCEDURE p_LivroValor
AS
SELECT Nome_Livro, Preco_Livro
FROM tbl_Livro

EXEC p_LivroValor

-- VISUALIZAR CONTEUDO DE UMA PROCEDURE

sp_helptext p_LivroValor

--CRIAR UMA STORED PROCEDURE CRIPTOGRAFADO

CREATE PROCEDURE p_LivroISBN
WITH ENCRYPTION
AS
SELECT Nome_Livro, ISBN
FROM tbl_Livro

sp_helptext p_LivroISBN

-- Parâmetros de Entrada em Stored Procedures no SQL Server

ALTER PROCEDURE teste (@PAR1 AS INT)
AS
SELECT @PAR1

EXEC teste 130

--

ALTER PROCEDURE p_LivroValor (@ID AS SMALLINT)
AS
    SELECT Nome_Livro, Preco_Livro
    FROM tbl_Livro
    WHERE ID_Livro = @ID

EXEC p_LivroValor 103

--

ALTER PROCEDURE teste (@PAR1 AS INT, @PAR2 AS VARCHAR(20))
AS
SELECT @PAR1
SELECT @PAR2

EXEC teste 24, 'Lucas Felicio'

-- POR PARAMETROS

EXEC teste @PAR2 = 'Lucas Felicio', @PAR1 = 5

-- Stored Procedures - Parâmetros de Entrada e INSERT no SQL Server - parte 03

ALTER PROCEDURE p_LivroValor
(@ID SMALLINT, @PRECO MONEY)
AS
SELECT ID_Livro AS ID, Nome_Livro AS LIVRO, Preco_Livro AS PRECO
FROM tbl_Livro
WHERE ID_Livro > @ID AND Preco_Livro > @PRECO

EXEC p_LivroValor 105, 46.00

--

ALTER PROCEDURE p_LivroValor
(@ID SMALLINT, @QUANTIDADE SMALLINT)
AS
SELECT Nome_Livro AS Livro, Preco_Livro * @QUANTIDADE AS PREÇO
FROM tbl_Livro
WHERE ID_Livro = @ID

EXEC p_LivroValor 103, 10

--

CREATE PROCEDURE p_InsereLivro
(@Nome AS VARCHAR(30))
AS
INSERT INTO tbl_Livro (Nome_Livro)
VALUES (@Nome)

SELECT * FROM tbl_Livro

EXEC p_InsereLivro 'SP_Teste'

-- Stored Procedures - Parâmetros de Saída e RETURN no SQL Server - parte 04

CREATE PROCEDURE p_valor_padrao
(@PAR1 INT, @PAR2 VARCHAR(20) = 'Valor Padrão!')
AS
SELECT 'Valor do Parametro 1: ' +CAST(@PAR1 AS VARCHAR)
SELECT 'Valor do Parametro 2: '+@PAR2

EXEC p_valor_padrao 12, 66

--

ALTER PROCEDURE teste
(@par1 INT OUTPUT)
AS
SELECT @par1 * 2
RETURN

DECLARE @VALOR INT = 15
EXEC teste @VALOR OUTPUT
PRINT

--

ALTER PROCEDURE p_LivroValor
(@QUANTIDADE SMALLINT, @COD SMALLINT = -10, @ID SMALLINT)
AS
    SET NOCOUNT ON -- RETIRAR MENSAGEM DE LINHAS AFETADAS
    IF @ID >=100
        BEGIN
            SELECT Nome_Livro AS LIVRO, Preco_Livro * @QUANTIDADE AS PREÇO
            FROM tbl_Livro
            WHERE ID_Livro = @ID
    RETURN 1
    END
        ELSE
    RETURN @COD

DECLARE @CODIGO INT
EXEC @CODIGO = p_LivroValor @ID = 102, @QUANTIDADE = 20
PRINT @CODIGO