Skip to content

Mock Exam 06

Instructions

This exam emphasizes database reasoning and dimensionality reduction.

Task 1: SQL joins and aggregation, 45 points

  1. Load the Olympics CSV files into SQLite.
  2. Write SQL queries for:
  3. The number of scheduled sessions per discipline and gender.
  4. Venues used by more than one discipline.
  5. Countries with at least one gold, one silver, and one bronze medal.
  6. The average athlete height per country, ignoring zero or missing heights.
  7. Disciplines where the same country won more than five medals.
  8. Explain one limitation of using CSV files as relational database tables.

Task 2: PCA and preprocessing, 30 points

  1. Generate or load a numeric dataset with at least ten features.
  2. Standardize the features.
  3. Fit PCA and show explained variance ratios.
  4. Choose the number of components needed to preserve about 90 percent of variance.
  5. Explain why standardization matters.

Task 3: LLM text pipeline, 15 points

  1. Draw the pipeline from raw text to input tensors.
  2. Explain tokenization, vocabulary, token IDs, sliding windows, and targets.
  3. Explain token embeddings and positional embeddings.
  4. Explain why LLM text preprocessing usually does not remove stop words.

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

Sessions per discipline and gender:

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

Venues used by more than one discipline:

SELECT venue, COUNT(DISTINCT discipline) AS discipline_count
FROM schedules
GROUP BY venue
HAVING COUNT(DISTINCT discipline) > 1
ORDER BY discipline_count DESC;

Countries with all three medal types:

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

Average athlete height:

SELECT country, AVG(height) AS avg_height
FROM athletes
WHERE height IS NOT NULL AND height > 0
GROUP BY country
ORDER BY avg_height DESC;

Disciplines where a country won more than five medals:

SELECT discipline, country, COUNT(*) AS medal_count
FROM medals
GROUP BY discipline, country
HAVING COUNT(*) > 5
ORDER BY medal_count DESC;

CSV files often contain list-like fields, inconsistent types, missing values, and weak relationship constraints. A real relational database would usually normalize repeated structures and enforce keys.

Task 2 answer

import numpy as np
from sklearn.datasets import load_wine
from sklearn.decomposition import PCA
from sklearn.preprocessing import StandardScaler

data = load_wine()
X_std = StandardScaler().fit_transform(data.data)

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

ratios = pca.explained_variance_ratio_
cumulative = np.cumsum(ratios)
print(ratios)
print(cumulative)

n_components = np.argmax(cumulative >= 0.90) + 1
print(n_components)

PCA finds directions with large variance. If one feature has a much larger scale, it can dominate variance even if it is not more meaningful. Standardization puts features on comparable scales before PCA learns principal components.

The selected number of components is the first point where cumulative explained variance reaches about 90 percent. The exact number depends on the dataset.

Task 3 answer

raw text
-> tokenizer
-> token strings/subwords
-> token IDs
-> sliding windows
-> input IDs and shifted target IDs
-> token embeddings + positional embeddings
-> model

Tokenization splits text into model-readable units. The vocabulary maps tokens to integer IDs. Sliding windows create fixed-length sequences from long text. Targets are usually the same sequence shifted by one position, because GPT-style models learn next-token prediction.

Token embeddings map token IDs to learned vectors. Positional embeddings add information about token order. LLM preprocessing usually keeps stop words, punctuation, and word forms because the model learns natural language structure from them.