Apostila SQLServer

Prévia do material em texto

1 
Introdução à SQL 
 
O SQL (Structured Query Language), é um linguagem padrão usada para acesso a Banco 
de Dados. O conjunto de seus comandos permite-nos definir a estrutura dos dados no 
banco de dados relacional, acessar e manipular esses dados. Trata-se de uma linguagem 
não Procedural. Quando usamos o SQL, especificamos o que queremos fazer, e não como 
fazê-lo. É uma linguagem de fácil aprendizagem. 
SQL teve seu início em 1970, com E.F. Codd, que formulou os principios de um sistema 
relacional para manipulação de banco de dados e descreveu uma álgebra relacional para 
organizar os dados em tabelas. De lá para cá, a linguagem foi se aperfeiçoando e hoje 
existe um padrão desenvolvido pela American National Standards Institute (ANSI). Esse 
padrão tem sido a base para a maioria dos SQL's existentes atualmente no mercado. Cada 
fabricante inseriu novas funcionalidades em seu SQL, fazendo com que encontremos 
dezenas de versões de SQL. 
Apresentamos a seguir um visão geral da Arquitetura de um Sistema Gerenciador de 
Banco de Dados, para que possamos compreender do que se compõe a Linguagem SQL. 
 
Arquitetura SGBD 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Visão 
Externa 1 
Visão 
Externa n 
Esquema 
Conceitual 
Esquema 
Interno 
Nível Externo 
Nivel Conceitual 
Nível Interno 
Banco de Dados 
 
2 
 
 
1) Nível Interno. Descreve a estrutura física de armazenamento do Banco de Dados. O 
Esquema Interno usa um modelo de dados físico e descreve todos os detalhes do 
armazenamento de dados e caminhos de acesso ao Banco de Dados. 
 
2) Nível Conceitual. Descreve a estrutura de todo o Banco de Dados para os usuários. O 
Esquema Conceitual esconde os detalhes da estrutura física de armazenamento e se 
concentra em descrever as entidades, tipos de dados, relacionamentos, operações dos 
usuários e restrições. 
 
3) Nível Externo. Inclue um número de esquemas externos ou visões de usuários. Cada 
uma delas descreve uma parte do Banco de Dados que um grupo particular de usuários 
está interessado e esconde o resto do Banco de Dados aos demais usuários. 
 
 
Linguagens SGBD 
 
- Data Definition Language (DDL) - Para definir o esquema conceitual e o esquema 
interno. 
- View Definition Language (VDL) - Para definir as visões dos usuários e seus 
mapeamentos ao esquema conceitual. 
- Data Manipulation Language (DML) - Para manipular os dados de uma Base de 
Dados. (Consultar, Inserir, Excluir, Modificar). 
 
 
 
 
 
 
Definição de Dados em SQL 
 
SQL utiliza os termos tabela (table), linha (row), e coluna (column) para relação ou 
entidade, tupla e atributo, respectivamente. 
 
A Linguagem de Definição de Dados (DDL), permite executar tarefas como: 
- criar um objeto de banco de dados 
- excluir um objeto de banco de dados 
- alterar um objeto de banco de dados 
- conceder privilégios em um objeto de banco de dados 
- revogar privilégios de um objeto de banco de dados 
 
 
 
 
 
 SQL = ( DDL + VDL + DML ) 
 
3 
Conceitos utilizados em SQL 
 
 
Apresentamos alguns objetos e conceitos fundamentais no entendimento da linguagem 
SQL. 
 
- Banco de Dados (Database). É um conjunto de objetos SQL. Quando definimos um 
banco de dados, damos-lhe um nome para uma eventual coleção de tabelas e índices 
associados. 
 
- Tabela. Um banco de dados contém uma ou mais tabelas. Cada tabela tem um nome 
e contém um número específico de colunas e linhas (não ordenadas). 
 
- Índice. É um conjunto ordenado de ponteiros para os dados de uma tabela, 
armazenado separadamente da tabela. Cada índice é baseado nos valores dos dados de 
uma ou mais colunas de uma tabela. O índice visa melhorar a performance e também 
garantir a unicidade do dado (caso deseje). 
 
- Visão (View). É uma forma alternativa de representar os dados que existem em uma 
ou mais tabelas. Uma view pode Ter todas ou algumas colunas de uma ou mais 
tabelas. Pode-se também Ter views baseadas em outras views ou da combinação de 
views e tabelas. Ela se assemelha a uma tabela, com a única diferença, de que a view 
não existe fisicamente. Os seus dados estão armazenados nas tabelas nas quais elas se 
baseiam. 
 
- Junção (Join). Quando se deseja obter dados de mais de uma tabela, efetuamos a 
junção dessas tabelas. 
 
- Chave Primária (Primary Key). É uma columa ou grupo de colunas, cujo valor 
identifica cada linha da tabela. 
 
- Chave Estrangeira (Foreign Key). Colunas de tabelas que referenciam a chave 
primária de outra tabela. 
 
- Integridade Referencial. Quando definimos uma chave estrangeira, estamos 
definindo uma integridade referencial entre as tabelas envolvidas. 
 
- Catálogo do Sistema. Cada banco de dados tem um catálogo do sistema que contém 
tabelas do sistema. Essas tabelas contém informações sobre as tabelas, visões, 
colunas, índices, e privilégios de segurança do banco de dados. Em alguns SGBDs, 
esse catálogo é chamado de dicionário de dados. 
 
 
 
 
 
 
4 
Tipos de Dados (Datatypes) 
 
