O mapa de hoje

• Bloco 1 — subconsultas: uma consulta dentro da outra
• Bloco 2 — views: guardar uma consulta com nome
• Bloco 3 — o CRUD em Python: as 4 funções
• Bloco 4 — mão na massa: construir o CRUD do projeto

Retomando a Aula 14

• Qual a forma do ON num INNER JOIN?
• Quando usar LEFT JOIN em vez de INNER?
• Qual palavra em português indica GROUP BY?

O problema

"Quais jogos são do PlayStation 5?"

Você sabe o nome da plataforma, mas a tabela jogo guarda o número.

• Opção A: consultar plataforma para descobrir o id, anotar, e usar na segunda consulta
• Opção B: INNER JOIN (Aula 14)
• Opção C: subconsulta — deixar o banco fazer as duas de uma vez

A subconsulta

SELECT titulo
FROM   jogo
WHERE  id_plataforma = (SELECT id_plataforma
                        FROM   plataforma
                        WHERE  nome = 'PlayStation 5');

IN — quando a subconsulta devolve vários

SELECT titulo
FROM   jogo
WHERE  id_plataforma IN (SELECT id_plataforma
                         FROM   plataforma
                         WHERE  fabricante = 'Sony');

Subconsulta com agregação

SELECT titulo, ano_lancamento
FROM   jogo
WHERE  ano_lancamento > (SELECT AVG(ano_lancamento) FROM jogo);

"Quais jogos são mais novos que a média do catálogo?"

Checagem do Bloco 1

O problema

Aquela consulta linda da Aula 14 — LEFT JOIN, COUNT, GROUP BY, ORDER BY — tem seis linhas.

• O dono quer esse relatório toda semana
• Ninguém vai digitar seis linhas toda semana
• E se cada pessoa digitar de um jeito, cada uma vê um número diferente

CREATE VIEW

CREATE VIEW jogos_por_plataforma AS
SELECT p.nome        AS plataforma,
       COUNT(j.id_jogo) AS total_jogos
FROM   plataforma p
LEFT JOIN jogo j ON p.id_plataforma = j.id_plataforma
GROUP BY p.nome;

Depois, basta:

SELECT * FROM jogos_por_plataforma;

Para que serve uma view

• Simplificar — a consulta difícil fica escrita uma vez só
• Padronizar — todo mundo usa a mesma definição de "jogo disponível"
• Proteger — dar acesso à view sem dar acesso à tabela inteira

DROP VIEW e o cuidado com dependências

DROP VIEW jogos_por_plataforma;

Checagem do Bloco 2

O que é CRUD

C · Createcriar
INSERT

R · Readler
SELECT

U · Updateatualizar
UPDATE

D · Deleteapagar
DELETE

C — criar

def criar_jogo(titulo, genero, id_plataforma):
    cursor.execute(
        "INSERT INTO jogo (titulo, genero, id_plataforma) VALUES (%s, %s, %s);",
        (titulo, genero, id_plataforma)
    )
    conexao.commit()
    print(f"Jogo '{titulo}' cadastrado.")

R — ler

def listar_jogos():
    cursor.execute(
        "SELECT id_jogo, titulo, genero FROM jogo ORDER BY titulo;"
    )
    for id_jogo, titulo, genero in cursor.fetchall():
        print(f"{id_jogo:3} | {titulo:30} | {genero}")

U — atualizar

def atualizar_jogo(id_jogo, novo_genero):
    cursor.execute(
        "UPDATE jogo SET genero = %s WHERE id_jogo = %s;",
        (novo_genero, id_jogo)
    )
    conexao.commit()
    print(f"{cursor.rowcount} linha(s) atualizada(s).")

D — apagar

def apagar_jogo(id_jogo):
    cursor.execute("DELETE FROM jogo WHERE id_jogo = %s;", (id_jogo,))
    conexao.commit()
    print(f"{cursor.rowcount} linha(s) apagada(s).")

Erro comum — esquecer o WHERE dentro da função

Confira, em cada função que altera dados:

• Existe WHERE?
• O WHERE usa a chave primária?
• A função imprime rowcount para você conferir?

Checagem do Bloco 3

def apagar(cursor, id_jogo):
    cursor.execute("DELETE FROM jogo")