w3resource

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:

FieldTypeNullKeyDefaultExtra
exam_idint(11)NOPRI
subject_idint(11)NOPRI
exam_yearint(11)NOPRI
no_of_studentint(11)YES

Data:

exam_idsubject_idexam_yearno_of_student
7120120175146
7120120183545
7120220172701
7120220185945
7120220192500
7120320172500
7220120183500
7220220173651
7320120182647
7320120192647
7320220184501

Table: subject_test

Structure:

FieldTypeNullKeyDefaultExtra
subject_idint(11)NOPRI
subject_namevarchar(255)YES

Data:

subject_idsubject_name
201Mathematics
202Physics
203Chemistry

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.



SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

Database: SQL

Ref: https://bit.ly/3zPxcD8