BANCO DE DADOS. Araújo Lima. Set / Araújo

(1)

Araújo ¹

BANCO DE DADOS

Araújo Lima Set / 2018

(2)

Índice

• 10.Linguagens de Consulta a Banco de Dados

CONSULTAS MAIS COMPLEXAS

(3)

Araújo ³

Consultas mais complexas ...

DML- Linguagem de Manipulação de Dados

func (mat, nom, sal, cdd)

• select * from func where cdd is null

exibe os atributos dos funcionários sem valor de código de departamento preenchido (NULL)

• select * from func where cdd is not null

exibe os atributos dos funcionários com código de

departamento preenchido (podendo ter qualquer valor inclusive zero ou espaço em branco) NULL - ausência de valor por desconhecimento ou por inexistência

(4)

Consultas mais complexas ...

mat nom sex sal cdd

100 Gil M 900.00 VEN

110 Eva F 1000.00 NULL

120 Rui M 900.00 ADM

select * from func where cdd is null -- retorna linha abaixo

110

(5)

Araújo ⁵

Consultas mais complexas ...

• select mat, nom, sal * 12 from func order by 3 desc

• select mat, nom, sal * 12 as salAnual from func order by 3 desc

• select mat, nom, sal * 12 as salAnual from func order by salAnual desc

exibe matrícula, nome e salário anual dos funcionários em ordem decrescente de salário anual (terceiro atributo)

(6)

Consultas mais complexas ...

100 Gil M 900.00 V

110 Eva F 1000.00 NULL

120 Rui M 900.00 A

select mat, nom, sal * 12 as salAnual from func order by 3 desc -- retorna linhas abaixo

mat nom salAnual

110 Eva 12000.00

(7)

Araújo ⁷

... Consultas mais complexas ...

• select * from func where cdd in (‘ven’,’pro’) exibe atributos dos funcionários cujo código do

departamento pertence ao conjunto dos elementos

“ven” e “pro”

• select * from func where cdd not in (‘ven’,’pro’)

atributos dos funcionários que não pertencem ao conjunto dos elementos “ven” e “pro”

(8)

... Consultas mais complexas ...

• select * from func

where mat between 150 and 500

exibe os atributos dos funcionários de valor de matrícula entre 150 e 500, incluindo os extremos

• select * from func

where mat not between 150 and 500

(9)

Araújo ⁹

... Consultas mais complexas ...

• select * from func where nom like ‘A_B%’

exibe os atributos dos funcionários que têm nome iniciado por A e a terceira letra do nome igual a B (ALBER,

ALBERTO, ALBA, ALBINA, ALBINO, AMBLER, ...)

• select * from func where nom not like ‘A_B%’

(10)

... Consultas mais complexas ...

• select * from func where nom like ‘%a’

exibe os atributos dos funcionários que têm nome

terminado em a (Francisco Lima, Laura, Maria, Diva, Eva, Lia, Sa)

• select * from func where nom not like ‘%a’

(11)

Araújo ¹¹

... Consultas mais complexas ...

• select * from func where nom like ‘A%’

exibe os atributos dos funcionários que têm nome iniciado com A (Ana Maria, Alves, Ari, Araujo)

• select * from func where nom not like ‘A%’

(12)

... Consultas mais complexas ...

DML- Linguagem de Manipulação de Dados func (mat, nom, sal, cdd)

• select distinct cdd from func

exibe os diferentes códigos de departamento existentes na tabela func (havendo 20 funcionários em pro, 5 em ven e 3 em adm, o resultado da consulta acima será 3 linhas):

adm pro ven

(13)

Araújo ¹³

... Consultas mais complexas ...

DML - Linguagem de Manipulação de Dados func (mat, nom, sal, cdd)

• select cdd, count(*), sum(sal), avg(sal) from func group by cdd having count(*) > 3

exibe para cada departamento com mais de 3

funcionários, o código, a quantidade de funcionários, a soma dos salários e a média salarial do departamento

(14)

... Consultas mais complexas ...

DML - Linguagem de Manipulação de Dados

• select count(*) as x from func where sal < 150

• select count(*) as y from func where sal >= 150

• select count(*) as z from func Seria possível z > x + y ?

Seria possível se houvesse algum funcionário com salário NULL (Mais problema com NULL)

• select * from func where sal is null

(15)

Araújo ¹⁵

... Consultas mais complexas ...

DML - Linguagem de Manipulação de Dados func (mat, nom, sex, sal, cdd)

• select count(*) from func where sex = ‘M’

• select dep, count(*) from func where sex = ‘M’ group by cdd -- qtd de homens por departamento

• select cdd, count(*) from func where sex = ‘M’ group by cdd having count(*) > 3

where - testa condição de linha (só homens)

having - testa condição de grupo (exibe só departamento que tenha mais de 3 homens)

(16)

... Consultas mais complexas ...

depa (cdd, dsd)

• select nom, sal, dsd from func f, depa d where f.cdd = d.cdd

exibe nome, salário e nome do departamento dos funcionários

junção (join) entre as tabelas func e depa, com base na

(17)

Araújo ¹⁷

... Consultas mais complexas ...

• select nom, sal from func

where sal > (select avg(sal) from func)

exibe nome, salário dos funcionários com salário superior à média salarial da empresa

subselect - select dentro de select; o que está entre parênteses é executado antes

(18)

... Consultas mais complexas ...

DML - Linguagem de Manipulação de Dado func (mat, nom, sal, cdd)

depa(cdd, dsd)

depe(mat, seq, nomDepe, dtNasc)

Exibir o total de dependentes dos funcionários do departamento de nome Vendas

