📝 Learn: 1/60 days
🏋🏻♀️ SQL Revision (Basics -> Intermediate)
• Comparison operation even works for letters. eg: where month_name > 'J' (This will include January). But NULL operations are not valid like greater than check for the null column.
• ILIKE is a logical operator for case insensitive matches.
• IS NULL check.
• COUNT(column_name) will give NOT NULL and NON UNIQUE values.
• COALESCE( .... ) any number of arg returns the first NOT NULL argument.
• WHERE doesn't work with aggregate columns that's where the HAVING clause comes in
• If-then equivalent is CASE- WHEN-THEN-ELSE[Optional]- END in SQL. ⭐️ It can be used inside SELECT, GROUP BY, Inside aggregate functions,
COUNT(CASE WHEN year = 'FR' THEN 1 ELSE NULL END)
• Declare Variables in SQL using SET @Variable1 = 'test'
🥊 Questions
• Write a query that returns all rows for songs that were on the charts in 2013 and do not contain the letter "a".
Ans: NOT ILIKE '%a%'
• How to insert a slice of a table into another table?
Ans: Insert into table (col1, col2 ...) values (select col1, col2... )
• Write a query that counts the number of 300lb+ players for each of the following regions: West Coast (CA, OR, WA), Texas, and Other (everywhere else).
Ans: group by case ... end
Next: https://mode.com/sql-tutorial/sql-case/
• Comparison operation even works for letters. eg: where month_name > 'J' (This will include January). But NULL operations are not valid like greater than check for the null column.
• ILIKE is a logical operator for case insensitive matches.
• IS NULL check.
• COUNT(column_name) will give NOT NULL and NON UNIQUE values.
• COALESCE( .... ) any number of arg returns the first NOT NULL argument.
• WHERE doesn't work with aggregate columns that's where the HAVING clause comes in
• If-then equivalent is CASE- WHEN-THEN-ELSE[Optional]- END in SQL. ⭐️ It can be used inside SELECT, GROUP BY, Inside aggregate functions,
COUNT(CASE WHEN year = 'FR' THEN 1 ELSE NULL END)
• Declare Variables in SQL using SET @Variable1 = 'test'
🥊 Questions
• Write a query that returns all rows for songs that were on the charts in 2013 and do not contain the letter "a".
Ans: NOT ILIKE '%a%'
• How to insert a slice of a table into another table?
Ans: Insert into table (col1, col2 ...) values (select col1, col2... )
• Write a query that counts the number of 300lb+ players for each of the following regions: West Coast (CA, OR, WA), Texas, and Other (everywhere else).
Ans: group by case ... end
Next: https://mode.com/sql-tutorial/sql-case/
