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
by hiding it behind a virtual table
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,
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,
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
unless someone gives you the skimmer and it's common to find out of bases where the skin has not provided
With That said, let's go ahead and get started. So I'm going to
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.
And I know I need the e p A employees rate table. So I'm going to grab that
and give it an alias of rate
on dhe. Given an alias of rate
and we don't want a Cartesian product here, wouldn't make sense.
And, uh, we're gonna make sure that they stay in the same company. We don't want a mangle are different companies together
on we need the V A C that be account idea
c equal the rate dot
And let's go ahead and select. It feels that we want
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.
So we want the effective date rate, that effective date,
and we want the rate dot hourly rates
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,
so let's go ahead and run that.
See what we get back,
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
in 2013 and 2014 2014 would actually be his current one.
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
we're going to group by
the account CD and the
Actually, we're just going to group by the account CD.
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.
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.
However, to take care of that, we are going to call the max function.
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
24 teams the largest. I will return that one.
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
for now, we're gonna use the max right on this
column, and we're gonna go ahead and push the hourly rate off the quarry so that we can see that this works
day. Effective date.
Let's go in and run that. We should see that drop down into one.
Oh, I forgot a keyword here. Guys. Forgot the by group by we need that was going on. Rerun it
so Sure enough, we saw,
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.
So we're going to select
from E P employees rate.
Where company I d is equal to rate that company I D
rate that are Keep it in the same order. Employee I d.
Is equal to rate that employee I D. And
effective date is equal to the max
of rate dot Effective date.
so let's go ahead and run that.
Oh, it looks like we forgot a comma here. Go ahead and put that in.
We get our results that this column looks really large because we didn't give it a name. Was given a name as current
You're on it. There we go.
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,
we call this sub query
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.
So let's go ahead and create a view for this.
We're gonna use the create
or replace statement because I like to use that so that I can continue working and just run it when I want to update.
We called an underscore hourly
was to stay current hourly
as I got to get those keywords in their Otherwise it will not work.
It's going to shrink this table here
and let's run the statement.
Okay. Says it worked. So let's left. Click on views, right? Click and then click, refresh,
there is the one we just created. Give that a double click.
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
the diagram for that view and the properties of that view
so you can see the source that creates it.
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,
That current hourly.
So now I can use this in
and its own queries and connected to other tables without having to worry about trying. Thio, come come up with this complicated query.
Where did we put that?
Whereas if we were going to take this data and go to
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
get even more complicated. But hide some of that complexity by making some of that complexity look like a virtual table.
this finishes our lesson on the create view statement. I hope you enjoyed it. I hope you got your view working, and, uh, I will see you in the next lesson. Thank you.