SQL 101

The first new thing I learnt in 2024 is SQL. I did a try back in 2022 job hunting, and was asked to write SQL quries in Databricks SA interview. I never write one single line of SQL before except for SELECT * FROM table. I studies all the SQL grammar for a week and did an interview again, and still failed. No suprise, knowing all the JOIN grammer doesn’t really help unless you really wrtie SQL in practice.

categrory	EMH	Comments
SELECT
1757	E	WHERE low_fats='Y' and recyclable ="Y" Use =
584	E	WHERE IFNULL(referee_id,0)<>2 use <> and IFNULL or WHERE referee_id IS NULL or referee_id<>2 use IS NULL
1148	E	SELECT DISTINCE
1683	E	WHERE CHAR_LENGTH(content)>15
JOIN
1378	E	FROM table1 LEFT JOIN table2 on table1.id = table2.id
1581	E	SELECT COUNT(*) AS col_new .... GROUP BY col1 WHERE col1 NOT IN (SELECT ...) nested and use NOT IN or IN
197	E	FROM Weather AS w1 JOIN Weather AS w2 ON DATEDIFF(w1.recordDate, w2.recordDate) = 1 self join to get different col comparison
1661	E	FROM table AS a1, table AS a2 WHERE a1.col='a' AND a2.col='b' Choose from the same table twice, compare rows .ROUND(AVG(xxx),3)...GROUP BY
1280	E	GROUP X Y find all rows with SAME X and Y col into a group
570	M	GROUP BY col HAVING COUNT(*) >=5 !!! Use HAVING instead of WHERE here!!!
1934	M	11. JOIN ... USING common_col_name instead of using ON
Aggregate
620	E	a != b, a%2=1
1251	E	SELECT cola+/-/*/clob You can do calc on columns on SELECT
1211	E	SUM(CASE WHEN col_a > 100 THEN col_b ELSE 0 END)
1193	M	SUBSTR(‘SQL Tutorial’, start, length)
1174	M	IF (col = ‘condition’, val_if, val_else)
ORDER BY and GROUP BY
1141	E	WHERE (date > "2019-06-27" AND date <= "2019-07-27")
1070	M	1. WHERE () in (Table with same columns) 2. Select cola=colb == Select If(cola=colb, 1, 0) 3. Select MIN(col_a)
1045	M	GROUP BY col HAVING COUNT(distinct col2) = (SELECT COUNT(col2) FROM tab1)