Skip to content

Mock Exam 04

Instructions

This mock exam emphasizes explanation. Code answers should still be executable in a normal notebook environment.

Task 1: Data management and SQL, 45 points

  1. Load the Olympics CSV files and inspect table shapes and columns.
  2. Create a temporary SQLite database.
  3. Write SQL queries for:
  4. The number of coaches per country.
  5. The number of technical officials per discipline.
  6. The top ten disciplines by number of scheduled medal events.
  7. Countries that appear in medals_total but have no athletes in athletes.
  8. For each country, the ratio of gold medals to total medals, sorted by ratio.
  9. Explain what can go wrong when generated SQL is executed without validation.

Task 2: Association, clustering, and recommendation, 30 points

  1. Explain support, confidence, and lift using a small basket example.
  2. Explain how Apriori finds frequent itemsets.
  3. Explain how clustering can support recommendation or semantic search.
  4. Generate a simple 2D blob dataset, run k-means, and plot the result.
  5. Explain how vector databases use embeddings and similarity search.

Task 3: Reasoning, RAG, and inference-time scaling, 15 points

  1. Explain the basic RAG pipeline.
  2. Explain agentic RAG versus simple RAG.
  3. Explain greedy decoding, temperature, top-p sampling, and self-consistency.
  4. Explain why inference-time scaling can improve answers but increases cost.

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

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

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

Coaches per country:

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

Technical officials per discipline:

SELECT disciplines, COUNT(*) AS official_count
FROM technical_officials
GROUP BY disciplines
ORDER BY official_count DESC;

Top disciplines by scheduled medal events:

SELECT discipline, COUNT(*) AS medal_session_count
FROM schedules
WHERE event_medal = 1
GROUP BY discipline
ORDER BY medal_session_count DESC
LIMIT 10;

Countries in medals_total but not in athletes:

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

Gold-to-total medal ratio:

SELECT country,
       "Gold Medal" AS gold_medals,
       Total AS total_medals,
       1.0 * "Gold Medal" / NULLIF(Total, 0) AS gold_ratio
FROM medals_total
ORDER BY gold_ratio DESC, total_medals DESC;

Generated SQL can select the wrong table, join on the wrong key, leak sensitive data, run expensive queries, mutate data, or produce plausible but wrong results. Safe systems validate SQL, restrict permissions, use read-only credentials, log generated queries, and require human approval for risky operations.

Task 2 answer

If 100 baskets exist and 20 contain bread and cheese, support for {bread, cheese} is 20 / 100 = 0.20. If 30 baskets contain bread and 20 of those also contain cheese, confidence for bread -> cheese is 20 / 30 = 0.67. Lift compares that confidence to the baseline probability of cheese; lift above 1 means the items co-occur more than expected by chance.

Apriori starts with frequent one-itemsets, then builds larger candidate itemsets only from itemsets that were already frequent. It prunes candidates because if an itemset is infrequent, any larger itemset containing it cannot be frequent.

Clustering can group similar users, products, documents, or embeddings. In recommendation, clusters can suggest similar items or users. In semantic search, embeddings place meaning into vectors, and nearby vectors are treated as semantically related.

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

X, _ = make_blobs(
    n_samples=300,
    centers=4,
    cluster_std=0.8,
    random_state=21,
)

model = KMeans(n_clusters=4, random_state=21, 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()

A vector database stores embeddings plus metadata. At query time, the query is embedded into the same vector space, and similarity search retrieves nearby vectors. Approximate nearest neighbor methods are used when exact search is too slow at large scale.

Task 3 answer

Basic RAG pipeline:

documents -> chunking -> embeddings -> vector index
user question -> query embedding -> retrieve chunks
-> rank/context assembly -> prompt LLM -> answer

Simple RAG usually follows a fixed retrieval path. Agentic RAG gives the system more control: it can choose tools, search multiple sources, retry retrieval, inspect intermediate results, and revise its plan.

Greedy decoding always chooses the highest-probability next token. Temperature changes how sharp or flat the probability distribution is before sampling. Top-p sampling keeps the smallest set of tokens whose cumulative probability reaches p, then samples from that set. Self-consistency generates multiple reasoning paths and selects the most common or best-supported answer.

Inference-time scaling can improve answers because the system spends more computation on search, sampling, verification, or voting. The trade-off is higher latency, higher cost, and more engineering complexity.