MYSQL CHEET SHEET FOR INTERVIEWS

STRING FUNCTION :

 SELECT CONCAT('MySQL','CONCAT'); 

 select length('MYSQL');   : 5

 select left('MYSQL',2);    : MY

 select RIGHT('MYSQL',2); QL

 select lower('MSQL')       :mysql

select replace('2021-01-01','2021','2022') : 2022-01-01

select substring('TARUN',1,2) : TA

select substring_index('2022-02-02','-',1); :  2022

select substring_index('2022-02-02','-',2); :  2022-02

select position('@' in 'tarun@gmail.com') : 6

SELECT RPAD('mysql',10,'*');   mysql***** 

Masking at value : 

set @email_id='tarun.rajput@google.com';

select concat(RPAD(substring(@email_id,1,1),6,'*'),'', substring(@email_id,position('@' in @email_id),
length(@email_id)- position('@' in @email_id)+1)) as email ;

OUTPUT : t****@google.com

select reverse('GOOGLE')  : ELGOOG

DATE :

ADDDATE
select '2021-01-01' + interval 1 DAY as DATADATE  : 2021-01-02;
select adddate('2021-01-01',interval 1 DAY)  : 2021-01-02
select adddate('2021-01-01',interval 1 hour) : 2021-01-02

DATEDIFF

select datediff('2022-01-01','2021-01-01') : 365

DATE_FORMAT

select DATE_FORMAT('2022-01-01','%Y/%m/%d') : 2022/01/01

DAYNAME

select DAYNAME('2022-02-09') : 'Wednesday'

DAYOFWEEK

select DAYOFWEEK('2022-02-09') : 4

EXTRACT

SELECT EXTRACT(MONTH FROM '2022-02-09 13:50:00') MONTH;
SELECT EXTRACT(YEAR FROM '2022-02-09 13:50:00') YEAR;
SELECT EXTRACT(DAY FROM '2022-02-09 13:50:00') YEAR;

WEEKDAY

select WEEKDAY('2022-02-09') : 2 WEEK

WEEK

select WEEK('2022-02-09') : 6

Comparison Function

 SELECT GREATEST(10, 20, 30),  -- 30
       LEAST(10, 20, 30); -- 10
 

SELECT GREATEST(10, null, 30),  -- null
       LEAST(10, null , 30); -- null