1 Subqueries - idéia

Considere o exemplo de das duas tabelas abaixo, com dados de Livros e Notas dadas a esses livros:

Suponha que queremos buscar todas as notas na tabela Nota que foram dadas ao livro Crime e castigo. Como poderíamos proceder?

Para buscar as notas dos livros na tabela Nota, primeiro precisamos descobrir qual é o id do livro Crime e castigo. Dessa forma, podemos resolver o problema usando duas queries: uma para buscar o id do livro na tabela Livro, e em seguida, com esse id, buscar as sua notas na tabela Nota.

  1. Buscando o id:
SELECT "id" FROM "Livro" 
WHERE "id" = "Crime e castigo";

Essa busca retorna o id 0.

  1. Buscando as notas:
SELECT 'Nota' FROM 'Nota' 
WHERE "id_livro" = 0;

O que retorna as notas do livro Crime e castigo.

No entanto, essa forma não é muit interessante, pois precisamos salvar o indice 0 em algum lugar, para depois usá-lo na busca pelas notas. A linguagem SQL nos permite resolver esse problema por meio de subqueries, ou queries aninhadas. No lugar de alocar o id 0 na segunda query, criamos uma nova query para buscar esse valor (que é a primeira que fizemos), ou seja, juntamos ambas as queries em uma única:

SELECT 'Nota' FROM 'Nota' 
WHERE "id_livro" = (
    SELECT "id" FROM "Livro" 
    WHERE "id" = "Crime e castigo";
    );

Essa é uma query aninhada, e o ordem de execução dos comandos segue a ordem dos parênteses: as queries com parênteses mais internos são resolvidas primeiro, no caso acima, primeiro a query da busca de id, e em seguida a query de busca por notas.

2 Subqueries - aplicações

Nesta sequência usaremos o banco de dados longlist-2.db. Ainda é um banco de dados de livros, porém com mais tabelas. Os nomes das tabelas (para ver no terminal basta usar o comando .tables), e das colunas (atributos) de cada uma são mostrados abaixo:

  • authors:
    1. id
    2. name
    3. country
    4. birth
  • authored:
    1. author_id
    2. book_id
  • books:
    1. id
    2. isbn
    3. title
    4. publisher_id
    5. format
    6. pages
    7. published
    8. year
  • ratings:
    1. book_id
    2. rating
  • translators:
    1. id
    2. name
  • translated:
    1. translator_id
    2. book_id
  • publisher:
    1. id
    2. publisher

2.1 Exemplo 1

Vamos criar uma query para buscar todos os livros que foram publicados pela editora “Fitzcarraldo Editions”. Sabemos que os livros estão na tabela books (coluna title). Porém a tabela não contém o nome das editoras, simplesmente o seu id (publisher_id), de forma que primeiro é necessário encontrar o id do editor “Fitzcarraldo Editions”. Vamos construir as suas queries de forma separada, e em seguida aninhá-las:

  1. Descobrindo o id de “Fitzcarraldo Editions”
SELECT "id" FROM "publishers" WHERE "publisher" = 'Fitzcarraldo Editions';
  1. Agora usamos este id para descobrir os livros publicados:
SELECT * FROM "books" WHERE "publisher_id" = 5;

Vamos realizar a mesma busca, porém usando uma query aninhada (subqueries):

SELECT * FROM "books" 
WHERE "publisher_id" = (
  SELECT "id" FROM "publishers" WHERE "publisher" = 'Fitzcarraldo Editions'
);

EXERCICIO: Faça a busca de todos os livros publicados pela editora MacLehose Press (sem usar query aninhada e usando).

2.2 Exemplo 2

Vamos encontrar todas as notas ratings do livro chamado “In Memory of Memory”.

Primeiro vamos encontrar o id do livro:

SELECT "id" FROM "books" WHERE "title" = 'In Memory of Memory';

Agora com esse id podemos fazer a busca na tabela de ratings pelas notas:

SELECT "rating" FROM "ratings" WHERE "book_id" = 33;

Vamos montar uma query mais dinâmica, combinando as duas anteriores:

SELECT "rating" FROM "ratings" 
WHERE "book_id" = (
    SELECT "id" FROM "books" 
    WHERE "title" = 'In Memory of Memory'
);

Vamos calcular a nota média deste livro:

SELECT AVG("rating") AS "Media" FROM "ratings" 
WHERE "book_id" = (
    SELECT "id" FROM "books" 
    WHERE "title" = 'In Memory of Memory'
) AS 'MEDIA';

2.3 Exemplo 3

Agora vamos descobrir o nome do autor que escreveu o livro “Flights”. Quais informações precisamos para essa query?

  1. O id do livro cujo nome é Flights (presente na tabela books).
  2. Com o id do livro, o id do autor que escreveu o livro cujo nome é Flights (presente na tabela authored).
  3. Com o id do autor, na tabela author buscamos o nome do mesmo.

Neste exemplo temos então que responder a 3 perguntas, de forma que a query terá 3 níveis. Vamos responder cada pergunta individualmente e em seguida criar a query composta.

  1. Id do livro:
SELECT "id" FROM "books" WHERE "title" = 'Flights';
  1. Id do autor, sabendo o id do livro (78):
SELECT "author_id" FROM "authored" WHERE "book_id" = 78;
  1. Nome do autor, sabendo o id do autor (58)
SELECT "name" FROM "authors" WHERE "id" = 58;

Agora podemos montar uma query aninhada com todas as autras:

SELECT "name" FROM "authors" 
WHERE "id" = (
        SELECT "author_id" FROM 
        "authored" WHERE "book_id" = (
            SELECT "id" FROM "books" 
            WHERE "title" = 'Flights' 
    ) 
);

EXERCICIO: Encontre o autor do livro “The Birthday Party”.

3 IN

Usamos o comando IN para selecionar elementos que estejam dentro de um conjunto. Até agora usamos o “=”, ou seja, fizemos buscas em que algo deveria ser igual a um único valor. Agora podemos buscar elementos que sejam iguais a qualquer elemento de um conjunto. Por exemplo, se quisermos selecionar o nome do livro cujo id é 1, fazemos:

SELECT "title" FROM "books" WHERE "id" = 1;

E se quisessemos selecionar os nomes de livros com ids = 1 e 2? Neste caso podemos substituir o “=” pelo IN:

SELECT "title" FROM "books" WHERE "id" IN (1,2);

3.1 Exemplo 1

Vamos buscar todos os livros que foram escritos pela autora “Fernanda Melchor”. Para isso precisamos das seguintes informações:

  1. O id da autora (na tabela authors)
  2. Com o id da autora, os ids dos livros que ela escreveu (da tabela authored) -> aqui temos um conjunto de elementos
  3. Todos os nomes de livros que estejam no conjuntos desses ids.

Respondendo às 3 perguntas:

  1. id da autora:
SELECT "id" FROM "authors" WHERE "name" = 'Fernanda Melchor';
  1. Ids dos livros da autora:
SELECT "book_id" FROM "authored" WHERE "author_id" = 24;
  1. Nome dos livros cujos ids estão no conjunto acima:
SELECT "title" FROM "books" WHERE "id" IN (14,48);

Agora podemos aninhar todas as queries:

SELECT "title" FROM "books" WHERE "id" IN (
    SELECT "book_id" FROM "authored" WHERE "author_id" = (
        SELECT "id" FROM "authors" WHERE "name" = 'Fernanda Melchor'
    )
);

4 JOIN

(PARA UMA EXPLICAÇÃO COMPLETA VER A APRESENTAÇÃO EM PDF)

Usamos o comando JOIN para unir informações de duas tabelas distintas. Existem básicamente 4 tipos diferentes de JOIN, como mostrado na Figura abaixo:

Considerando o banco de dados de migração de leões marinhos sea_lions.db. Para realizarmos um JOIN precisamos selecionar as duas tabelas que serão juntadas, bem como os atributos de cada uma que serão comparados (valores iguais serão colocados um ao lado do outro).

4.1 INNER JOIN

Para realizarmos o INNER JOIN entre as tabelas sea_lions e migrations, usando os ids de cada uma, temos:

SELECT * FROM "sea_lions"
JOIN "migrations"
ON "migrations"."id" = "sea_lions"."id";

4.2 LEFT JOIN

SELECT * FROM "sea_lions"
LEFT JOIN "migrations"
ON "migrations"."id" = "sea_lions"."id";

4.3 RIGHT JOIN

SELECT * FROM "sea_lions"
RIGHT JOIN "migrations"
ON "migrations"."id" = "sea_lions"."id";

4.4 FULL JOIN

SELECT * FROM "sea_lions"
FULL JOIN "migrations"
ON "migrations"."id" = "sea_lions"."id";

4.5 NATURAL JOIN

O NATURAL JOIN pode ser pensado em uma simplificação do INNER JOIN, quando as colunas que serão comparadas de ambas as tabelas possuem o mesmo nome. No caso anterior, ambas as tabelas possuem uma coluna chamada “id”, podemos então usar o NATURAL JOIN sem especificar as colunas, e automáticamente elas serão juntadas:

SELECT * FROM "sea_lions"
NATURAL JOIN "migrations";

4.6 MULTIPLOS JOIN

Podemos usar o JOIN pra juntar mais de duas tabelas ao mesmo tempo. Considere as 3 tabelas abaixo, em que queremos realizar o JOIN por:

  1. Elemento A da tabela1
  2. Elemento E da tabela2
  3. Elemento I da tabela3

E queremos mostrar os seguintes termos:

  1. Elemento D da tabela1
  2. Elemento G da tabela2
  3. Elemento J da tabela3

E ainda queremos aplicar algumas condições nas tabelas. A sintaxe fica então:

Note que no SELECT temos acesso a todos os elementos das tabelas, basta colocarmos o nome das tabelas antes. Ainda, primeiro fazemos todos os JOINs, para em seguida aplicar as condições WHERE. Note também que a sequência em que os JOINS são escritos importa, a primeiro JOIN junta as tabelas 1 e 2, e o resultado deste JOIN é juntado na tabela 3.

