Time
9 hours 41 minutes
Difficulty
Intermediate
CEU/CPE
10

Video Transcription

00:00
I welcome back to Model six. This is less than 6.10 challenge to. And if you went through the previous challenge, this slide is mostly the same is that one challenges air based on real world requests. Read the requirements Document
00:15
attempt to solve before viewing the solution.
00:17
Remember, there are multiple ways to reach the same answer. The most important thing is that you derive the correct answer
00:24
now. With that said, Let's go ahead and take a look at the requirements document.
00:28
All right, here we are at the course Challenge document. So let's go ahead and take a look at this. Utilizing the axiomatic a database. Generate the following report. Management needs a query that will expose department statistics. The lead programmers instructions Blow will guide you on the requirements.
00:44
Senior Programmer instructions, columns required.
00:47
Department I D Department description
00:51
Number of active employees assigned to each department.
00:54
Use the employee position table. One to many relationship occurrence of no on column end date. Any cased employee is still active,
01:03
so that sounds like a good area to do a table expression. It would seem that maybe one of our table expressions might be active employees and active employees are identified by having an open position. And that's identified on the position table by having a no foreign end date,
01:23
meaning there is no end date for that position.
01:26
And then finally, number of hours from time card
01:30
again on Lee Active Employees. We don't want the hours being
01:34
collected from inactive employees,
01:37
so tables required
01:38
AP timecard e p A. Employees be account
01:44
BP employees position an e p department.
01:48
We'll see we have some notes
01:49
for this query. It's likely you'll need table expressions to make the data flow more manageable.
01:56
Yeah, I think the active employees will make an excellent table expression.
02:00
Tried to make a table expression for active employees. Yep, I mentioned that again.
02:06
Then connect that to a department query that utilizes aggregation to generate account.
02:12
You also need to include the timecard hours so that managers can see how many hours are being used in each department.
02:17
Make sure to group by when you need distinct results.
02:23
We have some bullet points employee I D Maps to be account across tables to keep that in mind. That's the key connection
02:30
Department I. D. Is located on the E p A Employees table with respect to employees assignment
02:37
Focus on the department i D column as the overall report deals with statistics of the departments.
02:45
Okay, so if you like to take a shot at this, go ahead and pause the video in the next section. We will be working through the solution. All right, let's work through that solution. Now. I've copied the some of the data from that document into the the D Beaver pain, so I can easily look at it and see what the requirements were.
03:01
And, um so let's get started.
03:05
So I know we need active employees.
03:08
So a good way to handle these type of problems is to break things apart
03:13
and maybe use
03:15
table expressions to separate your areas of concern. So I know I need active employees, so we'll start with that so we'll start with with
03:22
active underscore employees
03:27
as
03:28
brackets
03:30
and let's start righting to query in here. So we're select star
03:37
from
03:39
E P A employees
03:43
and
03:46
and I'm going to join
03:51
the position table because the way that in a lot of applications that you can tell if an employee's active or terminated is by positions table. If they don't have an active position, then they're probably not working. Course you would need to check with how you use is used application to confirm it. That's true. But in this case, we're gonna assume that that's true.
04:11
And what we're gonna do is we're checked the positions table and make sure that there is a null in the end date. So that looks like
04:20
this E p employee position. If I open this up,
04:25
I can see we have a start day. We have a start reason we have an end date. This end date is what we are interested in focusing on.
04:31
I believe on the test data. Everyone that's on here is still active,
04:36
but because this record of who ever this is it's the CEO for the position I d.
04:43
They have no end date, which means that they started on 2012 12 31 and they're still working. And there's no end in sight. So they're still active now. If there was an end date filled in here of 2013 January 1st,
04:58
it would look as if that person had worked one or two days and then they don't work here anymore cause they ended on that date. Now this is a one to many relationship
05:09
based on the employee's. So, for example, if you're represented on this table and you get a promotion,
05:15
your old record will get an end date when your old position ended and it you'll get a new record with your same employee i. D. Number that has a new start date and no for the end day. And that's how this application would handle promotions. And I've seen it handled the same way in other applications as well.
05:31
So we're going to use that to identify our are active employees.
05:41
So join E P employees
05:44
position
05:46
and call it pos for position
05:48
on a pos dot company I d. That's the equal. The company idea is the easy one, because that's just always part of the composite key
05:57
and
05:59
dot the account I d
06:01
equals positioned. I think it's employee I d. Over here.
06:06
I'm gonna say where
06:11
position dot end date
06:14
is no.
06:16
And then we're gonna get to very specific. Actually, we're gonna get three very specific columns. We're gonna get the employees that be account I d.
06:25
We're gonna get the employees,
06:28
not
06:30
company i d. Let's put that before the account I d.
06:33
Company I D.
06:36
And let's bring back the department ideas. Well, because we have that here, and that's kind of the focus of this report.
06:45
So
06:46
company i d be account i d department I d active employees, and it is no. Now there's an issue here.
06:55
We really should have the possibility of getting multiple returns on Knoll because an employee should only have one and date. That is no, but we are not going to chance that we're just going to group by and make sure
07:10
that we do get unique records.
07:15
You know, you commonly see some programmers used the word distinct to group or it doesn't group by, but it's ah, away Teoh make the records distinct,
07:24
but it's a good happy to get used to using the group by because you use it with all the aggregate functions. Anyway,
07:30
let's go ahead and test this.
07:32
Flexed are from active
07:35
employees.
07:41
Run.
07:44
Okay, It looks like I got some results back,
07:46
so we have the active employees.
07:49
Another component we need is the timecard hours,
07:55
so let's go ahead and start a another table expression.
08:00
What time card? Hours as
08:03
the brackets.
08:07
And then let's go ahead and just select star for now. What tables do I need? Well, I definitely need the time card table,
08:16
So time card
08:18
P time card
08:20
call that TC
08:22
and I only one active employees. I don't want the time card data of inactive employees, so I'm gonna join to that active employees table expression I have above so join active
08:37
employees a e
08:39
on a company. I d
08:46
equals t See that company idea on
08:50
t see that it's an employee. I d equals e
08:56
that be the account I d.
09:01
All right,
09:05
so I need to get those statistics. I'm gonna need to get rid of the star, so TC dot
09:11
a company idea
09:15
on. Then we will get the TC. That's
09:20
department I D.
09:22
No, no, not TC Empty that
09:26
or a
09:28
for active employees.
09:30
That
09:31
department i d.
09:37
And then let's some
09:41
the TC that
09:45
hours or time spent,
09:50
all right.
09:50
And then we will group by
09:54
these two columns,
09:58
right?
10:01
And let let's make sure that's making sense. So select star from
10:07
time
10:09
card
10:11
hours.
10:13
That's another score
10:16
run this
10:18
says I have a mistake. I forgot a comma
10:22
Go.
10:24
All right. So we have our departments
10:28
and our hours
10:31
That looks right.
10:35
Okay. So far, we have our active employees as a temple expression.
10:39
Then in our time card hours, we're going to the time card table. We're connecting up our active employees that we that we formed in a table expression above
10:48
to make sure that we only get the data of active employees because we're focused on the department were including the department I d. As the aggregating pivot of some function.
11:01
So what do we need to do now? Well, we need to bring the department table, and and I don't think we need any more table expression. So let's just go ahead and,
11:11
uh, right arm a inquiry here. Start with a select star
11:16
from
11:18
E P Department.
11:20
And, of course, if we're interested in knowing what's on the table, we can always just look it up.
11:26
Of course we got a
11:28
spell it right?
11:31
Maybe
11:35
U P. Department. There we go.
11:39
So we can see we have these departments on our definition table. Four departments,
11:43
not too many.
11:46
So let's go ahead.
11:48
I select from it.
11:50
Gonna alias that with d B t d e p T.
11:54
Then I'm gonna left join.
11:56
And this is so that if I have departments on that department definition table that aren't representing and represented in the timecard anywhere, they will still come back with a zero, as opposed to not coming back at all.
12:11
So left join
12:16
active employees because we're gonna need that count. So we're gonna need that active employees table expression
12:22
on department dot
12:24
company i d equal e m p. That company i d
12:30
and departments
12:33
that department I d
12:35
equals e and P
12:37
dot department idea.
12:41
All right,
12:43
so let's go ahead and,
12:45
uh,
12:46
give it a that count company idea
12:54
department dot department i d
12:58
and the apartment dot description.
13:01
And now let's count
13:03
Count
13:07
P that
13:09
G count I d.
13:13
And, of course, to use an aggregate function, we have to group by.
13:18
So let's go ahead and get this in there.
13:24
Let's run this and see what it looks like.
13:28
All right, So we got our active employees counts and administration got four and consultant got 10.
13:33
And sales. We got the neat thing about a report like this too is that wherever it was being rendered, whenever they added new employees, you would see new counts poppin
13:45
so they would see. Oh, Consulting Consulting added to people today, I can see because I remember yesterday it was 10. Today it's 12.
13:52
That's the kind of benefit you get with these or the reason management looks for these kind of reports.
14:00
So we're not done yet. We need the hours that are being used per department by active employees,
14:07
so we actually need to join the table.
14:09
I need to bring in the time Cardo hours. So left join
14:15
timecard hours
14:22
t see on t c dot company i d
14:26
equals
14:26
harman dot company idea
14:30
and T c That
14:31
apartment I d
14:35
equals
14:37
e p t dot department idea.
14:41
And then we need Teoh
14:46
some
14:50
t c dot hours
14:52
alias this as ours. It needs a name
14:58
as department hours
15:03
on as department
15:07
employees
15:09
count.
15:09
Come on,
15:11
it's going and run this. Make sure it works,
15:16
All right, I'm seeing something I know that nobody wants to see, and that's knows.
15:22
So the reason that's happening is that you probably have some active employees that have no hours entered and any number you add to annul and sequel becomes no. So if I do know plus five,
15:35
that becomes no.
15:37
So what we want to do is that when an employee has no hours and we're summoning them together, we want instead of no, we want that knoll to become a zero. So there's a specific function that we need, and that's the police function. So let's go ahead and insert that
15:52
now. There's two places we could try to put that we could try to put that on the outside of the some,
15:56
or we could try to put it on the inside of this some. Now which one is the better choice?
16:02
Well, it's the inside of the some
16:03
because what will happen is on the inside, you're gonna get you No, no. Plus five plus five plus knoll, and you're gonna end up with no. And then police on the outside is gonna change that to zero.
16:17
Now, if you put it on the inside and you have five plus zero plus five plus zero, you're gonna end up with 10. So it's gonna be you end up with 10 or you end up with zero so the more accurate place to place that is on the inside
16:32
police
16:33
Any time we get no, you want to get a zero Instead,
16:37
Let's go ahead and run that and see what we get.
16:41
All right. Looks like we got zeros. So that's fine.
16:45
And it looks like we are done with this challenge.
16:49
So what were the interesting parts of this challenge? While we needed to use table expressions to make it more manageable,
16:57
we used one table expression and get the active employees.
17:02
We used the another table expression to get the timecard hours, and we focused on the department I d in both table expressions because the primary report request focused on department statistics. So that made sense. And then, on the final query,
17:18
we joined the previous two table expressions on the department i ds
17:22
to get those departments statistics. And the next interesting thing that we needed to do was be sure to use the coal lease function to replace Knoll with zero so that ah, people on the front end, we're looking at zeros instead of Knowles.
17:38
So that completes this challenge. No, to see in the next lesson. Thank you

Up Next

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.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor