PostgreSQL Prático/DML/Sub Consultas

4.3 – Sub consultas

São consultas dentro de consultas.

Subconsulta escalar é um comando SELECT comum, entre parênteses, que retorna exatamente um registro, com um campo.

 select nome, (select max(preco) from produtos where codigo=1) as "maior preço" from produtos;

 SELECT * FROM tabela1 WHERE tabela1.col1 = 
   (SELECT col2 FROM tabela2 WHERE col2 = valor);

 SELECT name FROM customer WHERE customer_id NOT IN ( SELECT customer_id FROM salesorder );

 SELECT 'test' AS test, id FROM (SELECT * FROM books) AS example_sub_query;

 SELECT firstname, state, 
   CASE
     WHEN state = 'PA' THEN 'close'
     WHEN state = 'NJ' OR state = 'MD' THEN 'far'
   ELSE 'very far'
 END AS distance
 FROM friend;

Expressões de Sub Consultas

EXISTS

SELECT campo1 FROM tabela1 WHERE EXISTS
  (SELECT 1 FROM tabela2 WHERE campo2 = tabela1.campo2);

Combinando CASE e EXISTS

CREATE TEMPORARY TABLE frutas (id SERIAL PRIMARY KEY, nome TEXT);
INSERT INTO frutas VALUES (DEFAULT, 'banana');
INSERT INTO frutas VALUES (DEFAULT, 'maçã');

CREATE TEMPORARY TABLE alimentos (id SERIAL PRIMARY KEY, nome TEXT);
INSERT INTO alimentos VALUES (DEFAULT, 'maçã');
INSERT INTO alimentos VALUES (DEFAULT, 'espinafre');

SELECT nome, CASE WHEN EXISTS (SELECT nome FROM frutas WHERE nome=a.nome)
               THEN 'sim'
               ELSE 'não'
           END AS fruta
FROM alimentos a;

IN

SELECT nome, CASE WHEN nome IN (SELECT nome FROM frutas)
                THEN 'sim'
                ELSE 'não'
           END AS fruta
FROM alimentos;

NOT IN

ANY/SOME

SELECT nome, CASE WHEN nome = ANY (SELECT nome FROM frutas)
                THEN 'sim'
                ELSE 'não'
           END AS fruta
FROM alimentos;

CASE WHEN

EXEMPLO1

create table amigos(
       codigo serial primary key,
       nome char(45),
       idade int
);

insert into amigos (nome, idade) values ('João Brito', 25);
insert into amigos (nome, idade) values ('Roberto', 35);
insert into amigos (nome, idade) values ('Antônio', 15);
insert into amigos (nome, idade) values ('Francisco Queiroz', 23);
insert into amigos (nome, idade) values ('Bernardo dos Santos', 21);
insert into amigos (nome, idade) values ('Francisca Pinto', 22);
insert into amigos (nome, idade) values ('Natanael', 55);

select nome,
       idade,
       case
           when idade >= 21 then 'Adulto'
           else 'Menor'
       end as status
from amigos order by nome;

-- CASE WHEN cria uma coluna apenas para exibição

EXEMPLO2

create table amigos(
       codigo serial primary key,
       nome char(45),
       estado char(2)
);

insert into amigos (nome, estado) values ('João Brito', 'CE');
insert into amigos (nome, estado) values ('Roberto', 'MA');
insert into amigos (nome, estado) values ('Antônio', 'CE');
insert into amigos (nome, estado) values ('Francisco Queiroz', 'PB');
insert into amigos (nome, estado) values ('Bernardo dos Santos', 'MA');
insert into amigos (nome, estado) values ('Francisca Pinto', 'SP');
insert into amigos (nome, estado) values ('Natanael', 'SP');

select nome,
               estado,
               case
                       when estado = 'PB' then 'Fechado'
                       when estado = 'CE' or estado = 'SP' then 'Funcionando'
                       when estado = 'MA' then 'Funcionando a todo vapor'
                       else 'Menor'
               end as status
from amigos order by nome;

Mostrar cada nota junto com a menor nota, a maior nota, e a média de todas as notas.

SELECT nota,
      (SELECT MIN(nota) FROM notas) AS menor,
      (SELECT MAX(nota) FROM notas) AS maior,
      (ROUND(SELECT AVG(nota) FROM notas)) AS media
FROM notas;