Time
9 hours 41 minutes
Difficulty
Intermediate
CEU/CPE
10

Video Transcription

00:00
All right, welcome back to module seven. This is less than 7.6 is the first challenge of this module. And just to recap over the bullet points, challenges are based on real world requests.
00:11
You should have read the requirements document and do attempt to solve before viewing the solution. We, of course, will walk through the solution as I reach it. And remember, there are multiple ways to reach the same answer. The most important thing is that you derive the correct answer in your solution and without letting complexity get out of control. Hopefully,
00:31
and in this child's, we will create a sequel data function. So let's go ahead and go over the requirements document.
00:38
Welcome to the Module seven Course Challenge document. This Is it. The Intermediate Challenge, one of Model seven.
00:44
The sequel program requirements are as follows, utilizing the axiomatic, a database jury and employee information function that generates useful data for a programmer without having to write complex queries. So the idea is that this function is a function that a sequel programmer might use to get employee data so that they do not have to go through the trouble
01:03
of writing the joints or identifying the tables involved in getting that data.
01:10
The function requirements are that the function name be employee data. The function parameters are the company i d. The B account I D, which is also known as Thean Employee i. D.
01:22
And then the request parameter is a text field that the programmer passes in and is the actual request for the type of data that they are looking for. So if they pass in the word key that returns the employees Natural Key or the B account dot account CD field
01:42
Theseus per visor if the word supervisor is passed in and returns the employee's supervisor's full name,
01:51
if employed is passed in,
01:53
it returns. True if employed falls. Otherwise, this is determined by the presence of an open position.
02:00
And then finally,
02:02
if the word salary is passed in, it returns to employees. Current or most recent salary
02:08
and the specific data type that's returned is a string or bar chart 32 the reason we're returning of Arch are 32 is because we are returning various data types and a string concussed for those various data types. For example, the key will be a string field supervisor will be a string field.
02:30
The salary will be a decimal field
02:31
and employed could be a Boolean field. But we're going to return their words true or false. The actual word
02:39
now with that said, Let's go ahead and work through a solution to this problem.
02:45
All right, let's get started on that solution.
02:46
I have the bare bones of the function up on the screen.
02:51
I don't have the meat of the function. We're going to develop that together. But let's walk through what I do have here at the very top. I dropped function If exists, Akeem attica dot employees underscored data, and I'm being explicit with the call here. So
03:05
I named the database, and then I named the Function. Now, if I wanted Teoh, this would work just fine, too.
03:12
But it's OK to be explicit. In fact, it will work in some cases where it might fail otherwise. For example, if I didn't have the database selected, this would return it air because it wouldn't know which database to create the functioning
03:23
or where to drop the function, but with an explicit naming technique. Even if I don't have a database selected, it will still create the function in the correct place or drop the function in the correct place.
03:36
And then after that, we do the limiter swap that we see on the other object types, too.
03:40
We then create a function with with the name and then the parameters, which is the company I D. Which is a manager. The employee I D. Which is an imager. And the request, which is a character size of 12 which means that a character string of length up to 12 would be passed in. Of course, if you exceed that for any reason,
04:00
the other or the extra characters would be truncated.
04:04
Then we return a character size of 32 the same is true. If we return a string that would be too long for that. Like 34 characters, we would truncate those last two characters.
04:15
The function is deterministic, and that's telling the database that if the same parameters air passed in that it can expect the same answer.
04:24
With that said, Let's go ahead and take a quick peek at that requirements document and see what we need to develop
04:31
so we can see that the key returns employees natural key from the B account account See Field. So that's going to be any pretty easy query
04:40
supervisors gonna be a little bit more difficult employed will be a little bit more difficult and the salary will be even a little bit more difficult than the previous one. So this is actually a good order to actually complete these requests and as well, because they climb up in difficulty as we go through them. So let's go ahead and start with that key request.
05:00
We're going to start with a if statement. If requests equals key,
05:06
then
05:09
we'll go ahead and end the f here, although I'm really gonna do an else if, but I'm just doing that. So I contest each component as I move through the function because I'm gonna test each component so that I don't have a bunch of problems at the end. I'm Onley ever dealing with one problem at a time, and that's as they occur.
05:29
So I'm gonna start with a select star now. This would happen after the function creation, and this is just my way of testing. Select star from BP employees. It's a employee function, so it makes sense to do that.
05:44
And the function is employee data
05:50
on a company. I d is a parameter
05:54
and the account I d.
05:57
And the first part of the function we're testing is the key.
06:01
No.
06:03
And up here, we're going to return.
06:08
We're going to put those brackets in and end with a semicolon.
06:11
Don't miss those semicolons, otherwise it will not work.
06:15
And we need to go to the B account table and we're looking for the account CD field, So this is gonna be pretty easy. We're gonna take those company and be account parameters, and we're going to use a query and were key. Use those key fields to return the data.
06:29
So
06:30
go ahead and select star from the account where company I d equals
06:39
company. I d Make sure your parameter names do not match or mirror the actual name of columns. Otherwise that my single engine will get confused
06:48
and the account
06:54
the account I d
06:56
equals employees
06:59
idea.
07:01
So we need the account CD
07:06
on that looks like it should work. Let's go to and rawness this whole script
07:13
on. It looks like it's working correctly because right here, we dropped the function. We then create the function
07:18
that statement ends, and then the next statement of a selection occurs, and then we're actually using the function.
07:27
So we need to change this. We're not gonna end are if there were going to do a Nelson for the next requirement,
07:33
I'm gonna end that one. And if
07:36
and it's gonna be else if request equals,
07:42
what was the next one? The next one was Theseus per visor. Billy
07:46
on the supervisor is gonna require two tables the E p A employees table and the B account table. So we need the e p A employees table because that's actually where the supervisor is indicated. If we bring up that table and take a look at it,
08:03
we can see if the supervisor i d. Is on this table.
08:09
So then
08:11
return
08:15
Select star from E P employees
08:20
when an alias that with an e p.
08:22
Then I'm gonna join the account.
08:26
When a list that with a b a
08:28
on the company I d equals e p dot company I d
08:33
and e p. That's
08:37
supervisor. I d equals B ada. Be account I d. That's where the target data is, here's where we apply Air Filter, where
08:46
U Preda Company I d. Is equal to company idea
08:52
and BP that the county is equal to
08:58
the employee idea.
09:03
No, right,
09:05
that's semi colon.
09:07
Then we need to be sure to get the right data here, so that's gonna be a
09:13
g A dot account name.
09:16
Let's go ahead and change this to supervisor and make sure this component is working
09:22
and run.
09:24
And yes, that's what I expect to see. So it's working.
09:30
So let's move on to the next one.
09:33
And the next one is We're asking if the individual is still employed. So I needed changes and if actually was gonna drop in an else If right here helps
09:43
request
09:46
equals
09:48
employed,
09:52
then
09:54
return
09:54
brackets
09:56
semicolon.
09:58
So for this one, we need the E p A employees position table,
10:05
and, um, that's actually it. So how can we get whether or not they're employed? What, We're gonna look for the presence of a position being active, So let's take a look at this table.
10:18
BP employees
10:22
position
10:26
so we can see that there is. This is active column,
10:31
so we're not actually going to check for whether or not the date makes sense, We're just going to check that the is active column is true now. This kind of choice would come down to application. Design is, can you depend on this column? Or does this column mean what we believe it means? And that if
10:48
somebody has an active position than they are an active employees
10:50
and sometimes within the application, it's being used differently at different companies and requires some insight in business rules on how you would take that approach? Now, that said, in this scenario, we're gonna just treat it as if the is active flag indicates that the person has an active position.
11:09
For example, if they did not, it would be a zero, which would mean false,
11:15
and then they would have no positions with a one.
11:18
Now, with that said, how was the best way to return return return that that's true or false?
11:24
Well, we could use an aggregate function where we count the number of active fields, present foreign employees, and we could return true if it's greater than zero and false otherwise,
11:39
and I think that might be a good way to go. So let's go ahead and implement that.
11:46
So
11:48
we're going to select
11:50
Star from BP employees Position
11:56
Good.
12:00
Where
12:01
Company I d equals company
12:05
on this score. I D and
12:07
employee I d
12:09
equals employees idea.
12:13
Now we still need to get that case statement correct. So let's go ahead and put that in there.
12:18
Case
12:22
when
12:24
count, just in case we get more than one
12:28
really would make sense for them to have more than one active position. But just in case this function will still
12:35
function correctly, then
12:39
true
12:39
else
12:41
false. And
12:46
so we're using an aggregate function. We're counting the results
12:52
when those results are greater than zero. So one or more. Then we return true. Otherwise we return false. So let's go ahead and run that function
13:01
employed.
13:05
I don't think there's any.
13:09
I don't think there's any examples of an employee not being employed in this sample data. We could change that.
13:16
So go here.
13:18
Um 28 91 has two positions.
13:22
Drums goes, switches other one to a zero.
13:26
I'll save that
13:28
now. He should have none.
13:30
I go ahead and run this whole script again. All right,
13:41
let's try this one.
13:43
You know what it is. I forgot the last requirement
13:48
and is active equal one. Let's run that.
13:54
There we go. There's are false So now it's working.
13:58
Now let's move on to the last request.
14:01
I'm dropping another else. If
14:03
request equals salary,
14:09
then
14:11
return
14:13
brackets semicolon.
14:15
And what do we need to get this? Ah,
14:18
get this answer. Well, this one's a little bit more complicated,
14:22
and that is because we actually need to select the maximum effective date for the employees and use. That is part of our primary selection mechanism. Because we don't know how the counting staff or the administrative staff are gonna move these records around.
14:39
It's possible that they could take a
14:43
a salary that was put in later and move it to a date range. That's before the previous salary. So we can't just use the max I d.
14:54
So let's take a look at what this table looks like. This is the
14:58
E. P. A. Employees rate table.
15:07
Okay, so we have a rate I d. You have employee I D.
15:11
So the way we need to solve this is that we need to get the effective date the maximal effective date for an employee and then use that date as a selection mechanism and the primary query to select that company i d. That employee i d. And that maximum effective i d to get the most recent annual salary.
15:31
And again
15:33
there is some data missing from the annual salary. That's not a concern for the requirements. So even if we return no back, that means that that user in this application doesn't have an annual salary and
15:46
that we're still within our scope of the request, even though we may notice that
15:52
in some cases this function we return No, and depending on who you are discussing this with that may or may not make sense.
16:00
So
16:02
what we need to do
16:03
is let's create the query that will select the maximum effective date. First, let's break this apart. So let's select that maximum effective date. So we're gonna need the max function,
16:17
and we're gonna need the effective date
16:22
from
16:22
E p. A employees rate
16:30
where a company I d equals company I D
16:36
and employee I d equals employee. I d.
16:42
Okay, so with this query, we have a piece of the puzzle. We have that maximum effective date which will make sure that we get their most recent salaries effective date or start date.
16:55
So that's not the whole, um, the answer that we need. So let's right the primary query and we're gonna need this
17:03
query to filter the primary results.
17:07
So we're gonna return, put another bracket and
17:15
go ahead and select Star.
17:19
Actually, I know what I need to select annual salary
17:23
from E P Employees Rate.
17:26
Where
17:29
Company I the equals company
17:32
I D
17:33
and
17:36
Employees Idea
17:38
equals employees idea.
17:41
Now we need to make use of that. Query wrote.
17:45
Let's go and start the filter. Constraint and
17:51
effective date is equal to
17:55
our answer for this one.
17:59
So we're using a sub query
18:00
to get the one of the filters of the primary query.
18:06
And well, that will make sure is that we always get the most recent or the most the most recent salary, which was part of the requirements.
18:17
And let's go ahead and give that a test
18:21
so salary,
18:22
we're probably gonna see some Knowles which we expect to see,
18:26
and we dio and that was our the answers I expect to see.
18:30
So that completes this challenge Now the trickiest. One was the salary one because the best way to do that was actually use a sub query.
18:40
And that's because you could see a scenario and you don't see it in the sample sample data. Unfortunately, where people have multiple salaries as they have evolved through the company, they've gotten raises every year, and that causes them to have multiple salaries. So you have to have a way of getting that most recent salary.
18:56
Now that said, that completes his challenge. I hope you enjoyed working through the solution, and I hope to see you in the next challenge slash 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