Intermediate SQL

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

Video Transcription

00:00
I work with a module seven. This is less than 7.7 challenge to
00:05
again. These are the bullet points that are familiar to all the challenges. They're based on real world requests.
00:11
Be sure to read the requirements document attempt to solve before viewing the solution. Oftentimes, there are multiple ways to reach the same answer. It's most important that you derive the correct answer, and in this challenge we're going to be creating a sequel procedure that as a position to an employee.
00:30
So let's go ahead and take a look at that requirements document.
00:34
All right, here we are at the requirements document. This document describes the intermediate sequel Challenge to four Module seven, and I do want to go ahead and say that this is a pretty good challenge. There are a lot of variables to pay attention to
00:47
now. With that said, let's go ahead and step through the requirements so that we can go ahead and complete those requirements.
00:53
So starting at the top,
00:55
utilizing the axiomatic of database generator procedure that, as a new position for an employee
01:00
name, the procedure add employees position were strictly concerned with adding a position with this procedure, not removing or editing
01:08
the parameters required.
01:11
Company I d employee I D Position I. D Start date and line number line number is an AL variable, and it completes the composite key. Now, when a programmer uses our procedure, they have 2/3 of the composite key. They have the company i D and the employee i d. What they're missing is the line number.
01:30
So when are procedure completes, we will give them the line number back
01:34
and then in their own program. If they need to reference the position that just created, they won't have a problem. They'll have the full key
01:41
now. The tables required
01:42
includes the E P A Employees position table and the E P position table.
01:49
Now there are some columns on the E p A employees position table that are required so we have some notes on what those requirements are and how to satisfy them.
01:57
The line number calm is the next number in the sequence. So if an employee has two positions and we're adding 1/3 position, that means the line number is gonna have a value of three.
02:07
This is also returned after insert as an out variable,
02:10
he is. Active COLUMN. If the start date has passed, it will be a one. Otherwise it'll be a zero. So we're going to do a little bit of date gymnastics to figure out if the given position is active.
02:25
Need to change the last position to a zero. If the new position is one
02:30
Onley, one position should be active at any time.
02:32
Start reason Pro for promotion. New for a new hire. So that's a three character string.
02:38
The is terminated column zero for false were not handling terminations in this procedure. So we're going to default to zero
02:47
is re hirable zero. This is used in conjunction with Terminated Again. We're not concerned with that in this procedure, So we're just hard coating to a zero
02:58
for the created by idea. We're just going to use the admin account I d. So we're gonna hard code this idea in. In a production environment, you may require that the programmer pass in their own i D. But it's also the case. A lot of the time that an admin account gets used
03:15
created by screen I D column will be a screen I d. That's hard coated in the procedure
03:20
they created. Date and time will be a current time stamp.
03:23
03:29
The last modified by screen idea will again be that same screen I d above
03:34
and that last modified date. Time will be the current time stamp. Remember to update any records your procedures touches. So if we're closing out the pre previous position with an end date, be sure to update that last modified date time field as well,
03:50
and make sure to include the following
03:52
in your procedure test. The position is valid.
03:55
Test that the employee exists
03:58
tested the start date parameter does not fall between previous positions. Start and end dates. So that would be that would indicate a position or two positions that have the same date ranges, which would mean that that employees had to positions at the same time, so that doesn't make sense, and we're going to avoid it.
04:15
Test. The start date parameter does not occur before the previous start date on the table, and that the start date parameter is more than one day after the previous start dates this. Check insurers that when we update an end date, the two dates do not cross
04:30
tested. The new data is the first position for a new employee or if it's a promotion.
04:36
If it's a promotion, be sure to update the previous record with an end date. That is, before the start date by one day
04:44
used transactions for the update and insert for a promotion.
04:48
Either both happen or neither, so we don't want to accidentally update the previous record and then fail on the insert and Onley complete half the process. We want to complete the full process.
05:01
If it is the employees first record insert. The required data
05:05
is active. Should be based on whether or not the current date is between the start date and the end date. At the end, it is no. Then is active. Should be one.
05:16
Use the date function to convert daytime data type. To date, we want to only concern ourselves with dates and ignore the time portion or, better stated, not let the time portion interfere with our results because when we are testing which data is larger, if time it's part of that data,
05:34
then time will be considered as well.
05:38
So we're going to make things a little bit simpler. In fact, if you run this application, the front end user can't see the time that a position was created or starts or ends. They can only set the dates. So the time portion from a front end user of his application would actually not even be aware of the time. So we're gonna follow suit
05:57
and we're going to use the date function to just convert all our date time stamps
06:01
Two dates when we are comparing them and seeing if we should insert data, update data or update that is active calm, for example. So use that date function to make sure that all your date times are just dates.
06:15
So here are the instructions.
06:20
There are no foreign keys. Because of this, there are some extra work to be done. We must check that The position I D passed in as a parameter is Avella position.
06:29
If the position for parameter is not a valid position, we must throw on air or indicate that an air has occurred in some way. We must a compliment. We must accomplish the same checks for the employee number
06:41
we must check to the start date passed into the procedure does not clash with related dates already stored, for example, does not make sense to have two salaries or two positions with same date range.
06:53
And as an additional note, I just wanted to add as software engineers, we should be a bit pessimistic, not too pessimistic. Now, when we design our programs, we should assume that users will submit bad data even if they promise not to. So that's why we're doing those additional checks.
07:10
And we should also assume that other programmers will misinterpret our design.
07:15
And that's the reason why you put in those unique constraints that is just to protect yourself and other programmers that have to work with the database. Now. Bad data often makes the application appear as if it's misbehaving, and often a programmer gets sent in to try and make sense of what is occurring.
07:31
But there's not much that can be said about bad data that has made its way into the database.
07:35
Usually you just need to get rid of it, and you need to identify how that bad data even got into the database.
07:43
Now, with that said, go ahead and pause this video and see if you can come up with a solution to this complex procedure on your own. And the next few seconds if we're gonna go ahead and switch to the solution which I've already typed up and we'll walk through all the steps to complete the requirements here. So I hope to see you there.
08:03
All right, here we are at Thesis Alu Shin. That is kind of a large solution or a large procedure. But each component by itself is not that complicated. So we're going to step through all those different components that builds up to that solution. And I'm sure that each component will make perfect sense to you as we walk through this
08:20
now at the top were dropping the procedure if it exists, so that just allows us to run the script over and over without getting a object already exist. Air
08:30
we do. That delimit er swapped occurs in a lot of different places. Then we create a procedure with the name, so add employees position.
08:37
Then we indicate the parameters, as they were in the requirements document. We have a C 1234 end parameters and one out parameter. That's the line number. That's the out parameter.
08:50
And then we have an S P colon begin. SP is a label for the procedure, and that label allows us to use the leave command to leave the procedure. If we deem it's necessary now, when would we deem it's necessary? Well, if we run a test, for example,
09:07
on the position I D and we find that the position I d does not exist, it's not a valid position I d.
09:13
Then we may want to leave the procedure. In fact, that's what I do in the first. If statement and I will discuss that when we get down to it
09:20
on the next couple lines, I declare some variables for use in the procedure. I declare a test imager variable. That's gonna be my variable for holding my test results and checking to see
09:33
what my test results are.
09:35
I declare a reason with a bar chart three and defaulted to new.
09:41
Now that's going to be my reason for the record being added to the table. So it's new for default, but I could switch it to promotion later on in the procedure, if that's necessary.
09:52
Next had to declare a sequel. Underscore Air Tiny int variable for air handling. This allows me to define a continue handler and also defined some diagnostic information, and that is a little bit of a complicated, more complicated way of handling heirs. But it really helps during development
10:13
because if you use a continue handler
10:15
without this special air catching the you won't be able to see your air text that might be causing you problems. So this black of code really helps with development, and by itself it's not too bad. We're just using some standard sequel commands, which is get diagnostics Condition one at Civil State Return sequel State.
10:35
And then we set that full heir to the heir texts and we select it. And then we also have set sequel Air to True.
10:45
Now we don't need this part of it. This just helps us get diagnostics information while we're developing. So this procedure, after everything is said and done, would work just fine with just this portion of it.
10:58
But this helps for development, so I'm gonna leave that in there, and there's no reason to really take it out once its end.
11:07
And the way I found this is I just checked the my sequel docks as well as some stack overflow discussions on how to get air texts from an air or a continue handler.
11:18
And then on the next part, we started testing. The first test is if the position is valid. So the test is a selection of count, which is an aggregate function from the E P position, where the company I d equals the company I. D. Kraemer that was passed in, and the post position I d equals the position i d. That was passed in.
11:39
Then I checked the results. If the results are less than one, so zero, then I know that position idea is not valid, at which point I select that message position i d. The parameter that was passed in this is all contaminated together does not exist.
11:56
I set line number equal to negative one. That's a common thing that programmers do for exit code. Sometimes they put negative one to indicate an error occurred and then I leave SP, which leaves the procedure.
12:11
So that's one test done.
12:13
Now. If you didn't do that part, that's completely fine if you didn't set the line. Number two, you native, One that wasn't in the requirements documents, so
12:20
that would not you would not be expected to implement that.
12:24
But testing that the position I d is valid was a requirement. So you would want to be sure to have done this in some way. It doesn't necessarily have to be the way that I did it, but as long as you're confirming that a position that was passed in exist or not,
12:39
so then we move on to the next check, which is? We tested them. The employee exists, and we do almost exact same thing Onley with a different table and Onley with different the different parameter that was passed in for the employees. So from the e p A employees table, we again select a count.
12:58
And if that test ends up less than one,
13:01
we print out a message that that employee I d. Does not exist and we do the exact same thing that we did for the position.
13:09
And then we move on to the next test and we tested the position range. Does that class with a previous position. So here is where things start to get a little bit complicated. So the meat of this test is, of course, that selection statement.
13:24
So what are we doing here? I'm gonna go ahead and push
13:26
Put some spacing in here so it's just easier to read.
13:37
Okay,
13:41
so there's some brackets that are being used in this filter, which are very important. If I remove these brackets
13:48
that will break up this or statement into a
13:52
aim or explicit or which would not produce the correct results. So we are selecting account of the employee position where the company ID equals the company I d passed in employee I d equals employee I D passed in and the date we're using the date function
14:09
to remove any time component from the parameter passed in. We also do that to the various columns on the table as well. We just want to examine the date portion. We don't want to worry about time because time could produce incorrect results for us. So we checked at the start. They passed in is between the dates start date
14:28
and the end date on the table
14:30
or if the start date is between the end date and the start date on the table. So this second filter just switches these around
14:41
because it changes the way that the sequel engine actually looks at the dates. So did to do a full check between those dates. We actually need to implement it in this way.
14:56
And then, of course, if that test is greater than zero, that means we got a match in this test. And then we say that the start date occurs between another position and we leave the procedure,
15:09
so that one was a little bit more complicated. If you wanted to see how this was working, you could just
15:15
copy this out the selection statement
15:18
and then change the start date to, for example, a now
15:22
a now function call and which you just putting now on. Then you could see or test the type of results you're getting back
15:31
on. Then in the next test, we're going to test if again, we're doing, we're going to tested. The start date is before a position that already exist. This would not be a promotion. We want control edge cases in which the updated and end date is the same as the start date.
15:48
So we're thinking about this in terms of what happens when the programmer passes in strange data. What if a employee has a position that starts and ends on the same day Wolf employees
16:00
passes in a new position that starts on a
16:06
previous Positions end date? You know, we want to avoid those conditions.
16:11
So we select account from E P. Employee position, where company I d equals company I d. Employee I d equals employees D that was passed in, and the dates start date is less than or equal to the start date on the table or the dates start date is equal to the dates of traction
16:30
off the start day interval one day.
16:33
So if we're ending at previous position that has an open end date, that's no. We want to make sure we aren't setting this start date and that end date to the same date. And when we end that date, it's going to be one day in the past from that start date.
16:48
So we wanna make sure those dates don't collide
16:51
and these brackets right here are important. This would run if I remove these two brackets, but it wouldn't filter correctly and would produce the incorrect results.
17:02
So again we do. We check our test if it's greater than zero, which means we got one or more back.
17:07
We print out a concatenation message that says the start date occurs before another position start, date or the day starts as another position ends, and then we leave the procedure again.
17:21
So once we pass those tests, we then,
17:25
um,
17:26
do a test to figure out if it's a new position or promotion. And this one is much simpler. We select account from E P. Employee position, where company ID equals the parameters Company I d. An employee I d equals the parameter employee i d.
17:40
We started transaction.
17:42
Now we know it's a promotion if test is greater than zero, so one or more positions are exist,
17:48
at which point we'll set. The reason to Pro for promotion
17:53
will set the line number equal to a select Max line number from E. P. Employee position, where the company I d equals the company D parameter and employee. I d equals the employee I d parameter.
18:04
We then update the previous employees position.
18:08
We use a case statement to get Thea date of the now function, and we check if it's between the start date in the end date. If it is, then it's one. Otherwise it zero
18:21
The end date is the date sub or dates of traction of the date of the start date passed in interval one day. So we subtract one day from the start date passed in, and that's the previous positions and date
18:34
and then the last modified date. Time is the now function, and then our filter, of course, is where the employee I d. Is the employee i d parameter the line number is the line number.
18:48
Ah parameter. The company i d is the company I d parameter and and date is No, because if the predisposition has an end date, we don't want to update it.
18:59
And then for the insertion of a new record,
19:03
we have to insert all the required columns. We set the line number to the police off a selection off the line number plus one within the max function
19:12
from the E. P. A employees position where the
19:17
respective composite keys are equal to the parameter keys passed in.
19:22
We then use that line number in our insert statement,
19:26
and we also referenced some of the variables we've created. For example, we used a position I d parameter start date parameter.
19:33
The reason variable that was declared in the procedure
19:37
and also the
19:40
line number
19:44
and the is active. We do a date. Compare where we check the date of the now function is greater than or equal to the date of peace tart date. If it is, then it's one. Otherwise it's zero.
19:56
20:00
we, of course, have the hard coded values mentioned in the document as well. Those air pretty easy. There's no way that really get those wrong. Other than copy and pasting incorrectly.
20:10
We then do a check to see if there's an air if there's an air we rolled back. Otherwise we commit and we select a success message that prints out
20:19
Okay, so that's a pretty beefy procedure. And if I was dealing with this in production, I would probably want to break this out into another procedure. For example, I might take all the test case cases out and put those in their own procedure called test position. Add data,
20:37
and that would make this position or this procedure? A little bit cleaner,
20:41
but it is. Ah, it is still under 200 lines. So it's not.
20:47
It's not terrible, but it's starting to get up there to where you want to look at it, see if you're gonna break down, break it down even further. But that wasn't part of the requirements. Requirements was just to complete the indicated process. So once we've done that, we want to give this a test.
21:00
So you go to your e p employee position table. Go ahead and delete this one.
21:07
Save that. I'm gonna switch. This is active toe one. So again, this individual employee I D 28 91
21:17
he has only one record. He's the CFO.
21:21
He has a start reason of new is turning zero. Give it to the end date.
21:26
Make that no,
21:27
and save it.
21:30
Go back to our
21:33
procedure and I'm gonna go ahead and change this to something that doesn't exist,
21:37
such as nine.
21:38
Then we're gonna go ahead and run this whole thing
21:41
and bring up my results.
21:45
Employee. I d with the i. D nine does not exist.
21:49
I'm going to run that again.
21:52
Okay? Yeah, does not exist,
21:56
so let's bring him back that 28 91. Let's check. A position doesn't exist, so
22:00
it's to say Cat
22:04
Cat does not exist. OK,
22:07
changes CEO.
22:11
I have the date add function, but let's go ahead and make this little bit simpler. Just putting now
22:19
and let's select at line. You just see what's coming out of the procedure.
22:25
So let's go ahead and run this and see how this Ah, this works
22:32
says it was successful.
22:33
I have a second result for the selection, which says that Line number two was added, which makes sense. It would have been the second position
22:40
going and refresh this.
22:42
So the previous position is now is activist zero. The new position is one.
22:48
We closed out the other positions and date with the day previous, which would be 11 24 and the new star treasonous promotion with a start date of 11 25.
23:00
All right, so that looks all correct. Now let's go out and run it again. This actually should not work when we run it twice because it would Clayton create a date collision,
23:11
and it did, says the occurs before another position, start date or the date starts on another positions and
23:18
you can see how this procedure is working. And this completes the requirements. This code will, of course, be available for download. And I hope you were able to get working. I hope to see you in the next lesson. Thank you.

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