advanced_group_by.sql

SELECT
	state,
	COUNT(state)
FROM users
GROUP BY state
-- BONUS 2: Only get states with more than 10 users
HAVING COUNT(state) > 10
-- BONUS 1: Order from lowest to highest user count
ORDER BY COUNT(state)

boolean_operator_precedence.sql

SELECT * FROM users
WHERE
	(last_name = 'Diaz' OR
	last_name = 'Rodriguez') AND
	gender = 'male'

dob_sentence.sql

SELECT
	CONCAT(
		first_name, ' ', last_name,
		' was born on ',
		FROM_UNIXTIME(dob, '%M %D, %Y'),
		'.'
	)
FROM users

find_repeated_names.sql

SELECT
	last_name, first_name, COUNT(*)
FROM users
GROUP BY last_name, first_name
ORDER BY COUNT(*)

function_calls_can_go_anywhere.sql

SELECT first_name, LENGTH(first_name)
FROM users
WHERE LENGTH(first_name) > 5
ORDER BY LENGTH(first_name)

gross_sales.sql

-- Get the SUM gross sales for each album (GROUP then JOIN)
SELECT
	albums.title AS "Album",
	CONCAT('$', FORMAT(SUM(gross), 2)) AS "Lifetime Gross"
FROM sales
	INNER JOIN albums ON sales.album_id = albums.id
GROUP BY album_id

group_by_food_category.sql

-- Sum of each food category's calorie count
SELECT
	cat.name 'Category', SUM(calories) 'Calories'
FROM foods food
	INNER JOIN food_categories cat
		ON food.category_id = cat.id
-- exclude foods with 300 or less calories
-- WHERE calories > 300
GROUP BY category_id
	-- exclude groups (categories) with 300 or less TOTAL calories
	HAVING SUM(calories) > 300
ORDER BY SUM(calories)

group_concat.sql

-- The sum of calories for each food group.
SELECT
	category.name AS category,
	SUM(calories) AS calories,
	GROUP_CONCAT(food.name)
FROM food
	INNER JOIN category
		ON food.category = category.id
GROUP BY category.id

morning_review_solutions.sql

-- 1. All users in order of first name length (longest first)
SELECT * FROM users ORDER BY LENGTH(first_name) DESC

-- 2. Get Californians, and anyone last named Diaz
SELECT * FROM users WHERE 
	state = 'Kansas' OR last_name = 'Diaz'

-- 3. Anyone NOT from Indiana
SELECT * FROM users WHERE state != 'Indiana'

-- 4. Everyone's full address **combined into one field**
SELECT
	CONCAT(street, ', ', city, ', ', state, ' ', zip)
FROM users

-- 5. Anyone whose last name ends in -son
SELECT * FROM users WHERE last_name LIKE '%son'

multiple_joins.sql

-- A bit simpler:
-- vocals
-- Who sang what position for each song?
-- "Solveig sang chorus on Overture"
-- members.first_name, songs.title, vocals.type
SELECT
	members.first_name, vocals.type, songs.title
FROM vocals
	INNER JOIN songs
		ON vocals.song_id = songs.id
	INNER JOIN members
		ON vocals.member_id = members.id
ORDER BY songs.id

sounds_like_robinson.sql

SELECT last_name, SOUNDEX(last_name) FROM users
WHERE SOUNDEX(last_name) = SOUNDEX("rawbinsen")

tracklist.sql

-- A bit more complex:
-- tracklists
-- What is the order of tracks for each album?
-- "Overture is Track 1 on Le Pop"
SELECT
	albums.title, songs.title, position
FROM tracklists 
	INNER JOIN songs  ON tracklists.song_id  = songs.id
	INNER JOIN albums ON tracklists.album_id = albums.id