3.13 Advanced CTE
Join over 3 million cybersecurity professionals advancing their career
Sign up with
Required fields are marked with an *
Already have an account? Sign In »
7 hours 36 minutes
I welcome back to Module three sequel programming. This is less than four table expressions. Sub lesson 4.3 advanced. And this lesson. We're going to go a little further than we did in the previous lesson, with table expressions were going to use a few of them lined up to make our primary query easier to manage.
So the the table expression query chain that we're going to build is one that brings together managers and their current salary rates,
and we mess with that a little bit in a previous lesson. But in this lesson, we're going to accomplish a very similar goal using a different method. So let's go ahead and get started.
So the first thing we're going to do is create a manager's table expression.
I'm going to select
the company, i d.
The supervisor i d
E P employees
Supervisor I d is not know or not empty.
We don't want empty supervisor ideas in that quarry,
and we're going to group by
the company I d
and the supervisor I d. So that we don't get duplicates. We just want one result for one supervisor
now Here's what. We're going to go a little further. We're going to
typing a comma because we're essentially creating a list of table expressions which again are like temporary tables for use in the career that you're building.
So we're gonna say current
make sure you get those keywords and those brackets in their Otherwise it won't work correctly. And also the d beaver I d does not like spaces in between
statements. So while some I d s will let you get away with this, De Beber will not in many cases. So make sure to get rid of those extra spaces there
if you're accidentally inserting them.
So we're going to select
company I d
employee I d.
As we're gonna go ahead and title this be account so that the tables keep the same name and we don't have to juggle those at the bottom.
I'm going to select the max rate I d. Because we're gonna use a great group by statement again,
as when you use a function, you have to label it or give it an alias.
Otherwise it won't know what you want to title that column. It does not assume that you want to use the name that you are calling within the function
on. We want to like that from the E P employee right
We want a group by again
way. Want to group by
You don't have to include the alias in the group by statement. In fact, that won't work. You have to make sure you include the actual column name. So what this is going to do this is going to group by the company i D. And the employee i D. And then when there's a choice to be made in the next column over which is the rate I D. Because again,
and employees can have multiple rates or multiple salaries, If they get raises or demotions
or changes, they will select the highest rate I d. Now this will work in this instance because if we go and look at the employee rate table and we go to the properties, we could see
that the auto increment for the rate idea is true, which means when a new salary is added, it's automatically incriminated, which means the most. The newest salary will have the highest number.
Now you have to be sure. Like if you're just looking in the database,
you'll probably want to also investigate the front end interface to see what users conduce because in some interface is it might be possible
for them, even though this auto in committee is going on
to do a swap where they take a previous salary, give it a ladder date and then take the future salary and give it a previous state, which would then mess up this quarry because then the rate I D. That was highest for that. Employees would actually be a previous salary, but I know in this application that's not possible. And I know that the auto increments being used
so I feel safe doing that. So we're going to go ahead and apply that here
again. You'll just want to check the way your database slash application architecture is set up to, You know, make sure certain that these kind of assumptions air safe to make So we have the current rate,
and that's in its own table expression. So let's add one more table expression called man
great and awful, but stands for manager rate in vote to say, as
I'm gonna say select.
I don't know what I want yet
and we're gonna say select from
managers. So we're using the table we establish or the temporary table we established, established above.
I'm gonna give that alias of man
on DA current rate And again, we're using a
a table expression we established above
great to see are given an alias of CR
And then I'm going to connect to the e p A employees rate again. So this is another instance of the e p A employees rate and it's not mangled with the expression we used above.
We're gonna call this one rate.
We don't want a Cartesian products we need to put in the table relationships. So where
Manda company I d
is equal to cr dot company i D
man dot supervisor
Is equal to see our
that the account I d
and then tell a sense is not really working with these multiple table expressions, and that happens in a lot of different idea environments. They get, they lose, track off how they would predict what you want to type,
and that's just the something you have to get used to What? You're starting to get more complicated with your queries. See, our company idea
is equal to rate that company I D
CR dot rate idee is equal to rate dot
And what do we want to select from that? Let's go ahead and get the cr dot company I d.
Now, of course, you could get the company i d from any of the tables. I just happened to select that one
cr dot the account i d
I'm just going to use that because that's the same as the supervisor. I d However, the B account I d shows up in more places. So just for familiarity, I'm just gonna make that part of the table great, that effective date.
Now the rate is being established
being related to the current rate above.
So by connecting the rate I d. We make sure that we get
the current rate, they gets established in this above table expression
the annual salary
and rate that are really great.
let's just go ahead and select from select everything from man rate and full just to make sure that everything is gonna work
from this, uh, this point,
it's a man. Rates careful.
I want to select everything. And let's go ahead and run That
put the cursor at the very top, just in case.
So you have an heir.
So forgot a keyword here. And it's
one you really shouldn't forget the from
Let's try that again.
Okay, that worked.
And let's go ahead and say that we want to attach some additional info.
So we need
the account table,
and we're gonna select
Well, we're gonna give these aliases to our I
on dhe d a.
Where are ie? That company i d
company idea is equal to p ay, that company I d
and all right that the account i d
spell it right is equal to
be a that,
uh, the account idea.
Let's go ahead and select
are i dot star for everything from the r I table and then be a dot account CD on be a dot count name. Let's see what we have.
All right, this is exactly what I expected to see. This quarry is working the way I intended it to see, So let's go ahead and walk through this one more time we have a little more going on here than we do in previous lessons. Okay, so we have multiple table expressions. So how many table expressions we have? We have one too
so we're creating. It's a temporary table that's known as managers based on this quarry from the E. P. A Employees table.
We're creating a temporary current rate table, which is based on this query that comes from the employee rate table.
And we're doing group by statements, which makes sure that we get rid of duplicates.
And we're also making a man rate info,
the two above table expressions and the E P A employees rate table again.
And finally we bring those together with the one table expression that combines the two above, and we connect to the B account table again to get the manager info,
that brings this lesson to a close. I hope you got your table expressions working, and I hope that you, ah, see that kind of flexibility and the power that comes with table expressions. You can break down your problem into certain ideas and concepts and then tie them together at the bottom
so that you deal with each problem by itself
and finally solve perhaps the main problem at the bottom. Anyways, I hope you enjoy this lesson and I hope to see in the next one. Thank you.