Skip to content

Mock Exam 07

Instructions

Use clear diagrams for conceptual tasks. Text arrows are acceptable.

Task 1: SQL and generated-query safety, 45 points

  1. Load the Olympics CSV files into an in-memory SQLite database.
  2. Write SQL queries for:
  3. The number of teams per country.
  4. The number of athletes per event.
  5. The number of medal events per day.
  6. Countries whose total medals are above the average total medals.
  7. Disciplines with medals won by at least fifteen different countries.
  8. Explain how an LLM could help with SQL and why generated SQL must be validated.

Task 2: Text classification, 30 points

  1. Draw a classic sentiment-classification pipeline.
  2. Explain bag-of-words and TF-IDF.
  3. Explain why train/test split must happen before final evaluation.
  4. Explain what a scikit-learn pipeline and grid search are used for.
  5. Compare this pipeline with LLM finetuning for spam classification.

Task 3: GPT architecture, 15 points

  1. Draw a GPT block.
  2. Explain layer normalization, masked multi-head attention, feed-forward layers, shortcut connections, and logits.
  3. Explain why causal masking is required.

Answer Key

Task 1 answer

from pathlib import Path
import sqlite3

import pandas as pd

dfs = {path.stem: pd.read_csv(path) for path in Path("Data").glob("*.csv")}
conn = sqlite3.connect(":memory:")
for name, df in dfs.items():
    df.to_sql(name, conn, index=False, if_exists="replace")

Teams per country:

SELECT country, COUNT(*) AS team_count
FROM teams
GROUP BY country
ORDER BY team_count DESC;

Athletes per event:

SELECT events, COUNT(*) AS athlete_count
FROM athletes
GROUP BY events
ORDER BY athlete_count DESC;

This treats the list-like events field as text. A normalized event-athlete table would support cleaner analysis.

Medal events per day:

SELECT medal_date, COUNT(DISTINCT event) AS medal_event_count
FROM medals
GROUP BY medal_date
ORDER BY medal_date;

Countries above average total medals:

SELECT country, Total
FROM medals_total
WHERE Total > (SELECT AVG(Total) FROM medals_total)
ORDER BY Total DESC;

Disciplines with medals won by at least fifteen countries:

SELECT discipline, COUNT(DISTINCT country_code) AS country_count
FROM medals
GROUP BY discipline
HAVING COUNT(DISTINCT country_code) >= 15
ORDER BY country_count DESC;

LLMs can help draft SQL, explain queries, translate between dialects, and debug errors. Generated SQL must be validated because it can use wrong columns, hallucinate tables, create bad joins, leak data, or mutate data if permissions allow it. Safe execution uses schema context, read-only permissions, query validation, logging, and human review for risky actions.

Task 2 answer

raw reviews
-> clean text
-> tokenize
-> vectorize with bag-of-words or TF-IDF
-> train classifier
-> evaluate on held-out test set
-> tune with validation or cross-validation

Bag-of-words represents text by token counts and loses most word order. TF-IDF scales counts so frequent words across many documents matter less, while document-specific terms matter more.

The final test set must be held out so it estimates performance on unseen data. If the test set is used during tuning, the estimate becomes too optimistic.

A scikit-learn pipeline chains preprocessing and modeling so the same transformations are applied consistently. Grid search tries combinations of hyperparameters using cross-validation.

LLM spam finetuning starts with a pretrained language model and adapts it to labeled spam/ham examples. Instead of sparse word-count vectors, it uses token IDs, embeddings, transformer layers, and a classification head or last-token classification.

Task 3 answer

input embeddings
-> layer norm
-> masked multi-head self-attention
-> shortcut add
-> layer norm
-> feed-forward network
-> shortcut add
-> output representations
-> vocabulary logits

Layer normalization stabilizes activations. Masked multi-head attention lets tokens use earlier context through several attention heads. Feed-forward layers transform each token representation. Shortcut connections help gradients flow and preserve information. Logits are raw vocabulary scores for next-token prediction.

Causal masking is required because GPT is trained to predict the next token from previous tokens only. Without the mask, the model could peek at future tokens during training.