CIS4301 Spring 2025 - Lab 2
1 Software you may need
You may need to transfer local files t o C ISE s erver i n t his l ab ( if you a re using
SQLite on CISE server). So you may need software for the transferring.
For Windows: download WinSCP from https://winscp.net/. Then you
may learn how to use the SCP transferring protocol to upload your files t o a
remote server via WinSCP by their official documentation
(https://winscp.net/eng/docs/start), or you can search online for tutorials.
For Linux and MacOS: the scp command is built-in and can be called in the
terminal. You can find t utorials o nline a bout h ow t o u se t he s cp c ommand to
upload your local files.
2 Preparation for the lab
Download the lab setup package (lab_2_setup.zip) from Canvas and place it in
CISE server, then decompress it and do the following steps (each corresponding
to a command to be run in the CISE server terminal) in the setup package
folder (that is, you must change the current directory to lab_2_setup and
then run the following commands) to prepare for your lab work. Steps (1) -
(2) should be executed outside the database (i.e., in the CISE server terminal).
Steps (3) - (5) should be executed in the database command line. In all the
following steps, if a field i s s urrounded by “ <” a nd “ >”, i t m eans t hat fi eld is a
variable and needs to be replaced with your own value.
(1) Install Python packages:
pip3 install -r requirements.txt
(2) Start SQLite on the target data:
sqlite3 climbers.db
1
1. This command will load the data into SQLite and login to the database.
After running this command the SQLite command line will be displayed.
2. Note that SQLite will NOT persist the data in it, which means you have
to run this command each time when you start the database to manually
load the data.
(3) List tables in database (note that there is a leading dot):
.tables
(4) Show the schema of any table:
.schema <table_name>
(5) Inspect the data in any table (note that there is an ending semi-
colon):
SELECT * FROM <table_name>;
Note: the system commands in SQLite should start with a dot, like “.tables”,
while the SQL statements should end with a semicolon, like the SELECT state-
ment above.
Now you are ready to start your lab work.
3 Lab questions
You are given the following database schema (with primary keys underlined):
PEAK(NAME, ELEV, DIFF, MAP, REGION)
CLIMBER(NAME, SEX)
PARTICIPATED(TRIP_ID, NAME)
CLIMBED(TRIP_ID, PEAK, WHEN_CLIMBED)
The database tables have the following semantics:
1. PEAK gives information about the mountain peaks that the SPCC is
interested in. This table lists the name of each peak, its elevation, its
difficulty level for climbers (on a scale of 1 to 5), the map that it is located
on, and the region of the Sierra Nevada that it is located in.
2. CLIMBER lists the SPCC membership, and gives their name and gender.
3. PARTICPATED gives the set of climbers who participated in each of the
various SPCC-sponsored climbing trips. The number of participants in
each trip varies.
2
4. CLIMBED tells which peaks were climbed on each of the SPCC-sponsored
climbing trips, along with the date that each peak was climbed.
Write SQL queries to answer these questions:
1. List the names of peaks whose difficulty level between 2 and 3 (inclusive).
2. List the names of climbers who have climbed the peak Whaleback.
3. List the regions of the peaks, sorted by their difficulty levels.
4. Count the number of male climbers.
5. Find the total number of female climbers who have climbed the peak
Whaleback.
6. List the names of peaks which have been climbed by more than 5 times,
along with their corresponding number of times.
7. Find the names of peaks with the highest difficulty level.
8. List names and regions of all the peaks whose difficulty level is equal to
the peak Whaleback.
4 Self-assessment
After you make up and test the SQL queries, you are ready to assess them using
the assessment scripts. This lab will NOT be counted into your course grade,
but only for your practice. The instructions of self-assessment are as follows:
1. Download the lab-2 assessment package (lab_2_assessment.zip) from Can-
vas and decompress it on CISE server.
2. In the assessment package folder, fill your SQL queries for each question
into the corresponding places in the script “lab_2_submission.py”. For
example, you need to fill your SQL statement for question 1 into the
string variable “self.query” in method “Q1” of class “Answers”.
• Note that the SQL statements in Python script should NOT include
the ending semicolon. The semicolon is necessary only when you run
the query directly in SQLite command line.
3. Then still in the assessment folder, run the following command to start
the assessment:
python3 lab_2_assessment.py
If your answers to all the questions are correct, a message “All answers
passed.” will be printed, otherwise the wrong answers will be figured out
by the script.
3
4. The assessment package also includes the example solution queries. Please
refer to “lab_2_groundtruth.py” for them. However, there could be mul-
tiple ways to get the same results. So your SQL queries may be different
from the example solutions, which is fine as long as your queries return
the same results with the example solutions.
Note:
(1) You should NOT modify anything except the query strings in “lab_2_submission.py”,
including but not limited to script filename, function names, class name, and
so on. Make sure you only replace the initially empty query strings with your
own SQL statements without any other modification. Otherwise the assessment
script may not work.
(2) If you are not familiar with Python, having trouble running the
script, or do not want to use the script, we also provide solution queries
in a single file, lab_2_solution.sql, within the same assessment package. You
can manually run the solution queries in SQLite and compare the results with
yours.
