My topic of interest is what are the popular things about Astronomy reflected on the related item types in the library. The related medias are in the Dewey Class between 520 and 530, which contains various subtopics like Chronology, Celestial mechanics, specific celestial bodies, etc. I will first explore what’s popular in different item types. The next step is to see the top-viewed works within a specific “sub-Dewey” Class (525), and the last exploration is the popularity of a classical book on cosmology over time -- A Brief History of Time.

1.Query from spl_2016.outraw for all check-outs of medias from Dewey class 520-530
SELECT
bibNumber,
itemType,
title,
COUNT(bibNumber) AS Counts
FROM
spl_2016.outraw
WHERE
deweyClass >= 520 AND deweyClass < 530
GROUP BY bibNumber,itemType,title
ORDER BY Counts DESC
limit 10;

2.Query to find the top 20 checkouts of books
SELECT
YEAR(cout) AS years,
bibNumber,
itemType,
title,
COUNT(bibNumber) AS Counts
FROM
spl_2016.outraw
WHERE
deweyClass >= 520 AND deweyClass < 530 AND itemType = 'acbk'
GROUP BY bibNumber,itemType,title,YEAR(cout)
ORDER BY Counts DESC
limit 20;

3.Query to find the top 20 checkouts of DVDs
SELECT
YEAR(cout) AS years,
bibNumber,
itemType,
title,
COUNT(bibNumber) AS Counts
FROM
spl_2016.outraw
WHERE
deweyClass >= 520 AND deweyClass < 530 AND itemType = 'acdvd'
GROUP BY bibNumber,itemType,title,YEAR(cout)
ORDER BY Counts DESC
limit 20;

4.Query to find the top 20 checkouts of books from Dewey class 525 : Earth
SELECT
YEAR(cout) AS years,
bibNumber,
itemType,
title,
COUNT(bibNumber) AS Counts
FROM
spl_2016.outraw
WHERE
title like '%Earth%' or title like '%earth%'
AND deweyClass >= 520
AND deweyClass < 530
GROUP BY bibNumber,itemType,title,YEAR(cout)
ORDER BY Counts DESC
limit 20;

5.Query to find the top 20 checkouts of all time related to Brief history of time
SELECT
YEAR(cout) AS years,
bibNumber,
itemType,
title,
COUNT(bibNumber) AS Counts
FROM
spl_2016.outraw
WHERE
title like '%brief history of time%'
AND deweyClass >= 520
AND deweyClass < 530
GROUP BY bibNumber,itemType,title,YEAR(cout)
ORDER BY Counts DESC
limit 20;

1.What's popular overall from 2006 to now?


Among all the popular medias, there are famous documentaries (universe : The complete season one explore the edges of the unknown) , educational books (Planets) and photobook for visual guidance of the universe (Eyewitness Planets).

2.What are the top 20 checkouts of books?


Astrophysics for people in a hurry is a 2017 popular science book centering some basic questions about the universe.The popularity of this book can be clearly shown in the record for it was the most popular book for successive 3 years.

3.What are the top 20 checkouts of DVDs?


Most of the preferred DVDs are copies of documentaris about the universe.The Universe sees to be the most popular documentary series of all time.Overall people prefer to see the universe in motion rather than reading books about it. (More counts of checkouts)

4.What are the top 20 checkouts of books from Dewey class 525 : Earth?


Among all the popular item types, the photobooks are the most popular. The top three in the list are lookbooks for kids to build a basic concept of our planet.

5.How popular is “Time” over time?


From the query to find the top 20 checkouts of all time related to Brief history of time by Stephen Hawking,the result shows that the book was actually popular in the 2010’s. “Illustrated a brief history of time” is an expanded edition of the original first version of the book.

In conclusion,instead of reading serious scientific books about the universe, people prefer introductory books about basic knowledge of the universe, and they prefer videos and pictures rather than reading a book full of text when getting to know the universe. Perhaps that's because people are curious about our universe, but not everyone can be an astronomer to understand the actual secrets of the universe.

All work is developed within MySQLWorkbench
Source Code + Slides