Cacher is the code snippet organizer for pro developers

• Some notes when solving Hackerrand SQL problem
• Based on mySQL

--- REGEXP for pattern matching in my SQL
X REGEXP '^[abc]' 

--- '^[^abc]', if do not want to start with abc
ORDER BY X ASC, Y DESC LIMIT 1
CHAR_LENGTH(X)
LEFT(X,1) in ('a','b','c')
RIGHT(X,1)
SUBSTRING(X,-5,5)

--- user variables
set @x := 0
set @y := (select count(1) from T) --- use select will print y

--- change itself in while
while (select @x:=@x+1) --- run row by row?
between @x/2 and @x/2+1

--- or change itself in select (add an extra column)
select avg(T.price) 
from (
select data, @x := @x+1 as haha from T --- run row by row
) T
where T.haha in (a, b) 

--- group by
--- first group, then order the grouped item
select count(x) as cnt from data group by x order by cnt

--- group by using the newly generated column
select a*b as x, count(x) from data group by x limit 1;

--- same functions work for digits and strings
replace(x,' 0','') -- remove 0 in some numbers
min(x) --- or max(x), choose non-null if possible

--- join condition for rows, =, between
from data1 inner join data2 on data1.x between data2.y and data2.z

--- a table inside every MySQL instance
select repeat('* ',@row:=@row+1) from information_schema.tables