Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- # get all students last_name, alphabetically ordered, not repeating
- SELECT DISTINCT last_name
- FROM students
- ORDER BY last_name
- # get the count of all students in each groups, order by groups names descending
- SELECT groups.name,
- Count(students.id)
- FROM groups
- LEFT JOIN students
- ON groups.id = students.group_id
- GROUP BY groups.name
- ORDER BY groups.name DESC
- # get the list of students with duplicate first_names. Order alphabetically
- SELECT DISTINCT ls.first_name,
- ls.last_name
- FROM students AS ls
- INNER JOIN students AS rs
- ON ( ls.first_name = rs.first_name
- AND ls.id != rs.id )
- ORDER BY ls.first_name
- # get list of all groups names, ordered by sum of max rating of students
- SELECT groups.NAME, coalesce(sum(students.rating), 0) as gr_rating
- FROM groups
- LEFT JOIN students # может быть INNER JOIN в зависимости от конкретизации задачи
- ON groups.id = students.group_id
- GROUP BY groups.NAME
- ORDER BY gr_rating
- # get list of all students with first_name ‘F’
- SELECT *
- FROM students
- where SUBSTRING(first_name, 1, 1) = 'F'
Add Comment
Please, Sign In to add comment