Given 3 tables, students(id, name) , courses(id, name) , grades(id, course_id, student_id, grade), find the top 100 students based on their average grades sorted descendingly by the average grade and in case multiple students have the same average grade, sort them lexicographically in ascending order by their names. Your query should output a table with the following columns (name, average_grade).

Sure, here is the SQL query that you can use to get the desired output:

SELECT s.name, AVG(g.grade) as average_grade
FROM students s
JOIN grades g ON s.id = g.student_id
GROUP BY s.id, s.name
ORDER BY average_grade DESC, s.name ASC
LIMIT 100;

Here is the step by step explanation:

1. FROM students s - This is the main table we are selecting from, and we are aliasing it as s for simplicity.

2. JOIN grades g ON s.id = g.student_id - We are joining the grades table (aliased as g) on the students table where the id field of the students table matches the student_id field of the grades table. This allows us to access the grade field for each student.

3. GROUP BY s.id, s.name - We are grouping the results by the id and name fields of the students table. This allows us to calculate the average grade for each student.

4. SELECT s.name, AVG(g.grade) as average_grade - We are selecting the name field from the students table and the average of the grade field from the grades table. The AVG function calculates the average of a group of values.

5. ORDER BY average_grade DESC, s.name ASC - We are ordering the results first by the average_grade field in descending order (so the students with the highest average grade are first), and then by the name field in ascending order (so if two students have the same average grade, the one with the name that comes first alphabetically will be first).

6. LIMIT 100 - We are limiting the results to the top 100 rows.

Sure, here is the SQL query that you can use to get the desired output:

SELECT s.name, AVG(g.grade) as average_grade
FROM students s
JOIN grades g ON s.id = g.student_id
GROUP BY s.id, s.name
ORDER BY average_grade DESC, s.name ASC
LIMIT 100;

Here is the step by step explanation:

1. SELECT s.name, AVG(g.grade) as average_grade: This line is selecting the name of the student from the students table and the average of the grades from the grades table.

2. FROM students s JOIN grades g ON s.id = g.student_id: This line is joining the students table and the grades table on the condition that the id in the students table matches the student_id in the grades table.

3. GROUP BY s.id, s.name: This line is grouping the results by the id and name in the students table. This is necessary because we want to calculate the average grade for each student.

4. ORDER BY average_grade DESC, s.name ASC: This line is ordering the results first by the average grade in descending order (so the students with the highest average grade come first), and then by the name in ascending order (so if two students have the same average grade, the one with the name that comes first in alphabetical order will come first).

5. LIMIT 100: This line is limiting the results to the top 100 students.