Is there another way that determining play counts of a song might have been implemented?

Question

In the context of this exercise, is there another way that determining the play counts for a song might have been implemented?

Answer

Yes, schemas can always be different depending on who implemented them, so there are always different ways that you can implement things. For determining the number of times a song has been played, the method used in this exercise works as needed, but we could do something different.

One other way to do this would be to store the number of plays in the songs table, where for each song, we have a value keeping track of how many times it has been played.

To do this, assume that this new column called play_count stores an integer value, that starts from 0 and increments by 1 each time the song has been played. To implement the increment, we could do something like the following, say if we wanted to update the play count for a song with id 15.

UPDATE songs
SET play_count = play_count + 1
WHERE id = 15;
5 Likes

If you wanted to update the play count in this manner for every song could this be accomplished using a GROUP BY statement and eliminating the id=15?

What to do if we need to select all columns except 1 or 2 ?
For example if we have say 10 columns and we want 8 columns in our query.
DO we have to write
Select column 1, column 2… column 8 from table
OR
is there any other shortcut?

Not really. If the table has many columns and you know that to make some sort of analysis you only need a selection of those columns then you could:

  1. Create a copy of the table;
    1. Drop these one or two columns that you don’t need.

This way you will have a temporary table with all the data that you need, and you will still get an original table intact.


Another way is to create a view consisting of all the columns that interest you. This way you will have to write Select column 1, column 2… column 8 from table only one time and then you will be able to perform queries on the view.