Planeta Download
Livro/PostgreSQL Prático

PostgreSQL Prático/DML/Sub Consultas

Alice
Planeta Download
segunda, 04 de abril de 2011

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;