CS50CS50x em Português - Aula 7 - SQL
CS50
Introdução ao SQL e paradigma declarativo 0:43
A aula 7 do CS50 apresenta o SQL, ou Structured Query Language, como mais uma linguagem de programação capaz de resolver problemas semelhantes aos das semanas anteriores, mas muitas vezes com mais facilidade. Diferente de C e Python, que são linguagens procedurais em que você escreve passo a passo o que o computador deve fazer, o SQL é declarativo: você apenas declara qual pergunta quer responder, e cabe à linguagem descobrir como chegar à resposta usando loops e condicionais internamente. A ideia central da aula é mostrar que existem várias ferramentas para resolver o mesmo problema, e que aprender novas linguagens fica mais fácil quando você já conhece diferentes paradigmas de programação.
Coleta de dados com Google Forms 3:00
Para trabalhar com dados reais, os alunos são convidados a responder a um formulário do Google Forms, indicando sua linguagem de programação favorita entre as estudadas até então e o problema favorito dos conjuntos de exercícios. As respostas aparecem em tempo real, mostrando que Python é a preferida, com mais de 70%, seguida por C com 18% e Scratch com 11%. O Google Forms está integrado ao Google Sheets, permitindo visualizar as respostas diretamente numa planilha.
Baixando dados como arquivo CSV 4:30
A partir do Google Sheets, os dados brutos podem ser baixados em diversos formatos, e o escolhido é o CSV, sigla para valores separados por vírgula. Esse formato é descrito como um banco de dados de arquivo plano: um simples arquivo de texto em que cada linha representa um registro e as colunas são separadas por vírgulas, embora existam variantes como TSV, separado por tabulação, e PSV, separado por barra vertical. O arquivo baixado, renomeado para favorites.csv, contém três colunas: carimbo de data e hora, linguagem escolhida e problema favorito, e é carregado no VS Code para começar a escrever código sobre ele.
Lendo o CSV linha por linha em Python 7:02
Usando a biblioteca csv do Python, o programa favorites.py abre o arquivo e cria um leitor que separa automaticamente os valores por vírgula, permitindo percorrer linha por linha sem escrever manualmente a lógica de separação. A primeira tentativa imprime a segunda coluna de cada linha, mas inclui por engano o cabeçalho, corrigido ao pular a primeira linha antes do laço. Esse comportamento mostra que o leitor mantém estado, lembrando em que ponto do arquivo está, de forma parecida com a leitura de arquivos em C. Em seguida, o código é ajustado para usar um DictReader, que entrega cada linha como um dicionário com chaves nomeadas como as colunas do cabeçalho, tornando o programa mais robusto a mudanças na ordem das colunas.
Contando respostas com variáveis e dicionários 14:01
Para contar quantas pessoas preferem cada linguagem, o professor primeiro cria três variáveis separadas, uma para Scratch, uma para C e uma para Python, incrementando cada uma conforme as respostas são lidas, obtendo os totais 109 para Python, 58 para C e 24 para Scratch. Essa abordagem é apontada como pouco escalável, pois exigiria uma nova variável para cada linguagem adicional. A solução é substituir as variáveis por um único dicionário chamado counts, cujas chaves são os nomes das linguagens e cujos valores são as contagens, tratando o erro de chave inexistente de quatro formas diferentes, incluindo verificar se a chave existe, inicializar sempre em zero antes de incrementar, ou usar um bloco try/except para capturar a exceção KeyError.
Rumo aos bancos de dados relacionais 24:02
Depois de perceber que quase vinte linhas de código são necessárias apenas para responder a uma pergunta simples, como qual é a linguagem mais popular, fica claro que há uma forma melhor de lidar com dados maiores e mais complexos. A aula então anuncia a transição do banco de dados de arquivo plano para o banco de dados relacional, no qual se definem relações entre diferentes partes dos dados, como a ligação entre um carimbo de data e hora, uma linguagem e um problema favorito. O SQL é apresentado como a linguagem para lidar com esses bancos, resumida em apenas quatro operações fundamentais, conhecidas pelo acrônimo CRUD: criar, ler, atualizar e excluir dados.
Criando o banco de dados com SQLite 25:32
O professor explica que os quatro comandos fundamentais de SQL para criar, ler, atualizar e excluir dados usam nomes diferentes, sendo SELECT o comando para ler dados. No CS50 será usada a versão leve chamada SQLite, em contraste com produtos maiores como Oracle, SQL Server, Postgres ou MySQL. Ele mostra como rodar o comando sqlite3 no terminal para criar um arquivo chamado favorites.db, entrando assim no prompt interativo do banco de dados.
Importando o CSV para uma tabela 29:01
Para trazer os dados do Google Forms para dentro do banco, ele usa três comandos que começam com ponto, específicos do SQLite: mudar para o modo CSV, importar o arquivo favorites.csv e nomear a tabela como favorites. Depois disso o arquivo favorites.db aparece na pasta, mas seu conteúdo é binário e não pode ser lido diretamente no editor de texto, o que é esperado porque bancos de dados armazenam informação de forma mais eficiente que texto puro. Usando o comando .schema, ele mostra que a tabela criada tem três colunas, timestamp, language e problem, todas do tipo texto por padrão.
Selecionando dados com SELECT 32:00
O comando SELECT permite declarar quais dados se quer ler, sem precisar abrir arquivos ou escrever laços em Python. Usando o asterisco como curinga para todas as colunas, ou nomeando colunas específicas como language e problem, é possível extrair exatamente a informação desejada da tabela favorites. Ele também mostra funções prontas do SQLite, como COUNT e DISTINCT, que permitem por exemplo contar que havia 272 respostas no total e apenas três linguagens distintas mencionadas.
Filtrando com WHERE e LIKE 36:30
Com a cláusula WHERE é possível impor condições, como contar quantas pessoas escolheram C como linguagem favorita, resultado que foi 58, ou combinar duas condições para descobrir que apenas 5 escolheram C junto com o problema Hello, World. Ele mostra também como escapar um apóstrofo dentro de uma string usando aspas simples duplas, ao testar o problema chamado Hello, it's me. Em seguida introduz a palavra-chave LIKE, que permite correspondência aproximada usando o símbolo de porcentagem como curinga, e explica que LIKE ignora maiúsculas e minúsculas, ao contrário da igualdade exata.
Agrupando, ordenando e limitando resultados 41:31
A cláusula GROUP BY permite agrupar linhas por valores comuns, como somar quantas pessoas escolheram cada linguagem, substituindo dezenas de linhas de código Python por um único comando. Combinando isso com ORDER BY é possível ordenar os resultados por contagem em ordem decrescente, e com a palavra-chave AS é possível dar um apelido à coluna de contagem, como chamá-la de n. Por fim, LIMIT permite restringir o resultado a uma única linha, revelando que Python foi a linguagem mais popular, escolhida por 190 pessoas.
Inserindo, apagando e atualizando linhas 46:00
Para adicionar uma nova linha usa-se INSERT INTO seguido do nome da tabela, das colunas e dos valores correspondentes, como ao inserir uma linha fictícia com a linguagem SQL e o problema Fiftyville, deixando o timestamp vazio, o que gera o valor NULL, que representa a ausência consciente de dado, diferente de uma célula em branco ou de uma string vazia. Para remover dados usa-se DELETE FROM com uma condição WHERE, com o alerta de que esquecer essa condição apaga a tabela inteira, algo que já aconteceu de verdade a estagiários em empresas reais. Por fim, ele começa a mostrar a sintaxe de UPDATE, que usa SET para definir novos valores em colunas específicas sob uma condição WHERE, propondo como exemplo mudar o favorito de todos para SQL e Fiftyville.
Perigos dos comandos SELECT, DELETE e DROP 50:33
O instrutor mostra que executar SELECT FROM favorites sem filtro apaga a distinção entre linhas e pode confundir todos os dados. Em seguida demonstra DELETE FROM favorites, que remove todos os registros da tabela, restando apenas a possibilidade de restaurar a partir do CSV original. Por fim executa DROP TABLE favorites, que elimina a tabela inteira do banco, mostrando que .schema fica vazio depois disso. Ele resume as operações CRUD vistas até agora: SELECT, INSERT, UPDATE, DELETE, além de CREATE e DROP.
Backups e controle de acesso 52:30
Uma pergunta da turma leva à discussão sobre como aplicativos reais se protegem de perdas de dados. A resposta é que backups automáticos são comuns, geralmente diários ou noturnos, o que implica um trade-off entre espaço de armazenamento e a quantidade de dados que se pode perder. Também se explica que, diferente do SQLite, bancos como Oracle, SQL Server, Postgres e MySQL têm nomes de usuário, senhas e permissões, permitindo dar a certos usuários apenas a capacidade de ler dados, sem poder alterar ou excluir.
Modelando dados do IMDb em planilhas 54:00
Usando o exemplo da série The Office, o instrutor testa três formas de organizar dados de programas e atores numa planilha. A primeira, com uma coluna por astro, gera redundância e colunas vazias para programas com elencos pequenos. A segunda, repetindo o título do programa em cada linha de astro, também desperdiça espaço e é propensa a erros de digitação. A terceira solução, considerada a correta, separa os dados em três abas distintas: Shows, Pessoas e Estrelas, cada uma com identificadores únicos que se cruzam por números inteiros, eliminando a redundância de texto.
Tabelas relacionadas no banco shows.db 1:00:00
O banco de dados shows.db, construído a partir de dados reais do IMDb, contém seis tabelas: pessoas, programas, estrelas, roteiristas, classificações e gêneros, ligadas entre si por setas que representam relações. Usando SELECT COUNT, descobre-se que há 250.087 séries e 704.315 pessoas no conjunto de dados. A tabela de classificações (ratings) guarda o ID do programa, a nota e o número de votos, e o instrutor explica os cinco tipos de dados do SQLite: inteiro, numérico, real, texto e BLOB, além das restrições NOT NULL e UNIQUE.
Chaves primárias, estrangeiras e consultas aninhadas 1:06:32
O conceito central apresentado é o de chave primária, o identificador único de cada linha de uma tabela, e chave estrangeira, quando esse mesmo valor aparece em outra tabela para referência cruzada. Para responder à pergunta de quais são os dez programas mais bem avaliados, o instrutor usa uma consulta aninhada: primeiro seleciona os IDs de programas com nota igual ou superior a 6.0 na tabela ratings, e depois usa esse resultado dentro de outra consulta sobre a tabela shows para obter os títulos correspondentes.
Unindo tabelas com JOIN 1:12:00
Para evitar a repetição de consultas aninhadas, o instrutor introduz o comando JOIN, que une a tabela shows com a tabela ratings usando a condição shows.id igual a ratings.show_id. O resultado combina as colunas de ambas as tabelas alinhadas pelo identificador comum, permitindo filtrar por nota maior ou igual a 6.0 e limitar a dez resultados. Refinando a consulta para selecionar apenas título e nota, ele obtém uma tabela simples e legível com os dez programas mais bem avaliados e suas respectivas notas, mostrando como o JOIN recompõe informações que estavam separadas em tabelas distintas.
Convenção de chaves estrangeiras 1:15:03
Qualquer coluna terminada em "_id" indica uma chave estrangeira, e a convenção usada é que, se a tabela se chama "shows" no plural, a chave estrangeira correspondente é chamada "show_id" no singular. Cada empresa pode ter sua própria prática, mas aqui a convenção de sublinhados e letras minúsculas é mantida em todas as tabelas. Sobre como os identificadores são gerados, no caso do conjunto de dados do IMDb isso foi feito por quem criou a base, mas na prática comum um banco de dados atribui números inteiros que se auto-incrementam a partir de 1, garantindo que nunca haja valores duplicados. Também se esclarece que o símbolo de reticências e a seta que aparecem no terminal são apenas um prompt de continuação do SQLite3, sem significado sintático, e que o comando LIMIT 10 simplesmente retorna as dez primeiras linhas na ordem em que aparecem na tabela, sem qualquer ordenação implícita.
Gêneros como relação um-para-muitos 1:17:00
Diferente da relação um-para-um entre programas e avaliações, um programa pode pertencer a vários gêneros, como comédia e drama ao mesmo tempo, o que caracteriza uma relação um-para-muitos. Isso é implementado com uma tabela separada chamada "genres", com duas colunas, show_id e genre, sendo show_id uma chave estrangeira que aponta para o id da tabela shows. Observando o esquema, nota-se que a tabela shows tem uma chave primária chamada id, enquanto a tabela ratings tinha uma restrição adicional de unicidade no show_id, garantindo a relação um-para-um. Já em genres essa restrição de unicidade não existe, permitindo duplicatas, já que um mesmo programa pode aparecer várias vezes associado a gêneros diferentes. O exemplo usado é o programa Catweazle, de 1970, com id 63881, que aparece associado aos gêneros aventura, comédia e família.
Subconsultas para descobrir gêneros 1:22:00
Para descobrir os gêneros de um programa sem precisar memorizar seu id, usa-se uma subconsulta aninhada: primeiro busca-se o id do programa na tabela shows pelo título, e depois usa-se esse resultado dentro de outra consulta que busca o gênero na tabela genres. Assim, obtêm-se os gêneros de Catweazle sem nunca precisar ver ou lidar diretamente com o número identificador. Esse mesmo resultado também pode ser obtido unindo as duas tabelas com JOIN, alinhando shows.id com genres.show_id, embora isso gere alguma redundância temporária nos dados exibidos, já que o banco de dados duplica as linhas necessárias para manter a mesma estrutura em cada linha da tabela resultante. Também se explica que, na forma mais simples de implementação, o SQL realiza uma busca linear nas tabelas, embora seja possível otimizar consultas para se aproximar do desempenho de uma busca binária.
Relação muitos-para-muitos entre pessoas e programas 1:28:00
Ao associar pessoas a programas, surge um terceiro tipo de relação, muitos-para-muitos, já que uma pessoa pode atuar em vários programas e um programa pode ter várias pessoas. Isso é resolvido com uma tabela chamada "stars", que liga show_id e person_id. Usando o exemplo de The Office, versão americana de 2005 com 188 episódios e id 386676, é feita uma sequência de subconsultas aninhadas: primeiro descobre-se o id do programa, depois todos os person_id relacionados na tabela stars, e por fim os nomes correspondentes na tabela people. O mesmo processo é feito de forma inversa para descobrir todos os programas em que Steve Carell atuou, partindo do seu id na tabela people, passando pela tabela stars e chegando aos títulos na tabela shows.
Joins explícitos e uso prático em sites reais 1:36:30
As mesmas perguntas também podem ser respondidas unindo diretamente as três tabelas, shows, stars e people, sem subconsultas aninhadas, alinhando shows.id com stars.show_id e stars.person_id com people.id, e filtrando pelo nome Steve Carell. Esse método produz os mesmos resultados, mas com alguma duplicação de dados e desempenho mais lento sem otimização. É apontado que sites como o IMDb.com provavelmente funcionam de forma semelhante, executando consultas SQL pré-fabricadas nas quais a entrada do usuário na busca é inserida dinamicamente. Por fim, discute-se que programadores normalmente precisam conhecer a estrutura interna do banco de dados para escrever essas consultas, embora hoje existam mapeamentos objeto-relacionais, chamados ORMs, que permitem usar bibliotecas capazes de descobrir automaticamente como fazer essas junções, dispensando o conhecimento manual do esquema.
Otimizando consultas com índices 1:40:30
Comparando junções com subconsultas, algumas junções pareciam mais lentas para obter os mesmos dados. Isso levou à ideia de que bancos de dados relacionais, diferente de planilhas, permitem dizer com antecedência como otimizar certas consultas. Usando o comando `timer on` no SQLite, foi medido que buscar todos os programas chamados "The Office" levava 0,042 segundos, algo já rápido, mas otimizável. Otimizar consultas frequentes importa porque torna os usuários mais satisfeitos e economiza dinheiro, já que um servidor com hardware fixo só consegue processar um número limitado de buscas por segundo, e uma consulta mais rápida permite atender mais usuários simultaneamente com o mesmo equipamento.
Criando um índice na tabela 1:43:00
O comando `CREATE INDEX` permite nomear um índice e indicar em qual tabela e coluna o banco deve otimizar buscas. Foi criado um índice chamado `title_index` na coluna `title` da tabela `shows`, o que levou quase meio segundo para ser construído, mas é uma operação feita apenas uma vez. Depois disso, a mesma consulta que antes levava 0,042 segundos passou a levar apenas 0,001 segundo, uma melhora de 42 vezes. Esse índice costuma ser implementado com uma estrutura chamada B-tree, parecida em espírito com uma árvore binária mas com nós que podem ter mais de dois filhos, permitindo que a árvore fique mais rasa e as buscas sejam feitas em tempo logarítmico em vez de busca linear do início ao fim.
Combinando Python e SQL 1:46:01
O curso está chegando ao ponto em que várias linguagens são usadas juntas para resolver um mesmo problema, como acontecerá nas próximas semanas com aplicações web usando Python, SQL, JavaScript, HTML e CSS. A biblioteca do CS50 para Python inclui um módulo que facilita executar comandos SQL dentro de código Python. Isso permite construir programas interativos ou sites que buscam dados em um banco de dados enquanto usam Python para gerar a lógica ou as páginas.
Reescrevendo favorites.py com SQL 1:47:02
Na versão anterior de favorites.py, ordenar o dicionário de contagens por chave ou por valor exigia várias linhas de código, usando `sorted`, a função `key` e o parâmetro `reverse=True`. Usando SQL diretamente, a mesma tarefa é resolvida em uma única linha com `SELECT language, COUNT(*) FROM favorites GROUP BY language ORDER BY COUNT(*) DESC`. A nova versão do programa importa a função `SQL` da biblioteca do CS50, cria uma variável `db` apontando para o arquivo `favorites.db` usando a sintaxe `sqlite:///favorites.db`, e executa a consulta com `db.execute`. O resultado é uma lista de linhas, cada uma um dicionário, permitindo acessar `row["language"]` e `row["n"]` sem precisar de laços complicados.
O risco da injeção de SQL 1:56:01
Ao tornar o programa interativo, pedindo ao usuário que digite seu programa favorito e inserindo essa entrada diretamente numa f-string dentro da consulta SQL, surge um problema grave: se o usuário digitar uma aspa simples seguida de dois traços, como em "malan@harvard.edu' --", isso pode quebrar a lógica da consulta e até permitir login sem senha, já que os dois traços funcionam como comentário em SQL, ignorando o restante do comando. Esse tipo de falha, conhecida como ataque de injeção de SQL, pode ser usado tanto para acessar contas indevidamente quanto para executar comandos destrutivos como `DELETE` ou `DROP`. A solução correta não é higienizar manualmente a entrada nem usar f-strings, mas usar espaços reservados com ponto de interrogação na consulta e passar os valores como argumentos separados para a função `execute`. Assim, a biblioteca escapa automaticamente caracteres perigosos, transformando aspas simples em duplas, e garante que a entrada do usuário nunca seja tratada como parte do código SQL, algo que já é prática comum em muitos sites que restringem certos símbolos em senhas por precaução.
Vazar informações sobre restrições do sistema 2:06:00
Dizer aos usuários quais caracteres eles não podem usar também é uma forma de vazar informação, porque um adversário atento pode deduzir qual linguagem ou tecnologia está por trás do sistema a partir dessas restrições. A lição é que revelar mais detalhes do que o necessário nunca traz vantagem, e por isso vale manter essa desconfiança saudável ao projetar sistemas.
O problema das condições de corrida 2:07:00
Bancos de dados usados por muitos servidores ao mesmo tempo, como acontece em empresas como Meta, Google ou Microsoft, enfrentam condições de corrida. A ideia é explicada com a história de dois colegas de quarto que, sem saber que o outro também notou a falta de leite na geladeira, vão cada um a uma loja diferente compra e acabam com leite em excesso, porque nenhum sabia o que o outro estava fazendo. O mesmo ocorre quando servidores leem e atualizam o número de curtidas de uma publicação do Instagram: dois servidores podem ler o mesmo valor, cada um somar 1, e o resultado final perder uma curtida em vez de somar as duas. Esse tipo de erro representa perda real de dados e é tanto um problema técnico quanto comercial.
Bloqueios e transações como solução 2:12:00
A solução conceitual é bloquear o banco de dados enquanto uma operação está em andamento, de forma parecida a trancar a geladeira ou deixar um bilhete avisando o colega de quarto. Em sistemas grandes isso seria lento se feito no banco inteiro, por isso existem transações, que bloqueiam apenas a linha necessária usando comandos como begin transaction e commit, garantindo que as consultas envolvidas sejam executadas todas juntas ou nenhuma delas, podendo ser revertidas se algo falhar.
Piadas finais sobre injeção de SQL 2:13:35
A aula termina com dois exemplos clássicos de humor sobre injeção de SQL: alguém que tentou apagar o banco de dados de pedágio colocando um comando SQL malicioso na placa do carro, e outro caso em que uma placa com o texto NULL confundiu o sistema. Por fim, é apresentado o personagem do quadrinho XKCD conhecido como Bobby Tables, uma referência canônica no mundo da computação sobre os perigos de não sanitizar entradas.
AI-generated summary. It can be wrong or incomplete - check anything that matters against the original.
