SQL Server

Aula
Restaurar Backups
 Constraints
• São utilizadas para especificar regras de armazenamentos de
dados nas tabelas e garantir integridade.
Tipo Constraint Descrição

NOT NULL Garante que uma coluna não recebera valor NULL

UNIQUE Garante que os valores em uma coluna sejam diferentes

PRIMARY KEY Chave única, linha exclusiva com, combinação com

FOREIGN KEY Referencia o valor de um campo em determinada linha e outra tabela.

DEFAULT Define um valor padrão para uma coluna quando nenhum valor é especificado
Usado para criar e recuperar dados do banco de dados com melhor
INDEX
performance
CHECK Valida valor que inserido em uma coluna, como uma restrição.
Operadores de Comparação
Operador Significado
= igual a
> maior que
< menor que
>= maior ou igual que
<= menor que ou igual a
<> diferente de
!= diferente de (não é padrão ISO)
!< Não é menor que (não é padrão ISO)
!> Não é maior que (não é padrão ISO)
 Operadores Lógicos / Filtros
• Os operadores lógicos testam a legitimidade de algumas condições. Operadores lógicos, como
operadores de comparação, retornam um tipo de dados Boolean com um valor TRUE, FALSE ou
UNKNOWN
Operador Significado

WHERE A cláusula WHERE é usada para extrair somente os registros/linhas que estiverem dentro das especificações da
condição.
AND TRUE se as duas expressões boolianas forem TRUE.

ANY TRUE se qualquer conjunto de comparações for TRUE.

BETWEEN TRUE se o operando estiver dentro de um intervalo.

EXISTS TRUE se uma subconsulta tiver qualquer linha.

IN TRUE se o operando for igual a um de uma lista de expressões.

LIKE TRUE se o operando corresponder a um padrão.

NOT Inverte o valor de qualquer outro operador booliano.

OR TRUE se qualquer expressão booliana for TRUE.

IS NULL TRUE se o valor for NULO

IS NOT NULL TRUE se o valor não for NULO

HAVING A cláusula HAVING foi adicionada ao SQL porque a palavra-chave WHERE não pode ser usada com funções agregadas.
Operadores Matemáticos
 Language Statements
• A linguagem SQL é dividida em quatro tipos de instruções de linguagem primárias :
DML, DDL, DCL e TCL .

• Usando estas declarações, podemos definir a estrutura de um banco de dados

através da criação e alteração
de objetos de banco de dados, e
podemos manipular dados em uma tabela
através de atualizações ou eliminações .

• Nós também podemos controlar qual

usuário pode ler / escrever dados ou
gerencia operações
Language Statements
Language Statements
Language Statements
Language Statements
Language Statements
 CRUD
• O CRUD é um acrônimo para as 4 operações básicas de um banco
de dados.
 SQL - Índices
• Definidos sobre atributos para acelerar consultas a dados
• Índices são definidos automaticamente para chaves primárias
• Operações:
CREATE [UNIQUE] INDEX nome_índice ON
nome_tabela (nome_atributo_1[{, nome_atributo_n }])
DROP INDEX nome_índice ON nome_tabela
• Exemplos
CREATE UNIQUE INDEX indPac_CPF ON Pacientes (CPF)
DROP INDEX indPac_CPF ON Pacientes
 DDL
CREATE TABLE Cliente(
codigo int,
nome varchar(50) not null,
endereco varchar(150) not null,
cod_departamento int,
CONSTRAINT pk_cliente PRIMARY KEY (codigo),
CONSTRAINT fk_cliente FOREIGN KEY (cod_departamento) references
Departamento (codigo)
);
 DDL

ALTER TABLE Cliente

ADD (Data_nascimento date)

DROP COLUMN endereco

ADD CONSTRAINT fk_cliente FOREIGN KEY

(cod_departamento) references Departamento

(codigo);

DROP TABLE Cliente;

 Praticar

• Criar um novo esquema de BD.

• Criar três tabelas:

Funcionario (codigo, nome, endereco, telefone, cod_departamento)
cod_departamento referencia Departamento
Departamento (codigo, descricao)
Dependentes (codigo, seq, cod_funcionario, nome, data_nasc)
cod_funcionario referencia Funcionario
 DML

• Linguagem para manipulação dos dados.

• Existem 4 operações principais:

