Lightspeed
Problem to Solve
In 1880, Albert A. Michelson ran a set of experiments to approximately measure the speed of light (then unknown!). The experiments paved the way towards our understanding of light as we know it today.
In a database called lightspeed.db
, within a table called observations
, use SQL to analyze the data from Michelson’s experiments.
Distribution Code
For this problem, you’ll need to download lightspeed.db
, along with a few .sql
files in which you’ll write your queries.
Download the distribution code
Log into cs50.dev, click on your terminal window, and execute cd
by itself. You should find that your terminal window’s prompt resembles the below:
$
Next execute
wget https://cdn.cs50.net/sql/2024/spring/ao/lightspeed.zip
in order to download a ZIP called lightspeed.zip
into your codespace.
Then execute
unzip lightspeed.zip
to create a folder called lightspeed
. You no longer need the ZIP file, so you can execute
rm lightspeed.zip
and respond with “y” followed by Enter at the prompt to remove the ZIP file you downloaded.
Now type
cd lightspeed
followed by Enter to move yourself into (i.e., open) that directory. Your prompt should now resemble the below.
lightspeed/ $
If all was successful, you should execute
ls
and see a database named lightspeed.db
alongside a few .sql
files. Executing sqlite3 lightspeed.db
should open the database in sqlite3
, via which you’ll execute SQL queries. If not, retrace your steps and see if you can determine where you went wrong!
Schema
Michelson’s data includes 100 trials across 5 separate experiments. For each trial, Michelson recorded the approximate speed of light in kilometers per second.
In lightspeed.db
you’ll find a single table, observations
. In the observations
table, you’ll find the following columns:
id
, which uniquely identifies each observation of the speed of lightexperiment
, which is the experiment number (1–5)trial
, which is the trial number within the experiment (1–20)speed
, which is the recorded speed of light in kilometers per second
Specification
1.sql
In 1.sql
, write a query to return the average speed of light across all experiments.
- Your query should return a table with a single column, called “average_speed”, and a single row.
2.sql
In 2.sql
, write a query to return the average speed of light within each experiment.
- Your query should return a table with two columns: one called “experiment”, which contains the experiment number, and one called “average_speed”, which is the average speed of light recorded in that experiment.
3.sql
In 3.sql
, write a query to find the 5 most frequently recorded observations of speed across all experiments and the number of times that observation of speed was recorded.
- Your query should return a table with two columns: one called “speed”, which is the speed of light recorded, and one called “count”, which is the number of times that observation appears across all experiments.
4.sql
In 4.sql
, write a query to find the speed(s) of light found in common across all 5 experiments.
- Your query should return a table with a single column, “speed”, representing the speed(s) of light common across all 5 experiments.
Usage
To test your queries as you write them in your .sql
files, you can query the database by running
.read FILENAME
where FILENAME
is the name of the file containing your SQL query. For example,
.read 1.sql
Assessment
This problem will be assessed as other query-based problems have been: along the axes of correctness, design, and style. As with past problems, your score is computed by summing the points you receive across each axis.
check50
check50 cs50/problems/2024/sql/lightspeed
How to Submit
After you submit, be sure to check your autograder results. If you see SUBMISSION ERROR: missing files (0.0/1.0)
, it means your file was not named exactly as prescribed (or you uploaded it to the wrong problem).
Correctness in submissions entails everything from reading the specification, writing code that is compliant with it, and submitting files with the correct name. If you see this error, you should resubmit right away, making sure your submission is fully compliant with the specification. The staff will not adjust your filenames for you after the fact!
Ensure your terminal’s working directory is the lightspeed
folder. Your prompt should resemble the below:
lightspeed/ $
When you type ls
to list the files in your working directory, you should see your .sql
files for lightspeed
.
Zip up your solution files by executing the following:
zip lightspeed-solutions.zip *.sql
Want to learn about this command?
Notice this command has three parts:
zip
, which is the name of the tool which will create a.zip
filelightspeed-solutions.zip
, which is the name of the.zip
file to create*.sql
, which represents the file(s) to include.*.sql
matches all files that end with.sql
, as*
is a “wildcard” character which matches any set of characters (similar to%
in SQL!).
Next, download lightspeed-solutions.zip
by control-clicking or right-clicking on the file in your codespace’s file browser and choosing Download.
Go to CSCI E-151’s Gradescope page.
Click Assessment Opportunity: Lightspeed.
Drag and drop your .zip
file to the area that says Drag & Drop. Be sure that each .sql
file is correctly named exactly as prescribed above, lest the autograder fail to run on your submission! Note that your submission is considered incomplete if any of the files are missing—be sure they’re all there!
Click Upload.
You should see a message that says “Assessment Opportunity: Lightspeed submitted successfully!”
Be sure to double-check your autograder results before moving on!
Acknowledgements
Data based on the morley
data set from the datasets
package in base R, stat.ethz.ch/R-manual/R-devel/library/datasets/html/morley.html.