Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

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
08:46
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.

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