Accenture SQL Interview Question | Database Case Sensitivity vs Insensitivity

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', '[e-mail]');
INSERT INTO employees (employee_id,employee_name, email_id) VALUES ('102','Josh Day', '[e-mail]');
INSERT INTO employees (employee_id,employee_name, email_id) VALUES ('103','Sean Mann', '[e-mail]');
INSERT INTO employees (employee_id,employee_name, email_id) VALUES ('104','Evan Blake', '[e-mail]');
INSERT INTO employees (employee_id,employee_name, email_id) VALUES ('105','Toby Scott', '[e-mail]');
INSERT INTO employees (employee_id,employee_name, email_id) VALUES ('106','Anjali Chouhan', '[e-mail]');
INSERT INTO employees (employee_id,employee_name, email_id) VALUES ('107','Ankit Bansal', '[e-mail]');
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

[@ankitbansal6]

Please like the video for more interview questions.

[@PinaakGoel]

with cte as (select *, count(*) over(partition by lower(email_id)) as cnt
from employees)
select * from cte
where cnt = 1 or lower(email_id) = email_id

[@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

[@abhishekjain1418]

will work only in case of case sensitive

[@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🙏

[@muhammedjasir2659]

This will work ?
not sure ? any one confirm on this
delete from emploee
where id not in (
select id from emp E join F
on E. email_id = F. email_id
where E.ID > F.ID
and E.email_id not in (select upper(email_id) from emp )
)

[@arpanscreations6954]

parition by lower/upper(email_id) will work for both case sensitive and insensitive email_ids. Correct me If I'm wrong.

[@ChandrashekarReddyY-hp4rl]

Thank you.Nice Explanation.

[@nhinuvalerie-anne7781]

Please let me know your opinion on my following answer, taking advantage of the rank() function:
WITH cte
(SELECT *, ROW_NUMBER() OVER (partition by email_id) AS rnu, RANK() OVER (partition by email_id) AS rnk,
FROM employees)
SELECT employee_id, employee_name, email_id FROM cte
WHERE rnu = rnk
ORDER BY email_id;

[@anandmahajan943]

@AnkitBansal , waiting for the Redshift course ❤

[@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)

[@vijaygupta7059]

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

[@sukanyaiyer2671]

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]

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;

[@arindamIT]

SELECT temp.employee_id, temp.employee_name, temp.email_id FROM employees temp
MINUS
SELECT temp1.employee_id, temp1.employee_name, temp1.email_id FROM (SELECT DISTINCT employee_id, employee_name, email_id ,dense_rank() over(partition by lower(email_id) order by employee_id) as r from employees) TEMP1 WHERE
TEMP1.R>1 AND TEMP1.EMAIL_ID=UPPER(TEMP1.EMAIL_ID);

[@parmoddhiman678]

with cte as (select*, ascii(email_id)as ascaii,
rank() over(partition by email_id order by ascii(email_id) desc) as rn
from employees)
select employee_id, employee_name, email_id from cte where rn=1

[@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

[@Damon-007]

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;

[@tanmaymodi8284]

Awesome, By the way I made this query:
select * from employees
where ASCII(email_id) ASCII(upper(email_id))
union
select * from employees where email_id in (
select email_id from employees
group by email_id
having count(email_id) = 1
)

[@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

[@AmanRaj-p8w]

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)

[@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);

[@Apna_tahlka_123]

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

[@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);

[@sambeetpanda6919]

this question asked for freshers or experienced?? ankit bhaiya

[@simranpreetsingh3052]

It probably asks for an experience candidate.

[@shivaprasad-kn3kw]

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

[@atharvabhangre9044]

Great!!!

[@srikanthadepu8728]

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

[@yatharthchauhan7407]

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

[@ayushtrivedi7496]

I found gold!

[@rk-ej9ep]

Awesome..as usually..

[@ankitbansal6]

Thank you! Cheers!

[@mr.pingpong502]

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

[@ishika7585]

this is wrong since it already treating it as same

[@mr.pingpong502]

@@ishika7585 Not sure what you are expecting but i am getting the right results. There are 2 filters one for case validation and second for rank. Please let me know if there is a gap in my answer.

[@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

[@nipunshetty9640]

THE SQL ARTIST ANKIT BANSAL❤❤❤

[@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?

[@shatirdimag1011]

Best solution

[@ayyappansri]

Splendid 🎉

[@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;

[@shreyagupta.1203]

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

[@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