Teachers Experience Years

        

Write an SQL query to retrieve the average number of years the teachers have taught in the school, rounded to two decimal places.

Input :-

School table :-
(School_ID, Teacher_ID) is the Primary Key column for the School table.

School_ID	Teacher_ID
1	1
1	2
1	3
2	1
2	4

Teachers table:-

Teacher_ID is the Primary Key column for the Teacher table.

Teacher_ID	Name	Experience_Years
1	Shalini	3
2	Roopa	2
3	Deepa	1
4	Savitha	2

Output :-

School_ID	Average_Years
1	2.00
2	2.50

Query :-

select s.School_ID, round(average(t.Experience_Years),2) as Average_Years
from School s
join Teachers t
on s.Teacher_ID=t.Teacher_ID
group by School_ID;

Explanation : - 

This SQL query retrieves the average number of years of experience for teachers in each school. Let's break down the query step by step :-

• The query begins with the Select statement, which specifies the columns to be returned in the result. In this case, it selects the ‘School_ID’ column from the “School” table and calculates the rounded average of the ‘Experience_Years’ column from the “Teachers” table upto 2 decimal digits, which is aliased as ‘Average_Years’.
• The Join clause combines the “School” table with the “Teachers” table. It matches the ‘Teacher_ID’ column from both tables to establish the relationship between schools and teachers.
• The on keyword is used to define the condition for the join, stating that the ‘Teacher_ID’ column in the “School” table should be equal to the ‘Teacher_ID’ column in the “Teachers” table.
• The Group by clause groups the result set by ‘School_ID’. This means that the average calculation will be performed separately for each distinct School_ID.

Overall, this query retrieves the ‘School_ID’ and the average years of experience for teachers rounded off upto 2 digits in each school by joining the “School” and “Teachers” tables and grouping the result by School_ID. The rounded average is then returned as ‘Average_Years’ in the result set.