• Insert – Inclusão de dados
• Update – Alteração dos dados
• Delete – Exclusão de dados
• Select – Seleção de dados
 INSERT

• Possui duas formas de utilizar esse comando:

1. Informando as colunas que deseja colocar valores:

INSERT INTO Cliente (codigo, nome, endereco) VALUES (1, ‘Pedro’,
‘Rua teste’);

2. Não informamos as colunas e valores para todas as colunas:

INSERT INTO Cliente VALUES (1, ‘Pedro’, ‘Rua teste’, ’04/04/1984’);
 UPDATE

• Comando utilizado para alterar os dados de uma tabela.

UPDATE table_name
SET column1 = value1, column2 = value2,...
WHERE some_column = some_value;
 DELETE

• Comando utilizado para deletar tuplas de uma tabela.

DELETE FROM table_name
WHERE column1 = value1
AND column2 = value2;
 SELECT

• Comando utilizado para selecionar tuplas de uma ou mais tabelas.

SELECT coluna1, coluna2, coluna3
FROM tabela_nome1, tabela_nome2
WHERE coluna1 = valor1
AND coluna2 = valor2
OR coluna2 = valor3;
SELECT
 SELECT

• Comando utilizado para selecionar tuplas de uma ou mais tabelas.

SELECT coluna1, coluna2, coluna3
FROM tabela_nome1, tabela_nome2
WHERE coluna1 = valor1
AND coluna2 = valor2 OR coluna2 = valor3
GROUP BY coluna1
HAVING AVG (coluna1) > 100
ORDER BY coluna2;
 Cláusula SELECT

• Informa quais as colunas existirão no resultado da consulta.

SELECT nome, email
SELECT (vlUnitario + 10) as valor
SELECT *
SELECT COUNT(*) quantidade
 Cláusula FROM

• Informa quais as tabelas envolvidas na consulta.

FROM cliente as c
FROM cliente c, fornecedor f
 Cláusula WHERE

• Define os filtros que serão aplicados na consulta.

WHERE nome LIKE ‘Nickerson%’
AND sobrenome LIKE ‘%Ferreira%’
OR sobrenome LIKE ‘%Fonseca%’;
 Funções de agregação

• Na linguagem SQL existem algumas funções que agrupam valores.

• São elas:
• COUNT: conta a quantidade de linhas
• AVG: realiza a média aritmética da coluna
• SUM: soma os valores da coluna
• MIN: retorna o menor valor da coluna
• MAX: retorna o maior valor da coluna
Funções de agregação
 Agrupando Valores

• As funções de agregação também podem agrupar os valores de

acordo com determinadas colunas.
 Agrupando Valores
• Podemos restringir os resultados das funções de agregação.
• Para isso utilizamos a cláusula HAVING.
 Ordenando Valores
• Para ordenar o resultado de uma pesquisa utilizamos a cláusula
ORDER BY.
• Pode ser ordenado de forma ascendente (ASC) ou descendente
(DESC).
• O padrão é ASC.
Junções
 SELECT

• Comando utilizado para selecionar tuplas de uma ou mais tabelas.

SELECT coluna1, coluna2, coluna3
FROM tabela_nome1, tabela_nome2
WHERE coluna1 = valor1 AND coluna2 = valor2
OR coluna2 = valor3
GROUP BY coluna1
HAVING AVG(coluna1) > 100
ORDER BY coluna2;
 Junções
• Até o momento temos consultas acessando apenas uma tabela.
• E quando temos duas tabelas ligadas por uma chave estrangeira ??
Como realizar essa junção ??
• Utilizando o comando SELECT podemos acessar várias tabelas.

SELECT [Link], [Link]

FROM funcionario FUNC, dependente DEP

WHERE [Link] = DEP.cod_func;

 Tipos de Junções

• Existem alguns tipos de junção:

• Junção de produto cartesiano
• Junção Interna
• Junção Externa
 Junção de produto cartesiano

• É uma junção entre duas tabelas que origina uma “terceira tabela”
constituída por todos os elementos da primeira combinadas com
todos os elementos da segunda.

• Exemplo:
Junção de produto cartesiano
 Junção Interna

• Funciona de forma semelhante à junção de produto cartesiano.

• Porém, utiliza uma sintaxe diferente.

SELECT [Link] NOME_FUNC, [Link] NOME_DEP

FROM funcionario FUNC INNER JOIN
dependente DEP ON ([Link] = DEP.COD_FUNC);
Junção Interna
 Junção Externa

• Retorna um valor nulo (null) para o correspondente que não

encontrar.

• Existem vários padrões de junção externa, os principais são:

• LEFT OUTER JOIN (Junção externa esquerda)

• RIGHT OUTER JOIN (Junção externa direita)

SELECT [Link] NOME_FUNC, [Link] NOME_DEP

FROM funcionario FUNC [LEFT ou RIGHT]
OUTER JOIN dependente DEP ON ([Link] = DEP.COD_FUNC);
LEFT OUTER JOIN
RIGHT OUTER JOIN
SELECT [Link] "[Link]", [Link] "[Link]"
FROM TABELA_A A INNER JOIN TABELA_B B
ON [Link] = [Link]
LEFT OUTER JOIN:
SELECT [Link] "[Link]", [Link] "[Link]"
FROM TABELA_A A
LEFT OUTER JOIN TABELA_B B ON [Link] =
[Link]
RIGHT OUTER JOIN:
SELECT [Link] "[Link]", [Link] "[Link]"
FROM TABELA_A A
RIGHT OUTER JOIN TABELA_B B ON [Link] =
[Link]
FULL OUTER JOIN:
SELECT [Link] "[Link]", [Link] "[Link]"
FROM TABELA_A A
FULL OUTER JOIN TABELA_B B ON [Link] =
[Link]
UPDATE
CREATE TABLE colaboradores (
id SMALLINT,
nome VARCHAR(50),
cargo VARCHAR(50),
casado BIT,
conjuge VARCHAR(50),
salario DECIMAL,
comissao DECIMAL
);
INSERT INTO colaboradores VALUES ...

(1, 'Armeloque Araujo', 'Gerente de Vendas', 0, null, 20000, 0)

(2, 'Colapso Cardiaco', 'Aspone Senior', 0, null, 15000, 10)

(3, 'Defuntina da Cruz', 'Assistente de Vendas', 0, null, 7000, 10)

(4, 'Jean Claude Van Dame Da Silva', 'Assistente de Vendas', 0, null, 8000, 10)

(5, 'Mitiko Kudo Endo', 'Assistente de Vendas', 0, null, 9000, 10)

(6, 'Amisvaldo Teixeira', 'Caixa', 0, null, 4000, 0)

 Tarefa

• Aumentar o salário dos assistentes em 10% e mudar a comissão

para 20%.
UPDATE colaboradores
SET comissao = 20.00, salario = vl_salario * 1.10
WHERE cargo = 'Assistente de Vendas'
 Tarefa

• Atualizar a comissão de TODOS os colaboradores para 10%.

UPDATE colaboradores
SET comissao = 10.00
• implementar o plano de cargos e salários da empresa de acordo com o cargo
-- Para fazer isso usamos o comando CASE para definir o valor do salário de
acordo com o cargo
UPDATE colaboradores
SET salario = CASE cargo
WHEN 'Gerente de Vendas' THEN 25000
WHEN 'Aspone Senior' THEN 20000
WHEN 'Aspone Junior' THEN 18000
WHEN 'Assistente de Vendas' THEN 15000
ELSE 5000
END
SELECT * FROM colaboradores
 Assumindo o esquema abaixo:
empregado (id_empregado,nome_empregado, rua , cidade)
trabalha (id_empregado, id_companhia, salário)
companhia (id_companhia,nome_companhia, cidade)
gerente (Id_empregado, nome_gerente)

a) Crie o banco de dados

b) Tabelas
c) Índices
 Aprenda fazendo

CREATE TABLE empregado (

id_empregado INT(8) ZEROFILL NOT NULL AUTO_INCREMENT,
nome_empregado VARCHAR(40),
rua VARCHAR(50),
cidade VARCHAR(20),
CONSTRAINT IDX_EMPREGADO
PRIMARY KEY (id_empregado)

);
 Aprenda fazendo

CREATE TABLE trabalha (

id_empregado INT(8) ZEROFILL NOT NULL,
id_companhia INT(6) ZEROFILL NOT NULL,
salario DECIMAL(10,2) NOT NULL,
CONSTRAINT idx_trabalha
PRIMARY KEY (id_companhia,id_empregado)

);
 Aprenda fazendo

