### Intermediate SQL

Course
Time
9 hours 41 minutes
Difficulty
Intermediate
CEU/CPE
10

### Video Transcription

00:00
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
00:16
now. They're not the exact same request in the exact same database, but they're pretty close.
00:21
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
00:38
and review the document and see if you can come up with a solution
00:42
00:43
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
01:02
now with that said,
01:03
going to pause the video and in the next part we're gonna walk through the solution.
01:07
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.
01:23
Thesiger Programmer instructions are as follows columns required.
01:29
We need the time card CD column
01:30
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
01:48
and is normally found to be an incriminating numerical
01:51
column.
01:52
The next column we need is the employee's name.
01:55
Then we need the employees supervisors, natural key.
01:59
Then we need the employee's supervisor name.
02:01
We need the number of hours per time cards, so we're gonna need an aggregation function, some with a group by
02:08
and then we also need a running total of hours that resets for each employee.
02:14
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
02:30
my sequel indicates you should use the partition by call
02:35
now below that, we have the tables required.
02:38
The tables required include E P timecard, BP employees and be a account. Now you should be able to solve this.
02:47
In fact, you will be able to solve this using Onley those three tables,
02:53
and then we have some notes here at the bottom.
02:55
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
03:06
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.
03:15
Use the window function for the running totals.
03:17
You may need to include the same table more than once with an alternate alias to get the data of interests.
03:24
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.
03:36
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.
03:47
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.
04:01
So it's gonna be in the axiomatic a database
04:04
on E p
04:06
timecard.
04:08
So there it is.
04:11
And I know one of the columns I need is the Time Car CD, and I see that column.
04:15
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.
04:30
04:32
from E P Time card.
04:38
Spell it right,
04:40
do you see?
04:44
So let me go ahead and run that
04:49
Onda. We need the pc dot timecard CD.
04:55
That's the first column.
04:58
Next we need the
05:00
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,
05:16
so that is located on
05:20
pressure be account. So he a
05:25
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
05:35
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.
05:55
Whenever somebody initiates that process
05:58
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.
06:08
So a TC that employees idea
06:11
on this maps to the account idea.
06:14
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
06:24
so the natural key
06:27
is going to be the account CD. Let's go ahead and run this portion of it
06:36
on what is going on here?
06:45
Oh,
06:46
we gotta join on. Don't forget those keywords,
06:53
so join be account on. We need the alias before the on keyword.
07:01
So as you may have noticed, it's completely normal for when you're new to programming or even as an experienced programmer
07:09
to make small mistakes. As you go on, just correct them as you go.
07:14
So we have time, Corsetti, the Natural Key or the employees.
07:17
Now we need the employee's name,
07:21
so that's going to be
07:27
to be account. Name is going to run that sure that looks right
07:31
and Yes, we are getting it. Now you're seeing duplicates because you're seeing the same employees having multiple time cards.
07:41
All right, let me go ahead and
07:43
spaces out differently here.
07:49
All right.
07:50
With the name. Now we need the supervisors key. Now, where is the supervisor?
07:57
I think that's on the account, but let me check.
08:01
You know, I think it's on e p. Employees. I mean, I think that's why we need that table
08:09
properties
08:13
on
08:16
supervisor. I d ok.
08:18
Yes, it ISS. So we need to bring that in.
08:22
So we're going to join BP employees
08:28
and
08:30
on t. C. That company I d equals empty dot company. Ivy
08:35
and
08:37
T sees that
08:39
employee I d equals empty
08:43
dot
08:46
is it?
08:48
He account i d
08:50
And then to get the supervisor, we're gonna left join. And the reason we're gonna left join
08:56
is so that if someone doesn't have a supervisor, they still show up on the report
09:05
as we go on. Look at this supervisor table supervisor.
09:09
It has not know unchecked, which means it can be no.
09:13
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
09:24
so we need be account again. So we'll call this one man for manager
09:28
on man dot company I d equals
09:33
Let's see, empty that company i d
09:37
and
09:39
man dot
09:41
the account i d equals m dot supervisor I d.
09:46
Okay, because I'm connecting this. Be account to the employee's supervisor. I went ahead and used the columns
09:54
or reference to columns from the imp
09:58
table reference as opposed to the TC table reference.
10:03
So now we have that join that we need and we need the supervisors National key. So let's just be man dot
10:09
count CD.
10:11
We need the employee supervisor name, so that should be a man dot count name.
10:16
It's going and run this and make sure that it's looking good.
10:22
So here we can see
10:24
Mr Martin burn. AEA is the supervisor of Mr
10:28
Get it Done. Alright. Creative there. I didn't come up with a test data. That's just what it was came with.
10:35
All right, so next we need
10:39
the number of hours per time card. Okay, so
10:45
that is by this
10:48
u p timecard timecard CD.
10:52
I think I copied that wrong. I don't think that's right.
10:54
10:56
on T. C that
11:00
time spent. That's the one we need.
11:03
So there's some that
11:07
and we're gonna group by these columns.
11:20
Let's run that. Make sure that makes sense. Okay?
11:26
Sure does.
11:30
Okay, now we need that running total that resets for each employee.
11:35
So we're going to do you need to use the window function with the partition by command as well.
11:41
So
11:43
we're gonna start this out with a some of TC dot time spent.
11:48
I'm going to go over.
11:52
They're in a partition
11:58
by
11:58
B a dot account CD.
12:03
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
12:16
in order
12:18
by and the order by is important. Otherwise, the summing will be incorrect.
12:26
So TC dot timecard cd
12:30
comma and be a dot account city.
12:35
Let's go ahead and run that. See if that works for us.
12:39
Okay. Says we don't have a We're missing one of the aggregate call aggregate columns.
12:46
All right, so it's this guy right here.
12:52
What's going and run that
12:56
on what we have here.
13:01
So that's still looks correct. And if we're looking at the some here,
13:07
we can see that when we add this all together, we get that running total.
13:13
So this is correct on this running total is correct, and then it's resetting for the next
13:18
employees.
13:18
So again we could see 4 80
13:20
9 60
13:24
33 63 3 60 So that's working.
13:30
So it looks like
13:33
we are done.
13:37
Okay, that's our solution.
13:39
So the key points to the solution include
13:43
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.
13:54
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.
14:07
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
14:18
when a new employees and the data occurred
14:22
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.

### Intermediate SQL

This free course introduces the student to intermediate concepts found in the implementation and application of Structured Query Language (SQL) within professional business environments.

Kitt Parker
Instructor