99 percent of the time you can solve a problem using an independent query with a combination of cte and window functions. As the correlated sub queries are not so performance always try to avoid them. If you are looking to learn SQL from basic to advanced with lots of practice material and projects do consider my 0 to hero SQL course : www.namastesql.com/course-detail/think-sql-go-from-zero-to-hero-english
Hi @ankitbansal6, the script provided don't match with the result set of the emp table! Fixing it may be useful. Also, thanks for the incredible content as usual!
This is the best video i have seen on corelated subquery which not only showed how we can solve question with independent subquery , thankyou for such great videos , gratitude🙏🙏
I saw multiple videos on this topic, but trust me guy's no one can simplify the lang. or concept other than Mr. Ankit Bansal. Respect you sir for a reason 🙏🙏 thanks a lot❤
why we need subqueries: when the required format of the data is not given/not available, then we need to derive it and the join it back with the main data. Independent Subquery - 1. it can be run independently 2. it runs only once Correlated Subquery - 1. It cannot run independently because it has the reference of the main query 2. it runs for every record of the main query
Solution using "AVG(salary) over ()" Solution: select * from (select * ,avg(salary) over(partition by department_id) avg_dep_salary from emp_6_dec) s where salary>avg_dep_salary
Hi @ankitbansal6 i m new to sql can anyone explain why cant we use select department_id ,avg(salary) as avgsal from emp groupby department_id having salary> avg(salary)
if you want to solve this way you should Learn Self Join first... Hint: you have to separate avg(salary) into different intermediate table, because code don't know whats is avg(salary)..
99 percent of the time you can solve a problem using an independent query with a combination of cte and window functions.
As the correlated sub queries are not so performance always try to avoid them.
If you are looking to learn SQL from basic to advanced with lots of practice material and projects do consider my 0 to hero SQL course :
www.namastesql.com/course-detail/think-sql-go-from-zero-to-hero-english
also correlated is more time consuming
Hi @ankitbansal6, the script provided don't match with the result set of the emp table! Fixing it may be useful.
Also, thanks for the incredible content as usual!
The link is directing to error page
@@kanikasuneja7777 fixed it. Thank you!
This is the best video i have seen on corelated subquery which not only showed how we can solve question with independent subquery , thankyou for such great videos , gratitude🙏🙏
Glad it was helpful!
Here is my Solution
select * from
(select *,
avg(salary) over(partition by department_id ) as avg_dep_sal
from emp) e
where e.avg_dep_sal < e.salary
I saw multiple videos on this topic, but trust me guy's no one can simplify the lang. or concept other than Mr. Ankit Bansal. Respect you sir for a reason 🙏🙏 thanks a lot❤
Best video in internet till now explaining the difference
Great explanation 💯
Would you like more videos like this where you cover important topics.
Thankyou!!😊
Sure 😊
why we need subqueries: when the required format of the data is not given/not available, then we need to derive it and the join it back with the main data.
Independent Subquery - 1. it can be run independently 2. it runs only once
Correlated Subquery - 1. It cannot run independently because it has the reference of the main query 2. it runs for every record of the main query
Ultimate explanation ❤
Thanks a lot 😊
Excellent
Hi Ankit, Can You explain Performance tuning.
Sure
Hey @ankitbansal6, you missed a paranthesis ')' in your insert statement while inputting for Ankit's entry.
Thanks 🙏
@ankit bansal please tell for analytics point of view which platform is better for practicing sql.. Leetcode or data lemur?
NamasteSQL : www.namastesql.com/coding-problems
finished watching
Hi Ankit, Please also explain inline queries..
Hi Ankit, is the PLSQL concepts like stored procedures, triggers important for a data engineer ? If it's important, can you pls make a vedio on it ?
Not so important
Mostly core sql is used 95% the select queries
Very Helpful 🫂🫂
Solution using "AVG(salary) over ()"
Solution:
select *
from (select *
,avg(salary) over(partition by department_id) avg_dep_salary
from emp_6_dec) s
where salary>avg_dep_salary
Hi Ankit, can you let us know when to use correlated subquery.
Check my pinned comment
Hi @ankitbansal6 i m new to sql can anyone explain why cant we use
select department_id ,avg(salary) as avgsal
from emp
groupby department_id
having salary> avg(salary)
if you want to solve this way you should Learn Self Join first...
Hint: you have to separate avg(salary) into different intermediate table, because code don't know whats is avg(salary)..
Hey,
To know this, please get the understanding of sql order of execution. You'll be able to understood
bcz, select executes after group by, so avg is calculated during the execution of select, group by won't be knowing avg during its execution
finished watching