7 hours 36 minutes
Hi. Welcome back Tomorrow, Joe. Three sequel programming. This is lesson to queries. This is the first sub lesson 2.2 sub queries in this lesson. I'm gonna try to keep the video short, but I am going to go over to uses up sub queries to help drive what they are home.
So a sub query or ness inquiry is just an additional quarry that is contained within a primary query. And for this
example, we're gonna focus on two tables, the users and the E P A employees table.
So if we go to E p employees table
When we viewed the diagram for that,
we will see that it mentions a field called User I. D.
So what we're going to do is we're going to get the user name into the results set of the E p employees query using a sub query. So let's go ahead and start up the new sequel editor
gonna select. I don't know what I want. I'm gonna leave a blank from e p employees.
Where and company
I d seems to be too seems to be primary. So we're gonna stick with that company we're gonna say company I d equals two.
And what do I want? I want to be account. I d
actually, I want the company. Andy starting first company. I d
be account idea
user. I d
on dhe. We're gonna be dealing with multiple table, so it's a decent idea to start using aliases for the table. So I'm going to this an alias of em
on. Then I'm gonna
but emp dot in front of every field call
dot Okay, there we go. Let's go in and run that. Make sure that works,
all right? Got the results back here and let's go ahead and bring in the sub chri.
That's some crazy. Don't forget your common here. We need that comma. Otherwise it will fail. This is going to become an additional field in the results set.
So we're going to do select user name
company I d
equals e m p dot company idea.
And the intelligence was losing tracts of that. It doesn't know which query I want the intelligence floor, which is fine. That happens in a lot of editors
on p k I d
and dot user, I d
now the thing that be aware of when riding this out, especially when the intelligence is starting to fill. You want to make sure that you get the field names exactly right. The table name is exactly right. And the casing exactly right. For example, if you put a lower case you here on accident, it would not run,
and you would need to go and look at that table's definition to figure out if you had typed any field names incorrectly. And if you really need it to, you would actually type this quarry off in its own sequel editor and then copy and paste it back into this quarry so that, um,
you could use the intelligence over and the other sequel editor to make sure
for, to help make sure you're getting all the field names correctly.
So we're supposed to get one result back because it doesn't make sense to put multiple column
or multiple records into a result set here. That was, for example, if we had a column user name and this user I d returned three results, it would not make sense within sequel to put those three results
into this one results that
and sequel will throw in air and will not like that. So to make sure that doesn't happen, we're gonna go ahead and put limit one on the end
now. It shouldn't occur anyway. But just in case we'll make sure that our quarry doesn't crash.
Let's go ahead and run that and make sure that works.
And it appears that we have a problem.
Let's see if we can debug this and find it really quickly.
Oh, I see the problem. We need an an statement right there. And
so let's go ahead and rerun that.
Sure enough, we got the username. Let's go to give that column a name, those as user name.
There we go.
Now we got a nice additional calm
associated with their employees at data
for the user's user name.
Now let's see
now, what's another way to run a sub query? Well, we could limit the results set based on the results in a subway. So how would we do that? Let's go ahead and put that company I D requirement back in
because it makes me feel safer
user i d. Let's say we wanted employees,
but we only one of the ones
that had a user i d So the way we would do that So you say would say where
I would say and emp dot
user I d
and again, I'm gonna put these brackets here
and we're going to
with this quarry
but we're going to change it a little bit. We're not gonna limit one.
And the only additional requirement is that the company I d matches what's in here. So what? This is going to dio select the user names which will return no results that won't match. We need the p k i. D.
now we have to sub queries. The first of query is getting the user name.
This one is making sure
that the user i d and the EMP table
is contained within this sub query.
Now, if we go on drug run this sub quarry in its own editor,
we can see what results it will get back.
it would have to change this because we don't have that table anymore. So we'll say to we know that's what's gonna get used.
It's going to turn on a list of the P K I D s exists in that table.
So we're requiring that the employees user I d exists in this result set when we run that sub cree over here,
we should see the exact same set come back because all of these guys have user names, which means they are all users.
So let's go ahead and run this and ensure that we get what we expect back, which will be 45 records.
And we got 45 records back.
And ah, that makes sense. Based on the current data within the database, if there were employees that didn't have a user name,
we would have saw them drop off this query because that would have been a requirement with this submarine.
that closes this lesson on some quarries and again to recap A. Some quarry is just an additional quarry contained within a primary quarry. And this example we did two examples. We had a sub query Generate a feel an extra field for us, which was the user name based on the user I d
being applied to the user's table from the employee table.
And then we also limited the results that too
users p k I d
from the user's table.
Now a lot of these quarries could have been rewritten as relationship relationships joints between the tables. But depending on the situation, sometimes it's much easier to write a sub query than two.
Ah performed their relationship join, and sometimes it makes a lot more sense to do the relationship joint, as that is typically the more efficient way of relating data to each other. And that brings this lesson to a close. I hope you enjoyed it. Hope you got your sub quarries working. And I will see you in the next lesson. Thank you.
Penetration Testing and Ethical Hacking
To assess the strength of your organization’s cybersecurity posture, you need to gather information, perform ...
7 CEU/CPE Hours Available
Certificate of Completion Offered
Ready to dive into SQL queries, aggregations, events, and database schemas? In our Intermediate SQL ...
10 CEU/CPE Hours Available
Certificate of Completion Offered