CREATE TABLE companhia (

id_companhia INT(6) ZEROFILL NOT NULL AUTO_INCREMENT,
nome_companhia VARCHAR(40),
cidade VARCHAR(40),
CONSTRAINT idx_companhia
PRIMARY KEY (id_companhia)

);
 Aprenda fazendo

CREATE TABLE gerente (

id_empregado INT(8) ZEROFILL NOT NULL,
nome_gerente VARCHAR(40) NOT NULL,
CONSTRAINT idx_gerente
PRIMARY KEY (id_empregado)

);
 Criando Índices Secundários

CREATE INDEX alfa_empregado

ON empregado (nome_empregado);

CREATE INDEX alfa_companhia

ON companhia (nome_companhia);

CREATE INDEX Alfa_gerente

ON gerente (nome_gerente);
empregado (nome_empregado, rua , cidade)
Inserir três empregados para cada empresa.
trabalha (nome_empregado, nome_companhia, salário)
Inserir aonde cada empregado trabalha
companhia (nome_companhia, cidade)
Inserir companhias
Byte Corporation, Small Bank Corporation, XYZ Ltda
gerente (nome_empregado, nome_gerente)
Inserir um gerente em cada empresa
 Inserindo Registros
INSERT INTO empregado (nome_empregado, rua, cidade) VALUES ('José Antonio’, 'Osvaldo Cruz’, 'Fortaleza');

INSERT INTO empregado (nome_empregado, rua, cidade) VALUES ('Paulo José','Tristão Gomes','Sobral');

INSERT INTO empregado (nome_empregado, rua, cidade) VALUES ('Pedro Ivo’, 'Pontes Alberto’, 'Fortaleza');

INSERT INTO empregado (nome_empregado, rua, cidade) VALUES ('Maria Joana’, 'Folião Augusto’, 'Eusébio');

INSERT INTO empregado (nome_empregado, rua, cidade) VALUES ('Samia Roberts’, 'Patrocinio’, 'Fortaleza');

INSERT INTO empregado (nome_empregado, rua, cidade) VALUES ('Kamylla Borges’, 'Princesa Isa’, 'Eusébio');

INSERT INTO empregado (nome_empregado, rua, cidade) VALUES ('Fábio Kraus’, 'Dom Pedro I’, 'Sobral');

INSERT INTO empregado (nome_empregado, rua, cidade) VALUES ('Sebastian Miller’, 'Floriano Itu’, 'Eusébio');

INSERT INTO empregado (nome_empregado, rua, cidade) VALUES ('Rosa Palmer’, 'Machado de Aço’, 'Sobral');
 Inserindo Registros
