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

SQL questions recently asked in Motocorp interview.
1. We need to mask first 12 digits of card number.
2. Need to select employee names with same salary.
Create table syntax
CREATE TABLE cards (card_number BIGINT)
INSERT INTO cards VALUES (1234567812345678),(2345678923456789),(3456789034567890)
CREATE TABLE Employee (employee_id INT,ename VARCHAR(50),salary INT)
INSERT INTO Employee VALUES (3, 'Bob', 60000),(4, 'Diana', 70000),(5, 'Eve', 60000),(6, 'Frank', 80000),(7, 'Grace', 70000),(8, 'Henry', 90000)
For more SQL interview questions. Check out our playlist.
• SQL Interview Questions
Contact us:
[e-mail]
Follow us on
Instagram : cloudchallengers
Facebook : cloudchallengers
LinkedIn : linkedin.com/company/cloudchallengers

[@abhishekkrpaul]

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

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

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

[@sravankumar1767]

Superb explanation 👌 👏 👍

[@srinivasulum414]

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

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

[@pallavimohapatra7241]

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

[@entertainmenthub5066]

Great Job Sir Ji

[@pravinshinde1891]

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

[@pavankumarreddy7866]

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

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

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

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

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

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

[@shraddhadhakad1154]

SELECT *, CONCAT(REPEAT('*', 4) ,RIGHT(CARD_NUMBER,4))as new_Cardnumber 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;

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

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