SQL Magic Show | Solving a Tricky SQL Problem with 10 Methods | SQL Tutorial

Hello everyone, today we are going to solve a tricky problem using 10 methods using SQL. In this process we will cover almost all the SQL topics so watch this video till end and enjoy the show.
Here is the script:
create table students (
student_id int,
skill varchar(20)
);
delete from students;
insert into students values
(1,'sql'),(1,'python'),(1,'tableau'),(2,'sql'),(3,'sql'),(3,'python'),(4,'tableau'),(5,'python'),(5,'tableau');
Zero to hero(Advance) SQL Aggregation:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
#sql #dataengineer

[@mohdtoufique7446]

Hi Ankit! Thanks for the content ...Another alternative approach SELECT student_id,count(*),STRING_AGG(skill,',')WITHIN GROUP(ORDER BY skill) AS skillset FROM students GROUP BY student_id HAVING STRING_AGG(skill,',')WITHIN GROUP(ORDER BY skill)='python,sql';

[@akashgoel601]

great solution! adding my sol, using pivot got the output. even simpler: with cte as ( SELECT student_id AS student_id, [python], [sql], [tableau] FROM ( SELECT student_id, skill FROM students ) AS SourceTable PIVOT ( min(skill) FOR skill IN ([python], [sql], [tableau]) ) AS PivotTable ) select student_id from cte where tableau is null and python is not null and sql is not null

[@anjalimoon619]

very nice Session Sir. SELECT student_id FROM students GROUP BY student_id HAVING COUNT(DISTINCT skill) = 2 AND SUM(CASE WHEN skill = 'sql' OR skill = 'python' THEN 1 ELSE 0 END) = 2;

[@mahesh97413]

Hi Ankit. I tried the below one: %sql WITH cte AS ( SELECT student_id, COUNT(DISTINCT skill) AS num_skills, SUM(CASE WHEN skill IN ('sql', 'python') THEN 1 ELSE 0 END) AS python_sql_count FROM students_A GROUP BY student_id ) SELECT student_id FROM cte WHERE num_skills = 2 -- Ensure there are exactly 2 distinct skills AND python_sql_count = 2; -- Ensure both skills are SQL and Python

[@rajat1999gupta]

Hey Ankit, my mentos style solution select student_id, group_concat(skill order by skill) from students group by student_id having group_concat(skill order by skill) ='python,sql'

[@piyushbamboriya1288]

select student_id from students group by student_id having sum(case when skill in ('sql','python') then 1 else -1 end) =2

[@ankitbansal6]

It won't work

[@hari_2305]

its working. good thought!

[@ankitbansal6]

Sorry for the confusion. I think it should work.

[@VikashKumar0409]

Loved the 10th soln

[@Ganeshay-09]

oooh ur real brilliant in sql queries ..thanks for more explain and details with different ways

[@ankitbansal6]

You are most welcome

[@letsdomistakes]

WITH SQL AS (SELECT * FROM students where skill = 'SQL') , python as (select * from students where skill = 'python') , other as (select * from students where skill not in ('sql', 'python')) SELECT * FROM SQL s inner JOIN python p on s.student_id = p.student_id where s.student_id NOT IN (SELECT student_id from other); check this method, it's really helpful

[@ReinisRap]

I liked the no. 10 the best!

[@sahilummat8555]

Hi Ankit another solution from my end select * from ( select student_id,STRING_AGG(skill,',') within group(order by skill) as skills from students group by student_id)a where a.skills ='python,sql'

[@avi8016]

Great video sir💯 Excellent use of different methods to achieve the same result. Loved the interesect and except method. Thankyou for bringing such wonderful videos🙏

[@ankitbansal6]

You are most welcome

[@vikramjitsingh6769]

It would be helpful if u could rank these on most efficient query and why....

[@kailashpatro5768]

Worth Varma worth vere level

[@anirbanbiswas7624]

with cte as(select student_id,case when skill='python' then 1 when skill='sql' then 1 else 3 end as flag from students), cte2 as( select student_id,sum(flag) as total from cte group by student_id) select STUDENT_ID from cte2 where total=2 ---YOU MAY USE ANY NUMBER MORE THAN 3 TO SATISFY THE CONDITION

[@randomlyaj4735]

hello sir , my solution is - select student_id,skills from( select student_id,skills from (select student_id,group_concat(skill) as skills from students -- where skill='sql'or skill='python' group by student_id)a where skills not like '%tableau%')b where skills not like '%sql'

[@karangupta_DE]

with cte as ( select student_id, listagg(skill, '_') as skills from students_new group by student_id ) select student_id from cte where skills = 'sql_python';

[@Dataengineeringlearninghub]

This is Great Ankit, keep doing it

[@sarithakakarla-y1x]

