*Org ORG Export*.txt

# Created 2016-04-17 Sun 21:14
#+TITLE: SQL
#+AUTHOR: Ross Donaldson
1. Which food is the most liked?
#+BEGIN_SRC sql
  SELECT food.name
         , COUNT(*) AS love_count
  FROM food
  LEFT JOIN  preferences AS prefs
  ON food.id = prefs.food_id
  WHERE prefs.attitude = 'love'
  GROUP BY food.name
  ORDER BY love_count DESC
  LIMIT 1 -- unnescessary, but handy!
  ;
#+END_SRC

#+RESULTS: 
| name   | love_count |
|--------+------------|
| carrot |          2 |

1. Find the food that doesn't have a description.
#+BEGIN_SRC sql
  SELECT name
  FROM food
  WHERE description IS NULL
  ;
#+END_SRC

#+RESULTS: 
| name       |
|------------|
| roast beef |

1. Who likes the food with "beta carotene" in the description?
#+BEGIN_SRC sql
  SELECT peeps.first_name || ' ' || peeps.last_name AS beta_carotene_liker
  FROM people AS peeps
  LEFT JOIN preferences AS prefs ON peeps.id = prefs.food_id
  LEFT JOIN food ON food.id = prefs.food_id
  WHERE food.description LIKE '%beta carotene%'
  GROUP BY 1 -- We can reference group columns by number, which is awfully handy
  ;
#+END_SRC

#+RESULTS: 
| beta_carotene_liker |
|---------------------|
| Captain Vegetable   |

Consider: what happens when you remove the ~GROUP BY~ statement, and why?