Mock Exam 08¶

Instructions¶

This mock exam mixes practical SQL with conceptual AI infrastructure.

Task 1: SQL with dates and grouping, 45 points¶

Load the Olympics CSV files into SQLite.
Write SQL queries for:
The first ten torch route stages by start date.
The number of scheduled sessions per day.
The number of venues per location code in schedules.
The top countries by bronze medals.
Events that appear in both events and medals.
Explain how date strings can affect analysis.

Task 2: Vector databases and clustering, 30 points¶

Explain what an embedding is.
Explain similarity search.
Explain why approximate nearest neighbor search is used.
Compare HNSW, IVF, and PQ at a high level.
Explain how k-means can appear inside vector indexing.

Task 3: Reasoning models and evaluation, 15 points¶

Explain benchmark evaluation, verifier-based evaluation, and LLM-as-judge.
Explain answer extraction and normalization.
Explain why reasoning evaluation is hard.
Explain one way inference-time scaling can improve reasoning.

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

First torch stages:

SELECT title, city, date_start, date_end
FROM torch_route
ORDER BY date_start
LIMIT 10;

Scheduled sessions per day:

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

Venues per location code:

SELECT location_code, COUNT(DISTINCT venue) AS venue_count
FROM schedules
GROUP BY location_code
ORDER BY venue_count DESC;

Top countries by bronze medals:

SELECT country, "Bronze Medal" AS bronze_medals, Total
FROM medals_total
ORDER BY "Bronze Medal" DESC
LIMIT 10;

Events appearing in both events and medals:

SELECT DISTINCT e.event, e.sport
FROM events AS e
JOIN medals AS m
  ON e.event = m.event
ORDER BY e.sport, e.event;

Date strings sort correctly only when they use consistent ISO-like formats. Mixed formats, time zones, missing values, and text dates can make ordering and duration calculations wrong unless parsed carefully.

Task 2 answer¶

An embedding is a numeric vector representation of content such as text, image, audio, user behavior, or an item. Similar meanings should land near each other in vector space.

Similarity search embeds a query and retrieves stored vectors that are close to it, often using cosine similarity, dot product, or Euclidean distance.

Approximate nearest neighbor search is used because exact comparison against every vector is too slow at large scale. It trades a small amount of recall for much faster retrieval.

HNSW builds a graph that lets search move through nearby neighbors efficiently. IVF partitions vectors into clusters and searches only promising partitions. PQ compresses vectors so storage and distance calculations become cheaper.

k-means can be used in indexing methods such as IVF to create centroids. New vectors are assigned to nearby centroids, and search can focus on the most relevant clusters.

Task 3 answer¶

Benchmark evaluation uses standardized datasets and compares answers to references. Verifier-based evaluation checks answers with rules, symbolic tools, or task-specific validators. LLM-as-judge asks another language model to score or compare outputs.

Answer extraction finds the final answer from a generated response, such as text inside a boxed expression. Normalization removes harmless formatting differences so equivalent answers can match.

Reasoning evaluation is hard because a final answer can be right for the wrong reason, generated reasoning can be plausible but false, benchmark data may be contaminated, and real tasks often require more than exact-match scoring.

Inference-time scaling can improve reasoning by generating multiple solutions and using self-consistency voting, adding verification steps, or allowing more search before finalizing an answer.