DBA – SQL select query – solution to exercise 2

Step by step we will solve the problem to the DBA exercise 2

I assume you’ve successfully loaded all data in your environment.

Analyse

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 ?

  1. For each student present in the table, we should obtain the average score of all disciplines.
  2. The student name should appear in the results.
  3. 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;
Scores / student - SQL Query
Scores / Student – SQL query

Starting from here, you need to think about the way to consider the coefficient which is provided within the discipline table.

Result

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 :

Scores and Coefficient - Select Statement
Scores / Student with coefficients

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.

Performance

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) :

Explain analyse with no index
No index on Student name

Now if we add an index as follow :

CREATE INDEX idx_student_name 
ON student(name);
Explain analyse with student name index
Explain analyse with index

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. 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Want more information?

Related links will be displayed within articles for you to pick up another good spot to get more details about software development, deployment & monitoring.

Stay tuned by following us on Youtube.

%d bloggers like this: