Appendix

GROUP BY

Get the number of dump people(iq<100) and normal people(iq>=100). Hint: use the COUNT() function.

SELECT MAX(iq),COUNT(*) FROM Character GROUP BY iq<100

Get the maximum IQ for male and female characters. That is Maximum Male IQ = ? and Maximum Female IQ = ?

SELECT sex,MAX(iq) FROM Character GROUP BY sex

Update

Write a small description for Brat Simpson in the 'description' field. If you want to get the id, use the select command to find it.

SELECT id FROM Character WHERE name='Bart Simpson' # Get the id this way
UPDATE Character SET description='Son of Homer and Marge and brother of Lisa and Maggie.' WHERE id=3

Change the job of Homer Simpson to 'Astronaut'.

UPDATE Character SET job='Astronaut' WHERE name='Homer Simpson' 
#Assuming that name has not been changed.

Change Homer's name to 'Homer Jay Simpson'.

UPDATE Character SET name='Homer Jay Simpson' WHERE name='Homer Simpson'

Database Relations

Find the name of all episodes where Lisa appeared.

SELECT Episode.name FROM Episode,Character, CharacterEpisode 
WHERE character_id=Character.id AND episode_id=Episode.id AND 
Character.name LIKE 'Lisa%'

Find the total number of episodes that had Bart in it.

SELECT COUNT(Episode.name) FROM Episode,Character, CharacterEpisode 
WHERE character_id=Character.id AND episode_id=Episode.id AND 
Character.name LIKE 'Bart%'