Here's the basic premise. If you need to travel on a given service, in a given class, on a given day you need to book as far in advance as possible. Every time you vary one of those provisions, makes it 'easier' to get on a train.
How about for short distance,popular routes (like Delhi-Agra,for example)?
In that example is not about how popular the service, or the length of the route - what matters is how many services there are a day.
Do they also need to be booked far in advance,
It comes back to which service you are prepared to take, and in which class.
or can you just go to the station and buy a ticket?
For the most part, you really can't do that in any practicable way.
Are they all seated only,or can you stand up if seats are full?
The trains are fully seated.
Do you need an RAC ticket in that situation?
An RAC ticket will get you on the train. It does not guarantee you a berth, and you may be sharing a seat.
Let's say you wanted to travel Delhi - Agra this Tuesday.
You're prepared to leave at 4:30am. You can't get a seat in 1A, but you can make a booking in every other class.
You want to leave between 10:00am and 12:00pm - sorry, not a ticket to be had.
Some trains will include unreserved carriages - so if you are prepared to fight the crowd at the ticket counter, and then have a cheek-by-jowl journey to Agra - you can.
And there are further conditions - most trains have a Tatkal quota which is released the day prior to the train leaving its originating station. And foreign tourists can access the Foreign Tourist Quota which has a limited number of seats on a very limited number of services. And even then, some other foreign tourist may have booked those tickets before you.