Accenture SQL Interview Question | Database Case Sensitivity vs Insensitivity

Рет қаралды 19,513

Ай бұрын

In this video we will discuss a Accenture SQL interview question and solve it when the data is case sensitive and when it is insensitivity .
Here is the script:
CREATE TABLE employees (employee_id int,employee_name varchar(15), email_id varchar(15) );
delete from employees;
INSERT INTO employees (employee_id,employee_name, email_id) VALUES ('101','Liam Alton', 'li.al@abc.com');
INSERT INTO employees (employee_id,employee_name, email_id) VALUES ('102','Josh Day', 'jo.da@abc.com');
INSERT INTO employees (employee_id,employee_name, email_id) VALUES ('103','Sean Mann', 'se.ma@abc.com');
INSERT INTO employees (employee_id,employee_name, email_id) VALUES ('104','Evan Blake', 'ev.bl@abc.com');
INSERT INTO employees (employee_id,employee_name, email_id) VALUES ('105','Toby Scott', 'jo.da@abc.com');
INSERT INTO employees (employee_id,employee_name, email_id) VALUES ('106','Anjali Chouhan', 'JO.DA@ABC.COM');
INSERT INTO employees (employee_id,employee_name, email_id) VALUES ('107','Ankit Bansal', 'AN.BA@ABC.COM');
ALTER TABLE employees
ALTER COLUMN email_id VARCHAR(15) COLLATE SQL_Latin1_General_CP1_CS_AS;
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 #dataanalytics

Пікірлер: 39

@ankitbansal6 Ай бұрын

Please like the video for more interview questions.

@typing07 Ай бұрын

What is the Power Of CTE, I got to know after watching your video, Lovely explaination, BHaiya aapke CTE ke video dekhne ke baad my confidence in CTE is on the top of the floor.

@ankitbansal6 Ай бұрын

It's my pleasure🙏

@ChandrashekarReddyY-hp4rl Ай бұрын

Thank you.Nice Explanation.

@vijaygupta7059 Ай бұрын

Thanks for explaining step by step real-time interview question, Request you please bring more real-time interview question

@rk-ej9ep Ай бұрын

Awesome..as usually..

@ankitbansal6 Ай бұрын

Thank you! Cheers!

@rakeshpanigrahi577 Ай бұрын

Thanks Ankit, here is my solution: with cte as ( select *, case when regexp_like(email_id, '.*[A-Z]+.*') then 1 else 0 end as cap_ind, count(lower(email_id)) over(partition by lower(email_id)) as cnt_email from employees ) select * from cte where not (cap_ind = 1 and cnt_email > 1);

@sukanyaiyer2671 9 сағат бұрын

delete from employees where lower(email_id) IN( select lower(email_id) from employees group by lower(email_id) having count(lower(email_id)) > 1)

@TheTUSHAR121 9 күн бұрын

with cte1 as (select emp_id, email_id, count(email_id) over (partition by email_id)as cnt from eid), cte2 as (select emp_id,email_id from cte1 where cnt>1) SELECT * FROM cte2 WHERE UPPER(email_id) COLLATE Latin1_General_CS_AS email_id;

@srikanthadepu8728 Ай бұрын

It was a wonderful explanation . If possible can you make a performance tuning videos

@atharvabhangre9044 Ай бұрын

Great!!!

@Gokulsai18 Ай бұрын

select * from ( select *,lower(email_id) as l_email ,ASCII(email_id) as ASCI, row_number() over(partition by lower(email_id) order by ASCII(email_id) desc) as rw from employees )A where ASCI>97 or rw=1

@SiriMaddala-gt6wn Ай бұрын

Hello Ankit, This is my solution. WITH CTE AS( SELECT *, COUNT(*) OVER(PARTITION BY LOWER(email_id)) AS distincts, CASE WHEN email_id = LOWER(email_id) THEN 1 ELSE 0 END AS FLAG FROM employees) SELECT employee_id, employee_name, email_id FROM CTE WHERE (distincts = 1 AND flag = 0) OR flag = 1

@shatirdimag1011 25 күн бұрын

Best solution

@mr.pingpong502 9 күн бұрын

with cte as( select *,case when lower(email_id)!=email_id then 'uppercase' else 'lowercase' end as caser,row_number() over(partition by lower(email_id) order by employee_id) as rn from employees ) select * from cte where rn

@Batmanspidermanironman 14 күн бұрын

Hi Ankit, Very well explained! Can we use dense_rank here instead of rank()?

@yatharthchauhan7407 Ай бұрын

will it be correct to use dense_rank() over here instead of rank()?

@atifsuhail7803 Ай бұрын

with cte as( select *,count(*) over(partition by email_id) cnt from employees ) select employee_id,employee_name, email_id from cte where cnt=1 or(cnt>1 and lower(email_id) COLLATE Latin1_General_CS_AS=email_id ) order by employee_id;

