CSCI 403 Database Management

Spring 2019

Project 4: Query (2)

(Back to Projects)

Purpose


Instructions

This project picks up where Project 2 left off (you may wish to reread the introductory material from project 2). Create a SQL script file to answer the questions below using SELECT queries against tables hogwarts_students, hogwarts_houses, and hogwarts_dada. Each answer should consist of exactly one query (which can include subqueries).

As you do this assignment, consider that there are often many ways to achieve the same result in SQL; that joins can often be replaced with a subquery or vice-versa is one example. On the quiz and midterm, you will have to read someone else's (my) SQL, which may use a different technique than you might have chosen. So you might want to take extra time on this assignment and try different ways of solving each question.

Questions:

    Statistics, aggregation, and grouping
  1. How many Slytherin students are there?
  2. What is the earliest start year of any student in our data?
  3. How many students have some missing information?
  4. How many Defense Against the Dark Arts (DADA) teachers have first names the same length as their last names?
  5. How many students are listed in each house (include the number who have no house listed as well; order by greatest number of students first)?
  6. Which houses have more than 20 associated student records, and how many students are in those houses?
  7. By house, what was the average number of years spent at Hogwarts by students for whom we know both start and end years? Order by average number of years.
  8. Which family names (last names) appear exactly twice in the hogwarts_students table?

    9. Subqueries and/or complex joins
  9. What are the names, houses, and house colors of the Defense Against the Dark Arts teachers (you only need to worry about the teachers who also have student records)?
  10. Who is the earliest known student, and what year did he or she start?
  11. Which student has the shortest first name?
  12. Who were the Gryffindors who would have had Dolores Umbridge as DADA teacher (assume all students take DADA, and all students are at school for the entire school year starting in Fall and ending in Spring, keeping in mind that each DADA teacher listed started in Fall and left the following Spring)?
  13. Which students have had other family members attend Hogwarts (assume anyone with the same last name is a family member)? Order by last name and first name.
  14. How many students of each house are known to have started the year that Remus Lupin was the appointed DADA teacher?

    15. Extra credit
  15. What student started in the same year as, and has the same length first and last names as a student with initials "R. H." who was in a house with a Lion as its heraldic animal? Don't return the student R. H., just the other one.

Grading:

Each question/query (11 total) 5 points each
README 5 points
Total: 75 points
Extra credit 2.5 points

Documentation:

README:

  1. Include names of all people who helped/collaborated as per the syllabus
  2. Describe the challenges you encountered and how you surmounted them
  3. What did you like/dislike about the assignment?
  4. How long did you spend on this assignment?

Submit a zip file on Canvas containing: