SQL Interview tips
- SQL order of execution
1. Getting Data (From, Join)
2. Row Filter (Where)
3. Grouping (Group by)
4. Group Filter (Having)
5. Return Expressions (Select)
6. Order & Paging (Order by & Limit / Offset)
EX: → Select * from table group by colname having condition order by colname
2. Aggregate operators(max,min,count,sum) can be used in 2 ways
i.Beside select statement → select max(* ) from tble
ii. having condition → select count(*) from tble group by name having count(*)<2
3. Columns used in group by only be selected on select statement (because rows are reduced when we use group by)
select col1, count from tble group by col1 →here col1 only be selected because we only used this column in group by
4. If we have req where the rows should not be reduced but we need to do aggregate operations
4.i. Windowing functions (partition by is used instead of group by → it will not reduce rows)
max(col1) over(partition by col2 order by col3)
avg(col1),min(col1),max(col1),count(col1)
rank() over (partition by col2 order by col3)
row_number(),dense_rank()
4.ii. self join correlated subquery
correlated subquery (b.id≤a.id) means inner query is executed for all the rows in a table for once outer query(if a table has 5 rows then first row outer query will checkwith all the 5 rows of innerquery so 5*5 = 25 times ) →here we can get same rows or more rows also
select a.id,sum(b.sal) from table a join table b where b.id≤=a.id group by a.dept,a.id order by a.id
4.iii. other correlated / normal subqueries but it will get reduced rows
select * from (select *,rank()over(order by col) from tble) where r=2
select * from tble a where sal > (select avg(sum) from tble b whre b.id = a.id)
Feel free to drop me a mail if you have any queries or planning to make a career in data engineering — “msdilli1997@gmail.com” . I can help you to get your doubts clarified
