Here are some essential SQL tips for beginners 👇👇
◆ Primary Key = Unique Key + Not Null constraint
◆ To perform case insensitive search use UPPER() function ex. UPPER(customer_name) LIKE ‘A%A’
◆ LIKE operator is for string data type
◆ COUNT(*), COUNT(1), COUNT(0) all are same
◆ All aggregate functions ignore the NULL values
◆ Aggregate functions MIN, MAX, SUM, AVG, COUNT are for int data type whereas STRING_AGG is for string data type
◆ For row level filtration use WHERE and aggregate level filtration use HAVING
◆ UNION ALL will include duplicates where as UNION excludes duplicates
◆ If the results will not have any duplicates, use UNION ALL instead of UNION
◆ We have to alias the subquery if we are using the columns in the outer select query
◆ Subqueries can be used as output with NOT IN condition.
◆ CTEs look better than subqueries. Performance wise both are same.
◆ When joining two tables , if one table has only one value then we can use 1=1 as a condition to join the tables. This will be considered as CROSS JOIN.
◆ Window functions work at ROW level.
◆ The difference between RANK() and DENSE_RANK() is that RANK() skips the rank if the values are the same.
◆ EXISTS works on true/false conditions. If the query returns at least one value, the condition is TRUE. All the records corresponding to the conditions are returned.
Like for more 😄😄
◆ Primary Key = Unique Key + Not Null constraint
◆ To perform case insensitive search use UPPER() function ex. UPPER(customer_name) LIKE ‘A%A’
◆ LIKE operator is for string data type
◆ COUNT(*), COUNT(1), COUNT(0) all are same
◆ All aggregate functions ignore the NULL values
◆ Aggregate functions MIN, MAX, SUM, AVG, COUNT are for int data type whereas STRING_AGG is for string data type
◆ For row level filtration use WHERE and aggregate level filtration use HAVING
◆ UNION ALL will include duplicates where as UNION excludes duplicates
◆ If the results will not have any duplicates, use UNION ALL instead of UNION
◆ We have to alias the subquery if we are using the columns in the outer select query
◆ Subqueries can be used as output with NOT IN condition.
◆ CTEs look better than subqueries. Performance wise both are same.
◆ When joining two tables , if one table has only one value then we can use 1=1 as a condition to join the tables. This will be considered as CROSS JOIN.
◆ Window functions work at ROW level.
◆ The difference between RANK() and DENSE_RANK() is that RANK() skips the rank if the values are the same.
◆ EXISTS works on true/false conditions. If the query returns at least one value, the condition is TRUE. All the records corresponding to the conditions are returned.
Like for more 😄😄
❤1👍1
You don't need to know everything about every data tool. Focus on what will help land you your job.
For Excel:
- IFS (all variations)
- XLOOKUP
- IMPORTRANGE (in GSheets)
- Pivot Tables
- Dynamic functions like TODAY()
For SQL:
- Sum
- Group By
- Window Functions
- CTEs
- Joins
For Tableau:
- Calculated Columns
- Sets
- Groups
- Formatting
For Power BI:
- Power Query for data transformation
- DAX (Data Analysis Expressions) for creating custom calculations
- Relationships between tables
- Creating interactive and dynamic dashboards
- Utilizing slicers and filters effectively
I have created Resources for Data Analyst 👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02
Hope it helps :)
For Excel:
- IFS (all variations)
- XLOOKUP
- IMPORTRANGE (in GSheets)
- Pivot Tables
- Dynamic functions like TODAY()
For SQL:
- Sum
- Group By
- Window Functions
- CTEs
- Joins
For Tableau:
- Calculated Columns
- Sets
- Groups
- Formatting
For Power BI:
- Power Query for data transformation
- DAX (Data Analysis Expressions) for creating custom calculations
- Relationships between tables
- Creating interactive and dynamic dashboards
- Utilizing slicers and filters effectively
I have created Resources for Data Analyst 👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02
Hope it helps :)
❤4