Please provide the tips to write good sql queries as refer to performance wise

as I know some tips as follow

1:- in like statement avoid '%%' instead use '%' as possible
2:- appropriate use of INDEXES on select queries as for column where,group by
order by , having is required
3:- use explain to identify possible indexes
4:- avoid subqueries instead of these use inner join that is more efficient

NOTE:- indexes not always is helpful to increase performance

Please tell more tips as possible