SQL & AR Practice problem solutions

solutions.md

SQL & AR Practice Solutions

These prompts taken from this lesson. Highly suggest trying these on your own FIRST before comparing to these solutions.

---

1. Get all songs

   # SQL
   SELECT * FROM songs;
   
   # AR
   Song.all
   

2. Get all the song lengths

   # SQL
   SELECT length FROM songs;
   
   # AR
   Song.select(:length)
   # or - Song.pluck(:length)
   

3. Get the songs with a play count greater than zero.

   # SQL
   SELECT * FROM songs WHERE play_count > 0;
   
   # AR
   Song.where("play_count > 0")
   

4. Get the titles of the songs with a play count greater than zero.

   # SQL
   SELECT title FROM songs WHERE play_count > 0;
   
   # AR
   Song.select(:title).where("play_count > 0")
   # or Song.where("play_count > 0").pluck(:title)
   

5. Get the titles of the songs with a play count greater than zero, sorted alphabetically.

   # SQL
   SELECT title FROM songs WHERE play_count > 0 ORDER BY title;
   
   # AR
   Song.select(:title).where("play_count > 0").order(:title)
   # or Song.where("play_count > 0").order(:title).pluck(:title)
   

6. Get the titles of the songs with a play count greater than zero, sorted reverse alphabetically.

   # SQL
   SELECT title FROM songs WHERE play_count > 0 ORDER BY title DESC;
   
   # AR
   Song.select(:title).where("play_count > 0").order(:title)
   # or Song.where("play_count > 0").order(:title).pluck(:title)
   

7. Get the titles of the two songs with a play count greater than zero, sorted reverse alphabetically.

   # SQL
   SELECT title FROM songs WHERE play_count > 0 ORDER BY title DESC LIMIT 2;
   
   # AR 
   Song.select(:title).where("play_count > 0").order(title: :desc).limit(2)
   

8. Get the length of the song with the most plays.

   # SQL
   SELECT length FROM songs ORDER BY play_count desc LIMIT 1; 
   
   # AR
   Song.order(play_count: :desc).first.length