SQLQuery2.sql

/* CONECTANDO A UM BANCO */

USE EMPRESA
GO 

/* CRIACAO DE TABELAS */

CREATE TABLE ALUNO(
	IDALUNO INT PRIMARY KEY IDENTITY,
	NOME VARCHAR(30) NOT NULL,
	SEXO CHAR(1)NOT NULL,
	NASCIMENTO DATE NOT NULL,
	EMAIL VARCHAR(30) UNIQUE
)
GO

/*CONSTRAINTS */

ALTER TABLE ALUNO 
ADD CONSTRAINT CK_SEXO CHECK (SEXO IN('M','F'))
GO

/*1 X 1 */

CREATE TABLE ENDERECO(
	IDENDERECO INT PRIMARY KEY IDENTITY(100,10),
	BAIRRO VARCHAR(30),
	UF CHAR(2) NOT NULL
	CHECK (UF IN('RJ','SP','MG')),
	ID_ALUNO INT UNIQUE 
)
GO

/* CRIANDO A FK */ 

ALTER TABLE ENDERECO ADD CONSTRAINT FK_ENDERECO_ALUNO 
FOREIGN KEY(ID_ALUNO) REFERENCES ALUNO(IDALUNO)
GO

/* COMANDO DE DESCRICAO */

/*PROCEDURES - JA CRIADAS E ARMAZENA NO SISTEMA */

SP_COLUMNS ALUNO
GO

SP_HELP
GO

/* INSERINDO DADOS */

INSERT INTO ALUNO VALUES('ANDRE','M','1981/12/09','ANDRE@IG.COM')
INSERT INTO ALUNO VALUES('ANA','F','1978/03/09','ANA@IG.COM')
INSERT INTO ALUNO VALUES('RUI','M','1951/07/09','RUI@IG.COM')
INSERT INTO ALUNO VALUES('JOAO','M','2002/11/09','JOAO@IG.COM')
GO

SELECT * FROM ALUNO
GO

/* CRIANDO A TABELA TELEFONES 1 X N */
CREATE TABLE TELEFONE(
	IDTELEFONE INT PRIMARY KEY IDENTITY,
	TIPO CHAR(3) NOT NULL,
	NUMERO VARCHAR(10) NOT NULL,
	ID_ALUNO INT,
	CHECK (TIPO IN ('RES','COM','CEL'))
)
GO

INSERT INTO TELEFONE VALUES('CEL','7899889',1)
INSERT INTO TELEFONE VALUES('RES','4325444',1)
INSERT INTO TELEFONE VALUES('COM','4354354',2)
INSERT INTO TELEFONE VALUES('CEL','2344556',2)
GO

SELECT * FROM ALUNO
GO
/* PEGAR DATA ATUAL */

SELECT GETDATE()
GO

/* CLAUSULA AMBIGUA */

SELECT A.NOME, T.TIPO, T.NUMERO, E.BAIRRO, E.UF
FROM ALUNO A LEFT JOIN TELEFONE T
ON A.IDALUNO = T.ID_ALUNO
INNER JOIN ENDERECO E
ON A.IDALUNO = E.ID_ALUNO
GO 

/* IFNULL */

SELECT A.NOME, 
	ISNULL(T.TIPO, 'SEM') AS "TIPO",
	ISNULL(T.NUMERO,'NUMERO') AS "TELEFONE", 
	E.BAIRRO, 
	E.UF
FROM ALUNO A LEFT JOIN TELEFONE T
ON A.IDALUNO = T.ID_ALUNO
INNER JOIN ENDERECO E
ON A.IDALUNO = E.ID_ALUNO
GO 

/* DATAS */

SELECT * FROM ALUNO 
GO 

SELECT NOME, NASCIMENTO
FROM ALUNO
GO

/* DATEDIFF - CALCULA A DIFERENCA ENTRE 2 DATAS 
FUNCAO GETADATE() TRAZ DIA E HORA
*/

SELECT NOME, GETDATE() AS DIA_HORA FROM ALUNO
GO

/* 1 PASSO */

SELECT NOME, DATEDIFF(DAY,NASCIMENTO,GETDATE())
FROM ALUNO
GO

/* 2 PASSO */

SELECT NOME, DATEDIFF(DAY,NASCIMENTO,GETDATE()) AS "IDADE"
FROM ALUNO
GO

SELECT NOME, DATEDIFF(YEAR,NASCIMENTO,GETDATE()) AS IDADE
FROM ALUNO
GO

SELECT NOME, DATEDIFF(MONTH,NASCIMENTO,GETDATE()) AS "IDADE"
FROM ALUNO
GO

