All right.
So we made it through or at least I made it through hopefully still hanging in there over the years
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 have multiple tables so we have to create schema we have to insert
the data then we have to you know 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 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 this a little bit more exciting because
once we have these tools and are built into a lot easier to start doing case studies which we'll be
doing are making it full application.
We're getting close to end products actually making things rather than you know silly one off the examples.
OK.
So it's a long preamble.
It's now your turn and this is where you're probably if you're confused if you're stuck with this joint
stuff.
This is hopefully where you'll be able to make some big connections to have some breakthroughs.
So it's this is a little different than exercise if you've done the past there's only one schema on
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 and papers.
So I'm a teacher let's suppose I guess I am teacher but let's say I'm working in a classroom with high
school students in an English class or literature class it's 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 names so don't even care about the last name.
Well 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 her 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 a student ID OK.
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 doing that.
And then we have six.
I can 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 that 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 and 60 percent.
My second book report and which only did marginally better 75 percent than we have Samantha who wrote
Russian let through the ages.
And we also have another paper by her to maintain the art of the essay.
And then Raj looks like we don't have anything from Rush where his idea is three.
I don't see anyone or any paper with the idea of three and we have Carlos who wrote or has in magical
realism.
And then we have Lisa who didn't submit anything either.
So let's say 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 you know talk to somebody about they didn't turn anything in who's going to fail and so
on.
So here's a 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 are far more integer integer.
OK moving on.
Once you have that done and you have your data in and 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 the student
and their corresponding paper the title and the grade.
Samantha and her two papers Carlos and his paper cart.
Caleb and his two papers and the corresponding grades.
So this is a joint.
What type of join is it.
And you only need these three things so I don't want to see I don't want to see student id just first
name title and grade.
And then once you've done that we have another joy.
I'll give you a hint.
Everything in the section in this exercise involves a joint.
This one is different because we have the same basic stuff.
Caleb in his two book reports Samantha her two papers rush though no title no grade.
Raj didn't have a paper.
Carlos Boreham and magical realism.
And then Lisa.
No no this is not a paper either.
So print this out.
It's a different joint and once you've done that move on similar but notice instead of having no and
no.
When a student didn't turn any papers that we now have missing and zero for their grade missing for
title 0 for their grade.
So it's the same thing here.
We're just subbing in OK.
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 Karlos the average of his one paper is 89.
Caleb's average for two papers.
Sixty seven point five.
Raj has an average of zero.
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 joint 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.
It just says if a student is passing or class or not and the threshold shouldn't 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 it's below 75 so he fails as you Raj and Lisa.
All right.
So there's a bunch of joints I 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.