Mock Exam 01

Instructions

Use the CSV files in the Olympics Data directory. Clearly state if you use an AI assistant for any code or explanation.

Task 1: Databases and SQL, 45 points

1. Import only the libraries needed to load CSV files, inspect them, create an in-memory SQLite database, and run SQL queries.
2. Load all top-level CSV files into a dictionary of DataFrames. Print each table name, shape, and column list.
3. Create an in-memory SQLite database from the DataFrames.
4. Write SQL queries for:
5. The ten countries with the most athletes.
6. The number of medal events per discipline.
7. The top ten countries by total medals.
8. Countries with medals in both Cycling Road and Swimming.
9. The number of male and female athletes per country for the ten largest delegations.
10. Show each result and briefly explain the SQL idea.

Task 2: Clustering and dimensionality reduction, 30 points

1. Generate a two-dimensional dataset with 400 points in five visible blob areas.
2. Fit k-means and visualize the cluster labels and cluster centers.
3. Use inertia or silhouette score to justify a good number of clusters.
4. Explain whether PCA or LDA would help here.
5. Name one case where DBSCAN would be better than k-means.

Task 3: Generative AI, 15 points

1. Draw the machine learning application workflow from problem definition to monitoring.
2. Extend the diagram by placing data cleansing and dimensionality reduction.
3. Explain the main steps in preparing text for GPT-style pretraining.
4. Explain attention using query, key, value, scores, weights, and causal masking.

Answer Key

Task 1 answer

from pathlib import Path
import sqlite3

import pandas as pd

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

for name, df in dataframes.items():
    print(name, df.shape)
    print(df.columns.tolist())

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

Ten countries with the most athletes:

SELECT country, COUNT(*) AS athlete_count
FROM athletes
GROUP BY country
ORDER BY athlete_count DESC
LIMIT 10;

This groups athlete rows by country and counts rows per group.

Number of medal events per discipline:

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

This counts unique medal events inside each discipline.

Top ten countries by total medals:

SELECT country, Total
FROM medals_total
ORDER BY Total DESC
LIMIT 10;

This uses the already aggregated medal table and sorts by total medals.

Countries with medals in both Cycling Road and Swimming:

SELECT DISTINCT m1.country
FROM medals AS m1
JOIN medals AS m2
  ON m1.country_code = m2.country_code
WHERE m1.discipline = 'Cycling Road'
  AND m2.discipline = 'Swimming'
ORDER BY m1.country;

This self-joins the medal table so the same country must satisfy both discipline conditions.

Male and female athletes per country for the ten largest delegations:

WITH country_sizes AS (
    SELECT country, COUNT(*) AS athlete_count
    FROM athletes
    GROUP BY country
    ORDER BY athlete_count DESC
    LIMIT 10
)
SELECT a.country,
       SUM(CASE WHEN a.gender = 'Male' THEN 1 ELSE 0 END) AS male_athletes,
       SUM(CASE WHEN a.gender = 'Female' THEN 1 ELSE 0 END) AS female_athletes,
       COUNT(*) AS total_athletes
FROM athletes AS a
JOIN country_sizes AS c
  ON a.country = c.country
GROUP BY a.country
ORDER BY total_athletes DESC;

The common table expression first finds the largest delegations, then the outer query counts genders inside those countries.

Task 2 answer

import matplotlib.pyplot as plt
from sklearn.cluster import KMeans
from sklearn.datasets import make_blobs
from sklearn.metrics import silhouette_score

X, _ = make_blobs(
    n_samples=400,
    centers=5,
    cluster_std=0.75,
    random_state=7,
)

scores = []
inertias = []
for k in range(2, 9):
    model = KMeans(n_clusters=k, random_state=7, n_init=10)
    labels = model.fit_predict(X)
    inertias.append(model.inertia_)
    scores.append((k, silhouette_score(X, labels)))

model = KMeans(n_clusters=5, random_state=7, n_init=10)
labels = model.fit_predict(X)
centers = model.cluster_centers_

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

A good answer chooses k=5 because the data was generated with five visible blob areas, and the elbow or silhouette comparison should support that choice. PCA is not needed because the generated data is already two-dimensional. LDA is not appropriate because clustering is unsupervised and LDA needs labels. DBSCAN can be better when clusters are irregularly shaped, when there is noise, or when the number of clusters is not known in advance.

Task 3 answer

problem definition
-> data collection
-> data understanding
-> data cleansing
-> feature engineering / representation
-> optional dimensionality reduction
-> train / validation / test split
-> model selection
-> training
-> evaluation
-> tuning
-> deployment
-> monitoring

Data cleansing belongs before feature engineering and modeling. Dimensionality reduction usually happens after cleaning, numeric representation, and scaling, often inside a training pipeline.

GPT-style text preparation:

raw text
-> tokenization
-> token IDs
-> input-target sliding windows
-> token embeddings + positional embeddings
-> transformer blocks
-> logits over vocabulary
-> next-token prediction loss

Attention computes how much each token should use information from other tokens. Queries and keys produce attention scores, softmax turns scores into attention weights, and the weights mix value vectors. A GPT-style causal mask prevents a token from attending to future tokens.