08 Structured Query Language

Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original

*
Introdução
Linguagem padrão para os bancos de dados relacionais;
Presente praticamente em todos os SGBD´s Relacionais;
Fácil migração de dados de um SGBD para outro que usa este mesmo modelo;
Uma das grandes responsáveis pela grande aceitação dos SGBDR´s.
*
*
Introdução
A linguagem foi desenvolvida inicialmente pela IBM para ser usada no SGBD System R;
Chamada inicialmente de SEQUEL (Structured English Query Language);
Depois, a ANSI e a ISO começaram a revisar estas linguagens e chegaram às novas versões;
SQL-86;
SQL-92 (SQL-2);
SQL-99 (SQL-3); 
SQL-2003;
SQL-2008 (atual)
*
*
Introdução
A partir da versão SQL-99, a linguagem SQL foi dividida em duas partes:
Núcleo;
Principais elementos da linguagem;
Operadores para definição e manipulação de dados, visões, etc;
Implementado em todo SGBDR;
Pacotes adicionais;
Utilizados para aplicações específicas de BD;
Data warehouse, Data Minning, dados multimídia, dados espaciais, etc; 
*
*
Introdução
O núcleo da linguagem SQL é dividido em várias partes;
DDL;
Criação, alteração e remoção de tabelas e índices;
DML;
Inserção, consulta, alteração e modificação de tuplas;
Manipulação de dados embutida;
Acesso ao BD através de linguagens de programação;
Definição de visões;
Definição de visões a partir das tabelas criadas;
*
*
Introdução
DML - Linguagem de Manipulação de Dados
Os elementos da DML (Data Manipulation Language):
A DML é um subconjunto da linguagem SQL usada para inserir, atualizar e apagar dados.
Operações com tuplas (dados)
INSERT é usada para inserir um registro (formalmente uma tupla) a uma tabela existente.
UPDATE para mudar os valores de dados em uma ou mais linhas da tabela existente.
DELETE permite remover linhas existentes de uma tabela.
*
*
Introdução
DDL - Linguagem de Definição de Dados
DDL (Data Definition Language - Linguagem de Definição de Dados). Uma DDL permite ao utilizador definir tabelas novas e elementos associados.
Operações de definição de dados
CREATE cria um objeto dentro da base de dados.
CREATE TABLE – cria uma nova tabela no banco de dados;
CREATE INDEX – cria uma novo índice no banco de dados;
CREATE VIEW – cria uma nova visão no banco de dados;
DROP apaga um objeto do banco de dados.
DROP TABLE – apaga uma tabela do banco de dados;
DROP INDEX – apaga um índice do banco de dados;
DROP VIEW – apaga uma visão do banco de dados;
ALTER altera um objeto do banco de dados.
ALTER TABLE – altera as definições de uma tabela do banco de dados;
ALTER INDEX – altera as definições de um índice do banco de dados;
ALTER VIEW – altera a uma visão do banco de dados;
*
*
Introdução
O núcleo da linguagem SQL é dividido em várias partes;
Definição de autorização;
Especificação dos direitos de acessos às tabelas e visões do banco de dados;
Definição de restrições de integridade;
Asserções e gatilhos;
Controle transacional;
*
*
Tipos de dados SQL
Tipos Numéricos:
INT ou INTEGER;
Números inteiros;
SMALLINT;
Números inteiros pequenos;
REAL;
Números reais com precisão simples;
*
*
Tipos de dados SQL
Tipos Numéricos:
DOUBLE PRECISION;
Número real com precisão dupla;
FLOAT(n);
Ponto flutuante com precisão definida pelo usuário;
NUMERIC (p,d);
Ponto flutuante onde p é a precisão (total de dígitos decimais) e d é a escala (número de casas depois do ponto);
*
*
Tipos de dados SQL
Cadeias de caracteres:
CHAR(n) ou CHARACTER(N);
Cadeia de caracteres de tamanho fixo;
Caracteres brancos são adicionados à direita caso o número de caracteres não seja preenchido;
 VARCHAR(n);
Cadeia de caracteres com tamanho variável, onde n é o comprimento máximo da cadeia;
CHARACTER LARGE OBJECT (CLOB);
Usado para armazenar strings grandes, como textos e documentos;
*
*
Tipos de dados SQL
Cadeias de bits:
BIT(n);
Conjunto de bits de tamanho fixo;
BIT VARYING(n);
Conjunto de bits de tamanho variável, onde n é o tamanho máximo;
BINARY LARGE OBJECT (BLOB);
Grandes quantidades de bits, usado para armazenar vídeos, fotos, mapas, etc;
*
*
Tipos de dados SQL
Tipos Booleanos:
BOOLEAN:
Valores booleanos;
EM SQL, ele pode assumir os valores true, false ou unknow;
Data e Hora:
DATE:
Especifica uma data com o formato YYYY-MM-DD;
TIME:
Especifica uma hora com o formato HH:MM:SS;
*
*
Manipulação de Tabelas
SQL define três comandos para a definição de dados:
CREATE (criação);
ALTER (alteração);
DROP (exclusão);
*
*
Manipulação de Tabelas
Criando uma nova tabela:
Usamos o comando CREATE TABLE;
Definimos um nome para a tabela;
Definimos depois a lista de atributos, cada qual com seu respectivo tipo;
Por fim, definimos as restrições;
Chave primária, chave estrangeira, etc;
*
*
Linguagem de definição de dados
Criando uma nova tabela:
A definição de um atributo tem a seguinte sintaxe:
Nome tipo [NOT NULL] [UNIQUE] [DEFAULT valor]
Onde:
Nome: corresponde ao nome do atributo;
Tipo: corresponde ao tipo de dado deste atributo (Tipo SQL);
NOT NULL: restrição que indica que o valor do atributo não pode ser nulo; 
UNIQUE: restrição que indica que o valor do atributo é único; 
Usada para chaves secundárias;
DEFAULT: usado para definir um valor inicial para o atributo caso o valor não seja informado;
*
*
Manipulação de Tabelas
Criando uma nova tabela:
Podemos especificar as seguintes restrições:
Chave Primária:
PRIMARY KEY (atributos_chaves);
Chave Estrangeira:
FOREIGN KEY (atributo) REFERENCES tabela_base(atributo);
Restrição de integridade:
CHECK (condição);
*
*
Manipulação de Tabelas
Criando uma nova tabela:
Exemplo: Criar uma tabela para cada esquema de relação:
Fornecedor (Codigo, Nome, Cidade);
Peça(CodPeca, Nome, Descricao)
Venda(CodFornecedor, CodPeca, Quantidade, Data); 		
*
*
Manipulação de Tabelas
Criando uma nova tabela: 
Exemplo:
CREATE TABLE Fornecedor(
 Codigo INT,
 Nome VARCHAR(20) NOT NULL,
 Cidade VARCHAR(20) NOT NULL,
 PRIMARY KEY (Codigo)
 )
