SQL Tutorial 6, Select with Group By and Having plus Where

Learn SQL, Introduction to DML: How to use group by and having in a select statement, using where and having together. Filter grouped data. #sql, #rdbms, #table, #amitchandakda #interviewquestions #interview

Definition Source: Google Search, Wikipedia, IBM, W3School

00:00 Introduction
03:00 Group By
12:00 Group By with Having

Last Video: SQL Tutorial 4- Where Clause, Copy Table, Import Data: https://youtu.be/4vAesfpoGfM

Code
select *
from sales
where Item_ID in
(select item_id
from item
where Brand = 'Brand 1'
) ;


select *
from sales
where City_Id in
(select City_Id
from geography
where State = 'New York'
) ;


select s.*
from Sales as s
inner join
(select City_Id
from geography
where State = 'New York'
) g on g.City_Id = s.City_Id ;


select s.*
from sales s ,
(select item_id
from item
where Brand = 'Brand 1'
) i
where i.Item_ID = s.item_id
;


select s.*
from sales s ,
item i
where i.Item_ID = s.item_id
and Brand = 'Brand 1';


select Brand, count(Distinct Category) cat_cnt
from item i
where Category in ('Category 1', 'Category 2')
group by Brand
having count(Distinct Category) =2;


select s.*
from sales s
inner join item i
on i.Item_Id = s.Item_ID
where brand in
(select Brand
from item i
where Category in ('Category 1', 'Category 2')
group by Brand
having count(Distinct Category) =2)


select s.*
from sales s
inner join item i
on i.Item_Id = s.Item_ID
where Category in ('Category 1', 'Category 2') ;



select s.*
from sales s
inner join item i
on i.Item_Id = s.Item_ID
inner join (select Brand
from item i
where Category in ('Category 1', 'Category 2')
group by Brand
having count(Distinct Category) =2) b
on i.Brand = b.Brand;



select item_id
from item
where Brand in (select Brand
from item i
where Category in ('Category 1', 'Category 2')
group by Brand
having count(Distinct Category) =2)


select s.*
from sales as s
inner join ( select item_id
from item
where Brand in (select Brand
from item i
where Category in ('Category 1', 'Category 2')
group by Brand
having count(Distinct Category) =2) ) i
on i.Item_Id = s.Item_ID


select *
from sales s
left join (select * from item where item_id LT= 50) i
on i.Item_Id = s.item_id



select *
from ( select * from sales s where item_id GT 10) s
right join item i
on i.Item_Id = s.item_id ;


select *
from ( select * from sales s where item_id GT 10) s
full join (select * from item where item_id LT= 50) i
on i.Item_Id = s.item_id

-~-~~-~~~-~~-~-
Please watch: "Microsoft Power BI Tutorial For Beginners✨ | Power BI Full Course 2023 | Learn Power BI"
https://www.youtube.com/watch?v=cN8AO3_vmlY
-~-~~-~~~-~~-~-