Tipos de Dados especifica que tipo de informação (caracteres, números, datas) a coluna 
de uma tabela pode armazenar e como esses dados serão armazenados. Abordaremos a 
seguir alguns tipos de dados encontrado no SQL da Microsoft. 
 
Binary [(n)] Qtde fixa de números binários 
Varbinary [(n)] Qtde variável de números binários 
Char [(n)] Qtde fixa de caracteres 
Varchar [(n)] Qtde variável de caracteres 
Datetime Data com precisão de HHMMSS e MS 
Smalldatetime Data com precisão de HHMMSS 
Decimal [(p[,s])] 
Numeric [(p[,s])] 
Valores numéricos, onde p é a qtde total incluindo a parte 
inteira e a parte decimal, e o s corresponde a parte decimal 
Tinyint Números inteiros. (-255 a +255) 
Smallint Númerso inteiros. (-32.767 à +32.767) 
Int Números inteiros. 
Money Valores monetários 
Smallmoney Valores monetários 
Text Textos 
Image Fotos e Imagens 
Bit, timestamp Tipos de dados especiais 
 
 
 
 
O usuário pode criar seu tipo de dado e armazená-lo no banco de dados para utilizá-lo na 
definição das colunas das novas tabelas. 
 
Exemplos: 
 
 EXEC sp_addtype cep, 'char(10)' 
 EXEC sp_addtype grandestring 'varchar(75)' 
 
 EXEC sp_droptype cep 
 
 
 
 
 
 
 
 
 
 
 
 
5 
Criação de Tabelas 
 
Para se criar tabelas dentro de um banco de dados, utilizamos a sentença CREATE 
TABLE. 
 
 CREATE TABLE [nome_banco.[dono_tabela]. ] nome_tabela 
 ( 
 [ nome_col propriedades_col [ constraint [constraint [ ... constraint]]] 
 | [ [ , ] constraint ]] 
 [ [ , ] [ próximo_nome_col | próxima_constraint] ... ] 
 ) 
 
 
 Nomes de colunas devem ser únicos dentro de uma mesma tabela 
 Toda coluna deve Ter um tipo de dado (datatype) 
 Pode-se definir até 2 bilhões de tabelas por banco de dados 
 Pode-se definir até 250 colunas por tabela 
 Um valor NULL indica a ausência de dados. NULL não é branco e nem zero. Por 
default, o SQL assume NOT NULL, para as colunas que não estão declaradas com 
NULL. Pode-se alterar esse default na configuração do SQL Server. 
 
 
Exemplos: 
 
CREATE TABLE empresa 
 ( cod_empresa int NOT NULL, 
 nome_empresa varchar(45) NOT NULL, 
 cgc char(14) NOT NULL, 
 dt_fundacao smalldatetime, 
 nro_funcionarios smallint ) 
 
 
 
 
CREATE TABLE funcionario 
 ( cod_func int, 
 nome_func varchar(45), 
 endereco varchar(50) NULL, 
 cidade varchar(30) NULL )6 
Integridade Referencial, Chave Primária e Chave Estrangeira 
 
A Integridade Referencial de um Banco de Dados Relacional é a verificação de que as 
ligações entre as linhas das tabelas relacionadas são válidas. Essas ligações são 
implementadas da relação entre a chave primária (PK) de uma tabela com a chave 
estrangeira (FK) da mesma tabela (no caso de um auto-relacionamento) ou de outra 
tabela. 
Chamamos a tabela que contém a chave primária de tabela pai, e a que contém a chave 
estrangeira de tabela filho. 
Quando criamos uma relação de Pai e Filho utilizando a Chave Primária e a Chave 
Estrangeira, o SGBD automaticamente passa a checar a integridade referencial das linhas 
das tabelas envolvidas, eliminando assim, a necessidade dessas checagem na 
programação das aplicações. 
Como exemplo, vamos considerar uma aplicação que trate os pedidos de um cliente. 
Temos uma tabela Cliente que é tabela pai de uma outra tabela Pedido, onde a Chave 
Primária da tabela Cliente é o código_do_cliente. Quando criamos a ligação entre as duas 
tabelas, teremos uma coluna código_do_cliente na tabela Pedido que será a Chave 
Estrangeira apontado para a Chave Primária da tabela Cliente. Dessa forma quando a 
aplicação for inserir um pedido de um cliente na tabela Pedido, o SGBD irá checar se 
esse cliente já existe na tabela Cliente, retornando um erro, caso não atenda essa 
condição. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Cód_cliente (PK) 
Nome_cliente 
CGC 
Endereço 
Cód_cliente (PK) / (FK) 
Nro_pedido (PK) 
Data_pedido 
Cliente 
Pedido 
 
7 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Alteração de Tabelas 
 
 
 
 
 
 
 
 
 
 
 
 
 
 Pode-se habilitar e desabilitar as constraints de Foreign Key e de Check com a opção 
CHECK (habilita) e NOCHECK (desabilita). 
 A coluna a ser acrescentada à tabela (ADD), deve ser definida como NULL. 
 Pode-se adicionar uma nova constraint à tabela ou deletar uma já existente. 
 
 
 
 
 
