Issuing a general SQL command

It's possible you may want to select on something sufficiently abstract that I haven't provided a built-in query for it. For instance, suppose you wanted to see all the classical music pieces shorter than five minutes. If you're conversant with SQL and you know something about how the Paloma database is put together, you can always do this on the fly.

First, select "General SQL query" from the Queries menu. You'll get a dialog that just consists of a large field, into which you can type your SQL command.

Type in the SQL command you want to issue. In this example, the command is:

    select songs.song_id, songs.writer, 
    songs.name, songs.length
    from songs, cd_tracks, cds_extension
    where songs.song_id = cd_tracks.song_id
    and cd_tracks.cd_id = cds_extension.cd_id
    and cds_extension.genre = 11
    and songs.length < 300;

This deserves some explanation.

    select songs.song_id, songs.writer, 
    songs.name, songs.length

This specifies the columns in the query. Except for the first one, these are the columns that will appear in the generated playlist. The first one is the primary key of the songs table, and must appear first in any query on songs. (You may also build a playlist where the first column is cd_id or artist_id. If the first column is not one of these three primary keys, you'll still get a result, but it's just a table output--you can't do anything with it.)

    from songs, cd_tracks, cds_extension

These are the tables that are mentioned in the where clause, below. This is just syntax required by SQL.

    where songs.song_id = cd_tracks.song_id
    and cd_tracks.cd_id = cds_extension.cd_id
    and cds_extension.genre = 11
    and songs.length < 300;

This is the where clause of the select statement, which both restricts the data returned and also specifies the relationships of the various tables. The first two lines:

    where songs.song_id = cd_tracks.song_id
    and cd_tracks.cd_id = cds_extension.cd_id

specify that the cd_tracks table connects the songs table to the cd_extensions table by their two primary keys, song_id and cd_id. This is called a "join" in SQL nomenclature, and is a very important concept in relational databases. The next two lines:

    and cds_extension.genre = 11
    and songs.length < 300;

qualify the data returned, such that the genre field on the CD is of type 11, which happens to be Classical, and the length field for the songs is less than 300 seconds, or five minutes. (There is another table, combo_items, that records the list of genre names by index number. It would have been possible, using another join, to test against the name "Classical" instead of the value 11, but it was easier just to type in the value 11 in this example.)

And here is your playlist: