Skip to content

Instantly share code, notes, and snippets.

@ajschumacher
Created March 30, 2022 18:26
Show Gist options
  • Save ajschumacher/3a3a7ccc51e60556f162fff4514a52e5 to your computer and use it in GitHub Desktop.
Save ajschumacher/3a3a7ccc51e60556f162fff4514a52e5 to your computer and use it in GitHub Desktop.

SQL workshop!


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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment