UC Berkeley DATA 101 / CS 187 Data Engineering Fall 2025 Midterm Exam Solutions | Correct Answers & Step-by-Step Explanations | 2026 Updated Study Guide | Computer Science & Data Engineering Exam Preparation

Data 101/CS 187: Data Engineering
Midterm Exam Solutions
UC Berkeley, Fall 2025
October 16, 2025

Name:

Email: @berkeley.edu

Student ID:

Examination room:

Name of the student on your left:

Name of the student on your right:



Instructions
Do not open the examination until you are instructed to do so.
This exam consists of 101 points spread over 6 questions (including the Honor Code), and
must be completed in the 110-minute time period on October 16, 2025, 7:10pm – 9:00pm unless
you have pre-approved accommodations otherwise.
For multiple-choice questions, select one choice for circular bubble options, and select all
choices that apply for box bubble options. In either case, please indicate your answer(s) by
fully shading in the corresponding circle/box.
Make sure to write your SID on each page to ensure that your exam is graded.



Honor Code [1 pt]

As a member of the UC Berkeley community, I act with honesty, integrity, and respect for
others. I am the person whose name is on the exam, and I completed this exam in accordance
with the Honor Code.

Signature:




Page 1 of 35

,Data 101/CS 187 Fall 2025 Midterm Exam SID:

Chapter 1: This One’s for the Books [24 pt]
CREATE TABLE students {
CREATE TABLE libraries {
id INT,
name VARCHAR(60),
name VARCHAR(30) NOT NULL,
hours VARCHAR(10) NOT NULL,
email VARCHAR(50) NOT NULL,
study_spaces BOOLEAN,
major VARCHAR(40) NOT NULL,
max_capacity INT,
second_major VARCHAR(40),
snacks_allowed BOOLEAN,
minor VARCHAR(40),
PRIMARY KEY name
PRIMARY KEY id
};
};
study_spaces attribute indicates whether the library can be used for studying.
1.1. [5 pt] UC Berkeley knows that its students love the libraries, so it wanted to conduct a survey
to figure out which libraries were students’ favorites. Each student filled out the survey once
and was only allowed to select one library as their favorite. This resulted in the following
survey_results relation:
CREATE TABLE survey_results {
id INT UNIQUE,
fav_lib VARCHAR(60),

FOREIGN KEY (id) REFERENCES students (id),
FOREIGN KEY (fav_lib) REFERENCES libraries (name)
};
Write a query to return the 5 most popular libraries according to the survey (aka the libraries
that received the greatest number of votes). When multiple libraries have received the same
number of votes, break ties by sorting the library names in alphabetical order.


SELECT

FROM




;




Page 2 of 35

,Data 101/CS 187 Fall 2025 Midterm Exam SID:


Solution:
SELECT fav_lib
FROM survey_results
GROUP BY fav_lib
ORDER BY COUNT(*) DESC, fav_lib ASC
LIMIT 5;




Page 3 of 35

, Data 101/CS 187 Fall 2025 Midterm Exam SID:

1.2. You create a materialized view called students_lib that performs a join on the students
and survey_results relations to save the combined students and survey data.
CREATE MATERIALIZED VIEW students_lib AS (
SELECT s.id AS id, name, email, major, second_major, minor, fav_lib
FROM students AS s
JOIN survey_results AS sr
ON s.id = sr.id);
You are not sure if it would have made more sense to create a new table, a view, or to use a
CTE instead. Given the following conditions, select the best ways to save the results of your
query to reduce query cost or the amount of code you have to write (select all that apply
for each setting).
i. [1 pt] The survey is halfway completed and students are still responding. You want to
include newly added responses in the queries you write referencing the joined tables.

□ A. TABLE
□ B. MATERIALIZED VIEW
□ C. VIEW
□ D. CTE
□ E. SUBQUERY
ii. [1 pt] You only need to join students and survey_results for one query, with all subse-
quent queries operating on students or survey_results alone.

□ A. TABLE
□ B. MATERIALIZED VIEW
□ C. VIEW
□ D. CTE
□ E. SUBQUERY
iii. [1 pt] The survey has closed, and now you want to do analysis on the results.

□ A. TABLE
□ B. MATERIALIZED VIEW
□ C. VIEW
□ D. CTE
□ E. SUBQUERY


Page 4 of 35