Linear Frequency
MAT 259, 2013
Anastasiya
Introduction
The goal of this project is to explore "How was the Solar System represented in the titles of books checked out in each
month of 2011?"
Query
SELECT
month(cout) as Month,
SUM(CASE WHEN title like "sun %" or title like "% sun
%" or title like "% sun" THEN 1 ELSE 0 END) as Sun,
SUM(CASE WHEN title like "mercury %" or title like "% mercury
%" or title like "% mercury" THEN 1 ELSE 0 END) as Mercury,
SUM(CASE WHEN title like "venus %" or title like "% venus
%" or title like "% venus" THEN 1 ELSE 0 END) as Venus,
SUM(CASE WHEN title like "earth %" or title like "% earth %"
or title like "% earth" THEN 1 ELSE 0 END) as Earth,
SUM(CASE WHEN title like "mars %" or title like "% mars
%" or title like "% mars" THEN 1 ELSE 0 END) as Mars,
SUM(CASE WHEN title like "jupiter %" or title like "% jupiter
%" or title like "% jupiter" THEN 1 ELSE 0 END) as Jupiter,
SUM(CASE WHEN title like "saturn %" or title like "% saturn
%" or title like "% saturn" THEN 1 ELSE 0 END) as Saturn,
SUM(CASE WHEN title like "uranus %" or title like "% uranus %" or
title like "% uranus" THEN 1 ELSE 0 END) as Uranus,
SUM(CASE WHEN title like "neptune %" or title like "% neptune
%" or title like "% neptune" THEN 1 ELSE 0 END) as Neptune,
SUM(CASE WHEN title like "pluto %" or title like "% pluto
%" or title like "% pluto" THEN 1 ELSE 0 END) as Pluto
FROM
inraw
WHERE
ITEMTYPE like "%bk%" and year(cout)='2011'
GROUP BY
month(cout) with rollup
Explanation
SELECT
month(cout) as Month- retrieves the month (1-12) that the item was
checked out
Anastasiya Lazareva
SUM(CASE WHEN title like "sun %" or title like "% sun %" or title like "% sun" THEN 1 ELSE 0 END) as Sun- retrieves the count of all the titles that contain the word ‘sun’ either in the beginning, middle, or end of the title, same is done for the other solar system bodies
FROM
Inraw-from the ‘inraw’ table
WHERE
ITEMTYPE like "%bk%" and year(cout)='2011'- gets the entries that correspond to books checked out in 2011
GROUP BY month(cout) with rollup- groups the data by month and displays the totals at the end
SUM(CASE WHEN title like "sun %" or title like "% sun %" or title like "% sun" THEN 1 ELSE 0 END) as Sun- retrieves the count of all the titles that contain the word ‘sun’ either in the beginning, middle, or end of the title, same is done for the other solar system bodies
FROM
Inraw-from the ‘inraw’ table
WHERE
ITEMTYPE like "%bk%" and year(cout)='2011'- gets the entries that correspond to books checked out in 2011
GROUP BY month(cout) with rollup- groups the data by month and displays the totals at the end
Process
Processing Time 0.044 sec
Analysis
The results of the query are displayed in the table below.
The data shows that the most commonly used solar system body in book titles checked out in 2011 is ‘Earth’ followed by ‘Sun’ and ‘Mars’. Additionally, books with ‘Earth’, ‘Mars’ and ‘Venus’ in the title decrease in the second half of the year.
The data shows that the most commonly used solar system body in book titles checked out in 2011 is ‘Earth’ followed by ‘Sun’ and ‘Mars’. Additionally, books with ‘Earth’, ‘Mars’ and ‘Venus’ in the title decrease in the second half of the year.
Code