*
*
Manipulação de Tabelas
Criando uma nova tabela: 
Exemplo:
CREATE TABLE Peca (
 CodPeca INT,
 Nome VARCHAR(30) NOT NULL,
 Descricao VARCHAR(50) NOT NULL,
 PRIMARY KEY (CodPeca)
)
*
*
Manipulação de Tabelas
Criando uma nova tabela: 
Exemplo:
CREATE TABLE Venda(
 CodFornecedor INT,
 CodPeca INT,
 Quantidade INT DEFAULT 1,
 Data DATE,
 PRIMARY KEY (CodFornecedor,CodPeca),
 FOREIGN KEY (CodFornecedor) REFERENCES Fornecedor(Codigo), 
 FOREIGN KEY (CodPeca) REFERENCES Peca(CodPeca)
 )
*
*
Manipulação de Tabelas
Definindo chaves estrangeiras:
Como já vimos, usamos a restrição “FOREIGN KEY” é usada para a definição de uma chave estrangeira;
Uma restrição de integridade referencial é gerada;
Podemos especificar o que o SGBD deve fazer para manter a integridade referencial dos dados:
Numa atualização (através da restrição ON UPDATE);
Numa exclusão (através da restrição ON DELETE);
*
*
Manipulação de Tabelas
Definindo chaves estrangeiras:
Para cada uma destas restrições, temos quatro valores possíveis;
Restrict:
A atualização ou remoção é rejeitada;
Cascade:
 A atualização ou exclusão é propagada para as tuplas relacionadas; 
Set Null:
As tuplas relacionadas com a tupla a ser atualizada ou removida tem o valor do atributo chave estrangeira correspondente alterado para Null;
Set Default:
As tuplas relacionadas com a tupla a ser atualizada ou removida tem o valor do atributo chave estrangeira correspondente alterado para um valor default;
*
*
Manipulação de Tabelas
Definindo chaves estrangeiras:
Vamos recriar uma das tabelas de nosso exemplo;
CREATE TABLE Venda(
 CodFornecedor INT,
CodPeca INT,
 Quantidade INT DEFAULT 1,
 Data DATE,
 PRIMARY KEY (CodFornecedor,CodPeca),
 
 FOREIGN KEY (CodFornecedor) REFERENCES Fornecedor(Codigo) ON DELETE 	 RESTRICT ON UPDATE CASCADE ,
 
 FOREIGN KEY (CodPeca) REFERENCES Peca(CodPeca) ON DELETE RESTRICT 	 ON UPDATE CASCADE
 )
*
*
Manipulação de Tabelas
Nomeando as restrições:
Podemos nomear cada restrição definida em uma tabela;
Isto permite remover uma determinada restrição depois que a tabela é criada;
Fazemos isso usando a palavra “CONSTRAINT” antes da restrição, seguido do nome dado para a mesma; 
*
*
Manipulação de Tabelas
Nomeando as restrições:
Exemplo:
CREATE TABLE Venda(
 CodFornecedor INT,
 CodPeca INT,
 Quantidade INT DEFAULT 1,
 Data DATE,
 PRIMARY KEY (CodFornecedor,CodPeca),
 CONSTRAINT Referencia_CodFornecedor
 FOREIGN KEY (CodFornecedor) REFERENCES Fornecedor(Codigo) ON DELETE 	 RESTRICT ON UPDATE CASCADE ,
 CONSTRAINT Referencia_CodPeca
 FOREIGN KEY (CodPeca) REFERENCES Peca(CodPeca) ON DELETE RESTRICT 	 ON UPDATE CASCADE
 )
*
*
Manipulação de Tabelas
Alterando uma Tabela:
Podemos alterar uma tabela usando o comando ALTER TABLE;
Ao alterar uma tabela, podemos:
Adicionar ou eliminar uma coluna;
Alterar a definição de uma coluna;
Adicionar ou eliminar restrições;
*
*
Manipulação de Tabelas
Alterando uma Tabela:
Adicionando ou removendo colunas:
Podemos adicionar uma coluna através da cláusula ADD:
Se a tabela já foi povoada, todas as tuplas recebem um valor nulo para o novo atributo;
A restrição NOT NULL não pode ser aplicada neste tipo de atributo;
Exemplo: Adicione na tabela Empregado um novo atributo chamado CPF.
ALTER TABLE Empregado ADD Cpf VARCHAR(14)
*
*
Manipulação de Tabelas
Alterando uma Tabela:
Adicionando ou removendo colunas:
Podemos remover uma coluna através da cláusula DROP;
Podemos usar as opções CASCADE ou RESTRICT para tratar as restrições e visões que referenciam o atributo a ser excluído;
Exemplo: Remover o atributo Salario de todos os Empregados
ALTER TABLE Empregado DROP Salario CASCADE;
*
*
Manipulação de Tabelas
Alterando uma Tabela:
Alterando a definição de uma coluna:
Podemos alterar a definição de uma tabela usando as cláusulas ALTER e SET;
Exemplo: Alterar o valor padrão do atributo Quantidade para 2.
ALTER TABLE Venda ALTER Quantidade SET DEFAULT 2 
*
*
Manipulação de Tabelas
Alterando uma Tabela:
Adicionando e removendo restrições:
Podemos adicionar uma restrição através da cláusula ADD CONSTRAINT;
Exemplo: Crie uma restrição para o atributo Quantidade, onde os seus valores não sejam menores do que 0.
ALTER TABLE Venda ADD CONSTRAINT QuantPositiva CHECK (Quantidade>=0)
*
*
Manipulação de Tabelas
Alterando uma Tabela:
Adicionando e removendo restrições:
Podemos remover uma restrição através da cláusula DROP CONSTRAINT;
Para ser removida, uma restrição deve ter sido nomeada;
Exemplo: Remova a restrição criada para o atributo Quantidade da tabela Venda.
 
ALTER TABLE Venda DROP CONSTRAINT QuantPositiva
*
*
Manipulação de Tabelas
Removendo uma Tabela:
Podemos remover uma tabela através do operador DROP;
Podemos usar as opções CASCADE ou RESTRICT para tratar as tabelas e visões que referenciam a tabela a ser excluída;
Exemplo: Remover a tabela Venda da base de dados juntamente com as suas referências.
	DROP TABLE Venda CASCADE
*
*
Manipulação de Tabelas
Todos e Todas.....
Até aqui vimos apenas alguns comando de DDL, 
ou seja, apenas estamos definindo o esquema das tabelas do banco de dados
Observe que ainda NÃO 
manipulamos os dados (tuplas) das tabelas!
Já sabem como criar, alterar e excluir uma tabela?
*
*
Inserções
Usamos o comando INSERT para fazer inserções de tuplas através da linguagem SQL;
Sintaxe:
INSERT INTO Tabela VALUES (valor1, valor2, ...,valorN)
 A ordem dos valores deve ser a mesma ordem em que os atributos foram definidos na tabela;
O SGBD se encarrega de verificar se a tupla a ser inserida satisfaz todas as restrições definidas para a tabela;
Caso ela não satisfaça alguma restrição, a inserção é rejeitada;
*
*
Inserções
Exemplo: Inserir todos os dados do empregado Janderson, com matrícula 1111-5, salário R$ 2000,00, supervisionado por Aluísio (matrícula 1111-4) e que trabalha no departamento 1;
	INSERT INTO Empregado VALUES ('1111-5', 'Janderson', 2000, '1111-4', 1 ) 
*
*
Inserções
Podemos alterar a ordem dos atributos, desde que façamos a descrição da ordem considerada na hora da consulta;
Atributos não especificados recebem valores default (caso tenham sido especificados na criação da tabela) ou nulos; 
Exemplo:
INSERT INTO Empregado (Nome, Salario, Matricula, Cod_Departamento, Supervisor)
VALUES ('Doquinha', 3000, '1111-5', 1, '1111-4' ) 
*
*
Inserções
Podemos inserir em uma tabela as tuplas recuperadas no resultado de uma consulta, desde que elas sejam compatíveis com os atributos da tabela onde a inserção será feita; 
Exemplo:
Seja a seguinte tabela:
 CREATE TABLE FuncionarioExemplar(
 Matricula VARCHAR(10),
	 Nome VARCHAR(50),
 PRIMARY KEY (Matricula)
 )
*
*
Inserções
Exemplo (Continuação): Inserir na tabela Funcionário Exemplar a matrícula e o nome dos empregado que trabalham em mais de 2 projetos
Podemos inserir dados da seguinte forma:
INSERT INTO FuncionarioExemplar 
SELECT Matricula,Nome 
FROM Empregado E, Trabalha_Projeto TP
WHERE E.Matricula=TP.Empregado
GROUP BY Matricula,Nome
HAVING COUNT(*)>=2
*
*
Exclusões
Usamos o comando DELETE para excluir tuplas de uma tabela;
As tuplas são excluídas através de um critério de remoção;
Sintaxe:
	DELETE FROM Tabela WHERE condição
A exclusão em uma tabela pode ser propagada para outras tabelas ou pode ser rejeitada;
Integridade referencial;
*
*
Exclusões
Exemplo: Excluir na tabela TrabalhaProjeto todas as tuplas que referenciam o projeto 2;
Remoção não viola a integridade referencial, pode ser feita normalmente;
DELETE FROM Trabalha_Projeto WHERE CodProjeto=2
*
*
Exclusões
Exemplo: Excluir o departamento 2:
Remoção viola a integridade referencial;
Pode ser realizada ou não, dependendo do SGBD e da definição da tabela;
DELETE FROM Departamento WHERE Cod_Departamento=2
*
*
Exclusões
Podemos omitir a cláusula WHERE para removermos todas as tuplas de uma tabela;
Neste caso, as tuplas são removidas, mas a tabela continua existindo;
Exemplo: Excluir todas as tuplas da tabela TrabalhaProjeto:
DELETE FROM Trabalha_Projeto
*
*
Atualizações
O comando UPDATE é utilizado para fazer atualizações;
Permite a atualização de uma ou mais tuplas de uma tabela;
Ou de outras tabelas, devido à integridade referencial;
Podemos selecionar as tuplas a serem alteradas através de um critério de seleção;
Sintaxe:
UPDATE Tabela SET atr1=vl1, atr2=vl2, ..., atrN=vlN
WHERE condição
*
*
Atualizações
A cláusula WHERE pode ser omitida para atualizarmos todas as tuplas de uma tabela;
Exemplo: Reajuste em 10 % o salário de todos os funcionários que ganham menos de R$ 3000,00;
	UPDATE Empregado SET Salario=Salario * 1.1
	WHERE Salario<3000
*
*
Consultas
As consultas são descritas na linguagem SQL através da operação SELECT;
Geralmente, uma consulta tem a forma:
SELECT <lista_de_atributos>
FROM <lista_de_tabelas>
WHERE <condição>
 
