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 ๐๐
Post #2349
4.23K
- โค 5