SQL Challenges-1: Students achieved 100 percent for the first year of each examination of every subject

SQL Challenges-1: Exercise-31 with Solution

From the following tables write a SQL query to compute total number of students who achieved 100 percent for the first year of each examination of every subject. Return examination ID, subject name, first year, number of students.

Input:

Table: exam_test

Structure:

Data:

Table: subject_test

Structure:

Data:

exam_id is the primary key of this table.

Sample Solution:

SQL Code(MySQL):

CREATE TABLE exam_test (exam_id int not null, subject_id int not null, exam_year int not null, no_of_student int,
primary key (exam_id,subject_id,exam_year));
INSERT INTO exam_test VALUES (71,201,2017,5146);
INSERT INTO exam_test VALUES (72,202,2017,3651);
INSERT INTO exam_test VALUES (73,202,2018,4501);
INSERT INTO exam_test VALUES (71,202,2018,5945);
INSERT INTO exam_test VALUES (73,201,2018,2647);
INSERT INTO exam_test VALUES (71,201,2018,3545);
INSERT INTO exam_test VALUES (73,201,2019,2647);
INSERT INTO exam_test VALUES (72,201,2018,3500);
INSERT INTO exam_test VALUES (71,203,2017,2500);
INSERT INTO exam_test VALUES (71,202,2019,2500);
INSERT INTO exam_test VALUES (71,202,2017,2701);
INSERT INTO exam_test VALUES (73,201,2017,1000);


CREATE TABLE subject_test (subject_id int not null unique, subject_name varchar(255));
INSERT INTO subject_test VALUES (201,'Mathematics');
INSERT INTO subject_test VALUES (202,'Physics');
INSERT INTO subject_test VALUES (203,'Chemistry');


select s1.exam_id, p.subject_name,s1.exam_year as first_year, s1.no_of_student
from exam_test s1
JOIN subject_test p on s1.subject_id = p.subject_id 
join (select subject_id, min(exam_year) min_yr 
from exam_test group by subject_id) s2
on s1.subject_id = s2.subject_id 
and s1.exam_year = s2.min_yr;

Sample Output:

exam_id|subject_name|first_year|no_of_student|
-------|------------|----------|-------------|
     71|Mathematics |      2017|         5146|
     71|Physics     |      2017|         2701|
     71|Chemistry   |      2017|         2500|
     72|Physics     |      2017|         3651|
     73|Mathematics |      2017|         1000|

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Students achieved 100 percent marks in every subject for all the year.
Next: Average experience for each scheme.