3.11 Create View

00:00

Hi. Welcome back to Model Three sequel programming. This is less than three views sub less than 3.3. Creative You. We were exposed to the Creator and delete view statements briefly in the previous lesson and this lesson. We're going to reinforce those concepts and creative you the abstracts, a complicated query

00:18

by hiding it behind a virtual table

00:21

and the query we're going to build is one that you find commonly out there. It's going to show current employees with their current hourly rates of pay,

00:30

and to accomplish that, we need to tables. We need the be account table and the e P A employees rate table. Now I know that because of my familiarity with the database, there isn't really a constraint that would show you that relationship. And that is,

00:46

You know, one of the weaknesses of not having a database with all the constraints in it is that you are left to have to explore and find the relationships yourself

00:55

unless someone gives you the skimmer and it's common to find out of bases where the skin has not provided

01:00

With That said, let's go ahead and get started. So I'm going to

01:04

start with a select I don't know what I want yet, so we'll drop down to the from I know I need to be account table. So I'm gonna start with that and give it an alias of B A. C.

01:15

And I know I need the e p A employees rate table. So I'm going to grab that

01:21

and give it an alias of rate

01:23

on dhe. Given an alias of rate

01:26

and we don't want a Cartesian product here, wouldn't make sense.

01:32

And, uh, we're gonna make sure that they stay in the same company. We don't want a mangle are different companies together

01:42

on we need the V A C that be account idea

01:47

c equal the rate dot

01:49

employees idea.

01:53

And let's go ahead and select. It feels that we want

01:56

I know I want

01:57

the, uh, count CD, which is really the front end key that users of the application see the primary key, which is the B account I d, is pretty much hidden from them. They that front end users of the application aren't really aware that that's there. But when you're working with the database, you typically always want to use the B account idea.

02:19

So we want the effective date rate, that effective date,

02:24

and we want the rate dot hourly rates

02:29

because we could try to get annual salary. But hourly employees will still show a no for that value, we will get an hourly rate for everyone, however,

02:38

so let's go ahead and run that.

02:40

See what we get back,

02:43

okay? It looks like we're getting some good results. However, we only want the current. We're getting multiples for the highlighted employee. For example, he has three hourly rates. It looks like he got a raise

02:55

in 2013 and 2014 2014 would actually be his current one.

03:00

So let's go ahead and use the group by statement. Now, this is a new statement, but let me show you how it works. It's not too bad

03:08

we're going to group by

03:10

the account CD and the

03:14

Actually, we're just going to group by the account CD.

03:16

So what this is going to do is it's going to get to this record is going to say OK, I have the Count CD. I have this road duplicated three times because they are the same value. I will combine them into one record.

03:30

So that takes care of this column. The account CD column. We cannot do that on the effective date because we have different values there.

03:39

However, to take care of that, we are going to call the max function.

03:46

So when the max function reaches this results set again, it's going to say, OK, I have three different results here. Which one do I want? Well, the command is for the max, so I'm going to return the largest off three, so it's going to be okay. I got 2012. I got 2013 2014

04:02

24 teams the largest. I will return that one.

04:05

Now you beat. You might be tempted to do that with the hourly rate, which you cannot necessarily do that because some employees might not get a raise. They might be commission based, and perhaps they lose a lot of business one year, and they actually have a rate that goes down. So we're going to use a different trick for them. But

04:24

for now, we're gonna use the max right on this

04:26

column, and we're gonna go ahead and push the hourly rate off the quarry so that we can see that this works

04:31

as current

04:33

day. Effective date.

04:38

Let's go in and run that. We should see that drop down into one.

04:45

Oh, I forgot a keyword here. Guys. Forgot the by group by we need that was going on. Rerun it

04:50

so Sure enough, we saw,

04:54

uh, the results to be exactly what we expected. Got the 2014 back for the record we were looking at. Now let's take care of the hourly rate we go. We're going to use a sub query for that.

05:05

So we're going to select

05:10

hourly

05:13

raids

05:17

from E P employees rate.

05:24

Where company I d is equal to rate that company I D

05:29

and

05:31

rate that are Keep it in the same order. Employee I d.

05:36

Is equal to rate that employee I D. And

05:44

effective date is equal to the max

05:49

of rate dot Effective date.

05:54

Okay,

05:57

so let's go ahead and run that.

05:59

Oh, it looks like we forgot a comma here. Go ahead and put that in.

06:04

Rerun it.

06:06

We get our results that this column looks really large because we didn't give it a name. Was given a name as current

06:13

hourly.

06:15

You're on it. There we go.

06:18

All right. So the way this sub query works again is that we are going record by record. And when we get to each record,

06:28

we call this sub query

06:30

when we pass in with the currents record company ideas what its current employee ideas and what the current max effective date of the returned results that is given us the expected results.

06:43

So let's go ahead and create a view for this.

06:45

We're gonna use the create

06:47

or replace statement because I like to use that so that I can continue working and just run it when I want to update.

06:55

We called an underscore hourly

06:59

was to stay current hourly

07:03

as I got to get those keywords in their Otherwise it will not work.

07:08

It's going to shrink this table here

07:14

and let's run the statement.

07:17

Okay. Says it worked. So let's left. Click on views, right? Click and then click, refresh,

07:25

expand that out

07:28

there is the one we just created. Give that a double click.

07:31

Um, depending on what you were looking at, these tabs could open in a different order. So this is the data tab showing us the data that's in that

07:39

view currently,

07:41

the diagram for that view and the properties of that view

07:46

so you can see the source that creates it.

07:48

That's kind of ugly quarry, but we've made it simple to deal with in the future because we could just select from this table. So if we say new sequel editor,

07:58

I can say Select

08:00

star from

08:01

Select Star from

08:05

E. M. P.

08:07

That current hourly.

08:11

So now I can use this in

08:13

and its own queries and connected to other tables without having to worry about trying. Thio, come come up with this complicated query.

08:20

Where did we put that?

08:22

Whereas if we were going to take this data and go to

08:26

a whole nother set of tables with it and we had to integrate this query that be complicated to look at just by itself Now we can just take this view connected to some other tables and potentially

08:37

get even more complicated. But hide some of that complexity by making some of that complexity look like a virtual table.

08:45

So