Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
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.
00:19
So the the table expression query chain that we're going to build is one that brings together managers and their current salary rates,
00:28
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.
00:39
So the first thing we're going to do is create a manager's table expression.
00:46
So with
00:50
managers
00:53
as
00:58
I'm going to select
01:00
the company, i d.
01:04
The supervisor i d
01:11
from
01:14
E P employees
01:19
where
01:23
Supervisor I d is not know or not empty.
01:27
We don't want empty supervisor ideas in that quarry,
01:30
and we're going to group by
01:34
the company I d
01:38
and the supervisor I d. So that we don't get duplicates. We just want one result for one supervisor
01:46
now Here's what. We're going to go a little further. We're going to
01:49
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.
02:00
So we're gonna say current
02:01
rates
02:04
as
02:07
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
02:16
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
02:25
if you're accidentally inserting them.
02:29
So we're going to select
02:31
company I d
02:36
employee I d.
02:45
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.
02:54
I'm going to select the max rate I d. Because we're gonna use a great group by statement again,
03:01
as when you use a function, you have to label it or give it an alias.
03:07
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
03:17
on. We want to like that from the E P employee right
03:27
on DDE.
03:28
We want a group by again
03:31
way. Want to group by
03:35
these columns?
03:37
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,
03:55
and employees can have multiple rates or multiple salaries, If they get raises or demotions
04:00
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
04:13
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.
04:24
Now you have to be sure. Like if you're just looking in the database,
04:29
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
04:39
for them, even though this auto in committee is going on
04:42
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
05:01
so I feel safe doing that. So we're going to go ahead and apply that here
05:04
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,
05:19
and that's in its own table expression. So let's add one more table expression called man
05:25
great and awful, but stands for manager rate in vote to say, as
05:31
as
05:33
brackets again.
05:38
I'm gonna say select.
05:40
I don't know what I want yet
05:42
and we're gonna say select from
05:46
managers. So we're using the table we establish or the temporary table we established, established above.
05:51
I'm gonna give that alias of man
05:55
on DA current rate And again, we're using a
06:00
a table expression we established above
06:04
great to see are given an alias of CR
06:08
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.
06:19
We're gonna call this one rate.
06:23
We don't want a Cartesian products we need to put in the table relationships. So where
06:28
Manda company I d
06:30
is equal to cr dot company i D
06:36
and
06:40
man dot supervisor
06:46
I d.
06:49
Is equal to see our
06:51
that the account I d
06:55
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,
07:08
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
07:17
is equal to rate that company I D
07:20
and
07:24
CR dot rate idee is equal to rate dot
07:30
Great idea.
07:31
And what do we want to select from that? Let's go ahead and get the cr dot company I d.
07:39
Now, of course, you could get the company i d from any of the tables. I just happened to select that one
07:44
cr dot the account i d
07:46
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.
07:59
Now the rate is being established
08:01
as
08:03
being related to the current rate above.
08:07
So by connecting the rate I d. We make sure that we get
08:09
the current rate, they gets established in this above table expression
08:16
So great
08:16
the annual salary
08:20
and rate that are really great.
08:24
Okay,
08:24
let's just go ahead and select from select everything from man rate and full just to make sure that everything is gonna work
08:31
from this, uh, this point,
08:33
it's a man. Rates careful.
08:37
I want to select everything. And let's go ahead and run That
08:43
put the cursor at the very top, just in case.
08:48
So you have an heir.
08:52
So forgot a keyword here. And it's
08:54
one you really shouldn't forget the from
08:56
Let's try that again.
08:58
Okay, that worked.
09:03
And let's go ahead and say that we want to attach some additional info.
09:07
So we need
09:09
the account table,
09:13
and we're gonna select
09:15
Well, we're gonna give these aliases to our I
09:20
on dhe d a.
09:24
Where are ie? That company i d
09:28
company idea is equal to p ay, that company I d
09:35
and all right that the account i d
09:41
spell it right is equal to
09:45
be a that,
09:46
uh, the account idea.
09:52
Let's go ahead and select
09:54
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.
10:07
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
10:26
three,
10:26
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.
10:35
We're creating a temporary current rate table, which is based on this query that comes from the employee rate table.
10:41
And we're doing group by statements, which makes sure that we get rid of duplicates.
10:48
And we're also making a man rate info,
10:50
which involves
10:52
the two above table expressions and the E P A employees rate table again.
11:00
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,
11:13
and
11:15
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
11:33
so that you deal with each problem by itself
11:35
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.

Up Next

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.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor