jukebox_query.rb

require 'sqlite3'
require 'byebug'

# ruby multi-line syntax:
# <<-SQL (SQL is a tag name of your choosing)
# string line 1
# string line 2
# ...
# SQL (closes the tag)

def detailed_tracks(db)
  # TODO: return the list of tracks with their album and artist, ordered by artist name.
  query = <<-SQL
            SELECT t.name, alb.title, art.name FROM tracks t
            JOIN albums alb ON t.album_id = alb.id
            JOIN artists art ON alb.artist_id = art.id
            ORDER BY art.name ASC
          SQL
  db.execute(query)
end

def stats_on(db, genre_name)
  # TODO: For the given category of music, return the number of tracks and the average song length (as a stats hash)

  # string that are to be compared in SQL
  # need to be quoted ''
  # that means if you interpolate a ruby variable
  # the interpolation needs to be quoted as well:
  # '#{variable}'

  query = <<-SQL
            SELECT COUNT(*), AVG(milliseconds) FROM tracks
            JOIN genres ON tracks.genre_id = genres.id
            WHERE genres.name = '#{genre_name}'
          SQL
  results = db.execute(query).flatten

  # returns an HASH
  {
    category: genre_name,
    number_of_songs: results[0],
    avg_length: (results[1] / 60_000).round(2)
  }
end

def top_five_artists(db, genre_name)
  # TODO: return list of top 5 rock artists

  # LIMIT X will return an X number of results
  query = <<-SQL
            SELECT artists.name, COUNT(*) AS c FROM artists
            JOIN albums ON albums.artist_id = artists.id
            JOIN tracks ON tracks.album_id = albums.id
            JOIN genres ON tracks.genre_id = genres.id
            WHERE genres.name = '#{genre_name}'
            GROUP BY artists.name
            ORDER BY c DESC
            LIMIT 5
          SQL
  db.execute(query)
end