SELECT NOME, DATEDIFF(MINUTE,NASCIMENTO,GETDATE()) AS "IDADE"
FROM ALUNO
GO

SELECT NOME, DATEDIFF(SECOND,NASCIMENTO,GETDATE()) AS "IDADE"
FROM ALUNO
GO  -- DEU ERRO KKKK --

/* 3 PASSO - RETORNO EM INTERIO + OPER MATEMATICA */

SELECT NOME, DATEDIFF(DAY,NASCIMENTO,GETDATE())/365 AS "IDADE"
FROM ALUNO
GO

SELECT NOME, DATEDIFF(MINUTE,NASCIMENTO,GETDATE()) AS "IDADE"
FROM ALUNO
GO

/* DATENAME - TRAZ O NOME DA PARTE DA DATA EM QUESTAO - STRING */

SELECT NOME, DATENAME(MONTH, NASCIMENTO)
FROM ALUNO 
GO

SELECT NOME, DATENAME(YEAR, NASCIMENTO)
FROM ALUNO 
GO

SELECT NOME, DATENAME(WEEKDAY, NASCIMENTO)
FROM ALUNO 
GO

/* DATEPART - POREM O RETORNO É UM INTEIRO */

SELECT NOME, DATEPART(MONTH, NASCIMENTO), DATENAME(MONTH, NASCIMENTO)
FROM ALUNO 
GO

/* DATEADD - RETORNA UMA DATA SOMANDO A OUTRA DATA */

SELECT DATEADD(DAY,365,GETDATE())

SELECT DATEADD(YEAR,10,GETDATE())

/* CONVERSAO DE DADOS */

SELECT 1 + '1'
GO

SELECT '1' + '1'
GO

SELECT 'PABLO' + 'OLIVEIRA'
GO

SELECT 'CURSO DE BANCO DE DADOS' + '1'
GO

SELECT 'CURSO DE BANCO DE DADOS' + 1
GO

/* FUNCOES DE CONVERSAO DE DADOS */

SELECT CAST('1' AS INT) + CAST('1' AS INT)
GO

/* CONVVERSAO E CONCATENACAO */

SELECT NOME,
NASCIMENTO 
FROM ALUNO
GO

SELECT NOME,
DAY(NASCIMENTO) + '/' + MONTH(NASCIMENTO) + '/' + YEAR(NASCIMENTO)
FROM ALUNO
GO

SELECT NOME,
CAST(DAY(NASCIMENTO) AS varchar) + '/' + 
CAST(MONTH(NASCIMENTO) AS varchar) + '/' + 
CAST(YEAR(NASCIMENTO) AS varchar)
FROM ALUNO
GO

/* CHARINDEX - RETORNA UM INTEIRO 
CONTAGEM DEFAULT - INICIA EM 01 
*/

SELECT NOME, CHARINDEX('A', NOME) AS INDICE 
FROM ALUNO
GO

SELECT NOME, CHARINDEX('A', NOME) AS INDICE 
FROM ALUNO
GO

SELECT NOME, CHARINDEX('A', NOME, 2) AS INDICE 
FROM ALUNO
GO

/* BULK INSERT - IMPORTACAO DE ARQUIVOS */

CREATE TABLE LANCAMENTO_CONTABIL(
	CONTA INT,
	VALOR INT,
	DEB_CRED CHAR(1)
)
GO

SELECT * FROM LANCAMENTO_CONTABIL
GO

/* \t = TAB */


BULK INSERT LANCAMENTO_CONTABIL
FROM 'D:\Arquivos do Usuario\Jogos Salvos\Aulas Softblue SQL\CONTAS.txt'
WITH 
(
	FIRSTROW = 2,
	DATAFILETYPE = 'char',
	FIELDTERMINATOR = '\t',
	ROWTERMINATOR = '\n'
)
GO

DELETE FROM LANCAMENTO_CONTABIL
GO

/* PROCEDURES */

SP_STORAGE PROCEDURE

CREATE TABLE PESSOA(
	IDPESSOA INT PRIMARY KEY IDENTITY,	
	NOME VARCHAR(30) NOT NULL,
	SEXO CHAR(1) NOT NULL CHECK (SEXO IN('M','F')), --ENUM 
	NASCIMENTO DATE NOT NULL
)
GO

CREATE TABLE TELEFONE(
	IDTELEFONE INT NOT NULL IDENTITY,
	TIPO CHAR(3) NOT NULL CHECK ( TIPO IN('CEL','COM')),
	NUMERO CHAR(10) NOT NULL,
	ID_PESSOA INT
)
GO

ALTER TABLE TELEFONE ADD CONSTRAINT FK_TELEFONE_PESSOA
FOREIGN KEY(ID_PESSOA) REFERENCES PESSOA(IDPESSOA)
ON DELETE CASCADE
GO

INSERT INTO PESSOA VALUES('ANTONIO','M','1981-02-13')
INSERT INTO PESSOA VALUES('DANIEL','M','1985-03-18')
INSERT INTO PESSOA VALUES('CLEIDE','F','1979-10-13')
INSERT INTO PESSOA VALUES('MAFRA','M','1981-02-13')


INSERT INTO TELEFONE VALUES('CEL','9879008',1)
INSERT INTO TELEFONE VALUES('COM','8757909',1)
INSERT INTO TELEFONE VALUES('CEL','9875890',2)
INSERT INTO TELEFONE VALUES('CEL','9347689',2)
INSERT INTO TELEFONE VALUES('COM','2998689',3)
INSERT INTO TELEFONE VALUES('COM','2098978',2)
INSERT INTO TELEFONE VALUES('CEL','9008679',3)
GO

/* CRIANDO A PROCEDURE */

CREATE PROC SOMA
AS
	SELECT 10 + 10 AS SOMA 
GO

/* EXECUÇÃO DA PROCEDURE */

EXEC SOMA
GO

/* DINAMICAS - COM PARAMETROS */

CREATE PROC CONTA @NUM1 INT, @NUM2 INT
AS 
	SELECT @NUM1 + @NUM2 AS RESULTADO
GO

/* EXECUTANDO */

EXEC CONTA 90,78
GO

/* APAGANDO A PROCEDURE */

DROP PROC CONTA
GO

/* PROCEDURES EM TABELAS */

SELECT NOME, NUMERO
FROM PESSOA
INNER JOIN TELEFONE
ON IDPESSOA = ID_PESSOA
WHERE TIPO = 'CEL'
GO

/* TRAZER OS TELEFONES DE ACORDO COM O TIPO PASSADO */
CREATE PROC TELEFONES @TIPO CHAR(3)
AS 
	SELECT NOME, NUMERO
	FROM PESSOA
	INNER JOIN TELEFONE
	ON IDPESSOA = ID_PESSOA
	WHERE TIPO = @TIPO
GO

EXEC TELEFONES 'CEL'
GO

EXEC TELEFONES 'COM'
GO


SELECT @@IDENTITY --- GUARDA O ULTIMO IDENTITY INSERIDO NA SECAO
GO



/* PARAMETROS DE OUTPUT */

SELECT TIPO, COUNT(*) AS QUANTIDADE
FROM TELEFONE
GROUP BY TIPO
GO

/* CRIANDO PROCEDURE COM PARAMETROS DE ENTRADA E PARAMETRO DE SAÍDA */
CREATE PROCEDURE GETTIPO @TIPO CHAR(3), @CONTADOR INT OUTPUT
AS				
	SELECT @CONTADOR = COUNT(*)
	FROM TELEFONE
	WHERE TIPO = @TIPO
	GO

/* EXECUCAO DA PROC COM PARAMETRO DE SAIDA */

/* TRANSACTION SQL -> LINGUAGEM QUE O SQL SERVER TRABALHA */

DECLARE @SAIDA INT
EXEC GETTIPO @TIPO = 'CEL', @CONTADOR = @SAIDA OUTPUT 
SELECT @SAIDA
GO 

DECLARE @SAIDA INT
EXEC GETTIPO 'CEL', @SAIDA OUTPUT 
SELECT @SAIDA
GO 

/* PROCEDURE DE CADASTRO */

CREATE PROC CADASTRO @NOME VARCHAR(30), @SEXO CHAR(1), @NASCIMENTO DATE,
@TIPO CHAR(3), @NUMERO VARCHAR(10)
AS 
	DECLARE @FK INT 

	INSERT INTO PESSOA VALUES(@NOME,@SEXO,@NASCIMENTO) --GERAR UM ID

	SET @FK = (SELECT IDPESSOA FROM PESSOA WHERE IDPESSOA
	= @@IDENTITY)

	INSERT INTO TELEFONE VALUES(@TIPO,@NUMERO,@FK)
GO

CADASTRO 'JORGE','M','1991-01-01','CEL','97273822'
GO

SELECT PESSOA.*, TELEFONE.*
FROM PESSOA 
INNER JOIN TELEFONE 
ON IDPESSOA = ID_PESSOA
GO

 /* TSQL É UM BLOCO DE LINGUAGEM DE PROGRAMAÇÃO. 
 PROGRAMAS SÃO UNIDADES QUE PODEM SER CHAMADAS DE BLOCOS
 ANÔNIMOS. BLOCOS ANONIMOS NAO RECEBEM NOME, POIS 
 NAO SAO SALVOS NO BANCO. CRIAMOS BLOCOS ANONIMOS QUANDO
 IREMOS EXECUTA-LOS UMA SO VEZ OU TESTAR ALOGO */

 /* BLOCO DE EXECUCAO */
 
 BEGIN 
		PRINT 'PRIMEIRO BLOCO'
 END
 GO

 /* BLOCOS DE ATRIBUICAO DE VARIAVEIS */

 DECLARE 
			@CONTADOR INT		
 BEGIN
			SET @CONTADOR = 5
			PRINT @CONTADOR
 END
 GO

/* NO SQL SERVER CADA COLUNA, VARIAVEL LOCAL, EXPRESSAO E PARAMETRO TEM UM TIPO. */

DECLARE 

	@V_NUMERO NUMERIC(10,2) = 100.52,
	@V_DATA DATETIME = '20170207'
BEGIN
	PRINT 'VALOR NUMERICO: ' + CAST(@V_NUMERO AS VARCHAR)
	PRINT 'VALRO NUMERICO: ' + CONVERT(VARCHAR, @V_NUMERO)
	PRINT 'VALOR DE DATA: '  + CAST(@V_DATA AS VARCHAR)
	PRINT 'VALRO DE DATA: ' + CONVERT(VARCHAR, @V_DATA, 121)
	PRINT 'VALRO DE DATA: ' + CONVERT(VARCHAR, @V_DATA, 120)
	PRINT 'VALRO DE DATA: ' + CONVERT(VARCHAR, @V_DATA, 105)
END
GO

/* ATRIBUINDO RESULTADOS A UMA VARIAVEL */

CREATE TABLE CARROS(
	CARRO VARCHAR(20),
	FABRICANTE VARCHAR(30)
)
GO

INSERT INTO CARROS VALUES('KA','FORD')
INSERT INTO CARROS VALUES('FIESTA','FORD')
INSERT INTO CARROS VALUES('PRISMA','FORD')
INSERT INTO CARROS VALUES('CLIO','RENAULT')
INSERT INTO CARROS VALUES('SANDERO','RENAULT')
INSERT INTO CARROS VALUES('CHEVETE','CHEVROLET')
INSERT INTO CARROS VALUES('OMEGA','CHEVROLET')
INSERT INTO CARROS VALUES('PALIO','FIAT')
INSERT INTO CARROS VALUES('DOBLO','FIAT')
INSERT INTO CARROS VALUES('UNO','FIAT')
INSERT INTO CARROS VALUES('GOL','VOLKSWAGEN')
GO

DECLARE 
		@V_CONT_FORD INT,
		@V_CONT_FIAT INT
BEGIN
		--METODO 1 - O SELECT PRECISA RETORNAR UMA SIMPLES COLUNA 
		--E UM SO RESULTADO 
		SET @V_CONT_FORD = (SELECT COUNT(*) FROM CARROS
		WHERE FABRICANTE = 'FORD')

		PRINT 'QUANTIDADE FORD: ' + CAST(@V_CONT_FORD AS VARCHAR)

		--METODO 2 
		SELECT @V_CONT_FIAT = COUNT(*) FROM CARROS WHERE FABRICANTE = 'FIAT'

		PRINT 'QUANTIDADE FIAT: ' + CONVERT(VARCHAR, @V_CONT_FIAT)

END
GO
		
/* BLOCOS IF E ELSE 
BLOCO NOMEADO - PROCEDURES
*/

DECLARE 
		@NUMERO INT = 5
BEGIN 
		IF	@NUMERO = 5 -- EXPRESSAO BOOLEANA - TRUE 
			PRINT 'O VALOR É VERDADEIRO'
		ELSE
			PRINT 'O VALOR É FALSO '
END
GO

/* CASE */

DECLARE
		@CONTADOR INT
BEGIN
		SELECT -- O CASE REPRESENTA UMA COLUNA
		CASE	
			WHEN FABRICANTE = 'FIAT' THEN 'FAIXA 1'
			WHEN FABRICANTE = 'CHEVROLET' THEN 'FAIXA 2'
			ELSE 'OUTRAS FAIXAS'
		END AS "INFORMACOES",
		*
		FROM CARROS
END
GO


/* LOOPS WHILE */

DECLARE 
		@I INT = 1 
BEGIN 
		WHILE (@I <15)
		BEGIN
			PRINT 'VALOR DE @I = ' + CAST(@I AS VARCHAR)
			SET @I = @I + 1 
		END

END
GO