Prof. Arthur Mendonça

(1)

Aula 02

Conhecimentos de Bancos de Dados para a CGU

Prof. Arthur Mendonça

(2)

Sumário

SUMÁRIO 2

INTRODUÇÃO 3

TEORIA DA AULA 4

DDL E DML–CRIAÇÃO DE TABELAS E INSERÇÃO DE DADOS 4

DDL – Criação de tabelas 4

Inserindo dados nas tabelas (DML) 7

DQL–LINGUAGEM DE CONSULTA 9

Operadores lógicos 11

Mais alguns operadores lógicos 17

Junções (joins) 20

Algumas considerações sobre o SELECT 30

Agrupamentos – A cláusula GROUP BY 31

QUESTÕES COMENTADAS PELO PROFESSOR 38

LISTA DE QUESTÕES COMENTADAS 64

GABARITO 76

(3)

Introdução

Olá! Hoje vamos ver um assunto que é novidade para muitos. Vamos falar sobre a linguagem SQL, que é a linguagem de programação que permite trabalhar com os bancos de dados relacionais.

Linguagem SQL

Antes de começar a teoria propriamente dita, um aviso! Essa é uma aula que não faz muito sentido ser decorada, ela tem que ser compreendida. Tenha em mente que você não precisa saber escrever um comando SQL de forma perfeita ao final do assunto, mas sim entender os resultados que alguns tipos de consultas simples produzem.

Sugiro que não se preocupe em anotar os mínimos detalhes de cada uma das consultas que vamos apresentar. Ao contrário, foque em pegar o espírito para ser capaz de reconhecer os conceitos apresentados quando for resolver questões, ok?

Vamos começar!

@analisedeinformacoes

(4)

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, pois é o objetivo da cobrança em provas da sua área. Afinal, o foco de cair SQL no seu concurso tem mais a ver com a análise de dados do que com a criação ou manipulação de objetos e registros. Em menor grau, vamos ver alguns comandos DDL e DML que costumam aparecer em prova.

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

(5)

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. Imagino que sua prova foque na cobrança de consultas para extrair dados dessas tabelas. Contudo, 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.

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,

(6)

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 DDL. Não vale a pena explicar o porquê aqui, mas acredite em mim quando digo que ele é DML! J

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.

Os principais tipos que utilizaremos na nossa aula são os seguintes:

Tipo Descrição

date Para datas

datetime Data e hora

char Cadeia de caracteres de tamanho fixo

(7)

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.

Para a sua prova, basta saber reconhecer a natureza de cada tipo de dados, conforme a tabela acima. Vamos em frente!

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

(8)

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. J 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

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, que deveria receber um número inteiro (destacado em azul acima), a consulta certamente produziria um erro de tipo!

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.

(9)

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. Não vamos explorá-los aqui para não deixar o curso muito pesado. Esses comandos não devem ser alvo de cobrança direta na sua prova, mas lembre-se que eles são do tipo DML!

Bom, já dissemos o bastante a respeito da criação de tabelas e inserção de dados. Hora de falar sobre o que deve ser cobrado de forma mais intensa na sua prova, que é a extração dos dados através de consultas:

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:

(10)

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.

Não estranhe nem se preocupe muito com esses detalhes!

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:

= (ou ==) igual a <> diferente de

> maior que < menor que

>= maior ou igual a <= menor ou igual a

(11)

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?

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.

Por exemplo, vamos tomar a consulta que já trouxemos como exemplo anteriormente:

SELECT nome

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

WHERE data_nascimento < '1960-01-01' AND sexo = 'M'

(12)

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

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

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

(13)

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;

2) Se somente s for verdadeira;

3) Se tanto r como s forem verdadeiras.

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 *

WHERE data_nascimento < '1960-01-01' AND sexo = 'M' OR data_nascimento < '1965-01-01' AND sexo = 'F'

Veja os resultados:

(14)

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 *

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

(15)

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 vai complementar o seu conhecimento teórico.

Após resolver, 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 de consulta baseados em SELECT.

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

(16)

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

(17)

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á:

NOT

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 *

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:

(18)

SELECT *

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 *

WHERE sobrenome IN ('Silva', 'Oliveira', 'Queiroz')

Veja que a consulta é equivalente a fazer vários “OR”:

SELECT *

WHERE sobrenome = 'Silva' OR sobrenome = 'Oliveira' OR sobrenome = 'Queiroz'

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 *

WHERE nome LIKE 'Michel%' AND sexo = 'F'

