Aprenda fazendo: como os LLMs devem remodelar a educação

OK, o que há de errado com meu SQL?

drop table if exists person;
create table person(
	ident integer not null primary key,
	name text not null
);

drop table if exists job;
create table job(
	ident integer not null primary key,
	name text not null
);

drop table if exists did;
create table did(
	person_id integer not null,
	job_id integer not null
);

insert into person values (1, "Alice"), (2, "Bob");
insert into job values (20, "cook"), (30, "clean");
insert into did values(1, 20), (2, 20), (1, 30);

select person.name, job.name, count(*)
from person inner join did inner join job
on person.ident = did.person_id and did.job_id = job.ident
group by person.ident, job.ident
order by person.ident, job.ident;

O resultado não mostra 0 para (Bob, limpo). Por que não?

Bate-papo GPT: Seu script SQL parece correto, mas há um problema com a consulta final que você está usando para combinar as tabelas. Vamos decompô-lo:

Criação e inserção de tabelas: Seu script cria corretamente as tabelas person, job e did e insere dados nelas. Esta parte parece boa.

Consulta final para dados combinados: você está tentando combinar dados dessas tabelas para mostrar qual pessoa faz qual trabalho. No entanto, a consulta que você escreveu usando INNER JOIN não incluirá combinações em que uma pessoa não tenha emprego (como Bob com “limpo” no seu exemplo). Isso ocorre porque INNER JOIN inclui apenas linhas onde há uma correspondência em ambas as tabelas.

Para incluir todas as combinações de pessoas e cargos, mesmo quando uma pessoa não tem um cargo específico, você deve usar um CROSS JOIN para criar um produto cartesiano de pessoa e cargo e, em seguida, um LEFT JOIN com a tabela did. Dessa forma, você obterá todas as combinações e poderá então contar as ocorrências.

Aqui está a consulta corrigida:

SELECT person.name, job.name, COUNT(did.person_id) as job_count
FROM person
CROSS JOIN job
LEFT JOIN did ON person.ident = did.person_id AND job.ident = did.job_id
GROUP BY person.name, job.name
ORDER BY person.name, job.name;

John: Parece bom, por favor escreva um teste para provar que funciona.

Bate-papo GPT: Para testar a consulta SQL, configurarei uma simulação do ambiente do banco de dados e depois executarei a consulta. Isso demonstrará a exatidão da consulta no tratamento de seus requisitos. Vamos prosseguir com o teste:

Crie as tabelas: criarei as tabelas person, job e did de acordo com seu script. Inserir dados: inserirei os dados que você forneceu nessas tabelas. Execute a consulta: executarei a consulta corrigida para mostrar como ela trata a combinação de pessoa e cargo, incluindo casos em que uma pessoa não possui registro de cargo.

Vamos começar com este teste:

import sqlite3
import pandas as pd

# Create an in-memory SQLite database
conn = sqlite3.connect(':memory:')

# Create tables
create_statements = (
    '''
    CREATE TABLE person (
        ident INTEGER NOT NULL PRIMARY KEY,
        name TEXT NOT NULL
    );
    ''',
    '''
    CREATE TABLE job (
        ident INTEGER NOT NULL PRIMARY KEY,
        name TEXT NOT NULL
    );
    ''',
    '''
    CREATE TABLE did (
        person_id INTEGER NOT NULL,
        job_id INTEGER NOT NULL
    );
    '''
)

for create_statement in create_statements:
    conn.execute(create_statement)

# Insert data
person_data = ((1, "Alice"), (2, "Bob"))
job_data = ((20, "cook"), (30, "clean"))
did_data = ((1, 20), (2, 20), (1, 30))

conn.executemany('INSERT INTO person VALUES (?, ?);', person_data)
conn.executemany('INSERT INTO job VALUES (?, ?);', job_data)
conn.executemany('INSERT INTO did VALUES (?, ?);', did_data)

# Query to combine the tables
query = '''
SELECT person.name AS PersonName, job.name AS JobName, COUNT(did.person_id) AS JobCount
FROM person
CROSS JOIN job
LEFT JOIN did ON person.ident = did.person_id AND job.ident = did.job_id
GROUP BY person.name, job.name
ORDER BY person.name, job.name;
'''

# Execute the query
result = pd.read_sql_query(query, conn)
conn.close()

result

  PersonName JobName  JobCount
0      Alice   clean         1
1      Alice    cook         1
2        Bob   clean         0
3        Bob    cook         1

Bate-papo GPT: Para mostrar a saída do CTE PersonJobCombos, precisamos executar apenas essa parte da consulta. Este CTE gera todas as combinações possíveis de pessoas e empregos, independentemente de uma pessoa ter realmente realizado um trabalho ou não. Vamos executar este CTE separadamente para ver sua saída.

# Reconnect to the in-memory SQLite database
conn = sqlite3.connect(':memory:')

# Recreate tables and insert data as before
for create_statement in create_statements:
    conn.execute(create_statement)

conn.executemany('INSERT INTO person VALUES (?, ?);', person_data)
conn.executemany('INSERT INTO job VALUES (?, ?);', job_data)
conn.executemany('INSERT INTO did VALUES (?, ?);', did_data)

# Query for PersonJobCombos CTE
person_job_combos_query = '''
WITH PersonJobCombos AS (
    SELECT person.ident AS PersonID, person.name AS PersonName, job.ident AS JobID, job.name AS JobName
    FROM person
    CROSS JOIN job
)
SELECT * FROM PersonJobCombos;

# Execute the PersonJobCombos CTE query
person_job_combos_result = pd.read_sql_query(person_job_combos_query, conn)
conn.close()

person_job_combos_result

PersonID PersonName  JobID JobName
0         1      Alice     20    cook
1         1      Alice     30   clean
2         2        Bob     20    cook
3         2        Bob     30   clean

A saída do CTE PersonJobCombos mostra todas as combinações possíveis de pessoas e empregos:

Alice é combinada com “cozinheira” e “limpa”.

Bob também está emparelhado com “cozinhar” e “limpar”.

Isso demonstra o produto cartesiano das tabelas de pessoas e cargos, criando uma combinação de cada pessoa com cada cargo, independentemente de terem realmente executado aquele cargo ou não.

Professor: Estas são as tarefas que as pessoas podem fazer: cozinhar, limpar.

Estas são as pessoas: Alice, Bob.

Estes são os trabalhos que realizaram: Alice: cozinhar, limpar; Bob: cozinheiro.

Mostre como modelar esse cenário usando uma ou mais tabelas SQL e relate os dados nesse formato.

PersonName  JobName JobCount
Alice       clean   1
Alice       cook    1
Bob         clean   0
Bob         cook    1

Você deve esperar que o ChatGPT escreva, execute e itere em um script Python que incorpore o SQLite e invoque as instruções SQL necessárias. Quando você chegar a um script que relata os dados acima, execute-o você mesmo para verificar e inclua o script.

Agora responda a esta pergunta: Como a saída SQL inclui esta linha?

Bob         clean   0

Não estava nos dados, de onde vem?