CREATE TABLE cliente 
 ( cod_cliente int Primary Key, 
 nome_cliente varchar(30), 
 CGC varchar(14), 
 Endereco varchar(50)) 
 
 
CREATE TABLE pedido 
 ( cod_cliente int, 
 nro_pedido int, 
 data_pedido smalldatetime, 
 CONSTRAINT pk_pedido PRIMARY KEY (cod_cliente, nro_pedido), 
 CONSTRAINT fk_cliente_pedido FOREIGN KEY (cod_cliente) 
 REFERENCES cliente (cod_cliente)) 
ALTER TABLE nome_tabela 
 {{CHECK | NOCHECK } CONSTRAINT {nome_constraint | ALL } 
 | 
[ ADD 
 {nome_col propriedades_col [constraints_col ] 
 | [[, ] constraint_tabela ]} 
 [, {proxima_col | proxima_constraint_tabela }] ... ] 
 | 
 [DROP CONSTRAINT] 
 nome_constraint [, nome_constraint2 ] ... ] 
 
8 
Exemplos 
 
 
 
 
 
 
 
 
 
 
 
Exclusão de Tabelas 
 
 
 
 
 Não se pode excluir uma tabela se esta tiver alguma outra tabela “filha” ligada à 
ela. 
 Quando se elimina uma tabela, todos os registros contidos nela são apagados e a 
tabela em si com toda sua estrutura é eliminada da Base de Dados. 
 
 
Exemplo: 
 
 
 
 
 
 
ALTER TABLE depto 
 ADD nome_chefia varchar(20) NULL 
 
ALTER TABLE empregado 
 NOCHECK CONSTRAINT fk_depto_emp 
 
ALTER TABLE empregado 
 ADD CONSTRAINT fk_depto_emp FOREIGN KEY (cod_depto) 
 REFERENCES depto (cod_depto) 
 
ALTER TABLE empregado 
 DROP CONSTRAINT fk_depto_emp 
 
 DROP TABLE nome_tabela 
 
 DROP TABLE empregado 
 
9 
 Extraindo Informações das Tabelas 
Para que se consiga consultar os dados armazenados em tabelas, utilizamos o comando 
SELECT. 
 
 
 
 
 
 
 
 SELECT - especifica as colunas 
 FROM - especifica as tabelas 
 WHERE - especifica as linhas 
 Quando se utiliza * na lista de select, seleciona-se todas as colunas da tabela. 
 Não se utilizando a cláusula WHERE, todas as linhas serão selecionadas. 
 
Exemplos 
 
 
 
 
 
 
 
 
 
 
 
 
 
SELECT [ALL | DISTINCT ] select_list 
 FROM nome_tabela [, nome_tabela2] [..., nome_tabela16] 
 [WHERE cláusula ] 
 [GROUP BY cláusula ] 
 [HAVING cláusula ] 
 [ORDER BY cláusula ] 
 [COMPUTE cláusula ] 
 
SELECT * FROM empregado 
 
SELECT nome, idade, salario FROM empregado 
 
SELECT cod_depto, cod_empregado, salario 
 FROM empregado 
 WHERE salario > 1200 
 
10 
Utilizando Literais 
 
 
SELECT nome, 'Código do Departamento: ', cod_depto 
 FROM depto 
 
 
Definindo Cabeçalho de Colunas 
 
Por default, o cabeçalho mostrado no resultado de uma query é o nome da coluna 
definido na criação da tabela. Porém, é possível mudar este cabeçalho, inserindo-o na 
lista do Select. 
 
 
SELECT NOME_EMPREGADO = nome, 
DATA_DE_ADMISSAO = dt_admissao 
 FROM empregado 
 
 
 
 
Operadores Aritméticos 
 
Os operadores aritméticos podem ser utilizados em qualquer coluna numérica (numeric, 
decimal, int, smallint, tinynt, float, real, money ou smallmoney). 
 
Símbolo Operação 
+ Adição 
- Subtração 
/ Divisão 
* Multiplicação 
% Módulo 
 
 O operador de módulo (%) não pode ser executado nos tipos de dados money, 
smallmoney, float ou real. 
 
 
 
SELECT nome, salario, (salario * 1.15) 
 FROM empregado 
 
 
 
 
 
 
11 
Funções de Manipulação de Dados Numéricos: 
 
Função Parametro Resultado 
ABS (numeric_expr) Valor Absoluto 
CEILING (numeric_expr) Maior inteiro mais próximo ou igual ao valor 
especificado 
EXP (float_expr) Valor exponencial do valor especificado 
FLOOR (numeric_expr) Menor inteiro mais próximo ou igual ao valor 
especificado 
LOG (float_expr) Logaritmo do numérico especificado 
LOG10 (float_expr) Logaritmo de base 10 
PI () Valor constante de 3.141592653589793 
ROUND (numeric_expr, lenght) Arredondamento no tamanho especificado 
SQRT (float_expr) Raiz Quadrada 
SIGN (numeric_expr) Positivo, Negativo ou Zero 
 
 
 
Funções de Manipulação de Dados Character: 
 
