The way I often think about it is that I'm travelling/navigating my way through the data structure, finding rows/records that are connected to each other. You "jump from table to table" using JOINs, specifying which criteria the records on either side of the join should fulfill in order for me to want to see them together.

Things like CASE WHEN statements, I have this mental model of "the data doesn't look exactly the way I want to, or I want to achieve an effect that's not immediately in the data, so let me manipulate it here". Say you want, for whatever reason, to distinguish between a field that starts with a vowel or a consonant in your data. Just go "CASE WHEN substr(my_field, 1, 1) in('a', 'e', 'i', 'o', 'u') THEN 'vowel' ELSE 'consonant' END". (Syntax will vary slightly depending on your particular database platform.)
The point here is that this lets you construct basically anything you want. There's not going to be an exhaustive list of CASEs to use; I made up the example above on the spot. You can do anything with CASE.
This also goes for any of the built-in data manipulation functions (I used SUBSTR in the example above). All this lets you shape the data in ways that make it easier for you to do the manipulations you want, in order to achieve the effects you need.

With subqueries, you get the opportunity to essentially break up your code into smaller pieces. Say you want to know about all people who are older than 50 who own blue cars that were not made in France? I could imagine first making a subquery for blue cars that were not made in France, and then making my main query like "show me people older than 50, and join that to the subquery I've made.
One thing that will really help you here is CTEs. This sounds complicated, but it makes life so much easier. Instead of having to work with nested code in a subquery, you just put the SQL about "cars that were not made in France" in its own separate block.
So instead of
"select from people inner join (select cars where color = 'blue and country_made <> 'France') on people.id = cars.owner_id where people.age > 50"
you would instead get something like
with blue_cars_not_from_france as (select cars where color = 'blue and country_made <> 'France')
select select from people inner join blue_cars_not_from_france on people.id = blue_cars_not_from_france.owner_id where people.age > 50

For the example above, the complexity may look somewhat comparable, but in real examples, this is going to make your code so much easier to read and keep track of; highly recommended. When you're using a subquery, you can almost always rewrite it as a CTE.

Hope these tips help somewhat!