Knowledge Discovering with MySQL
MAT 259, 2022
Lijuan Cheng
Concept
Last year, I watched two film adaptations of the famous novels , “Little Women” and “Dune”, which have the same leading actor “Timothée Chalamet”. So I am very interested in the correlation between the movie releasing activity and the check out behavior of the original novel. Hence, I designed the first query to search the check out numbers of these two novels from 2006 to 2021 to observe the variation trend. Furthermore, I also noticed that the population diversity of the Seattle metropolitan area keeps growing in the last decade. Especially more and more Asian people move to Seattle due to the increasing job opportunities in tech companies. Therefore, the second query in my project is to investigate the reading interest of history knowledge about different continents, which can help us to understand the correlation between reading behavior and population composition.
Query
MySQL Query 1
SELECT
YEAR(cout) AS Years, COUNT(IF(LOWER(title) = 'little women', 1, NULL)) AS 'Little Women',
COUNT(IF(LOWER(title) = 'dune', 1, NULL)) AS 'Dune'
FROM spl_2016.outraw
WHERE
YEAR(cout) < 2022
GROUP BY Years
ORDER BY Years
MySQL Query 2
SELECT
YEAR(cout) AS Years,
COUNT(IF(deweyClass >= 940 AND deweyClass <= 949, 1, NULL)) AS 'History of Europe',
COUNT(IF(deweyClass >= 950 AND deweyClass <= 959, 1, NULL)) AS 'History of Asia',
COUNT(IF(deweyClass >= 960 AND deweyClass <= 969, 1, NULL)) AS 'History of Africa',
COUNT(IF(deweyClass >= 970 AND deweyClass <= 979, 1, NULL)) AS 'History of North America',
COUNT(IF(deweyClass >= 980 AND deweyClass <= 989, 1, NULL)) AS 'History of South America'
FROM spl_2016.outraw
WHERE
deweyClass > 900 AND YEAR(cout) < 2022
GROUP BY Years
ORDER BY Years
SELECT
YEAR(cout) AS Years, COUNT(IF(LOWER(title) = 'little women', 1, NULL)) AS 'Little Women',
COUNT(IF(LOWER(title) = 'dune', 1, NULL)) AS 'Dune'
FROM spl_2016.outraw
WHERE
YEAR(cout) < 2022
GROUP BY Years
ORDER BY Years
MySQL Query 2
SELECT
YEAR(cout) AS Years,
COUNT(IF(deweyClass >= 940 AND deweyClass <= 949, 1, NULL)) AS 'History of Europe',
COUNT(IF(deweyClass >= 950 AND deweyClass <= 959, 1, NULL)) AS 'History of Asia',
COUNT(IF(deweyClass >= 960 AND deweyClass <= 969, 1, NULL)) AS 'History of Africa',
COUNT(IF(deweyClass >= 970 AND deweyClass <= 979, 1, NULL)) AS 'History of North America',
COUNT(IF(deweyClass >= 980 AND deweyClass <= 989, 1, NULL)) AS 'History of South America'
FROM spl_2016.outraw
WHERE
deweyClass > 900 AND YEAR(cout) < 2022
GROUP BY Years
ORDER BY Years
Final result
Analysis 1
Although both of the two movies are very popular and obtain a lot of attention from the public, the effects on the reading behavior of the original novels are very different.
If we analyze the box office and the popularity of online discussion, maybe “Dune” is much better than “Little Women”. But the variation trend of check out in the Seattle public library is on the contrary of the popularity tendency.
Hence, I guess this will be mainly attributed to the difference of film genres and novel types. The novel of “Little Women” can almost cover all of the age groups and have the everlasting influence of various readers. In contrast, the novel “Dune” is science fiction which is loved by a small number of readers. Even if a large amount of audiences are fascinated by the wonderful movie, they won’t read the original novel owing to the obscurity of science fiction.
Analysis 2
The ranking of check out numbers every year always keeps the same: history of North America, history of Europe, history of Asia, history of Africa and history of South America. The results are as expected, consistent with population proportions.
In 2020, there was a dramatic decrease at the check out numbers compared with that of 2019. We can know that the Covid-19 indeed affected reading behavior significantly.
We also can find that there was a continuous downward trend in the check out of the history field from 2011 to 2021. I guess the reason can be attributed to the development of electronic books, as it mentioned in the project description: “Electronic books have been in the collection since 2009 but these are not recorded in the database we receive”.
Although both of the two movies are very popular and obtain a lot of attention from the public, the effects on the reading behavior of the original novels are very different.
If we analyze the box office and the popularity of online discussion, maybe “Dune” is much better than “Little Women”. But the variation trend of check out in the Seattle public library is on the contrary of the popularity tendency.
Hence, I guess this will be mainly attributed to the difference of film genres and novel types. The novel of “Little Women” can almost cover all of the age groups and have the everlasting influence of various readers. In contrast, the novel “Dune” is science fiction which is loved by a small number of readers. Even if a large amount of audiences are fascinated by the wonderful movie, they won’t read the original novel owing to the obscurity of science fiction.
Analysis 2
The ranking of check out numbers every year always keeps the same: history of North America, history of Europe, history of Asia, history of Africa and history of South America. The results are as expected, consistent with population proportions.
In 2020, there was a dramatic decrease at the check out numbers compared with that of 2019. We can know that the Covid-19 indeed affected reading behavior significantly.
We also can find that there was a continuous downward trend in the check out of the history field from 2011 to 2021. I guess the reason can be attributed to the development of electronic books, as it mentioned in the project description: “Electronic books have been in the collection since 2009 but these are not recorded in the database we receive”.