learn-co-curriculum/sql-library-lab

SQL Fantasy Library

We're going to build a SQL database that will keep track of books from a fantasy series in a library. These types of books can get complex, with many characters that span many books in a series, or just appear in one book, and characters that are species other than human. We will have tables for: Characters, Books, Series, Authors, and Sub-Genres. For a refresher on SQL syntax as you work through this lab, the W3Schools SQL Tutorial is a helpful reference, as well as the resources listed below.

Objectives

• Become comfortable writing SQL statements to create tables that have complex relations with each other
• Understand and implement JOINs to write complex SELECT statements to query a database

Section 1: schema.sql

Build out the schema for our Fantasy Library database:

1. All tables must have a PRIMARY KEY on the id
2. The Series table should have a title and belong to an author and a sub-genre
3. The Sub-Genres table has a name
4. The Authors table has a name
5. The Books table has a title and year and belong to a series
6. The Characters table has a name, motto, and species and belong to an author
7. The Books table has many characters and characters are in many books in a series. How do we accomplish this complex association? With a join table between Characters and Books. This join table (let's call it character_books) will just have -in addition to its primary key- two foreign key columns for the character and book ids. Each row in this join table acts as a relation between a book and a character.

Section 2: insert.sql

Populate the database with the following:

• 2 series

• 2 sub-genres

• 2 authors

• 3 books in each series

• 8 characters

  • 4 characters in each series
    • of each of those 4, make 2 in all of the books in a series, and 2 in just 1 book in a series

• Note you will need to insert values into your character_books join table

• Feel free to make these up if you don't know any Fantasy series :)

Section 3: update.sql

Update the species of the last character in the database to "Martian" by writing an update statement in update.sql.

Section 4: Querying your database

In lib/querying.rb, complete the tests by writing the appropriate queries to satisfy the queries. Note that for this section, the database will be seeded with external data so don't expect it to reflect the data you added above.

Resources

• W3Schools SQL Tutorial
• Seldom Blog - About SQL Joins: The 3 Ring Binder Model
• Coding Horror - A Visual Explanation of SQL Joins
• Geeky is Awesome - SQL Joins Tutorial

View SQL Library Lab on Learn.co and start learning to code for free.

sql-library-lab's Issues

select_value_and_count_of_most_prolific_species

This function tests for the value and count of the most prolific species from the first series only; the test expects a return value of 'human, 4'. It might be worth making that clear in the description. The most prolific species without qualification would be 'cylon, 8', using the seed data.

Geeky is Awesome link redirects to blog homepage

The link provided at the bottom seems no longer active. The link now redirects to the blog homepage (https://geekyisawesome.blogspot.com/) In the previous lab the link took you to this post (https://geekyisawesome.blogspot.com/2011/03/sql-joins-tutorial.html) which might be more appropriate.

Best,
Sam

False Positive

In querying.rb, the following code passes the tests:

def select_series_title_with_most_human_characters
"SELECT series.title
FROM series
JOIN characters
ON series.id = characters.series_id

GROUP BY series.title
ORDER BY SUM(characters.species) DESC
LIMIT 1"
end

This, however, totals all the characters without totaling all the human characters. It passes because the correct answer not only has the most human characters, but the most characters total of any species

The correct answer should be required to include the WHERE statement below:

def select_series_title_with_most_human_characters
"SELECT series.title
FROM series
JOIN characters
ON series.id = characters.series_id
WHERE characters.species = 'human'
GROUP BY series.title
ORDER BY SUM(characters.species) DESC
LIMIT 1"
end

select_character_names_and_number_of_books_they_are_in order

The test requires the results to be ordered by the number of characters and then by name, while neither the query title nor any other source indicates any ordering is necessary or prefered.

in the tests Contexts should be Describes

Characters table should not have series_id

The Readme states "The Characters table has a name, motto, and species and belong to an author and a series".

However, the tests require characters and series to be joined via character_books and books, not via characters.series_id = series.id. The Flatiron GitHub solution confirms this.

Solution
Change Readme to say: "The Characters table has a name, motto, and species and belong to an author".

The Querying spec had all the answers in it on master

Spelling Errors in the Query Spec & Data.sql files

Specifically in the spelling of a famous dwarf's name. Adjusted in my files appropriately.

series id making things too easy

Having a series_id for the characters table makes the 3 join challenge of the following query unnecessary, as demonstrated by the solutions below (intended solution first, followed by alternate solution). Also, having a series id for characters means that characters can have cameos in other series...

I suggest refactoring the character table schema, the insert, and the data to remove the series_id from the character table.

def select_series_title_with_most_human_characters
<<-SQL
SELECT series.title
FROM series
JOIN books
ON books.series_id = series.id
JOIN character_books
ON character_books.book_id = books.id
JOIN characters
ON character_books.character_id = characters.id
WHERE characters.species = 'human'
GROUP BY series.title
ORDER BY COUNT(*) DESC
LIMIT 1
SQL
end

Can be solved with:
def select_series_title_with_most_human_characters
"SELECT series.title
FROM series
INNER JOIN characters
ON series.id = characters.series_id
WHERE characters.species = 'human'
GROUP BY series.id
ORDER BY COUNT(characters.species = 'human') DESC
LIMIT 1
"
end

Drop the encoded data stuff

just decode it

Learn didn't recognize the fork or the passed test

Tried re-forking, forking manually, as well as deleting the original copy of the lab. It passed all the tests and successfully performed a pull request.

Typo: proposing change "spec/querying_spec.rb" to "lib/querying.rb"

The solutions to the test should be written in querying.rb and not the querying_spec.rb

back-tick all table names

especially the ones in the schema.sql stuff

Test in update spec not clear

Update instructions in Readme to clarify students should add update code in update.sql, and write the Select statement in the spec.

https://github.com/flatiron-school-curriculum/SQL-library/blob/845ac7edd8d068a4d9c0630d3c605539dc5344bf/spec/update_spec.rb#L10

Link to current spec.

Solution example query isn't correct

The solution to the select_series_title_with_most_human_characters method really only tells you which series has the most human character appearances. What I mean is, the 'second series' would become the correct answer, if it just had 6 more books, and 'character two' appeared in all of them. Then the query would naively compare 7 to 8, and conclude more human characters were in that series, when really it should have compared 3 human characters in the first series, to 1 human character in the second series. With no change at all to the dataset, it's comparing 7 to 3 right now (2 humans in 3 books and 1 human in 1 book, 23 + 11 = 7, compared to 1 human in 3 books, 1*3 = 3). It arrives at the correct conclusion, but only by coincidence, really.

In order to actually filter out repeat appearances of the same character to then properly compare 3 to 1, I think it might actually be necessary to do a subquery, which I don't expect students to have to figure out. Maybe I'm off base, and there's a simpler solution than that, but I know our current solution isn't sufficient.