Hey Toufik, your lectures are super helpful in understanding the concepts in a much better way. Appreciate your inputs here. Could you please make a video on transactions and indexes, that would really help a lot to all our folks.
Indeed these are good videos. I found the below interesting SQL interview task for Data Analyst. This may help. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-g-bLekv2kjU.html
The same solution pop-uped in my head. Definitely there are other ways to solve the problem, but the shown solution is clever and readable and I cant figure out the better one.
Sir.... Why I am not getting idea to write query like you did in this video.... I know each functions but still I am. Not able to write complex query. Please give me some idea how can I improve atleast 40% of your SQL knowledge.
My solution which is little similar: with cte as (select a.title as t ,a.groups as g ,a.levels as l ,a.payscale as p , b.name as n from (select jp.*, row_number() over (partition by id order by id) as r1 from job_positions jp, generate_series(1,jp.totalpost)) a left join (select *, row_number() over (partition by position_id order by id) as r2 from job_employees) b on a.r1 = b.r2 and a.id = b.position_id) select t, g, l, p, case when n is null then 'Vacant' else n end as Emp_Name from cte;
@@anjankumaro7530I don't know exactly his solution but let try this oracle sql query: WITH CTE AS ( SELECT E.*, ROW_NUMBER() OVER (PARTITION BY GROUPS ORDER BY GROUPS) AS RN FROM ( SELECT * FROM JOB_EMPLOYEES J JOIN JOB_POSITIONS JP ON J.POSITION_ID = JP.ID) E), CTE1 AS ( SELECT F.*, ROW_NUMBER() OVER (PARTITION BY GROUPS ORDER BY GROUPS) AS RN FROM ( SELECT ID, TITLE, GROUPS, LEVELS, PAYSCALE FROM job_positions CONNECT BY PRIOR ID = ID AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL --if not used then error: CONNECT BY LOOP in user data AND LEVEL
I did it using the recursive method. Don't worry about column names in recursion. I just needed to understand recursion. with t1 as (select name, position_id, ROW_NUMBER() over(partition by position_id order by id) as rn from job_employees ) ,job_cte as (select id as dd, 1 as cnt, title, groups, levels, payscale from job_positions union all select b.id, cnt+1, b.title, b.groups, b.levels, b.payscale from job_positions b inner join job_cte a on a.dd = b.id and cnt
with cte(lvl,id,title,groups,levels, payscale,totalpost) as (select 1 as lvl,id,title,groups,levels, payscale,totalpost from job_positions union all select lvl+1 as lvl,id,title,groups,levels, payscale,totalpost from cte where lvl
Parabéns pelo ótimo vídeo - ótima explicação e detalhamento de cada passo. Muito bom!!! Congratulations for the great video - great explanation and detailed step by step. Very good!!!
Another Simpler WAY!!!! ; WITH cte as ( SELECT e.id,title,groups,levels,payscale,name,totalpost , ROW_NUMBER() OVER(PARTITION BY p.groups ORDER BY p.groups) as RNK FROM job_employees as e CROSS JOIN job_positions as p ) ,cte1 as ( SELECT * FROM cte WHERE RNK
Hi Sir, I have answered by pausing your video Below is my query, not sure whether u used same way or not with recursive job_positions_data as ( select id,title,grp,levels,payscale,1 as row_id,totalpost from job_positions union select id,title, grp,levels,payscale,row_id + 1 as row_id,totalpost from job_positions_data where row_id
with recursive cte as ( select id,title,groups,levels,payscale,totalpost from job_positions union select id,title,groups,levels,payscale,totalpost-1 as totalpost from cte where totalpost >1 ),cte2 as (select id,name,position_id, row_number() over(partition by position_id) as rn from job_employees) , cte3 as( select a.*,b.* from cte a left join cte2 b on a.id=b.position_id and a.totalpost=b.rn) select title, groups,levels,payscale,case when name is not null then name else 'Vacant' end as employeename from cte3
@@techTFQ so no udemy then. Will there be certificate? I wanna show of that I learned SQL in my cv. Otherwise people don't take it seriously when I say I have been managing ERP database for years. Apperantly some paper makes more impact than years of developing ERP software on my own.
Hi, Thankyou for yet another wonderful video.can you please let me know which oracle certification a person with 12 yrs of experience in plsql should take..?
Sir, please post all the sql classes please sir..........i am trying for job but so many qns on sql they are asking.....iam very poor in sql....but while listening your classes i clearly understand the concept ......plz....if i want talk about course personally ...how cam i contact you sir.....plz rply me sir....
Hey! What if we took row number instead of generate series for job_positions table n gonna partition by title which had the value of manager n all…. It would also give same output na??
SIR, we can also join job_positions and employee by only using position_id as common column right? then what is the need of using row number window function there?
Thanks for the problem! May I know, if this is the question for an experienced person who’s looking for a Data role or even for freshers, we gonna get such questions? And if it’s for an experienced guy, then may I know the count of exp? If any one can help me as I am trying to change my domain into Data roles.
SQL SERVER SOLUSTION with cte as ( select 1 n, id,title,groups, level, payscale,totalpost from postition union all select n+1,id,title,groups,level,payscale,totalpost from cte where n
my solution with recursive cte as ( select id,title,groups,levels,payscale,totalpost,1 as n from job_positions union select id,title,groups,levels,payscale,totalpost,n+1 as n from cte where totalpost>n ) select cte.title,cte.groups,cte.levels,cte.payscale,cte.totalpost,coalesce(e.name,'vacant') from cte left join (select *,row_number() over(partition by position_id order by id) as rn from job_employees) e on cte.id=e.position_id and cte.n=e.rn order by cte.id
I have one small question, in postgresql how can we return multiple results sets with different column size , Ms SQL server returned this , but in postgresql is it possible. Give answer please
Good explanation , But generate_series not available in sql server management studio 2022. And i have one doubt ISNULL replacing null values and COALESCE its returns 1st non null value of in our table data am i right? Then what is difference main between ISNULL and coalesc? Can you please tell me tfq
Hi Sir, Require one help related to mysql query where a table having data into month and yearwise only with single or multiple value so want to extract data where if same month and year repeated twice with different value then column M1 return M1='R' and again M1='B' and if same month and year repeated only once say Aug2023 then M2='R' and again M2=' ' and if again another same month and year repeated only once I.e sept2023 then M3='B' and M3=' ' respectively into mysql, kindly help me to share the way or query through which I can make on my db and can extract the data
with recursive cte_rec as (select id,title,groups,levels,payscale,totalpost, 1 as level from job_positions union select id,title,groups,levels,payscale,totalpost,level+1 from cte_rec where level
with a as ( select position_id, char(null) name, cnts, totalpost from (select distinct position_id, totalpost, totalpost - count(a.id) over(partition by position_id) cnts, count(a.id) over(partition by position_id) cnt from job_employees a join job_positions b on a.position_id = b.id) t where cnt
