BioShock
Leisure Suit Larry – Magna Cum Laude
Duke Nukem Forever

I am yet to play it but I imagine Empire of Sin focuses a lot on the alcohol trade and consumption as its plot line, as doe's Mafia 1 come to think of it.

You could just get drunk yourself, and try playing any game you choose. Could be a great source of entertainment in and of itself, in addition to the contents of the actual game....

Esp. drunk couch MP is fun... but mind the pandemic.

In Gothic there are beer and smoke (weed).

Stardew Valley has a bunch of different alcoholic beverages you can craft (wine, pale ale, beer), though it mostly makes you slow so it's better to sell it or gift it to people.

Kingdom Come: Deliverance has a "Drinking" skill and some perks ;)

Cyberpunk has an alcohol mechanic. The effects are entirely ignorable, but negative and your screen gets wobbly for about 30 seconds. That's it.

the Metal gear solid games allow you to smoke to calm your nerves, which works great with a sniper rifle, reducing the movement of the reticle.

Red Dead Redemption 2 has alcohol and cigarettes. I don't think it's necessary as good as banned - cigarettes seem to have come out of fashion eventually through mixture of social and governmental pressure. Alcohol is still with us :)

In HuniePop you can buy drinks for your dates in the evenings.

Not drinking, but this mod - https://www.nexusmods.com/skyrim/mods/65259 - for Skyrim is a quest chain to become a Skooma drug lord

In Metal Gear Solid you can smoke to show some traps (laser beams), but it gradually depletes life.

1)
The main character of Dragon Fin Soup is a boozehound, and I think you can consume her favorite 'potions' in the game, too. I don't remember to what effect, I didn't play the game for long.

The Neverwinter Nights titles feature alcoholic beverages as well. Unless I confuse it with another game, they give you a temporary boost and malus at the same time, like +1 STR, -1 INT or something. I might be wrong though, I usually just ignore those drinks.

Forgotten Realms Unlimited Adventures allows module creators to trigger an event if players consume a certain amount of alcohol in taverns. Whether it is good or bad is entirely up to the designer.



2)
Because there are a lot of double standards or ambivalences concerning alcohol in our societies in general? Anyway, not what you asked, but The Illusive Man in Mass Effect 2 is a cigarette smoker, and the Gothic games are all about smoking weeds. ;) On the other hand, I know more than one indie game that portrays alcoholism as a serious problem, too. Won't name the titles because that might be slightly spoilerish.

Hm, that vaguely reminds me of another game, probably an RPG, in which you can smoke cigarettes to regain some points in some stat bar like mana or sanity or whatever, at a small cost (hp?). But I can't put my finger on which game that was.

I actually liked Witcher implementation of alcohol linked to conversations - you had to be careful and not overdo it, otherwise the effort goes to waste :) Quite clever way of intertwining alcohol into the game mechanics.