15.4. Lesson: Consultas
Quando você escreve um comando SELECT ... é comumente conhecido como uma consulta - você está interrogando o banco de dados para obter informações.
O objetivo dessa lição: Saber como criar consultas que retornam informações úteis.
Nota
Se você não o fez na lição anterior, adicione nos seguintes objetos pessoais para a sua tabela people. Se você receber erros relacionados a restrições de chave estrangeira, você vai precisar adicionar o objeto ‘Main Road’ para a sua mesa ruas primeira
insert into people (name,house_no, street_id, phone_no)
values ('Joe Bloggs',3,2,'072 887 23 45');
insert into people (name,house_no, street_id, phone_no)
values ('Jane Smith',55,3,'072 837 33 35');
insert into people (name,house_no, street_id, phone_no)
values ('Roger Jones',33,1,'072 832 31 38');
insert into people (name,house_no, street_id, phone_no)
values ('Sally Norman',83,1,'072 932 31 32');
15.4.1. Ordenando os resultados
Vamos recuperar uma lista de pessoas ordenadas pelos números de casa:
select name, house_no from people order by house_no;
Resultado:
name | house_no
--------------+----------
Joe Bloggs | 3
Roger Jones | 33
Jane Smith | 55
Sally Norman | 83
(4 rows)
Você pode classificar os resultados pelos valores de mais de uma coluna:
select name, house_no from people order by name, house_no;
Resultado:
name | house_no
--------------+----------
Jane Smith | 55
Joe Bloggs | 3
Roger Jones | 33
Sally Norman | 83
(4 rows)
15.4.2. filtragem
Muitas vezes, você não vai querer ver cada registro único na base de dados - especialmente se existem milhares de registros e você está interessado apenas em ver um ou dois.
Aqui está um exemplo de um filtro numérico que retorna apenas objetos cujo house_no é menor que 50:
select name, house_no from people where house_no < 50;
name | house_no
-------------+----------
Joe Bloggs | 3
Roger Jones | 33
(2 rows)
Você pode combinar filtros (definidos usando a cláusula ONDE) com classificação (definida usando a cláusula ORDENAR POR):
select name, house_no from people where house_no < 50 order by house_no;
name | house_no
-------------+----------
Joe Bloggs | 3
Roger Jones | 33
(2 rows)
Você também pode filtrar com base em dados de texto:
select name, house_no from people where name like '%s%';
name | house_no
-------------+----------
Joe Bloggs | 3
Roger Jones | 33
(2 rows)
Aqui usamos a cláusula LIKE para encontrar todos os nomes com um s neles. Você notará que esta consulta é sensível ao caso, por isso a entrada Sally Norman não foi devolvida.
Se você deseja procurar uma sequência de letras independentemente do caso, pode fazer uma pesquisa sensível a maiúsculas e minúsculas usando a cláusula ILIKE:
select name, house_no from people where name ilike '%r%';
name | house_no
--------------+----------
Roger Jones | 33
Sally Norman | 83
(2 rows)
Essa consulta retornou todos os objetos pessoas com um r ou R em seu nome.
15.4.3. Uniões
E se você quiser ver os detalhes da pessoa e o nome da rua em vez da identificação? Para fazer isso, você precisa unir as duas tabelas em uma única consulta. Vamos ver um exemplo:
select people.name, house_no, streets.name
from people,streets
where people.street_id=streets.id;
Nota
Estando unidas, você sempre irá indicar as duas tabelas que as informações sejam provenientes, neste caso, as pessoas e as ruas. Você também precisa especificar quais duas chaves devem coincidir (chave estrangeira & chave primária). Se você não especificar isso, você terá uma lista de todas as combinações possíveis de pessoas e ruas, mas não há maneira de saber quem realmente vive em que rua!
A saída correta deveria ser assim:
name | house_no | name
--------------+----------+-------------
Joe Bloggs | 3 | Low Street
Roger Jones | 33 | High street
Sally Norman | 83 | High street
Jane Smith | 55 | Main Road
(4 rows)
Vamos revisitar a união como criar consultas mais complexas mais tarde. Apenas lembre-se que eles fornecem uma maneira simples de combinar as informações a partir de duas ou mais tabelas.
15.4.4. Sub-Seleção
Sub-seleções permitem selecionar objetos de uma tabela com base nos dados de outra tabela que está ligada através de uma relação de chave estrangeira. No nosso caso, queremos encontrar pessoas que vivem em uma rua específica.
Mas antes, vamos fazer um pequeno ajuste em nossos dados:
insert into streets (name) values('QGIS Road');
insert into streets (name) values('OGR Corner');
insert into streets (name) values('Goodle Square');
update people set street_id = 2 where id=2;
update people set street_id = 3 where id=3;
Vamos dar uma olhada rápida em nossos dados após essas alterações: podemos reutilizar nossa consulta da seção anterior:
select people.name, house_no, streets.name
from people,streets
where people.street_id=streets.id;
Resultado:
name | house_no | name
--------------+----------+-------------
Roger Jones | 33 | High street
Sally Norman | 83 | High street
Jane Smith | 55 | Main Road
Joe Bloggs | 3 | Low Street
(4 rows)
Agora vamos mostrar uma sub-seleção nesses dados. Queremos mostrar apenas as pessoas que vivem no número street_id1:
select people.name
from people, (
select *
from streets
where id=1
) as streets_subset
where people.street_id = streets_subset.id;
Resultado:
name
--------------
Roger Jones
Sally Norman
(2 rows)
Embora este seja um exemplo muito simples e desnecessário com os nossos pequenos conjuntos de dados, ele ilusta como sub-seleções úteis e importantes pode ser ao consultar conjuntos de dados grandes e complexos.
15.4.5. Consultas agregadas
Um dos recursos poderosos de um banco de dados é a capacidade de resumir os dados em suas tabelas. Esses resumos são chamados de pesquisas agregadas. Aqui está um exemplo típico que nos diz quantas pessoas existem na nossa tabela de pessoas:
select count(*) from people;
Resultado:
count
-------
4
(1 row)
Se queremos que as contagens estejam resumidas pelo nome da rua, podemos fazer isso:
select count(name), street_id
from people
group by street_id;
Resultado:
count | street_id
-------+-----------
2 | 1
1 | 3
1 | 2
(3 rows)
Nota
Porque nós não usamos uma cláusula ORDER BY, a ordem de seus resultados podem não coincidir com o que é mostrado aqui.
Try Yourself 
Resumir as pessoas pelo nome de rua e mostrar os nomes de ruas reais em vez das street_ids.
Responda
Aqui está a instrução SQL correta que você deve usar:
select count(people.name), streets.name
from people, streets
where people.street_id=streets.id
group by streets.name;
Resultado:
count | name
------+-------------
1 | Low Street
2 | High street
1 | Main Road
(3 rows)
Você notará que temos nomes de campos prefixados com nomes de tabelas (por exemplo, pessoas.nome e ruas.nome). Isso precisa ser feito sempre que o nome do campo for ambíguo (ou seja, não exclusivo em todas as tabelas do banco de dados).
15.4.6. In Conclusion
Você já viu como utilizar as pequisas para retornar os dados em seu banco de dados de uma forma que lhe permite extrair informações úteis a partir dele.
15.4.7. What’s Next?
Em seguida, você verá como criar visualização com as consultas que você escreveu.