LIVECODE SQL Day One

app.rb

DB = "connection to the DB"

def find_tracks_by_genre(genre_name)
  query = <<SQL
  SELECT tracks.name, tracks.composer
  FROM tracks
  JOIN playlist_tracks ON playlist_tracks.track_id = tracks.id
  JOIN playlists ON playlist_tracks.playlist_id = playlists.id
  WHERE playlists.name = '#{genre_name}';
  SQL

  DB.execute(query)
end

solution.sql

-- 1. List all customers (name + email), ordered alphabetically (no extra information)
SELECT first_name, last_name, email
FROM customers
ORDER BY first_name, last_name ASC;

-- 2. List tracks (Name + Composer) of the Classical playlist
SELECT tracks.name, tracks.composer
FROM tracks
JOIN playlist_tracks ON playlist_tracks.track_id = tracks.id
JOIN playlists ON playlist_tracks.playlist_id = playlists.id
WHERE playlists.name = 'Classical';

-- 3. List the 10 artists mostly listed in all playlists
SELECT artists.name, COUNT(artists.id) AS listing_count
FROM artists
JOIN albums ON artists.id = albums.artist_id
JOIN tracks ON albums.id = tracks.album_id
JOIN playlist_tracks ON playlist_tracks.track_id = tracks.id
GROUP BY artists.id
ORDER BY listing_count DESC
LIMIT 10;

-- 4. List the tracks which have been purchased at least twice, ordered by number of purchases
SELECT tracks.name, COUNT(invoice_lines.id) AS purchases
FROM tracks
JOIN invoice_lines ON invoice_lines.track_id = tracks.id
GROUP BY tracks.name
HAVING purchases >= 2
ORDER BY purchases DESC