Mock Exam 10

Instructions

This is a broad review mock exam. Answer with code, diagrams, and short explanations.

Task 1: Mixed SQL review, 45 points

1. Load the Olympics CSV files into SQLite.
2. Write SQL queries for:
3. The top ten countries by number of distinct disciplines represented by athletes.
4. The number of events per discipline from schedules.
5. The countries with the highest gold-medal ratio among countries with at least ten total medals.
6. The number of medal winners per nationality.
7. A joined result showing medal records with matching athlete birth dates where possible.
8. Explain the difference between WHERE and HAVING.

Task 2: Model choice, 30 points

For each scenario, choose a method and justify it:

1. You have unlabeled customer behavior data and want natural groups.
2. You have 100 numeric features and need a 2D visualization.
3. You have labeled classes and want a lower-dimensional feature space for classification.
4. You have market baskets and want rules like bread -> cheese.
5. You have private company documents and want an LLM to answer questions from them.
6. You have a pretrained GPT model and want it to classify spam.

Task 3: Attention and generation, 15 points

1. Explain attention in three recurring steps.
2. Explain query, key, and value.
3. Explain why attention scores are scaled before softmax.
4. Explain greedy decoding, temperature, top-k, and top-p.
5. Explain KV caching.

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")

Countries by distinct athlete disciplines:

SELECT country, COUNT(DISTINCT disciplines) AS discipline_count
FROM athletes
GROUP BY country
ORDER BY discipline_count DESC
LIMIT 10;

Events per discipline from schedules:

SELECT discipline, COUNT(DISTINCT event) AS event_count
FROM schedules
GROUP BY discipline
ORDER BY event_count DESC;

Highest gold ratio among countries with at least ten medals:

SELECT country,
       "Gold Medal" AS gold_medals,
       Total AS total_medals,
       1.0 * "Gold Medal" / Total AS gold_ratio
FROM medals_total
WHERE Total >= 10
ORDER BY gold_ratio DESC, total_medals DESC;

Medal winners per nationality:

SELECT nationality, COUNT(*) AS medal_winner_count
FROM medallists
GROUP BY nationality
ORDER BY medal_winner_count DESC;

Medal records with athlete birth dates:

SELECT m.name,
       m.country,
       m.discipline,
       m.event,
       m.medal_type,
       a.birth_date
FROM medals AS m
LEFT JOIN athletes AS a
  ON m.code = a.code
ORDER BY m.country, m.discipline, m.event;

WHERE filters rows before grouping. HAVING filters groups after aggregation, for example HAVING COUNT(*) > 5.

Task 2 answer

Scenario	Method	Justification
Unlabeled customer behavior groups	Clustering, such as k-means or DBSCAN	The goal is unsupervised grouping without labels
100 numeric features to 2D	PCA	PCA gives unsupervised variance-preserving projection
Labeled classes to lower dimensions	LDA	LDA uses labels to preserve class separation
Market baskets and rules	Association rule mining	Support, confidence, and lift describe item co-occurrence rules
Private documents for LLM answers	RAG	Retrieval supplies external context without retraining model weights
GPT model for spam classification	Classification finetuning	Adapt pretrained representations to labeled spam/ham outputs

Task 3 answer

Attention has three recurring steps:

score relationships
-> normalize scores into weights
-> mix value vectors using those weights

The query represents what the current token is looking for. Keys represent what other tokens offer for matching. Values contain the information that will be mixed into the output after weights are computed.

Scores are scaled before softmax to prevent very large dot products from making the softmax too sharp, which can hurt gradient flow and training stability.

Greedy decoding picks the highest-probability next token. Temperature adjusts how sharp the probability distribution is. Top-k samples only from the k most likely tokens. Top-p samples from the smallest token set whose cumulative probability reaches p.

KV caching stores previously computed key and value tensors during generation. It speeds up autoregressive generation because the model does not need to recompute attention information for all previous tokens at every step.