wk3 - 10.11 .22 2nd Project in MySQL

Post Reply
glegrady
Posts: 203
Joined: Wed Sep 22, 2010 12:26 pm

wk3 - 10.11 .22 2nd Project in MySQL

Post by glegrady » Fri Sep 16, 2022 7:47 am

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.
George Legrady
legrady@mat.ucsb.edu

shaokang
Posts: 8
Joined: Fri Sep 23, 2022 10:07 am

Re: wk3 - 10.06 .22 MySQL queries studies

Post by shaokang » Tue Oct 11, 2022 2:07 am

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)
Assignment 02_revised.pdf
(714.25 KiB) Downloaded 34 times
Topic 01 results:
SPL_overview.csv
(960 Bytes) Downloaded 39 times
Hotspot_devices_type.csv
(256 Bytes) Downloaded 47 times
models_over_years.csv
(450 Bytes) Downloaded 44 times
Topic 02 results:
Income_diff.csv
(38 Bytes) Downloaded 43 times
newIncome.csv
(32 Bytes) Downloaded 43 times
Last edited by shaokang on Sun Oct 16, 2022 11:14 pm, edited 5 times in total.

briannagriffin
Posts: 11
Joined: Fri Sep 23, 2022 10:04 am

Re: wk3 - 10.06 .22 MySQL queries studies

Post by briannagriffin » Tue Oct 11, 2022 11:05 am

Here is my Week 3 Assignment write up that contains the SQL code:
Assignment Week 3.pdf
(1.94 MiB) Downloaded 62 times
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)
part_1_war_general.csv
(54.62 KiB) Downloaded 47 times
part_1_war_books_in2022.csv
(29.26 KiB) Downloaded 34 times
part_1_civil_war_books.csv
(2.96 MiB) Downloaded 38 times
part_1_america_war_books.csv
(1.83 MiB) Downloaded 37 times
part_1_war_most_popular.csv
(45.25 KiB) Downloaded 43 times
part_2_CD_songbooks_records_general.csv
(3.29 MiB) Downloaded 39 times
part_2_cd_most_popular_ByYear.csv
(2.2 MiB) Downloaded 38 times
part_2_CD_most_checkout.csv
(3.97 KiB) Downloaded 44 times
part_2_song_books_most_checkouts.csv
(6.2 KiB) Downloaded 35 times
part_2_records_most_checkouts.csv
(4.76 KiB) Downloaded 35 times
part_3_biography_subj_earch.csv
(20.77 KiB) Downloaded 36 times
part_3_biography_and_dewey_book.csv
(47.45 KiB) Downloaded 47 times
part_3_biography_and_itemtype.csv
(4.24 MiB) Downloaded 46 times
part_3_biography_books_tennis.csv
(18.88 KiB) Downloaded 45 times
part_3_biography_books_softball.csv
(372 Bytes) Downloaded 42 times
part_3_biography_books_per_sport.csv
(126 Bytes) Downloaded 44 times
part_3_biography_per_sport_with_titles.csv
(325.24 KiB) Downloaded 40 times
[*](updated 10/13 to include new CSV file names and descriptions of what the queries explore per email on 10/11)
Last edited by briannagriffin on Thu Oct 13, 2022 11:10 am, edited 1 time in total.

ilianikiforov
Posts: 8
Joined: Tue Oct 04, 2022 10:24 am

Re: wk3 - 10.06 .22 MySQL queries studies

Post by ilianikiforov » Tue Oct 11, 2022 1:58 pm

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.
Attachments
jkrowling_by_date.csv
(469 Bytes) Downloaded 35 times
checkin_before_checkout_top5_difference.csv
(54 Bytes) Downloaded 30 times
checkin_before_checkout_monthly_2018.csv
(136 Bytes) Downloaded 34 times
checkin_before_checkout_10.csv
(533 Bytes) Downloaded 31 times
busiest_days.csv
(184 Bytes) Downloaded 33 times
busiest_day_top10_items.csv
(235 Bytes) Downloaded 30 times
busiest_day_details.csv
(166 Bytes) Downloaded 31 times
Assignment 2 Nikiforov.pdf
(165.28 KiB) Downloaded 43 times
Last edited by ilianikiforov on Mon Oct 17, 2022 2:06 pm, edited 1 time in total.

nataliadubon
Posts: 15
Joined: Tue Mar 29, 2022 3:30 pm

Re: wk3 - 10.06 .22 MySQL queries studies

Post by nataliadubon » Tue Oct 11, 2022 3:17 pm

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*
Attachments
Vinyl_Popularity.csv
(3.31 KiB) Downloaded 41 times
Stephen_King_All_Quarters.csv
(1.16 KiB) Downloaded 30 times
Stephen_King_4th_Quarter.csv
(338 Bytes) Downloaded 32 times
Carrie_Yearly_Checkouts.csv
(168 Bytes) Downloaded 32 times
PetSematary_Yearly_Checkouts.csv
(150 Bytes) Downloaded 36 times
TheShining_Yearly_Checkouts.csv
(149 Bytes) Downloaded 37 times
It_Yearly_Checkouts.csv
(143 Bytes) Downloaded 31 times

Post Reply