SQL Exercise - Poems by Kids

Note: The data in this exercise is derived from the datasets found here. An academic paper describing the PoKi project can be found here The data is used for education purposes with permission from the maintainer.

Setup

1. Download this SQL script
2. Open Visual Studio
3. From Visual Studio's main menu, Select the File -> Open -> File menu option
4. Use the file explorer to navigated to and select the file you downloaded in step 1
5. The file will open in an editor window. Find and click the Connect near the upper-left of the editor
6. In the Connect window, find and select your SQL Express database instance and click the Connect button
7. Back in the editor window click the Execute button or use the Ctrl + SHift + E shortcut to run the script
8. Open the SQL Server Object Explorer and expand the Databases node beneath your SQL Express instance. You should see a new database named PoKi
9. Open a new query window to the PoKi database. Use this editor window to write SQL to answer the questions below.

PoKi ERD

Investigating the Data

Query the PoKi database using SQL SELECT statements to answer the following questions.

1. What grades are stored in the database?
2. What emotions may be associated with a poem?
3. How many poems are in the database?
4. Sort authors alphabetically by name. What are the names of the top 76 authors?
5. Starting with the above query, add the grade of each of the authors.
6. Starting with the above query, add the recorded gender of each of the authors.
7. What is the total number of words in all poems in the database?
8. Which poem has the fewest characters?
9. How many authors are in the third grade?
10. How many authors are in the first, second or third grades?
11. What is the total number of poems written by fourth graders?
12. How many poems are there per grade?
13. How many authors are in each grade? (Order your results by grade starting with 1st Grade)
14. What is the title of the poem that has the most words?
15. Which author(s) have the most poems? (Remember authors can have the same name.)
16. How many poems have an emotion of sadness?
17. How many poems are not associated with any emotion?
18. Which emotion is associated with the least number of poems?
19. Which grade has the largest number of poems with an emotion of joy?
20. Which gender has the least number of poems with an emotion of fear?