All right.
So we made it through.
At least I made it through.
Hopefully still hanging in there.
Hopefully you're alive and you survived that section.
I know it can be brutal.
This is for me.
And for most teachers, the hardest thing to teach.
Not because it's technically that difficult, but it's a big thing.
We have to have multiple tables and to have multiple tables, we have to create the schema.
We have to insert the data, then we have to show what the data looks like to understand it.
And then we can get started with the joint and you have to manipulate the data to change.
It's just a lot of work and it's tough.
It's tough to keep it engaging.
And frankly, I don't think that that was terribly engaging.
So I'm sorry.
Hopefully you made it and I'm doing my best to make the rest of these a little bit more exciting because
once we have these tools in our belt, it's a lot easier to start doing case studies, which we'll be
doing or making a full application.
We're getting close to end products to actually making things rather than, you know, silly one off
little examples.
So it's a long preamble.
It's now your turn.
And this is where you'll probably if you're confused, if you're stuck with this join stuff, this is
hopefully where you'll be able to make some big connections, to have some breakthroughs.
So this is a little different than exercises we've done in the past.
There's only one schema, one table you'll be working with, and there's not even that many questions.
But the first thing is that we're going to be working with students in papers.
So I'm a teacher, let's suppose.
I guess I am a teacher, but let's say I'm working in a classroom with high school students in an English
class or a literature class.
It's going to be very, very simple data.
I'm telling you what I'm looking for.
Students and papers and for students will only worry about an ID and their first name, so don't even
care about the last name.
Let's make sure first name is unique and then we're going to have papers and it's a one to many relationship.
One student can have multiple papers, but a paper can only belong to one student.
So these papers will have a title, a grade, a letter grade like or not a letter grade, a numeric
grade.
Excuse me?
Like 90 or 60 if they don't do particularly well.
And then a foreign key student ID which is referencing the student ID.
So that's the basic schema.
And I'd like for you to first go through and actually implement this.
And don't worry about the data because in the next slide I'm giving you the data you can copy and paste
it in.
So this data is what we'll be working with.
So we have one, two, three, four, five users.
Students keep doing that and then we have six.
Nope, I can't count five different papers, but there's a couple important things.
One is that a couple of students actually have multiple papers.
So, for instance, if we take Caleb.
His ID is one, although you can't see that here, but he's the first one inserted, so his idea is
one.
And he has my first book report, which didn't get a great grade, 60% and my second book report, which
only did marginally better, 75%.
Then we have Samantha who wrote Russian Lit Through the Ages, and we also have another paper by her
to Montaigne and the art of the essay.
And then Raj looks like we don't have anything from Raj where his idea is.
Three.
I don't see anyone or any paper with ID of three.
Then we have Carlos who wrote Borges and Magical Realism, and then we have Lisa who didn't submit anything
either.
So let's say in the whole point of this is that it's the end of the year and I'm trying to figure out
who submitted what papers, what their average grades are.
If I need to talk to somebody about they didn't turn anything in, who's going to fail and so on.
So here's the data we're working with, but the first thing you need to do is write the schema.
So just again, to reiterate, primary key, foreign key for cha cha cha integer integer.
Moving on.
Once you have that done and you have your data in, you can play around with it.
But I'd like you to print this first table.
So all that we have here is first the name of a student and their corresponding paper, the title and
the grade.
Samantha and her two papers, Carlos and his paper.
Caleb and his two papers and then the corresponding grades.
So this is a joint.
What type of joint is it?
And you only need these three things.
So I don't want to see ID, I don't want to see student ID, just first name, title and grade.
And then once you've done that, we have another join.
I'll give you a hint.
Everything in this section, in this exercise involves a joint.
This one is different because we have the same basic stuff.
Caleb and his two book reports.
Samantha her two papers.
Raj Though no title, no grade.
Raj didn't have a paper.
Carlos Borges And Magical Realism.
And then Lisa.
No, no, Lisa doesn't have a paper either.
So print this out.
It's a different joint.
Then once you've done that, move on similar.
But notice, instead of having null and null when a student didn't turn any papers in.
We now have missing and zero for their grade missing for title zero for their grade.
So it's the same thing here.
We're just subbing in.
Then we get to this.
This is going to be an average grade next to every student.
So we have Samantha and the average of her two papers is 96.
Carlos The average of his one paper is 89.
Caleb's average for two papers, 67.5.
Raj has an average of zero because he didn't turn a paper in and Lisa has an average of zero as well.
So I won't give away too much about this, but you can use the same John you used here.
You just then need to condense it.
We need less information.
You'll need to.
Well, that's I'll leave it at that.
And then finally print this.
An extension of what this one was, except there's a new column which is passing status and passing
status.
Just says if the student is passing our class or not in the threshold, I should I should have written
this out.
The threshold is if they get a 75 average in the class or higher.
So Samantha passes.
Carlos passes.
Caleb, unfortunately, is below 75.
So he fails, as do Raj and Lisa.
All right, so there's a bunch of joints you need to write.
First things first, though.
Get the data in there, create the schema, get the data in, play around with it, and then attempt
these.
How many?
Five joints.