Função Parâmetro Resultado 
+ (expressão, expressão) Concatena 2 ou mais character, strings ou 
colunas 
LOWER (char_expr) Converte para minúscula 
LTRIM (char_expr) Supressão dos espaços em branco a esquerda 
PATINDEX ('%string%',expressão) Retorna a primeira posição da primeira 
ocorrência da string na expressão definida, ou 
zero se a string não for encontrada 
REPLICATE (char_expr, integer_expr) Repete um expressão char quantas vezes 
especificado 
REVERSE (char_expr) Retorna o inverso do char_expr. 
RIGHT (char_expr, integer_expr) Parte da string começando integer_expr 
characteres da direita 
RTRIM (char_expr) Supressão de espaços em brancos a direita 
SOUNDEX (char_expr) Retorna um codigo de semelhanças fonética 
SPACE (integer_expr) Retorna uma string de repetidos espaços 
SUBSTRING (expression, start, length) Retorna parte de uma string de caracteres. 
UPPER (char_expr) Converte para maiúscula 
 
 
 
 
 
 
 
 
12 
Funções de Manipulação de Datas: 
 
Função Parâmetros Resultados 
DATEADD (datepart, number, date) Soma o numero de dateparts à data 
DATEDIFF (datepart, date1, date2) Número de dateparts entre 2 datas 
DATENAME (datepart, date) Especifica datepart para a data, retornando 
como um valor ASCII (Ex: Setembro) 
GETDATE () Data e hora corrente no formato interno 
 
 
DatePart Abreviação Valor 
year yy 1753 - 9999 
quarter qq1 - 4 
month mm 1 - 12 
day of year dy 1 - 366 
day dd 1 - 31 
week wk 0 - 51 
weekday dw 1 - 7 (1 é Domingo) 
hour hh 0 - 23 
minute mi 0 - 59 
second ss 0 - 59 
millisecond ms 0 - 999 
 
 
 
SELECT nome, dt_admissao, DATEDIFF (month, dt_admissao, GETDATE()) 
 FROM empregado 
 
SELECT nome, dt_admissao FROM empregado 
 WHERE DATENAME (dw, dt_admissao) = "MONDAY" 
 
 
 
Funções do Sistema 
 
Função Parâmetros Resultados 
COL_NAME ('table_name', column_id) Retorna o nome da coluna 
COL_LENGTH ('table_name','column_name) Retorna tamanho da coluna 
DATALENGTH ('expressão') Retorna o tamanho atual de uma 
expressão de qualquer tipo de dado 
DB_ID ('databasename') Retorna ID do banco 
DB_NAME (database_id) Retorna nome do banco 
HOST_NAME () Retorna nome do computador 
USER_ID ('username') Retorna ID do usuário 
USER_NAME (user_id) Retorna nome do usuário 
 
13 
 SELECT tamanho = DATALENGTH (nome), nome 
 FROM empregado 
 
 
 
 
 
CONVERSÃO DE DADOS 
 
A função CONVERT permite converter expressões de um tipo de dado para outro tipo de 
dado. Permite também formatar datas em diferentes estilos. 
 
 
CONVERT (tipo_de_dado [(tamanho)], expressão [, estilo]) 
 
 
 
Estilo (yy) Estilo (yyyy) Padrão Formato da Data 
- 0 ou 100 Default mês dd yyyy hh:mi AM (ou PM) 
1 101 USA mm/dd/yy 
2 102 ANSI yy.mm.dd 
3 103 Ingles/Francês dd/mm/yy 
4 104 Alemão dd.mm.yy 
5 105 Italiano dd-mm-yy 
6 106 - dd mês yy 
7 107 - mês dd. yy 
8 108 - hh:mi:ss 
- 9 ou 109 Default + milisegs mês dd.yyyy hh:mi:ss:ms AM (ou PM) 
10 110 USA mm-dd-yy 
11 111 Japonês yy/mm/dd 
12 112 ISO yymmdd 
- 13 ou 113 Default Europeu + 
milisegs 
dd mês yyyy hh:mi:ss:ms(24h) 
14 114 - hh:mi:ss:ms(24h) 
 
 
 
SELECT CONVERT (char(30), GETDATE (), 102) 
 
Resultado: 1999.03.07 
 
 
 
 
14 
 Condições de Pesquisa 
 
Operadores de Comparação (=,>,<,>=,<=,<>,!=,!<,!>) 
Intervalos (BETWEEN e NOT BETWEEN) 
Listas (IN e NOT IN) 
String de comparação (LIKE e NOT LIKE) 
Valores desconhecidos (IS NULL e IS NOT NULL) 
Combinações (AND, OR) 
Negação (NOT) 
 
Obs: Evitar a utilização de condições de Negação, tal como NOT IN e NOT BETWEEN, 
pois essas condições não são reconhecidas pelo otimizador de query. 
 
 
 
 
 
 
 
SELECT nome, dt_admissao FROM empregado 
 WHERE dt_admissao BETWEEN '1/1/95' AND '12/31/95' 
 
SELECT nome, cod_depto FROM empregado 
 WHERE cod_depto IN (1, 3, 4 ) 
 
SELECT * FROM empregado 
 WHERE nome LIKE '%Jose%' 
 
SELECT nome FROM empregado 
 WHERE salario IS NULL 
 
SELECT * FROM empregado 
 WHERE (nome LIKE 'S%' OR cod_depto = 1) 
 AND (salario > 500.00) 
 
 
 
 
 
 
 
 
 
 
 
 
15 
 
Eliminando Duplicidades: 
 
 
SELECT DISTINCT nome FROM empregado 
 
 
 
 
 
 
Classificando o Resultado: 
 
 Pode-se definir na cláusula ORDER BY o número relativo da coluna ao invés do 
nome da coluna. 
 Por default, a forma ascendente (ASC) é assumida. 
 Pode-se classificar por até 16 colunas. 
 
SELECT nome, cod_depto, salario FROM empregado 
 ORDER BY cod_depto, salario DESC 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
16 
Funções de Agregação: 
 
 Retorna valores resumidos 
 O resultado aparece como novas colunas 
 Utilizado na Lista de Select ou na Cláusula HAVING 
 
AVG Retorna a média aritmética de uma coluna 
COUNT Retorna o total de elementos do grupo 
SUM Retorna o somatório de uma coluna 
MIN Retorna o menor valor de uma coluna 
MAX Retorna o maior valor de uma coluna 
 
 
SELECT COUNT(*) FROM empregado 
 
SELECT SUM (salario) FROM empregado 
 
SELECT AVG (idade) FROM empregado 
 
SELECT MAX (salario) FROM empregado 
 
 
 
 
 
Cláusula GROUP BY: 
 
 Organiza os dados em grupos 
 Geralmente utilizada com funções de agregação 
 Resulta um único valor para cada grupo 
 Cada coluna na lista de Select deve estar na cláusula GROUP BY 
 
 
 
 
Cláusula HAVING: 
 
 Cláusula de restrição de grupo 
 Permite funções de agregação 
 Similar a cláusula WHERE 
 As colunas na cláusula HAVING devem Ter somente um único valor 
 Uma query com HAVING deve Ter um GROUP BY 
 
 
 
 
17 
 
SELECT cod_depto, media_de_salario = AVG ( salario ) 
 FROM empregado 
 GROUP BY cod_depto 
 
SELECT cod_depto, salario_do_depto = SUM ( salario ) 
 FROM empregado 
 GROUP BY cod_depto 
 HAVING SUM ( salario ) > 5000.00 
 
 
 
 
 
COMPUTE e COMPUTE BY 
 
 Para uso com funções de Agregação 
 O COMPUTE gera um Total Geral, enquanto que o COMPUTE BY gera Totais dos 
Grupos 
 Este resultado gerado, produz uma nova linha no resultado da query. 
 É possível obter os Totais por Grupo e o Total Geral, numa mesma query 
 Não é do padrão ANSI 
 
SELECT cod_depto, nome, salario FROM empregado 
 WHERE salario > 500 
 COMPUTE SUM (salario) 
 
SELECT cod_depto, nome, salario FROM empregado 
 ORDER BY cod_depto 
 COMPUTE SUM (salario) BY cod_depto 
 COMPUTE SUM (salario) 
 
 
 
Restrições: 
 
 Não é permitido utilizar DISTINCT com Agrupamentos 
 Deve-se utilizar ORDER BY, se o COMPUTE BY for utilizado 
 As colunas listadas no COMPUTE BY devem ser idênticos ou um sub-conjunto das 
colunas listadas no ORDER BY. 
 
 
 
 
 
18 
 
JOINS: 
 
 Operação que permite o tratamento de duas ou mais tabelas 
 Implementado pela utilização da sentença SELECT, onde na lista de select colocamos 
as colunas de uma ou mais tabelas que desejamos listar, na cláusula FROM definimos 
todas as tabelas envolvidas no Join, e na cláusula WHERE especificamos quais linha 
serão incluidas no resultado. Na cláusula WHERE, um operador de join é utilizado 
entre os componentes que estão fazendo a junção. 
 Pode-se definir até 16 tabelas na cláusula FROM. 
 Normalmente, uma chave primária é comparada a uma chave estrangeira em qualquer 
join 
 O operador utilizado para o join é o sinal de igual (=), que resulta somente nas linhas 
onde o valor da coluna de uma tabela é igual ao valor da coluna de outra tabela. 
 Caso não se defina nenhuma condição de Join (com a cláusula WHERE), é gerado 
um produto Cartesiano entre as tabelas. (Join Irrestrito) 
 
 
Exemplo de Join Irrestrito (Produto Cartesiano entre as Tabelas): 
 
 
 SELECT nome_empreg, nom_depend 
 FROM empregado, dependente 
 
 
No exemplo acima, supondo que a Tabela Empregado tenha 10 linhas, e a Tabela 
Dependente 20 linhas, então o resultado do Join Irrestrito será 10 * 20 = 200 linhas. 
 
 
 
EQUI JOIN 
 
 Especifica uma condição de igualdade entre as tabelas para satisfazer o Join. 
 
 
SELECT d.nome, e.nome FROM depto d, empregado e 
 WHERE d.cod_depto = e.cod_depto 
 
SELECT d.nome, e.nome, dp.nome, dp.idade 
 FROM depto d, empregado e, dependente dp 
 WHERE d.cod_depto = e.cod_depto 
 AND e.cod_empreg = dp.cod_empreg 
 
 
 
 
 
19 
SELF JOIN 
 
 Realiza o join das linhas de uma tabela com outras linhas da mesma tabela 
 Por tratar a mesma tabela, deve-se definir aliases distintos para essas tabelas 
 
SELECT au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname 
 FROM authors au1, authors au2 
 WHERE au1.city = 'Oakland' 
 AND au1.state = 'CA' 
 AND au1.zip = au2.zip 
 AND au1.au_id < au2.au_idOUTER JOIN 
 
 Mostra todas as linhas do join, inclusive aquelas que não encontram linhas 
correspondentes na tabela relacionada. 
 Só se pode fazer um outer join entre duas tabelas. 
 
 
 
Operadores do Outer Join: 
 
*= Inclui todas as linhas da primeira tabela especificada nas restrições da sentença 
=* Inclui todas as linhas da Segunda tabela especificada nas restrições da sentença 
 
SELECT titles.title_id, title, qty 
FROM titles, sales 
WHERE titles.title_id *= sales.title_id 
 
 
 
 
 
 
 
 
 
 
 
 
20 
SUBQUERIES: 
 
 São sentenças SELECT aninhadas 
 Muitas sentenças SQL que incluem subqueries podem ser formuladas de modo 
alternativo através de Joins. Outras queries são resolvidas somente com subqueries. 
 A sentença SELECT de uma subquery é sempre colocado dentro de parênteses. 
 Uma subquery pode estar inserida na lista de SELECT, numa cláusula WHERE ou 
HAVING da query externa. 
 Em aninhamentos normais de subqueries, as mais internas são executadas primeiro. 
 
 
 
 SELECT cod_depto, nome FROM depto 
 WHERE cod_depto IN ( SELECT DISTINCT cod_depto FROM empregado) 
 
 
 SELECT Título = title_id, Quantidade = qty, 
 Total = ( SELECT SUM(qty) FROM sales ), 
 Porcentagem_do_Total = 
( CONVERT (float, qty) / ( SELECT SUM(qty) FROM sales )) * 100 
 FROM sales 
 
 
 
 
SUBQUERIES CORRELATAS: 
 
 São aquelas subqueries que referenciam na cláusula WHERE, uma tabela da cláusula 
FROM da query externa. Neste caso, a subquery é executada para cada linha da 
tabela referenciada na query externa. 
 
 
SELECT title_id, au_id, royaltyper 
 FROM titleauthor ta 
 WHERE royaltyper = (SELECT MAX(royaltyper) 
 FROM titleauthor tb 
 WHERE tb.title_id = ta.title_id) 
 
 
 
 
 
 
 
 
 
 
21 
SELECT INTO 
 
 Cria uma nova tabela baseada no resultado de um SELECT 
 Se a nova tabela for uma tabela permanente, a opção select into/bulkcopy deve ser 
assinalada na configuração do banco. Se a nova tabela for uma tabela temporária, o 
nome desta deve ser precedido de um # ou ## 
 As colunas na lista de select devem Ter apelidos (alias) 
 Denota-se a nova tabela como sendo temporária, colocando um sinal de escopo (#) 
como primeiro caracter do nome da tabela. 
 
 
SELECT pnome = SUBSTRING (nome, 1, 10) , 
 Sal_anual = salario * 12 
 INTO #novatabela 
 FROM empregado 
 
 
 
Operador UNION 
 
 Permite unir os resultados de duas ou mais queries num único resultado. 
 O UNION resulta num incremento de linhas. (Um JOIN resulta num incremento de 
colunas). 
 Na união, as estruturas das colunas tem de ser as mesmas, e os tipos de dados 
compatíveis. 
 Por default, as linhas duplicadas são removidas do resultado final. Se desejar que 
elas permaneçam, utilizar a opção ALL. 
 As listas de colunas dos Selects devem Ter o mesmo número de colunas, tipos de 
dados similares, e aparecerem na mesma ordem. 
 Os nomes das colunas são obtidos da primeira sentença SELECT. 
 
 
SELECT lista_de_select FROM ... 
UNION [ ALL ] 
SELECT lista_de_select FROM ... 
 
 
 
 
 
 
 
22 
Inserindo LINHAS: 
 
 O comando INSERT é utilizado para adicionar linhas a uma tabela 
 Utilize uma sentença INSERT para cada linha a ser adicionada 
 A ordem e o tipo de dado dos item a serem inseridos devem corresponder a ordem e 
tipo de dado das colunas da tabela 
 Os itens de dados na cláusula VALUES deve corresponder a Lista dos nomes de 
coluna 
 
 
 INSERT [ INTO ] 
 { nome_tabela | nome_view } [ (lista_de_colunas) ] 
 { VALUES ( lista_de_valores ) | sentença_select } 
 
 
 
 
 
INSERT INTO depto 
 VALUES (1, 'CONTABILIDADE') 
 
INSERT INTO empregado (nro_empregado, nome, cod_depto) 
 VALUES (7000, 'Jose Luiz de Oliveira', 1) 
 
 
 
Inserindo linhas com SELECT 
 
 Insere linhas resultantes de uma Query em uma tabela que já existe. 
 
 INSERT INTO nome_tabela 
 SELECT lista_de_colunas 
 FROM lista_de_tabelas 
 [ WHERE condições_de_pesquisa ] 
 
 
 
 INSERT INTO empreg_salarioanual 
 SELECT nome_empreg , (salario * 12 ) 
 FROM empregado 
 WHERE idade > 21 
 
 
 
 
 
 
23 
Alterando dados da Linha: 
 
 O comando UPDATE altera os dados de linhas de uma tabela 
 A cláusula SET especifica a coluna e o valor a ser alterado 
 A cláusula WHERE identifica exatamente as linhas a serem alteradas 
 Se o UPDATE viola alguma constraint de integridade, a alteração não será executada 
 
 
 UPDATE { nome_tabela | nome_view } 
 SET nome_coluna = expressão 
 [ , nome_coluna = expressão , ... ] 
 [ cláusula WHERE ] 
 
 
 
 
UPDATE empregado 
 SET salario = salario * 1.05 
 
UPDATE empregado 
 SET cod_depto = 5 
 WHERE cod_depto = 2 
 
 
 
Alteração com base em dados de outras tabelas: 
 
 
 UPDATE titles 
 SET ytd_sales = ( SELECT SUM (qty) 
 FROM sales 
 WHERE sales.title_id = titles.title_id 
 AND ord_date BETWEEN '01/01/95' 
 AND '12/31/95' ) 
 
 
 
 
 
 
 
 
 
 
 
 
 
24 
Deletando Linhas: 
 
 O comando DELETE remove uma ou mais linhas de uma tabela 
 A cláusula WHERE especifia os critérios pelos quais as linhas serão removidas 
 Se A cláusula WHERE não é especificada, todas as linhas da tabela serão removidas. 
 
 DELETE [ FROM ] nome_tabela 
 [ WHERE condições_de_pesquisa ] 
 
 
 
 
DELETE empregado 
WHERE DATEDIFF (YEAR, dt_admissao, GETDATE()) >= 5 
 
 
 
Os dois exemplos a seguir realizam a mesma coisa. Deletam todas as linhas da tabela 
titleauthor que tenha no título do livro a string "computers". 
 
/* subquery padrão ANSI */ 
 
DELETE FROM titleauthor 
 WHERE au_id IN 
 ( SELECT a.au_id 
 FROM authors a, titles t, titleauthor ta 
 WHERE a.au_id = ta.au_id 
 AND ta.title_id = t.title_id 
 AND t.title LIKE '%computers%' ) 
 
/* extensão Transact-SQL */ 
 
 DELETE FROM titleauthor 
 FROM authors a, titles t 
 WHERE a.au_id = titleauthor.au_id 
 AND t.title_id = titleauthor.title_id 
 AND t.title LIKE '%computers%' 
 
 
 
 
 
 
 
 
 
 
25 
ÍNDICES: 
 
 O principal objetivo da utilização de índices, é para aumentar a performane nas 
pesquisas 
 Melhora a performance na resolução de ORDER BY e GROUP BY 
 O próprio banco cria um índice para cada chave primária definida na tabela 
 As modificações (UPDATE) tendem a se tornar mais demorados quando da utilização 
de índices 
 Evitar definir índices para colunas de tipo text, image ou bit 
 Evitar definir índices quando a performance do UPDATE é mais importante que a 
performance do SELECT 
 A cláusula UNIQUE especifica que a coluna ou conjunto de colunas que compõem o 
índice não podem Ter valores duplicado na tabela. 
 
 
CREATE UNIQUE INDEX i1_empregado 
 ON empregado (nome) 
 
 
 
Tipos de Índices: 
 
 Clusterizado 
 Não Clusterizado 
 
 
Indices Clusterizados: 
 As linhas são classificadas e se mantém ordenadas 
 Pode haver somente um índice clusterizado por tabela 
 O índice clusterizado deve sempre ser o primeiro índice a ser criado. 
 Grande vantagem quando se deseja consultar enormes intervalos. 
 Não definindo que o índice é clusterizado, será criado um índice não clusterizado. 
 
Índices Não Clusterizados: 
 Classificação fisica das linhas nãoé a mesma da classificação nos índices. 
 Pode-se Ter até 249 indíces não clusterizados por tabela. Cada um pode prover acesso 
aos dados em diferentes ordens. 
 
 
 
CREATE CLUSTERED INDEX idc_empregado 
 ON empregado (nome) 
 
 
 
 
26 
INTEGRIDADE DE DADOS 
 
A Integridade de Dados fala da consistência de dados armazenadas em um banco de 
dados, facilitando para as aplicações que dela se utilizam. 
 
Propriedade IDENTITY 
 
 Os valores para as colunas definidas como IDENTITY são gerados pelo sistema de 
forma sequencial. 
 Permitido somente uma única coluna IDENTITY por tabela 
 Não pode ser alterada por um comando UPDATE 
 Definidos somente para tipos de dados: tinyint, smallint, int, numeric, decimal 
 
 
IDENTITY [(valor_inicial, incremento)] 
 
 
 
CREATE TABLE aluno 
 ( aluno_id INT IDENTITY (100, 5), 
 nome VARCHAR(20) ) 
 
INSERT aluno VALUES ('Mirtes') // Será criado com aluno_id = 100 
INSERT aluno VALUES ('Wagner') // Será criado com aluno_id = 105 
 
 
 
 
 
DEFAULTS 
 Criado antes ou após a criação da tabela 
 Nomes devem ser únicos dentro do Banco 
 São checados no INSERT 
 O tipo de dados deve ser o mesmo definido para a coluna da tabela associada 
 Deve ser compatível com as regras (rules) definidas para a coluna 
 
 
CREATE DEFAULT def_uf 
 AS 'SP' 
 
 
 
 
 
 
 
27 
REGRAS (RULES) 
 Criado antes ou após a criação da tabela 
 Nomes devem ser únicos dentro do Banco 
 São checados no INSERT e UPDATE 
 O nome do parâmetro deve ser prefixado com @ 
 
 
 CREATE RULE rl_desconto 
 AS @desc = 10 
 OR @desc < 5 
 
 
 
Tanto os DEFAULTS quanto as RULES devem ser associadas (ligadas) a uma coluna 
da tabela. 
 
EXEC sp_bindefault def_uf, „empregado.uf‟ 
 
EXEC sp_bindrule rl_desconto, „duplicata.desc‟ 
 
 
 
EXEC sp_unbindefault „empregado.uf‟ 
 
EXEC sp_unbindrule „duplicata.desc‟ 
 
 
DROP DEFAULT def_uf 
 
DROP RULE rl_desconto 
 
 
 
CONSTRAINTS 
 
 Pode ser colocado em uma coluna ou uma tabela 
 Pode ser adicionada a uma tabela que já contenha dados 
 Caso não se coloque um nome para a constraint, o sistema cria um com o seu padrão 
 
Tipos de Constraints: 
 PRIMARY KEY 
 UNIQUE 
 FOREIGN KEY 
 DEFAULT 
 CHECK 
 
 
28 
 
ALTER TABLE empregado 
 ADD CONSTRAINT fk_cod_depto FOREIGN KEY (cod_depto) 
 REFERENCES depto (cod_depto) 
 
 
ALTER TABLE empregado 
 ADD CONSTRAINT uq_nome 
 UNIQUE NONCLUSTERED (nome) 
 
ALTER TABLE empregado 
 ADD CONSTRAINT df_uf DEFAULT 'SP' FOR uf 
 
ALTER TABLE empregado 
 ADD CONSTRAINT ck_cep 
 CHECK (cep LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
29 
VIEWS (VISÕES) 
 
 Forma alternativa de visualizar os dados de uma ou mais tabelas do banco de dados 
 Uma query armazenada permanentemente no banco de dados 
 Como uma tabela, porém fisicamente a view não existe 
 Ideal para se criar visões específicas de determinadas colunas de tabelas para grupos 
de usuários 
 Auxilia na construção de queries complexas. 
 Views não podem ser alteradas. Deve-se deletar e recriá-la novamente. 
 
 
 
CREATE VIEW vw_empregado 
AS SELECT d.nome nome_depto, e.nome nome_empreg, salario 
 FROM depto d, empregado e 
 WHERE d.cod_depto = e.cod_depto 
 AND idade > 18 
 
 
 
DROP VIEW vw_empregado 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
30 
STORED PROCEDURES 
 
Stored procedures são sentenças SQL pré-compiladas que ficam armazenadas no 
servidor. 
 As sentenças são checadas quanto a sintaxe e compiladas na primeira vez que é 
executado 
 A versão compilada é armazenada numa cache de procedure e recuperada a partir 
desta nas chamadas subsequentes, tornando a execução mais rápida e mais eficiente. 
 Grande vantagem quando se tem tarefas repetitivas a serem executadas. 
 
Passos para criação de uma procedure simples: 
1) Escreva a transação SQL 
2) Teste a transação SQL 
3) Se o resultado é o esperado, crie a procedure 
4) Execute a procedure 
 