4.7 JOIN COM MULTIPLAS CONDIÇÕES

Um outro caso possível é realizar JOINS em que existem multiplas condições de junções, por exemplo:

Neste caso, a tabela3 se junta às outras por 2 elementoe, combinados por um “AND”.

5 SETS

Um resumo das operações entre conjuntos (SETS) é apresentado abaixo:

5.1 UNION

Vamos encontrar todos os nomes de autores (da tabela authors) e todos os tradutores (da tabela translators):

SELECT "name" FROM "authors";
SELECT "name" FROM "translators"

Agora podemos selecionar a união, ou seja, todos que são autores ou tradutores:

SELECT "name" FROM "authors"
UNION
SELECT "name" FROM "translators";

OBS Observe a cardinalidade dos conjuntos:

  1. Autores: 72
  2. Tradutores: 74
  3. Autores UNION Tradutores: 145

Ou seja, como Autores + Tradutores - 1 = Autores UNION Tradutores, temos que um nome é tanto autor como tradutor.

5.2 INTERSECT

Podemos descobrir qual é usando o INTERESECT:

SELECT "name" FROM "authors"
INTERSECT
SELECT "name" FROM "translators";

5.3 EXCEPT

Agora podemos usar o EXCEPT para selecinar somente os autores e somente os tradutores.

SELECT "name" FROM "authors"
EXCEPT
SELECT "name" FROM "translators";

Agora somente os tradutores:

SELECT "name" FROM "translators"
EXCEPT
SELECT "name" FROM "authors";

5.4 Exemplo 1

Vamos usar as operações entre conjuntos para descobrir quais livros foram traduzidos de forma conjunta pela tradutora “Sophie Hughes” e pela tradutora “Margaret Jull Costa”. Para isso, vamos usar as tabelas translated e translator. Primeiro podemos buscar todos os ids dos livros que foram traduzidos por “Sophie Hughes”, usando uma query aninhada (descobrindo o id da tradutora buscamos os livros traduzidos com com o id desse tradutor):

SELECT "book_id" FROM "translated" WHERE "translator_id" = (
    SELECT "id" FROM "translators" 
    WHERE "name" = 'Sophie Hughes');

Agora podemos encontrar todos os livros que foram traduzidos por “Margaret Jull Costa”

SELECT "book_id" FROM "translated" WHERE "translator_id" = (
    SELECT "id" FROM "translators" 
    WHERE "name" = 'Margaret Jull Costa');

Finalmente, para encontrar os livros em que ambas foram traduzidas, usamos o INTERSECT com as duas queries anteriores:

SELECT "book_id" FROM "translated" WHERE "translator_id" = (
    SELECT "id" FROM "translators" 
    WHERE "name" = 'Sophie Hughes')
INTERSECT
SELECT "book_id" FROM "translated" WHERE "translator_id" = (
    SELECT "id" FROM "translators" 
    WHERE "name" = 'Margaret Jull Costa');

DESAFIO: Crie a query para encontrar o nome do livro que as duas traduziram juntas.

6 GROUP BY

Podemos agregar (agrupar) valores para resumos e estatísticas usando C. Considere a tabela de notas dos livros ratings, com dados da seguinte forma:

book_id rating
1 4
1 4
2 8
3 7
3 4
4 7

Se aplicarmos a função AVG(“rating”) obteremos a média de todos os livros, no entanto, podemos estar interessados em obter a média por livro, algo como:

book_id mean(rating)
1 4.0
2 8.0
3 5.5
4 7.0

Para isso usamos o GROUP BY. Vamos fazer esse mesmo exemplo com as notas no BD:

SELECT "book_id",AVG("rating") AS "average rating" 
FROM "ratings"
GROUP BY "book_id";

Agora vamos aplicar um filtro nas notas médias, para buscar somente aquelas que são > 4.0. Poderíamos pensar na seguinte query:

SELECT "book_id",AVG("rating") AS "average rating" 
FROM "ratings"
GROUP BY "book_id"
WHERE "average rating" > 4;

Porém para aplicar condições em grupos usamos outra palavra chave, o GROUP BY, portanto a query fica:

SELECT "book_id",ROUND(AVG("rating"),2) AS "average rating" 
FROM "ratings"
GROUP BY "book_id"
HAVING "average rating" > 4;

DESAFIOS:

  1. Encontre o número de votos que cada livro obteve, e ordene o resultado de forma decrescente
SOLUÇÃO 
SELECT "book_id",COUNT("rating") AS "count" 
FROM "ratings"
GROUP BY "book_id"
ORDER BY "count" DESC;
  1. Imprima o resultado passado com o nome de cada livro
SOLUÇÃO 
SELECT "title" FROM "books" WHERE "id" IN (
    SELECT "book_id" FROM (
        SELECT "book_id",COUNT("book_id")
        FROM "ratings"
        GROUP BY "book_id" 
        ORDER BY COUNT("book_id") DESC));