Best Practices:

1. Use UNION ALL instead of UNION unless you want to eliminate duplicate selected rows.
2. Avoid using SELECT * FROM table. Define the column names instead.
3. Avoid using DISTINCT if you can tolerate having duplicate rows returned by a query.
4. Don't mix LEFT and RIGHT OUTER JOIN in the same query.
5. Use bind variables.
6. Use SELECT... BULK COLLECT INTO ... FROM ...  only when you are sure that the result set will be of a reasonable size, otherwise, use an explicit cursor and fetch with the LIMIT clause. (more...)
7. Use the following query to check for existence based on a condition:
   1.  select count(*) into l_cnt

          from dual

         where exists ( select NULL

                          from emp

                         where sal > 4000 );

      that'll return 0 or 1.  The where exists short circuts after finding the first row. (more...)

 Worst Practices:

1. Try to get published.
2. Believe gurus instead of proving things with code.
3. Use weirdo design practices that replace "obsolete" relational design.
4. Try to keep your code dbms-neutral
5. Put things that belong in the database, like referential integrity, into the application layer.