Brent Ozar has a great blog/newsletter.

A lot of thier examples use the stack overflow database that you can download and tinker with or query directly here: https://data.stackexchange.com/stackoverflow/query/new

I like seeing content like this and support general practices for writing queries, but one note:

1. Define all the necessary fields for SELECT instead of using SELECT *

This isn't always true. For example lets say you start with a query such as:

select
    a
    , b
    , c
    , d
from table

Objectively even if the table only has 4 columns, it is probably better to spell them out as opposed to using *, however:

select *
from (
    select
        *
        , stuff
    from (
        select
            a
            , b
            , c
            , d
        from table x
    ) y
) z

Here you can see that when using recursive sub-queries, etc., that you may want to use a * because otherwise you are going to be spelling out the same basic list multiple times in a row, and this makes it much more of a pain in the ass to go in and make changes. Instead of making them in one place, you're making them in 10 places. Also, you might want to consider creating a view that is a select * from table so that new columns automatically show up and filter into front end applications like Tableau. So now if you update a stored procedure and add a column, you don't have to go adding it in multiple places.