Mock Exam 03¶

Instructions¶

Answer with runnable code where code is requested, and with concise explanations where concepts are requested.

Task 1: Databases, SQL, and Pandas, 45 points¶

Load the Olympics CSV files into a dictionary of DataFrames.
Create an in-memory SQLite database.
Write SQL queries for:
The number of scheduled sessions per venue.
The first and last scheduled day per discipline.
The number of athletes born in each birth country, ordered by count.
The countries with gold medals in at least three disciplines.
Disciplines where the medal table contains gold, silver, and bronze medals.
For one query, explain how you would solve the same task in Pandas and when Pandas would be preferable.

Task 2: PCA, LDA, and clustering, 30 points¶

Load or generate a classification-style dataset with many numeric features and three classes.
Standardize the features.
Project the data to two dimensions using PCA.
Project the data to two dimensions using LDA.
Explain how PCA and LDA differ and why LDA cannot be used without labels.
Explain whether either projection proves that a classifier will perform well.

Task 3: LLM systems, 15 points¶

Draw the flow for pretraining a GPT-style model.
Draw the flow for classification finetuning.
Draw the flow for instruction finetuning.
Compare RAG and finetuning.
Explain one risk in evaluating LLMs with benchmark scores.

Answer Key¶

Task 1 answer¶

from pathlib import Path
import sqlite3

import pandas as pd

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

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

Scheduled sessions per venue:

SELECT venue, COUNT(*) AS session_count
FROM schedules
GROUP BY venue
ORDER BY session_count DESC;

First and last scheduled day per discipline:

SELECT discipline,
       MIN(day) AS first_day,
       MAX(day) AS last_day,
       COUNT(*) AS session_count
FROM schedules
GROUP BY discipline
ORDER BY first_day;

Athletes born in each birth country:

SELECT birth_country, COUNT(*) AS athlete_count
FROM athletes
WHERE birth_country IS NOT NULL AND birth_country <> ''
GROUP BY birth_country
ORDER BY athlete_count DESC;

Countries with gold medals in at least three disciplines:

SELECT country, COUNT(DISTINCT discipline) AS gold_disciplines
FROM medals
WHERE medal_type = 'Gold Medal'
GROUP BY country
HAVING COUNT(DISTINCT discipline) >= 3
ORDER BY gold_disciplines DESC;

Disciplines containing all three medal types:

SELECT discipline, COUNT(DISTINCT medal_type) AS medal_type_count
FROM medals
WHERE medal_type IN ('Gold Medal', 'Silver Medal', 'Bronze Medal')
GROUP BY discipline
HAVING COUNT(DISTINCT medal_type) = 3
ORDER BY discipline;

Pandas version of scheduled sessions per venue:

sessions_per_venue = (
    dataframes["schedules"]
    .groupby("venue")
    .size()
    .reset_index(name="session_count")
    .sort_values("session_count", ascending=False)
)

SQL is preferable when the data already lives in a database or the task is mostly filtering, joining, and aggregation. Pandas is preferable when the data is already in Python and the next step is cleaning, reshaping, plotting, or model preparation.

Task 2 answer¶

import matplotlib.pyplot as plt
from sklearn.datasets import load_wine
from sklearn.decomposition import PCA
from sklearn.discriminant_analysis import LinearDiscriminantAnalysis as LDA
from sklearn.preprocessing import StandardScaler

data = load_wine()
X = data.data
y = data.target

X_std = StandardScaler().fit_transform(X)

pca = PCA(n_components=2)
X_pca = pca.fit_transform(X_std)

lda = LDA(n_components=2)
X_lda = lda.fit_transform(X_std, y)

fig, axes = plt.subplots(1, 2, figsize=(10, 4))
axes[0].scatter(X_pca[:, 0], X_pca[:, 1], c=y, cmap="viridis", s=20)
axes[0].set_title("PCA")
axes[1].scatter(X_lda[:, 0], X_lda[:, 1], c=y, cmap="viridis", s=20)
axes[1].set_title("LDA")
plt.show()

PCA is unsupervised and finds directions that preserve variance. LDA is supervised and finds directions that separate known classes by increasing between-class separation and reducing within-class spread. LDA cannot run without labels because the labels define the classes it tries to separate.

A good 2D projection does not prove a classifier will perform well. It is only a lower-dimensional view. You still need proper train/test splitting, training, and evaluation.

Task 3 answer¶

Pretraining flow:

raw text -> tokens -> token IDs -> input-target windows
-> embeddings -> transformer blocks -> logits
-> next-token loss -> update weights

Classification finetuning flow:

pretrained GPT -> replace or adapt output head
-> labeled examples -> predict class logits
-> cross-entropy loss -> update selected or all weights

Instruction finetuning flow:

pretrained GPT -> instruction/input/response examples
-> prompt formatting -> masked/padded batches
-> train to generate response tokens
-> evaluate response quality

RAG retrieves external context at prompt time and usually does not change model weights. Finetuning changes model weights so the model adapts to a task, domain, or response style. RAG is often better for current, private, or changing knowledge; finetuning is better for stable behavior patterns.

Benchmark scores can be misleading because benchmark examples may appear in training data, scores may not reflect the user’s real task, and a single score can hide failures in reasoning, formatting, or robustness.