INSERT INTO companhia (nome_companhia, cidade) VALUE ('Byte Corporation’, 'Fortaleza');

INSERT INTO companhia (nome_companhia, cidade) VALUE ('Small Bank Corporation’,

'Eusébio');

INSERT INTO companhia (nome_companhia, cidade) VALUE ('XYZ Ltda’, 'Sobral');

INSERT INTO companhia (nome_companhia, cidade) VALUE ('Byte Corporation’, 'Eusébio');

INSERT INTO companhia (nome_companhia, cidade) VALUE ('Small Bank Corporation’,

'Sobral');

INSERT INTO companhia (nome_companhia, cidade) VALUE ('XYZ Ltda’, 'Fortaleza');

 Inserindo Registros
INSERT INTO trabalha (id_companhia, id_empregado, salario) VALUES (1, 1, 1000);

INSERT INTO trabalha (id_companhia, id_empregado, salario) VALUES (1, 3, 2000);

INSERT INTO trabalha (id_companhia, id_empregado, salario) VALUES (1, 5, 11000);

INSERT INTO trabalha (id_companhia, id_empregado, salario) VALUES (2, 2, 1000);

INSERT INTO trabalha (id_companhia, id_empregado, salario) VALUES (2, 4, 3000);

INSERT INTO trabalha (id_companhia, id_empregado, salario) VALUES (2, 6, 12000);

INSERT INTO trabalha (id_companhia, id_empregado, salario) VALUES (3, 7, 1000);

INSERT INTO trabalha (id_companhia, id_empregado, salario) VALUES (3, 8, 4000);

INSERT INTO trabalha (id_companhia, id_empregado, salario) VALUES (3, 9, 15000);

 Inserindo Registros
INSERT INTO gerente (id_empregado, nome_gerente) VALUES (1, 'Samia Roberts');

INSERT INTO gerente (id_empregado, nome_gerente) VALUES (3, 'Samia Roberts');

INSERT INTO gerente (id_empregado, nome_gerente) VALUES (2, 'Kamylla Borges');

INSERT INTO gerente (id_empregado, nome_gerente) VALUES (4, 'Kamylla Borges');

INSERT INTO gerente (id_empregado, nome_gerente) VALUES (7, 'Rosa Palmer');

INSERT INTO gerente (id_empregado, nome_gerente) VALUES (8, 'Rosa Palmer');

 Aprenda fazendo

SELECT * FROM empregado;

SELECT * FROM companhia;

SELECT * FROM gerente;

SELECT * FROM trabalha;

a) Encontre os nomes de todos os empregados que trabalham para a XYZ Ltda.
SELECT empregado.nome_empregado, trabalha.id_companhia,
[Link]

FROM empregado INNER JOIN

(companhia INNER JOIN trabalha ON

companhia.id_companhia = trabalha.id_companhia) ON

(empregado.id_empregado = trabalha.id_empregado)

AND (companhia.nome_companhia = 'XYZ Ltda');

b) Encontre todos os nomes das cidades dos empregados que
trabalham na XYZ Ltda.
SELECT [Link] FROM empregado
INNER JOIN (companhia INNER JOIN trabalha ON
companhia.id_companhia = trabalha.id_companhia) ON
(empregado.id_empregado = trabalha.id_empregado)
AND (companhia.nome_companhia = 'XYZ Ltda');
c) Encontre os nomes, endereço e cidade da residência de todos os
empregados da XYZ Ltda. que ganham mais de dez mil.

SELECT empregado.nome_empregado, rua, [Link] FROM

empregado INNER JOIN
(companhia INNER JOIN trabalha ON
companhia.id_companhia = trabalha.id_companhia) ON
(empregado.id_empregado = trabalha.id_empregado) AND
(companhia.nome_companhia = 'XYZ Ltda') AND
[Link] >10000;
d) Encontre os nomes de todos os empregados que moram na mesma
cidade da companhia em que trabalham.

SELECT empregado.nome_empregado, [Link]

FROM empregado INNER JOIN
(companhia INNER JOIN trabalha ON
companhia.id_companhia = trabalha.id_companhia) ON
(empregado.id_empregado = trabalha.id_empregado) AND
([Link] = [Link]);
e) Encontre os nomes de todos os empregados que moram na mesma
cidade e na mesma rua de seu gerente.
SELECT ef.nome_empregado, [Link], g.nome_gerente, [Link]
FROM empregado AS ef
INNER JOIN gerente AS g ON
ef.id_empregado = g.id_empregado
INNER JOIN empregado AS T2 ON
t2.nome_empregado = g.nome_gerente
WHERE [Link] = [Link];
f) Encontre os nomes de todos os empregados que não trabalham
para a XYZ Ltda.
SELECT nome_empregado FROM empregado
WHERE NOT EXISTS
(SELECT trabalha.id_empregado FROM trabalha
WHERE empregado.id_empregado = trabalha.id_empregado
AND trabalha.id_companhia = 3);
g) Encontre os nomes de todos os empregados que ganham mais
que os empregados da Byte Corporation.
SELECT T1.id_empregado, empregado.nome_empregado
FROM trabalha T1
INNER JOIN empregado ON
(empregado.id_empregado = t1.id_empregado)
WHERE T1.id_companhia <> 3 AND
EXISTS (SELECT * FROM trabalha T2
WHERE T2.id_companhia = 3 AND [Link] > [Link]);
h) Assuma que as companhias possam estar localizadas em diversas
cidades. Encontre todas as companhias localizadas em todas as cidades
onde haja unidades da Small Bank Corporation
SELECT DISTINCT c2.nome_companhia
FROM companhia AS c2
WHERE c2.nome_companhia <> "Small Bank Corporation" AND
NOT EXISTS (SELECT cidade FROM companhia WHERE
companhia.nome_companhia = "Small Bank Corporation" AND
companhia.nome_companhia = c2.nome_companhia);