select count(*) from depe where mat in (select mat from func where cdd in

(19)

Araújo ¹⁹

... Consultas mais complexas ...

DML - Linguagem de Manipulação de Dado func (mat, nom, sal, cdd)

depa(cdd, dsd)

Exibir o total de dependentes dos funcionários do departamento de nome Vendas

select count(*) from func, depa, depe

where func.cdd = depa.cdd and func.mat = depe.mat and dsd = 'Vendas’

-- resultado semelhante usando junção ao invés de subselect

(20)

... Consultas mais complexas ...

Exibir dados dos funcionários sem dependentes

• select * from func where mat not in

(select distinct mat from depe) OU

• select func.* from func left join depe

on func.mat = depe.mat where depe.mat is null

(21)

Araújo ²¹

... Consultas mais complexas ...

Exibir dados dos funcionários com salário maior que qualquer salário do departamento de código igual a “P”

• select * from func where sal >

any (select sal from func where cdd = ‘P’) ou

• select * from func where sal >

(select min(sal) from func where cdd = ‘P’)

(22)

... Consultas mais complexas ...

func (mat, nom, sal, match)

• select sub.nom, sub.sal, che.nom from func sub, func che where sub.match = che.mat

-- exibe nome e salário de cada funcionário e, na mesma linha, o nome do seu chefe

implementação de autorelacionamento 1: n

sub, che - alias ou nome de correlação

(23)

Araújo ²³

... Consultas mais complexas ...

DML - Linguagem de Manipulação de Dados func (mat, nom, sal, match)

select sub.nom, sub.sal from func sub, func che where sub.match = che.mat and sub.sal > che.sal

-- exibe nome e salário dos funcionários que ganham mais que seus respectivos chefes implementação de autorelacionamento 1: N

sub, che - alias ou nome de correlação

(24)

... Consultas mais complexas ...

F (cdf, nmf, cidf) -- Fornecedor P (cdp, nmp, peso, cidp)-- Produto

E (cdf, cdp, qtd) -- Embarque

select nmf from F where not exists (select * from P where exists

(select * from E where E.cdf = F.cdf and E.cdp = P.cdp))

- - fornecedor para o qual não exista produto que tenha embarque

- - exibe nome dos fornecedores que não forneceram nenhum produto

(25)

Araújo ²⁵

... Consultas mais complexas ...

F (cdf, nmf, cidf) -- Fornecedor P (cdp, nmp, peso, cidp)-- Produto E cdf, cdp, qtd) -- Embarque

select nmf from F where not exists (select * from P where not exists

- -fornecedor para o qual não exista produto que não tenha embarque - -exibe o nome dos fornecedores que forneceram todos os produtos

(26)

... Consultas mais complexas ...

• select * from func where sal =180 and sex = ‘M’

• select * from func where sal =180 or sex = ‘M’

• select * from func where sal =180 and sex <> ‘M’

• select * from func where sal !=180 and sex <> ‘M’

• select * from func where sal !=180 and sex != ‘M’

• select * from func where sal <> 180 and sex = ‘M’

(27)

Araújo ²⁷

... Consultas mais complexas ...

• INNER JOIN – resultado da operação, exibe

apenas as linhas em que haja igualdade entre o par de atributos (por exemplo, PK = FK)

• OUTER JOIN – resultado da operação exibe,

além das linhas em que PK = FK, também linhas de uma tabela sem correspondência na outra

tabela

(28)

... Consultas mais complexas ...

INNER JOIN ou simplesmente JOIN FUNC(mat, nom, cdd) DEPA(cdd, dsd)

select * from func join depa on func.cdd = depa.cdd /* Inner Join */

• Resultado exibe só os funcionários que estão alocados em algum departamento e apenas departamentos que têm pelo menos um funcionário alocado ( Inner join)

• select * from func, depa where func.cdd = depa.cdd /* produz mesmo resultado */

(29)

Araújo ²⁹

... Consultas mais complexas ...

LEFT / RIGHT OUTER JOIN

• select * from func left outer join depa on func.cdd = depa.cdd

• Resultado mostra todos os funcionários, mesmo os que não estão alocados em nenhum departamento

• select * from func right outer join depa on func.cdd = depa.cdd

• Resultado mostra todos os departamentos, mesmo os que não têm nenhum funcionário alocado

• A palavra outer pode ser suprimida

(30)

... Consultas mais complexas ...

FULL OUTER JOIN

• select * from func full outer join depa on func.cdd = depa.cdd

• Resultado mostra todos os funcionários, mesmo os que não estão alocados em algum departamento e todos departamentos, mesmo os que não têm nenhum funcionário alocado ( Full join)

• A palavra outer pode ser suprimida

(31)

Araújo ³¹

... Consultas mais complexas ...

CROSS JOIN

• select * from func cross join depa

• Resultado do comando acima é o produto

cartesiano das tabelas func X depa (para 8 linhas em func e 4 linhas em depa, o resultado terá 32

linhas, cada uma delas formada pela concatenação de uma linha de func com uma linha de depa)

• select * from func, depa -- mesmo resultado

(32)

... Consultas mais complexas ...

select nmf from F where not exists (select * from P where exists

- -fornecedor para o qual não exista produto que tenha embarque

ou

select nmf from ((F left join E on F.cdf = E.cdf ) left join P on E.cdp = P.cdp) where P.cdP is null

(33)

Araújo ³³

... Consultas mais complexas

• select “Nome:", nom from func

exibe o texto Nome: antes do nome de cada funcionário:

Nome: Ana Maria Nome: José Silva

...

• select 5 + 4 * 10 45