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;
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:
Create a copy of the table;
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.
Is there a reason why the challenge 9 code suggests an inner join instead of a LEFT JOIN?
From the sample:
WITH play_count AS (
SELECT song_id,
COUNT(*) AS 'times_played'
FROM plays
GROUP BY song_id)
SELECT songs.title,
songs.artist,
play_count.times_played
FROM play_count
JOIN songs
ON play_count.song_id = songs.id;
Theoretically, couldn’t there be songs that have never been played? So we would want to do a left join to see those songs which have NULL values from the play_count table?
e.g.
WITH play_count AS (SELECT song_id,
COUNT(*) AS 'times_played'
FROM plays
GROUP BY song_id)
SELECT songs.title, songs.artist, play_count.times_played
from songs
left join play_count
on songs.id = play_count.song_id;
I wonder, how do we initialize the play_count variable to 0 in SQL. I’m curious to learn what you have done and seems to be useful operation (something like ‘for’ loop).
On the given database, there is no song that has never been played. So the INNER JOIN or LEFT JOIN would produce the same results here.
On the other hand this is a matter of your expected result. I could imagine that you would like to put your advertisement on the most popular songs and ignore all those who have never been played before.
I might be a little “off” here,
but this is getting more similar to JavaScript, where you can write functions that increment your total count each time the function is called.
Thing is, the play_count column does not exist in our songs table in the exercise, therefore I tried experimenting with the code and could not UPDATE it. I think we would have to log in a new row in the plays table and then link that to count as an update in our play_count temporary table. The function would have to be called each time a new song is played and added to the plays table… this seems to be more complex than the scope of this course
If anyone knows how to do this, I’d be curious to know.