3.12 Table Expressions
Join over 3 million cybersecurity professionals advancing their career
Sign up with
Required fields are marked with an *
Already have an account? Sign In »
7 hours 36 minutes
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,
and the first question you have is probably, well, what is a table expression?
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
for that brief period, period of time
in system memory that your query is actually running. So it's like a temporary view. So it might be a data set
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.
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.
So let's go ahead and get started. We're only going to need one table
to create this table expression and the table expression will result in a table that contains Onley our managers.
So let's go ahead and get started.
So to start with the table expression, you start with the
with the table expressions name. So we're gonna call it managers
and then as we see that key word a lot and we're gonna put the brackets in there
and here's where we're going to create our temporary table or our table expression. So we're gonna select.
I don't know what I want yet, so I'm gonna drop drop down to the from statement. So from
E p employees,
supervisor I d is not? No,
because we don't want to see the results of, um,
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.
Um, let's see, What do we want? We want the company I d.
And we want the supervisor, I d.
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.
Now what is the group by statement do again?
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
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
is to just use the distinct keyword,
which basically says the same thing and says,
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
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,
then just making a distinct results set.
So group by
company I d. Supervisor idea. So this is our temporary table.
So how do we use this? What? We're simple. We're going to select
star from managers.
Okay, So managers
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
So let's go out and run this and see what we get.
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
a manager's temporary table
from the following quarry, and then we're able to select from it.
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.
So what this allows you to do is again abstract some complexity out of your primary problem.
if I was facing a problem where I just needed the managers But I didn't wanna have to complicate complicate my query
with unnecessary bloat
to select the managers, I could use a table expression, name it the managers, and then use that as a table
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.