w3resource

SQL Challenges-1: Students achieved 100 percent in every subject in every year

SQL Challenges-1: Exercise-29 with Solution

From the following tables write a SQL query to find those students who achieved 100 percent in various subjects in every year. Return examination ID, subject name, examination 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
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);

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 e.exam_id,s.subject_name,
e.exam_year,
e.no_of_student
FROM exam_test  e
JOIN subject_test s
ON e.subject_id = s.subject_id
ORDER BY exam_id,subject_name,exam_year;

SELECT  s.subject_id, p.subject_name, 
SUM(s.no_of_student) 'Students for all year'  
FROM exam_test s 
JOIN subject_test p 
ON s.subject_id = p.subject_id
GROUP BY s.subject_id; 

Sample Output:

exam_id|subject_name|exam_year|no_of_student|
-------|------------|---------|-------------|
     71|Chemistry   |     2017|         2500|
     71|Mathematics |     2017|         5146|
     71|Mathematics |     2018|         3545|
     71|Physics     |     2018|         5945|
     71|Physics     |     2019|         2500|
     72|Mathematics |     2018|         3500|
     72|Physics     |     2017|         3651|
     73|Mathematics |     2018|         2647|
     73|Mathematics |     2019|         2647|
     73|Physics     |     2018|         4501|

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Actors and Directors who jointly worked three or more movies.
Next: Students achieved 100 percent marks in every subject for all the year.



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