Hero Motocorp SQL Interview Questions using REPLICATE(), RIGHT() and CONCAT() Functions

[@Harini-x7t]

Q1
with cte as(
select salary,count(*) as cnt
from EP
group by salary
having count(*) > 1 )
select ename from EP where salary in (
select salary from cte )
Q2 select card_number, replicate('*',12) + RIGHT(cast(card_number as varchar(20)),4) as card_no from cards

[@CloudChallengers]

@Harini-x7t, Thanks for sharing the alternative approach

[@adityasahasrabuddhe70]

with cte AS (select salary,count(salary) from Employee group by salary having count(salary) > 1 )
select ename from Employee where salary in (select salary from cte)

[@abhishekkrpaul]

2. SELECT ename
FROM employee
WHERE salary IN (SELECT salary
FROM employee
GROUP BY salary
HAVING count(*) >1)

[@sravankumar1767]

Superb explanation 👌 👏 👍

[@entertainmenthub5066]

Great Job Sir Ji

[@shyamshivakumar7807]

1) select concat(repeat("*",length(card_number)-4),substr(card_number,length(card_number)-3,length(card_number))) as new_card_number from cards
2) with cte as (
select *,
count(salary) over(partition by salary) as cnt
from emp_que)
select employee_id,ename from cte where cnt!=1

[@pallavimohapatra2697]

1. select repeat('*', 12) || SUBSTRING(card_number::text FROM 12 + 1) as card_number from cards
2. with cte as(select ename, count(salary) over(partition by salary) as cnt from Employee)
select ename from cte where cnt != 1

[@chandanpatra1053]

please cover all the hackerrank medium & advanced level questions.If possible start a new series called "Hackerrank" series video

[@CloudChallengers]

@chandanpatra1053, Definitely, I will be working on this in future.

[@srinivasulum414]

with cte as (
select *,count(*)over( partition by salary )As rno from Employee
)
select ename from cte
where rno>1

[@AbinashPatra-n2i]

select ename
from Employee
where salary in(select salary
from Employee
group by salary
having count(*) >1)

[@pritambanerjee6957]

Q1
SELECT REPLACE(card_number,SUBSTRING(card_number,1,12),REPEAT("*",LENGTH(SUBSTRING(card_number,1,12)))) AS masked_card_number
FROM cards;
Q2
SELECT ename FROM Employee WHERE salary IN(
SELECT salary FROM Employee GROUP BY salary HAVING COUNT(ename) > 1);

[@suprisahana]

Can you share the query for oracle database, as we don’t have replica keyword in Oracle.

[@CloudChallengers]

SELECT card_number, CONCAT(LPAD('', LENGTH(card_number) - 4, '*'),SUBSTR(card_number, -4)) AS newcard_number FROM cards
Try this

[@sravankumar1767]

SELECT
CASE
WHEN LEN(column_name) > 12 THEN REPLICATE('X', 12) + RIGHT(column_name, LEN(column_name) - 12)
ELSE REPLICATE('X', LEN(column_name))
END AS masked_column
FROM
table_name;

[@CloudChallengers]

@sravankumar1767, Thanks for sharing the different approach

[@pavankumarreddy7866]

select REPLICATE('*',12)+RIGHT(card_number,4) as new from cards

[@june17you]

Can you please share the query for masking in MySQL as well? I tried the same but it's not working. Thank you

[@CloudChallengers]

SELECT card_number, CONCAT(REPEAT('*', LENGTH(card_number) - 4), RIGHT(card_number, 4)) AS newcard_number FROM cards
You can try this

[@pravinshinde1891]

select lpad('*',12,'*')||''||rpad(CARD_NUMBER,4) as card_number from cards

[@sravankumar1767]

SELECT ename
FROM employees
WHERE salary IN (
SELECT salary
FROM employees
GROUP BY salary
HAVING COUNT(*) > 1
)
ORDER BY salary;

[@CloudChallengers]

@sravankumar1767, Thanks for sharing the different approach

[@beedalabharathi3120]

Select card_number,lpad(substr(card_number,-4), length (card_number),'*')as New _card_number from table

[@ashishpise4910]

WITH CTE AS
(
SELECT *,COUNT(SALARY) OVER(PARTITION BY SALARY) AS Salwise_count
FROM Employee
)
SELECT ename FROM CTE
WHERE Salwise_count=2
ORDER BY ename;

[@dasubabuch1596]

1)
select lpad(substr(card_number,13,14),16,'*'), card_number from cards;
2)
with t as
(
select salary, count(*)
from employees
group by salary
having count(*) >=2
),
t1 as
(
select ename from employees where salary in (select salary from t)
)
select * from t1;

[@HARSHRAJ-gp6ve]

Ouestion 1
with cte as(
select REPEAT('*',8) AS x2,SUBSTRING(card_number,13,4) as x1 FROM cards
)
select CONCAT(x2,x1) as card_number FROM cte;
Question 2
with cte as(
select ename,salary FROM Employee
), cte1 as(
select ename as name1,salary as salary1 FROM Employee
),cte2 as(
select ename,name1 FROM cte JOIN cte1 ON cte.salary=cte1.salary1 and cte.ename!=cte1.name1
),cte3 as(
select ename FROM cte2
union all
select name1 FROM cte2
)
select DISTINCT(ename) FROM cte3;

[@theinsightminer08]

ques1:
SELECT CONCAT(REGEXP_REPLACE(LEFT(card_number,12),'[0-9]','*'),'',RIGHT(card_number,4))
FROM cards;
ques2:
SELECT
ename
FROM Employee
WHERE salary IN
(SELECT
salary
FROM Employee
GROUP BY salary
HAVING COUNT(salary) > 1);

[@shraddhadhakad1154]

SELECT *, CONCAT(REPEAT('*', 4) ,RIGHT(CARD_NUMBER,4))as new_Cardnumber FROM cards;

[@landchennai8549]

SELECT *
FROM #Employee WHERE salary IN (
SELECT salary
FROM #Employee
GROUP BY salary
HAVING COUNT(1) > 1)

[@Sachin_Sambare]

1st Oue. Solution in Oracle SQL -
SELECT CARD_NUMBER,REGEXP_REPLACE(SKP,'[^*]','')||MM AS NEW_CARD FROM (
SELECT C.*,LPAD(CARD_NUMBER,28,'*') AS SKP,SUBSTR(CARD_NUMBER,-4) AS MM FROM CARDS C);

[@hulhoop7197]

SELECT
*,
STUFF(card_number, 1, 12, REPLICATE('*', 12)) AS masked_number
FROM Cards;

[@hairavyadav6579]

My Approach
select c1.* from cards c1 join cards c2 on c1.salary = c2.salary where c1.employee_id != c2.employee_id;