Hi Sir, Is this correct way select student_id,listagg(skill,',') from students group by student_id having listagg(skill,',')='sql,python'

[@Mahi_RSV]

Know Only SQL, Python: select student_id,string_agg(skill,',') as skills from students group by student_id having Upper(string_agg(skill,',')) like 'SQL,PYTHON' or upper(string_agg(skill,',')) like 'PYTHON,SQL' O/P: student_id skills 3 sql,python Know SQL , Python for sure, can have other skills too: select student_id,string_agg(skill,',') as skills from students group by student_id having string_agg(upper(skill),',') like '%SQL%PYTHON%' or string_agg(upper(skill),',') like '%PYTHON%SQL%' O/P: student_id skills 1 sql,python,tableau 3 sql,python 5 python,tableau,sql

[@RamaKrishna-ll8iz]

select student_id from students where skill='python' and student_id in ( select student_id from students where skill='sql' ) and student_id in ( select student_id from students group by student_id having count(skill)=2 )

[@sz6618]

-- Method 6 -- co related sub query explantion Select student_id, count(*) as Total_skills from #students s1 where not exists (select student_id from #students s2 where s2.skill not in ('sql','python') and s1.student_id=s2.student_id) group by student_id having count(*) = 2 /* select student_id from #students s2 where s2.skill not in ('sql','python') Result s2 id : 1,4,5 Select student_id, count(*) as Total_skills from #students s1 group by student_id Result S1 id : 1,2,3,4,5 where not exists means ( result of s2 id : 1,4,5 will be excluded ) id = 2,3 left after putting having count(*) = 2 id 2 will be excluded output would be id = 3 */

[@KoushikT]

My Simple Approach select student_id from students group by student_id having count(skill) = 2 and count(skill) = sum(case when skill = 'sql' or skill ='python' then 1 else 0 end)

[@SaiPrashanthThalanayar]

Another Solution: SELECT student_id,count(*) FROM students GROUP BY student_id having count(*) =2 and max(skill) = 'sql' and min(skill) = 'python'

[@ashnakhera5733]

with skill_count as (select student_id,count(skill) as cnt from students group by student_id) , desired_skill as (select student_id as idd from students where student_id not in (select student_id as id from students where skill not in ('SQL','Python'))) select distinct student_id from skill_count join desired_skill on skill_count.student_id = desired_skill.idd and cnt=2

[@MegaSanku123]

--approach 1 with cte as (select student_id,string_agg(skill,',' order by skill) as skill_aggregated from students group by student_id ) select student_id from cte where skill_aggregated='python,sql'; --approach 2 with cte as ( select student_id, max(case when skill='sql' then 'Y' else 'N' end) as sql_skill, max(case when skill='python' then 'Y' else 'N' end) as python_skill, max(case when skill='tableau' then 'Y' else 'N' end) as tableau_skill from students group by student_id order by student_id ) select * from cte where sql_skill='Y' and python_skill='Y' and tableau_skill='N'

[@himanshuparihar9888]

select student_id from students_1 group by student_id having sum(case when skill in ('sql' , 'python' ) then 1 else 0 end) = 2 and count(*) =sum(case when skill in ('sql' , 'python' ) then 1 else 0 end) --------------------------------------------------------------- with cte as ( select student_id from students_1 group by student_id having count(distinct skill) = 2 ) select student_id from students_1 where skill in ('sql' , 'python' ) and student_id in (select * from cte ) group by student_id having count(*) = 2

[@theraizadatalks14]

One more solution : Select student_id from students group by student_id having count(distinct skill) = 2 and sum(case when skill in ('sql','python') then 1 else 0 end ) = 2

[@Leo-qo5hk]

select student_id,count as total_skill,STRING_AGG(skill,', ') as skill from(select *, lag(skill) over(order by student_id) as lag, lead(skill) over(order by student_id) as lead, count(*) over(partition by student_id) as count from students_skills)x where count = 2 and (skill='sql' and lead='python' or skill='python' and lag='sql') group by student_id,count

[@fog2640]

finished watching

[@sanyamgarg8288]

hi Ankit. with a as( select student_id, count(*) from students group by student_id having count(*)=2) ,b as ( select student_id, min(skill),max(skill) from students where student_id in(select student_id from a) group by student_id having min(skill)='python' and max(skill)='sql' ) select distinct student_id from b; is this ok?

[@monasanthosh9208]

1st question solution Select Student_id from (Select *,group_concat(Skill) as Skills From Students group by Student_id Order by Skill)N Where Upper(Skills) Like "SQL,PYTHON";

[@dakshbhatnagar]

My solution to the problem with cte as ( select student_id, group_concat(skill) as skills from students group by student_id) select * from cte WHERE skills LIKE '%sql%' AND skills LIKE '%python%' and skills NOT LIKE '%tableau%'

