Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
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.
00:16
So a sub query or ness inquiry is just an additional quarry that is contained within a primary query. And for this
00:25
example, we're gonna focus on two tables, the users and the E P A employees table.
00:29
So if we go to E p employees table
00:33
When we viewed the diagram for that,
00:36
we will see that it mentions a field called User I. D.
00:42
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
00:53
gonna select. I don't know what I want. I'm gonna leave a blank from e p employees.
01:00
Where and company
01:03
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.
01:10
And what do I want? I want to be account. I d
01:12
And
01:15
actually, I want the company. Andy starting first company. I d
01:22
be account idea
01:23
user. I d
01:26
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
01:34
on. Then I'm gonna
01:37
but emp dot in front of every field call
01:40
dot
01:44
doc
01:46
dot Okay, there we go. Let's go in and run that. Make sure that works,
01:53
all right? Got the results back here and let's go ahead and bring in the sub chri.
01:57
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.
02:06
So we're going to do select user name
02:12
from users
02:16
where
02:21
company I d
02:22
equals e m p dot company idea.
02:27
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
02:37
on p k I d
02:38
equals
02:40
and dot user, I d
02:43
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,
03:00
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,
03:16
you could use the intelligence over and the other sequel editor to make sure
03:20
for, to help make sure you're getting all the field names correctly.
03:24
So we're supposed to get one result back because it doesn't make sense to put multiple column
03:30
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
03:46
into this one results that
03:47
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
03:57
now. It shouldn't occur anyway. But just in case we'll make sure that our quarry doesn't crash.
04:03
Let's go ahead and run that and make sure that works.
04:08
And it appears that we have a problem.
04:12
Let's see if we can debug this and find it really quickly.
04:17
Oh, I see the problem. We need an an statement right there. And
04:25
so let's go ahead and rerun that.
04:28
Sure enough, we got the username. Let's go to give that column a name, those as user name.
04:35
There we go.
04:38
Now we got a nice additional calm
04:40
associated with their employees at data
04:44
for the user's user name.
04:46
Now let's see
04:47
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
04:59
because it makes me feel safer
05:00
and
05:03
user i d. Let's say we wanted employees,
05:06
but we only one of the ones
05:09
that had a user i d So the way we would do that So you say would say where
05:15
I would say and emp dot
05:19
user I d
05:21
is in
05:26
and again, I'm gonna put these brackets here
05:31
and we're going to
05:35
with this quarry
05:38
down here,
05:39
but we're going to change it a little bit. We're not gonna limit one.
05:45
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.
05:59
So
06:00
now we have to sub queries. The first of query is getting the user name.
06:03
This one is making sure
06:06
that the user i d and the EMP table
06:11
is contained within this sub query.
06:14
Now, if we go on drug run this sub quarry in its own editor,
06:17
we can see what results it will get back.
06:23
Well,
06:25
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.
06:30
It's going to turn on a list of the P K I D s exists in that table.
06:35
So we're requiring that the employees user I d exists in this result set when we run that sub cree over here,
06:46
we should see the exact same set come back because all of these guys have user names, which means they are all users.
06:53
So let's go ahead and run this and ensure that we get what we expect back, which will be 45 records.
07:00
And we got 45 records back.
07:03
And ah, that makes sense. Based on the current data within the database, if there were employees that didn't have a user name,
07:11
we would have saw them drop off this query because that would have been a requirement with this submarine.
07:16
So
07:17
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
07:35
being applied to the user's table from the employee table.
07:41
And then we also limited the results that too
07:45
users p k I d
07:47
from the user's table.
07:49
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.
08:03
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.

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