# 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.

## Distribution Code

For this problem, youâ€™ll need to download views.db, along with several .sql files in which youâ€™ll write your queries.

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/2023/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 table
• print_number, which identifies the number of the print in either Hokusaiâ€™s or Hiroshigeâ€™s series
• english_title, which is the English title of the print
• japanese_title, which is the Japanese title of the print
• artist, which is the last name of the printâ€™s artist
• average_color, which is the hexadecimal representation of the color found by averaging the colors of each pixel in the image
• brightness, which is a number corresponding to the overall lightness or darkness of the image
• contrast, which is a number representing the extent of the difference between light and dark areas of the image
• entropy, 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 ids 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.

1. 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.
2. 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?)
3. 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?
4. 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.
5. 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?
6. 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â€ť.
7. 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.
8. 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.
9. In 9.sql, write a SQL query to find the English title and artist of the print with the highest brightness.
10. 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

## 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/2023/sql/views  ## How to Submit Ensure your terminalâ€™s working directory is the views folder. Your prompt should resemble the below: views/$


When you type ls to list the files in your working directory, you should see your .sql files for views.

Zip up your solution files by executing the following:

zip views-solutions.zip *.sql


Notice this command has three parts:

• zip, which is the name of the tool which will create a .zip file
• views-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 views-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 Problem Set 0: Views.

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!