sql_answers.org

SQL

1. Which food is the most liked?

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!
;

name	love_count
carrot	2

1. Find the food that doesn’t have a description.

SELECT name
FROM food
WHERE description IS NULL
;

name
roast beef

1. Who likes the food with “beta carotene” in the description?

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
;

beta_carotene_liker
Captain Vegetable

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