The Polish National Anthem has an upbeat, cheerful tune, but the translation of the lyrics towards the end is anything but cheerful.
Father, in tears Says to his Basia "Just listen, it seems that our people Are beating the drums" March, march
Gilbert and Sullivan have "With Catlike Tread" from The Pirates of Penzance. A song about how sneakily the titular pirates are breaking into the Major General's home... annotated Fortissimo (Italian, and musician, for "Really Loud"), and set to the sound of blaring trumpets, rumbling kettle drums, and crashing cymbals.
Joseph Haydn had a bit of fun with this in the Agnus Dei portion of his Creation Mass. The lyrics (translated from Latin) are "Lamb of God, that taketh away the sins of the world, have mercy upon us". Haydn proceeded to write the happiest Agnus Dei he could think of.
The words of the first number from Claudio Monteverdi's Scherzi musicali cioè arie et madrigali, "Maladetto sia l'aspetto", translate as "Cursed be the looks that have set my heart on fire. Alas! unhappy me, for I suffer cruel torment and will surely die, nor can any but you ease my suffering," while set to a lively and carefree melody in the major key.
"Batti, batti o bel Masetto" ("Beat me, oh lovely Masetto") from Don Giovanni is a calm and tender love song in which a woman begs her fiance to beat her. Though to be fair, the subtext of the aria basically comes down to a teasing, "You love me way too much to beat me, even if I did cheat on you like you think I did."
"Der Hölle Rache", or "Hell's Vengeance", is one of those classical pieces everyone recognizes but nobody can name. It's an aria from the opera The Magic Flute, in which an enraged queen threatens damnation and disownment upon her daughter if the girl doesn't kill one of the queen's enemies. The general tone of the piece, however, is somewhat less than fiendish.
Carl Orff's Carmina Burana contains some of the most instantly recognisable music in the world. The first four minutes, better known as "O Fortuna", is quite probably the most famously epic piece of music ever written, and the platinum-iridium standard for Ominous Latin Chanting besides. The lyrics? The Carmina Burana covers a wide variety of subjects, and most are utterly mundane. "O Fortuna" itself is about bad luck. More specifically, it's the lament of a student emo-ing out because he just gambled away the last of his drinking money.
Any serious opera by Gioachino Rossini. It seems the man was practically incapable of writing anything not upbeat and cheerful, even if the lyrics call for vengeance, anguish, distress, fear, etc.
Anna Russell's parody-Lied (German art song), entitled "Schlumph", is in a tragic musical style, with heavy chordal piano textures and doleful harmonies that never leave the minor key. The words are... rather silly, a combination of stereotypical tourist German vocabulary with pseudo-German gobbledigook. Incidentally, while "Schlumph" doesn't mean anything, schlumpf is German for "smurf".
The trailer for The Social Network features a harmonized choral arrangement of Radiohead's "Creep", a song about a man wishing he could have a woman he thinks is too good for him, by the Belgian choir Scala & Kolacny Brothers.note Incidentally, it's from their 2004 album On The Rocks It's weirdly haunting and awesome.
The rather sweet lullaby Mariya sings at the end of Pyotr Ilyich Tchaikovsky's Mazeppa. It would be quite beautiful if she wasn't A) completely mad, B) holding and rocking a dying man who she thinks is a child, who dies half-way through, and C) about to freeze to death.
On Christopher Tin's 2009 album Calling All Dawns, there is a song called "Se E Pra Vir Que Venha", which has a rather upbeat tempo and is even a bit of an ear worm, but the Portuguese lyrics? It's about the narrator waiting for her own death to come, and she's even quite joyful about it. Not long after that, though, there's "Rassemblons-Nous", which is a pumping anthemabout the opposite.
The opening aria in Act III of Giuseppe Verdi's Rigoletto, "La donna e mobile", is an incredibly cheery song. It's sung by the Duke, the villain of the show, and the lyrics are effectively him expressing casual misogyny.
" from The Pirates of Penzance. A song about how sneakily the titular pirates are breaking into the Major General's home... annotated Fortissimo (Italian, and musician, for "Really Loud"), and set to the sound of blaring trumpets, rumbling kettle drums, and crashing cymbals.