CREATE PROC cont_empregados 
 AS 
 SELECT count(*) FROM empregado 
 
EXEC cont_empregados 
 
 
 
CURSORES 
 
 
 
Sentença Descrição 
DECLARE Cria e define um cursor 
OPEN Abre um cursor declarado 
FETCH Retira uma linha do cursor 
CLOSE Fecha o cursor 
DEALLOCATE Remove o cursor 
 
 
 
 
 
 
 
 
 
 
 
 
 
31 
 
 
 
DECLARE cur_empregado CURSOR 
FOR 
 SELECT nro_empregado, salario FROM empregado 
 
DECLARE @nro int 
DECLARE @salario numeric(10,2) 
 
OPEN cur_empregado 
FETCH cur_empregado INTO @nro, @salario 
WHILE (@@fetch_status = 0) 
 BEGIN 
 IF (@salario > 1000) 
 INSERT INTO novatab VALUES (@nro, @salario) 
 
 FETCH cur_empregado INTO @nro, @salario 
 
 END 
 
CLOSE cur_empregado 
DEALLOCATE cur_empregado 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
32 
 
 
TRIGGERS 
 
 Tipo especial de Stored Procedure que é executado quando se modifica, inclui ou 
exclui dados de uma determinada tabela 
 Seu lançamento é automático 
 Triggers podem disparar mudanças em cascata nas tabelas relacionadas no Banco. 
 
O exemplo seguinte mostra uma trigger de insert, que altera a coluna ytd_sales na tabela 
titles toda vez que uma nova linha na tabela sales é inserida. 
 
 
CREATE TRIGGER intrig 
ON sales 
FOR INSERT AS 
 /* checa o valor de @@rowcount */ 
IF @@rowcount = 1 
 UPDATE titles 
 SET ytd_sales = ytd_sales + qty 
 FROM inserted 
 WHERE titles.title_id = inserted.title_id 
ELSE 
 /* quando rowcount é maior que 1, 
 **use a clausula GROUP BY */ 
 UPDATE titles 
 SET ytd_sales = ytd_sales + (SELECT SUM(qty) 
 FROM inserted 
 GROUP BY inserted.title_id 
 HAVING titles.title_id = inserted.title_id)