live_code.rb

require "sqlite3"
db = SQLite3::Database.new("chinook.sqlite")

# List all customers (name + email), ordered alphabetically (no extra information)
def query_1(db)
  query = <<-SQL
    SELECT first_name, last_name, email
    FROM customers
    ORDER BY first_name asc
  SQL

   rows = db.execute(query)
end

#p query_1(db).length
# should be 59

# ===================================================================

# List tracks (Name + Composer) of the Classical playlist
def query_2(db)
  query = <<-SQL
   -- SQL Query here
   SELECT tracks.name, tracks.composer
   FROM tracks
   JOIN playlist_tracks ON tracks.id = playlist_tracks.track_id
   JOIN playlists ON playlists.id = playlist_tracks.playlist_id
   WHERE playlists.name = "Classical"
  SQL

  rows = db.execute(query)
end

 # p query_2(db).length
# should be 75

# ===================================================================

# List the 10 artists mostly listed in
# all playlists
def query_3(db)
  query = <<-SQL
  SELECT COUNT(*), artists.name
  FROM tracks
  JOIN playlist_tracks ON tracks.id = playlist_tracks.track_id
  JOIN playlists ON playlists.id = playlist_tracks.playlist_id
  JOIN albums ON tracks.album_id = albums.id
  JOIN artists ON artists.id = albums.artist_id
  GROUP BY artists.name
  ORDER BY COUNT(*) DESC
  LIMIT 10
  SQL
  rows = db.execute(query)
end

 # p query_3(db)

 # should return:
 # [[516, "Iron Maiden"], [333, "U2"], [296, "Metallica"], [252, "Led Zeppelin"],
 # [226, "Deep Purple"], [184, "Lost"], [177, "Pearl Jam"], [145, "Eric Clapton"],
 # [145, "Faith No More"], [143, "Lenny Kravitz"]]



# ===================================================================

# List the tracks which have been purchased
# at least twice, ordered by number
# of purchases

def query_4(db)
  query = <<-SQL
   -- SQL Query here
    SELECT tracks.name, COUNT(*)
    FROM invoice_lines
    JOIN tracks ON tracks.id = invoice_lines.track_id
    GROUP BY tracks.name
    HAVING COUNT(*) >= 2 -- we use 'HAVING' when we want to put a condition on an aggregated column (SUM, AVG, COUNT etc)
    ORDER BY COUNT(*) DESC
  SQL

  rows = db.execute(query)
end

p query_4(db)

# Result should contain track name and count of purchases

# [["The Trooper", 5], ["Eruption", 4], ["Hallowed Be Thy Name", 4], ["Sure Know Something", 4], ["The Number Of The Beast", 4], ["Untitled", 4], ["2 Minutes To Midnight", 3], ["Blood Brothers", 3], ["Brasil", 3], ["Can I Play With Madness", 3], ["Dazed and Confused", 3], ["Flying High Again", 3]]