Order You Should write the query
- from
- where
- group by
- select
- having
- order by
Select
Distinct
if you use two columns with distinct only the combination of columns will be unique
even though there are duplicated values for last name department_id will be different
Case
- every case statement will generate an additional column
windowing_functions
Where
Null and not Null
in and not in
- instead of writing multiple where clauses we can use
inandnot it
between and not between
Like
Any vs All
you can think of any and all like this,
any and all returns a tuple of values.
the difference is
any ⇒ (10 or 20 or 30 or 40) all ⇒ (10 and 20 and 30 and 40)
so when you are comparing values with any it just need to match one case. but if you are using all it needs to match all the cases
Exsits
Operators
Sets and Union
- order by first column and second column
Intersect
Minus Operator
Unions_and_Joins
Subqueries and CTE
Correlated subqueries
Pivots
with case statements
Example 1
Example 2
-
but our avg calculations are in 20s. it should be in 70s or 80s. so something wrong with our calculation
-
problem is this 0 values
-
so we’ll replace those 0 values with null values
-
just delete the else in the case statements and other values will be null
Functions
String Functions
- like is not a function. it’s actually a keyword. anyway we include it here because it do something similar to function
regex
Date function
Rolling Calculations
- rollup
- cumulative sums
- moving average
- moving average last 3 years
- how this is working?
- remember window functions works one row at a time.
- so when it reaches 2 row and do the sum it will do the current row and all the previous rows and sum it up.
- when it goes to the 3 row it will get the value in 3 row and all the previous columns values and sum it up
that’s how you can use a window function to calculate the cumalative sum
but this is taking all the prior years average. we only want last 3 years average
Order by
- this means that last names are in order
