Hey guys last SQL lesson of the day before I had to sleep so what I have
done now is I now have three tables in the database so if you don't want to
create your own tables then feel free to open up the Access file that I've
attached to the description of this video but basically I have three tables
now the first table is the student table which you should be familiar with if you
have been following my tutorials I made a subjects table which is basically a
table with three columns or three fields we got the subject ID a subject name and
subject teacher so basically a table that lists all the subjects of my fake
school so subject ID number one has the name subject name is English and the
teacher is Smith and subject number two has the name maths and taught by Chan so
then I've also made an enrollment table that has a list of subject IDs and
student IDs so that's basically I can say enroll students in a subject by
putting them in this table so let's say if I want to enroll student number ten
in the subject English then I can just put subject ID one which is English and
student ID ten and you'll see if you look at the design of this table that
both fields are primary keys and what that means is that you're allowed to
repeat just one field so you can have for example you can have two rows with
the same subject ID as long as their student IDs is different okay so as long
as the combination of these two two fields are unique then that's fine
so I can't have another row with one one for example because that's already taken
up but I could have 1/2 or 1/4 but not 1/3 because I've already put that there
and so on so what I'm going to do is I'm now going to make a sub-query
okay so sub-query is basically let me open up a query ok so sub-query is
basically a query within a query so let's say I select from the students
table let's just select the student ID the first name and the last name from
students let's just run that so I got three tables sorry three columns of the of
the students table and what I'm going to do is I'm only going to list the
students who are in who are enrolled in the subject number one so I can go oops
where's my where is my select query so where the student ID is in and then I
can put another query in here where I select the from the enrollment table
where the student ID select
student ID wait did I put them in cap capitals student ID from enrollment
where the subject ID is equal to one so and then close the bracket so what I
have done is basically I firstly select it from the enrollment table where the
subject ID is equal to one I've selected all the student IDs so that's my first
query and it's embedded inside this query where I select all the students
from the student table where the student ID is a result from my first query so if
I run this query now then it's selecting the students 1, 3, 5, 10 which in the
enrollment table are the students who were enrolled in the subject ID number 1
so 1, 3, 5, 10 these are the students that were enrolled in that subject so that's
what this query is doing a sub query is basically just a query the answer from
one query is used to obtain the answer of another query and now the cool thing
is we can actually have a query within a query within a query so let's say I want
to select all the subjects that were taught by the teacher Chan so I can go
select let's just do it down here so select um what is it select subject
teacher so select subject name or subject ID
from subjects where subject teacher that it subject teacher equals chan ok so
then that's obviously going to give me from this little query just here
ignoring everything from up here if I just look at this query it's going to
give me number two as the answer so instead of saying we're subject ID
equals something I can put in here in and then I can put this inside the
bracket and then I will need to finish it off with two brackets because I have
an open bracket here and another open bracket here so I'm finishing it off
with two brackets so this is going to list all the students who were taught by
Chan right because I'm firstly selecting the subject ID number two which is
taught by Chan and then I'm going to the enrollment table and I'm going to select
all the student IDs which are taught by which are enrolled in the subject
number two so that's 2, 4, 7 and then I go to student table and I go to
the numbers 2, 4, 7 and I list their first name and last name and so on so let's
run that and see if it works so run it go to design run and yes it does work
student 2, 4, 7 which were the ones enrolled in subject number two okay
thanks for watching guys see you next time.