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.
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.
Indeed these are good videos. I found the below interesting SQL interview task for Data Analyst. This may help. ruclips.net/video/g-bLekv2kjU/видео.html
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.
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
@@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
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!!!
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;
@@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.
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, 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, 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?
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??
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
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
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....
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
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.
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
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 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 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
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
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
You are master in sql we need more problems like this
this video is absolute gem to watch, no better person available on internet to teach you SQL.
I really like your explanation. I can see your teaching passion in it. Continue sharing knowledge 👍🙌👏
Thanks a lot :)
First of all thanks for uploading this type of SQL video and Please upload more videos like this and if you have time please make live videos as well
Live streaming to solve problems.. good suggestion, will consider it
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.
This RUclipsr is the best and will go far more than other RUclipsrs.
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.
You rock, bro! Haven't seen someone that keen on SQL than you!
I request you to please start a Interview Problems Series! It would help a lot!
Please upload more videos like this 👌👌
I love theses videos
Thank you guys 😍
Indeed these are good videos. I found the below interesting SQL interview task for Data Analyst. This may help.
ruclips.net/video/g-bLekv2kjU/видео.html
I used recursive cte to stack up the job postings (similar to ungrouping) but thanks for this alternate solution
This is really a service. Thank you so much Sir.
This dude is a genius 🎉
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.
Practice. All the best
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
Really loved the way you simplied the problem.
Your explanation is on another level, Thanks!!! It's really helpful
You're very welcome!
Mine solution was same as yours. Except I use recursive CTE to generate series in MS SQL. 😀
Can you share me the code of CTE. your used.
@@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
Your teaching is awesome. When are you starting sql boot camp for new batch?
I will release a recorded course next month
Bootcamp like this will join
@@techTFQ really needed SQL bootcamp on graphy
Best one sir❤ please keep educating and challenging us🎉
Wonderful explanation sir.
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!!!
Your content is so good that i like your video before even watching ❤️
Great video Thoufiq!
Thanks for uploading this type of SQL video..I am unable to use generate_series function in mysql ..please suggest!!!!
Hi Thoufiq thanks for this question, please make video for query asked in MAANG companies.
Noted, will do
@@techTFQ, how to use generate_series in oracle db.
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;
I have liked this video it so challenging.
thank you
Yes sir make more Videos solved the problems
Clear cut explanation tq sir
Thanks.
Please make more of this video
Thank you will do
Hi, can you upload the same in oracle db?
Thanks!! learnt something new today, can you upload a version of this using recursive sql? Thanks!
I really loved your videos❤
Very helpful and detailed
Glad you liked it
I solved using Cte ' s only and another solution is using recursive cte ( i used MySQL)
Awesome 👌 well done
Superb, Thanks
Your welcome 🙏
This is very interesting problem
Amazing, thanks!;)
Hi Thoufiq I want to learn sql from basic to advance when are you launching ur course in Udemy
m2
I will launch the course soon and it will be on my own platform .. working on it currently
@@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.
Great explanation so I also want to send you an interview question related to SQL. Can you please tell me where can I send that question?
Thanks you Thoufiq
Any update on the Udemy course ??
Any update on your oracle course learning videos. You mentioned that you are going to create a platform and place your videos there?
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
Nice .. thanks for sharing
@@techTFQ Most welcome
Your solution return incorect results.
@@florincopaci6821 Kindly check the table names that you are using also i have used SQL server for the solution
@@nachiketpalsodkar4356 You are right. Your solution gave correct Output in Sql Server and Postgresql but in Oracle-NO.
Thank you
Sir I have a task releted to banking sector for Sql how can I send you question
email me
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, 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?
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??
Great video
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
Hi Taufiq can you pls do a video on using connect by clause in oracle?
Will you be launching an SQL bootcamp later this year?
Yes I’ll launch a recorded sql course next month
Keep price less 😅
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
Good job buddy
@@techTFQ Thanks Bro
Please upload more such videos
noted will do
Hi, I have a question on dates, I need to get date of 2 or 3rd occurence of Monday in a month, please make a video or SQL query..
@techTFQ I assume this task is not from a junior position interview but more like mid or senior-level? Am I right?
pls which sql database are you using
because I'm using azure?
You have other options to solve because sir my system does not accept the generate_ series' function
hello, would you please solve some interviews like these are but for python?
My version, But a bit unreadable(
with cte as (select level rownb from dual connect by level
nice man, well done
Vera level
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....
Please upload more scenarios anna
where can I learn these kind of new functions like Generate_series?
google bro, i am not aware of any cheat sheet with this info
How do I solve this query in mysql DB as generate_series() is not present in mysql ??
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
pls upload more like this
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.
Hi sir i have I sql interview query please help me.
Write a query to get all months end date of any year or given year.
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
you can use recursive cte to do the same thing, I have mentioned in comments my solution for sql server
can u please find how to do the same in mysql
Thanks a lot
Good content, u are too fast in ur delivery, please ,if u can speak slowly so we can grasp
Can you please launch new course beginners to pro sql with project on graphy
In MS SQL Server, there is no Generate_Series.
How to solve it in mysql and oracle db?
Amazing
Please make plsql course?
I’m planning for next year
Select Emp_name, title, Group, Level, payscal
From Emp
Where Position_ID = ID,
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
Please upload more
noted will do
Super
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 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
Hi Thofic i want sql course recording classes
recorded sql course should release next month
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
I have one interview Question I can’t answer, can you help me
Q : one table have 30 Indexes how to find good and bad one In SqlServer
bro make a video on keys in sql
you mean constraints?
yes sir
@@techTFQ
when can i expect a video sir
Not really planning on it .. I teach it during my sql course
Where can I get your SQL course
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
Not sure If I am new to SQL or this was very confusing for me :(
👌👌👌👌
😍
This seems so overwhelming
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
I have a solution to this posted on my channel.
❤🔥
Thanks a lot