Step by step we will solve the problem to the DBA exercise 2
I assume you’ve successfully loaded all data in your environment.
We have 3 tables and we need to build a query which rely on fields present across all of them.
Obviously there is a need to make some SQL joins as there are relationships in between.
An SQL aggregate function is also required so we will likely rely on a group by clause for our select statement.
What do we actually need to display ?
- For each student present in the table, we should obtain the average score of all disciplines.
- The student name should appear in the results.
- You should take care of the coefficient to compute the average.
The first step you can achieve is to compute the average without bothering about the coefficients :
SELECT ROUND(AVG(score),2), student.name FROM student LEFT JOIN score ON score.student_id = student.id GROUP BY student.name;
Starting from here, you need to think about the way to consider the coefficient which is provided within the discipline table.
I bet another join will do !
SELECT ROUND(AVG(score * coefficient),2), student.name FROM student LEFT JOIN score ON score.student_id = student.id INNER JOIN discipline ON discipline.id = discipline_id GROUP BY student.name;
Following is the screenshot showing the new resulting rows :
You likely noticed the coefficients made some student loose while others gain !
About the type of JOIN used, did you see the INNER versus the LEFT type ?
Well in our case, there is no much difference as we have a score on all disciplines for every students. There could have been some in case we had missing entry in our tables.
Before you leave this page, let’s improve the performance of our query by using an index on the student name.
Before the index constraint, here is the result of the server analysis (ex : Postgres) :
Now if we add an index as follow :
CREATE INDEX idx_student_name ON student(name);
That’s it with the exercise !
If you have any questions or concerns, drop a comment here.
And if you need more details about the optimization, get back to solutions of other exercises. We try to provide such process for each topic.