The Poems By Kids database includes a large number of poems by children in various grades, associates with various emotions. Your task in the following exercises is to learn more about those poems using SQL queries.
Because this database is large, the database file, with all of the tables and rows you will need, has already been created for you and is included in this repo. You do not need to run the sql script included in this repo, but you should look at the first part of it to understand the structure of the database. Use that script to create an ERD for Poems By Kids that you can reference while working on the queries. Create a file called solutions.sql to store the answers to the questions.
- What grades are stored in the database?
- What emotions may be associated with a poem?
- How many poems are in the database?
- Sort authors alphabetically by name. What are the names of the top 76 authors?
- Starting with the above query, add the grade of each of the authors.
- Starting with the above query, add the recorded gender of each of the authors.
- What is the total number of words in all poems in the database?
- Which poem has the fewest characters?
- How many authors are in the third grade?
- How many total authors are in the first through third grades?
- What is the total number of poems written by fourth graders?
- How many poems are there per grade?
- How many authors are in each grade? (Order your results by grade starting with
1st Grade) - What is the title of the poem that has the most words?
- Which author(s) have the most poems? (Remember authors can have the same name.)
- How many poems have an emotion of sadness?
- How many poems are not associated with any emotion?
- Which emotion is associated with the least number of poems?
- Which grade has the largest number of poems with an emotion of joy?
- Which gender has the least number of poems with an emotion of fear?