SQL Interview Questions and answers Part 41 | SQL Scenario based Interview Question and Answer

SQL Interview Questions and answers Part 41 | SQL Scenario based Interview Question and Answer

Problem Statement :- Club Table has three columns namely Club_ID, Member_id and EDU.
Same member can be a part of different club. The EDU column has different rewards. The points for these awards are as follows :-
MM – 0.5, CI – 0.5, CO- 0.5, CD – 1, CL-1, CM – 1
Write a SQL query to find the total points scored by each club as shown in the desired output.
Club_Id Reward
1001 4.0
1002 6.0
1003 NULL

-------------------------------------------------------------------------
Follow me on Social Networking Sites :
-------------------------------------------------------------------------
*Twitter:
*Instagram :

-------------------------------------------------------------------------
Table and Insert SQL Script :
-------------------------------------------------------------------------

Create Table Club (
Club_Id int,
Member_Id int,
EDU varchar(30))

Insert into Club Values (1001,210,Null)
Insert into Club Values (1001,211,'MM:CI')
Insert into Club Values (1002,215,'CD:CI:CM')
Insert into Club Values (1002,216,'CL:CM')
Insert into Club Values (1002,217,'MM:CM')
Insert into Club Values (1003,255,Null)
Insert into Club Values (1001,216,'CO:CD:CL:MM')
Insert into Club Values (1002,210,Null)
-----------------------------------------------------------------------------------------------------------------
#SQLInterviewQuestionsandanswers #sqlInterviewQuestions #sqlInterviewQuestionsForTesting #sqlInterviewQuestionsQuery #sqlInterviewQuestionsOnJoins #sqlTechnicalInterviewQuestions #SQLforETLTesters #CommonSqlInterviewQuestions #ETLTesting #ITJUNCTION4ALL #AmazonInterview

[jagdishgyanchandani]

Sir. It was the best playlist on SQL Queries I have ever wathced. I have learnt a lot from this. Please try to make more videos on SQL scenario based queries. Thanks.

[karthikramu]

Best set of videos to whomever prepares for SQL Interviews !!! Great work !!!

[sivagssri]

Superb Content... I have one scenario where you need to split two tables out of one table, 1st row go to 1st table, 2nd row go to 2nd tables, 3rd row go to first table... Another scenario is 1 table split to 5 tables, 1st row to 1table, 2nd row to 2table... 5th row to 5table, 6th row to 1table, 7th row to 2nd table... Can you do these 2 scenarios... Can you please do these two... Thanks for making videos with good content... Your last video was 5 months back... Do understand you were busy... Pls try to make video at least 2 weeks once... Which will be helpful for all of your subscribers...

[vedasiva]

Great Video!
Can you help me with this question ?

Delete duplicate words from a column
Example: If a column has "General General Manager", update the column value to "General Manager"

table used for this is

CREATE Table #temp (FailMsgs text)
INSERT INTO #temp VALUES ('General heating procedure');
INSERT INTO #temp VALUES ('Maximum pressure pressure reached');
INSERT INTO #temp VALUES ('Minimum pressure reached');

[NomanKhan]

Very Nice Brother, , Please make at least one video on SQL Partition By

[YASHKUMARJAIN]

hey,
i am not clear how i will do this in my sql as string_split function doesnot exist in mysql :/ . please help

[ankitaSharma-ebhz]

I tried this problem without string_split and got desired output.
WITH CTE AS(
select *, ISNULL(case when edu LIKE '%MM%' then 0.5 end, 0) AS MM,
ISNULL(case when edu LIKE '%CI%' then 0.5 end, 0) AS CI,
ISNULL(case when edu LIKE '%CO%' then 0.5 end, 0) AS CO,
ISNULL(case when edu LIKE '%CD%' then 1 end, 0) AS CD,
ISNULL(case when edu LIKE '%CL%' then 1 end, 0) AS CL,
ISNULL(case when edu LIKE '%CM%' then 1 end, 0) AS CM
from club
),
CTEE AS(
SELECT CLUB_ID, MM+CO+CI+CD+CL+CM AS T FROM CTE
GROUP BY CLUB_ID, mm, CO, CI, CD, CL, CM)

SELECT CLUB_ID, SUM(T) AS TOTAL_POINTS FROM CTEE
GROUP BY CLUB_ID