3.13 Advanced CTE

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