Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
Hi. Welcome to Module three. Sequel programming were at Lesson four. Table expressions were in sub lesson 4.2. Introduction to table expressions on We're going to hit the ground running with this. This first lesson is gonna be a pretty short sweet to the point. We're going to take a look at a simple table expression,
00:19
and the first question you have is probably, well, what is a table expression?
00:24
Well, the an easy way to look at table expressions is that they are temporary tables or temporary reviews that exists for the duration of your quarry Run. So you create them to use an inquiry that you're writing. And then when you execute that query, they're there
00:42
for that brief period, period of time
00:45
in system memory that your query is actually running. So it's like a temporary view. So it might be a data set
00:51
that is close to being a view, but you don't really want to make it of you. You just have to use it in this query. It's gonna make your query easier to work with, so you're just using it in your current query, and you don't really intend or plan to use it elsewhere.
01:06
So let's go ahead and get started. I think if I show you what I'm talking about, it will be very easy to understand. So we're going to use a table expression in our career e to create a temporary table that is just the manager's off the company.
01:22
So let's go ahead and get started. We're only going to need one table
01:26
to create this table expression and the table expression will result in a table that contains Onley our managers.
01:33
So let's go ahead and get started.
01:34
So to start with the table expression, you start with the
01:38
with keyword
01:41
with the table expressions name. So we're gonna call it managers
01:45
and then as we see that key word a lot and we're gonna put the brackets in there
01:53
and here's where we're going to create our temporary table or our table expression. So we're gonna select.
02:00
I don't know what I want yet, so I'm gonna drop drop down to the from statement. So from
02:06
E p employees,
02:12
where
02:15
supervisor I d is not? No,
02:20
because we don't want to see the results of, um,
02:24
employees that do not have a supervisor and we know the president of this company doesn't have one, so that would return a No. So we don't want that.
02:34
Um, let's see, What do we want? We want the company I d.
02:39
And we want the supervisor, I d.
02:44
And right now we would get a lot of duplicates because a supervisor could have many subordinates. So for all those subordinates, we would see his record or his super supervisor I d replicated. We don't want to see that. So we're going to use a statement you were exposed to in a previous lesson. The group by statement.
03:02
Now what is the group by statement do again?
03:05
Well, when it gets to a result set where the group by columns are repeating, it's going to say, OK, these values are all the same. I'm gonna compress them into one rope
03:17
and get rid of that duplication, and that's exactly what we want. So we're gonna group by company I D supervisor I D, which will give us a unique data set now, another way that you could do the same thing, but it is slightly less efficient
03:36
is to just use the distinct keyword,
03:38
which basically says the same thing and says,
03:42
Hey, for these records, anything that is not unique, go ahead and discarded. I want only unique records. I don't want duplicated records, but
03:51
we're going to use the group by statement because it's good to get used to using that as it has many more uses,
03:58
then just making a distinct results set.
04:01
So group by
04:05
company I d. Supervisor idea. So this is our temporary table.
04:12
So how do we use this? What? We're simple. We're going to select
04:15
star from managers.
04:19
Okay, So managers
04:23
is a table or a temporary table or a table expression created by this query and given this name. So it's similar to a view that you create on the fly and then select from
04:34
So let's go out and run this and see what we get.
04:38
This is exactly what I expected to get. So we have company, too, and we have all the managers. So we have created a table expression that generates
04:48
a manager's temporary table
04:51
from the following quarry, and then we're able to select from it.
04:56
Now you might be asking yourself Well, okay, great. We have this temporary table and were selected from an awesome. Can we do everything that we can do with a regular table with it? Can we add other tables and join to it? And yes, you can. You can use it just like you would a regular table.
05:13
So what this allows you to do is again abstract some complexity out of your primary problem.
05:19
So
05:20
if I was facing a problem where I just needed the managers But I didn't wanna have to complicate complicate my query
05:29
with unnecessary bloat
05:30
to select the managers, I could use a table expression, name it the managers, and then use that as a table
05:38
in my primary query of interest that completes this lesson. I hope you enjoyed it. I hope you got your table expression working. And 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