36 Views
Fine Wind, Clear Morning by Katsushika Hokusai
Problem to Solve
From 1830 to 1832, the Japanese artist Katsushika Hokusai created 36 woodblock prints depicting 36 different views of Mount Fuji, a volcano on the Honshū island of Japan. Among the series’ most famous works are Fine Wind, Clear Morning and The Great Wave off Kanagawa. The prints became so influential that another Japanese artist of the time period—Utagawa Hiroshige—created his own series of 36 prints, each depicting a new view of Fuji.
In views.db
, you’ll find details on the 36 prints created, respectively, by Hokusai and Hiroshige. In total, you’ll have data on 72 prints. Prints like these tend to be appreciated for their aesthetics, not their statistics, but computer science now helps create additional numeric insights about artwork. In addition to each print’s title and author, you’ll find some statistics commonly used in computational image analysis: the print’s average color, its brightness, its contrast, and its entropy. In the accompanying .sql
files, write SQL queries to answer questions about this database of 72 prints and the statistics about their composition.
Curious about average color, brightness, contrast, or entropy?
- Average color is the color created by summing the values for the red, green, and blue channels in each pixel, then dividing each sum by the number of pixels in the image.
- Brightness is defined as an image’s lightness or darkness, on a scale of 0 to 1. A white image would measure a 1, while a black image would measure a 0.
- Contrast is defined as the extent to which an image has differences in brightness throughout, on a scale of 0 to 1. Higher contrast (higher differences in brightness) enable objects to stand out. Lower contrast (lower differences in brightness) means objects don’t stand out as much.
- Entropy is a measure of an image’s randomness, often used to characterize the complexity of artwork. A 10, for our sake, is pretty high—whereas a 0 is very low.
Demo
Distribution Code
For this problem, you’ll need to download views.db
, along with several .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/x/psets/0/views.zip
in order to download a ZIP called views.zip
into your codespace.
Then execute
unzip views.zip
to create a folder called views
. You no longer need the ZIP file, so you can execute
rm views.zip
and respond with “y” followed by Enter at the prompt to remove the ZIP file you downloaded.
Now type
cd views
followed by Enter to move yourself into (i.e., open) that directory. Your prompt should now resemble the below.
views/ $
If all was successful, you should execute
ls
and see a database named views.db
alongside several .sql
files. Executing sqlite3 views.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
In views.db
you’ll find a single table, views
. In the views
table, you’ll find the following columns:
id
, which uniquely identifies each row (print) in the tableprint_number
, which identifies the number of the print in either Hokusai’s or Hiroshige’s seriesenglish_title
, which is the English title of the printjapanese_title
, which is the Japanese title of the printartist
, which is the last name of the print’s artistaverage_color
, which is the hexadecimal representation of the color found by averaging the colors of each pixel in the imagebrightness
, which is a number corresponding to the overall lightness or darkness of the imagecontrast
, which is a number representing the extent of the difference between light and dark areas of the imageentropy
, which is a measure used to quantify the complexity of the artwork
Specification
For each of the following questions, you should write a single SQL query that outputs the results specified by each problem. Your response must take the form of a single SQL query. You should not assume anything about the id
s of any particular observations: your queries should be accurate even if the id
of any particular observation were different. Finally, each query should return only the data necessary to answer the question.
- In
1.sql
, write a SQL query that a translator might take interest in: list, side by side, the Japanese title and the English title for each print. Ensure the Japanese title is the first column, followed by the English title. - In
2.sql
, write a SQL query to list the average colors of prints by Hokusai that include “river” in the English title. (As an aside, do they have any hint of blue?) - In
3.sql
, write a SQL query to count how many prints by Hokusai include “Fuji” in the English title. Though all of Hokusai’s prints focused on Mt. Fuji, in how many did “Fuji” make it into the title? - In
4.sql
, write a SQL query to count how many prints by Hiroshige have English titles that refer to the “Eastern Capital”. Hiroshige’s prints were created in Japan’s “Edo period,” referencing the eastern capital city of Edo, now Tokyo. - In
5.sql
, write a SQL query to find the highest contrast value of prints by Hokusai. Name the column “Maximum Contrast”. Does Hokusai’s prints most contrasting print actually have much contrast? - In
6.sql
, write a SQL query to find the average entropy of prints by Hiroshige, rounded to two decimal places. Call the resulting column “Hiroshige Average Entropy”. - In
7.sql
, write a SQL query to list the English titles of the 5 brightest prints by Hiroshige, from most to least bright. Compare them to this list on Wikipedia to see if your results match the print’s aesthetics. - In
8.sql
, write a SQL query to list the English titles of the 5 prints with the least contrast by Hokusai, from least to highest contrast. Compare them to this list on Wikipedia to see if your results match the print’s aesthetics. - In
9.sql
, write a SQL query to find the English title and artist of the print with the highest brightness. - In
10.sql
, write a SQL query to answer a question of your choice about the prints. The query should:- Make use of
AS
to rename a column - Involve at least one condition, using
WHERE
- Sort by at least one column, using
ORDER BY
- Make use of
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
You can also run
$ cat FILENAME | sqlite3 views.db > output.txt
to redirect the output of the query to a text file called output.txt
. (This can be useful for checking how many rows are returned by your query!)
How to Test
While check50
is available for this problem, you’re encouraged to instead test your code on your own for each of the following. If you’re using the views.db
database provided in this problem’s distribution, you should find that…
- Executing
1.sql
results in a table with 2 columns and 72 rows. - Executing
2.sql
results in a table with 1 column and 3 rows. - Executing
3.sql
results in a table with 1 column and 1 row. - Executing
4.sql
results in a table with 1 column and 1 row. - Executing
5.sql
results in a table with 1 column and 1 row. - Executing
6.sql
results in a table with 1 column and 1 row. - Executing
7.sql
results in a table with 1 column and 5 rows. - Executing
8.sql
results in a table with 1 column and 5 rows. - Executing
9.sql
results in a table with 2 columns and 1 row.
10.sql
is up to you!
Note that row counts do not include header rows that only show column names.
Correctness
check50 cs50/problems/2024/sql/views
How to Submit
In your terminal, execute the below to submit your work.
submit50 cs50/problems/2024/sql/views
Acknowledgements
Images and titles of prints retrieved from en.wikipedia.org/wiki/Thirty-six_Views_of_Mount_Fuji and en.wikipedia.org/wiki/Thirty-six_Views_of_Mount_Fuji_(Hiroshige).