Mock Exam 05

Instructions

Use the Olympics Data directory for Task 1. Keep explanations short and precise.

Task 1: SQL and data inspection, 45 points

1. Load the top-level CSV files into a dictionary of DataFrames and create an in-memory SQLite database.
2. Write SQL queries for:
3. The number of athletes per gender.
4. The ten countries with the most coaches.
5. The number of events per sport in the events table.
6. The medal count per discipline and medal type.
7. Countries that have athletes but no medals.
8. Explain each query.

Task 2: Cluster analysis, 30 points

1. Generate a two-dimensional dataset with three blob clusters of different standard deviations.
2. Run k-means with k=3.
3. Plot the clusters and centers.
4. Explain why k-means can struggle when clusters have different spread.
5. Explain how hierarchical clustering represents cluster structure.

Task 3: Neural networks, 15 points

1. Draw the training loop of a neural network.
2. Explain forward propagation, loss, backpropagation, and gradient descent.
3. Explain why the learning rate matters.
4. Compare batch, stochastic, and mini-batch gradient descent.

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 gender:

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

Countries with the most coaches:

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

Events per sport:

SELECT sport, COUNT(*) AS event_count
FROM events
GROUP BY sport
ORDER BY event_count DESC;

Medal count per discipline and medal type:

SELECT discipline, medal_type, COUNT(*) AS medal_count
FROM medals
GROUP BY discipline, medal_type
ORDER BY discipline, medal_type;

Countries with athletes but no medals:

SELECT DISTINCT a.country
FROM athletes AS a
LEFT JOIN medals_total AS mt
  ON a.country_code = mt.country_code
WHERE mt.country_code IS NULL
ORDER BY a.country;

The last query uses a left join and keeps rows where no matching medal-total country exists.

Task 2 answer

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

X, _ = make_blobs(
    n_samples=450,
    centers=[[-4, 0], [0, 0], [4, 0]],
    cluster_std=[0.4, 1.2, 0.7],
    random_state=5,
)

model = KMeans(n_clusters=3, random_state=5, n_init=10)
labels = model.fit_predict(X)

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

k-means assumes roughly compact, center-shaped clusters and assigns points to the nearest centroid. If one cluster is much wider than another, boundary points may be assigned poorly because k-means optimizes squared distance, not density.

Hierarchical clustering builds a tree of merges or splits. A dendrogram shows which points or clusters combine at each distance level, so the user can choose a cut level instead of fixing k immediately.

Task 3 answer

input batch
-> forward pass
-> predictions
-> loss
-> backpropagation
-> gradients
-> optimizer update
-> repeat

Forward propagation computes predictions from inputs through the layers. The loss measures prediction error. Backpropagation uses the chain rule to compute gradients of the loss with respect to parameters. Gradient descent updates weights in the direction that reduces loss.

If the learning rate is too small, training is slow. If it is too large, training can jump over good solutions or diverge. Batch gradient descent uses all examples per update, stochastic uses one example, and mini-batch uses small groups, which is the usual practical compromise.