[@ank_kumar12]

select student_id from students group by student_id having group_concat(skill) ='sql,python';

[@reshmaammu4726]

with cte AS (select student_id, count(skill) as skills from students group by student_id having count(skill)=2), cte2 as (select a.student_id, skills, case when skill="sql" or skill="python" then 1 else 0 end as checker from cte a left join students b on a.student_id=b.student_id) select student_id, count(checker) from cte2 where checker=1 group by student_id having count(checker)=2 or with cte as (select student_id, count(skill) as skills from students where skill in('sql', 'python') group by student_id having count(skill)=2) select b.student_id from students a right join cte b on a.student_id=b.student_id group by b.student_id, skills having skills=count(skill)

[@deaspirant]

Hi Ankit, I am planning to buy your course and next week I am going to attend for Tableau interview for one of the top product based company. I have some basic knowledge about sql can you help with what questions and concepts need to prepare for the interview.

[@ankitbansal6]

There are no specific topics. They will ask problems and you need to solve them. That's it.

[@pranavtaparia7711]

--find students who know only sql and python with cte as( select student_id, (case when skill = 'sql' then 1 else 0 end) as SQL , (case when skill = 'python' then 1 else 0 end) as Python, (case when skill = 'tableau' then 1 else 0 end) as Tableau from students ) ,jte as( select student_id, sum(sql) as "SQL", sum(python) as "Python", sum(tableau) as "Tableau" from cte group by student_id order by student_id ) select student_id from jte where "SQL" = 1 and "Python" = 1 and "Tableau"!=1 ;

[@as-youtuber6215]

Ankit sir, Can you start your AWS Training as soon as possible????

[@Ganeshay-09]

AWS FREE COURSE |#devops #aws #abhishekveeramalla

[@sunandpal]

WITH SQL_PYTHON AS ( SELECT student_id, COUNT(CASE WHEN skill = 'sql' THEN 1 ELSE NULL END) SQL, COUNT(CASE WHEN skill = 'python' THEN 1 ELSE NULL END) PYTHON, COUNT(CASE WHEN skill = 'tableau' THEN 1 ELSE NULL END) TABLEAU FROM students GROUP BY student_id) SELECT student_id FROM SQL_PYTHON WHERE SQL=1 AND PYTHON =1 AND TABLEAU=0;

[@radhikagupta7314]

SELECT DISTINCT student_id FROM ( SELECT students.*, LISTAGG(skill, ',') WITHIN GROUP( ORDER BY skill ) OVER(PARTITION BY student_id) AS consolidated_skill FROM students ORDER BY student_id ) WHERE consolidated_skill = 'python,sql' or consolidated_skill ='sql,python'

[@akhiladevangamath1277]

select student_id from students group by student_id having COUNT(DISTINCT skill)=2 AND SUM(skill NOT IN ('sql', 'python')) = 0;

[@RSJ1947]

WITH SKILLS AS ( SELECT STUDENT_ID , STRING_AGG(SKILL, ',' ORDER BY SKILL) AS ALL_SKILLS FROM STUDENTS_NS GROUP BY STUDENT_ID ) SELECT STUDENT_ID FROM SKILLS WHERE ALL_SKILLS = 'PYTHON,SQL' ;

[@ashishkumarjha2587]

WITH cte AS (SELECT student_id , GROUP_CONCAT(skill ORDER BY skill ASC SEPARATOR "_") AS skills FROM students_skill GROUP BY student_id ) SELECT student_id FROM cte WHERE skills = "python_sql";

[@letslearn9384]

with cte as(select *, case when skill="python" then 1 when skill="sql" then 2 else 3 end as ans from student) select student_id from cte group by student_id having count(student_id)>1 and sum(ans)=3;

[@Demomail-m6w]

Hi Ankit, I tried to solve problem with this approach with cte as ( select student_id,count(distinct skill) cnt from students group by student_id order by student_id ), cte1 as( select s.student_id,c.cnt,s.skill,row_number() over(partition by s.student_id order by s.student_id) rn from students s join cte c on s.student_id=c.student_id and c.cnt=2 and (s.skill='sql' or s.skill='python')) select student_id from cte1 group by student_id having count(rn)!=1; let me know your thoughts on this?

[@rishavvv6441]

with base as( select student_id,skill, count(*) over(partition by student_id ) as cnt, case when skill='sql' or skill='python' then 1 else 0 end as flag from students) select student_id from base group by student_id having max(cnt)=2 and sum(flag)=2

[@dishantjain7789]

with cte as ( select *, case when skill='sql' then 1 when skill='python' then 2 else 3 end as skill_cntr from students ) select student_id from cte group by student_id having count(student_id)>1 and sum(skill_cntr)=3;