Previous
Reference 
SQL(Structured Query Language) Tutorial
Appendix

Appendix

GROUP BY

Question:
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%'
Previous
Reference 
blog comments powered by Disqus
Subscribe to Feed