SQL(Structured Query Language) Tutorial
- 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
- 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'
- 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%'