DBA – SQL Select query – Exercise 2

Following the exercise 1 on sql update, today we introduce a new problem to train yourself on a select query.

The context provided will be valid for the 4 next exercises.

Context

3 SQL tables with some relation which you can create using the below scripts.

  1. Student table
  2. Discipline
  3. Exam score
CREATE TEMPORARY TABLE student (
    id serial PRIMARY KEY,
    name varchar(50) NOT NULL
);
 
CREATE TEMPORARY TABLE discipline (
    id serial PRIMARY KEY,
    name varchar(50) NOT NULL,
    scale numeric(4,2) NOT NULL DEFAULT 100,
    coefficient numeric(2,1) NOT NULL DEFAULT 1
);
 
CREATE TEMPORARY TABLE score (
    id serial PRIMARY KEY,
    score numeric(4,2) NOT NULL,
    discipline_id integer NOT NULL references discipline(id),
    student_id integer NOT NULL references student(id)
);

Now some dummy data to fill in your tables :

INSERT INTO student(name) values 
('Theo'), 
('Georges'), 
('Simone'), 
('Eleonore'), 
('Megane'), 
('John'), 
('Thomas'), 
('Roger');

INSERT INTO discipline(name, scale, coefficient) values 
('Literature', 10, 1), 
('Information Technology', 10, 1.2), 
('Physics', 10, 1.4), 
('Mathematics', 10, 1.5), 
('Biology', 10, 1), 
('Anatomy', 10, 1), 
('Physiology', 10, 1), 
('Music', 10, 1);

INSERT INTO score(score, discipline_id, student_id) values
(9, 1, 1),(7, 2, 1),(5, 3, 1),(6, 4, 1),(4, 5, 1),(8, 6, 1),(3, 7, 1),(1, 8, 1),
(7, 1, 2),(3, 2, 2),(8, 3, 2),(9, 4, 2),(8, 5, 2),(10, 6, 2),(9, 7, 2),(10, 8, 2),
(9, 1, 3),(8, 2, 3),(9, 3, 3),(9, 4, 3),(9, 5, 3),(8, 6, 3),(8, 7, 3),(9, 8, 3),
(5, 1, 4),(9, 2, 4),(10, 3, 4),(9, 4, 4),(10, 5, 4),(7, 6, 4),(8, 7, 4),(3, 8, 4),
(6, 1, 5),(10, 2, 5),(5, 3, 5),(10, 4, 5),(10, 5, 5),(0, 6, 5),(0, 7, 5),(0, 8, 5),
(7, 1, 6),(3, 2, 6),(4, 3, 6),(8, 4, 6),(7, 5, 6),(7, 6, 6),(8, 7, 6),(7, 8, 6),
(9, 1, 7),(5, 2, 7),(5, 3, 7),(9, 4, 7),(8, 5, 7),(9, 6, 7),(9, 7, 7),(3, 8, 7),
(10, 1, 8),(7, 2, 8),(8, 3, 8),(1, 4, 8),(9, 5, 8),(10, 6, 8),(4, 7, 8),(7, 8, 8);

We could have used an SQL script with random data but for a better catch at the solution, let’s share the same sets.

Question

Build a query to display the average score of each student for all disciplines.

Take care about the coefficient for proper values !

Don’t hesitate to alter the tables in order to improve performances 🙂

3 responses to “DBA – SQL Select query – Exercise 2”

  1. […] Step by step we will solve the problem to the DBA exercise 2 […]

  2. […] Yet another SQL exercise in the series using the same context data. […]

  3. […] is the last SQL training exercise of the series featuring the students business […]

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: