SQL Tutorial - 54: Subqueries in SQL Video Lecture | Microsoft SQL for Beginners - Database Management

today with my channel this is the 54th
tutorial in this course and in this
tutorial we are going to talk about sub
queries and the sub queries are also
known as nested queries or in turquoise
and you know these are simply queries
within queries and the best way to
understand the sub queries is to see one
in an application so we're going to take
a very simple application of a sub query
and what we do is we work would be
employed by the way and you know just so
if you forgotten the structure or the
data that's there in the variable I just
you know run a select star query from
the employee table and show you guys
what it has so you know the table has
got one two three four five fees the ID
field name field age email and salary
and now all this information is you know
about employees right so let's say you
want to find out you know the highest
salary you know in this table and you
also want to find out you know who all
gets that salary so you can see like you
know the tables got few doors so we can
just scroll down and we can check which
is the highest salary so they about
twenty-two thousand seven hundred fifty
is the highest salary in the table and
we can easily find this out by executing
the max function on the salary free and
the table right now if you want to find
out how many employees get this salary
or you know
that's the salary details about those
employees then you have to use
sub-queries right there is another way
to do that but then if you want to use a
single query to achieve that then you go
to your sub queries and I'll just show
you how right so if suppose you do not
want to use sub queries and if you also
have like too many rows in the table you
just don't want to scroll down and you
want to find out what's the highest Savi
amount then the first thing you got to
do the first step is you have to find
out the highest salary and that as I
said to do that you can use the max
function on the salary field so you know
I have to type and select max salary
from employee and when I execute this
query I get in the results of the answer
twenty thousand seven and fifty now if F
wish to find out the names and I use of
all the employees or get the salary I
can execute this query select ID comma
name from employee where salary is equal
to twenty two thousand seven hundred and
fifty so when I execute this I see that
you know there are one two three four
five employees that get the salary now
if suppose I want to do both these
things in a single step then what is a
way to it if you guys are thinking okay
you know in the where clause instead of
putting the amount going to thousand
seven and fifty we can simply put max
salary in there if you will do this then
it's not going to work right because you
know the max function as you guys know
is an aggregate function networks will
result set but then this is not a query
in its own so in order to make this
equation order to be able to apply the
aggregate function on the salary field
here
what are you going to do as you go to
insert another query here so you know
within parentheses you type in select
max salary from employee it's as simple
as that you know instead of just typing
in max salary here
amount you've written a simple query
that's going to return the maximum
salary and then that savvy amount is
going to be used by the salary condition
here and then we are going to get the
IDs and names so when I execute this
query we're going to see like no
difference at all between the results
that we had earlier the results are
going to get now so again you see that
you know we get all the five names there
are the five IDs and you know this query
that we see here you know this right
here is known as the sub query or the
inner query for this entire query right
so first this query is going to execute
and then the result of this query is
going to be used by the outer query so
that's what a sub query is and you know
you guys imagine now you know the kind
of things that you can do with sub
queries you know it gives you so many
options and then you know in this case
this query the inner query is going to
result in two just one salary amount but
if it's going to result in two
range of values and what you can do is
you can have the like or in operators
here and you can work with those so you
know sub-queries they just you know
often order for you know options too and
you know in certain cases you won't be
able to do things without using sub quiz
like if you want to find out the second
highest savvy amount in the table then
you know again you would have to put
something you have to change the
operator here to less than equal to and
then you know the square you would have
to be the same if you want to find out
the third highest amount and you can do
that too so that's it about sub queries
and thank you so much for watching this
tutorial I see you guys in the next one
in which we talk about something else
you know maybe we all do table command
because that's something that I really
want to discuss in this course and sub
queries you know you guys can use
security not just with the Select
statement but also with insert update
and delete and you know so thank you so
much for watching this tutorial and you
may subscribe to my channel if you
haven't already and I'll see you soon