All right.
It's time to get some practice with joints and foreign keys and all that fun stuff.
So for this exercise, I would like you to start by creating this schema, right?
These two tables.
Relatively simple.
We have students and papers.
So we're a professor or a teacher and we have students.
They write different papers and each student has multiple papers, potentially a midterm paper, a final
paper essay, whatever.
So the way I'd like it to work is that a student has an ID and a first name.
Just keep it simple.
We don't need the last name and other stuff.
And then papers have a title like Russian Lit through the Ages, my second book report.
And then they also have a grade that we assign to them.
Out of 160 would be bad, 98 would be very good.
And then finally we have the student ID, which is a foreign key referencing students ID, and this
should be a primary key auto increment, all that stuff.
So start by writing the two tables and then once you've done that, just copy and paste this information,
these inserts.
I'd like you to insert these into students so that we have the same data to work with.
Just some sample data.
And then once you've done that, I would like for you to print this Joined Together table that has the
first name of every student who wrote a corresponding essay.
So what their essay was.
Samantha has two essays and the grade next to it.
So just a simple join.
You can figure out which one would work here, but it's the overlap between essays and papers.
Then we have this one, which is a little different.
It's hard to notice.
We still have first name, title and grade, but we have these null values down here and here as well.
So what join would result in this?
And then after that, we've got a variation.
Notice that the text becomes missing down here instead of null, and over here instead of null, we
have zero.
So this will need to be some sort of join along with if null.
Just as a hint.
All right.
And then after that, print this here, which is an average of all of the grades each student received
on their papers.
And for some of the students, they don't have papers, Roge and Lisa.
So their average is zero.
They are slackers.
Next up, print, whether it's a passing grade or not.
Based off of the average for each student.
So it's the same data from this example.
But now we have this extra conditional use case here, a case statement that is either passing or failing
and the threshold should be 75.
So if the average is above 75, it's a passing grade.
Otherwise it's a failing grade.
So try it out and I'll be back in the next video with a solution.