Where we can use sub query ?

How we can find, When we use of sub-query with WHERE, FROM, or JOIN ?

Subqueries are used when you have to make an intermediate table in between SQL operations.
If you check the lesson on subqueries this exact topic was covered with example when do we use subqueries with FROM, WHERE and JOIN.
Most frequently you use it in FROM part and then JOIN and finally WHERE (in the lessons it was told that subqueries with WHERE part is very uncommon and only few such example exist). Please check the examples and explanation in the subquery lesson once again.

I see the lesson notes and got the working of Sub queries with FROM, JOIN and WHERE.
BUT i didn’t get how do i know, when we use subquerry with those clauses.

Like when we use subquery with FROM,
Subquery with JOIN and subquery with Where.

I get that,

  1. With Join it is used to reduced the table size.
  2. With From it is used to do aggregation on aggregated table.
  3. And with Where to select the sorted things from table.

Is it what i get . Is it right or something else.?

Your understanding is correct karan. I just want to add that FROM is the most basic one so it can be used even in cases when you just want to filter anything based on subquery (you can observe this in Q11 of the SQL advanced assignment) or even just check the columns of the subquery.
One more aspect is that once you learn CTEs you will find them much more easier and they can be used in place of subqueries as well. But for interview purposes you need to learn both.

ok got it