test sql queries

test_sql_queries.sql

# 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'