Time
9 hours 41 minutes
Difficulty
Intermediate
CEU/CPE
10

Video Transcription

00:00
Hi. Welcome to Module seven. This is less than 7.3 views in this lesson. We're going to review views now. Views are virtual named tables derived from a provided query.
00:12
Again, this is another object that promotes code reuse and avoids that copy paste programming.
00:18
Something to keep in mind with views is that data is not physically stored, and this can have an effect on performance. For example, when you query a regular physical table, that is not a view you most often will use the index's. How. However, when you query a
00:35
views and this can depend by database, engine indexes may not be used.
00:39
And in my sequel, their documentation indicates that view processing is not optimal, so extra care should be taken to avoid slow performance.
00:50
For example, views built from views of other views can slow down very quickly, and you should really think those out if you find yourself in that paradigm of trying to make views from views.
01:02
That said, let's go ahead and take a quick look in our development environment and implement of you.
01:07
I welcome back to the development for this lesson. I've switched back to the axiomatic a database. So let's create a view or a common query that a lot of the administrators might find themselves writing. Let's say we want to know the last 10 users that have logged into our application. How might we do that?
01:26
Well, if we take a look at the user's table in the axiomatic a database,
01:34
we'll see all these columns on, then one column that was interesting. That will let us accomplish that is the last log in date.
01:42
So these air common columns that you do find on user tables in databases last log in date is definitely one of the comment ones on. We can see users that have never logged in are represented by Knoll, and then we can see a date that represents the last time a user locked in. So how might we get the last 10
02:02
users that logged in?
02:04
Well, it's fairly simple. All we're gonna do a selected columns that were interested in, and then we're gonna order by last log in date descending and maybe limit 10 and let's go ahead and take that query and make it of you.
02:20
So select star from users
02:27
Go
02:29
and I'm gonna get user name,
02:32
full name,
02:36
last log in date.
02:44
Go ahead and, uh,
02:46
arrange. That's what's more readable,
02:51
you know, Let's get the company I d to.
03:00
And then we're gonna order by last long. In date
03:08
descending
03:09
it's going and run that
03:12
that's working the same. We only want the last 10 people limit 10
03:17
and there we go.
03:20
So this is a pretty simple query way don't have to only make simple queries a view. Of course, we could make a very complicated query view. And a lot of times that makes sense to do too abstract, some of that complexity. So how would we make this of you? Well, it's It's pretty easy. We could say creative you
03:38
last
03:39
10.
03:42
Logan's
03:44
keep it in. That s an extra time
03:46
as
03:49
and that's our view.
03:52
And if we go and look at our view
03:55
last
03:58
and really fresh
04:00
expand,
04:02
we can see our view automatically has calmed definitions as derived by the query. We look at the source, we can see the queries definition, which is very useful.
04:14
And then, of course, we can look at the data.
04:16
Okay, so that completes this section on views. Let's go ahead and head over to the summary.
04:21
And that brings us to the 7.3 summary, and this lesson reviewed the scope and implementation of views In the My sequel database engine.
04:30
We implemented a custom view using a simple user query to expose the last 10 Loggins of the application.
04:36
Remember that views by the My sequel Docks do not have optimized processing, and what this means is that extra care should be taken with views and performance.
04:46
For example, you would probably want to avoid using a view for some kind of high traffic processing, as if there was a need to optimize of you. It be very difficult to do because the my simple engine itself doesn't really support their optimization.
05:01
And that completes this lesson I hope to see in the next thank you.

Up Next

Intermediate SQL

This free course introduces the student to intermediate concepts found in the implementation and application of Structured Query Language (SQL) within professional business environments.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor