CSCI 403 Database Management

Spring 2018

Project 4: Query (2)

(Back to Projects)

Purpose

• Master SELECT queries

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).

Questions:

Statistics and aggregation
1. How many Slytherin students are there?
2. What is the earliest start date of any student in our data?
3. How many students have some missing information?
4. How many students have all information?
5. How many students are listed in each house (include the number who have no house listed as well; order by greatest number first)?
Subqueries and/or complex joins
6. Who is the earliest known student, and what year did he or she start?
7. How many students of each house are known to have started the year that Alastor Moody was the appointed DADA teacher?
8. 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)?
9. Who were the Gryffindors who would have had Gilderoy Lockhart 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)?
10. 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.
11. Which family names (last names) appear exactly once in the hogwarts_students table?
Extra credit
12. Considering only the students for whom some information is missing, what letter occurs 8 times as the first letter of the last name of such a student?

Grading:

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:

• README
• Your SQL script.