1 de 90 | www.direcaoconcursos.com.br
Aula 00
1 Linguagens de definição e manipulação de dados em SGBDs relacionais Parte I
Fundamentos de Banco de Dados para
SEFAZ SE
Sumário
TEORIA DA AULA ... 3
DDL E DML–CRIAÇÃO DE TABELAS E INSERÇÃO DE DADOS ... 4
DDL – Criação de tabelas ... 5
Inserindo dados nas tabelas (DML) ... 7
DQL–LINGUAGEM DE CONSULTA ... 9
Operadores lógicos ... 12
Mais alguns operadores lógicos ... 18
Junções (joins) ... 22
Algumas considerações sobre o SELECT ... 31
Agrupamentos – A cláusula GROUP BY ... 35
QUESTÕES COMENTADAS PELO PROFESSOR ... 44
LISTA DE QUESTÕES ... 72
GABARITO ... 86
RESUMO DIRECIONADO ... 87
Linguagem SQL ... 87
DQL ... 87
Teoria da aula
Os bancos de dados relacionais possuem suporte à linguagem SQL – Structured Query Language. Essa linguagem serve não somente para extrair (consultar) os dados em um SGBD, mas também para realizar todo o gerenciamento do sistema. Esse gerenciamento inclui a criação e modificação de estruturas, inserção, atualização e remoção de registros, dentre outras tarefas.
SQL é uma linguagem padronizada, ou seja, existe um documento formal registrado na ANSI (uma organização americana análoga à ABNT) que indica quais são os comandos da linguagem. Contudo, existem variações de SGBD para SGBD com algumas modificações e extensões em cada uma. A relação entre o padrão e as demais é como se fosse a de uma língua mãe com seus vários dialetos regionais - alguém que conhece o padrão SQL vai poder se adaptar às diferentes variações entre os SGBDs sem grandes dificuldades.
A linguagem SQL se subdivide em várias linguagens, de acordo com o propósito dos comandos. Veja o quadro a seguir:
DDL – Data Definition Language – Linguagem de definição de dados. Esses comandos possibilitam a manipulação de objetos do banco de dados. Por exemplo, a criação, alteração e remoção de tabelas que contém dados.
DML – Data Manipulation Language – Linguagem de manipulação de dados. Esses comandos interagem com os dados nas tabelas. Através dos comandos você pode inserir, remover ou atualizar registros nas relações.
DQL – Data Query Language – Linguagem de consulta. Esse conjunto de comandos nos permite extrair os dados do banco de dados de diferentes maneiras para realizar as mais diversas análises.
DTL – Data Transaction Language – Linguagem de transação. Esses comandos nos permitem realizar o controle de início, fim e registro das transações realizadas em um banco de dados.
DCL – Data Control Language – Linguagem de controle. Essa linguagem contém comandos que permitem aos administradores do banco de dados controlarem a parte de segurança, concedendo, revogando ou negando acesso a determinados registros, objetos ou até a bancos de dados inteiros a determinados usuários.
Para a nossa aula de hoje, o interessante mesmo será a DQL. Em menor grau, vamos ver alguns comandos DDL e DML somente para que possamos resolver algumas questões. Entraremos em maior detalhe a respeito dessas outras linguagens nas próximas aulas.
Algo que você deve ter em mente para a nossa aula, é que SQL é uma linguagem declarativa. Isso quer dizer que cada consulta SQL descreve o resultado esperado da consulta que será retornado pelo SGBD, e não os procedimentos necessários para chegar a ele. Assim, o usuário não se preocupa em como o sistema irá fazer para encontrar os dados, mas somente em descrever a estrutura e as condições para que as linhas e colunas sejam exibidas no resultado.
Os comandos SQL possuem algumas características importantes que vou passar para você através da resolução de uma questão. Fique atento!
(FCC – TRE/PB – 2015)
SQL é a linguagem padrão utilizada em bancos de dados relacionais, cujas instruções a) devem ser inseridas em apenas uma linha.
b) devem ser finalizadas por dois-pontos (:).
c) não possuem distinção entre letras maiúsculas e minúsculas.
d) podem ser abreviadas, inclusive as palavras-chave.
e) não podem ter as cláusulas separadas em linhas diferentes.
RESOLUÇÃO:
a) Na linguagem SQL, um comandos pode estar em uma ou mais linhas. A linguagem não leva em consideração a formatação e a identação do código, como outras linguagens. Você verá, ao longo da aula, que iremos mostrar os mesmos comandos organizados de diferentes maneiras. ERRADA
b) O fim de cada comando SQL é marcado por ponto e vírgula (;), mas isso geralmente não é obrigatório, por isso o item está incorreto ao dizer que "devem". De todo modo, a utilização do ponto e vírgula é uma boa prática, pois delimita corretamente onde termina um comando e começa outro. ERRADA
c) Geralmente se representa os comandos da linguagem SQL como SELECT, UPDATE, DELETE, etc., com letras maiúsculas, mas isso é somente uma convenção. Esses comandos podem ser escritos com letras maiúsculas ou minúsculas, já que a linguagem é case insensitive, ou seja, não faz essa distinção.
Só se deve ficar atento aos nomes dos itens de dados, como tabelas, campos e valores de registros, pois pode haver distinção entre maiúsculas e minúsculas . CERTA
d) Os comandos SQL devem ser referidos pelos seus nomes, não se admitindo abreviações. Por exemplo, não faz sentido substituir o comando DELETE pela palavra DEL, já que o SGBD não saberia que tipo de comando é esse. ERRADA
e) Por fim, como vimos na letra a, as cláusulas de uma instrução SQL podem estar separadas em múltiplas linhas, sem problemas. ERRADA
Gabarito: C
DDL e DML – Criação de tabelas e inserção de dados
Antes de tudo, vamos fazer uma breve introdução aos comandos DDL e DML que podem ser cobrados de forma direta ou indireta na sua prova.
DDL – Criação de tabelas
Para começar, sabemos que a linguagem SQL é capaz de criar objetos no banco de dados. Essa manipulação de objetos é feita através dos comandos da linguagem de definição de dados (DDL).
Em um banco de dados relacional, como você já sabe, os dados estão armazenados em tabelas e essas tabelas estão relacionadas entre si. Já me deparei com várias questões em que o examinador, no lugar de mostrar a você um modelo lógico, ilustrado, com as tabelas lindamente representadas, te dá somente os comandos SQL que foram utilizados para criar a tabela da qual você vai extrair os dados. Então, mesmo que seu objetivo seja somente aprender como se extrai os dados das tabelas do banco de dados, fique bem ligado nesta seção!
Por isso, precisamos apresentar, primeiramente, o comando DDL CREATE TABLE. Veja a seguir o comando que cria uma tabela chamada Funcionario que contém os dados a respeito dos funcionários de uma empresa:
CREATE TABLE Funcionario (
idFuncionario int(11) NOT NULL PRIMARY KEY, data_nascimento date NOT NULL,
nome varchar(14) NOT NULL, sobrenome varchar(16) NOT NULL, sexo char(1) NOT NULL,
data_contratacao date NOT NULL );
Vamos por partes! O comando CREATE TABLE, como o nome em inglês já indica, é o comando que cria uma tabela. Logo seguida desse comando, colocamos o nome da tabela, no nosso exemplo “Funcionario”. Até aí tudo tranquilo, não é?
Entre parênteses, logo após o nome da tabela, vamos ter a lista de atributos, separados por virgula, que vão compor a tabela. Cada definição de atributo funciona da seguinte maneira:
Então, veja que no exemplo acima temos a definição do atributo chamado nome, que assume o tipo de dados varchar (uma cadeia de caracteres, que juntos podem formar uma palavra), de tamanho máximo 14 e cuja presença é obrigatória no registro, ou seja, não é possível inserir um registro nessa tabela com o valor NULL no lugar do nome. Tudo certo?
Por fim, observe que, ao lado da definição do atributo idFuncionario, colocamos a expressão PRIMARY KEY.
Como você já deve suspeitar, isso indica que aquele atributo é a chave primária da nossa tabela.
No nosso exemplo, temos uma chave simples substituta idFuncionario. Se esse não fosse o caso e quiséssemos definir uma chave primária em múltiplas outras colunas com algum significado inerente, precisaríamos adotar a seguinte sintaxe:
CREATE TABLE Funcionario ( data_nascimento date NOT NULL, nome varchar(14) NOT NULL, sobrenome varchar(16) NOT NULL, sexo NOT NULL,
data_contratacao date NOT NULL,
CONSTRAINT PK_Funcionario PRIMARY KEY (nome, data_nascimento, data_contratacao) );
Veja que a linha que começa com CONSTRAINT indica que está sendo criada uma restrição, não um novo atributo. Em seguida, é definido o nome dessa restrição, no nosso caso, PK_Funcionario. Logo após, é definido o tipo da restrição que estamos criando, que á uma chave primária, ou PRIMARY KEY. Por fim, ao abrir os parênteses, estamos dizendo que a chave primária é composta pelos atributos a seguir, ou seja, nome, data_nascimento e data_contratacao.
Para que esses atributos possam compor uma chave primária, o projeto desse modelo hipotético deve ter indicado que o conjunto dos valores desses atributos não se repete ao longo da tabela, claro!
Pronto! Já sabemos os básicos a respeito da criação de tabelas. A criação de views, funções, stored procedures e outros objetos de bancos de dados também acontece através do comando CREATE, contudo, como este é um curso de nível básico, não faz sentido abordarmos esses assuntos agora.
Além do comando CREATE, temos os comandos DML DROP (remove objetos), ALTER (altera a estrutura de um objeto) e TRUNCATE TABLE (remove todos os registros da tabela de uma vez). O TRUNCATE TABLE, por remover registros de uma tabela, às vezes é confundido com a linguagem DML. Não vale a pena explicar o porquê agora, mas acredite em mim quando digo que ele é DDL! ☺
Vamos em frente!
Tipos de dados
“Legal, professor, entendi como se cria uma tabela, mas como eu reconheço os diferentes tipos de dados dos atributos?” Vou te apresentar os principais tipos agora mesmo! Os dados geralmente tomam a forma de cadeias de caracteres, números ou datas, como foi o caso do nosso exemplo acima. Contudo, saiba que existem outras dezenas de tipos de dados, que variam um pouco de nome e implementação conforme o SGBD.
Tipo Descrição
date Para datas
datetime Data e hora
char Cadeia de caracteres de tamanho fixo
varchar Cadeia de caracteres de tamanho variável
int Número inteiro. Possui variações que diferem em tamanhos mínimo e máximo: tinyint, smalint, bigint...
double, numeric,
decimal Tipos de dados que representam números com casas decimais
bool Booleano, só pode assumir valores TRUE ou FALSE (verdadeiro ou falso)
bit Tamanho de dados bem pequeno, só assume os valores 1 ou 0. Muitas vezes é utilizado no lugar de booleanos.
Curiosidade!
Veja que para representar um determinado número inteiro podemos utilizar qualquer um dos tipos de inteiro, como tinyint, smallint ou o próprio int. A diferença entre eles é o espaço que cada valor ocupa no banco de dados. O tinyint, por exemplo, ocupa somente 1 byte, enquanto que um valor no formato int ocupa 4 bytes.
“Ah, professor, entendi. Então vamos usar sempre tinyint, porque é mais econômico, não é?”
Não é bem assim! Ocorre que cada um desses tipos tem conjunto de valores possíveis que pode assumir. Por exemplo, o tinyint só vai de 0 a 255, então se queremos representar a idade de uma pessoa, por exemplo, podemos usá-lo sem problemas pois não há ninguém que chegue a mais de 255 anos. Contudo, se quiséssemos representar a quantidade de habitantes em um país, com certeza precisaremos de um tipo maior. Poderíamos, assim, utilizar o int, que pode representar os valores entre -2.147.483.648 e 2.147.483.647!
Devemos sempre usar o menor tipo de dados que seja capaz de conter todos os valores possíveis para os nossos dados.
Inserindo dados nas tabelas (DML)
Bom, no tópico acima criamos nossa primeira tabela, a de Funcionario, e vimos um pouquinho sobre tipos de dados. Isso já é suficiente para que eu possa apresentar para você como se insere dados em uma tabela já criada no banco, não é?
Depois de termos os dados inseridos, já poderemos ver como se consulta os dados das bases. Por ora, veja o comando a seguir que insere três funcionários na tabela Funcionario:
INSERT INTO Funcionario
(idFuncionario, data_nascimento, nome, sobrenome, sexo, data_contratacao) VALUES
(10001,'1953-09-02','Georgi','Facello','M','1986-06-26'), (10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21'), (10003,'1959-12-03','Parto','Bamford','M','1986-08-28')
Observe que primeiro dizemos onde vamos inserir os dados, através do comando INSERT INTO, que significa literalmente “inserir em” ou “inserir dentro de”. Ou seja, no código acima estamos dizendo “insira dentro de Funcionario...”.
Em seguida, delimitamos a ordem dos atributos que vamos apresentar “(idFuncionario, data_nascimento, nome...)”. Aqui estamos “dizendo” ao banco de dados algo como “Veja só, vou te apresentar uma série de valores para os registros. Cada conjunto de valores estará estruturado da seguinte maneira: primeiro vem o idFuncionario, depois vem a data_nascimento, depois o nome, sobrenome, sexo e, por último, a data_contratacao.”
Apesar de termos utilizado a mesma ordem com que os atributos foram definidos na criação da tabela, poderíamos misturá-los de qualquer maneira sem maiores problemas. Só teríamos que tomar cuidado para não acabar inserindo a data de contratação na data de nascimento ou o nome no lugar do sobrenome, por exemplo. ☺ Essa definição da ordem dos atributos, aliás, é opcional. Quando a omitimos, o que queremos dizer é que estamos utilizando a mesma ordem com que os atributos foram originalmente definidos na criação da tabela. No nosso exemplo, como usamos a mesma ordem da criação, poderíamos ter escrito a consulta de uma maneira mais simples, omitindo essa ordem. O resultado seria o mesmo:
INSERT INTO Funcionario VALUES
(10001,'1953-09-02','Georgi','Facello','M','1986-06-26'), (10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21'), (10003,'1959-12-03','Parto','Bamford','M','1986-08-28')
Voltando à nossa consulta, temos o termo VALUES, que marca o ponto em que começaremos a listar os valores a serem registrados na tabela. Em seguida listamos, separados por vírgula, os conjuntos de atributos que compõem cada registro. Cada linha como a reproduzida a seguir, portanto, é um Funcionário a ser incluído na tabela:
(10001,'1953-09-02','Georgi','Facello','M','1986-06-26')
Veja que os valores dos atributos a serem inseridos devem ser compatíveis com o tipo e tamanho de dados de cada atributo da tabela. Se tentássemos incluir uma cadeia de caracteres na posição onde fica o atributo idFuncionario,
Na inserção, o SGBD “confere” se o registro respeita todas as restrições de integridade definidas para o banco de dados – se não está duplicando o valor de uma chave primária, se as chaves estrangeiras respeitam a integridade referencial, se não estamos incluindo um valor nulo em um atributo que não aceita nulos, etc.
Enfim, após a inserção dos registros acima, nossa tabela ficaria assim:
As aspas simples servem para caracterizar as cadeias de caracteres (tipo de dados literal capaz de formar palavras, frases e sequências diversas de caracteres), diferenciando-as de nomes de objetos, comandos SQL, valores de tipos numéricos, etc.
Veja que se digitarmos o valor 'SELECT' em algum lugar do nosso código, ele não será confundido com o comando SELECT.
Da mesma maneira, observe que os valores 10001 e '10001' são de tipos distintos. O primeiro é um número inteiro, o outro uma cadeia de caracteres. Isso tem implicações importantes para o SGBD, como veremos ao longo da nossa aula!
Além do comando INSERT, na DML também temos os comandos UPDATE, que permite alterar dados já inseridos, e o comando DELETE, que permite que se remova registros de uma tabela.
DQL – Linguagem de consulta
Tendo visto como são criadas e populadas as tabelas, podemos agora nos dedicar a falar sobre as consultas realizadas pra extrair os dados delas.
A estrutura básica do comando SELECT
Para extrair os dados de uma ou mais tabelas, primeiro especificamos quais colunas (ou atributos) queremos ver.
Nós podemos fazer isso através do comando SELECT. Se queremos consultar todas as colunas envolvidas na consulta, simplesmente colocamos um asterisco (*) no lugar dos nomes das colunas, assim:
SELECT *
FROM Funcionario
Para especificar a tabela de onde vamos tirar os dados, utilizamos o termo FROM, ou “de”, em inglês. Traduzindo a consulta aí de cima para o bom Português, estamos pedindo ao banco de dados o seguinte: “me retorne os valores de todos os atributos para todos os registros da tabela Funcionario”.
Se os demais atributos não nos interessam e quisermos saber simplesmente os nomes e sobrenomes de todos os funcionários da empresa, podemos fazer o nosso “pedido” ao banco de dados especificando, após o comando SELECT, as colunas desejadas:
SELECT nome,sobrenome FROM Funcionario
Assim, aprendemos a limitar o escopo da nossa consulta a determinadas colunas. Essa é uma operação do modelo relacional conhecida como projeção. No entanto, veja que ambas as consultas acima trazem todos os registros (linhas ou tuplas) da tabela. Lógico que nem sempre isso é interessante. Assim como fizemos com as colunas, às vezes queremos que o BD retorne somente um subconjunto das linhas das tabelas.
Por exemplo, imagine que queremos saber quais os funcionários na nossa empresa estão chegando perto da aposentadoria. Para esse fim, vamos extrair quais funcionários nasceram até 1965. Para isso, podemos utilizar a data de nascimento, aplicando um filtro que irá retornar somente aqueles usuários que nasceram antes desse ano.
No modelo relacional formal, esse é uma operação conhecida como seleção.
SELECT nome ,sobrenome FROM Funcionario
WHERE data_nascimento < '1965-01-01'
Dica! O formato padrão para datas nos bancos de dados é AAAA-MM-DD. Ou seja, ano-mês-dia. Quando estamos trabalhando com datas, muitas vezes colocamos o valor que queremos nesse formato acima entre aspas simples. Assim, o banco de dados entende que se trata de uma cadeia de caracteres e automaticamente converte esse valor para uma data.
Assim, podemos dizer que delimitamos o escopo da nossa consulta em relação às linhas ou registros da tabela através da cláusula WHERE. Um registro só vai ser retornado pela consulta se satisfizer a expressão lógica imposta pelo WHERE.
Veja que, no caso acima, o SGBD vai comparar o valor de data_nascimento de cada registro com o valor dado, 01/01/1965. Se o resultado da expressão data_nascimento < '1965-01-01' for VERDADEIRO, isto é, se a data de nascimento do funcionário for anterior a 1 de janeiro de 1965, o registro irá compor o resultado da consulta. Caso contrário, não irá. Simples assim!
O < (menor que) não é o único operador de comparação entre dois valores no SQL. Podemos utilizar os seguintes:
> maior que < menor que
>= maior ou igual a <= menor ou igual a
Fique atento! Perceba que o SGBD considera uma data “menor” que a outra quando ela é anterior no tempo. Ou seja, 01/01/2019 é menor que 02/01/2019.
Pronto! Vimos qual é uma estrutura básica de uma consulta SQL. Para construir essa estrutura padrão das consultas, respondemos às seguintes perguntas:
SELECT – O quê?
FROM – De onde?
WHERE – Com que condições?
SELECT DISTINCT
Um detalhe é que uma consulta nessa estrutura básica que acabamos de mostrar pode retornar tuplas repetidas, ou seja, duas tuplas com os mesmos valores. Imagine a tabela Pessoa a seguir. Veja que existem duas pessoas diferentes (cada uma com seu próprio CPF) com mesmos nome e idade:
CPF Nome Idade
788.802.160-31 José da Silva 40 353.270.060-34 José da Silva 40 705.668.150-60 Maria da Silva 37
Agora vamos realizar uma consulta simples sobre essa tabela, para retornar o nome e a idade das pessoas:
SELECT Nome, Idade FROM Pessoa O resultado será o seguinte:
Nome Idade
José da Silva 40
José da Silva 40 Maria da Silva 37
Como o SELECT irá passar por todos os registros da tabela definida no FROM e extrair determinado subconjunto de atributos, é possível que haja tuplas duplicadas no resultado, como pôde ser visto acima. Embora pertençam a registros diferentes na tabela original, os atributos retornados apresentam os mesmos valores no resultado, já que não incluímos a chave primária CPF na nossa consulta.
Para eliminar as duplicidades do resultado da consulta é muito simples. É só colocarmos o comando DISTINCT após o SELECT :
SELECT DISTINCT Nome, Idade FROM Pessoa
Essa consulta terá como resultado o seguinte:
Nome Idade
José da Silva 40 Maria da Silva 37
Assim, podemos concluir que o SELECT DISTINCT elimina as tuplas repetidas do resultado da consulta, de forma independente dos registros originais.
Operadores lógicos
Quando realizamos uma consulta no mundo real, ela geralmente não será algo tão simples como as que temos mostrado até agora. As consultas podem envolver várias e várias cláusulas dentro do WHERE, que vão delimitar o escopo dos dados dentro das tabelas, conforme vimos.
É importante que você perceba que o comando WHERE propriamente dito só aparece uma vez em cada consulta.
Então só podemos colocar uma expressão lógica por consulta. Mas, calma! Essa expressão pode ser composta por um encadeamento de várias outras.
SELECT nome ,sobrenome FROM Funcionario
WHERE data_nascimento < '1965-01-01'
Veja, o propósito da consulta era verificar os funcionários que estão se aproximando da aposentadoria. Contudo, como você sabe, a idade mínima para aposentadoria de homens e mulheres é diferente. Dessa maneira, é interessante que façamos uma alteração na consulta acima para que ela passe a retornar os homens que nasceram antes de 1960 e as mulheres que nasceram antes de 1965.
Para fazer essa consulta, vamos precisar de alguns operadores interessantes chamados operadores lógicos. Esses operadores nos ajudam a encadear proposições lógicas. Veja a seguir:
Operador AND
Vamos modificar a nossa consulta original por etapas. Vamos começar pelo sexo masculino, trazendo os homens que nasceram antes de 1960. Para fazer isso, vamos utilizar o operador AND:
SELECT nome ,sobrenome FROM Funcionario
WHERE data_nascimento < '1960-01-01' AND sexo = 'M'
No caso acima, para uma linha da tabela Funcionario satisfazer às condições da nossa consulta e fazer parte do resultado, ela tem que, de forma cumulativa, atender às restrições que impusemos tanto à data de nascimento quanto ao sexo.
AND em inglês significa simplesmente “e”. Assim, a nossa consulta modificada diz o seguinte: “retorne os valores dos atributos nome e sobrenome para todos os registros da tabela Funcionario, cujo valor de data_nascimento é anterior a 01/01/1960 e, cumulativamente, cujo valor do atributo sexo é igual a M, de masculino”. O registro será retornado se e somente se satisfizer ambos os “lados” do AND.
Ficou claro? Então vamos em frente!
Operador OR
Bom, vamos voltar ao nosso exemplo. Nosso objetivo em modificar a consulta é obter os homens que nasceram antes de 1960 e as mulheres nascidas antes de 1965. Ao mostrar o operador AND, já construímos uma consulta que retorna os homens que nasceram antes de 1960. Relembre:
SELECT nome ,sobrenome
FROM Funcionario
WHERE data_nascimento < '1960-01-01' AND sexo = 'M'
Agora precisamos incluir as mulheres na equação. Vamos criar uma outra cláusula, equivalente à dos homens, mudando somente os valores dos atributos. Essa cláusula vai trazer as mulheres que nasceram antes de 1965:
data_nascimento < '1965-01-01' AND sexo = 'F'
A partir daí, precisamos incluir essa cláusula que acabamos de construir no nosso WHERE da consulta principal.
Mas veja que não faz sentido usar um novo operador AND:
SELECT nome ,sobrenome FROM Funcionario
WHERE data_nascimento < '1970-01-01' AND sexo = 'M' AND data_nascimento < '1975-01-01' AND sexo = 'F'
Essa consulta não trará nenhum resultado! Veja que, para satisfazê-la, um registro teria que, cumulativamente, ter o sexo igual a 'M' e igual a 'F'. Contudo, você sabe que os atributos só assumem valores atômicos, ou seja, um atributo de um registro não pode assumir mais de um valor ao mesmo tempo.
Da mesma maneira, a cláusula data_nascimento < '1960-01-01 ficaria sem nenhum sentido de existir, já que já temos data_nascimento < '1965-01-01'. Se uma data é anterior a 1960, ela obviamente também será anterior a 1965.
Assim, precisamos de um novo operador, em que utilizá-lo dê uma noção de alternativa, não de cumulatividade.
Esse é o operador OR , que vamos usar na nossa consulta. O OR, “ou” em Português, vai retornar o valor verdadeiro quando qualquer uma das duas proposições for verdadeira.
Isto é, se temos as proposições:
r = data_nascimento < '1960-01-01' AND sexo = 'M' s = data_nascimento < '1965-01-01' AND sexo = 'F'
Um determinado registro será retornado em qualquer uma das três situações a seguir:
1) Se somente r for verdadeira;
OBS: Esse último caso é impossível na nossa situação atual, já que um registro não poderia ter o sexo masculino e feminino ao mesmo tempo. Contudo, se estivéssemos utilizando atributos diferentes nos dois lados do OR, poderia ocorrer de ambas as proposições serem verdadeiras sem maiores problemas.
Já sabemos que temos que utilizar o OR para compor a nossa consulta. Vamos agora tentar montá-la. Vamos passar a utilizar o * ao invés de somente os atributos nome e sobrenome para fins práticos, para que analisemos todas as colunas do resultado:
SELECT *
FROM Funcionario
WHERE data_nascimento < '1960-01-01' AND sexo = 'M' OR data_nascimento < '1965-01-01' AND sexo = 'F'
Veja os resultados:
Deu certo! Veja que cortei a imagem para não ficar muito grande, então só temos parte dos registros na nossa tela, mas dá pra perceber que todos os homens que foram retornados nasceram antes de 1960 e todas as mulheres antes de 1965.
Precedência de operadores:
Cuidado! A consulta que construímos retornou os resultados corretos porque o operador AND leva precedência sobre o OR.
Ou seja, o SGBD faz primeiro as operações AND e só depois avalia os OR. Se isso não fosse o caso, se ele avaliasse as expressões somente da esquerda para direita, obteríamos resultados indesejados devido à ordem com que as comparações são realizadas.
Por isso, é uma boa prática utilizarmos parênteses, que dizem expressamente ao SGBD que comandos interpretar primeiro.
Assim temos um maior controle e não dependemos dessas regras de precedência, que muitas vezes podem ser bem confusas.
Veja a nossa consulta reescrita utilizando parênteses:
SELECT *
FROM Funcionario
WHERE (data_nascimento < '1960-01-01' AND sexo = 'M') OR (data_nascimento < '1965-01-01' AND sexo = 'F')
Se o examinador trouxer uma consulta com múltiplos parênteses, vá avaliando as cláusulas lógicas dos parênteses mais internos para os mais externos. É que nem nas equações matemáticas!
Por exemplo, se temos as proposições p = V, q = F, r = V e s = F, podemos avaliar a expressão s AND ( (p AND q) OR r) da seguinte maneira:
1) s AND ( (p AND q) OR r) → s AND (F OR r)
2) s AND (F OR r) → s AND V
3) s AND V → F
Antes de seguirmos em frente, veja a seguinte questão que irá complementar o seu conhecimento teórico. Após resolvê-la, sugiro que faça uma pausa no estudo para ter tempo de absorver tudo que você viu até agora. Não sei se é o seu caso, mas o assunto é completamente novo para muitos alunos, então é melhor ir com calma!
(UFES – UFES – 2018)
Em SQL padrão, há comandos, como o INSERT, que podem ser utilizados para modificar um banco de dados.
Sobre a utilização do comando INSERT em SQL padrão, é INCORRETO afirmar:
a) O comando INSERT é utilizado, em SQL padrão, para acrescentar tuplas a uma relação.
b) Não é possível inserir em uma relação várias tuplas recuperadas do próprio banco de dados a partir de comandos
c) Quando uma relação possui muitos atributos, é possível listar apenas os nomes de atributos específicos que correspondam aos valores fornecidos, porém, para que a inserção ocorra, é necessário listar no comando todos os atributos previamente especificados como NOT NULL e sem valor padrão.
d) É possível inserir em uma relação múltiplas tuplas separadas por vírgulas.
e) Um Sistema de Gerenciamento de Banco de Dados (SGBD) que implementa totalmente a SQL padrão deve rejeitar operações de inserção que não forneçam valores para um atributo previamente especificado como NOT NULL.
RESOLUÇÃO:
Preste bem atenção, o examinador pede a alternativa incorreta:
a) Vimos que o INSERT é utilizado para inserir registros em uma tabela, ou seja, tuplas em uma relação. CERTA b) É possível sim! Ao invés de delimitar os valores diretamente com o comando VALUES, podemos simplesmente colocar uma consulta com SELECT após o INSERT, desde que os campos retornados pela consulta tenham as corretas correspondências de tipo e restrições com os campos da tabela em que estamos inserindo os dados:
Por exemplo, vamos dizer que temos uma tabela Employees, da filial estrangeira da nossa empresa. Vamos adicionar os registros da tabela Employees na nossa Funcionarios:
INSERT INTO Funcionario
SELECT idEmployee, birth_date, first_name, last_name, gender, hire_date FROM Employees
Esse é um comando válido SQL! Portanto, a alternativa está ERRADA e por isso é a nossa resposta.
c) Como nós vimos, podemos listar o conjunto dos atributos que estamos inserindo, na forma:
INSERT INTO Funcionario (idFuncionario, data_nascimento, nome, sobrenome, sexo, data_contratacao)...
Podemos, inclusive, omitir um ou outro campo nessa lista, não inserindo nenhum valor para o atributo omitido, desde que esse atributo admita o valor NULL.
Isso ocorre porque, ao inserir um registro, se não especificarmos nenhum valor para algum atributo, o SGBD vai entender que você está colocando um NULL naquela posição.
Outra alternativa é, no momento da criação da tabela, definirmos um valor padrão para o atributo. Dessa forma, poderíamos dizer, por exemplo, que se a data_contratacao não for preenchida, seu valor padrão será sempre a data da própria inserção do registro.
Assim, quando colocarmos um registro em funcionário, poderemos simplesmente omitir o valor para data_contratacao. Veja:
INSERT INTO Funcionario (idFuncionario, data_nascimento, nome, sobrenome, sexo) VALUES (1, '1990-01-01', 'José', 'Silva', 'M')
O comando rodaria sem problemas e o registro ficaria da seguinte forma, assumindo que a data dessa inserção foi no dia 17/02/2019:
Portanto, a alternativa está CERTA, não sendo a nossa resposta.
d) Como vimos durante a teoria, é plenamente possível inserir várias tuplas em um mesmo INSERT, separando-as por vírgulas. Veja novamente nosso exemplo:
INSERT INTO Funcionario VALUES
(10001,'1953-09-02','Georgi','Facello','M','1986-06-26'), (10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21'), (10003,'1959-12-03','Parto','Bamford','M','1986-08-28') Alternativa CERTA.
e) Exatamente! Se dizemos que um atributo é NOT NULL, na hora de inserir um registro na tabela, esse atributo deve ter um valor atribuído. Como nada foi dito a respeito de valor padrão, a consulta necessariamente vai ter que determinar algum valor para o atributo. CERTA
Gabarito: B
Mais alguns operadores lógicos
Vamos apresentar mais alguns operadores lógicos que podem aparecer nas cláusulas WHERE das nossas consultas. Eles são bem simples, então não desanime agora! Vamos lá:
O NOT é a negação. Se o valor de p é Verdadeiro, o valor de NOT(p) vai ser Falso, simples assim. Veja que, ao contrário do AND e do OR, o NOT é um operador unário, pois ele é aplicado a uma só proposição, não duas. O AND e o OR são operadores binários.
Veja que as duas consultas a seguir são equivalentes:
SELECT *
FROM Funcionario WHERE NOT(sexo = 'M') SELECT *
FROM Funcionario WHERE sexo <> 'M'
Dizer que o funcionário não tem o sexo igual a ‘M’ é a mesma coisa que dizer que ele tem o sexo diferente de ‘M’.
Ambas as consultas trarão todos os funcionários que não têm o sexo = ‘M’. Por eliminação, podemos dizer que elas trarão somente as mulheres que trabalham na nossa empresa.
BETWEEN
O operador BETWEEN, que quer dizer “entre” em inglês, retorna todos os valores entre dois valores quaisquer.
Veja:
SELECT *
FROM Funcionario
WHERE data_nascimento BETWEEN '1960-01-01' AND '1960-12-31'
Essa consulta irá retornar todos os funcionários cujo nascimento ocorreu entre 01/01/1960 e 31/12/1960, incluindo essas datas dos extremos. Ele é equivalente a fazer o seguinte:
SELECT *
FROM Funcionario
WHERE data_nascimento >= '1960-01-01' AND data_nascimento <= '1960-12-31'
IN
O operador IN nos ajuda a trazer os registros cujos atributos se encaixem em qualquer um dos valores de uma lista.
Por exemplo, a consulta a seguir retorna todos os funcionários que tenham sobrenome igual a Silva, Oliveira ou Queiroz:
SELECT *
FROM Funcionario
WHERE sobrenome IN ('Silva', 'Oliveira', 'Queiroz')
Veja que a consulta é equivalente a fazer vários “OR”:
SELECT *
FROM Funcionario
WHERE sobrenome = 'Silva' OR sobrenome = 'Oliveira' OR sobrenome = 'Queiroz'
No lugar de se listar todos os valores possíveis para o atributo que está sendo comparado com a lista, o comando IN pode ser usado também com uma subconsulta, que é uma consulta SQL que aparece dentro de outra. Nesse caso, a subconsulta deve retornar somente uma coluna, para que seus valores possam ser comparados com o atributo que vem antes do IN.
Veja o exemplo:
SELECT * FROM Vendas
WHERE idVendedor IN (SELECT idFuncionario FROM Funcionario
WHERE Cidade = 'Recife')
A subconsulta entre parênteses irá retornar uma coluna contendo todos os valores de idFuncionario dos funcionários que são da cidade do Recife. Assim, a consulta principal irá, para cada registro da tabela Vendas, verificar se o campo idVendedor tem um valor igual a algum dos valores dessa coluna retornada pela subconsulta.
Se o valor estiver nessa lista, o registro será retornado. Na prática, serão retornadas todas as vendas cujo vendedor é um funcionário da cidade do Recife.
Note somente que a consulta produziria um erro se fossem retornados múltiplos campos na subconsulta. Por exemplo:
SELECT * FROM Vendas
WHERE idVendedor IN (SELECT idFuncionario, Nome FROM Funcionario
Nesse caso, o SGBD iria ficar sem saber se compara idVendedor com os valores de idFuncionario ou com os valores de Nome, o que levaria a um erro.
LIKE
Já o operador LIKE é muito utilizado para fazer consultas em cadeias de caracteres. Às vezes faz sentido comparar um atributo em relação a uma parte de outro. Veja que o operador LIKE, em conjunção com o caractere %, nos ajuda a fazer isso.
Imagine que um funcionário do RH quer extrair uma lista de todas as funcionárias cujo nome é Michele. Contudo, ele sabe que há diversas variações para esse nome, como Michelle, Michelly, etc. Então, esse funcionário terá que construir uma consulta que retorne todas as funcionárias cujo nome começa com a cadeia de caracteres “Michel”:
SELECT *
FROM Funcionario
WHERE nome LIKE 'Michel%' AND sexo = 'F'
Esse % é um marcador que quer dizer “qualquer coisa”, ele pode estar presente em qualquer posição dentro da cadeia de caracteres e indica que ali tem um conjunto qualquer de caracteres, até mesmo nenhum.
Veja que o padrão 'a%b' pode abarcar as seguintes possibilidades, por exemplo:
➢ 'ab'
➢ 'a2116a51$$@#!6!b'
➢ 'a b'
Além do %, temos outro wildcard (como são chamados esses marcadores em inglês) interessante, o ‘_’. Esse caractere indica que, naquela posição, existe somente um caractere qualquer. Portanto, a consulta:
SELECT *
FROM Funcionario
WHERE nome LIKE 'Michel_' AND sexo = 'F'
Poderia retonar as funcionárias Michele, Michely, etc., mas nunca Michelle, pois seria necessária a inclusão de dois caracteres onde o padrão só comporta um.
IS NULL/IS NOT NULL
Os comandos IS NULL ou IS NOT NULL permitem verificar se determinado valor é nulo ou não. Considere o comando a seguir:
SELECT *
FROM Funcionario
WHERE data_contratacao IS NULL
Essa consulta iria retornar todos os funcionários cujo valor para o atributo data_contratacao é nulo.
Alternativamente, poderíamos trazer os funcionários para os quais o atributo não é nulo:
SELECT *
FROM Funcionario
WHERE data_contratacao IS NOT NULL
Pronto! Com isso terminamos a nossa parte da aula a respeito dos operadores lógicos da linguagem SQL. Se você ainda não fez alguma pausa, faça agora pois iremos introduzir um assunto um pouco mais complexo.
Junções (joins)
As consultas que vimos até agora só incluíam uma tabela, a Funcionario. Contudo, para que os bancos de dados sejam instrumentos poderosos para consultas e relacionamentos entre os dados, é necessário dar suporte à extração de dados de múltiplas tabelas de uma vez.
Antes de começarmos...
Para introduzir os comandos JOIN, vamos primeiro trazer uma nova tabela ao nosso modelo. A tabela de Salários.
O comando de criação dela é o seguinte:
CREATE TABLE Salario (
idFuncionario int(11) NOT NULL, salario_anual int(11) NOT NULL, data_inicio date NOT NULL, data_fim date NOT NULL,
CONSTRAINT PK_Salario PRIMARY KEY (idFuncionario, data_inicio),
CONSTRAINT FK_Salario_Funcionario FOREIGN KEY (idFuncionario) REFERENCES Funcionario (idFuncionario)
)
Opa! Temos algumas novidades aqui. Veja que temos uma nova restrição, ou constraint, cujo comando contém os termos FOREIGN KEY. Veja que se trata de uma chave estrangeira. A sintaxe desse comando é a seguinte:
Observamos, então, que está sendo criada uma chave estrangeira em Salario, mais especificamente no atributo idFuncionario, que referencia o atributo de mesmo nome (idFuncionario) na tabela Funcionario. Ou seja, há um relacionamento 1:N de Funcionario para Salario. Um funcionário da empresa pode ter múltiplos salários ao longo do tempo, cada um delimitado por sua data_inicio e data_fim de vigência.
Por exemplo, se o funcionário de id = 10.001 é contratado no dia 22/06/2001 e um ano depois, em 22/06/2002, recebe um reajuste, ficando com esse salário vigente até o momento em que os dados foram extraídos, veja como seriam seus registros em Salario:
Entendido? Para seguirmos em frente para os joins, gostaria que você observasse também no comando de criação da tabela que a chave primária de Salario é composta pela chave estrangeira (idFuncionario) e por um atributo próprio (data_inicio), indicando tratar-se de uma entidade fraca de Funcionario.
Inner join
As operações de join visam combinar os registros de duas (ou mais) tabelas ao utilizar valores que elas têm em comum. Por exemplo, poderíamos unir Funcionario e Salario pelo atributo idFuncionario. Assim, para um registro de funcionário cujo idFuncionario é igual a x, retornaremos todos os registros de Salário que têm também o atributo idFuncionario igual a x.
A representação gráfica dessa operação é mais ou menos essa:
Para ilustrar melhor, vamos construir um join entre as duas tabelas acima.
Imagine que queremos criar um relatório com nome, sobrenome e data de nascimento de cada funcionário, juntamente com seus salários ao longo do tempo. Só que os atributos nome, sobrenome e data_nascimento estão na tabela Funcionario, enquanto que os valores e datas de seus salários estão na tabela Salario. Precisamos combinar essas duas tabelas para fazer o nosso relatório.
A consulta que utilizaremos para fazer isso será a seguinte:
SELECT
f.idFuncionario ,f.nome
,f.sobrenome ,f.data_nascimento ,s.idFuncionario ,s.salario_anual ,s.data_inicio ,s.data_fim FROM Funcionario f
INNER JOIN Salario s ON f.idFuncionario = s.idFuncionario
Veja que o INNER JOIN, a partir de uma cláusula inserida após o comando ON, junta os registros das duas tabelas que satisfaçam determinadas condições. No nosso caso, a condição apresentada é f.idFuncionario = s.idFuncionario. Ou seja, a consulta vai unir todos os registros de Funcionario e Salario para os quais a igualdade acima seja verdadeira!
Repare que “nomeamos” as tabelas Funcionario de f e Salario de s. Isso ocorre com frequência nas sequências que contêm junções, já que precisamos diferenciar atributos de mesmo nome, mas que vêm de tabelas diferentes.
Também poderíamos utilizar o nome original das tabelas para esse fim: Funcionario.idFuncionario = Salario.idFuncionario.
Assim, o resultado da consulta vai ser do tipo:
Veja que os quatro atributos da esquerda, com o cabeçalho em branco, são da tabela Funcionario. Já os atributos da direita, com o cabeçalho destacado com fundo azul, são originalmente da tabela Salario. Perceba como, para cada registro, o idFuncionario de Funcionario é igual ao valor de idFuncionario em Salario.
A operação é chamada de “inner” join porque ela traz somente os registros do “meio”, da interseção entre as duas tabelas. Veja a representação visual a seguir:
Figura: Diagrama de Venn representando um INNER JOIN (Wikipediai)
Os registros retornados, assim, não são nem de Funcionario, nem de Salario, mas da junção das duas. Repare na imagem que traz os resultados da consulta que, como um mesmo Funcionario tem várias correspondências na tabela Salario, seus atributos, como nome, sobrenome e data_nascimento irão se repetir a cada ocorrência da junção.
Left (outer) join
Como vimos acima, o inner join só retorna os registros que satisfaçam a igualdade. Tudo bem! Agora vamos falar sobre o LEFT JOIN, ou LEFT OUTER JOIN. Essa operação de junção é muito parecida com a primeira, a diferença é que vamos trazer, além da interseção entre as duas tabelas, aqueles registros da tabela da esquerda que não têm correspondência com os da direita.
Figura: Diagrama de Venn representando um LEFT JOIN (Wikipediaii)
Mas o que isso quer dizer? Imagine que existam funcionários sem salário fixo. Ou seja, aqueles que ganham comissão por vendas ou percebem alguma outra forma de remuneração. Eles não terão um salário registrado na tabela Salario, mas estarão normalmente registrados na tabela de Funcionario.
Se o chefe que solicitou o relatório da consulta anterior quiser ver também esses Funcionarios que não possuem um salário correspondente, poderá utilizar o LEFT JOIN, ao contrário do INNER:
SELECT
f.idFuncionario ,f.nome
,f.sobrenome ,f.data_nascimento ,s.idFuncionario ,s.salario_anual ,s.data_inicio ,s.data_fim FROM Funcionario f
LEFT JOIN Salario s ON f.idFuncionario = s.idFuncionario
Essa consulta, como já adiantamos, retornará também aqueles registros da tabela anterior, a da esquerda, que não possuem equivalência com registros da outra tabela. Veja que quase todos os registros do resultado a seguir encontraram correspondência, mas o último registro, o de José da Silva, não possui um registro associado na tabela Salario:
Isso não o impediu de ser retornado! Contudo, veja que, como esse registro não tem correspondência em Salario, os atributos desta tabela “da direita” ficarão nulos no resultado da consulta.
Right (outer) join
De forma análoga, o RIGHT JOIN ou RIGHT OUTER JOIN traz os registros da tabela da direita que não possuem equivalência com os registros da tabela da esquerda:
Figura: Diagrama de Venn representando um RIGHT JOIN (Wikipediaiii)
SELECT
f.idFuncionario ,f.nome
,f.sobrenome ,f.data_nascimento ,s.idFuncionario ,s.salario_anual ,s.data_inicio ,s.data_fim FROM Funcionario f
RIGHT JOIN Salario s ON f.idFuncionario = s.idFuncionario
A consulta acima retornaria a interseção de Funcionario e Salario, mais aqueles registros de Salario que não possuem equivalência em funcionário.
Obs.: Na prática, no modelo de dados que construímos, isso não poderia ocorrer, já que vimos que Salario é entidade fraca de Funcionario, e portanto, há sempre um funcionário associado a um salário. Considere a consulta de exemplo acima apenas para fins didáticos.
Full (outer) join
Também temos o FULL OUTER JOIN, que é um pouco menos comum. O FULL JOIN traz todos os registros das duas tabelas que satisfaçam ou não à cláusula de junção. Por exemplo, considere que adicionamos a tabela Departamento ao nosso modelo já construído durante a aula. Os dados contidos nela são os seguintes:
Considere também que a nossa empresa está em um processo de reestruturação, portanto, alguns departamentos estão temporariamente sem nenhum gerente associado. Assim, observe na tabela acima que três departamentos têm o valor NULL na chave estrangeira idGerente, que referencia a tabela Funcionario.
Assim, vamos construir uma consulta que nos retorne todo um conjunto de funcionários da empresa e todos os departamentos, relacionando os departamentos que possuem gerente com os funcionários que os gerenciam. A nossa consulta vai ser a seguinte:
SELECT f.nome ,f.sobrenome
,d.nome_departamento AS departamento_gerenciado FROM Departamento d
FULL JOIN Funcionario f ON d.idGerente = f.idFuncionario
Veja os primeiros resultados dessa consulta:
Veja que os resultados de números 4, 6 e 8 são de departamentos que não possuem gerente. Já os registros 1, 2, 3, 5, 7 e 9 correspondem a registros em que houve correspondência entre as duas tabelas. Por fim, os demais registros, de 10 a 15, são de funcionários que não são gerentes de nenhum departamento.
Assim, podemos representar o FULL JOIN pelo seguinte diagrama de Venn, em que todas as áreas estão preenchidas. Por isso mesmo chamamos essa junção de FULL, ou seja, cheia ou completa. Veja a relação entre as cores das áreas do diagrama e os registros destacados no parágrafo anterior.
Figura: Diagrama de Venn representando um FULL JOIN (Wikipediaiv)
Você deve ter percebido uma novidade nesse trecho de código acima: o comando ASpermite renomear as colunas exibidas no resultado da consulta. A coluna original na tabela permanece com o mesmo nome, mas o resultado da consulta será exibido de acordo com os nomes que foram aplicados. Esse nome atribuído a cada coluna é conhecido como alias.
Poderíamos utilizar esse AS também no FROM, pois ele já existe, mas está implícito. Por exemplo:
FROM Departamento AS d
FULL JOIN Funcionario AS f ON d.idGerente = f.idFuncionario
Cross join
O cross join é nada mais nada menos que o produto cartesiano entre duas tabelas. Ou seja, ele vai combinar todos os registros da primeira tabela com todos da segunda. Para que você visualize melhor, criei duas tabelas de exemplo bem simples: Tabela_A e Tabela_B. Veja a composição de cada uma a seguir:
Tabela A Tabela B
Agora veja o produto cartesiano, ou CROSS JOIN das duas:
Observe que cada valor de A está associado a todos os valores de B e vice-versa. Como estamos associando todos os valores das duas tabelas, temos que não existe cláusula ON no CROSS JOIN!
Aproveitei essa consulta simples do CROSS JOIN para introduzir mais um conceito para você, o comando ORDER BY . Esse comando, que fica no final da consulta, permite que se ordene os resultados baseados nos valores de algum atributo ou conjunto de atributos.
Observe acima que ordenamos os valores da consulta acima pelo campo valor_A, mas poderíamos também tê-lo ordenado pelo valor_B:
Repare que os valores colocados após o ORDER BY são, por padrão, ordenados de forma crescente. Para ordená-los de forma decrescente, colocamos o termo DESC após o atributo. Podemos ordenar os resultados das consultas por múltiplos atributos, cada um de forma crescente ou decrescente:
Acima, temos o resultado de uma consulta que ordena primeiro pelo valor_A de forma decrescente, e depois pelo valor_B de forma crescente.
Podemos explicitar quando estamos ordenando um atributo de forma crescente colocando o termo ASC. Contudo, como esse é o padrão implícito, não precisamos fazê-lo.
Aqui finalizamos nosso estudo a respeito dos múltiplos tipos de junção existentes. Lembre-se que os mais importantes para a sua prova são o INNER JOIN e o LEFT JOIN.
Algumas considerações sobre o SELECT Operações aritméticas
simplesmente trazê-los em seus valores originais. Por exemplo, é possível manipular o valor do salário de um funcionário de diferentes maneiras. Observe o registro a seguir da tabela Salario.
Suponha que esse funcionário do resultado acima está tendo um ótimo desempenho na empresa. Por isso, seu chefe está considerando recompensar financeiramente o seu esforço. Contudo, a empresa está num momento financeiro difícil e precisa avaliar com bastante cuidado para não dar um aumento que não pode pagar. Assim, o chefe precisa avaliar as seguintes possibilidades:
1) Dar um bônus anual de R$ 2.000 ao funcionário 2) Aumentar o salário do funcionário em 4%
3) Dar dois bônus semestrais de 2,5% do salário
Ao invés de utilizar a calculadora, o chefe pode simplesmente fazer uma simulação na base de dados utilizando operadores aritméticos em uma consulta SQL. Vejamos:
SELECT idFuncionario
,salario_anual AS SalarioAtual ,salario_anual + 2000 AS ComBonus
,salario_anual + (salario_anual * 0.04) AS AumentoPercentual ,salario_anual + 2 * (salario_anual * 0.025) AS BonusSemestral FROM Salario
WHERE idFuncionario = 10002 AND data_inicio = '2001-08-02'
Os resultados dessa consulta são os seguintes:
são realizadas primeiro, antes das somas e subtrações. Assim como nas equações e nos operadores lógicos da própria linguagem SQL, podem ser usados parênteses para alterar a ordem de precedência das operações.
Além dos operadores de soma (+) e multiplicação (*) mostrados acima, podem ser usados os operadores de divisão (/) e subtração (-), bem como o modulo (%). Podemos também realizar operações entre os valores de múltiplos atributos distintos ao invés de valores fixos, algo como:
SELECT atributo1 + atributo2 – atributo3 * atributo4 FROM Tabela
Essas operações são realizadas a nível de linha. Ou seja, a cada linha ou registro do banco de dados, são considerados os valores dos atributos daquele registro. Assim, a consulta acima irá fazer o cálculo atributo1 + atributo2 – atributo3 * atributo4 para cada um dos registros da Tabela e retornar um resultado para cada um deles.
Obs.: Muito cuidado com valores nulos. Uma operação em nível de registro com um valor NULL sempre retornará NULL. Ou seja: 2 + NULL = NULL, 2* NULL = NULL, 2 – NULL = NULL, e assim sucessivamente.
Uma breve curiosidade a respeito de tipos de dados: veja que os resultados nomeados AumentoPercentual e BonusSemestral foram convertidos em números decimais. Isso acontece porque os números inteiros, após serem envolvidos em uma operação matemática com algum decimal, são automaticamente convertidos pelo SGBD.
UNION e UNION ALL
É possível unir os resultados de duas ou mais consultas diferentes através dos comandos UNION e UNION ALL.
Ambos têm a mesma função, com uma pequena diferença. Veja a consulta a seguir, que traz todos os adultos que existem em uma determinada base de dados:
SELECT Nome, Sexo FROM Adultos
Nome Sexo
José da Silva M Joaquim Xavier M Maria da Silva F
Agora veja a consulta sobre a tabela análoga Crianças, que contem as pessoas jovens de um determinado banco de dados:
SELECT Nome, Sexo FROM Crianças
Nome Sexo
Valentina Lopes F Maria da Silva F Enzo Gomes M
Imagine agora que queremos unir os resultados dessas duas consultas em uma só, de modo a obtermos uma grande lista com adultos e crianças que populam o banco de dados. Não faz sentido fazer joins, pois não queremos unir os atributos das duas tabelas, nosso objetivo aqui é realizar uma união entre os registros produzidos pelas duas consultas.
Assim, podemos utilizar o comando UNION ALL entre as duas consultas:
SELECT Nome, Sexo FROM Adultos UNION ALL
SELECT Nome, Sexo FROM Crianças
O resultado iria ser a combinação dos resultados das consultas:
Nome Sexo
José da Silva M Joaquim Xavier M Maria da Silva F Valentina Lopes F Maria da Silva F Enzo Gomes M
Perceba, contudo, que temos uma criança que tem o mesmo nome e sexo de um adulto. Veja os registros destacados em laranja na tabela acima.
Claro que nesse caso faz sentido que se repitam mesmo, pois são pessoas distintas. Contudo, às vezes é interessante que a união dessas duas ou mais consultas não traga resultados repetidos. Para isso, utilizamos o comando UNION, sem o ALL, que remove as tuplas duplicadas do resultado:
SELECT Nome, Sexo FROM Adultos UNION
SELECT Nome, Sexo FROM Crianças
Nome Sexo
José da Silva M Joaquim Xavier M Maria da Silva F Valentina Lopes F Enzo Gomes M
Uma última observação a respeito do UNION e do UNION ALL é que os tipos das colunas das duas consultas devem ser compatíveis para que possamos realizar a união. Por exemplo, assumindo que Idade é um atributo numérico e Nome é um atributo literal, temos que a consulta a seguir certamente produziria um erro devido à mistura dos tipos dos dados:
SELECT Idade, Nome FROM Adultos UNION
SELECT Nome, Idade FROM Crianças
Entretanto, os nomes dos atributos entre as duas consultas podem até ser diferentes, caso em que o resultado irá reter os nomes dos atributos da primeira consulta.
Agrupamentos – A cláusula GROUP BY
Para fechar nossa aula, vou te mostrar como se agrupa dados em consultas. ”Como assim, professor?” É muito simples. Você tem visto que todas as consultas que realizamos até agora trazem registro a registro da tabela selecionada, não é?
Às vezes fizemos filtragens através do WHERE, outras vezes usamos os joins para combinar diferentes tabelas, mas o fato é que sempre retornamos todas as linhas das tabelas resultantes da nossa consulta (após o FROM e o WHERE).
Já as consultas agrupadas através do GROUP BY vão sumarizar os registros, agregando valores.
não nos interessa ver informações no nível de funcionário, vamos dizer que queremos um nível maior de agregação das nossas consultas. Por exemplo, podemos querer saber quantos funcionários de cada sexo temos na empresa.
Para isso, utilizaremos a seguinte consulta:
SELECT f.sexo
,COUNT(f.idFuncionario) AS qtdFuncionarios FROM Funcionario f
GROUP BY f.sexo
Veja que temos duas novidades nessa consulta, o comando GROUP BY e a função COUNT. O primeiro serve justamente para agrupar os funcionários pelo valor de um atributo ou de um conjunto de atributos. Na consulta acima, estamos agrupando os registros da tabela Funcionario por sexo.
Já o COUNT é uma função de agregação, ou seja, é uma função especial que opera em cima de um conjunto de registros, retornando um valor só por registro agregado. Soou estranho? Vai ficar claro quando você vir o resultado da consulta:
O COUNT é uma função de agregação que simplesmente conta os registros da tabela. Então veja que a consulta acima agrupa os registros de Funcionario por sexo através do GROUP BY, enquanto que o comando COUNT agrega os registros desses grupos, contando quantos são do sexo masculino e quantos são do sexo feminino.
Veja que se rodarmos simplesmente um COUNT sem nenhum agrupamento, ele vai nos retornar a quantidade de registros total na tabela que estamos consultando:
Entendido? Seguindo em frente, para apresentar as outras funções de agregação mais comuns, vamos trazer
Opa! Peraí! Veja que o número de funcionários aumentou muito. Você saberia dizer o motivo? Acontece que, quando fazemos o join de Funcionario com Salario, passamos a trabalhar nem com uma tabela nem com a outra, mas com a junção das duas. Lembra?
Ou seja, quando estamos fazendo uma contagem (COUNT) de ocorrências do campo idFuncionario na consulta, o resultado será calculado com base no resultado da junção, não com a tabela original Funcionario. Assim, como o resultado da junção traz várias linhas por funcionário, correspondentes aos vários salários de cada funcionário ao longo do tempo, a nossa contagem de ocorrências também vai aumentar na mesma razão.
Superado este ponto, vamos voltar com o nosso GROUP BY e fazer mais algumas consultas:
1) O salário médio por gênero
2) O maior salário que ocorre nos funcionários, também segregado por gênero 3) Da mesma maneira, vamos calcular o menor salário por gênero
4) A soma de todos os registros de salario dos funcionários de cada gênero Veja a consulta que utilizaremos:
SELECT f.sexo
,AVG(s.salario_anual) AS SalarioMedio ,MAX(s.salario_anual) AS MaiorSalario ,MIN(s.salario_anual) AS MenorSalario ,SUM(s.salario_anual) AS TotalSalarios FROM Funcionario f
INNER JOIN Salario s ON f.idFuncionario = s.idFuncionario GROUP BY f.sexo
Os resultados obtidos são os seguintes:
SUM soma os valores do atributo.
Para finalizar, veja que podemos utilizar mais de um atributo no GROUP BY. Vamos agrupar os salários também por ano em que eles entraram em vigor:
SELECT f.sexo
,YEAR(s.data_inicio) AS Ano
,AVG(s.salario_anual) AS SalarioMedio ,MAX(s.salario_anual) AS MaiorSalario ,MIN(s.salario_anual) AS MenorSalario ,SUM(s.salario_anual) AS TotalSalarios FROM Funcionario f
INNER JOIN Salario s ON f.idFuncionario = s.idFuncionario GROUP BY f.sexo, YEAR(s.data_inicio)
ORDER BY f.sexo, YEAR(s.data_inicio)
A função YEAR extrai o ano de um atributo do tipo data. Não se preocupe muito com ela agora, só veja que podemos colocar vários atributos no GROUP BY.
Veja uma parte do resultado dessa consulta:
atributos no SELECT devem estar em uma de duas situações possíveis - ou dentro da cláusula GROUP BY, ou dentro de uma função de agregação.
Veja, isso é bastante lógico. Não estamos trabalhando mais registro a registro, e sim com conjuntos agregados de dados, certo?. Nessas circunstâncias, se colocarmos um atributo solto, o SGBD simplesmente não saberia como retornar o resultado da consulta.
Vamos voltar ao exemplo da contagem de funcionários para ilustrar nosso argumento. Repare no atributo nome destacado em vermelho:
SELECT f.sexo ,f.nome
,COUNT(f.idFuncionario) AS qtdFuncionarios FROM Funcionario f
GROUP BY f.sexo
Ao executar a contagem anteriormente, vimos que existiam mais de 100 mil funcionários de cada sexo. Assim, quando agrupamos os funcionarios por sexo, estamos efetivamente trabalhando com dois grandes grupos de mais de cem mil registros cada.
Agora olhe para o trecho de código logo acima e imagine, qual dos cem mil nomes para cada sexo o SGBD iria trazer nessa consulta? Não há nenhuma instrução clara. A consulta tenta extrair um atributo no nível de detalhe de registro, mas em uma consulta que trabalha com os dados em nível de agregação por sexo. Entendeu? Dessa forma, o SGBD iria acusar um erro e mandar o usuário incluir o f.nome em um GROUP BY ou então em uma função de agregação.
Podemos utilizar o comando DISTINCT também dentro do COUNT, caso em que as ocorrências de valores duplicados serão desconsideradas. Tome a tabela a seguir:
Devido à repetição do valor 2, temos que:
COUNT(valor) = 4
COUNT(DISTINCT valor) = 3
(CESPE – EBSERH – 2018)
Com relação a banco de dados, julgue o item seguinte.
Na SQL (structured query language), existem funções de agregação com diferentes capacidades; como, por exemplo, a função AVG, que é responsável pelo cálculo da média dos valores de determinado campo.
RESOLUÇÃO:
É isso mesmo. As diversas funções de agregação previstas na linguagem SQL permitem agregar registros, retornando uma só linha de resultado para a consulta ou mesmo uma linha para cada grupo definido, quando utilizadas em conjunção com a cláusula GROUP BY.
Gabarito: C
Funções de agregação e valores NULL
Um detalhe importante a respeito das funções de agregação é que elas “passam por cima” dos valores nulos, como se eles não existissem.Veja a seguinte tabela de exemplo:
ValorA ValorB
1 NULL
NULL 2
4 3
Agora vamos rodar o seguinte comando:
SELECT SUM(ValorA), COUNT(ValorA) FROM Tabela
Veja nos resultados que os nulos não atrapalharam! As funções de agregação são capazes de operar sobre a tabela como se eles não estivessem lá:
SELECT SUM(ValorA + ValorB), COUNT(ValorA + ValorB) FROM Tabela
Veja:
Achou estranho? Não ache. Isso acontece porque a operação a nível de registro – a que está dentro dos parênteses - ocorre primeiro, antes da agregação. Assim, essa consulta primeiro soma cada um dos ValorA com cada um dos ValorB, para só depois agregar esse resultado.
1) Primeiro fazemos a soma ValorA + ValorB
ValorA ValorB ValorA + ValorB
1 NULL = NULL
NULL 2 = NULL
4 3 = 7
2) Quando fizermos o SUM(ValorA + ValorB), os dois nulos serão ignorados e o valor será dado pela “soma”
agregada somente do último valor, 7.
3) O COUNT também só irá considerar os valores não nulos, então, da mesma maneira que o SUM, só irá operar sobre o último valor, 7. Como só há uma ocorrência de valores, o resultado é 1.
Recomendo que você que assista ao final da parte teórica da nossa vídeo aula. Lá resolvi uma questão da banca FCM que é bem interessante para elucidar esse último detalhe.
HAVING
Para finalizar a nossa aula, também preciso mostrar para você que podemos filtrar os agrupamentos através dos resultados de uma função de agregação. Complicou demais? Calma! Veja o exemplo:
SELECT f.sexo
,YEAR(s.data_inicio) AS Ano
,AVG(s.salario_anual) AS SalarioMedio ,MAX(s.salario_anual) AS MaiorSalario ,MIN(s.salario_anual) AS MenorSalario ,SUM(s.salario_anual) AS TotalSalarios
FROM Funcionario f
INNER JOIN Salario s ON f.idFuncionario = s.idFuncionario
GROUP BY f.sexo, YEAR(s.data_inicio) HAVING AVG(s.salario_anual) >= 60000 ORDER BY f.sexo, YEAR(s.data_inicio)
O importante para você notar na consulta acima é a cláusula HAVING. Ao contrário do WHERE, que filtra registro a registro os que correspondem a uma determinada expressão lógica, o HAVING vai filtar os grupos que satisfizerem determinada condição representada por funções de agregação.
O exemplo acima filtrará os grupos cuja média (dada pelo AVG) dos salários anuais seja igual ou maior que R$
60.000. O resultado será o seguinte:
Veja que, assim, nenhum dos grupos retornados (representados pelo sexo e pelo ano) possui salário médio menor que R$ 60.000!
Correspondência com operadores relacionais
Para finalizar, é interessante que façamos uma comparação da linguagem SQL com os operadores relacionais já apresentados como parte do modelo relacional teórico.
De modo geral, as bancas costumam considerar que as tabelas contidas na cláusula FROM e seus joins correspondem à realização da operação relacional do produto cartesiano, já que há o cruzamento entre as tuplas das tabelas envolvidas.
Em seguida, vemos que há uma operação de seleção das tuplas desejadas, efetivada através do comando WHERE. Por fim, as consultas realizam uma seleção dos atributos/colunas a serem retornados no produto da consulta, através da operação
filtrar as tuplas a serem retornadas, sem os comandos explícitos JOIN e suas cláusulas ON. Veja:
SELECT <atributo1>, <atributo2>, ... , <atributo n>
FROM <tabela1>, <tabela2>, ... , <tabela n>
WHERE <condição1>, <condição2>, ... , <condição n>
Observe que as operações relacionais são mais facilmente traduzidas nessa sintaxe. O SELECT faz a projeção das colunas, o FROM especifica as tabelas envolvidas e o WHERE realiza a filtragem das tuplas através de uma série de critérios. Hoje em dia, com a sintaxe o padrão ANSI-92, se utiliza os comandos JOIN e suas respectivas cláusulas ON, que compartilham o papel de filtrar os registros com as cláusulas definidas no WHERE. Entendido?
Ah! Antes de terminarmos, gostaria de chamar a atenção para o fato de que a operação de seleção não é realizada pelo operador SELECT, apesar do nome. Alguns autores consideram infeliz essa nomenclatura justamente por causar esse tipo de confusão. As bancas, claro, não perdem tempo e costumam explorar essa distinção para pegar os candidatos desavisados (o que não é o seu caso ☺).
Questões comentadas pelo professor
1.
(CESPE – PF – 2018 - ADAPTADA)As informações de um departamento devem ser organizadas e armazenadas conforme a estrutura mostrada no diagrama precedente. Para isso, serão utilizados os comandos DDL a seguir.
Tendo como referência as informações apresentadas, julgue o próximo item.
Assertiva: A seguir, são apresentadas as expressões SQL corretas para inserir na tabela organograma as informações constantes do diagrama apresentado.