*
*
Consultas
Onde:
<lista_de_atributos> corresponde às colunas que devem ser selecionadas das tabelas;
Corresponde à operação de projeção da álgebra relacional;
<lista_de_tabelas> corresponde às tabelas
que serão usadas para realizar a consulta;
<condição> é uma expressão booleana que indica o critério de seleção das tuplas;
Pode ser omitida, quando quisermos recuperar todas as tuplas de uma tabela;
Os operadores relacionais =, <, <=, >,>= e <> podem ser usados para expressar as condições;
Os conectores lógicos AND, OR e NOT podem ser usados para especificar condições compostas;
*
*
Um estudo de caso
Vamos considerar a seguinte relação Empregado:
*
*
Um estudo de caso
Vamos considerar a seguinte relação Departamento:
*
*
Um estudo de caso
Vamos considerar a seguinte relação Projeto:
*
*
Um estudo de caso
Vamos considerar a seguinte relação Dependente:
*
*
Um estudo de caso
Vamos considerar a seguinte relação Trabalha_Projeto:
*
*
EMPREGADO
DEPENDENTE
DEPARTAMENTO
TRABALHA_PROJETO
PROJETO
*
*
Consultas
Exemplo 1: Selecionar a matrícula e o nome de todos os empregados que trabalham no departamento 2;
SELECT Matricula, Nome FROM Empregado 
WHERE Cod_Departamento =2;
Resultado:
*
*
Consultas
Exemplo 2: Selecionar a matrícula e o nome dos empregados do departamento 2 que ganham mais de três mil reais;
	
	SELECT Matricula, Nome FROM Empregado
 	WHERE Cod_Departamento=2 AND Salario>3000;
	Resultado: 
	
*
*
Consultas
Podemos omitir a condição para selecionar todas as tuplas de uma tabela;
Exemplo 3: Selecionar a matrícula e o nome de todos os empregados;
	 
 SELECT Matricula, Nome FROM Empregado;
Resultado:
*
*
Consultas
Podemos também selecionar todas as colunas de uma tabela usando o valor *;
Exemplo 4: Selecionar as informações sobre os empregados do departamento dois;
SELECT * FROM Empregado WHERE Cod_Departamento=2;
Resultado:
*
*
Consultas
Podemos alterar o nome de uma ou mais colunas através da cláusula AS:
Exemplo 5: Selecionar a matricula e o nome de todos os empregados, renomeando somente atributo nome para Nome_Empregado;
	 
 SELECT Matricula, Nome AS Nome_Empregado FROM Empregado;
	
	 Resultado:
*
*
Consultas
Podemos aplicar cálculos aos valores de uma coluna:
Exemplo 6: Calcule como ficaria o novo salário de cada empregado caso eles recebessem um aumento de 15%, recuperando a sua matricula, nome e o novo salário reajustado.
SELECT Matricula, Nome, 1.15 * Salario AS Salario_Reajustado 
FROM Empregado;
	Resultado:
*
*
Consultas
Podemos realizar junções:
Exemplo 7: Selecione o nome dos empregados que trabalham no departamento de Vendas;
 SELECT Empregado.Nome FROM Empregado, Departamento 
 WHERE Empregado.Cod_Departamento = Departamento.Cod_Departamento AND Departamento.Nome ='Vendas'
Resultado:
*
*
Consultas
Podemos usar aliases para tabelas para facilitar a consulta e eliminar ambigüidades;
Exemplo 8: Selecione o nome dos empregados que trabalham no departamento de Vendas;
SELECT E.Nome FROM Empregado AS E, Departamento AS D 
 WHERE E.Cod_Departamento = D.Cod_Departamento AND D.Nome ='Vendas'
Resultado:
*
*
Consultas
Podemos também omitir a cláusula AS ao renomear tabelas e atributos;
Exemplo 9: Selecione o nome dos empregados que trabalham no departamento de Vendas;
SELECT E.Nome empregados_venda FROM Empregado E, Departamento D 
 WHERE E.Cod_Departamento = D.Cod_Departamento AND 
		 D.Nome ='Vendas'
Resultado:
*
*
Consultas
Todos e Todas.....
Já sabem como realizar consultas?
*
*
Consultas
Podemos também renomear tabelas para fazer junção de uma tabela com ela mesma;
Exemplo 10: Recupere a matrícula, o nome e o nome do supervisor de cada empregado;
SELECT E.Matricula, E.Nome, S.Nome AS Supervisor 
FROM Empregado E, Empregado S 
WHERE E.Supervisor=S.Matricula
- Resultado:
*
*
Consultas
Comparando com valores nulos:
Uma tabela pode conter valores nulos para vários atributos;
Para selecionar tuplas que possuem um valor de atributo nulo ou não nulo usamos os operadores IS e IS NOT, respectivamente; 
Exemplo 11: Recupere o nome de todos os empregados que não tem supervisor;
SELECT Nome FROM Empregado
WHERE Supervisor IS NULL
Resultado:
*
*
Consultas
Comparando com valores nulos:
Uma tabela pode conter valores nulos para vários atributos;
Para selecionar tuplas que possuem um valor de atributo nulo ou não nulo usamos os operadores IS e IS NOT, respectivamente; 
Exemplo 11: Recupere o nome de todos os empregados que possuem algum supervisor;
SELECT Nome FROM Empregado
WHERE Supervisor IS NOT NULL
Resultado:
*
*
Consultas
Representando tabelas como conjuntos:
A linguagem SQL trata uma tabela como um multiconjunto;
Tuplas repetidas não são descartadas do resultado final;
Podemos usar a palavra-chave DISTINCT para eliminar tuplas repetidas do resultado;
Podemos usar a palavra-chave ALL para especificar explicitamente que todas as tuplas devem ser recuperadas no resultado de uma consulta;
*
*
Consultas
Representando tabelas como conjuntos:
Exemplo 12: Recupere o salário de cada funcionário da empresa;
SELECT ALL Salario FROM Empregado;
Resultado:
*
*
Consultas
Representando tabelas como conjuntos:
Exemplo 13: Selecione todos os salários distintos pago pela empresa;
SELECT DISTINCT Salario FROM Empregado;
Resultado:
*
*
Consultas
Representando tabelas como conjuntos:
SQL define três operadores para trabalhar com conjuntos:
UNION (operação de união);
INTERSECT (operação de interseção);
EXCEPT (operação de diferença);
As tabelas devem ser compatíveis de união para realizar qualquer uma destas operações. 
*
*
Consultas
Representando tabelas como conjuntos:
Significando que ambas devem retornar o mesmo número de colunas, e que as colunas correspondentes devem possuir tipos de dado compatíveis. 
O número e a ordem das colunas devem ser os mesmos em todas as consultas;
 Os tipos de dado devem ser compatíveis.
