18 Manipulating

select artist, min(year), max(year), count(album) from albums group by artist

Our Abba albums came out between 1974 and 1980. Our Alice Cooper albums came out

between 1971 and 2003, and there were 22 of them.

Who do we have the most albums of? We can give any function a name and than sort by

that name.

select artist, count(album) as albumcount from albums group by artist order

by albumcount desc, artist

Alice Cooper and Elton John top the list at 22 and 21 albums, respectively. That  desc  means

descending. By default, sorting goes from least to greatest. When we specify desc it will go from

greatest to least. In this case, it puts the artists with the most albums on top of the list.

Replacing text

Sometimes you'll want to replace text inside of other text. You'll use the  update  statement

for this as well, using the body() function. For example, if you want to replace all occurrences

of  Beetles  in your album names with  Beatles , whether the word occurs on its own or

inside some title such as  The Beetles at Carnegie Hall , you would do this:

update albums set album=replace(album, "Beetles", "Beatles")

Since there is no where portion of the statement, this will affect all records in the albums

table.