The most important thing the score is there to provide is an enhancement and complement to the visual cues on-screen. This means, as an example, that bombastic brass in the middle of a tender love scene would not be particularly appropriate.
A good score can also act as another tool for the director (or game designer), adding subtext that may not otherwise be available, or shifting the perspective of a scene. The latter is a very important concept, but not necessarily appropriate for all situations.
Most movies, games, etc. are pretty straightforward, and usually demand a straightforward accompaniment. Nevertheless, it is important enough that we will discuss it in-depth later.
In the last 25 years or so, films and television have discovered that a score doesn't always have to be "music" in the traditional sense that there's a melody, harmony, steady time signature, and so forth.
Sometimes a simple texture can get the point across as well or better than a more formally structured piece. Sometimes the music's lack of structure can support the picture's theme of amorphousness.
Think back on the shows of the '70s and '80s. These were written so that when the show started, people all over the house would hear the first few notes and think, "Bonanza's on! Time to gather 'round the TV!" You're probably humming one of the theme songs right now. Eventually, we transitioned to this:
This is not an inherently bad or good thing, but another tool in the composer's toolbox they can use to properly paint the scene. The "pad", or group of low strings/synths sustaining one or a handful of notes out of time below a scene, is extremely common now in movies to build suspense or otherwise denote a holding pattern, so to speak. At any rate, we now have music, textures, and sounds to play with, which opens things up quite a bit.
As noted in the preceding paragraph, it is not enough to say, "write something punchy here" or "make this sad". You have to be attentive to far more detail than just that. The best way to show this is by example. To show that I don't hate everything that you hold dear, we'll take a cue from Howard Shore's score to The Fellowship of the Ring. Now, concerning hobbits...
What is the music trying to portray here, and how does it accomplish it? Note that for the first minute, we see a hint of things to come. The camera sweeps through Bilbo's house, over things that are to be very important in a very short while (like the fireplace). During this you actually hear a low and incomplete version of the Fellowship Theme, an introduction to the extremely important concept of leitmotifs that we'll get to later. So the music is foreshadowing for us, in support of the video that does the same. Then Bilbo begins to discuss hobbits.
Hobbits are simple. They love peace, food, and good tilled earth. The music has to represent all of these things, as well as their general lazy, jovial nature. Shore chooses to go purely with a string section here to keep things simple, as a complicated instrumentation would belie the point. A fiddle (or a solo violinist playing one) is chosen as the lead instrument. Shore relies on prior knowledge here to stress the theme -- another helpful concept. Most every human who is culturally involved associates a single fiddle with a simple, rural, pastoral setting. Whether it be Ireland or the American South -- or what have you -- the sound instantly conjures up those memories. So half of his work is done for him before he ever puts notes to page.
The music itself is short and punctuated. Somewhat lively, but not too fast. Were it elongated, with sweeping strings, it would feel too sappy and lovey-dovey; were it much livelier (e.g. faster) it would feel manic, and clash with the rather lazy nature of the Shire's inhabitants. Note also that the register of the violin is nearly two octaves above where Sir Ian is speaking, which ties into our earlier discussion about avoiding the diegesis.
There are ideas in this short little piece that I don't have time to discuss, and I'm pretty sure that there are ideas I don't even hear but that we all may absorb sub-consciously. Howard Shore may be the best film composer currently practicing, so I wouldn't put it past him.
Let's listen to another example, but I want you to listen to the song, without visual accompaniment, and think about what it's portraying:
What do you hear? There's tenderness, but there's tension. Even in the first couple of notes, there's what I would call "consonant dissonance", that is, dissonance that we're pretty used to hearing in the form of a lot of upper-tertian harmonies, major 7ths and minor 9ths and so on. Pretty, but full of tension. And then that trumpet comes in. Oh goodness, that trumpet. Uan Rasey, the storied trumpeter who plays that part, was famously told by Jerry Goldsmith (the composer) to "play it sexy. But not like it's good sex!" A fitting portrayal of Chinatown, and the level of granularity that needs to be thought of in games as well.
Okay, one more. If you can, try to avoid looking at the title on this one! Just click the play button, close your eyes and tell me what you picture:
What do you hear? I hear a lot of energy, but of a very intense nature. Likely an action sequence. A lot of industrial sounds, samples of things like banging on metal drums and the like, but mixed with strings that have some Middle Eastern flair (like the little trills they do) and later on, a pan flute of all things. Okay, now look at the title -- this is the music that plays after you've completed a main assassination, and have to high-tail it back to a brotherhood base. Does it make sense to you?
I'm a bit on the fence about this one. In one sense, I get the combination of the old-world themes with the modern sound, since we're talking about taking highly technological equipment being used to send a person back to the 1100s. I like that. It also does good job of sending a sense of urgency to the player to get out of the area before major trouble ensues.
On the other hand, the very industrial, mechanical feel of the modern elements screams steampunk/industry to me, not the cybernetic technology and level of espionage at play in Assassin's Creed. Additionally, the slow, methodical, clunky battles with foot soldiers that you'll likely get into during your flight (though you can avoid them, it's difficult when they congregate directly outside the base) are an odd match. That, however, is more the fault of the game than the score, but designers must constantly think of how the two fit together. The score is not there to distract the ears while the eyes play the game.
As an aside, most of what we think of as instruments weren't invented until the 1800s at the earliest (including the piano). Rolling backward, we had things like crumhorns and sackbuts, and farther back you are restricted to gut-string guitar, lute, harp/lyre, and a basic woodwinds like a pan flute.
So pre-Renaissance settings and Bronze Age fantasy games that use heavy brass or even electric instruments are being very anachronistic. This often feels weird, even if you can't put your finger on precisely why. (300, I'm looking at you.) Assassin's Creed has a get-out-of-jail-free card due to ostensibly being set in the modern day, but it would be nice to see them pay more attention to this fact.