Estas operações descartam as tuplas repetidas do resultado;
*
*
Consultas
Representando tabelas como conjuntos:
Exemplo 14 (UNION): Selecione a matrícula de todos os empregados que trabalham no departamento 1 ou que trabalham em um projeto:
(SELECT Matricula FROM Empregado WHERE Cod_Departamento=1) UNION
(SELECT DISTINCT Empregado AS Matricula FROM Trabalha_Projeto)
Resultado:
*
*
Consultas
Representando tabelas como conjuntos:
Exemplo 14 (INTERSECT): Selecione a matrícula de todos os empregados do departamento 2 que trabalham em algum projeto
( SELECT Matricula FROM Empregado WHERE Cod_Departamento=2 )
INTERSECT
( SELECT Empregado FROM Trabalha_Projeto )
Resultado:
*
*
Consultas
Representando tabelas como conjuntos:
Exemplo 14 (EXCEPT): Selecione a matrícula de todos os empregados que não trabalham em nenhum projeto
(SELECT Matricula FROM Empregado) 
EXCEPT
(SELECT Empregado FROM Trabalha_Projeto)
Resultado:
*
*
Consultas
Procurando por substrings:
Podemos procurar por um substring em uma cadeia de caracteres usando o operador LIKE;
Podemos usar o caractere % para substituir zero ou mais caracteres;
Exemplo: Para localizar strings que contenham o substring 'Cajazeiras' usamos a expressão '%Cajazeiras%'
SELECT * FROM Cidade WHERE Nome LIKE '%Cajazeiras%'
*
*
Consultas
Procurando por substrings:
Podemos usar o caractere _ (anderline) para substituir um único caractere;
Exemplo: Para localizar todos os strings cujo segundo caractere seja a letra 'a', usamos a expressão '_a%';
Exemplo 15: Recupere a matrícula e o nome de todos os empregados que possuem o nome Alisson;
SELECT Matricula, Nome FROM Empregado 
WHERE Nome LIKE '%Alisson%';
Resultado:
*
*
Consultas
Ordenando o resultado de uma consulta:
Podemos ordenar o resultado de uma consulta usando o operador ORDER BY;
Podemos especificar
uma lista de atributos para a ordenação, onde o primeiro é usado como primeiro critério e os demais sucessivamente, para ordenar tuplas que apresentam o mesmo valor para o critério anterior;
Para cada critério podemos usar os valores ASC ou DESC, para especificar se ordenação deve ser ascendente ou descendente;
A ordenação default é ascendente;
*
*
Consultas
Ordenando o resultado de uma consulta:
Exemplo 16: Recupere o nome de cada empregado, juntamente com o nome do seu departamento e o nome de cada projeto em que ele trabalha; ordene o resultado da busca primeiramente pelo nome do empregado e depois pelo nome do projeto.
SELECT E.Nome AS Empregado, D.Nome AS Depto, P.Nome AS Projeto
FROM Empregado E, Departamento D, Projeto P, Trabalha_Projeto TP
WHERE E.Cod_Departamento=D.Cod_Departamento AND E.Matricula=TP.Empregado AND P.Cod_Projeto=TP.Cod_Projeto
ORDER BY E.Nome ASC, P.Nome ASC
*
*
Consultas
Ordenando o resultado de uma consulta:
Exemplo 16: Recupere o nome de cada empregado, juntamente com o nome do seu departamento e de cada projeto em que ele trabalha;
Resultado:
*
*
Consultas
Consultas aninhadas:
São consultas feitas sobre os dados recuperados por uma outra consulta;
Usamos o operador IN para verificar se uma tupla está presente em um conjunto ou multiconjunto;
Exemplo 17: Selecione a matrícula e o nome dos empregados que trabalham no departamento gerenciado por Marcondes;
*
*
Consultas
Consultas aninhadas:
Exemplo 17 (continuação):
SELECT Matricula, Nome FROM Empregado
WHERE Cod_Departamento IN
(SELECT E.Cod_Departamento FROM Empregado E, Departamento D
 WHERE E.Matricula=D.Gerente AND E.Nome='Marcondes' 
)
Resultado:
*
*
Consultas
Consultas aninhadas:
Podemos usar o quantificador ALL para comparar uma tupla com um conjunto de tuplas recuperado em uma consulta aninhada;
O resultado da operação é verdadeiro se a comparação for verdadeira para todas as tuplas presentes no resultado da consulta aninhada;
Exemplo 18: Recupere o nome dos funcionários que ganham mais que Reginaldo e Alisson;
*
*
Consultas
Consultas aninhadas:
Exemplo 18 (Continuação):
SELECT Nome FROM Empregado
WHERE Salario > ALL 
(SELECT Salario FROM Empregado WHERE Nome=‘Dijalma' OR Nome='Alisson')
Resultado:
*
*
Consultas
Consultas aninhadas:
O quantificador SOME (ou ANY) também pode ser utilizado para comparar uma tupla com as tuplas do resultado de uma consulta aninhada;
O resultado da operação é verdadeiro se a comparação for verdadeira para alguma das tuplas presentes no resultado da consulta aninhada;
Exemplo 19: Recupere o nome e o salário de todos os funcionários que não trabalham no departamento 2 mas ganham mais do que algum funcionário que trabalha no departamento 2;
*
*
Consultas
Consultas aninhadas:
Exemplo 19 (continuação):
SELECT Nome FROM Empregado
WHERE Cod_Departamento <> 2 AND Salario > ANY
(SELECT Salario FROM Empregado WHERE Cod_Departamento=2) 
Resultado:
*
*
Consultas 
A cláusula EXISTS;
A cláusula EXISTS é usada para verificar se o resultado de uma consulta aninhada é ou não vazio;
Ela retorna true caso o resultado da consulta não seja vazio, e false, caso contrário;
Podemos definir também a cláusula NOT EXISTS, que funciona da forma inversa;
Exemplo 20: Recupere o nome de todos os empregados que possuem algum dependente;
*
*
Consultas
A cláusula EXISTS;
Exemplo 20 (continuação, EXITS):
SELECT Nome FROM Empregado E
WHERE EXISTS 
(SELECT * FROM Dependente D WHERE E.Matricula = D.Empregado) 
	- Resultado:
	
*
*
Consultas
A cláusula NOT EXISTS;
Exemplo 20 (continuação, NOT EXISTS): Recupere o nome de todos os empregados que não possuem nenhum dependente;
SELECT Nome FROM Empregado E
WHERE NOT EXISTS 
(SELECT * FROM Dependente D WHERE E.Matricula = D.Empregado) 
	- Resultado:
	
*
*
Consultas
Todos e Todas.....
Já sabem como construir consultas com valores?
*
*
Consultas
Junção de Tabelas:
Até agora, vimos como fazer junção em SQL da forma clássica;
Porém a SQL define o operador JOIN e vários tipos de junção de tabelas:
Equijunção;
Junção Natural;
Junção Externa à esquerda;
Junção Externa à Direita;
Junção Externa Completa;
Produto Cruzado;
Etc;
*
*
Consultas
Junção de Tabelas – Equijunção:
Representa a equijunção da álgebra relacional;
Os dois atributos de junção são considerados no resultado final;
Realizada através da cláusula JOIN;
A operação de junção é definida na cláusula FROM;
Sintaxe:
		
		Tabela1 JOIN Tabela2 ON CondiçãoDeJunção
*
*
Consultas
Junção de Tabelas – Equijunção:
Exemplo 22: Recupere o nome de todos os empregados que trabalham no departamento Financeiro;
SELECT E.Nome 
FROM (Empregado E JOIN Departamento D ON E.Cod_Departamento=D.Cod_Departamento)
WHERE D.Nome='Financeiro' 
Resultado:
*
*
Consultas
Junção de Tabelas – Equijunção:
Exemplo 22 (outro): Recupere o nome de todos os dependentes do empregado Alisson.
SELECT D.nome_Dep 
FROM (Empregado E JOIN Dependente D ON E. matricula = D.empregado)
WHERE E.Nome = 'Alisson' 
Resultado:
*
*
Consultas
Junção de Tabelas – Junção Natural:
Corresponde à operação de junção natural da álgebra relacional;
A junção é feita pelo valor dos atributos que têm o mesmo nome;
Os atributos de junção só aparecem uma vez no resultado final;
Uma junção natural é feita através da cláusula NATURAL JOIN;
*
*
Consultas
Junção de Tabelas – Junção Natural:
Exemplo 23: Recupere o nome de todos os empregados que trabalham no departamento Financeiro;
SELECT E.Nome 
FROM (Empregado E NATURAL JOIN Departamento D (Cod_Departamento, NomeDep, Gerente))
WHERE D.NomeDep = 'Financeiro' 
Resultado:
*
*
Consultas
Junção de Tabelas – Junção Externa:
A equijunção e a junção natural são operações de junção interna;
Tuplas que não estão relacionadas, ou que possuem um valor nulo para o atributo da condição de junção são descartadas do resultado final;
Podemos incluir todas as tuplas de uma tabela no resultado de uma junção através de uma junção externa;
A junção externa é realizada através da cláusula OUTER JOIN; 
*
*
Consultas
Junção de Tabelas – Junção Externa:
Existem três tipos de junção externa:
Junção externa à esquerda (LEFT OUTER JOIN ou LEFT JOIN);
Junção externa à direita (RIGHT OUTER JOIN ou RIGHT JOIN);
Junção externa completa (FULL OUTER JOIN ou FULL JOIN);
Caso os atributos de junção tenham o mesmo nome, podemos incluir a palavra-chave NATURAL antes da cláusula de junção; 
*
*
Consultas
Junção de Tabelas – Junção Externa à Esquerda:
Indica que todas as tuplas da tabela do lado esquerdo da junção devem ser recuperadas;
Mesmo que não estejam associadas à nenhuma tupla da tabela do lado direito;
Mesmo que tenham um valor nulo para o atributo da condição de junção;
As tuplas da direita sem nenhuma tupla correspondente à esquerda ou com valores nulos para o atributos de junção recebem valores nulos para todos os atributos da tabela do lado direito da junção;
*
*
Consultas
Junção de Tabelas – Junção Externa à Esquerda:
Exemplo 24: Recupere todos os empregados e os departamentos que cada um gerencia:
SELECT *
FROM (Empregado LEFT OUTER JOIN Departamento ON Matricula=Gerente) 
- Resultado
*
*
Consultas
Junção de Tabelas – Junção Externa à Direita:
Indica que todas as tuplas da tabela do lado direito da junção devem ser recuperadas;
Mesmo que não estejam associadas à nenhuma tupla da tabela do lado esquerdo;
Mesmo que tenham um valor nulo para o atributo da condição de junção;
As Tuplas da esquerda sem nenhuma Tupla correspondente à direita ou com valores nulos para o atributos de junção recebem valores nulos para todos os atributos da tabela do lado esquerdo da junção;
*
*
Consultas
Junção de Tabelas – Junção
Externa à Direita:
Exemplo 25: Recupere os dependentes seguido de todos os seus respectivos empregados
SELECT * FROM (Dependente RIGHT OUTER JOIN Empregado ON Empregado=Matricula)
*
*
Consulta
Junção de Tabelas – Junção Externa Completa
Recupera todas as tuplas das duas tabelas;
Tuplas do lado esquerdo que não estão associadas a nenhuma tupla da tabela à direita ou que possuem valor nulo para o atributo de junção são tratadas como na junção externa à esquerda;
Tuplas do lado direito que não estão associadas a nenhuma tupla da tabela à esquerda ou que possuem valor nulo para o atributo de junção são tratadas como na junção externa à dirteita;
*
*
Consulta
Junção de Tabelas – Junção Externa Completa
Exemplo 26: Recupere os dependentes seguido de todos os seus respectivos empregados
SELECT * FROM (Dependente FULL OUTER JOIN Empregado ON Empregado=Matricula)
*
*
Consulta
Junção de Tabelas – Produto Cruzado
Representa a operação produto cartesiano da álgebra relacional;
Todas as tuplas da primeira tabela são combinadas com todas as tuplas da segunda tabela, independente de qualquer condição;
Representada pela cláusula CROSS JOIN;
*
*
Consulta
Junção de Tabelas – Produto Cruzado
Exemplo 26: Combine todos os departamentos com todos os projetos:
SELECT * FROM (Departamento CROSS JOIN Projeto)
*
*
Consultas
Funções Agregadas:
A linguagem SQL possui algumas funções agregradas que podem ser aplicadas aos dados no momento de uma consulta:
As principais funções são:
COUNT
Conta quantas tuplas foram recuperadas em uma determinada consulta;
Pode ser aplicada a qualquer consulta SQL;
O seu resultado é sempre um número inteiro;
*
*
Consultas
Funções agregadas:
As principais funções são:
COUNT: 
Exemplo 27: Verifique quantos empregados trabalham no departamento 2;
SELECT COUNT(*) AS TotalDeEmpregados
FROM Empregado WHERE Cod_Departamento =2;
O resultado é uma tabela que
contém um atributo 
TotalDeEmpregados 
e uma única tupla com o valor 2;
*
*
Consultas
Funções agregadas:
As principais funções são:
COUNT: 
Podemos usar a palavra DISTINCT para eliminar duplicidades do resultado final;
Exemplo 28: Recupere quantos valores de salários diferentes são pagos pela empresa; 
SELECT COUNT (DISTINCT Salario) AS Tipos_De_Salario
 FROM Empregado
O resultado é uma tabela com um único 
atributo 'Tipos_De_Salario' e com uma única
 tupla com o valor 2;
*
*
Consultas
Funções agregadas:
As principais funções são:
COUNT:
Podemos usar a cláusula COUNT em consultas aninhadas; 
Exemplo 29: Recupere o nome de todos os empregados que possuem mais de um dependente;
SELECT Nome FROM Empregado E
WHERE (SELECT COUNT(*) FROM Dependente D
 WHERE E.Matricula=D.Empregado) >1
*
*
Consultas
Funções agregadas:
As principais funções são:
COUNT:
Exemplo 29:
 Obtemos a seguinte resposta;
*
*
Consultas
Funções agregadas:
As principais funções são:
MIN e MAX:
Recuperam, respectivamente, o menor e o maior valor de uma coluna;
Podem ser aplicados a qualquer tipo de dado que tenha a noção de ordenação;
Números, cadeia de caracteres, datas, etc;
AVG:
Recupera a média dos valores de uma determinada coluna;
Aplicado apenas a tipos numéricos;
*
*
Consultas
Funções Agregadas:
Exemplo 30:
Recupere o menor salário pago pela empresa, o maior salário pago pela empresa e média salarial dos funcionários;
SELECT MIN(Salario) AS Menor_Salario, MAX(Salario) AS Maior_Salario, AVG(Salario) AS Media_Salarial
 FROM Empregado;
Resultado:
*
*
Consultas
Agrupamento:
Muitas vezes, precisamos aplicar as funções agregadas para subgrupos de tuplas da relação;
Qual a média salarial de um departamento?
Quantos funcionários trabalham em cada projeto da empresa?
Podemos agrupar os elementos de uma relação através da cláusula GROUP BY;
A cláusula é seguida pelo nome do atributo usado para fazer o agrupamento;
Podemos selecionar apenas o atributo que é critério de agrupamento e as funções agregadas;
*
*
Consultas
Agrupamento:
Exemplo 31: Para cada departamento da empresa, recupere o seu código, o seu total de funcionários e a sua média salarial;
SELECT Cod_Departamento, COUNT(*) AS Total_Empregados,AVG(Salario) AS Media_Salarial
FROM Empregado
GROUP BY Cod_Departamento
*
*
Consultas
Agrupamento:
Para recuperarmos atributos adicionais, podemos incluir os atributos adicionais desejados na cláusula GROUP BY após o atributo de agrupamento;
Exemplo 32: Recupere o código de cada projeto, o nome de cada projeto, o total de pessoas que trabalham em cada projeto, e o total de horas trabalhadas por todas as pessoas neste projeto;
SELECT P.Cod_Projeto, P.Nome, COUNT(*) AS Numero_De_Pessoas, SUM(Num_Horas) AS Horas_Trabalhadas
FROM Trabalha_Projeto TP, Projeto P
WHERE TP.Cod_Projeto = P.Cod_Projeto
GROUP BY P.Cod_Projeto, P.Nome
Neste caso, o agrupamento só é feito após a junção das tabelas;
 
*
*
Consultas
Agrupamento:
Exemplo 32 (Resposta):
Obtemos a seguinte resposta:
*
*
Consultas
Agrupamento:
As vezes não queremos recuperar todos os grupos gerados em um agrupamento;
Podemos selecionar apenas os grupos que satisfazem algum critério de seleção;
Podemos fazer esta seleção usando a cláusula HAVING, seguida do critério de seleção desejado;
Apenas os grupos que satisfazem o critério de seleção são mostrados no resultado da consulta;
*
*
Consultas
Agrupamento:
Exemplo 33: Recupere o código e o nome dos projetos em que trabalham mais de um empregado;
SELECT P.Cod_Projeto, P.Nome
FROM Projeto P, Trabalha_Projeto TP
WHERE P.Cod_Projeto=TP.Cod_Projeto
GROUP BY P.Cod_Projeto,P.Nome
HAVING COUNT(*)>1
*
*
Consultas
Criando novas colunas em uma consulta:
Podemos criar novas colunas em nossa consulta através do comando CASE;
O valor para este atributo em cada tupla é calculado no momento da execução da consulta, através de uma ou mais condições;
Tuplas que não satisfazem à nenhuma das condições recebem valores nulos;
Uma cláusula ELSE, que é opcional, pode ser usada para calcular o valor destas tuplas;
*
*
Consultas
Criando novas colunas em uma consulta:
Sintaxe do comando CASE;
CASE
 WHEN <cond1> THEN <valor1>
 WHEN <cond2> THEN <valor2>
 ...
 WHEN <condN> THEN <valorN>
 ELSE <valorDefault>
 END 
*
*
Consultas
Criando novas colunas em uma consulta:
Exemplo: Selecione a matricula, nome e salário dos empregados. Crie no resultado uma nova coluna que informe se o empregado ganha muito ou pouco segundo a seguinte regra: se o salário for menor ou igual a 3000 reais o empregado ganha pouco, mas se o salário for maior que 4000, então ele ganha muito.
SELECT Matricula, Nome, Salario,
 CASE
 WHEN Salario<=3000 THEN 'Ganha pouco'
 WHEN Salario>4000 THEN 'Ganha muito'
 END AS Situacao FROM Empregado 
*
*
Consultas
Criando novas colunas em uma consulta:
Exemplo(Resultado):
*
*
Visões
São tabelas derivadas de outras tabelas;
Permitem controlar a visão que cada usuário ou grupo de usuário tem sobre os dados armazenados;
Nem sempre é bom os usuários terem acesso a todos os dados do banco de dados;
São tabelas virtuais, não são armazenadas fisicamente;
Boas para representar consultas que devem ser feitas com muita freqüência;
*
*
Visões
Cada visão tem um nome, um conjunto de atributos e uma consulta que a representa;
As visões contém sempre os dados atualizados;
A consulta é executada sempre que a visão é invocada; 
A definição da visão é armazenada no catálogo do SGBD;
Usamos a seguinte sintaxe para descrever uma visão:
CREATE VIEW NomeDaVisão
AS Consulta 
*
*
Visões
Exemplo: Crie uma visão que ao ser invocada possa retornar os nomes dos empregados, o nome dos projetos e o número de horas trabalhadas
em cada projeto.
	 CREATE VIEW EmpregadoComProjetos
 AS SELECT E.Nome AS Empregado, P.Nome AS Projeto, Num_Horas 
	 FROM Empregado E, Projeto P, Trabalha_Projeto TP
	WHERE E.Matricula=TP.Empregado AND P.Cod_Projeto=TP.Cod_Projeto
*
*
Visões
O resultado será a seguinte tabela virtual:
*
*
Visões
Podemos fazer consultas em cima dos dados de uma visão;
Exemplo: Recupere o nome de todos os empregados que trabalham no projeto Max Lucro;
	SELECT Empregado FROM EmpregadoComProjetos
	WHERE Projeto = 'Max Lucro'
	
A consulta vai recuperar os empregados Dijalma, Alisson e Aluísio;
*
*
Visões
Atualização:
Muitas vezes, os usuários podem querer inserir e atualizar dados em visões:
Isto pode trazer problemas:
O que aconteceria se o usuário tentasse executar o comando:
INSERT INTO EmpregadoComProjetos VALUES 
('Reginaldo','Boa Oferta', '12')
*
*
Visões
Atualização:
Em geral, a atualização de visões é tratada da seguinte forma:
Uma visão definida numa única tabela é atualizável se os atributos da visão contêm a chave primária e todos os atributos que possuem a restrição NOT NULL sem nenhum valor default;
Visões definidas sobre múltiplas tabelas usando junção geralmente não são atualizáveis;
Visões usando funções de agrupamento e funções agregadas não são atualizáveis;
*
*
Visões
Redefinindo uma visão:
Podemos redefinir o nome de uma visão através do operador ALTER VIEW;
Sintaxe:
ALTER VIEW NomeDaVisão RENAME TO NovoNomeDaVisão;
Exemplo:
ALTER VIEW EmpregadoComProjetos
RENAME TO EmpregadosESeusProjetos
*
*
Visões
Exluindo uma visão:
Podemos excluir uma visão usando o operador DROP VIEW;
Sintaxe:
DROP VIEW NomeDaVisão
Exemplo:
DROP VIEW EmpregadoComProjetos
*
*
Índices
Um índice é uma estrutura de acesso físico aos dados, usado para acelerar o tempo das consultas;
Semelhante ao índice de um livro que usamos para encontrar um determinado assunto;
Definido sobre o valor de um atributo;
Geralmente, a chave primária e os atributos com a restrição UNIQUE;
Uma tabela pode ter mais de um atributo indexado
*
*
Índices
Criando um novo índice:
Podemos criar um novo índice através do operador CREATE INDEX;
Sintaxe:
CREATE INDEX NomeDoÍndice
	ON NomeDaTabela(Atributo)
Exemplo:
CREATE INDEX ind1
	ON Empregado(Matricula)
*
*
Índices
Criando um novo índice:
Podemos criar um novo índice através com uma condição;
Sintaxe:
CREATE INDEX NomeDoÍndice
	ON NomeDaTabela(Atributo)
	WHERE condição
Exemplo:
CREATE INDEX indiceSalario
	ON Empregado(Salario)
	WHERE salario>3000
*
*
Índices
Alterando um índice:
Podemos alterar um índice existente através do operador ALTER INDEX;
Sintaxe:
ALTER INDEX NomeDoÍndice
	RENAME TO NomeDoNovoÍndice
Exemplo:
ALTER INDEX indiceSalario
	RENAME TO indiceSalarioRenomeado
*
*
Índices
Removendo um índice:
Podemos remover um índice usando o operador DROP INDEX;
Sintaxe:
DROP INDEX NomeDoIndice;
Exemplo:
DROP INDEX indiceSalarioRenomeado; 
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*