I welcome back to module six. This is less than 6.9 challenge one. So this is the first challenge of module six. So let's go ahead and walk through these bullet points. These challenges air based on real world requests that I've had to deal with
now. They're not the exact same request in the exact same database, but they're pretty close.
So when you're taking a look at this, be sure to read the requirements document attempt to solve before viewing the solution. I am going to walk through the solution in this video. So after I'm done with this slide, I will begin the solution. Walk through. So be sure to pause
and review the document and see if you can come up with a solution
There's multiple ways to reach the same answer, and that's one of the things that some people find difficult in programming is that you can send three programmers at the same problem, and they could all solve that same problem differently now. The most important thing is that you derive the correct answer
going to pause the video and in the next part we're gonna walk through the solution.
So let's go ahead and take a look at this course challenge document and walk through it. Theocratic a database usual utilizing the axiomatic a database. Generate the following report. Management needs a query that will expose time card data with some employees information that includes a running total as well.
Thesiger Programmer instructions are as follows columns required.
We need the time card CD column
We need the employees. Natural key. Now recall that the natural key is the key that people see from the front end. So your front on users are typically aware of this key, and they know what it looks like now. The surrogate key is the back and key that users typically do not know about
and is normally found to be an incriminating numerical
The next column we need is the employee's name.
Then we need the employees supervisors, natural key.
Then we need the employee's supervisor name.
We need the number of hours per time cards, so we're gonna need an aggregation function, some with a group by
and then we also need a running total of hours that resets for each employee.
Now, one component of the over window function that we did not go over was the partition by call. And we're going to use that partitioned by to indicate win are running total needs to reset. So if you're not familiar with that, be sure to check out the my sequel window function and look at how
my sequel indicates you should use the partition by call
now below that, we have the tables required.
The tables required include E P timecard, BP employees and be a account. Now you should be able to solve this.
In fact, you will be able to solve this using Onley those three tables,
and then we have some notes here at the bottom.
For your query, be sure toe use surrogate keys and not the natural keys on your joints, so the circuit keys are for the programmers. The natural keys are for the front end users to look at
in most database applications. The surrogate key is for the programmers, and the natural keys are for the front end users. So that's just reaffirms what I just said.
Use the window function for the running totals.
You may need to include the same table more than once with an alternate alias to get the data of interests.
And you will need to use the partition by clause in conjunction with the over window function. Now, with that said, let's go ahead and start writing this query.
All right, so let's get started. I've copied some of that document onto the D Beaver pain, so I can just quickly see what some of the notes were on that document.
And if we get started, we can see that we're gonna need E p timecard. And because the requests him to focus on time card data, the running total of hours and the hours I'm gonna go ahead and open that table first and take a look at it.
So it's gonna be in the axiomatic a database
And I know one of the columns I need is the Time Car CD, and I see that column.
I see the employee i d. Something to keep in mind about employee I d. Is that it maps to the B account I d. On other tables. You won't see it because there's no foreign key to highlight that highlight that.
So let's start with a select star
So let me go ahead and run that
Onda. We need the pc dot timecard CD.
That's the first column.
employees. Natural key. Well, that's not available on the time card table. Natural keys, air typically going to be stored on the definition table of whatever objects you're working on. And I say normally, because you can always veer off from that in some cases,
so that is located on
pressure be account. So he a
t c dot company I d need to make sure we include those company i ds because that's part of the composite primary key for this database. And even if we just have one
company idea and useful right now, the sample that is all for company, too. But in this application is very easy to copy a company into another company, which is commonly done by people that use Akeem Attica to test data. And if you don't respect that composite key in your query, all your queries for that application will get messed up.
Whenever somebody initiates that process
so be sure to include that company idea, composite key or any application that you're working in. Be sure to respect the composite key if you're if you know that it's there.
So a TC that employees idea
on this maps to the account idea.
I've mentioned it a couple times because that's more or less inside knowledge. There's no foreign key that would tell you that you just need to have experience with the database and then you know that
is going to be the account CD. Let's go ahead and run this portion of it
on what is going on here?
we gotta join on. Don't forget those keywords,
so join be account on. We need the alias before the on keyword.
So as you may have noticed, it's completely normal for when you're new to programming or even as an experienced programmer
to make small mistakes. As you go on, just correct them as you go.
So we have time, Corsetti, the Natural Key or the employees.
Now we need the employee's name,
so that's going to be
to be account. Name is going to run that sure that looks right
and Yes, we are getting it. Now you're seeing duplicates because you're seeing the same employees having multiple time cards.
All right, let me go ahead and
spaces out differently here.
With the name. Now we need the supervisors key. Now, where is the supervisor?
I think that's on the account, but let me check.
You know, I think it's on e p. Employees. I mean, I think that's why we need that table
Yes, it ISS. So we need to bring that in.
So we're going to join BP employees
on t. C. That company I d equals empty dot company. Ivy
employee I d equals empty
And then to get the supervisor, we're gonna left join. And the reason we're gonna left join
is so that if someone doesn't have a supervisor, they still show up on the report
as we go on. Look at this supervisor table supervisor.
It has not know unchecked, which means it can be no.
So it's possible that someone would not have a supervisor. I don't know. That's true in the test data because it's a possibility. I'm gonna go ahead and left. Join it
so we need be account again. So we'll call this one man for manager
on man dot company I d equals
Let's see, empty that company i d
the account i d equals m dot supervisor I d.
Okay, because I'm connecting this. Be account to the employee's supervisor. I went ahead and used the columns
or reference to columns from the imp
table reference as opposed to the TC table reference.
So now we have that join that we need and we need the supervisors National key. So let's just be man dot
We need the employee supervisor name, so that should be a man dot count name.
It's going and run this and make sure that it's looking good.
Mr Martin burn. AEA is the supervisor of Mr
Get it Done. Alright. Creative there. I didn't come up with a test data. That's just what it was came with.
All right, so next we need
the number of hours per time card. Okay, so
u p timecard timecard CD.
I think I copied that wrong. I don't think that's right.
time spent. That's the one we need.
So there's some that
and we're gonna group by these columns.
Let's run that. Make sure that makes sense. Okay?
Okay, now we need that running total that resets for each employee.
So we're going to do you need to use the window function with the partition by command as well.
we're gonna start this out with a some of TC dot time spent.
I'm going to go over.
They're in a partition
Now there's a couple columns you could use. You could use the B account i d the point. The important thing is that you're using a unique identifier for the employees
by and the order by is important. Otherwise, the summing will be incorrect.
So TC dot timecard cd
comma and be a dot account city.
Let's go ahead and run that. See if that works for us.
Okay. Says we don't have a We're missing one of the aggregate call aggregate columns.
All right, so it's this guy right here.
What's going and run that
on what we have here.
So that's still looks correct. And if we're looking at the some here,
we can see that when we add this all together, we get that running total.
So this is correct on this running total is correct, and then it's resetting for the next
So again we could see 4 80
33 63 3 60 So that's working.
Okay, that's our solution.
So the key points to the solution include
the use of or the unique parts of the solution are. They used to be account table twice used. It wants to get the employees natural key, and then you use it again to get the manager natural key.
You needed the E p A employees table to get the employee's supervisor. So that's the bridge you cross to get from employee data to the supervisor data.
And then also something else that we used was the partition by as part of the over window function to create the correct running total that would reset
when a new employees and the data occurred
and it appears that we got everything working on DA that completes this lesson, this challenge and I hope to see in the next one. Thank you. Bye.