select * from tbldept;
select * from tblemp;

/*
CTE – Common Table Expression – It returns a temp result in form of table
and on this result set we will fire a query to get our desire result
syntax –

with cte cte_name(col,col,…)
as
select statement;
select col1, col2 from cte_name;

Application:
When your desire result achived from multiple steps and joins become it more complex

*/
with ct1(dno,ecount)
as
(select DNo,isnull(count(eno),0) from tblemp group by DNo)
select d.DName,c.ecount from tbldept d join ct1 c on d.DNo=c.dno;

select * from tblhdfcdata;
select * from tblpnbdata;

create procedure sp_banksdata
as
begin

with ct1(Id,CardTitle,BaseRate)
as(select SR_No,Card_Name,Base_Rate from tblhdfcdata),
ct2(Id,CardTitle,BaseRate)
as(select SRNo,Card_Title,Rate from tblpnbdata)
select Id,CardTitle,BaseRate from ct1
union
select Id,CardTitle,BaseRate from ct2
end

execute sp_banksdata