# DBA – SQL select query – solution to exercise 2

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

## 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;
```

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 :

You likely noticed the coefficients made some student loose while others gain !

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

```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.

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