Time
9 hours 41 minutes
Difficulty
Intermediate
CEU/CPE
10

Video Transcription

00:00
I welcome back to module six. This is less than 6.4 common table expressions or C t e for short.
00:07
In this lesson, we're going to review common table expressions. So let's go ahead and go over these bullet points.
00:13
They improve query organization and complex queries. So in complex queries, when you don't have access to a common table expression, let's say you're in my sequel five. For example. You are typically gonna handle these types of situations with a Dr table or sub quarry, and some other situations will be much more complicated to deal with
00:33
because of this.
00:34
So with the introduction of table expresses, expressions regain access to a temporary name result except all of the major table crowd functions. So when you generate a table expression, you can perform inserts, updates, deletes and selections against them,
00:49
which is very useful in some situations.
00:52
So to move onto the second bullet point, they're much more suitable in some situations when compared to derive tables. That's because of those crowd functions, and a lot of the time they're easier to read. I myself much prefer to use a common table expression than a bunch of Dr Tables by a sub choirs. Now, with that said, let's move into the development environment.
01:11
Take a look at some of these
01:14
Hi, Welcome back to the development environment. Let's go ahead and get started with coating some of these table expressions and see what they are. Review what they look like.
01:23
So we're going to start with the table expression code words with
01:27
I'm gonna say C t e
01:30
as.
01:32
Let's make sure of that syntax, right? So just, say, select
01:38
test
01:40
as test
01:42
And then down here we will perform a select star from C T.
01:49
That's good and run that
01:53
and that's working. So I got the Syntex right there. That's good.
01:57
I think it's a little different. And Microsoft sequel.
02:00
Or maybe it was DB two. Anyways, this is how you do it in my sequel.
02:06
So what do we want to dio? Let's let's implement a couple of these derived tables that we use in the previous lesson as common table expressions and see what they look like. So let's get the teachers
02:22
select Star from
02:24
user, of course, is you see
02:28
join you user types you t
02:31
on
02:34
ut that I d equals u c dot user type i d.
02:39
Let's change the name of the CTE to teachers
02:46
or teacher courses.
02:51
I go,
02:55
we need that filter in there. So where
02:59
UT that title equals teacher
03:05
Onda se you see dot
03:07
school I d
03:09
you see that?
03:12
Of course I d And you see that,
03:15
uh, user I d as a teacher, I d
03:21
It's going and run that, but we're going to need to change this selection from
03:27
teacher courses.
03:30
Okay.
03:31
All right. So we have a table expression.
03:36
So we have defined what? This table is up here, and then we're selecting from it down here. Now, if we weren't going to do it this way, we'd use a derived expression like we did in the previous lessons, and that would look like,
03:51
um, like this,
03:58
which is gonna little very familiar.
04:01
This should still work.
04:03
Yep.
04:04
Now, the reason I prefer the table expression is because I like to keep
04:10
these parts separated.
04:13
So I know I've selected from a table object here, and if I'm interested in what this is, I can just look at my table expressions say OK, that's what teacher courses is.
04:23
Where is when you have a bunch of derived queries embedded in a complex
04:28
query like we did in the previous lesson. It could get very confusing toe look at aunt to read, and table expressions make it easier to read. Now table suppressions can get out of control, and you can start to chain a bunch of those together and make them confusing as well. But in general, they remain easier to read when compared to the derived table equivalent.
04:48
Okay, so let's add another table expression. So we need to do is Atacama and say,
04:54
Let's say student course, right student
04:58
courses
05:00
as
05:05
and we're going to select Star. Actually, we're gonna just copy this from above.
05:16
We're gonna change the title to students.
05:21
Andi, we're going to change this to
05:26
student I d. And we're gonna fix the spelling on this one.
05:30
It's gonna run that
05:32
now. We don't have to use the table expression. So if I run this, it still runs. And I have done nothing with this table expression.
05:41
But let's say I want to connect it to Well, the night will just join them like regular tables, so join
05:49
join student courses. Unfortunately, the intelligence on
05:57
every idea I've ever tried gets lost. When you start working with table expressions or derived tables
06:03
TC
06:08
s t
06:10
on sc that school I d
06:14
equal
06:15
t see that school I D
06:17
and
06:21
seed? Of course I d
06:28
equal
06:29
t see that course I d
06:34
Let's go ahead and make sure that's working now. If I try to run that by itself, it's not gonna work again and air.
06:41
And that's because this whole script tests to run together. I run this by itself. This part has not been run, so it has the program or the sequel. Engine has no idea what teacher courses or student courses is. So control a and run them all together.
06:59
So here we have a course 20 with Teacher
07:01
Idea 51
07:03
and soon idea four to. So again, we know that teacher 51 taught student 42
07:10
in course 20.
07:12
So let's clean this up a little bit.
07:15
Duty CDA School I d.
07:18
You see that teacher I d?
07:24
And then
07:26
as he that
07:29
student idea.
07:32
Make sure this runs
07:35
and let's put the course in there, too.
07:40
It's a TC that course I d.
07:44
Let's give that a run
07:46
So we got a course 20. Teacher, 51 taught. Teach student 42. All right,
07:51
that's all great. But what if we want to make this a table expression? Well, that wouldn't be hard.
07:58
So we can say comma.
08:00
Well, say, um,
08:01
teacher
08:03
student
08:07
horses
08:09
has
08:13
so you can chain these together
08:18
and use the previous table expressions.
08:22
So now, at the bottom. If I just do a select star
08:26
from teacher student courses
08:33
and I, of course, spell it right
08:37
with the exact same results. And you think this is a great example of table expressions? Were we've created a teacher course table expression, a student course
08:48
table, expression. Then we connected those two into an additional third table expression and then at the very bottom, were selecting from that final table expression. Although we don't have to use the last table expression we could select from the 1st 1 the 2nd 1 or the 3rd 1 or either one that we want to.
09:05
So that completes this section on table expressions. So let's go ahead and head back over to the summary section,
09:15
and that brings us to the 64 summary. So what do we do in this lesson, we reviewed table expressions. We took a look at their definition and their implementation. We change three table expressions together in the development environment and is a reminder CTS Permit the following operations select, insert, update or delete.
09:35
So if you change a bunch of them together, you can select from the 1st 2nd 3rd or the last one. How many ever that you have. And they help with segregating your area of concerns in your primary query.
09:46
So that completes this lesson, and 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