Esse % é um marcador que quer dizer “qualquer coisa”, ele pode estar presente em qualquer posição dentro

(19)

Ø '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 *

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.

(CESPE – ABIN – 2018)

Em LIKE '%BRASILIA,DF%', o recurso LIKE foi definido de forma incorreta, uma vez que a utilização da vírgula (,), sem a inclusão da palavra-chave ESCAPE, impedirá que o código seja executado.

RESOLUÇÃO:

Questão interessante, mas incorreta! A vírgula não é um dos wildcards que aprendemos, então ela não precisa de um ESCAPE.

O ESCAPE serve quando queremos utilizar uma das wildcards no meio do nosso padrão. Por exemplo, imagine que temos uma tabela chamada imposto, que contém os registros de tributos cobrados por um determinado ente, juntamente com suas alíquotas em formato de cadeia de caracteres.

Agora imagine que queremos ver quais são aqueles impostos que têm valores em torno de 15%, com uma casa decimal, ou seja, 15,1%, 15,2%, 15,3%, etc.

Poderíamos pensar no seguinte trecho de código:

SELECT * FROM imposto WHERE aliquota LIKE '15,_%'

Veja que a nossa intenção ao colocar o símbolo de percentual não é utilizar a wildcard %, que quer dizer “qualquer coisa”. Na verdade, esse percentual é realmente o símbolo de percentual pura e simplesmente. A única wildcard considerada na cadeia deveria ser o underline ( _ ).

Para utilizar o símbolo de percentual dessa maneira convencional, podemos definir um caractere de “escape”, que indica que o caractere seguinte a ele não deve ser enxergado como uma wildcard, mas como um caractere comum. Assim:

SELECT * FROM imposto WHERE aliquota LIKE '15,_/%' ESCAPE '/'

(20)

Veja agora que o caractere logo após a barra, que foi definida como nosso caractere de escape, vai ser encarado como um caractere integrante da cadeia como qualquer outro, não como wildcard. Assim, o nosso resultado pretendido será retornado corretamente.

Gabarito: C

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 *

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 *

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)

(21)

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:

(22)

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:

(23)

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 (Wikipediaⁱ)

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.

(24)

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 (Wikipediaⁱⁱ)

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.sobrenome

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:

(25)

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 (Wikipediaⁱⁱⁱ)

SELECT

,f.sobrenome

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

(26)

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:

(27)

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 (Wikipedia^iv)

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. Na verdade, ele já existe, mas está implícito. Por exemplo:

FROM Departamento AS d

FULL JOIN Funcionario AS f ON d.idGerente = f.idFuncionario

(28)

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

(29)

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 nossa base 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.

Antes de seguir para o próximo tópico, um pequeno detalhe! Os joins podem ser escritos através da cláusula WHERE.

Antigamente, quando não existia a sintaxe do INNER JOIN, se utilizava o seguinte código:

SELECT *

FROM Tabela1 a, Tabela2 b WHERE a.valor = b.valor

Veja que a vírgula separa as tabelas, enquanto que o WHERE faz as vezes do ON. Essa sintaxe é antiga e tem caído em desuso, mas algumas bancas insistem em cobrar de vez em quando. Não espero que seja o caso da sua prova, mas temos que estar

(30)

Algumas considerações sobre o SELECT

Dentro do SELECT podemos realizar inúmeras operações sobre os valores dos atributos, não precisamos 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:

As operações aritméticas são processadas de acordo com as equações matemáticas: as multiplicações e

(31)

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.

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.

Vamos tomar como exemplo, novamente, a empresa que temos discutido ao longo da aula. Agora imagine que 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

(32)

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 também os registros da tabela Salario através de um INNER JOIN para a nossa consulta:

Opa! Peraí! Veja que o número de funcionários aumentou muito. Você saberia dizer o motivo? Acontece que,

(33)

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 obtido são os seguintes:

Assim, temos que a função AVG dá a média, as funções MAX e MIN os valores máximos e mínimos, e a função 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

(34)

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, só veja que podemos colocar vários atributos no GROUP BY.

Veja uma parte do resultado dessa consulta:

Trouxe esse último exemplo também pra te explicar uma coisa: em uma consulta com GROUP BY, todos os 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

(35)

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.

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á:

Agora veja essa outra consulta a seguir. Você consegue imaginar qual será o resultado?

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

(36)

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

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:

(37)

Veja que, assim, nenhum dos grupos retornados (representados pelo sexo e pelo ano) possui salário médio menor que R$ 60.000!

