### Introduction to SQL

Course
Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

### Video Transcription

00:00
I welcome back to Mom's a Force equal programming to we're in the last subsection of lesson one sequel functions. This is we're gonna finish up our name porous function.
00:10
In the previous lesson, we parsed out the last name and this lesson we're going to integrate the pursing of the first name from the string. Now, a couple things to keep in mind with this function is that this function works specifically
00:25
against a certain format. So if the format of the name changed, of course, it would not produce the correct results. For example, if for some reason somebody passed in a name that was
00:37
last name Dash first name, it would not function correctly because it's looking for commas
00:43
and not dashes. So that's something to keep in mind when working with this type of functions that you've developed to deal with a specific problem, because occasionally you'll run into instances where somebody has decided
00:56
to change the ah, the pattern and then your functions that would chase that pattern no longer work correctly. With that said, let's go ahead and get started and finished this function up.
01:11
So if we look back at the query, we had,
01:15
ah couple things we had to do to get the first name.
01:18
Um, first we would get the sub string.
01:22
Then we would send to the ah layer below.
01:26
And then we also went ahead and grab the position
01:29
and sent that to the layer below.
01:30
We measured the position. If it was less than one,
01:34
then we just returned the first name.
01:38
Else we returned a sub strain
01:41
of the Ah
01:42
first piece,
01:46
starting at position one to the position of the location of the Kama.
01:52
So
01:53
let's go ahead and go into our function, and it's quite normal to have to ah, you know, move things around a bit and see where you have to
02:00
go at it, sort of speak to get the results that you want. So it's normal to like, you know, type in a command that you believe will work and then find out. You need to adjust it a little bit to get it
02:13
to, uh,
02:14
work the way you want it to work with. That said, Let's go ahead and start typing where we need to do now. I say, if
02:21
choice
02:23
equal first
02:25
we need that in quotes is the being quotes. That's how we indicate a string here.
02:30
Then
02:32
I'm gonna go ahead and close this one off with an end.
02:36
Yes.
02:38
Um, okay, so
02:40
we need
02:45
we need The first thing we're gonna do is set the sub string to this first piece. So we're gonna copy this.
02:53
I'm going to say we're gonna set string.
02:57
So the string comes in, we're gonna go ahead and mutate it
03:00
or more if it or change it
03:04
to save. Ah, variable space. So we have a variable there. We don't need to save its original form.
03:09
We're gonna go ahead and use it, and ah, said it and updated as we work through it
03:17
on dhe something. Keep take note. Here. You have to use the key words set
03:22
the variable name
03:23
and then also
03:25
the equal symbols.
03:28
A lot of languages. You would just see this. But here in this environment,
03:34
we need that set. Keyword.
03:36
All right. And next,
03:38
Next, we need the ah position.
03:42
So we're gonna go ahead and copy this.
03:44
Now, we actually have to declare variable for this because you can't just save it in this string.
03:49
We're gonna go back to the top of the function. These are required to be at the top.
03:53
Say, declare
03:55
location,
03:58
Aunt.
04:00
So that's how we declare variable. We used the key word declare the name of the variable and the type of the variable. We need an end to hold the location.
04:10
So
04:11
well, say set and
04:14
or not, Aunt. Sorry. Sorry. Said location.
04:18
Now, you could all roll this up until one function chain and say variable, but we're gonna spread it out because it's easier to read. And it's easier to digest, especially when you're new to this stuff.
04:30
And it doesn't waste too much off memory. Resource is anyway.
04:34
Okay, so now we got the string in the location. However,
04:40
we need to switch these account names
04:44
with the words string
04:46
because account name is not defined as anything in this function. So this function will not know what we're talking about. If we use the word account, name
04:55
String is because it comes in as a parameter,
04:59
so it will know exactly what we're talking about.
05:00
Okay, so here
05:02
we set this string
05:04
equal to
05:06
this computation, which is ah, finding the location of a space and getting the sub string of that.
05:15
And then we gain the location of that.
05:16
Bye again. Looking for a comma
05:19
on getting a sub string.
05:23
I'm doing the same computation we did up here
05:27
so we can actually get rid of this, cause it's the same thing.
05:32
We can just look at the string.
05:36
Right? So next What did we D'oh?
05:41
We dropped down a layer.
05:43
We analyzed the position. If it was less than one, we just returned what we had.
05:47
So let's do that. We're gonna need another if statement. So this is a nested If
05:51
if
05:53
location
05:55
less than one.
05:58
We need that key word, then
06:00
return
06:02
string.
06:04
Okay.
06:05
It'll be our computer to string. If not,
06:09
we just need an l statement,
06:12
and then we're gonna return.
06:15
What are we gonna return?
06:17
We need to return this
06:23
return.
06:27
And we need to replace that first piece name with a string
06:30
was again and won't know what that is. And we change the name of this from position to location
06:38
on. We need a semicolon there. Don't forget your semicolons also will not work. We need to end this if now that we be careful with your embedded ifs And here because you have to explicitly end them.
06:51
So we need to end ifs. We haven't if starting there and enough starting there
06:57
Hey
06:58
and I will leave. This should work. Let's go ahead and run this.
07:02
We close up these extra spaces here because I know Deaver doesn't like them. Sometimes
07:08
on Dhe Run
07:12
Stop.
07:13
We'll need to see Michael in there after the end. If
07:16
and it ran.
07:18
Now let's go give it a test,
07:21
so I already have it laid out. What? All you need to do is add the parson aim again with a call to the first for choice
07:30
and again passing the account name and I a listed as first thing.
07:35
Let's go ahead and run that,
07:43
and it looks like it produced the same results. We were able to parse this account name and break it up into the last name and the first name
07:51
successfully.
07:54
So there's your introduction to functions that we created a
07:58
specialized function for the axiomatic, a database that could parse their name, field and break it apart into the first name and last name.
08:07
Um, because if you, for some reason needed to, you know, regularly query that ours you really don't want this
08:16
or this in all your queries where you're just trying to break the name apart because that that adds unnecessary complexity to a simple idea, which is, Hey, I just want the names. I don't want to be worried about this stuff, so it's when you have something like that that's coming up repeatedly. It's a good idea to move it into a function
08:35
so that you could just call that function.
08:37
And this is much easier to read and digest.
08:41
You can look at it, tell what it's doing. Parse name. Okay,
08:46
Obviously it's got something to with the name. There's an argument called first.
08:50
I without even look at them function. I would be pretty confident, saying This is getting the first name
08:56
out of this string,
08:58
How it's doing that I'm not concerned with that. I'm just gonna trust that it works
09:05
and, of course, report any problems that I see with it
09:09
now. Something keeping in mind with function is they're very. They have a lot of utility. You can put sub quarries in them.
09:16
You can return tables of data. You can do a lot of interesting things with functions now something
09:22
to Ah, keep in mind when working with them is that you can make them inefficient, just like you can with the views. You could do something that's very, very taxing on the system
09:35
and make a query. Take a very long time to finish. So that is something to keep in mind when working with functions and typically, functions that have slow resolution times are dealing with a lot of sub queries. It's not a great idea
09:50
to put some queries or even our a lot of sub quarries and a function.
09:54
Um, however, sometimes it's it ends up being, Ah, you know, the best thing
10:01
to fix the problem and sometimes that's what you need to do. But it's just something to be thinking about when developing these type of solutions
10:07
anyways, that completes the function lesson. I hope you enjoyed it, and I will see you in the next lesson. Thank you.

### Introduction to SQL

This introductory SQL training teaches SQL core concepts that can be applied in professional environments. Once students complete this course, they will be able to query and interact with an SQL database, and know how to design database schemas.

Kitt Parker
Instructor