SQL is such a basic and useful language but the underlying thinking is non-intuitive when you come from imperative languages like Java, Ruby and similar.
SQL is centered around sets and operations on them. The straight forward solution might not be the best one.
Limit
Let’s say we need the maximum value in a certain set. Easy:
select max(value) from table
But what if we need the row with the maximum value? Just adding the other columns won’t work since aggregations only work with other aggregations and group bys. Joining with the same table may be straight forward but better is to not do any joins:
select * from (select * from table order by value desc) where rownum<=1
Group by and having
Even duplicate values can be found without joining:
select value from table group by value having count(*) > 1
Grouping is a powerful operation in SQL land:
select max(value), TO_CHAR(time, 'YYYY-MM') from table group by TO_CHAR(time, 'YYYY-MM')
Finding us the maximum value in each month.
Mapping with outer joins
SQL is also good for calculations. Say we have one table with values and one with a mapping like a precalculated log table. Joining both gets the log of each of your values:
select t.value, log.y from table t left outer join log_table log on t.value=log.x
Simple calculations
We can even use a linear interpolation between two values. Say we have only the function values stored for integers but we values between them and these values between them can be interpolated linearly.
select t.value, (t.value-floor(t.value))*f.y + (ceil(t.value)-t.value)*g.y from table t left outer join function_table f on floor(t.value)=f.x left outer join function_table g on ceil(t.value)=g.x
When you need to calculate for large sets of values and insert them into another table it might be better to calculate in SQL and insert in one step without all the conversion and wrapping stuff present in programming languages.
Conditions
Another often overlooked feature is to use a condition:
select case when MOD(t.value, 2) = 0 then 'divisible by 2' else 'not divisible by 2' end from table t
These handful operations are my basic toolbox when working with SQL, almost all queries I need can be formulated with them.
Dates and timestamps
One last reminder: when you work with time always specify the wanted time zone in your query.
Schneide Blog 