Get everything you can about people.
select * from people
-- comments
/* */ comments
SELECT * FROM people
capitalization?
danger: too many records!
Get everything we can about three people.
select * from people limit 3
select top 3 * from people -- in MS SQL Server
Get first name and age for three people.
select first_name, age from people limit 3
Get first name and age for three people, and have first name be called "person".
select first_name as person, age from people limit 3
Get everything for the youngest three people.
select * from people order by age asc limit 3
-- asc is optional!
Get everything for the oldest three people.
select * from people order by age desc limit 3
Get the average age of all people.
select avg(age) from people
Get all the people over age 55.
select * from people where age > 55
Get all the people between ages 12 and 42.
select * from people where 12 < age and age < 42
Get all the people named "ahmed".
select * from people where first_name = 'ahmed'
Get the average of the youngest two people's ages.
select avg(age) from people order by age asc limit 2
-- fails
select avg(age) from (select * from people order by age asc limit 2)
with youths as(select * from people order by age asc limit 2) select avg(age) from youths
Move over to grades...
Get the grades for class two.
select grade from grades where class = 2
Get the average grade for class two.
select class, avg(grade) from grades where class = 2
Get the average grade for each class.
select class, avg(grade) from grades group by class
Get all students with a grade of 90 or better.
select * from grades where grade >= 90
Get all classes with an average grade of 90 or better.
select class from grades group by class having avg(grade) >= 90
Get all information from grades, plus each student's within-class rank.
SELECT name, class, grade, ROW_NUMBER() OVER ( PARTITION BY class ORDER BY grade DESC ) AS rank_in_class FROM grades
Get the number of pets for each owner.
select owners.name, sum(pets.type is not null) from owners left outer join pets on owners.id = pets.owner group by owners.name