@anandmahajan943 Ай бұрын

@AnkitBansal , waiting for the Redshift course ❤

@user-dw4zx2rn9v Ай бұрын

My Sql Solution: with cte as ( select employee_id, employee_name, email_id,count(*) over (partition by email_id) as cnt from employees ) , cte2 as ( select employee_id, employee_name, lower(email_id) as lw, email_id from cte where cnt > 1 ) select * from cte2 where ascii(lw) = ascii(email_id)

@Aman-lv2ee Ай бұрын

with temp as ( Select lower(email_id) as l_email_id, count(1) from employees group by lower(email_id) having count(1) >= 2 ) select * from employees where email_id not in (select UPPER(l_email_id) from temp)

@shivaprasad-kn3kw 12 күн бұрын

what if duplicate upper case emaild is getting rank as 1?

@nipunshetty9640 Ай бұрын

THE SQL ARTIST ANKIT BANSAL❤❤❤

@surajmahapatra-dr6jw Ай бұрын

WITH CTE AS ( SELECT *, RANK( )OVER(PARTITION BY EMP_MAIL ORDER BY EMP_NAME DESC) AS EMP_RANK FROM DUPLICATE_VALUES ), FINAL AS ( SELECT *, RANK()OVER (PARTITION BY EMP_RANK ORDER BY EMP_NAME DESC ) AS FINAL_EMP_RANK FROM CTE ) SELECT TOP 2 EMP_ID,EMP_NAME,EMP_MAIL FROM FINAL WHERE FINAL_EMP_RANK=1

@shilpakottargi6323 Ай бұрын

hi ankit how about this query ? select distinct (ascii(email_id)), email_id from employees

@ankitbansal6 Ай бұрын

We need all the columns. Emp I'd name etc

@Apna_tahlka_123 Ай бұрын

A humble request to you plj use some hindi some eng by which I can understand easily

@sambeetpanda6919 Ай бұрын

this question asked for freshers or experienced?? ankit bhaiya

@simranpreetsingh3052 Ай бұрын

It probably asks for an experience candidate.

@shikharanjan3137 Ай бұрын

select * from employees where employee_id not in ( select employee_id from ( select employee_id, employee_name, email_id, upper(email_id) as U_email_id , count(1) over(partition by upper(email_id) ) as cont from employees) a where email_id=upper(email_id) and cont>1) order by employee_id;

@raviyadav-dt1tb Ай бұрын

hello Ankit,can you please help me out i am also giving data egineer interview and failing in sql part..please give me suggestion how to make it strong in sql. please.

@ankitbansal6 Ай бұрын

Just complete this course : www.namastesql.com/

@raviyadav-dt1tb Ай бұрын

@@ankitbansal6 is it paid?

@viswa6126 Ай бұрын

My Query: using CTE and Window function. WITH CTE AS( SELECT *, CASE WHEN COUNT(*) OVER (PARTITION BY email_id) > 1 THEN 0 ELSE 1 END AS IsDuplicate FROM Employees ) SELECT employee_id,employee_name, email_id FROM CTE where isDuplicate = 0 ORDER BY employee_id

@viswa6126 Ай бұрын

After trying myself I have checked your video. I can see there are some edge case also you have given by adding your name. Thanks today have learned new about case sensitivity.

@Anubis1010-tx1ev Ай бұрын

I have a burning question that need to be answered by Industry expert.As we all know AI is becoming super advance so my question is why we put our effort to learn these sql while ai can do it faster and better way...please answer this anyone who has knowledge on this

@saibhargav7610 Ай бұрын

-- is this correct? SELECT e1.employee_id FROM employees e1 INNER JOIN (SELECT LOWER(email_id) AS email_id,count(1) AS count FROM employees GROUP BY lower(email_id) HAVING count(1) > 1) e2 ON e1.email_id = e2.email_id ORDER BY e1.employee_id

@grzegorzko55 Ай бұрын

WITH cte AS( SELECT DISTINCT EMAIL_ID FROM ( SELECT EMPLOYEE_ID ,EMPLOYEE_NAME ,UPPER(EMAIL_ID) AS EMAIL_ID ,ROW_NUMBER() OVER(PARTITION BY UPPER(EMAIL_ID) ORDER BY UPPER(EMAIL_ID)) AS rn FROM employees ) WHERE RN >1 ),cte2 AS( SELECT x.EMPLOYEE_ID ,x.EMPLOYEE_NAME ,x.EMAIL_ID --,y.email_id FROM employees x INNER JOIN cte y ON x.EMAIL_ID = y.EMAIL_ID ) SELECT * FROM employees WHERE EMPLOYEE_ID NOT IN (SELECT EMPLOYEE_ID FROM cte2);