Media Arts and Technology

10.11.22 2nd Project in MySQL

The next step is to discuss 4 to 6 new MySQL queries that deliver more detailed information or are more complex in their design. Consider what we learned from the first assignment.

. Get more precise and detailed results.

. Do not limit your search to the Dewey classification as most items in the database are not Dewey, and it may be interesting to see why some are classified in Dewey and others not.

. Make sure that the results are true to your search, so that there is no ambiguous information, for instance Shaokang's "Headphones" referred to both a musical cd but also people checking out headphones in the library.

. Share the sequence of rewrites Its interesting for the class to share how you evolved your query from a less efficient to a more efficient version.

. Check the Library's online search tool to see if something you got is actually what it is: https://seattle.bibliocommons.com/v2/se ... New+Titles

. A database is an evolving thing. Our negotiations with the library resulted in getting data starting in 2006, and since then classifications, categories of things, or for instance, Itemtypes: https://www.mat.ucsb.edu/~g.legrady/aca ... mTypes.pdf have been added or been reduced. For instance, checkout of cdroms, VHS tapes, are rarely checked out today, but maybe they continue to be looked at.

A report focused on two topics: SPL hotspot devices and 2019 No Late Fee Policy
Thinking on adding one topic more: about the legacy items.
Here's the of the report:
(Revised as of 10.17) Topic 01 results: Topic 02 results:

Here is my Week 3 Assignment write up that contains the SQL code: Please describe in a short paragraph what your queries and results explore:
For this week’s assignment, I explored three different topics. With each, I practiced and refined my SQL skills while querying a wide array of columns from the Seattle Public Library database. I was able to get a good understanding of the different metrics inside of the database and how they are all connected to each other.

• Topic 1: Books on War

For this topic, I explored books at the SPL that were about wars. I began by searching for books with the word “war” in their subject line. Proceeding, I grouped them by year and was able to query the most popular book checked out per year. I followed by narrowing down the data further looking at civil war books and war books dealing with the country America, each yielding a set of interesting results.

• Topic 2: Items other than Books checkout out at the library

Next, I explored the different item types being checked out at the SPL. I am interested in who is going to the library for musical/listening purposes. In order to explore this, I began by looking at the different item types. I wrote down which were for CDs, song books and records keeping note of this for my following queries. Furthermore, I grouped each category (CDs, song books, and records) by title and grouped their number of checkouts per year in order to see which was the most popular and during which year.

• Topic 3: Biographies on Athletes grouped by their sports

Finally, I wanted to explore autobiographies/biographies/memoirs specifically for those involved with sports. I began by querying data that had ‘biography’/’autobiography’/’memoir’ in their subject description as a starting point. Following, I pulled data about biographies on certain sports yielding the titles, item types, subjects, and bibNumbers. I wrote a query to analyze the differences in the number of biographical books between sports.


Along with the CSV output files located here:
(they are all compiled into one numbers document on my Mac, but I am unable to upload this onto this forum) [*](updated 10/13 to include new CSV file names and descriptions of what the queries explore per email on 10/11)

For this assignment, I analyzed several other (disconnected) questions. Specifically: (1) the aftermath of J.K. Rowling’s Twitter scandal; (2) items that were not returned to the library and associated check-in issues; and (3) general information regarding the busiest day in the SPL to date.

The following is a link to my document where I have all my queries and explanations:
https://docs.google.com/document/d/1CK7 ... sp=sharing

Attached are the corresponding cvs files:
*The order below follows the order in the document*