Code Stylings

Another problem with being an idiot developer is I don't have the wherewithal or academic background to really question code style. I find that for practical, in-the-trenches issues, I can often contribute something useful to how variables, classes, etc. should be named, but when it comes to things like the good, bad, and ugly of Hungarian naming, there's a high-level theoretical argument going on that I just don't grasp, and which seems to be evergreen. People were writing about it in 1998, and they were writing about it in 2004, and I'm sure they'll continue writing about it for the forseeable future. What can an idiot hope to contribute to a dialogue of such minds? I just go with whatever the conventions are in place at my current contract, and try to learn from the architects and other coders why certain decisions were made.

This applies to all types of code, too. Take this snippet of SQL, for example:

SELECT
elg.SUBSCRIBER_ID, elg.LAST_NAME, elg.FIRST_NAME, elg.DOB, en.ADDRESS1, en.ADDRESS2, en.CITY, en.STATE, en.ZIP,
p.NAME AS PRODUCT, l.NAME AS LOCATION
FROM
dbo.ELIGIBILITY AS elg INNER JOIN
SomeDB.dbo.ENROLLED_USERS AS en
ON en.ENROLLED_USER_ID = elg.ENROLLED_USER_ID LEFT OUTER JOIN
SomeOtherDB.dbo.PRODUCTS AS p ON p.PRODUCT_ID = elg.PRODUCT_ID
INNER JOIN
SomeOtherDB.dbo.LOCATIONS AS l ON l.LOCATION_ID = elg.LOCATION_ID

I don't have an issue, per se, with how this code is written. I mean, personally I find it difficult to read, but that's purely subjective. Also, I'm an idiot, so everything's hard for me. My question is more, why do it this way at all, with the TABLE as ALIAS stuff? Is there some efficiency gained by doing it this way, or is it just intended to save typing? Also, why name tables in ALL CAPS? Doesn't that make it harder to differentiate SQL key words from database objects? I always suspect that someone had a class that told them about why this was a better way to do it. It's tough to find explanations for this sort of thing by Googling it, and even when I do find an explanation, usually on an MSDN blog or something, I also usually find a Style Jihad going on in the comments that isn't helpful at all.