Mock Exam 02

Instructions

Use the Olympics Data directory. Prefer clear, small code over overly general code.

Task 1: Databases and SQL, 45 points

1. Load the top-level CSV files into DataFrames and create an in-memory SQLite database.
2. Write SQL queries for:
3. The number of athletes per discipline.
4. The five venues hosting the most distinct sports.
5. Medal counts by gender.
6. Countries that have both coaches and athletes in the dataset.
7. Disciplines where at least ten countries won medals.
8. Explain each query in one or two sentences.

Task 2: Clustering and method choice, 30 points

1. Generate a dataset with four compact blob clusters and 40 random noise points.
2. Run k-means with k=4 and plot the result.
3. Explain how noise can affect k-means centers.
4. Explain whether DBSCAN might be a better choice.
5. Explain whether scaling matters for clustering.

Task 3: Generative AI, 15 points

1. Compare classic sentiment-analysis preprocessing with LLM text preprocessing.
2. Explain bag-of-words, TF-IDF, token IDs, embeddings, and positional embeddings.
3. Explain pretraining versus finetuning.
4. Explain why a pretrained GPT model produces poor output before training but can generate coherent output after pretraining.

Answer Key

Task 1 answer

from pathlib import Path
import sqlite3

import pandas as pd

dataframes = {
    path.stem: pd.read_csv(path)
    for path in Path("Data").glob("*.csv")
}

conn = sqlite3.connect(":memory:")
for name, df in dataframes.items():
    df.to_sql(name, conn, index=False, if_exists="replace")

Athletes per discipline:

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

This counts athlete rows for each discipline-list value. A stronger answer may note that disciplines is stored like a list in text, so exact multi-discipline normalization is limited.

Venues hosting the most distinct sports:

SELECT venue, sports
FROM venues
ORDER BY LENGTH(sports) DESC
LIMIT 5;

Because sports is stored as a text representation of a list, SQLite cannot directly count list elements without preprocessing. This answer is acceptable if the limitation is explained. A Pandas preprocessing answer that explodes the list before writing to SQL is stronger.

Medal counts by gender:

SELECT gender, COUNT(*) AS medal_count
FROM medals
GROUP BY gender
ORDER BY medal_count DESC;

This groups medal rows by the gender field in the medal table.

Countries with both coaches and athletes:

SELECT DISTINCT a.country
FROM athletes AS a
JOIN coaches AS c
  ON a.country_code = c.country_code
ORDER BY a.country;

This joins by country code and keeps countries appearing in both tables.

Disciplines where at least ten countries won medals:

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

This uses HAVING because the filter depends on an aggregate value.

Task 2 answer

import matplotlib.pyplot as plt
import numpy as np
from sklearn.cluster import KMeans
from sklearn.datasets import make_blobs

X_blobs, _ = make_blobs(
    n_samples=360,
    centers=4,
    cluster_std=0.65,
    random_state=11,
)
rng = np.random.default_rng(11)
noise = rng.uniform(low=-10, high=10, size=(40, 2))
X = np.vstack([X_blobs, noise])

model = KMeans(n_clusters=4, random_state=11, n_init=10)
labels = model.fit_predict(X)

plt.scatter(X[:, 0], X[:, 1], c=labels, s=18, cmap="viridis")
plt.scatter(
    model.cluster_centers_[:, 0],
    model.cluster_centers_[:, 1],
    c="red",
    marker="X",
    s=180,
)
plt.show()

Noise can pull k-means centers away from the true compact groups because k-means minimizes squared distance to centroids. DBSCAN may be better because it can mark sparse points as noise and find dense regions without preselecting k. Scaling matters because distance-based methods treat large-scale features as more important unless features are standardized.

Task 3 answer

Classic sentiment analysis often cleans text aggressively: lowercasing, removing HTML, removing stop words, stemming, and converting documents into bag-of-words or TF-IDF vectors. LLM preprocessing usually keeps more of the original text structure because punctuation, word endings, capitalization, and order can carry useful information.

Bag-of-words counts token occurrences and mostly loses order. TF-IDF downweights common terms and upweights terms that are more specific to a document. Token IDs are integer identifiers for tokens. Embeddings turn token IDs into learned dense vectors. Positional embeddings add order information because the model otherwise sees a set of token vectors without sequence position.

Pretraining learns general language patterns from large unlabeled text through next-token prediction. Finetuning adapts a pretrained model to a specific task, such as spam classification, or to an instruction-following response style.

An untrained GPT architecture has random weights, so its output is essentially random. After pretraining, the weights encode statistical patterns of language, syntax, facts, and task-relevant representations, so next-token predictions become coherent.