* * *

Assim terminamos a nossa parte teórica de hoje!

Trouxe questões de diversas bancas para que você possa praticar bem até sabermos quem será a real organizadora do próximo concurso da CGU.

Bons estudos!

(38)

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.

A seguir, são apresentadas as expressões SQL corretas para inserir na tabela organograma as informações constantes do diagrama apresentado.

(39)

A questão parece complicada demais, mas não é. Veja que primeiro há a representação gráfica de um organograma da instituição e em seguida é criada a tabela que deve armazenar os dados a respeito dos componentes desse organograma.

É importante que você note que temos um autorrelacionamento na tabela que representa a hierarquia entre os setores. Esse relacionamento está implementado através da chave estrangeira fk_organograma. Aqui acredito que temos um erro de sintaxe, pois seria necessário especificar o atributo referenciado pela chave estrangeira. O trecho do comando correto seria:

CONSTRAINT fk_organograma FOREIGN KEY (pai) REFERENCES organograma (id)

De todo modo, como essa parte está no enunciado da questão, vamos abstrair esse problema e julgar somente as inserções realizadas:

Veja que já na primeira inserção temos um problema, pois tentamos inserir primeiro o item 101.2 – Assistente, que tem o atributo chave estrangeira pai referenciando o item de id = 2. Ora, se ainda não inserimos esse outro registro na tabela, como podemos referenciá-lo? Se trata de de um claro problema de integridade referencial!

Além desse claro problema, temos que as inserções geram um organograma distinto do representado na imagem.

O correto seria algo como o disposto a seguir.

INSERT INTO organograma VALUES (1, 'diretoria de execução', '101.5') INSERT INTO organograma VALUES (2, 'assistente', '101.2', 1)

INSERT INTO organograma VALUES (3, 'coordenação de imigração', '101.4', 1) INSERT INTO organograma VALUES (4, 'coordenação de controle', '101.4', 1) INSERT INTO organograma VALUES (5, 'coordenação de documentação', '101.4', 1) INSERT INTO organograma VALUES (6, 'setor de registros', '101.3', 3)

Obs.: Removi a lista de campos (id, descricao, cargo, pai) em nome da clareza do código. Veja que, como os valores estão sendo adicionados na mesma ordem com que foram definidos na tabela, não teríamos problemas de sintaxe!

Um detalhe é que o primeiro registro não vai ter mesmo valor para o atributo pai, já que está no topo da hierarquia representada. Nesse caso podemos simplesmente omiti-lo na inserção ou mesmo colocar diretamente o valor NULL, em nome da clareza.

Lembre-se que a chave estrangeira, ao contrário da primária, pode conter valores nulos, o que indica que a entidade não participa do relacionamento. No caso do autorrelacionamento, ela desempenha apenas um dos papeis, o de “pai” em relação a outros registros, mas não participa do relacionamento no papel de “filho” em momento algum.

Gabarito: E

2.

(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.

(40)

É 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

Tendo como referência o código SQL precedente, julgue os quatro itens a seguir.

3.

(CESPE – ABIN – 2018)

Na cláusula WHERE, a condição de seleção area = 'INTELIGENCIA' escolhe a tupla de interesse em particular na tabela funcionario, pois area é um atributo de funcionario.

RESOLUÇÃO:

A redação não é das melhores, mas o que o examinador quer dizer é que, ao utilizar o trecho WHERE area =

‘INTELIGENCIA’, o usuário está restringindo a consulta a uma tupla (ou a um conjunto de tuplas) de interesse, através da condição imposta ao valor do atributo area. Está correta!

Gabarito: C

4.

(CESPE – ABIN – 2018)

O código em apreço realiza uma consulta que mostra o nome dos funcionários da área de INTELIGENCIA e que têm, como parte do endereço, a cidade de BRASILIA,DF.

RESOLUÇÃO:

É isso mesmo! O comando LIKE permite utilizar wildcards como % e _ para representar valores diversos no casamento de padrões. No caso do código acima, ao colocar endereço LIKE ‘%BRASILIA,DF%’, o usuário quer que sejam retornados quaisquer registros cujo endereço contenha, em qualquer parte, a sequência de caracteres

‘BRASILIA,DF’.

Gabarito: C

5.

(CESPE – ABIN – 2018)

A palavra INTELIGENCIA está entre aspas simples por pertencer a um atributo, area, o qual tem o tipo de dados definido como caractere.

RESOLUÇÃO: